1、EXECL 2007帮帮助助文文件件整整理理：23.523.5 说说明明 TEXT 函数可将数值转换为文本，并可使用户通过使用特殊格式字符串来指定显示格式。需要以可读性更高的格式显示数字或需要合并数字、文本或符号时，此函数很有用。 例如，假设单元格 A1 含有数字 23.5。若要将数字格式设置为美元金额，可以使用以下公式： =TEXT(E1,“$0.00“) 在本例中，Excel 会显示 $23.50。 也可以使用功能区“开始”选项卡上的“数字”组中的命令来设置数字格式。但是，只有整个单元格都为数字时，这些命令才起作用。如果需要设置数字格式并将其与其他文本 合并，使用 TEXT 函数是最佳选择
2、。例如，可以向前一个公式中添加文本： =TEXT(E1,“$0.00“) “小小于于0“;0; 0!“贷贷 “!“;!“平平“!“;!“!“ 正正;负负;零零;非非 数数值值 =500“500 “;=300“300 “;“300“;“ 非非数数值值“ 2899999.000000“贷“正500 21.000000“贷“正300 402.313460“贷“正300 25403.436910“贷“正500 300.000000“贷“正300 0.000000“平“零300 15022226.807280“贷“正500 500.000000“贷“正500 -3014.56037小于0“借“负-300
3、 -201223.43691小于0“借“负-300 112.313460“贷“正300 -1.19000小于0“借“负-300 0.001000“贷“正300 101.124460“贷“正300 1654789650“贷“正500 ni好ni好“ni好“非数值非数值 3 3、日日期期时时间间格格式式: : 日期时间格式 用常规格式 显示日期 年 DBNum4G/通用格式y 1900/01/10 00:00:0010101000 1901/02/10 01:01:01407.0423727407.042372685185407.042372701 1902/03/14 02:02:02804.0
4、847454804.08474537037804.084745402 1903/04/15 03:03:031201.1271181201.127118055561201.12711803 1904/05/16 04:04:041598.1694911598.169490740741598.16949104 1905/06/17 05:05:051995.2118631995.211863425931995.21186305 1906/07/19 06:06:062392.2542362392.254236111112392.25423606 1907/08/20 07:07:072789.2
5、966092789.29660879632789.29660907 1900/01/14 00:00:0014141400 2010/05/15 11:12:3340313.4670540313.467052314840313.4670510 2008/08/08 00:00:0039668396683966808 1900/01/15 00:00:0015151500 备注：TEXT的格式设置可以参考论坛文章： 自定义单元格格式一网打尽 或者根据单元格格式自己组合 0.000.0000.0000#.#,# 1.191.1901.190011.21 1102.311102.3131102.31
6、3511021102.31,102 2103.442103.4372103.436921032103.42,103 1304.561304.5601304.560413051304.61,305 0.000.0000.0000. 15022226.8115022226.80715022226.80731502222715022226.815,022,227 -4015.68-4015.684-4015.6838-4016-4015.7-4,016 -3014.56-3014.560-3014.5604-3015-3014.6-3,015 -201223.44-201223.437-201223
7、.4369-201223-201223.4-201,223 -1012.31-1012.313-1012.3135-1012-1012.3-1,012 -1.19-1.190-1.1900-1-1.2-1 20100203.0020100203.00020100203.00002010020320100203.20,100,203 0.000.0010.0010. 101.12101.124101.1245101101.1101 165478965.00165478965.000165478965.0000165478965165478965.165,478,965 只只显显示示负负值值显显示
8、示=0=0的的值值只只显显示示0 0值值只只显显示示文文本本 =500“1级级 “;=500#,#;= 300#,#0.00;#,# #0.00 ;“小小于于0“;0.0;0;G/通用格式 1级2,899,9992899999.0 3级21.0021.0 2级402.31402.3 1级25,40325403.4 2级300.00300.0 3级0.000 1级15,022,22715022226.8 1级500500.0 1级-3,014.56小于0 1级-201,223.44小于0 3级112.31112.3 3级-1.19小于0 3级0.000.0 3级101.12101.1 1级165
9、,478,965165478965.0 #VALUE!ni好ni好 条条件件格格式式的的结结构构：1 1、text(text(数数据据源源，“0“0显显示示值值；1?1? ?/?;=0?/?;=0? ?/?;?/?/?;?/? #.0,“#.0,“千千元元“ “0!.0,“0!.0,“万万“ “0!.000,0!.000, 1 19/100.0千元0.0万0.000 1102 205/6541.1千元0.1万0.001 2103 232/5312.1千元0.2万0.002 1304 427/7621.3千元0.1万0.001 0 .0千元0.0万0.000 15022226 377/46715
10、022.2千元1502.2万15.022 -546133/136-4.0千元-0.4万-0.004 -2297095/762-3.0千元-0.3万-0.003 -106849645/531-201.2千元-20.1万-0.201 -662053/654-1.0千元-0.1万-0.001 -119/100.0千元0.0万0.000 20100203 20100.2千元2010.0万20.100 0/1 .0千元0.0万0.000 101 57/458.1千元0.0万0.000 165478965 165479.0千元16547.9万165.479 用用%表表示示用用*填填充充 强强制制由由!或或
11、后后边边的的字字符符填填 充充 #.00%“*“;“*“;“*“;“*“!*;!;!;!,*;!;, 289999900.00%* 2100.00%* 40231.35%* 2540343.69%* 30000.00%* .00%* 1502222680.73%* 50000.00%* -301456.04%*! -20122343.69%*! 11231.35%* -119.00%*! .10%* 10112.45%* 16547896500.00%* ni好*, 时+分 hh 时hmhh:m 2400 时0000:0 97691 时1101:1 192982 时2202:2 288273
12、时3303:3 383564 时4404:4 478855 时5505:5 574146 时6606:6 669437 时7707:7 3360 时0000:0 96752311 时111211:12 9520320 时0000:0 3600 时0000:0 时 0!.0,0!.0, 0.0 0.0 0.0 0.0 0.0 1.5 0.0 0.0 0.0 0.0 0.0 2.0 0.0 0.0 16.5 重重复复显显示示文文本本, 次次数数与与个个数数有有关关 2899999 21 402.313456 25403.43691 300 0 15022226.81 500 -3014.56036
13、8 -201223.4369 112.313456 -1.19 0.001 101.124456 165478965 ni好ni好ni好ni好ni好 分分+秒秒上、下午时间 hmmhh:mmmm:smm:ssssssss.00hh:mm AM/PM 00000:00144000:000:0086400000000.0012:00 AM 10101:015861411:101:013516846110101.0001:01 AM 20202:0211578822:202:026947292220202.0002:02 AM 30303:0317296233:303:031037773833030
14、3.0003:03 AM 40404:0423013644:404:0413808184440404.0004:04 AM 50505:0528731055:505:0517238630550505.0005:05 AM 60606:0634448466:606:0620669076660606.0006:06 AM 70707:0740165877:707:0724099522770707.0007:07 AM 00000:00201600:000:00120960000000.0012:00 AM 111211:125805139212:3312:333483083553333333.32
15、11:12 AM 00000:00571219200:000:00342731520000000.0012:00 AM 00000:00216000:000:00129600000000.0012:00 AM 时+分 日期+时间显示中文日期 h:mm:ss a/pe-m-d h:mm:ss.00dbnum1yyyy年m月d日dbnum2yyyy年m月d日 12:00:00 a1900-1-10 0:00:00.00一九年一月十日壹玖零零年壹月壹拾日 1:01:01 a1901-2-10 1:01:01.00一九一年二月十日壹玖零壹年贰月壹拾日 2:02:02 a1902-3-14 2:02:0
16、2.00一九二年三月十四日壹玖零贰年叁月壹拾肆日 3:03:03 a1903-4-15 3:03:03.00一九三年四月十五日壹玖零叁年肆月壹拾伍日 4:04:04 a1904-5-16 4:04:04.00一九四年五月十六日壹玖零肆年伍月壹拾陆日 5:05:05 a1905-6-17 5:05:05.00一九五年六月十七日壹玖零伍年陆月壹拾柒日 6:06:06 a1906-7-19 6:06:06.00一九六年七月十九日壹玖零陆年柒月壹拾玖日 7:07:07 a1907-8-20 7:07:07.00一九七年八月二十日壹玖零柒年捌月贰拾日 12:00:00 a1900-1-14 0:00:0
17、0.00一九年一月十四日壹玖零零年壹月壹拾肆日 11:12:33 a2010-5-15 11:12:33.32二一年五月十五日贰零壹零年伍月壹拾伍日 12:00:00 a2008-8-8 0:00:00.00二八年八月八日贰零零捌年捌月捌日 12:00:00 a1900-1-15 0:00:00.00一九年一月十五日壹玖零零年壹月壹拾伍日 上、下午时间 自定义应用 dbnum3yyyy年m月d日 dbnum4yyyy年m月 d日 e-m 月月报ms Sm/d/yyd-mm 年月日1900年1月10日1900-1 月月报14400864000 S1/10/0010-01 年月日1901年2月10
18、日1901-2 月月报58614135168461 S2/10/0110-02 年月日1902年3月14日1902-3 月月报115788269472922 S3/14/0214-03 年月日1903年4月15日1903-4 月月报1729623103777383 S4/15/0315-04 年月日1904年5月16日1904-5 月月报2301364138081844 S5/16/0416-05 年月日1905年6月17日1905-6 月月报2873105172386305 S6/17/0517-06 年月日1906年7月19日1906-7 月月报3444846206690766 S7/19
19、/0619-07 年月日1907年8月20日1907-8 月月报4016587240995227 S8/20/0720-08 年月日1900年1月14日1900-1 月月报201601209600 S1/14/0014-01 年月日2010年5月15日2010-5 月月报 580513923483083553 S5/15/1015-05 年月日2008年8月8日2008-8 月月报 571219203427315200 S8/8/088-08 年月日1900年1月15日1900-1 月月报216001296000 S1/15/0015-01 显示中文日期 Y年m月D日d-mmmd-mmmmd-
20、mmmm-yyyy-mmmmmmmm yy$-F800 00年1月10日10-Jan10-January10-January-0000-JanuaryJanuary 001900年1月10日 01年2月10日10-Feb10-February10-February-0101-FebruaryFebruary 011901年2月10日 02年3月14日14-Mar14-March14-March-0202-MarchMarch 021902年3月14日 03年4月15日15-Apr15-April15-April-0303-AprilApril 031903年4月15日 04年5月16日16-M
21、ay16-May16-May-0404-MayMay 041904年5月16日 05年6月17日17-Jun17-June17-June-0505-JuneJune 051905年6月17日 06年7月19日19-Jul19-July19-July-0606-JulyJuly 061906年7月19日 07年8月20日20-Aug20-August20-August-0707-AugustAugust 071907年8月20日 00年1月14日14-Jan14-January14-January-0000-JanuaryJanuary 001900年1月14日 10年5月15日15-May15
22、-May15-May-1010-MayMay 102010年5月15日 08年8月8日8-Aug8-August8-August-0808-AugustAugust 082008年8月8日 00年1月15日15-Jan15-January15-January-0000-JanuaryJanuary 001900年1月15日 自定义应用 $-F400 0:00:00 1:01:01 2:02:02 3:03:03 4:04:04 5:05:05 6:06:06 7:07:07 0:00:00 11:12:33 0:00:00 0:00:00 一一、在在TEXTTEXT函函数数的的条条件件格格式式
23、中中使使用用变变量量：例例题题请请按按住住F9F9 TEXT函数的条件格式公式样式：text(数据源，“条件1显示格式；条件2显示格式；否则显示格式；文本显示格式“) 条件格式可以全部或者部分使用变量，当部分使用变量时，条件和显示格式的任何部分都可以使用变量，包括条件格式中的运算符号。 当使用变量时，不使用变量的部分用引号（“)引起来，然后用运算符号“格式代码“) 运算符号应是比较运算符：、=、=、0.3 运算符号用单元格引用 3、在格式代码中使用变量 公公式式样样式式：=text(数据源,“条件代码“条件代码“)相当于IF(A1条件1,显示1,“)； 3、当有三个时，如：text(A1，“条
24、件1显示1；条件2显示2;显示3)相当于IF(A1条件1,显示1,IF(A1条件2,显示2,显示3) text(A1，“条件1显示1；条件2显示2;显示3)相当于IF(A1条件1,显示1,IF(A1条件2,显示2,显示3) 欢迎你欢迎你 4、当有四个时，如：text(A1，“条件1显示1；条件2显示2;显示3;显示4)相当于IF(ISTEXT(A1),“显示4“,IF(A1条件1,显示1,IF(A1条件2,显示2,显示3) 不合格不合格 text(A1，“条件1显示1；条件2显示2;显示3;)相当于IF(ISTEXT(A1),“,IF(A1条件1,显示1,IF(A1条件2,显示2,显示3) 三
25、三、TEXTTEXT函函数数的的第第一一个个参参数数研研究究 1、第一个参数为一个单元格引用60及格如果A1-60=20显示优秀，如果A1-60为负数（即A10.20.5“)，此处0.是占位符，0.69四舍五入为1.，结果是：1.51.5 解释：在TEXT函数中，如果在“0“和“.“前面没有!(或者)，就是占位符，否则就是被强制显示的字符。只有有了小数点占位符号（不是!.）， 小数点后面的小数部分才能被显示，否则只显示整数部分，并且没有被显示的部分会自动四舍五入。 关于“0“、“.“和“!“的详细解释请参考补充说明部分。 六六、TEXT函函数数的的参参数数中中带带“-”的的解解释释: 1、TE
26、XT函数的参数带一个“-“号的解释，见以下语句： 1）=TEXT(-1,“-2;=-1-3“)显示-3-3-3 2）=TEXT(-1,“=-1-2;-3“)显示-2-2-2 3）=TEXT(-1,“=1-2;-3“)显示-3-3#-3 4）=TEXT(-1,“-2;=-1-3“)显示-3-3-3 5)=TEXT(-1,“=1-2;=2-3;-4“)显示-4-4#-4 6）TEXT(-1,“=1-2;=-1“=2-3;“=-1“=2-3;-你好;欢迎“)显示-你好-你好# 10)=TEXT(“-1“,“-2;=-1-3“)显示-3-3-3 个人解释：第一个参数的“-”作为运算符负号使用；如果第二
27、个参数中=第一参数的条件出现，则第二个参数的“-”作为运算符负号使用； 如果第二个参数中=第一参数的条件没有出现，则作为字符符号使用并且如果且TEXT的条件只有一部分时，显示原值，有两部分以上时，则多显示一个运算符负号“-”。 2、TEXT函数的参数带两个“-“号的解释，见以下语句： 1)=TEXT(-1,“=1-2;=-1-3;-4“)显示-2-2-22 2)=TEXT(-1,“=1-2;=-1-3;-4“)显示-2-2#-2 3)=TEXT(“-1“,“=1-2;=-1-3;-4“)显示-1-1#-1 4)=TEXT(-1,“=1-2;=-1-3;-4“)显示-2-2#-2 5)=TEXT
28、(-1,“=-1-2;=1“-3“)显示-3-3# #-3 2)=TEXT(-1,“=1-2;-3“)显示-2-2#-22 3)=TEXT(-1,“=1-2;-3“)显示-3-3#-33 4)=TEXT(-6,“=1-2;-3“)显示-2-2-22# 5）TEXT(-6,“=1-2;-3;-4“)显示-6#VALUE!# # # 6）=TEXT(-1,“=-1-2;-3“)显示-3-3#-33 7)=TEXT(-1,“=1-2;“=-1-3;-4“)显示-1-1# #-1 10)=TEXT(“-1“,“=1-2;=-1-3;-4“)显示-1-1# # #-1 概括： 1）第一参数的“-”号如果
29、多于1个且在引号内，则第一参数为字符。 2）第一参数的“-”号如果只有1个或者多于1个但没在引号内，则第一参数为数值。 3）如果第一参数的计算结果为正数，第二个参数的“-“不在引号内,“-“全部为运算符符号使用。第二个参数的“-“在引号内时,紧挨着数字的一个“-”作为运算符负号使用。 其它“-”作为字符符号使用。 4）如果第一参数的计算结果为负数， 4-1)第二个参数的“-“不在引号内时:当“-”为单数时，则有1个作为字符使用，其它作为运算符负号使用，双数时全部“-”作为运算符符号使用。 4-2)第二个参数的“-“在引号内，则紧挨着数字的一个“-”作为运算符负号使用，其它“-”作为字符符号使用
30、；并且如果第二参数中=第一参数的条件没有出现， 此时还要看TEXT的条件格式有几部分：有两部分且第二部分没有条件时或者有三部分但第二部分有条件（=非第一参数）时，此时结果值前面还要多显示一个“-”字符。 4、条件格式是区间，第一参数为负数 1）=TEXT(-3,“B10的值 =INDIRECT(TEXT(102,“r00c0“),)-B10的值,注意前后的变化 =INDIRECT(TEXT(103,“r00c0“),)-C100的值 =INDIRECT(TEXT(1003,“r00c0“),)-C100的值 =INDIRECT(TEXT(901,“r00c0“),)-A90的值 INDIREC
31、T(TEXT(1681;1682;1683;1691;1692;1693;1701;1702;1703,“!r00c0“),)- 得到一个数组INDIRECT(“r168c1“;“r168c2“;“r168c3“;“r169c1“;“r169c2“;“r169c3“;“r170c1“;“r170c2“;“r170c3“,) 1474545 258 369 2、怎么解决繁体版TEXT函数的R0C000用法问题？ 问题：为什么在繁体版的Excel中使用INDIRECT+TEXT(数字，“R0C000“)的解法不管用呢？怎样解决？ 回答：繁体版的格式代码与R、C有冲突，需要加强制占位符！或，用TEX
32、T(数字，“!R0!C000“)或者TEXT(数字，“R0C000“)可以解决。 indirect与TEXT联合应用例子，将数字按S型排列： indirect函数的使用，请参考论坛chenjun版主的文章：函数的深入理解。 八八、TEXTTEXT函函数数嵌嵌套套 格式如：=TEXT(TEXT(TEXT(TEXT(A1,“=90优秀;=80良好;0“),“=70中等;=60及格;0“),“=50不及格;=40差;0“),“=30很差;=20特别差;0“) 一般是把前每个TEXT的前2个判定条件满足后，第三个设定为显示原值，然后再进行嵌套。 例如=TEXT(TEXT(D4-60,“=30优秀;不及
33、格;0“),“=10良好;及格“) 这里假设分数全都是整数，没有小数 这是TEXT函数嵌套的经典例子，首先解释里面的TEXT: 如果D4-60=30显示优秀，D4-60=90、D460、60=D490三个范围 再解释外面的TEXT: 如果里面的结果为“优秀”或者“不及格”，则为文本，最后显示为“优秀”或者“不及格”； 如果为数值（即60=D4=10时显示良好，否则（即TNT(D4-60+0.5)10）显示及格 经过外面的TEXT后又把D4的范围分为70=D490和60=D470 本文参考论坛文章： 1、你未曾见过的TEXT函数用法 2、TEXT 函数应用技巧 1434445878889 242
34、468690 341478591 440488492 539498393 638508294 737518195 836528096 935537997 1034547898 1133557799 12325676100 13315775101 14305874102 15295973103 16286072104 17276171105 18266270106 19256369107 20246468108 212223656667109 1.公式中不要用IF函数。 2.只允许在A1写一个公式，右拉，下拉完成！ 3.不要用VBA，用函数和公式完成！ 4.字符数越少越好！ 5.加一条，公式适用任意行、任意列（可能是7行8列，也可能是9行13列等，拉到哪儿，算到哪儿）
