Excel函數(shù)實戰(zhàn) text什么意思 _生活經驗

Excel的主要功能是數(shù)據(jù)處理，但是在使用時，經常會遇到一些文本數(shù)據(jù)，如提取指定的字符串，查找指定值的位置；有時候需要對數(shù)據(jù)進行文本化，比如18位的身份證，因超過15位，如以數(shù)值類型保存，最后3位會變?yōu)? ，所以我們需預先設置單元格格式為文本或數(shù)字前面加上英文狀態(tài)下的單引號等等，所以對文本類函數(shù)的學習尤為重要。
在excel中文本函數(shù)共有33個，其中最特別、最神奇的文本函數(shù)，非TEXT函數(shù)莫屬，外界它為“文本之王”、“萬能文本”等。
下面主要從四方面對text函數(shù)進行解讀：1. text函數(shù)的含義；2. text函數(shù)的語法格式； 3. text1函數(shù)的10個案例解讀；4.函數(shù)使用的注意點。
一、Text函數(shù)的含義1.Text函數(shù)：指根據(jù)指定的數(shù)值格式將數(shù)字轉為文本，也就是把數(shù)值格式轉變?yōu)橄胍奈谋?。

文章插圖
二、text函數(shù)的語法格式1、表達式：Text(value,format_text）中文表達式：Text (數(shù)值，單元格格式)參數(shù)① Value 為數(shù)字值。參數(shù)② Format_text 為設置單元格格式中要選用的文本格式

文章插圖
單元格的文本格式有幾十種可選，如下所示：

文章插圖
2、文本格式說明：
（1）小數(shù)位和整數(shù)位的格式
A、占位符 0 與 # 的區(qū)別（一個保留 0，另一個舍棄 0）。
當保留指定小數(shù)位數(shù)（如保留兩位小數(shù)）時，如果格式中小數(shù)點右邊為 0 ，例如 #.00，當數(shù)值沒有兩位小數(shù)時，在末尾會顯示 0 ，如 3.2 保留兩位小數(shù)變?yōu)?.20；如果格式中小數(shù)點右邊為 #，例如 #.##（或 0.##），當數(shù)值沒有兩位小數(shù)時，在末尾不會顯示 0，如 3.2 保留兩位小數(shù)變?yōu)?3.2 。
B、占位符 ? 用于補空格。
如果要求兩個數(shù)位不同的小數(shù)的小數(shù)點對齊，可以使用 ? 補空格；例如要求 3.2 與 23.41 的小數(shù)點對齊，可以把格式定義為 0.0? 。
C、小數(shù)點左邊的 0 不顯示格式的定義。
如果要求小數(shù)點左邊的 0 不顯示，可以把格式定義為 #.00，例如 0.51 會變?yōu)?.51 。
（2）千位分隔符格式
千位分隔符共有三種格式，第一種為 #,###，表示每三位加一個千位分隔符（逗號）；第二種為“#,”，表示省略千位分隔符后的數(shù)字；第三種為“0.0,”，表示右起第一個千位分隔符后的數(shù)字用小數(shù)表示并四舍五入。
（3）日期時間格式
A、日期中年的格式有兩種，一種為 yy（僅顯示年份后兩位），另一種為 yyyy（顯示四位年份）。日期中月格式共有五種，一種為 m（省略前導 0），另一種為 mm（顯示前導 0），還有三種為用月份的英文單詞或其縮寫表示。日期中日格式共有四種，一種為 d（省略前導 0），另一種為 dd（顯示前導 0），還有兩種為用周一到周日的英文單詞或其縮寫表示。
B、時分秒的格式都有三種，并且格式表示方法也一樣；例如：小時的格式分別為 h（省略前導 0）、[h]（返回小時數(shù)超過 24 的時間）和 hh（顯示前導 0）。
（4）貨幣符號格式
如果要把貨幣符號顯示到數(shù)字前，可以在格式中添加相應的貨幣符號；例如：在要數(shù)字前顯示元（￥），可以把格式定義為“￥#.00”；￥可以用快捷鍵 Alt + 0165（小鍵盤上的數(shù)字）輸入，具體輸入方法及其它貨幣符號的輸入方法，請看下文的實例。
（5）百分號格式
數(shù)字如果要用百分號（%）表示，可以在格式中加百分號；例如把格式定義為 0.00% 或 0% 。（6）科學記數(shù)法格式
科學記數(shù)法的格式可以為“0.0E + 0”、“0.0E + 00”或“#.0E + 0” ， E（或 e）表示以 10 為底，它右邊的數(shù)值表示小數(shù)點往左移動的位數(shù) 。
三、文本函數(shù)TEXT的10個案列講解
1、阿拉伯數(shù)字轉為中文數(shù)字
阿拉伯數(shù)字如何互換為中文數(shù)字，解決方案就是將TEXT的第二參數(shù)設置為"[DBnum1]"即可，"[DBnum1]"可以將阿拉伯數(shù)字轉化為中文小寫數(shù)字
公式：=TEXT(A3,"[DBnum1]")

文章插圖
公式講解：它通過"[DBnum1]"將阿拉伯數(shù)字轉化為中文小寫數(shù)字，但僅限整數(shù) 。
2、計算時間間隔
如何計算上班時長或者加班時長，用TEXT函數(shù)可以搞定！
公式：=TEXT(B3-A3,"h")

文章插圖
公式講解：參數(shù)②"[h]"表示將數(shù)值轉化為以1/24為一個單位的小時數(shù)，且只取整數(shù)位。公式中的h即為英文hour小時，同理也可以寫為分鐘m或者秒鐘s，分別計算相隔的分鐘和秒鐘。
3、日期轉星期
(1) 當Format_text為aaaa時， aaaa為中文的星期幾的格式。
公式：=TEXT(A3,"aaaa")

文章插圖
（2）同理：Format_text為dddd時，dddd為英文的星期幾的格式
Format_text為ddd時，ddd為英文的星期幾的省略格式。
4、日期轉年月

文章插圖
5、劃分等級
公司對員工年進行度考核時，如何劃分為三個等級？
公式：=TEXT(C3,"[>=90]優(yōu)秀;[>=60]良好;不及格") 。
【Excel函數(shù)實戰(zhàn) text什么意思】

文章插圖
公式講解：
(1)如果要將等級劃分的更多層次，可使用下面的公式：=IFS(C3=100,"滿分",C3>=90,"優(yōu)秀",C3>=80,"良好",C3>=60,"及格",C3<60,"不及格") 。
(2)該函數(shù)只適用于劃分三個等級的評選。
6、提取身份證號碼中的出生日期
如何從居民身份證號碼中提取出生日期和性別，并按日期格式填寫？借助TEXT+MID函數(shù)的組合公式就可以實現(xiàn) 。
C3單元格公式為：
=--TEXT(MID(B3,7,8),"0000-00-00")

文章插圖
公式講解：
（1）MID(B3,7,8)用于提取18位身份證號碼中出生日期的8位字符串，而TEXT函數(shù)將8位數(shù)的出生日期字符串按0000-00-00的格式顯示，此時尚不是真正的日期格式。
(2)在TEXT函數(shù)前加上負負得正的運算，將文本字符轉換為日期字符，最后再設置單元格格式。
(3)由于MID函數(shù)提取出來的日期是一個字符串，而非真正的日期，所以不能使用"yyyy-mm-dd"來設置格式.
7、提取身份證號碼中的性別
如何從居民身份證號碼中提取出性別？借助TEXT+MID+MOD函數(shù)的組合公式就可以實現(xiàn) 。
身份證號碼的倒數(shù)第二位表示性別，男性為奇數(shù)，女性為偶數(shù) 。
根據(jù)這個規(guī)則，D3單元格公式：
=TEXT(MOD(MID(B3,17,1),2),"男;;女")

文章插圖
公式講解：
（1）用MID函數(shù)提取18位身份證號碼中的第17位，MID(B2,17,1)；
（2）用MOD函數(shù)判斷奇偶，MOD函數(shù)有兩個參數(shù)，格式為：MOD（被除數(shù)，除數(shù)），結果是余數(shù)，本例中被除數(shù)是身份證號碼的第17位數(shù)字，除數(shù)是2 ，當被除數(shù)是偶數(shù)時，余數(shù)為零，反之余數(shù)為1，利用TEXT的四段分類顯示規(guī)則"正;負;零;文本"，將正數(shù)定義為“男”，零定義為“女”，從而計算出性別。

文章插圖

文章插圖
8、短日期轉換為長日期格式。
公式：=TEXT(B3,"yyyy年mm月dd日") 。

文章插圖
公式講解: 日期中年格式為yyyy（顯示四位年份），月格式為mm（顯示前導 0），日格式為 dd（顯示前導 0）
9、設置盈虧平衡判斷
TEXT函數(shù)可以作為三種條件的結果判斷的函數(shù)來使用，將滿足條件的數(shù)值轉化為指定的格式。以判斷公司經營的盈虧情況為例，利潤為正則顯示為盈，負數(shù)則為虧，0則顯示為平。
公式：D2=TEXT(C2,"[>0]盈;[<0]虧;平")

文章插圖
公式講解：：TEXT函數(shù)可以將數(shù)據(jù)分為正數(shù)、負數(shù)、零和文本四種類型來分別指定顯示方式，類型之間使用分號隔開，標準格式為"正;負;零;文本" 。數(shù)字格式支持兩次條件判斷，即[條件1]格式1;[條件2]格式2;格式3，參數(shù)②"[>0]盈;[<0]虧;平"就是這種用法的一個實例。它對C列數(shù)值先進行條件1判斷，如果大于0，則顯示“盈”，如不大于0，則進行條件2判斷，如果小于0，則顯示“虧”；如前兩個條件都不滿足，則顯示為“平” 。
10、固定數(shù)字位數(shù)
以發(fā)票號碼為例，發(fā)票號碼均為8位數(shù) ，但有時系統(tǒng)導出來的發(fā)票號碼將其前面的0省略了，這時如何將0批量自動補齊呢？通過將0作為占位符，用TEXT函數(shù)可以完成。
公式為：=TEXT(A3,"00000000") 。

文章插圖
公式講解：（1）參數(shù)②為“00000000”，省略了負數(shù)、零值和文本的格式，這表示該格式對所有數(shù)值適用，但對文本不適用。
(2)此處的0在TEXT中是數(shù)字占位符，一個0就代表一個數(shù)位,表示該數(shù)位如有有效數(shù)值，則取有效數(shù)值，否則以0填充該數(shù)位。以A3單元格為例，個位到萬位都有有效數(shù)值，所以這部分數(shù)值保持不變；前三位數(shù)沒有有效數(shù)值，則用0填充，于是83880就變成了00083880 。
四、text函數(shù)使用的注意點
在Excel函數(shù)中如果是文本，通過加引號把它變成字符，且引號為英文狀態(tài)下輸入。

文章插圖