TEXT函数

一、TEXT()函数基础

1. 函数语法

=TEXT(value, format_text)

2. 参数详解

参数	说明	必需
value	要格式化的数值、日期或文本	是
format_text	格式代码，用引号括起来	是

3. 重要特性

• 返回文本类型，不能直接用于计算
• 格式化效果仅显示，不改变原始值
• 格式代码区分大小写
• 可以自定义各种显示格式

---

二、详细示例数据表

表1：销售数据表

订单ID	销售日期	销售额	利润率	完成百分比
001	2024-01-15	158000	0.25	0.85
002	2024-02-20	89250	0.18	1.00
003	2024-03-10	1250000	0.32	0.75
004	2024-04-05	75600	0.15	0.90
005	2024-05-18	2345000	0.28	0.60
006	2024-06-22	189000	0.22	1.00
007	2024-07-30	567800	0.19	0.80
008	2024-08-12	1234500	0.35	0.95

表2：员工信息表

工号	姓名	入职日期	基本工资	手机号	身份证号
E001	张明	2018-03-15	8500	13800138001	110101199003075678
E002	李华	2019-07-20	9200	13912345678	310104199105128912
E003	王芳	2020-01-10	7800	13678901234	440305199208153456
E004	赵强	2021-05-18	10500	13755556666	330106198512204321
E005	孙丽	2022-11-30	6800	13512341234	420111199510087890

表3：产品信息表

产品ID	产品名称	成本价	售价	库存量	重量(kg)
P001	iPhone 15	4500	5999	150	0.171
P002	MacBook Pro	12000	14999	85	1.61
P003	华为P60	3800	4999	120	0.197
P004	小米电视	2800	3499	200	12.5
P005	索尼耳机	1200	1999	300	0.25
P006	戴尔笔记本	6500	7999	90	1.78

表4：考试分数表

学号	姓名	数学	语文	英语	总分	平均分
S001	张三	85	92	78	255	85.0
S002	李四	92	88	95	275	91.7
S003	王五	78	85	82	245	81.7
S004	赵六	65	72	68	205	68.3
S005	孙七	96	94	98	288	96.0

---

三、数字格式详解

1. 基本数字格式

示例1：千位分隔符

原始：158000
=TEXT(B2, "#,##0")           // 158,000
=TEXT(B2, "#,##0.00")        // 158,000.00

示例2：货币格式

=TEXT(B2, "¥#,##0")          // ¥158,000
=TEXT(B2, "$#,##0.00")       // $158,000.00
=TEXT(B2, "￥#,##0.00")      // ￥158,000.00

示例3：百分比格式

原始：0.25 (表1的利润率)
=TEXT(D2, "0%")             // 25%
=TEXT(D2, "0.0%")           // 25.0%
=TEXT(D2, "0.00%")          // 25.00%

示例4：小数位数控制

=TEXT(B2, "0")              // 158000
=TEXT(B2, "0.0")            // 158000.0
=TEXT(B2, "0.00")           // 158000.00
=TEXT(0.85, "0.0")          // 0.9 (四舍五入)
=TEXT(0.85, "0.00")         // 0.85

2. 特殊数字格式

示例5：科学计数法

=TEXT(1250000, "0.00E+00")  // 1.25E+06
=TEXT(0.000123, "0.00E-00") // 1.23E-04

示例6：分数格式

=TEXT(0.25, "# ?/?")        // 1/4
=TEXT(0.333, "# ??/??")     // 1/3
=TEXT(1.5, "# ?/?")         // 1 1/2

示例7：中文数字

=TEXT(158000, "[DBNum1]")   // 十五万八千
=TEXT(158000, "[DBNum2]")   // 壹拾伍万捌仟
=TEXT(158000, "[DBNum3]")   // 一十五万八千

示例8：正负号显示

=TEXT(100, "+#,##0;-0;0")   // +100
=TEXT(-100, "+#,##0;-0;0")  // -100
=TEXT(0, "+#,##0;-0;0")     // 0

---

四、日期时间格式详解

1. 基本日期格式

示例9：年月日格式

日期：2024-01-15
=TEXT(B2, "yyyy年mm月dd日")  // 2024年01月15日
=TEXT(B2, "yyyy-mm-dd")      // 2024-01-15
=TEXT(B2, "mm/dd/yyyy")      // 01/15/2024
=TEXT(B2, "dd-mmm-yy")       // 15-Jan-24

示例10：中文日期

=TEXT(B2, "aaaa")            // 星期一
=TEXT(B2, "aaa")             // 一
=TEXT(B2, "yyyy年m月d日")    // 2024年1月15日
=TEXT(B2, "m月d日")          // 1月15日

示例11：季度和星期

=TEXT(B2, "第q季度")         // 第1季度
=TEXT(B2, "dddd")            // Monday
=TEXT(B2, "ddd")             // Mon

2. 时间格式

示例12：时间格式化

时间：14:30:45
=TEXT(时间, "hh:mm:ss")      // 14:30:45
=TEXT(时间, "hh:mm AM/PM")   // 02:30 PM
=TEXT(时间, "h时mm分ss秒")   // 14时30分45秒

示例13：日期时间组合

=TEXT(NOW(), "yyyy-mm-dd hh:mm:ss")  // 2024-01-02 14:30:45
=TEXT(NOW(), "m/d/yyyy h:mm AM/PM")  // 1/2/2024 2:30 PM

---

五、文本格式与自定义格式

1. 文本连接与显示

示例14：在数字前后添加文本

销售额：158000
=TEXT(B2, "销售额：¥#,##0")        // 销售额：¥158,000
=TEXT(B2, "#,##0元")               // 158,000元
=TEXT(0.25, "利润率：#0%")         // 利润率：25%

示例15：条件格式文本

分数：85
=TEXT(C2, "[>=90]优秀;[>=60]及格;不及格")
解释：
- [>=90]优秀：大于等于90显示"优秀"
- [>=60]及格：大于等于60显示"及格"
- 不及格：其他情况显示"不及格"

示例16：手机号格式化

原始：13800138001
=TEXT(C2, "000-0000-0000")        // 138-0013-8001
=TEXT(C2, "(0000)0000-0000")      // (1380)0138-0001

示例17：身份证号格式化

原始：110101199003075678
=TEXT(D2, "000000-YYYYMMDD-0000") // 不适用，需要提取
正确做法：
=LEFT(D2,6)&"-"&MID(D2,7,8)&"-"&RIGHT(D2,4)
或者：
=TEXT(--MID(D2,7,8), "0000年00月00日") // 1990年03月07日

2. 零值处理

示例18：零值显示控制

=TEXT(0, "0.00;0.00;""-""")       // -
=TEXT(0, "#,##0;;""N/A""")        // N/A
=TEXT(0, "0.00;0.00;@")           // 0.00

---

六、实际应用场景

场景1：销售报表美化

示例19：创建格式化的销售报表

在销售数据表旁添加格式化列：

F列：格式化日期 =TEXT(B2, "yyyy年mm月dd日 ddd")
G列：格式化销售额 =TEXT(C2, "¥#,##0")
H列：格式化利润率 =TEXT(D2, "0.0%")
I列：完成状态 =TEXT(E2, "[=1]已完成;[<1]进行中")

结果： | 订单ID | 原日期 | 格式化日期 | 原销售额 | 格式化销售额 | 原利润率 | 格式化利润率 | 完成状态 | |--------|--------|------------|----------|--------------|----------|--------------|----------| | 001 | 2024-01-15 | 2024年01月15日 Mon | 158000 | ¥158,000 | 0.25 | 25.0% | 进行中 |

场景2：员工信息卡生成

示例20：生成员工信息卡片

员工信息卡：
="姓名：" & B2 & CHAR(10) &
"工号：" & A2 & CHAR(10) &
"入职日期：" & TEXT(C2, "yyyy年mm月dd日") & CHAR(10) &
"工龄：" & TEXT((TODAY()-C2)/365, "0年") & CHAR(10) &
"工资：" & TEXT(D2, "¥#,##0")

注意：CHAR(10)是换行符，需要设置单元格格式为"自动换行"

场景3：产品标签生成

示例21：生成产品价格标签

产品标签：
="【" & B2 & "】" & CHAR(10) &
"产品编号：" & A2 & CHAR(10) &
"成本价：" & TEXT(C2, "¥#,##0") & CHAR(10) &
"零售价：" & TEXT(D2, "¥#,##0") & CHAR(10) &
"毛利率：" & TEXT((D2-C2)/D2, "0.0%") & CHAR(10) &
"库存：" & TEXT(E2, "#,##0件") & CHAR(10) &
"重量：" & TEXT(F2, "0.0kg")

场景4：成绩单格式化

示例22：生成学生成绩报告

成绩报告：
=B2 & "同学成绩单" & CHAR(10) &
"数学：" & TEXT(C2, "0分") & " (" & TEXT(C2/100, "0.0%") & ")" & CHAR(10) &
"语文：" & TEXT(D2, "0分") & " (" & TEXT(D2/100, "0.0%") & ")" & CHAR(10) &
"英语：" & TEXT(E2, "0分") & " (" & TEXT(E2/100, "0.0%") & ")" & CHAR(10) &
"总分：" & TEXT(F2, "0分") & CHAR(10) &
"平均分：" & TEXT(G2, "0.0分") & CHAR(10) &
"评价：" & TEXT(G2, "[>=90]优秀;[>=80]良好;[>=60]及格;不及格")

---

七、TEXT函数高级技巧

技巧1：动态格式代码

示例23：根据数值大小动态格式化

销售额格式化：
=IF(C2>=1000000, TEXT(C2, "¥#,##0,, ""百万"""),
    IF(C2>=1000, TEXT(C2, "¥#,##0, ""千"""),
        TEXT(C2, "¥#,##0")))

技巧2：TEXT与其它函数结合

示例24：TEXT + VLOOKUP

查找并格式化：
=TEXT(VLOOKUP(A2, 产品表!A:C, 3, FALSE), "¥#,##0.00")

示例25：TEXT + SUMIF

汇总并格式化：
="总销售额：" & TEXT(SUMIF(销售表!B:B, "技术部", 销售表!C:C), "¥#,##0")

示例26：TEXT + DATE

计算日期并格式化：
=TEXT(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), "yyyy年mm月")
// 返回当前年月，如"2024年01月"

技巧3：多条件格式化

示例27：复杂的成绩等级

=G2 & "分，" & TEXT(G2, 
    "[>=95]等级A+;[>=90]等级A;[>=85]等级A-;
     [>=80]等级B+;[>=75]等级B;[>=70]等级B-;
     [>=60]等级C;等级D")

技巧4：创建自定义格式代码

示例28：电话号码分段

自定义分段：
=TEXT(A2, "000-0000-0000")  // 固定格式
或者
=LEFT(A2,3)&"-"&MID(A2,4,4)&"-"&RIGHT(A2,4)  // 更灵活

示例29：金额大写（人民币）

=TEXT(INT(C2), "[DBNum2]") & "元" & 
 TEXT(--RIGHT(TEXT(C2*100, "0"), 2), "[DBNum2]0角0分;;整")
注意：这是简化版，完整版需要更复杂的公式

---

八、常见错误与解决

错误1：返回#NAME?错误

原因：函数名拼写错误或缺少引号
错误：=TEXT(A2, yyyy-mm-dd)   // 格式代码缺少引号
正确：=TEXT(A2, "yyyy-mm-dd")

错误2：格式不生效

原因1：单元格格式为文本
解决：先设置为常规格式，再输入公式

原因2：值不是数值/日期
解决：确保value参数是数值或日期类型

错误3：TEXT结果不能计算

TEXT("100", "0") + 10   // 错误，因为TEXT返回文本
解决方法：
VALUE(TEXT("100", "0")) + 10   // 正确，VALUE转数值
或
--TEXT("100", "0") + 10        // 正确，--强制转数值

错误4：日期显示为数字

原因：Excel日期本质是序列数
解决：使用正确的日期格式代码
=TEXT(44927, "yyyy-mm-dd")   // 2024-01-02

错误5：自定义格式代码无效

常见错误：
1. 分号使用错误：正数;负数;零值;文本
2. 格式代码放在错误的位置
3. 特殊字符未正确转义

---

九、格式代码速查表

数字格式代码

代码	说明	示例
0	数字占位符（显示0）	0.00
#	数字占位符（不显示0）	#.##
.	小数点	0.00
,	千位分隔符	#,##0
%	百分比	0%
/	分数分隔符	# ?/?
E+	科学计数法	0.00E+00

日期时间代码

代码	说明	示例
yyyy	四位数年份	2024
yy	两位数年份	24
m	月份（不补零）	1
mm	月份（补零）	01
mmm	月份缩写	Jan
mmmm	月份全称	January
d	日（不补零）	5
dd	日（补零）	05
ddd	星期缩写	Mon
dddd	星期全称	Monday
h	小时（12小时制）	9
hh	小时（补零）	09
H	小时（24小时制）	21
HH	小时（补零）	21
m	分钟（时间中）	5
mm	分钟（补零）	05
s	秒	5
ss	秒（补零）	05
AM/PM	上午/下午	02:30 PM

文本和特殊代码

代码	说明	示例
@	文本占位符	@
*	重复字符	0*-
_	留空格	_0
"文本"	显示文本	"单位: "0
[颜色]	颜色代码	[红色]0
[条件]	条件格式	[>1000]0
[DBNum1]	中文小写	一百
[DBNum2]	中文大写	壹佰

---

十、综合练习

练习1：创建销售汇总报表

需求：将以下数据格式化成报表
原始数据：日期=2024-01-15, 金额=158000, 增长率=0.25

目标格式：
2024年第一季度销售报告
日期：2024年01月15日 星期一
销售额：¥158,000.00
增长率：+25.00%

公式：
="2024年第一季度销售报告" & CHAR(10) &
"日期：" & TEXT(A1, "yyyy年mm月dd日 aaaa") & CHAR(10) &
"销售额：" & TEXT(B1, "¥#,##0.00") & CHAR(10) &
"增长率：" & TEXT(C1, "+0.00%;-0.00%;0%")

练习2：生成员工工资条

数据：姓名=张明, 基本工资=8500, 奖金=2000, 扣款=500

目标格式：
========================
姓名：张明
基本工资：¥8,500.00
奖金：¥2,000.00
扣款：¥500.00
实发工资：¥10,000.00
发薪日期：2024年01月02日
========================

公式：
=REPT("=", 24) & CHAR(10) &
"姓名：" & A2 & CHAR(10) &
"基本工资：" & TEXT(B2, "¥#,##0.00") & CHAR(10) &
"奖金：" & TEXT(C2, "¥#,##0.00") & CHAR(10) &
"扣款：" & TEXT(D2, "¥#,##0.00") & CHAR(10) &
"实发工资：" & TEXT(B2+C2-D2, "¥#,##0.00") & CHAR(10) &
"发薪日期：" & TEXT(TODAY(), "yyyy年mm月dd日") & CHAR(10) &
REPT("=", 24)

练习3：产品库存状态报告

数据：产品=iPhone 15, 库存=150, 预警线=50

状态公式：
=TEXT(B2/C2, 
    "[<1]紧急!库存仅剩" & TEXT(B2/C2, "0%") & "!;
     [<2]警告!库存为" & TEXT(B2/C2, "0%") & ";
     库存充足(" & TEXT(B2/C2, "0%") & ")")

结果：
紧急!库存仅剩300%!  // 150/50=3，库存充足
修正公式：
=TEXT(B2/C2,
    "[<1]紧急!库存仅剩" & TEXT(B2/C2, "0%") & "!;
     [<2]警告!库存为" & TEXT(B2/C2, "0.0") & "倍;
     库存充足(" & TEXT(B2/C2, "0.0") & "倍)")

---

十一、性能优化建议

1. 避免过度使用

TEXT函数会使结果变为文本，影响后续计算。
只在最终显示时使用TEXT进行格式化。

2. 批量处理

使用数组公式或填充柄批量应用TEXT函数，
而不是逐个单元格输入。

3. 考虑使用自定义单元格格式

对于纯显示需求，使用单元格格式设置（Ctrl+1）
比TEXT函数更高效，且保持数值类型。

设置方式：右键→设置单元格格式→自定义

4. 缓存中间结果

对于复杂格式化，先计算好数值结果，
最后再用TEXT进行格式化。

---

十二、与自定义格式对比

特性	TEXT函数	自定义单元格格式
返回类型	文本	数值（显示为文本）
可计算性	不能直接计算	保持数值，可计算
灵活性	可在公式中动态改变	固定格式
组合性	可与其他函数组合	单独使用
性能	稍慢	更快
适用场景	需要文本拼接时	纯显示格式化

选择建议： - 需要将格式化结果与其他文本拼接 → 使用TEXT - 仅需要显示格式化，仍需计算 → 使用自定义格式 - 格式需要根据条件动态变化 → 使用TEXT - 固定格式，数据量大的报表 → 使用自定义格式

---

十三、总结与记忆技巧

常用格式代码记忆

数字：0补零，#不补零，千分逗号记心间
日期：y年m月d日，h时分s秒要分清
文本：@代表原文本，引号可加任意文
条件：[>0]正数显，分号分隔三段情

使用场景总结

1. 报表美化：数字、日期、货币格式化
2. 文本生成：拼接动态文本信息
3. 数据展示：条件格式化显示
4. 导出准备：统一数据格式
5. 标签制作：生成标准化标签

最佳实践

1. 先数值，后文本：先完成计算，最后用TEXT格式化
2. 测试边界值：测试0、负数、大数等特殊情况
3. 文档化格式代码：复杂格式代码添加注释说明
4. 考虑兼容性：避免使用过于特殊的格式代码
5. 备用方案：准备未格式化版本用于计算

一句话总结

TEXT函数是Excel的数据"化妆师"，能把原始数据打扮成你想要的样子，但记住化完妆就不能直接参加算术比赛了！

** 符号在 Excel TEXT 函数中的含义**

一、基本含义

# 在 Excel TEXT 函数中是数字占位符，表示：

1. 只显示有意义的数字（不显示前导零和末尾多余的零）
2. 如果该位置没有数字，则显示为空白
3. 用于定义数字格式的显示方式

---

二、详细解释

1. 与 "0" 占位符对比

占位符	含义	示例	数值 123.45 的显示
#	可选数字位	"###.##"	123.45
0	强制数字位	"000.00"	123.45
区别	没有数字时，#显示空白，0显示0	"###.##"	1234.5 → 1234.5

2. 具体示例分析

数值：158000

1. =TEXT(158000, "#,##0")        // 158,000
   - #,##0 分解：
     #,## → 每三位用逗号分隔，但不显示前导零
     0 → 确保至少显示一位数字（如果整数部分为0，显示0）

2. =TEXT(158000, "#,##0.00")     // 158,000.00
   - #,##0.00 分解：
     #,##0 → 整数部分（至少显示一位）
     .00 → 小数部分（强制显示两位，不足补零）

3. =TEXT(0.5, "#,##0.00")        // 0.50
   - 整数部分：0（因为需要至少显示一位）
   - 小数部分：.50（强制两位，0.5补零为0.50）

4. =TEXT(1234.567, "#,##0.0#")   // 1,234.57
   - 整数：1,234
   - 小数：.57（第二位小数四舍五入）

3. 更多示例

=TEXT(1234, "#")           // 1234
=TEXT(1234, "###")         // 1234
=TEXT(1234, "####")        // 1234
=TEXT(1234, "#####")       // 1234

=TEXT(5, "00000")          // 00005  （强制5位，不足补零）
=TEXT(5, "#####")          // 5      （不补零）

=TEXT(0.25, "0.#")         // 0.3    （四舍五入到一位小数）
=TEXT(0.25, "0.##")        // 0.25   （两位小数）

=TEXT(1234567, "#,##0")    // 1,234,567

---

三、各种占位符对比表

符号	名称	功能	示例	输入值	输出结果
#	数字占位符	只显示有效数字	"###"	5	5
#	数字占位符	只显示有效数字	"###"	0	``（空白）
0	零占位符	强制显示数字位	"000"	5	005
0	零占位符	强制显示数字位	"000"	0	000
,	千位分隔符	每三位加逗号	"#,##0"	1234567	1,234,567
.	小数点	小数点位置	"0.00"	1.5	1.50
?	数字占位符	对齐小数点	"???.??"	1.5	1.5

---

四、实际应用场景

场景1：千位分隔符显示

销售额：1580000
=TEXT(1580000, "#,##0")          // 1,580,000
=TEXT(1580000, "¥#,##0")         // ¥1,580,000
=TEXT(1580000, "$#,##0.00")      // $1,580,000.00

场景2：固定位数显示

产品编号：25
=TEXT(25, "00000")               // 00025  （强制5位）
=TEXT(25, "P-#####")             // P-25    （灵活位数）

场景3：小数位数控制

百分比：0.856
=TEXT(0.856, "0.0%")             // 85.6%
=TEXT(0.856, "0.00%")            // 85.60%
=TEXT(0.856, "#.#%")             // 85.6%

场景4：电话号码格式

手机号：13800138000
=TEXT(13800138000, "000-0000-0000")  // 138-0013-8000

---

五、常用数字格式代码

1. 整数格式

"#"         // 基本整数
"0"         // 至少一位整数
"#,##0"     // 千位分隔整数
"#,##0_);(#,##0)"  // 负数用括号

2. 小数格式

"0.0"       // 一位小数
"0.00"      // 两位小数
"#.##"      // 最多两位小数
"#.0#"      // 至少一位，最多两位小数

3. 货币格式

"¥#,##0"    // 人民币，无小数
"$#,##0.00" // 美元，两位小数
"#,##0.00 €" // 欧元，两位小数

4. 百分比格式

"0%"        // 百分比，无小数
"0.0%"      // 百分比，一位小数
"#.##%"     // 百分比，灵活小数

5. 科学计数法

"0.00E+00"  // 科学计数法
"##0.0E+0"  // 灵活的科学计数法

---

六、练习与测试

练习数据

A	B	数值
1	原始值	158000
2	原始值	1234.567
3	原始值	0.5
4	原始值	25
5	原始值	0.856

练习题

1. 将158000显示为带千位分隔符：=TEXT(B1, "__________")
2. 将1234.567显示为两位小数：=TEXT(B2, "__________")
3. 将0.5显示为百分比（一位小数）：=TEXT(B3, "__________")
4. 将25显示为5位数字（不足补零）：=TEXT(B4, "__________")
5. 将0.856显示为货币格式（两位小数）：=TEXT(B5, "__________")

参考答案

1. =TEXT(B1, "#,##0")          // 158,000
2. =TEXT(B2, "#,##0.00")       // 1,234.57
3. =TEXT(B3, "0.0%")           // 50.0%
4. =TEXT(B4, "00000")          // 00025
5. =TEXT(B5, "$#,##0.00")      // $0.86

---

七、注意事项

1. TEXT函数返回的是文本，不能用于后续计算
2. 如果需要计算，要保留原始数值
3. 自定义格式 vs TEXT函数：
4. 单元格格式：只改变显示，值不变

5. TEXT函数：将数值转换为特定格式的文本

6. 常见错误：

   =SUM(TEXT(A1, "0"), TEXT(A2, "0"))  // 错误！TEXT返回文本，不能求和
   
   正确做法：
   =SUM(A1, A2)                        // 先求和
   =TEXT(SUM(A1, A2), "#,##0")         // 再格式化
   

八、记忆技巧

• #：像筛子，只让"有意义"的数字通过
• 0：像强制器，必须填满位置
• 组合使用：#,##0.00 是最常用的格式
• #,##：整数部分用逗号分隔
• 0：确保至少显示一位整数
• .00：确保两位小数

掌握了#的含义，你就能灵活创建各种数字显示格式了！