TEXT函數是一個超級好用的格式化文本函數,今天韓老師詳細給大家講來。
函數基礎
功能:
TEXT 函數可通過格式代碼對數字應用格式,從而更改數字的顯示方式。
如果要按更可讀的格式顯示數字,或者將數字與文本或符號組合,它將非常有用。
語法:
TEXT(數值,格式代碼)
- Value:數值,或是計算結果為數字值的公式,也或對包含數字值的單元格的引用。
- Format_text:文本形式的數字格式。
text返回的一律都是文本形式的數據。如果需要計算,可以先將文本轉換為數值,然後再計算。
文本型數值遇到四則運算會自動轉為數值。
但文本會不參與sum之類的函數運算。
分類應用
TEXT 函數主要是通過格式代碼來應用格式的。
今天,韓老師來講TEXT函數格式日期與時間的用法:
1、格式日期
先看下圖,TEXT函數通過不同的格式代碼,轉換日期格式的結果:
(格式代碼,即是TEXT公式的第二個參數,下圖公式中“”內的部分)
公式中的代碼及其含義:
另:阿拉伯數字與中文數字轉換時:
- 格式參數為"[dbnum1]":普通的大寫,如“七百八十九”;
- 格式參數為"[dbnum2]":財務專用大寫,如“柒佰捌拾玖”;
- 格式參數為"[dbnum3]":阿拉伯數字之間加單位,如“7百8十9”;但用"[dbnum3]"轉成的數字是全角,所與如果轉換成普通的半角,TEXT函數之外要套用ASC函數。
2、格式時間
公式中的代碼及其含義:
3、千分位分隔符
要將逗號顯示為千位分隔符或按倍數1,000 縮放數字。
,(逗號)在數字中顯示千位分隔符。如果格式中含有被數字符號(#) 或零包圍起來的逗號,Excel 會分隔千位。位佔位符後的逗號會以1,000 為單位計量數字。例如,如果format_text 參數為 "#,###.0,",Excel會將數字 12,200,000顯示為 12,200.0。
公式中的代碼及其含義:
4、格式數字、貨幣
公式中的代碼及其含義:
5、加0前導符補充位數
6、百分比
7、特殊格式
8、條件區段判斷8.1四個條件區段:
TEXT函數的格式代碼默認分為4個條件區段,各區段之間用半角分號間隔。
默認情況下,這四個區段的定義為:
[>0];[<0];[=0];[文本]
【舉例1】按區段條件判斷,然後返回相應結果:
公式:=TEXT(A2,"0.00;-0;0;文本")的含義是:
A2單元格的值,按照四種情況返回結果:
- >0,保留兩位小數;
- <0,只保留整數;
- =0,返回0值;
- 文本,返回“文本”二字。
【舉例2】按區段條件,強制返回相應結果:
公式:=TEXT(A8,"1!0!0;5!0;0;文本")的含義是:
A8單元格的值,按照四種情況返回結果:
- >0,返回100;
- <0,返回50;
- =0,返回0值;
- 文本,返回“文本”二字。
公式中使用的感嘆號(英文半角)是轉義字符,強制其後的第一個字符不具備代碼的含義,而僅僅是數字。比如:1!0!0,將兩個0強制成數字0,而不是數字格式代碼0。
在實際應用中,可以使用部分條件區段。
8.2三個條件區段:
三個區段為:
[>0];[<0];[=0]
【舉例3】
公式:=TEXT(A15,"盈利;虧損;平衡")的含義是:
A15單元格的值,按照三種情況返回結果:
- >0,返回“盈利”;
- <0,返回“虧損”;
- =0,返回“平衡”;
8.3兩個條件區段:
兩個區段的為:
[>0];[<0]
【舉例4】
公式:=TEXT(A22,"盈利;虧損")的含義是:
A22單元格的值,按照兩種情況返回結果:
- >0,返回“盈利”;
- <0,返回“虧損”;
一個區段的,就不講了,昨天前天的兩篇文章,都算是一個區段的。
9、自定義條件區段
TEXT函數除了可以使用默認區段以外,還可以自定義條件區段。
9.1四個自定義條件區段:
四個區段的定義為:
[條件1];[條件2];[不滿足條件的其他部分];[文本]
【舉例5】
公式:=TEXT(A38,"[>=85]優秀;[>=60]合格;不合格;無成績")的含義是:
A38單元格的值,按照自定義的四種情況返回結果:
- >=85,返回“優秀”;
- >=60,返回“合格”;
- 不滿足以上條件的數值,返回“不合格”;
- 非數值,返回“文本”二字。
9.2三個自定義條件區段:
三個區段的定義為:
[條件1];[條件2];[不滿足條件的其他部分]
【舉例6】
公式:=TEXT(A46,"[>=85]優秀;[>=60]合格;不合格")的含義是:
A46單元格的值,按照自定義的四種情況返回結果:
- >=85,返回“優秀”;
- >=60,返回“合格”;
- 不滿足以上條件,返回“不合格”;
9.3兩個自定義條件區段:
兩個區段的定義為:
[條件];[不滿足條件的其他部分]
【舉例7】
公式:=TEXT(A54,"[>=60]合格;不合格")的含義是:
A54單元格的值,按照自定義的四種情況返回結果:
- >=60,返回“合格”;
- 不滿足以上條件,返回“不合格”;
10、巧用TEXT嵌套自定義多條件區段以上舉例中,我們可以看到,成績只能判斷到“優秀、合格、不合格”級別,如果再多級別,一個TEXT就解決不了了。TEXT函數也可以嵌套解決這個問題:
【舉例8】
要求:
- 90分及以上,返回“優秀”;
- 70分及以上,返回“良好”;
- 60分及以上,返回“合格”;
- 60分以下,返回“不合格”。
結果如下:
公式:TEXT(TEXT(A62-60,"[>=30]優秀;不合格;0"),"[>=10]良好;合格"),分解來解釋:
TEXT(A62-60,"[>=30]優秀;不合格;0")
對A62-60進行分段計算:
- 如果>=30,返回“優秀”;
- 如果<0,返回“不合格”;
- 不滿足以上條件,返回成績的整數。
- 如果成績中有小數,最後一個區段可以寫成0.0,或0.00.
通過這個公式,把成績分段成了>=90,<60,60~89三個區段。
TEXT(TEXT(A62-60,"[>=30]優秀;不合格;0"),"[>=10]良好;合格")
這一部分,對60~89的成績,減去60,然後計算:
- 如果>=10,返回“良好”;
- 否則,返回“合格”;
閱讀更多 韓老師講office 的文章