文本函數中的戰斗機萬金油 Excel Text函數基本與組合用法

Excel中文本函數參數較少，易學易用，比如Left、Right、Mid函數等，但是有一個非常強大的文本函數，就是因為參數少加上看起來很簡單被我們低估了。

它就是函數中的戰斗機、函數中的“萬金油”Text函數。

1.Text函數的基本用法

text函數的語法：text(value,format_text），其中：Value為數字值，Format_text 為設置單元格格式中自己所要選用的文本格式。

簡單來說，Text函數最主要的功能就是將數值轉換成文本。

因此需要注意，Text函數產生的結果一定要文本，是無法參與計算的。

舉例來說：在工作中我們經常可以看到一些文本格式的日期，在Excel中這樣的日期是不規范的，也不能直接作為日期參與計算。因此為了方便統計與分析，我們需要將文本日期轉換為真正的日期。

在B2單元格中輸入公式=--TEXT(A2,"#-00-00")

將B列單元格格式更改為日期格式，即可完成操作。

這里需要注意的是，直接修改A列的單元格格式為日期格式或者直接轉化為數字均是無法成為規范的日期格式的。

在這種情況下，Text的作用就凸顯出現了。

2.提取身份證號碼中的性別和出生日期

在上周分享的文章《10個小技巧，數據輸入效率提升10倍》中，我們知道身份證號碼是文本格式的，要想從文本格式中提取數字信息需要利用Text函數進行轉換。

在15位身份證號碼中，最后一位表示性別，男性為奇數，女性為偶數。18位身份證號碼中，倒數第二位表示性別，同樣是男性為奇數，女性為偶數。

公式=TEXT(MOD(MID(B2,15,3),2),"男;;女")

公式解析：MID(B2,15,3)表示提取15身份證號碼中的最后一位，提取18位身份證號碼中的第15、16、17位；MOD(MID(B2,15,3),2)判斷奇偶；

TEXT(MOD(MID(B2,15,3),2),"男;;女")余數為正數顯示“男”，余數為負數顯示空，余數為0則顯示“女”。

注意：這里Text表示四種數據類型，常用方法為=text(數據,"正;負;零;文本")

不管是15位身份證號碼還是18位身份證號碼，出生日期都是從第7位開始的，區別是15位少了“19”，例如B4的出生日期是“690626”，即1969年06月26日。

公式=--TEXT((LEN(B2)=15)*19&MID(B2,7,6+(LEN(B2)=18)*2),"0-00-00")

公式解析：(LEN(B2)=15)*19，如果身份證號碼為15位，則結果為1*19=19，如果身份證號碼不是15位，則結果為0*19=0。

MID(B2,7,6+(LEN(B2)=18)*2)從身份證號碼的第7位開始取值，如果為18位，那么截取的位數就=6+1*2=8，如果為15位，那么截取的位數就是6+0*2=6。剛好實現了18位截取8位、15位截取6位的目的。

15位最后的結果：19與截取的6位合并，例如A4單元格，19690626。

18位最后的結果：0與截取的8位合并，例如A2單元格，019870623。

TEXT((LEN(B2)=15)*19&MID(B2,7,6+(LEN(B2)=18)*2),"0-00-00")將字符串19690626轉變為1969-06-26這樣的格式，再通過--負負得正的運算將文本字符轉換為日期字符。

最后再通過設置單元格格式，將數字格式設置為日期，最后年月日就計算出來了。

3.格式化員工工號

由于公司重組合并，需要對員工工號進行升級，不足8位的需要在工號前補0升成8位，升級后的工號前還需要有其所屬的部門。

公式=A2&TEXT(B2,"00000000")

公式解析：TEXT(B2,"00000000")將原始工號升級為8位數的工號。

A2&TEXT(B2,"00000000")將部門名稱與8位數工號合并。

4.阿拉伯數字轉換為中文大小寫數字

在實際工作中，我們可能會遇到需要把數字表示的年、月、日轉換為中文的年、月、日，例如2017年轉換為“二〇一七年”或“貳零壹柒年”。

公式解析：[DBNum1]是中文小寫格式，[DBNum2]是中文大寫格式。

[DBNum1]后面加0表示讓數字逐位顯示。

加0與不加0區別如下：

還有一種是大小寫金額的轉換，在工作中也非常常見。

公式=TEXT(A11*100,"[DBNum2]0億0仟0佰0拾0萬0仟0佰0拾0元0角0分")

5.累計加班時間

下圖為某員工上半月每天的加班時長，現需要計算加班總時長。如果使用公式=SUM(B2:B16)得出來的結果為18:35（實際為42:35），顯然是錯誤的。

這是因為單元格自動把超過24小時的時間進位到天了，而結果只顯示小于24小時的時間。

所以，我們需要借助Text公式=TEXT(SUM(B2:B16),"[h]:mm")，阻止單元格自動把超過24小時的時間進位到天，只以時間累計。

6.自動判斷盈虧、評定等級

一提到盈虧、等級，可能很多首先想到的是IF函數，其次是LOOKUP函數，其實Text函數也可以判斷盈虧、評定等級。

公式=TEXT(B2,"[>90]!優秀;[<80]!不及格;及格")

可以用=text(數據,"正;負;零;文本")判斷盈虧，公式=TEXT(B2,"增加￥0元;減少￥0元;持平;缺崗")

7.與SUM函數組合使用

現有公司3月份和4月份每天的銷售額，需要計算3月和4月份的銷售總額。

在E3單元格輸入公式=SUM((TEXT($A:$A,"YYYYMM")=TEXT(D3,"YYYYMM"))*$B:$B)

同時按下ctrl+shift+enter鍵，得到3月份的銷售總額。

公式解析：TEXT($A:$A,"YYYYMM")是將日期單元格轉化為“201703”和“201704”的形式。TEXT(D3,"YYYYMM")也是將2017年3月轉換為“201703”。

如果TEXT($A$2:$A$21,"YYYYMM")和TEXT(D3,"YYYYMM")相等的話（同時為201703），則返回值為1，否則返回值為0。

SUM((TEXT($A$2:$A$21,"YYYYMM")=TEXT(D3,"YYYYMM"))*$B$2:$B$21)是對所有返回值為1所對應的B列銷售額進行求和。

今天的教程就到這里了

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。