Excel Text函数用法的18个实例，含文本日期、条件范围格式与数组{0,1}/{1,

在 Excel 中，Text函数用于把数值或日期按指定格式转换为文本。当把数值转为文本时，可保留小数或取整，加上千位分隔符、货币符号和百分号，还可把数值用科学记数法表示；当把日期时间转为文本时，可定义年月日时分秒显示几位，也可用相应的英文单词或其缩写表示。

在Text函数的格式中，可以带条件，并且既可带一个条件又可带多个条件。另外，在数值和格式参数中都可以使用数组，例如在数值使用 {0,1}、{1,-1}、{-1,1} 等，这种情况常与 Value 和 VlookUp函数组合使用查找指定值。

1、表达式：TEXT(Value, Format_Text)

中文表达式：TEXT(数值, 格式)

2、说明：

（1）小数位和整数位的格式

A、占位符 0 与 # 的区别（一个保留 0，另一个舍弃 0）。当保留指定小数位数（如保留两位小数）时，如果格式中小数点右边为 0，例如 #.00，当数值没有两位小数时，在末尾会显示 0，如 5.8 保留两位小数变为 5.80；如果格式中小数点右边为 #，例如 #.##（或 0.##），当数值没有两位小数时，在末尾不会显示 0，如 5.8 保留两位小数变为 5.8。

B、占位符 ? 用于补空格。如果要求两个数位不同的小数的小数点对齐，可以使用 ? 补空格；例如要求 5.8 与 68.48 的小数点对齐，可以把格式定义为 0.0?。

C、小数点左边的 0 不显示格式的定义。如果要求小数点左边的 0 不显示，可以把格式定义为 #.00，例如 0.25 会变为 .25。

（2）千位分隔符格式

千位分隔符共有三种格式，第一种为 #,###，表示每三位加一个千位分隔符（逗号）；第二种为“#,”，表示省略千位分隔符后的数字；第三种为“0.0,”，表示右起第一个千位分隔符后的数字用小数表示并四舍五入。

（3）日期时间格式

A、日期中年的格式有两种，一种为 yy（仅显示年份后两位），另一种为 yyyy（显示四位年份）。日期中月格式共有五种，一种为 m（省略前导 0），另一种为 mm（显示前导 0），还有三种为用月份的英文单词或其缩写表示。日期中日格式共有四种，一种为 d（省略前导 0），另一种为 dd（显示前导 0），还有两种为用周一到周日的英文单词或其缩写表示。

B、时分秒的格式都有三种，并且格式表示方法也一样；例如：小时的格式分别为 h（省略前导 0）、[h]（返回小时数超过 24 的时间） 和 hh（显示前导 0）。

（4）货币符号格式

如果要把货币符号显示到数字前，可以在格式中添加相应的货币符号；例如：在要数字前显示元（¥），可以把格式定义为“¥#.00”；¥ 可以用快捷键 Alt + 0165（小键盘上的数字）输入，具体输入方法及其它货币符号的输入方法，请看下文的实例。

（5）百分号格式

数字如果要用百分号（%）表示，可以在格式中加百分号；例如把格式定义为 0.00% 或 0%。

（6）科学记数法格式

科学记数法的格式可以为“0.0E + 0”、“0.0E + 00”或“#.0E + 0”，E（或 e）表示以 10 为底，它右边的数值表示小数点往左移动的位数。

（一）用占位符 0 和 # 保留两位小数的区别实例

1、双击 B1 单元格，把公式 =TEXT(A1,"0.00") 复制到 B1，按回车，返回保留两位小数的结果 15.85；再把公式 =TEXT(A1,"#.##") 复制到 B2，按回车，同样返回 15.85；双击 A1，把 15.846 改为 15.8，单击 B1，B1 中的数值变为 15.80，B2 中的数值变为 15.8；操作过程步骤，如图1所示：

图1

2、公式说明：

A、公式 =TEXT(A1,"0.00") 中，A1 为要保留两位小数的文本，0.00 为格式，公式的意思是：把 A1 中数值保留两位小数。

B、公式 =TEXT(A1,"#.##") 的格式为 #.##，也是把 A1 中的数值保留两位小数；它与格式 0.00 的异同点为：当小数点后有两位数时，它们都保留两位；当小数点后只有一个位数时，格式 0.00 会补 0 ，而格式 #.## 会省略 0。

（二）用占位符 ? 补空格的实例

1、假如要使数字位数不同的小数 3.8 与 23.85 的小数点对齐。双击 A1 单元格，把公式 =TEXT(A1,"0.0?") 复制到 A1，按回车，返回 3.8；双击 B2，把公式 =TEXT(A2,"0.0?") 复制到 B2，按回车，返回 23.35，并且 B1 与 B2 中的数值中的小数点对齐；操作过程步骤，如图2所示：

图2

2、公式说明：

A、公式 =TEXT(A1,"0.0?") 与 =TEXT(A2,"0.0?") 中的格式同为 0.0?，格式中的半角问号（?）表示补空格，即在 A1 的 3.8 前后分别补一个空格以与 A2 的 23.85 同数位，从而实现小数点对齐。

（三）不显示小数点左边 0 的实例

1、双击 B1 单元格，把公式 =TEXT(A1,"#.00") 复制到 B1，按回车，返回 .38；双击 A1，把 0.38 改为 2.38，B1 中的数值变为 2.38；操作过程步骤，如图3所示：

图3

2、公式说明：

从演示可知，当小数点左边的数小于 1 时，格式 #.00 返回省略小数点左边 0 的结果；当小数点左边的值大于等于 1，格式返回保留小数点左边数值的结果。

（四）小数显示为分数的实例

1、假如要把 2.5 显示为分数。双击 B1 单元格，把公式 =TEXT(A1,"# 0/0") 复制到 B1，按回车，返回 2 1/2；再次双击 B1，把格式 "# 0/0" 改为 "# ?/?"，按回车，同样返回 2 1/2；操作过程步骤，如图4所示：

图4

2、公式说明：

当把小数显示为分数时，可以用格式 "# 0/0" 或 "# ?/?"，也就是分子与分母既可以用 0 又可以用 ?。另外，如果分子或分母有多位，可以用多个 0（或 ?），例如把 2.334 显示为分数，可以用格式 "# ???/???"。

（五）数值显示为千位分隔符形式的实例

1、假如要给 2380000 加上分隔逗号。双击 B1 单元格，把公式 =TEXT(A1,"#,###") 复制到 B1，按回车，返回 2,380,000；双击 B1，把格式 "#,###" 改为 "#,"，按回车，返回 2380；双击 B1，把 "#," 改为 "#.#,"，按回车，返回 2380.，双击 B1，把 "#.#," 改为 "#.#,,"，按回车，返回 2.4；再次双击 B1，把 "#.#,," 改为 "0.0,,"，同样返回 2.4；操作过程步骤，如图5所示：

图5

2、公式说明：

A、格式 "#,###" 表示从数值的个位起每隔三位显示一个千位分隔符（逗 ,）；格式 "#," 表示省略个位到百位的数字并进行四舍五入，如 2380505 会变为 2381。

B、格式 "#.#," 表示省略个位到百位并四舍五入保留一位小数；如演示中的 2380000 变为 2380.（小数点右边为 # 会省略 0，上面已经介绍过）；格式 "#.#,," 表示省略个位到十万位的数值并四舍五入保留一位小数，如演示中的 2380000 变为 2.4；格式 "0.0,," 与 "#.#,," 相同。

（六）日期格式的实例

1、双击 B1 单元格，把公式 =TEXT(A1,"yy-m-d") 复制到 B1，按回车，返回 19-1-25；双击 B2，把公式 =TEXT(A1,"yyyy-mm-dd") 复制到 B2，按回车，返回 2019-01-25；双击 B3，把公式 =TEXT(A1,"yyyy-mmm-ddd") 复制到 B3，按回车，返回 2019-Jan-Fri；双击 B4，把公式 =TEXT(A1,"yyyy-mmmm-dddd") 复制到 B4，按回车，返回 2019-January-Friday；操作过程步骤，如图6所示：

图6

2、公式说明：

A、格式 "yy-m-d" 中，yy 表示年显示两位，m 和 d 表示月和日都显示一位；"yyyy-mm-dd" 中，yyyy 表示年显示四位，mm 和 dd 表示月和日都显示两位，若为单个数字，则用 0 补充。

B、格式 "yyyy-mmm-ddd" 中，mmm 表示月用月份的英文单词缩写显示，ddd 表示日用周一至周日的英文单词缩写显示，如演示中返回 2019-Jan-Fri，Jan 是一月英文单词的缩写，Fri 是星期五英文单词的缩写。

C、格式 "yyyy-mmmm-dddd" 中，mmmm 表示月用月份的英文单词显示，日用周一至周日的英文单词显示，如演示中返回 2019-January-Friday，January 是一月的英文单词，Friday 是星期五英文单词。

3、如果要在日期中显示中文的年月日，公式可以这样写：=TEXT(A1,"yyyy年mm月dd日")，也就是说日期格式中的间隔符（如 -）可以自定义。

（七）时间格式的实例

1、双击 B1 单元格，把公式 =TEXT(A1,"h:m:s") 复制到 B1，按回车，返回 15:6:9；双击 B2，把 =TEXT(A1,"hh:mm:ss") 复制到 B2，按回车，返回 15:06:09；双击 A4，输入 23:66，双击 B4，把公式 =TEXT(A4,"[h]:mm") 复制到 B4，按回车，返回 24:06；双击 B5，把公式 =TEXT(A5,"[m]:ss") 复制到 B5，按回车，返回 119:06；双击 B6，把公式 =TEXT(A6,"[s].00") 复制到 B6，按回车，返回 7146.50；操作过程步骤，如图7所示：

图7

2、公式说明：

A、格式 "h:m:s" 表示时分秒只显示一位；"hh:mm:ss" 表示时分秒都显示两位，若只有一位，则用 0 补充。

B、格式 "[h]:mm" 中的 [h] 表示以小时为单位显示时间，它能返回小时数超过 24 的时间，如演示中的 23:66（23时66分）返回 24:06（24小时06分）；"[m]:ss" 中的 [m] 表示以分钟为单位显示时间，它能返回分钟数超过 60 的时间，如演示中的 1:59:06 返回 119:06（119分钟06分）；"[s].00" 中的 [s] 表示以秒为单位显示时间，它能返回秒数超过 60 的时间，如演示中的 01:59:06.5 返回 7146.50（7146秒50毫秒）。

3、如果时间要用上午（AM）或下午（PM）表示，公式可以这样写：=TEXT(A7,"hh:mm AM/PM")。

（八）在数值前添加货币符号的实例

1、假如要给“价格”列添加元符号（¥）。双击 E2 单元格，输入公式 =TEXT(C2,"¥0.0")，按回车，返回 ¥5.0；选中 E2，把鼠标移到 E2 右下角的单元格填充柄上，鼠标变为加号后，双击左键，则剩余价格也都加上 ¥；操作过程步骤，如图8所示：

图8

2、公式说明：

A、元符号（¥）的输入方法，按住 Alt，再按小键盘上的 0165，输完数字后放开 Alt；注意：一定要关闭中文输入法，且不是按 Shift 关闭，而是把中文输入法退出，如右键中文输入法，然后选择“退出或关闭”；否则将无法输入 ¥。

B、其它货币符号的输入快捷键为：美元 $（Shift + 4），美分 ¢（Alt + 0162），英镑 £（Alt + 0163），欧元 €（Alt + 0128）；需要按住 Alt 的，数字都要从小键盘输入，输入方法输入元符号（¥）一样。

（九）显示百分号（%）的实例

1、双击 B1 单元格，把公式 =TEXT(A1,"0.00%") 复制到 B1，按回车，返回 36.54%；选中 B1，把鼠标移到 B1 右下角的单元格填充柄上，鼠标变为加号后，双击左键，则剩余数值也加上 %；操作过程步骤，如图9所示：

图9

2、公式说明：

公式 =TEXT(A1,"0.00%") 中格式为 "0.00%"，意思是把数值保留两位小数且加上百分号；从演示可知，无论是小数还是整数都扩大了 100 倍并加上百分号且都保留了两位小数。

（十）用科学记数法表示的实例

1、双击 B1 单元格，把公式 =TEXT(A1,"0.0E+0") 复制到 B1，按回车，返回 5.8E+9；双击 B2，把同一公式复制到 B2，在 0.0E+0 后输入一个 0，按回车，返回 5.8E+09；双击 B3，把公式 =TEXT(A3,"0.0E+0") 复制到 B3，按回车，返回 5.7E+8；双击 B4，把 B3 中的公式 复制到 B4，再把小数点前的 0 改为 #，按回车，也返回5.7E+8；操作过程步骤，如图10所示：

图10

2、公式说明：

A、公式 =TEXT(A1,"0.0E+0") 与 =TEXT(A1,"0.0E+00") 区别在于格式中加号（+）后少一个 0 与多一个 0，其实就是定义指数不足两位时是否显示前导 0。

B、公式 =TEXT(A3,"0.0E+0") 与 =TEXT(A3,"#.0E+0") 返回一样的结果，而它们的区别为：前者小数点前用 0 表示，后者小数点前用 # 表示，说明格式中，小数前既可用 0 又可用 #。

C、从对 A1 和 A3 返回的结果可知，在用科学记数法表示时，会自动四舍五入。

（一）用占位符 # 和 * 把数字转为文本并取整

1、假如要把销量转文本并取整。双击 E2 单元格，把公式 =TEXT(D2,"#*,") 复制到 E2，按回车，返回 2686；用双击单元格填充柄的方法把其它数值转为文本并取整；操作过程步骤，如图11所示：

图11

2、公式 =TEXT(D2,"#*,") 说明：

格式 "#*," 中的 # 表示数字，* 表示任意多个字符，“,”是千位分隔符，"#*," 表示把所有数字转为文本且仅保留整数并进行四舍五入。

（二）格式带条件

1、假如要求把销量大于 0 的显示销量，销量等于 0 或为空的显示 0。双击 E2 单元格，把公式 =TEXT(D2,"[>"&$D$4&"]0") 复制到 E2，按回车，返回 892；用双击单元格填充柄的方法返回剩余的结果；再双击 F2，把同一公式复制到 F2，然后在格式后输入“;零”，按回，也返回 892，把鼠标移到 F2 右下角的单元格填充柄上，鼠标变为加号后，按住左键并往下拖，则返回剩余单元格的结果；操作过程步骤，如图12所示：

图12

2、公式 =TEXT(D2,"[>"&$D$4&"]0") 说明：

A、$D$4 为 0，$ 表示对列和行的绝对引用，以确保往下拖时，D4 不会变为 D5、D6 等。

B、则格式 "[>"&D4&"]0" 变为 "[>0]0"，[>0] 为条件，0 为满足条件时显示的值，且格式中的 0 为占位符而不是指 0 本身。

C、则公式变为 =TEXT(D2,"[>0]0")，意思是，如果 D2 大于 0，显示占位符 0（即 D2），否则默认显示 0（若再定义一个值，则显示该值），这一点可以从公式 =TEXT(D2,"[>"&$D$4&"]0;零") 得到印证，当公式在 F2 时， D2 大于 0，它返回 D2 中的值 892；当公式在 F4 时，D4 为 0，它返回“零”。

D、另外，格式 "[>0]!0" 在后一个 0 前加 ! 后，意思恰好与 "[>0]0" 相反，意思是，如果 D2 大于 0，不显占位符 0，而显示 0。

（三）两种正负数、0、空单元格与文本格式的比较

1、假如要求正数保留一位小数、负数显空、0 和空单元格显示 0、文本显示 0 或其本身。双击 B2 单元格，把公式 =TEXT(A2,"0.0;;0;!0") 复制到 B2，按回车，返回 2.0；选中 B2, 把鼠标移到 B2 右下角的单元格填充柄上，鼠标变粗体红色加号后，双击左键，则返回剩余单元格的结果；双击 C2，把同一公式复制到 C2，把公式中“;!0" ”删除，按回车，同样返回 2.0，也用往下拖的方法返回其它结果；操作过程步骤，如图13所示：

图13

2、公式说明：

A、格式 "0.0;;0;!0" 共定义的四种格式，第一种“0.0;”表示把正数保留一位小数；第二种“;”表示把负数显示为空文本；第三种“0;”表示把 0 和空单元格返回 0；第四种“!0”表示把文本转为 0，这一点从公式 =TEXT(A7,"0.0;;0;!0") 与 =TEXT(A7,"0.0;;0") 对 A7 的返回值可知，有“!0”的返回 0，无“!0”返回 excel。

B、另外，如果数值中没有文本，可以不用 “!0”，直接用公式 =TEXT(A7,"0.0;;0") 即可。

（四）条件范围格式

1、假如要求销量大于等于 3000 与小于 2000 的显示数值，其它的显示空文本。双击 E2 单元格，把公式 =TEXT(D2,"[>=3000]0;[=3000]高;[=3000]0;[=3000]高;[=3000]0;[