Excel表格格式化文字函式數怎麼使用

2023-08-24 10:00:42

TEXT函式是一個超級好用的格式化文字函式，今天韓老師詳細給大家講來。

函式基礎

功能：

TEXT 函式可通過格式程式碼對數字應用格式，從而更改數字的顯示方式。

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

語法

TEXT(數值,格式程式碼)

Value：數值，或是計算結果為數字值的公式，也或對包含數字值的單元格的引用。
Format_text：文字形式的數字格式。

　　text返回的一律都是文字形式的資料。如果需要計算，可以先將文字轉換為數值，然後再計算。

文字型數值遇到四則運算會自動轉為數值。

但文字會不參與sum之類的函式運算。

分類應用

TEXT 函式主要是通過格式程式碼來應用格式的。

今天，韓老師來講TEXT函式格式日期與時間的用法：

1、格式日期

先看下圖，TEXT函式通過不同的格式程式碼，轉換日期格式的結果：

（格式程式碼，即是TEXT公式的第二個引數，下圖公式中“”內的部分）

公式中的程式碼及其含義：

程式碼	含義
m	將月顯示為不帶前導零的數字。
mm	根據需要將月顯示為帶前導零的數字。
mmm	將月顯示為縮寫形式（Jan 到 Dec）。
mmmm	將月顯示為完整名稱（January 到 December）。
d	將日顯示為不帶前導零的數字。
dd	根據需要將日顯示為帶前導零的數字。
ddd	將日顯示為縮寫形式（Sun 到 Sat）。
dddd	將日顯示為完整名稱（Sunday 到 Saturday）。
yy	將年顯示為兩位數字。
yyyy	將年顯示為四位數字。

另：阿拉伯數字與中文數字轉換時：

格式引數為"[dbnum1]"：普通的大寫，如“七百八十九”；
格式引數為"[dbnum2]"：財務專用大寫，如“柒佰捌拾玖”；
格式引數為"[dbnum3]"：阿拉伯數字之間加單位，如“７百８十９”；但用"[dbnum3]"轉成的數字是全形，所與如果轉換成普通的半形，TEXT函式之外要套用ASC函式。

2、格式時間

公式中的程式碼及其含義：

程式碼	含義
h	將小時顯示為不帶前導零的數字。
[h]	以小時為單位顯示經過的時間。如果使用了公式，該公式返回小時數超過 24 的時間，請使用類似於 [h]:mm:ss 的數字格式。
hh	根據需要將小時顯示為帶前導零的數字。如果格式含有 AM 或 PM，則基於 12 小時制顯示小時；否則，基於 24 小時制顯示小時。
m	將分鐘顯示為不帶前導零的數字。
註釋 m 或 mm 程式碼必須緊跟在 h 或 hh 程式碼之後或緊跟在 ss 程式碼之前；否則，Excel 會顯示月份而不是分鐘。
[m]	以分鐘為單位顯示經過的時間。如果所用的公式返回的分鐘數超過 60，請使用類似於 [mm]:ss 的數字格式。
mm	根據需要將分鐘顯示為帶前導零的數字。
註釋 m 或 mm 程式碼必須緊跟在 h 或 hh 程式碼之後或緊跟在 ss 程式碼之前；否則，Excel 會顯示月份而不是分鐘。
s	將秒顯示為不帶前導零的數字。
[s]	以秒為單位顯示經過的時間。如果所用的公式返回的秒數超過 60，請使用類似於 [ss] 的數字格式。
ss	根據需要將秒顯示為帶前導零的數字。如果要顯示秒的小數部分，請使用類似於 h:mm:ss.00 的數字格式。
AM/PM、am/pm、A/P、a/p	基於 12 小時制顯示小時。時間介於午夜和中午之間時，Excel 會使用 AM、am、A 或 a 表示時間；時間介於中午和午夜之間時，Excel 會使用 PM、pm、P 或 p 表示時間。

3、千分位分隔符

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

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

公式中的程式碼及其含義：

程式碼	含義
"#,###"	只保留整數
"#,###.00"	保留兩位小數
"#,"	顯示為1,000的整倍數
"#,###.0,"	顯示為1,000的整倍數，且保留一位小數
"0.0,,"	顯示為1,000,000的整倍數，且保留一位小數

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

4、格式數字、貨幣

公式中的程式碼及其含義：

程式碼	含義
"0.00"	只保留整數
"#,##0"	千分位分隔符，只保留整數
"#,##0.00"	千分位分隔符，保留整數兩位小數
"$#,##0"	只保留整數
"$#,##0.00"	保留兩位小數
"$#,##0.00_);($#,##0.00)"	兩位小數，負數
"$ * #,##0"	只保留整數，$與數字間一個空字元
"$ * #,##0.00"	兩位小數，$與數字間一個空字元

5、加0前導符補充位數

6、百分比

7、特殊格式

8、條件區段判斷

8.1四個條件區段：

TEXT函式的格式程式碼預設分為4個條件區段，各區段之間用半形分號間隔。

預設情況下，這四個區段的定義為：

[>0];[<0];[=0];[文字]< p="">

【舉例1】按區段條件判斷，然後返回相應結果：

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

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

>0，保留兩位小數；
<0，只保留整數；< p="">
=0，返回0值；
文字，返回“文字”二字。

【舉例2】按區段條件，強制返回相應結果：

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

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

>0，返回100；
<0，返回50；< p="">
=0，返回0值；
文字，返回“文字”二字。

公式中使用的感嘆號（英文半形）是轉義字元，強制其後的第一個字元不具備程式碼的含義，而僅僅是數字。比如：1!0!0，將兩個0強制成數字0，而不是數字格式程式碼0。

在實際應用中，可以使用部分條件區段。

8.2三個條件區段：

三個區段為：

[>0];[<0];[=0]< p="">

【舉例3】

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

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

>0，返回“盈利”；
<0，返回“虧損”；< p="">
=0，返回“平衡”；

8.3兩個條件區段：

兩個區段的為：

[>0];[<0]< p="">

【舉例4】

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

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

>0，返回“盈利”；
<0，返回“虧損”；< p="">

一個區段的，就不講了，昨天前天的兩篇文章，都算是一個區段的。

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，返回“不合格”；< p="">
不滿足以上條件，返回成績的整數。
如果成績中有小數，最後一個區段可以寫成0.0，或0.00.

通過這個公式，把成績分段成了>=90,<60,60~89三個區段。< p="">

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

這一部分，對60~89的成績，減去60，然後計算：

如果>=10，返回“良好”；
否則，返回“合格”；