如果函數(shù)有職業(yè),TEXT絕對(duì)是變裝女皇!
?
作者:老菜鳥來(lái)源:部落窩教育發(fā)布時(shí)間:2019-05-20 14:55:37點(diǎn)擊:7138
編按:
大家試想一下,如果函數(shù)有職業(yè),那各函數(shù)的職業(yè)會(huì)是什么呢?別的先不說(shuō),就拿TEXT而言,它可以讓日期變數(shù)字、數(shù)字變?nèi)掌?、阿拉伯?dāng)?shù)字變大寫中文數(shù)字、金額元變?nèi)f元,連IF的條件判斷它也可以變出來(lái)…這簡(jiǎn)直就是當(dāng)之無(wú)愧的變裝女皇?。?/span>
在Excel的函數(shù)中,有個(gè)被稱為魔法師的函數(shù),它就是TEXT。在之前的教程中,我們就曾經(jīng)領(lǐng)略過(guò)這個(gè)函數(shù)的魔法《最魔性的TEXT函數(shù),看一眼就心動(dòng)~》。
不過(guò),今天,我們更樂(lè)意把它稱為變裝女皇!為什么呢?請(qǐng)往下看!
變裝一:八位數(shù)字變成日期
很多公司都會(huì)使用ERP系統(tǒng),某些系統(tǒng)中的日期是以8位數(shù)字的形式呈現(xiàn)的,當(dāng)我們導(dǎo)出系統(tǒng)中的數(shù)據(jù)時(shí),很可能會(huì)看到這樣的情況:
使用這樣的日期去做數(shù)據(jù)分析是不便的,需要將其變成標(biāo)準(zhǔn)的日期格式才行,請(qǐng)看TEXT的表演:
公式解讀:
=TEXT(A2,"0年00月00日")
A2是需要處理的數(shù)據(jù),奧秘在"0年00月00日"這一部分,0是占位符,使用年月日將8位數(shù)字分成三段。需要注意的是劃分從右往左進(jìn)行,首先將A列最右邊的兩位數(shù)字當(dāng)作“日”,然后挨著的左邊兩位當(dāng)作“月”,最后剩下的四位數(shù)字只需要一個(gè)0就可以代表,這四位數(shù)字當(dāng)作“年”。
這個(gè)公式完整的寫法是:=TEXT(A2,"0000年00月00日"),這樣八位日期數(shù)字就能看明白了!
變裝二:日期變成八位數(shù)字
在某些時(shí)候,還會(huì)遇到將日期變成八位數(shù)字的情況,既然TEXT能把八位數(shù)字變成日期,那么再變回去當(dāng)然沒(méi)問(wèn)題了:
公式解讀:
=TEXT(H2,"emmdd")
H2是要處理的數(shù)據(jù),不同的是后面的格式代碼相比上次完全不同了。
在第一個(gè)例子中,我們要處理的數(shù)據(jù)源是數(shù)字,因此用到了數(shù)字占位符0。但在這個(gè)例子中,數(shù)據(jù)源是日期,就不能用0了。e表示“年”,也可以用yyyy代替,m表示“月”,d表示“日”。一個(gè)e就是四位,再加上兩個(gè)m和兩個(gè)d,剛好就是8位。
變裝三:拆分日期和時(shí)間
把數(shù)字和日期之間的戲法耍過(guò)之后,來(lái)看看TEXT是如何拆分日期和時(shí)間的。
這種情況常見(jiàn)于考勤數(shù)據(jù):
只有把打卡日期和時(shí)間分開才好做進(jìn)一步的統(tǒng)計(jì),TEXT真的可以辦到嗎?
拆分日期:
公式解析:
=TEXT(B2,"e/m/d")
e表示年,m表示月,d表示日,很好理解。
拆分時(shí)間:
公式解析:
=TEXT(B2,"h:mm:ss")
h表示時(shí),m表示分,s表示秒。
戲法揭穿了其實(shí)一點(diǎn)也不難。
但是你可千萬(wàn)別以為懂得這幾個(gè)代碼就能看穿TEXT,不信往下看……
變裝四:數(shù)字變大寫中文
這個(gè)戲法變得怎么樣!
公式解析:
=TEXT(A2,"[DBNUM2]")
DBNUM2是針對(duì)于數(shù)字的特定代碼,需要放在一對(duì)中括號(hào)中。數(shù)字2還可以改成1和3,具體是什么效果你可以試試看,記得留言告訴大家你測(cè)試的結(jié)果哦!
對(duì)了,改成4也是可以的,至于5、6、7……
看到這一個(gè)例子,做財(cái)務(wù)工作的朋友估計(jì)會(huì)有想法了,能不能用TEXT函數(shù)將會(huì)計(jì)報(bào)表中的金額變成包含圓角分的大寫金額呢?
可以自己先試一下,如果需要這方面的教程記得留言告訴我們。
變裝五:金額元變?nèi)f元
連阿拉伯?dāng)?shù)字都能變成中文大寫數(shù)字,金額元變成萬(wàn)元就更不在話下了:
公式解析:
=TEXT(A2,"0!.0000萬(wàn)元")
和第一個(gè)例子一樣,0還是占位符,不過(guò)這里多了一個(gè)感嘆號(hào)。如果沒(méi)有感嘆號(hào),"0.0000"表示數(shù)字保留四位小數(shù)。在TEXT的秘密武器中,感嘆號(hào)用于在原有內(nèi)容的某個(gè)位置強(qiáng)制增加感嘆號(hào)后的字符,所以我們?cè)趩卧裰锌吹降哪莻€(gè)小數(shù)點(diǎn)其實(shí)是在原數(shù)據(jù)千位數(shù)字左邊強(qiáng)行加進(jìn)去的,最后加上“萬(wàn)元”這個(gè)后綴,就變成這樣的效果了。
如果你覺(jué)得四位小數(shù)太多了,還可以保留一位小數(shù):
公式解析:
=TEXT(A2,"0!.0,萬(wàn)元")
在這個(gè)公式中,特定代碼中間出現(xiàn)了一個(gè)逗號(hào)。這個(gè)逗號(hào)其實(shí)就是數(shù)字格式中的千位分隔符:
使用了千位分隔符后,數(shù)字就縮小了千倍,相當(dāng)于變成以千元計(jì)的數(shù)字,因此只需要在最后一位數(shù)字的前面顯示小數(shù)點(diǎn)就能變成萬(wàn)元計(jì)的數(shù)字。
什么!還想要兩位小數(shù)的……
這個(gè)要求雖說(shuō)有些為難TEXT,但也不是不行。在之前的例子中,還從來(lái)沒(méi)有對(duì)第一參數(shù)動(dòng)過(guò)手腳,只是在玩格式代碼,現(xiàn)在看來(lái)不出絕招是不行了:
公式解析:
=TEXT(A2%%,"0.00萬(wàn)元")
A2后面加兩個(gè)百分號(hào),表示對(duì)單元格A2中的數(shù)字除以10000。既然已經(jīng)對(duì)數(shù)據(jù)源做了手腳,格式代碼自然就不需要感嘆號(hào)了,直接按照數(shù)字的設(shè)置規(guī)則去做就好了。0.00表示以兩位小數(shù)顯示,當(dāng)然也可以用0.0、0.000、0.0000去設(shè)置不同的小數(shù)位。
變裝六:搶IF的風(fēng)頭做條件判斷
在日期、時(shí)間、數(shù)字、金額這些地方耍了一圈的TEXT,這次又跑到了IF的領(lǐng)域,連IF函數(shù)的風(fēng)頭也想搶:
看上去表現(xiàn)還不錯(cuò),這又是什么套路呢?
公式解析:
=TEXT((A2-B2)/A2,"上升0%;下降0%;持平;")
這次TEXT沒(méi)有使用格式代碼,而是用了一個(gè)新道具:分號(hào)。使用分號(hào)后,TEXT函數(shù)可以做條件判斷。
第一種,默認(rèn)判斷:
套路是TEXT(數(shù)據(jù),">0結(jié)果;<0結(jié)果;=0結(jié)果;文本的結(jié)果")。TEXT默認(rèn)把數(shù)據(jù)分成四種類型,正數(shù)、負(fù)數(shù)、零與文本,不同的類型返回不同的結(jié)果。參數(shù)中各個(gè)結(jié)果之間依次用分號(hào)隔開。參數(shù)中第一個(gè)分號(hào)前的值是正數(shù)的返回值;第二個(gè)分號(hào)前的值是負(fù)數(shù)的返回值;第三個(gè)分號(hào)前的值是零的返回值,最后一個(gè)值是文本的返回值。
當(dāng)(A2-B2)/A2為正數(shù)時(shí),顯示上升和百分比的增長(zhǎng)率;為負(fù)數(shù)時(shí),顯示下降和百分比的下降率;為零時(shí),顯示持平。
第二種,運(yùn)算符判斷:
實(shí)際上TEXT函數(shù)還支持用比較運(yùn)算符作為判斷的條件,例如成績(jī)大于等于85分為優(yōu)秀,大于等于60分為及格,60以下為不及格,使用TEXT的公式是這樣的:=TEXT(F2,"[>=85]優(yōu)秀;[>=60]合格;不合格")
在這種用法中,條件要放在中括號(hào)內(nèi),括號(hào)后面緊跟要顯示的內(nèi)容。最后使用分號(hào)作為一組條件和結(jié)果的分隔符。
一個(gè)TEXT函數(shù)條件最多可以使用3個(gè)條件,如果多于3個(gè)條件,則返回錯(cuò)誤值#VALUE!。對(duì)于一些簡(jiǎn)單的判斷問(wèn)題,使用TEXT函數(shù)不僅比IF更為簡(jiǎn)短,而且看起來(lái)更高大上呢。
是不是很神奇,喜歡這個(gè)函數(shù)變裝女皇的話記得要點(diǎn)“在看”??!
本文配套的練習(xí)課件請(qǐng)加入QQ群:264539405下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
13種日期數(shù)據(jù)方法《13種日期輸入法你只會(huì)第1種手動(dòng)輸入?》
TEXT函數(shù)(上)《Excel教程:最魔性的TEXT函數(shù),看一眼就心動(dòng)~》
IF函數(shù)講解《IF函數(shù):剝洋蔥》
最熱教程
- 像綠皮火車一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒(méi)有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問(wèn)題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說(shuō)是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!
最新教程
- SHEETSNAME,一鍵獲得所有工作表名稱完成目錄制作
- 延遲退休落地,快速查詢你上幾年才退休!
- 快速將PDF電子發(fā)票數(shù)據(jù)提取到發(fā)票明細(xì)登記表的方法
- 提取、查找、替換數(shù)據(jù)的王者——REGEXP正則函數(shù)
- Excel表10萬(wàn)行數(shù)據(jù),篩選卡頓怎么辦?
- 兩個(gè)不用嵌套的萬(wàn)能提取公式,含用REGEXP函數(shù)提取
- 用Excel制作刷題器,備考無(wú)憂 !
- 如何用LET+LAMBDA直接寫遞歸公式?
- 用函數(shù)合并多個(gè)工作表數(shù)據(jù)
- 用LAMBDA自定義顏色求和函數(shù)