TEXT函數(shù)是一個(gè)超級好用的格式化文本函數(shù)，今天韓老師詳細(xì)給大家講來。

函數(shù)基礎(chǔ)

功能：

TEXT 函數(shù)可通過格式代碼對數(shù)字應(yīng)用格式，從而更改數(shù)字的顯示方式。

如果要按更可讀的格式顯示數(shù)字，或者將數(shù)字與文本或符號組合，它將非常有用。

語法

TEXT(數(shù)值,格式代碼)

• Value：數(shù)值，或是計(jì)算結(jié)果為數(shù)字值的公式，也或?qū)Π瑪?shù)字值的單元格的引用。

• Format_text：文本形式的數(shù)字格式。

text返回的一律都是文本形式的數(shù)據(jù)。如果需要計(jì)算，可以先將文本轉(zhuǎn)換為數(shù)值，然后再計(jì)算。

文本型數(shù)值遇到四則運(yùn)算會(huì)自動(dòng)轉(zhuǎn)為數(shù)值。

但文本會(huì)不參與sum之類的函數(shù)運(yùn)算。

分類應(yīng)用

TEXT 函數(shù)主要是通過格式代碼來應(yīng)用格式的。

今天，韓老師來講TEXT函數(shù)格式日期與時(shí)間的用法：

1、格式日期

先看下圖，TEXT函數(shù)通過不同的格式代碼，轉(zhuǎn)換日期格式的結(jié)果：

（格式代碼，即是TEXT公式的第二個(gè)參數(shù)，下圖公式中“”內(nèi)的部分）

公式中的代碼及其含義：

另：阿拉伯?dāng)?shù)字與中文數(shù)字轉(zhuǎn)換時(shí)：

• 格式參數(shù)為'[dbnum1]'：普通的大寫，如“七百八十九”；

• 格式參數(shù)為'[dbnum2]'：財(cái)務(wù)專用大寫，如“柒佰捌拾玖”；

• 格式參數(shù)為'[dbnum3]'：阿拉伯?dāng)?shù)字之間加單位，如“７百８十９”；但用'[dbnum3]'轉(zhuǎn)成的數(shù)字是全角，所與如果轉(zhuǎn)換成普通的半角，TEXT函數(shù)之外要套用ASC函數(shù)。

2、格式時(shí)間

公式中的代碼及其含義：

3、千分位分隔符

要將逗號顯示為千位分隔符或按倍數(shù)1,000 縮放數(shù)字。

,（逗號）在數(shù)字中顯示千位分隔符。如果格式中含有被數(shù)字符號(#) 或零包圍起來的逗號，Excel 會(huì)分隔千位。位占位符后的逗號會(huì)以1,000 為單位計(jì)量數(shù)字。例如，如果format_text 參數(shù)為 '#,###.0,'，Excel會(huì)將數(shù)字 12,200,000顯示為 12,200.0。

公式中的代碼及其含義：

其中：# 只顯示有意義的數(shù)字而不顯示無意義的零。

4、格式數(shù)字、貨幣

公式中的代碼及其含義：

5、加0前導(dǎo)符補(bǔ)充位數(shù)

6、百分比

7、特殊格式

8、條件區(qū)段判斷

8.1四個(gè)條件區(qū)段：

TEXT函數(shù)的格式代碼默認(rèn)分為4個(gè)條件區(qū)段，各區(qū)段之間用半角分號間隔。

默認(rèn)情況下，這四個(gè)區(qū)段的定義為：

[>0];[<>

【舉例1】按區(qū)段條件判斷，然后返回相應(yīng)結(jié)果：

公式：=TEXT(A2,'0.00;-0;0;文本')的含義是：

A2單元格的值，按照四種情況返回結(jié)果：

• >0，保留兩位小數(shù)；

• <>

• =0，返回0值；

• 文本，返回“文本”二字。

【舉例2】按區(qū)段條件，強(qiáng)制返回相應(yīng)結(jié)果：

公式：=TEXT(A8,'1!0!0;5!0;0;文本')的含義是：

A8單元格的值，按照四種情況返回結(jié)果：

• >0，返回100；

• <>

• =0，返回0值；

• 文本，返回“文本”二字。

公式中使用的感嘆號（英文半角）是轉(zhuǎn)義字符，強(qiáng)制其后的第一個(gè)字符不具備代碼的含義，而僅僅是數(shù)字。比如：1!0!0，將兩個(gè)0強(qiáng)制成數(shù)字0，而不是數(shù)字格式代碼0。

在實(shí)際應(yīng)用中，可以使用部分條件區(qū)段。

8.2三個(gè)條件區(qū)段：

三個(gè)區(qū)段為：

[>0];[<>

【舉例3】

公式：=TEXT(A15,'盈利;虧損;平衡')的含義是：

A15單元格的值，按照三種情況返回結(jié)果：

• >0，返回“盈利”；

• <>

• =0，返回“平衡”；

8.3兩個(gè)條件區(qū)段：

兩個(gè)區(qū)段的為：

[>0];[<>

【舉例4】

公式：=TEXT(A22,'盈利;虧損')的含義是：

A22單元格的值，按照兩種情況返回結(jié)果：

• >0，返回“盈利”；

• <>

一個(gè)區(qū)段的，就不講了，昨天前天的兩篇文章，都算是一個(gè)區(qū)段的。

9、自定義條件區(qū)段

TEXT函數(shù)除了可以使用默認(rèn)區(qū)段以外，還可以自定義條件區(qū)段。

9.1四個(gè)自定義條件區(qū)段：

四個(gè)區(qū)段的定義為：

[條件1];[條件2];[不滿足條件的其他部分];[文本]

【舉例5】

公式：=TEXT(A38,'[>=85]優(yōu)秀;[>=60]合格;不合格;無成績')的含義是：

A38單元格的值，按照自定義的四種情況返回結(jié)果：

• >=85，返回“優(yōu)秀”；

• >=60，返回“合格”；

• 不滿足以上條件的數(shù)值，返回“不合格”；

• 非數(shù)值，返回“文本”二字。

9.2三個(gè)自定義條件區(qū)段：

三個(gè)區(qū)段的定義為：

[條件1];[條件2];[不滿足條件的其他部分]

【舉例6】

公式：=TEXT(A46,'[>=85]優(yōu)秀;[>=60]合格;不合格')的含義是：

A46單元格的值，按照自定義的四種情況返回結(jié)果：

• >=85，返回“優(yōu)秀”；

• >=60，返回“合格”；

• 不滿足以上條件，返回“不合格”；

9.3兩個(gè)自定義條件區(qū)段：

兩個(gè)區(qū)段的定義為：

[條件];[不滿足條件的其他部分]

【舉例7】

公式：=TEXT(A54,'[>=60]合格;不合格')的含義是：

A54單元格的值，按照自定義的四種情況返回結(jié)果：

• >=60，返回“合格”；

• 不滿足以上條件，返回“不合格”；

10、巧用TEXT嵌套自定義多條件區(qū)段

以上舉例中，我們可以看到，成績只能判斷到“優(yōu)秀、合格、不合格”級別，如果再多級別，一個(gè)TEXT就解決不了了。TEXT函數(shù)也可以嵌套解決這個(gè)問題：

【舉例8】

要求：

• 90分及以上，返回“優(yōu)秀”；

• 70分及以上，返回“良好”；

• 60分及以上，返回“合格”；

• 60分以下，返回“不合格”。

結(jié)果如下：

公式：TEXT(TEXT(A62-60,'[>=30]優(yōu)秀;不合格;0'),'[>=10]良好;合格')，分解來解釋：

TEXT(A62-60,'[>=30]優(yōu)秀;不合格;0')

對A62-60進(jìn)行分段計(jì)算：

• 如果>=30，返回“優(yōu)秀”；

• 如果<>

• 不滿足以上條件，返回成績的整數(shù)。

  如果成績中有小數(shù)，最后一個(gè)區(qū)段可以寫成0.0，或0.00.

通過這個(gè)公式，把成績分段成了>=90,<>

TEXT(TEXT(A62-60,'[>=30]優(yōu)秀;不合格;0'),'[>=10]良好;合格')

這一部分，對60~89的成績，減去60，然后計(jì)算：

• 如果>=10，返回“良好”；

• 否則，返回“合格”；