手把手教你，学会格式化文本

用TEXT 函数对字符格式化
TEXT函数是使用频率非常高的文本函数之一，虽然函数的基本语法十分简单，但它的参数规则变化多端，因此能够演变出十分精妙的应用。
1. TEXT函数的基本语法
TEXT函数的基本语法如下。
第一参数value，要转换为指定格式文本的数值，也可以是文本型数字。
第二参数format_text，用于指定格式代码，与单元格数字格式中的大部分代码基本相同。有少部分代码仅适用于自定义格式，不能在TEXT函数中使用。
例如，TEXT函数无法使用星号（*）来实现重复某个字符以填满单元格的效果。同时也无法实现以某种颜色显示数值的效果，如格式代码“#,##0;[红色]-#,##0”等。
但TEXT函数较自定义格式更加灵活，如可以在格式参数中引入变量，实现动态的格式设置。
除此之外，设置单元格格式与TEXT函数还有以下区别。
（1）设置单元格的格式仅仅是数字显示外观的改变，其实质仍然是数值本身，不影响进一步的汇总计算，即得到的是显示的效果。
（2）使用TEXT函数可以将数值转换为带格式的文本，其实质已经是文本，不再具有数值的特性，即得到的是实际的效果。
2. 了解TEXT函数的格式代码
TEXT函数的格式代码分为4个条件区段，各区段之间用半角分号间隔，默认情况下，这四个区段的定义如下。
示例10-46  按条件返回结果
图10-92为某单位员工技能考核的成绩，B列、C列分别是两次考试结果，D列为C列数值减去B列的结果，缺席考核的人员记为“缺席”。需要在E列将D列中的结果大于0的显示为“升”，小于0的显示为“降”，等于0的显示为“持平”，文本仍显示为“缺席”。
在E2单元格中输入以下公式，向下复制到E9单元格。
=TEXT(D2,'升;降;持平;缺席')
3. 省略部分条件区段
在实际使用中，可以根据需要省略TEXT函数第二参数的部分条件区段，条件含义也会发生相应变化。
自定义条件的四区段可以表示如下。
自定义条件的三区段可以表示如下。
自定义条件的两区段可以表示如下。
示例10-47  使用TEXT函数判断产品是否合格
图10-93为某公司生产的锂电池容量测试结果，产品标称容量为8Ah，实际容量大于等于8Ah为合格，需要在B列标识出A列对应的容量是否合格。
在B2单元格中输入以下公式，向下复制到B9单元格。
=TEXT(A2,'[>=8]合格;不合格')
公式中TEXT函数的第二参数使用两区图10-93 使用TEXT判断产品是否合格段的自定义条件：当A2单元格数值大于等于8时，返回“合格”，否则返回“不合格”。
公式中以一对半角中括号包含的内容为判断的条件，中括号后紧跟着的是符合该条件时返回的结果。
示例10-48  使用TEXT函数评定员工考核成绩
图10-94为某公司员工考核结果，需要按照B列的“考核成绩”对应评级。评级标准为大于等于90分显示为“优秀”，大于等于80分小于90分显示为“良好”，大于等于70分小于80分显示为“中等”，大于等于60分小于70分显示为“及格”，小于60分显示为“不及格”。
在C2单元格中输入以下公式，向下复制到C9单元格。
=TEXT(TEXT(B2,'[>=90]优秀;[>=80]良好;0'),'[>=70 ]中等;[>=60]及格;不及格')
这是TEXT函数嵌套使用的典型用法，由于评级的条件一共为5个，而TEXT自定义条件的数量最多为3个，故使用两次TEXT函数，第一次TEXT函数解决前三个条件的显示方式，其他的内容将返回原始字符串。再利用TEXT函数解决对剩余条件的判断。
TEXT(B2,'[>=90]优秀;[>=80]良好;0')表示当成绩大于等于90分时返回“优秀”，成绩大于等于80分时返回“良好”，其他情况返回原值。这里默认成绩都为整数，如果包含小数，则第三区段的格式应写为相应的小数位数，如两位小数位“0.00”。否则返回的结果按照四舍五入进行计算。
外层使用TEXT将内层符合第三区段的值再次进行区分，大于等于70分时返回“中等”，大于等于60分时返回“及格”，其他情况返回“不及格”。
使用TEXT函数设置不同的数字格式
TEXT函数可用于实现类似于Excel自定义格式的功能，使用部分自定义格式代码，作为TEXT函数第二参数，将数字设置为不同的格式。
TEXT函数中几种常用的符号如下。
0：数字占位符，数量不足的需补齐，如“000”则返回的整数不能小于3位数字。
#：数字占位符，数量不足的无须补齐。
@：文本占位符，连续使用表示重复显示文本。
!或\：强制显示符，如需在公式结果中显示有特定含义的字符，需在字符前加“!”或“\”以强制其显示出来。如果要在结果中始终显示某个特定“0”，需写为“!0”。如图10-95所示，不同的TEXT函数的第二参数将数字设置为不同的格式。
示例10-49  根据条件进行判断
图10-96中的A列包含数字和文本，需要在B列进行判断，如果A列为文本，则返回“文本”。如果为数字 ，正数返回“>0”,负数返回“<0”,等于0时返回“=0”。
在B2单元格中输入以下公式，向下复制到B8单元格。
=TEXT(A2,'>!0;<!0;=!0;文本')
公式中第二参数使用默认的四个区段格式定义，分别定 义第一参数大于0、小于0、等于0和为文本格式时的显示方式。
当A2单元格中的数字大于0时，返回“>0”，但由于0在TEXT函数中是数字占位符，要让其在最终结果中显示出来，需要在前面加半角感叹号“!”。第二和第三区段的格式同理。
示例10-50  使用TEXT函数设置楼门号格式
图10-97为某小区楼门号，A列中的数字包含楼号、单元号及门牌号。需要在B列将其格式修改为x楼x单元xxx的格式。这里默认所有的门牌号都是3位数字，单元号都是一位数字，楼号位数不等。
在B2单元格中输入以下公式，向下复制到B8单元格。
=TEXT(A2,'0号楼0单元000')
TEXT函数中多位数字分段显示时，从右向左依次 满足格式要求。公式中“'0号楼0单元000'”会优先满足将最右侧3位数显示在“单元”后面，倒数第4位数字显示在
“号楼”和“单元”之间，剩余的数字显示最前面。
因为公式中设置显示“0号楼”，所以此处至少显示一位数字，如果设置为“00号楼”则至少显示两位数字。例如，“1号楼”会显示为“01号楼”。
示例10-51  使用TEXT函数设置项目日期格式
图10-98为某公司项目存档的部分内容，其中A列为项目编号，B列为项目的结束日期。需要在C列将A列和B列中的内容一起显示，同时加上“项目编号：”和“结束日期：”作为引导。
在C2单元格中输入以下公式，向下复制到C7单元格。
=$A$1&':'&A2&$B$1&':'&TEXT(B2,'YYYY/MM/DD')
公式中的“ $A$1&':'&A2&$B$1&':'”部分表示用“&”符号将A1单元格与冒号“:”、B1单元格中的标题及冒号“:”连接在一起。A1、B1单元格使用绝对引用使公式在向下复制时，此单元格引用不会发生变化。
“TEXT(B2,'YYYY/MM/DD')”部分将B2单元格中的日期显示为文本格式的日期。如果不使用TEXT函数，日期在使用“&”符号与其他文本连接时，会显示为日期的序列号。
TEXT函数使用变量参数
TEXT函数中的第二参数除了可以引用单元格格式代码或使用自定义格式代码字符串外，还可以通过引入变量或公式运算结果，构造符合代码格式的文本字符串，使TEXT函数具有动态的第二参数。
参数中的“条件”区段或“格式”区段单独引入变量，或“条件”“格式”区段同时引入变量均可。引入变量的区段格式书写需注意，变量前后需用“&”符号与其他字符串相连；“&”符号及变量不包含在标识第二参数的半角双引号内。
为保证书写正确，可以先按正常顺序写完参数代码，再添加上应有的“&”符号及双引号。例如, 如果A2单元格减去60大于0则返回“合格”，否则返回“不合格”。可以先书写以下公式。
然后在第二参数的A2前后分别添加“'&”和“&'”构成完整的参数。
示例10-52  TEXT函数使用变量参数
图10-99为某项实验记录的部分内容，其中B列、C列分别是同批样品两次的测试结果。如果第二次较第一次数值有所增长，则在D列显示第二次的测试结果且保留3位小数，如果第二次与第一次的值相等或小于第一次的结果，则返回“未增长”。
在D2单元格中输入以下公式，向下复制到D6单元格。
=TEXT(C2,'[>'&B2&']0.000;未增长')
公式中使用判断条件“>B2”则显示为3 位小数的数字，否则显示“未增长”。
由于B2在公式中是单元格引用的变量，因此，在“B2”前后需要用“&”符号与其他字符进行相连。
数值与中文数字的转换
TEXT函数不仅可以设置数字格式，还可以将数值与中文数字进行转换。
示例10-53  使用TEXT函数转换中文格式的日期
图10-100中A列是某项目各个合同的签订日期，需要在B列将其修改为中文的日期格式。
在B2单元格中输入以下公式，向下复制到B7单元格。
=TEXT(A2,'[DBnum1]yyyy年m月d日')
格式代码“yyyy年m月d日” 用于提取A2单元格中的日期，并且年份以4位数字表示。再使用格式代码“[DBnum1]”将其转换为中文小写数字格式。
示例10-54  将中文小写数字转换为数值
如图10-101所示，需要将A列中的中文小写数字转换
为数值。
在B2单元格中输入以下数组公式，按<Ctrl+Shift+Enter>组合键，向下复制到B7单元格。
{=MATCH(A2,TEXT(ROW($1:$99 99),[DBnum1]'),0)}
“ROW($1:$9999)”用于生成1~9999的自然数序列。
TEXT函数使用格式代码[DBnum1]将其全部转换为中文小写格式。再由MATCH函数从中精确查找A2单元格字符所处的位置，变相完成从中文到数值的转换。
公式适用于一至九千九百九十九的整数中文小写数字转换，可根据需要调整ROW函数的参数范围。
使用RMB函数转换货币格式
RMB函数可以将数字转换为货币格式，同时设置指定的小数位数，其语法如下：
第一参数number为必需参数，可以是数字、包含数字的单元格的引用或计算结果为数字的公式。
第二参数decimals为可选参数，表示保留的小数位数。如果为负数，则第一参数从小数点往左按相应位数四舍五入。如果省略，则默认其值为2。
此函数依照货币格式将小数四舍五入到指定的位数并转换成文本。例如，“=RMB(4528.75,2)”，返回结果“¥4,528.75”。
NUMBERSTRING函数可以将数值转换为中文小写数字，共有3种不同的数字格式。函数仅支持正整数，不支持包含小数的数字。基本语法如下：
第一参数为要转换的数字或单元格引用。
第二参数为转换类型，分别为如下结果。
“=NUMBERSTRING (1234567890,1)”返回结果为一十二亿三千四百五十六万七千八百九十。
“=NUMBERSTRING (1234567890,2)”返回结果为壹拾贰亿叁仟肆佰伍拾陆万柒仟捌佰玖拾。
“=NUMBERSTRING (1234567890,3)”返回结果为一二三四五六七八九〇。
示例10-55  使用TEXT函数生成中文大写金额
为满足《中华人民共和国票据法》中对中文大写金额的书写要求，可以使用TEXT函数将数字格式转换为中文大写金额。图10-102中A列是小写的金额，需要在B列使用公式将其转换为中文大写金额。
在B2单元格中输入以下公式，向下复制到B6单元格。
=SUBSTITUTE(SUBSTITUTE(IF(-RMB(A2,2),TEXT(A2,';负')&TEXT (INT(ABS(A2)+
0.5%),'[dbnum2]G/通用格式元;;')&TEXT(RIGHT(RMB(A2,2),2),'[dbnum2]0角0分;;
整'),),'零角',IF(A2^2<1,,'零')),'零分','整')
公式中“RMB(A2,2)”部分的作用是依照货币格式将数值四舍五入到两位小数并转换成文本。
使用TEXT函数分别将金额数值的整数部分和小数部分及正负符号进行格式转换。
“TEXT(A2,';负')”部分的作用是，如果A2单元格的金额小于0则返回字符“负”。
“TEXT(INT(ABS(A2)+0.5%),'[dbnum2]G/通用格式元;;')”部分的作用是将金额取绝对值后的整数部分转换为大写。+0.5%的作用是为了避免0.999元、1.999元等情况下出现的计算错误。
“TEXT(RIGHT(RMB(A2,2),2),'[dbnum2]0角0分;;整')”部分的作用是，将金额的小数部分转换为大写。
再使用连接符号“&”连接3个TEXT函数的结果。
IF函数对“-RMB(A2,2)”进行判断，如果金额大于等于1分，则返回连接TEXT函数的转换结果，否则返回空值。
最后使用两个SUBSTITUTE函数将“零角”替换为“零”或空值，将“零分”替换为“整”。
用T函数返回值引用的文字
T函数返回值引用的文字，函数语法为：
函数只用一个参数value，当参数为文字或引用文字，将返回该文字，如果为数字或逻辑值，将返回空文本。如果参数为错误值，仍返回错误值，如图10 -103所示。
使用宏表函数时，由于计算结果不能实时更新，通常会在公式最后加上“&T(NOW())”。
NOW()是易失性函数，可以通过在单元格中的输入、编辑等操作刷新结果。再 用T函数将NOW生成的日期时间返回空文本。因此&T(NOW())虽然不生成文本数据，但组合使用时可以起到自动刷新的功能。
另外，在数组公式中，T函数还被用于将OFFSET、INDIRECT等函数形成的三维引用转为二维引用。

---------------------------------------------------------------------

推荐图书

北京大学出版社
《Excel 2016函数与公式大全》
▼

1. 专家云集：多位身处各行各业，并身怀绝技的微软全球有价值专家与您无私分享。多年对 Excel的研究结果进行揭秘。
2. 知识点全覆盖：详尽而又系统地介绍了 Excel 2016函数与公式的所有技术特点和应用方法，全面覆盖相关知识点，完备知识体系无人能及。
3. 解决实际问题：大量源自实际工作的典型案例，通过细致地讲解，生动地展示各种应用技巧，快速提高读者的办公效率，让读者提前完成手头工作，不用加班。
4. 专业级深度剖析：对常常困扰学习者的功能性特性进行深入剖析，可以让读者既能知其然，又能知其所以然。

赞 (0)

相关推荐