5分鐘,學(xué)會文本函數(shù)之王——TEXT的常用套路
?
作者:小花來源:部落窩教育發(fā)布時間:2020-04-26 15:10:04點擊:4295
編按:
哈嘍,大家好!要說在excel中最特別的文本函數(shù),那必定非TEXT函數(shù)莫屬,外界給它的稱號不計其數(shù)“文本之王”“整容大師”“千面鬼才”等等,由此可看出對它的喜愛。下面小花就和大家分享5種最實用的TEXT函數(shù)用法,趕緊來看看吧!
TEXT函數(shù)是眾多文本函數(shù)中頗受花瓣們喜愛的一個,小花將它稱之為千機變。它和自定義數(shù)字格式相似度極高,多數(shù)時候二者都可以任意擇用。要說二者的區(qū)別,大致有如下幾點:
1.在自定義數(shù)字格式中,星號(*)表示重復(fù)某個字符直到填滿單元格;而在TEXT中無法實現(xiàn)這一功能,這一功能需要REPT函數(shù)才能實現(xiàn)。
2.在自定義數(shù)字格式中,它為我們提供了八種不同的顏色可供使用,而在TEXT函數(shù)中同樣無法使用這些顏色,但我們可以使用條件格式來輔助TEXT函數(shù)完成該功能。
3.自定義數(shù)字格式僅改變數(shù)字的顯示格式,不改變數(shù)字的數(shù)值本質(zhì),這些被“易容”了的數(shù)字一樣可以進(jìn)行加減乘除求和平均等匯總計算;而TEXT函數(shù)則更“高明”一些,不僅“易容”,而且易質(zhì),經(jīng)過TEXT函數(shù)處理后的數(shù)字,有些不能再參與運算,它們經(jīng)過質(zhì)變,已“表里如一”。
4.自定義數(shù)字格式可以在原數(shù)據(jù)所在單元格生效,而TEXT函數(shù)必須在新的單元格內(nèi)設(shè)置,前者更為簡潔,后者更具靈活性!
看完TEXT函數(shù)和自定義數(shù)字格式的區(qū)別后,讓我們一起來通過幾個例子看下千面鬼才——TEXT函數(shù)的絕代風(fēng)華。
基本用法
基本語句:=TEXT(數(shù)值,文本格式)
用法說明:TEXT函數(shù)可通過格式代碼對數(shù)字應(yīng)用格式,進(jìn)而更改數(shù)字的顯示方式。其中,參數(shù)①是我們想要改變格式的數(shù)值,它可以是文本,也可以是數(shù)字;而參數(shù)②就是我們想要參數(shù)①最終變成的格式,它的作用原理和自定義數(shù)字格式一致,基本用法共分為四個分段,用英文分號(;)區(qū)分隔開:
[正數(shù)的格式];[負(fù)數(shù)的格式];[0的格式];[文本的格式]
具體用法,我們會在下方案例中逐一說明。
實戰(zhàn)案例
案例1:固定數(shù)字位數(shù)
以發(fā)票號碼為例,發(fā)票號碼均為8位數(shù),但有時系統(tǒng)導(dǎo)出來的發(fā)票號碼將其前面的0省略了,遇到這種情況時,如何將0批量自動補齊呢?只需輸入=TEXT(A4,"00000000")。
用法說明:
參數(shù)②為“00000000”,省略了負(fù)數(shù)、零值和文本的格式,這表示該格式對所有數(shù)值適用,但對文本不適用。此處的0是占位符,表示該數(shù)位如有有效數(shù)值,則取有效數(shù)值,否則以0填充該數(shù)位。以A4單元格為例,個位到十萬位都有有效數(shù)值,所以這部分?jǐn)?shù)值保持不變;前兩位數(shù)沒有有效數(shù)值,則用0填充,于是550277就變成了00550277,這就是TEXT函數(shù)的作用。
案例2:提取出生日期
如何從身份證號碼中提取出生日期并按日期格式填寫?這個問題的解決方案有很多,TEXT+MID函數(shù)的組合公式就是其中一種。
用法說明:
MID(A2,7,8)用于提取18位身份證號碼中出生日期的8位字符串,而TEXT函數(shù)負(fù)責(zé)將8位數(shù)的出生日期字符串按0000-00-00的格式排列填寫。這里需要著重說明的是,由于MID函數(shù)提取出來的日期是一個字符串,而非真正的日期,所以,不能使用"yyyy-mm-dd"來設(shè)置格式。只有當(dāng)我們需要轉(zhuǎn)變的是真正的日期的顯示格式時,才可以使用y(年)、m(月)、d(天)這三個字符的組合來實現(xiàn)。
案例3:簡單的條件判斷
在某種程度上,TEXT函數(shù)可以作為三種條件結(jié)果判斷的加強版IF函數(shù)來使用,將滿足條件的數(shù)值轉(zhuǎn)化為指定的格式。以判斷區(qū)域公司盈虧情況為例,利潤為正則顯示為盈,負(fù)數(shù)則為虧,0則顯示為平,我們只需要使用如下公式即可:=TEXT(B2,"[>0]盈;[<0]虧;平")。
公式說明:
數(shù)字格式支持兩次條件判斷,即[條件1]格式1;[條件2]格式2;格式3,參數(shù)②"[>0]盈;[<0]虧;平"就是這種用法的一個實例。它對B列數(shù)值先進(jìn)行條件1判斷,如果大于0,則顯示“盈”,如不大于0,則進(jìn)行條件2判斷,如果小于0,則顯示“虧”;如前兩個條件都不滿足,則顯示為“平”。
案例4:計算時間間隔
如何計算上班時長或者加班時長,一直是HR小哥哥小姐姐頭痛的問題,不過還好,我們有TEXT函數(shù),什么時間間隔,什么去除尾數(shù),統(tǒng)統(tǒng)搞定!
用法說明:
參數(shù)②"[h]"表示將數(shù)值轉(zhuǎn)化為以1/24為一個單位的小時數(shù),且只取整數(shù)位。這里的h即為hour小時,同理也可以寫成分鐘m或者秒鐘s,分別計算相隔的分鐘和秒鐘。
想知道更多計算時長的方法,還可以查看往期教程《996和955到底差了多少小時,你會算嗎?》
案例5:阿拉伯?dāng)?shù)字與中文數(shù)字互換
中文數(shù)字和阿拉伯?dāng)?shù)字如何互換的問題,讓很多花瓣頭痛不已。解決方案就是將TEXT的第二參數(shù)設(shè)置為"[DBnum1]"即可,"[DBnum1]"可以將阿拉伯?dāng)?shù)字轉(zhuǎn)化為中文小寫數(shù)字,但僅限整數(shù)。
公式1:
=TEXT(A2,"[DBnum1]")
公式2:
{=MATCH(A2,TEXT(ROW($1:$9999),"[DBnum1]"),)}
公式說明:
公式1極易理解,它通過"[DBnum1]"將阿拉伯?dāng)?shù)字轉(zhuǎn)化為中文小寫數(shù)字;公式2比較生澀,它是將1-9999的阿拉伯?dāng)?shù)字逐一轉(zhuǎn)變成中文小寫數(shù)字,形成一個用中文小寫數(shù)字表示的從一到九千九百九十九的數(shù)組,最后使用MATCH來實現(xiàn)精確匹配,通過A列中文數(shù)字在該中文數(shù)組中的位置值來確定中文數(shù)字對應(yīng)的阿拉伯?dāng)?shù)字。所以說,公式2的本質(zhì)和公式1是一樣的,實質(zhì)上都是利用"[DBnum1]"來實現(xiàn)的,這一點著實巧妙!
提問:大家知道如何將阿拉伯?dāng)?shù)字轉(zhuǎn)化成中文大寫的數(shù)字嗎?
不知道的可以看下這篇文章哦~《如果函數(shù)有職業(yè),TEXT絕對是變裝女皇!》
THE END
TEXT函數(shù)在聯(lián)合其他函數(shù)使用時也有很出色的表現(xiàn),生成諸如月度財務(wù)簡報、流程數(shù)據(jù)披露、個人績效介紹等有序固定文字模板,結(jié)合數(shù)據(jù)有效性,我們可以很輕易地在Excel中生成我們需要的文字+數(shù)字的復(fù)雜段落。這些用法通常需要小花瓣們自行根據(jù)實際工作情況設(shè)置公式,小花就不在此處詳解了,上述五個例子極具代表性,相信一定有助于加深各位小花瓣們對TEXT函數(shù)的理解和掌握!
本文配套的練習(xí)課件請加入QQ群:1043683754下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
TEXT函數(shù)的應(yīng)用①《如果函數(shù)有職業(yè),TEXT絕對是變裝女皇!》
TEXT函數(shù)的應(yīng)用②《Excel教程:最魔性的TEXT函數(shù),看一眼就心動~》
Excel自定義格式①《神奇的魔法—自定義格式讓單元格多樣顯示》
Excel自定義格式②《6個職場人士最常用的自定義格式,速收藏?。ɑA(chǔ)篇)》
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!