变化多端的TEXT函数

donghan · 发表于 2010-3-9 09:29

虽然TEXT函数比较常见，但是由于它的参数规则变化多端，还是有很多人对它只是限于最基本的了解，本文是参考了论坛的精华帖子，在自己理解的基础上整理出来的，如果有错误之处，请大家指正，并且如果本文对TEXT函数的用法有不详细或者没有包含之处，请大家在此贴跟帖，希望此贴子能成为TEXT函数功能的终结贴，具体内容请见下面（根据13楼修正并增加问题中的TEXT函数嵌套）:

[ 本帖最后由 donghan 于 2010-3-9 14:57 编辑 ]
再讨论一下90楼的贴子:
http://club.excelhome.net/viewthread.php?tid=544935&page=6#pid3796952

[ 本帖最后由 wshcw 于 2010-5-2 23:09 编辑 ]

donghan · 发表于 2010-3-9 09:32

EXECL 2007帮助文件整理：
说明
TEXT 函数可将数值转换为文本，并可使用户通过使用特殊格式字符串来指定显示格式。需要以可读性更高的格式显示数字或需要合并数字、文本或符号时，此函数很有用。
例如，假设单元格 A1 含有数字 23.5。若要将数字格式设置为美元金额，可以使用以下公式：
=TEXT(23.5,"$0.00")
在本例中，Excel 会显示 $23.50。
也可以使用功能区“开始”选项卡上的“数字”组中的命令来设置数字格式。但是，只有整个单元格都为数字时，这些命令才起作用。如果需要设置数字格式并将其与其他文本
合并，使用 TEXT 函数是最佳选择。例如，可以向前一个公式中添加文本：
=TEXT(E1,"$0.00") & " per hour"
Excel 会显示 $23.50 per hour。

语法：
TEXT(value, format_text)
TEXT 函数语法具有以下参数 （参数：为操作、事件、方法、属性、函数或过程提供信息的值。）：
1）value  必需。数值、计算结果为数值的公式，或对包含数值的单元格的引用。
2）format_text  必需。使用双引号括起来作为文本字符串的数字格式，例如，"m/d/yyyy" 或 "#,##0.00"。有关详细的格式准则，请参阅以下部分。
备注：Format_text 为“单元格格式”对话框中“数字”选项卡上“分类”框中的文本形式的数字格式。
1、有关数字格式的准则
1)显示小数位和有效位:若要设置分数或含有小数点的数字的格式，请在 format_text 参数中包含以下位占位符、小数点和千位分隔符。
占位符说明
0（零）如果数字的位数少于格式中零的数量，则显示非有效零。例如，如果键入 8.9，但要将其显示为 8.90，请使用格式 #.00。
# 按照与 0（零）相同的规则执行操作。但是，如果键入的数字在小数点任一侧的位数均少于格式中 # 符号的数量，Excel 不会显示多余的零。
例如，如果自定义格式为 #.## 且在单元格中键入了 8.9，则会显示数字 8.9。
? 按照与 0（零）相同的规则执行操作。但是，对于小数点任一侧的非有效零，Excel 会加上空格，使得小数点在列中对齐。例如，自定义格式 0.0? 会
对齐列中数字 8.9 和 88.99 的小数点。
.（句点）在数字中显示小数点。
2)如果数字的小数点右侧的位数大于格式中的占位符，该数字会四舍五入到与占位符具有相同小数点位的数字。如果小数点左侧的位数大于占位符数，
Excel 会显示多余的位数。如果格式仅在小数点左侧含有数字符号 (#)，小于 1 的数字会以小数点开头；例如，.47。

显示内容显示格式使用此格式
1234.59 1234.6 "####.#"
8.9 8.9 "#.000"
0.631 0.6 "0.#"
12 12 "#.0#"
1234.568    1234.57
44.398   44.398 "???.???"
102.65 102.65
2.8     2.8
（小数点对齐）
5.25 5 1/4 "# ???/???"
5.3 5 3/10
（分数对齐）
3)显示千位分隔符:要将逗号显示为千位分隔符或按倍数 1,000 缩放数字，请在数字格式中包含以下",（逗号）"分隔符。
,（逗号）:在数字中显示千位分隔符。如果格式中含有被数字符号 (#) 或零包围起来的逗号，Excel 会分隔千位。位占位符后的逗号会以 1,000 为单位计量数字。
显示内容显示格式使用此格式
12000 12,000 "#,###"
12000 12 "#,"
12200000 12.2 "0.0,,"
12200000 12,200.0 #,###.0,

2、有关日期和时间格式的准则
1)显示日、月和年:若要将数字显示为日期格式（如日、月和年），请在 format_text 参数中使用以下代码。
m 将月显示为不带前导零的数字。
mm 根据需要将月显示为带前导零的数字。
mmm 将月显示为缩写形式（Jan 到 Dec）。
mmmm 将月显示为完整名称（January 到 December）。
mmmmm 将月显示为单个字母（J 到 D）。
d 将日显示为不带前导零的数字。
dd 根据需要将日显示为带前导零的数字。
ddd 将日显示为缩写形式（Sun 到 Sat）。
dddd 将日显示为完整名称（Sunday 到 Saturday）。
yy 将年显示为两位数字。
yyyy 将年显示为四位数字。

显示内容显示格式使用此格式
月 1月12日 "m"
月 1月12日 "mm"
月 Jan–Dec "mmm"
月 January–December "mmmm"
月 J–D "mmmmm"
日 1月31日 "d"
日 01–31 "dd"
日 Sun–Sat "ddd"
日 Sunday–Saturday "dddd"
年 00-99 "yy"
年 1900-9999 "yyyy"
2)显示小时、分钟和秒钟  若要显示时间格式（如小时、分钟和秒钟），请在 format_text 参数中使用以下代码。
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 将秒显示为不带前导零的数字。
以秒为单位显示经过的时间。如果所用的公式返回的秒数超过 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 表示时间。

显示内容显示格式使用此格式
小时 0-23 "h"
小时 00-23 "hh"
分钟 0-59 "m"
分钟 00–59 "mm"
秒 0-59 "s"
秒 00–59 "ss"
时间 4:00:00 "h AM/PM"
时间 0.691666667 "h:mm AM/PM"
时间 4:36:03 PM "h:mm:ss A/P"
时间 0.191710069 "h:mm:ss.00"
经过的时间（小时和分钟） 1:02:00 "[h]:mm"
经过的时间（分钟和秒） 2.594444444 "[mm]:ss"
经过的时间（秒和百分之一秒） 3735.8 "[ss].00"

3、有关货币、百分比和科学记数法格式的准则
1）包含货币符号  若要在数字前加上美元符号 ($)，请在 format_text 参数的开头键入美元符号（例如，"$#,##0.00"）。若要以数字格式输入以下
货币符号之一，请按 Num Lock 并使用数字小键盘键入各符号对应的 ANSI 代码。
显示内容使用此组合键
¢ Alt+0162
£ Alt+0163
¥ Alt+0165

donghan · 发表于 2010-3-9 09:33

补充说明部分：
1、(:) 时间分隔符。在一些国别，可能用其他符号来当时间分隔符。格式化时间值时，时间分隔符可以分隔时、分、秒。
2、(/) 日期分隔符。在一些国别，可能用其他符号来当日期分隔符。格式化日期数值时，日期分隔符可以分隔年、月、日。
3、y 或 yy 以两位数来表示年 (00 – 99)。
4、yyy或yyyy或e 以四位数来表示年 (0000 – 9999),其中e与语言设定有关的，并不是必然等于yyyy，所以不确定打开文件的Excel环境的话，最好不要用e代替yyyy。
5、b或bb佛历以2位数来表示佛历年，公元前544元是佛历元年
6、bbb或bbbb佛历以4位数来表示佛历年，公元前545元是佛历元年
B1和B2 他们本身不显示任何东西，但是控制后面的y,m,d等所采用的日历，B1是Gregorian历，也就是现行的公历，忽略区域语言设置，B2是一种回历(伊斯兰历法)
7、（g）这个是年号，在简体中文版中没有相关内容可用来显示，给个例子“[$-411]ggge”，这个格式可以显示日本年号纪年，比如1991年的日期会显示成“平成3”，两个g
就用年号的简写，一个g使用一个英文字母表示年号
8、（%）百分比符号占位符。表达式乘以 100。而百分比字符 (%) 会插入到格式字符串中出现的位置上。
9、(,) 千分位符号占位符。在一些国别，是用句点来当千位符号。千位符号主要是把数值小数点左边超过四位数以上分出千位。关于千位分隔符，这个要写在小数点左边
的0或#之间，写在小数点之前或者写在末尾，都表示除以1000的意思，国际单位制的词头大多是1000倍一个，此项功能用于更改单位。写在数字最前面，就直接显
示“,”，写在小数点后但不是结尾，则忽略。
10、(\) 将格式字符串中下一个字符显示出来。如想显示一作为原义字符并有特殊含义的字符，可将此字符置于反斜杠 (\) 之后。反斜杠本身并不会显示出来。
而使用反斜杠 (\) 的效果和使用双引号是一样的。如想显示反斜杠字符，可使用两个反斜杠 (\\)。那些不能显示为原义字符的字符是日期格式字符和
时间格式字符（a, d, h, m, s, y, / 和 :）、数值格式字符（#, 0, %, E, e, 逗点和句点）和字符串格式字符（@, &,和 !）。
11、(!) 强制显示下一个字符,作用同\。
12、(@) 就表示原来的整个文本串，写在哪里，就是把原先的文本串显示在哪里
13、(0) 数字占位符。显示一位数字或是零。如果表达式在格式字符串中 0 的位置上有一位数字存在，那么就显示出来；否则，就以零显示。如果数值的位数
少于格式表达式中零的位数（无论是小数点的左方或右方），那么就把前面或后面的零补足。如果数值的小数点右方位数多于格式表达式中小数点右面零的
位数，那么就四舍五入到有零的位数的最后一位。如果数值的小数点左方位数多于格式表达式中小数点左面零的位数，那么多出的部分都要不加修饰地显示出来。
14、(#) 数字占位符。显示一位数字或什么都不显示。如果表达式在格式字符串中“#”的位置上有数字存在，那么就显示出来；否则，该位置就什么都不显示。
此符号的工作原理和“0”数字占位符大致相同，不同之处只有在当表达式中数值的位数少于“#”的位数（无论是小数点左方或右方）时，不会把前面或后面的零显示出来。
15、(.) 小数点占位符。在一些国别是用逗号来当小数点的。小数点占位符用来决定在小数点左右可显示多少位数。如果格式表达式在此符号左边只有正负号，那么小于
1 的数字将以小数点为开头。如想在小数前有“0”的话，那么请在小数点占位符前加上“0”这个数字占位符。小数点占位符的实际字符在格式输出时要看
系统的数字格式而定。例如，可以用格式字符串 "##0,," 将 1 亿表示成 100。数值小于 1 百万的话表示成“0”，两个邻近的千分位符号除了紧接在小数
点左边以外，在任何位置出现时均简单地视为指定了使用千分位符号。小数点占位符的真正字符在格式输出时，需视系统识别的数字格式而定。
16、(E- E+ ) "科学格式。科学记数只能用大写的“E+""、“E-”，小写不行，估计是M$增加了""e""的含义后，忘了改帮助文件
。如果格式表达式在 E-、E+的右方含有至少一个数字占位符"
（0 或 #），那么数值将表示成科学格式，而 E 会被安置在数字和指数之间。E右方数字占位符的个数取决于指数位数。使用 E-时，会用减号来表示负的乘幂。使用 E+时，
会用减号来表示负的乘幂并用加号来表示正的乘幂。
17、- + $ ( )等显示一个原义字符。如想显示那些列出的字符之外的字符时，可以用反斜杠 ( \ ) 字符作前缀或以双引号 (" ") 括起来。
18、TEXT与定义单元格格式的区别：
text函数以及Excel的所有工作表函数、自定义函数都只能返回结果值，而不能对单元格的其他属性进行操作。
TEXT得到的是“真实”的效果，其结果是文本；而自定义格式得到的是“显示”的效果，表面现象而已，原本是数值的还是数值，未曾改变其本质。
19、G/通用格式以常规的数字显示，相当于”分类”列表中的”常规”选项。
[dbnum1] 中文小写，如：TEXT(NOW(),"[dbnum1]yyyy年m月d日")
[dbnum2] 中文大写日期，如：TEXT(NOW(),"[dbnum2]yyyy年m月d日")
[Dbnum3] 这个格式符号是将数字由半角转为全角字符
[Dbnum4] [Dbnum4]约等价于常规格式，但如果数值的小数位很多时，自动保留的小数位数不同。
20、text函数省略格式:text(value,)相当于 text(value,"") ,是文本就显示,如果数值前是“-”，就显示“-”，否则显示空。
- A30.52
21、在自定义格式中，部分自定义格式代码含有[$-804]，比如“[dbnum2][$-804]G/通用格式”，将数字显示为中文大写数字，如果删除[$-804]，并不影响显示结果，
那么这里的[$-804]作用是什么呢？[$-804]表示采用“汉语（中国）”的语言习惯显示结果，一般可以不填。如果电脑上没有装相应的语言，把[$-804]改掉都不会影响显示结果。
[$-804]把数值都转换为简体形式的，比如说月份，星期。[$-404]把数值转换为繁体的
aaa相当于"[$-804]ddd",aaaa相当于"[$-804]dddd"
22、TEXT函数第二参数为条件格式时，第一参数可以为文本型或数值型的数值，那么第二参数也可以为文本型或数值型的数值条件格式；
如果Text函数的第一参数为文本格式的日期数据时，仍反映出日期序列数数值的特性，但如果第一参数和条件代码中的数值均为文本格式的日期数据，则将以文本字符串的形式进行比较。
对于普通的文本字符串来说，进行条件比较没有意义，不会返回正确结果。
23、TEXT函数的结果为文本，如果用其返回的文本型数据继续以后的运算时，应该将其转化为数值型数据，转换方法如下（其它字符处理函数的结果如果为文本型数值，方法同）：
1)在TEXT()函数前加“--”，如--TEXT()
2)乘1，如TEXT()*1
3)加0，如TEXT()+0
既使--TEXT()进行一次值不变的运算后在进行以后的运算。
24、TEXT只能支持15位数
25、TEXT函数第一参数如果是文本，必须在两端加上""；TEXT函数第二参数如果在某个单元格时，被引用单元格中的代码一般均为文本字符串，因此像“0.00”之类的格式代码需要设置
为文本格式或在前面加单引号,如果是直接写在TEXT公式里面的，必须在两端加上"".
26、在TEXT的第二参数中想写空白""要敲四下

[ 本帖最后由 donghan 于 2010-3-9 14:32 编辑 ]

donghan · 发表于 2010-3-9 09:35

格式举例部分参考附件：

donghan · 发表于 2010-3-9 09:36

高级应用部分：
一、在TEXT函数的条件格式中使用变量：例题请按住F9
TEXT函数的条件格式公式样式：text(数据源，"[条件1]显示格式；[条件2]显示格式；否则显示格式；文本显示格式")
条件格式可以全部或者部分使用变量，当部分使用变量时，条件和显示格式的任何部分都可以使用变量，包括条件格式中的运算符号。
当使用变量时，不使用变量的部分用引号（"")引起来，然后用&符号与使用变量的部分连接起来，运算后形成条件格式的样式。
变量可以是单元格引用，公式(包括数组公式）、函数结果。
根据需要，条件格式的每个部分或者全部可以省略不使用。
1、条件格式全部使用变量：见格式举例部分，条件格式完全使用的是单元格引用。
2、在条件代码中使用变量
公式样式： =text(数据源,"[运算符号"&变量&"]格式代码;[运算符号"&变量&"]格式代码;格式代码")
运算符号应是比较运算符：>、=、<、>=、<=、<>。
格式代码是自定义单元格的代码（颜色代码除外）。

例子： 0.1561 随机数<0.3 判定随机数大小
0.4085 随机数>0.3 > 运算符号用单元格引用
3、在格式代码中使用变量
公式样式： =text(数据源,"条件代码"&变量&";条件代码"&变量&";"&变量)
例子： 0.09322163 0.0932 0.00 0.0000 0.0
B13>0.5显示两位小数，B13<0.3显示一位小数，其它则显示四位小数
15 非月份数据 B20=10时出现错误
在格式代码中使用变量时，要注意引用的变量中是否有不能显示为原义字符的字符,如0。
可用公式：非月份数据

当然可以在条件部分和格式部分同时使用变量，此处不在举例，有兴趣的可以根据上面的公式自己组合。
4、条件格式中使用函数公式
如：text()与REPT()联合使用
例1：
123456789 10￥ 20￥ 30￥ 40￥ 50￥ 60￥ 70￥ 80￥ 90￥
A26=123456789,如何在C26显示10￥ 20￥ 30￥ 40￥ 50￥ 60￥ 70￥ 80￥ 90￥，公式C26=SUBSTITUTE(TEXT(A26,REPT("0] ",9)),"]","0￥")
例2：将"*"号前后的数字统一补足4位数,不足4位的前面补"0"
原格式公式
1*12*234*254 0001*0012*0234*0254
10*234*56*211 0010*0234*0056*0211
1*1*25*36 0001*0001*0025*0036
以上公式只是演示在条件格式中使用变量的方法，大家可以根据该方法灵活运用，解决实际问题。
例3：怎样才能去掉每组数前面的0呢
原格式公式
121 1,21
30523 3,5,23
4502 45,2
123 1,23
567030612 5,67,3,6,12

二、与IF函数比较例题请按住F9
text的一般格式"正数;负数;零;文本"对于处理数据来说，最多可以有四部分，但第四部分是数据源是非数据时的显示值,如：正 10
加[]判断时的条件格式也最多只能加四个判断条件格式，与IF函数对比如下：
1、当有一个的时候，如：text(A1，"[>条件1]显示1")，相当于IF(A1>条件1,显示1,A1)；你好你好 23.75102578
2、当有两个时，如：text(A1，"[>条件1]显示1；显示2")相当于IF(A1>条件1,显示1,显示2) 你好你好
text(A1，"[>条件1]显示1;")相当于IF(A1>条件1,显示1,"")；你好你好
3、当有三个时，如：text(A1，"[>条件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)))

三、TEXT函数的第一个参数研究
1、第一个参数为一个单元格引用
这种情况是比较常见的，具体例子可以看格式举例部分。
2、第一个参数为多单元格引用
日期求出B57:B62区域中最小的年份、月份
1985-06-17 05:05:05 数组公式如下：
1906-07-19 06:06:06 1906年
1907-08-20 07:07:07 2月
1999-02-14 00:00:00 19060719
2010-03-09 14:23:38 8日
2008-08-08 00:00:00
3、第一个参数为函数、公式
!A:A !B:B !C:C !D:D
4、第一个参数为2纬数组
姓名性别籍贯出生年月工号
张三丰男北京 1970年8月 KT001
李四光女天津 1980年9月 KT002
王麻子男河北 1975年3月 KT003

工号姓名籍贯用VLOOKUP联合TEXT从右往左查询
KT001 张三丰北京 TEXT({1,-1},）类似if({1,-1},,)的功能
5、第一个参数为多纬数组
优不及格优不及格优及格不及格及格及格

四、错误处理：例题请按住F9
TEXT函数只能简单处理错误值，对于出错的引用单元格本身不能在进行引用计算。
公式样式： =TEXT(--ISERROR(A1),"[=1]显示值1;显示值2")或者=TEXT(--ISERROR(A1),"[=0]显示值1;显示值2")
显示值里不能包含A1，否则A1为错误值时仍然出现错误值。
1、错误时显示为错误值,否则显示欢迎你，=TEXT(--ISERROR(K44),"[=1]错误值;欢迎你") 欢迎你
2、错误时显示为空值,否则显示另外单元格的值，=TEXT(--ISERROR(K44),"[=1]"""";[=0]"&E91+1) 46
3、错误时显示为0,否则显示另外单元格的值，==--TEXT(--ISERROR(K44),"[=1]!0;"&E91+1) 46
4、错误时显示另外单元格的值，否则也显示另外单元格的值，=TEXT(--ISERROR(K44),"[=1]"&D91+1&";"&E91+1) 46
5、错误时显示为错误值，否则显示原值,此时会出错，=TEXT(--ISERROR(K44),"[=1]错误值;"&K44) 10

五、TEXT函数带"0"、"."和"!"的解释：
1）=TEXT(2,"[=2]我0;[=1]你;-1;")，此处0是占位符，结果是：我2 我2
2）=TEXT(2,"[=2]我!0;[=1]你;-1;") ，此处0前有！，不是占位符，被强制显示，结果是：我0 我0
3）=TEXT(-222,"[=-222]3!00!00.00;[=1]"),此处第一和第三的0时被强制显示，其它为占位符， 302202.00
所以小数点前只有两位占位符，所以第二个0处显示22，第四个0处显示2，结果是：302202.00
4）TEXT(-2,"[=-2]我3!.0;[=1]"),此处小数点是被强制显示的字符，即在小数点前各有一个占位符0，结果是：我32.2 我32.2
5）=TEXT(-2,"[=-2]我3!0.0;[=1]")，此处第一个0被强制显示，只有“.0"才是占位符号，结果是：我302.0 我302.0
6)=TEXT(-22.22,"[=-22.22]3!0!.0;[=1]"),此处只有3后面的“0.”都是强制显示，只有最后一个才是占位符号， 30.22
实际上没有小数点及小数部分的占位符号，结果是：30.22
7）=TEXT(-22.666,"[=-22.666]3!0!.0.00;[=1]")，此处的后面的“0.0”才是占位符号，结果是：30.22.67 30.22.67
7)=TEXT(0.69,"[>0.2]0.5")，此处0.是占位符，0.69四舍五入为1.，结果是：1.5 1.5
解释：在TEXT函数中，如果在"0"和"."前面没有!(或者\)，就是占位符，否则就是被强制显示的字符。只有有了小数点占位符号（不是!.），
小数点后面的小数部分才能被显示，否则只显示整数部分，并且没有被显示的部分会自动四舍五入。
关于"0"、"."和"!"的详细解释请参考补充说明部分。

六、TEXT函数的参数中带“-”的解释:
1、TEXT函数的参数带一个"-"号的解释，见以下语句：
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 #VALUE! -3
4）=TEXT(-1,"-2;[=-1]-3")显示-3 -3 -3
5)=TEXT(-1,"[=1]-2;[=2]-3;-4")显示--4 --4 #VALUE! -4
6）TEXT(-1,"[=1]-2;[=-1]"&-3)显示-3 -3 -3
7)=TEXT(-1,"[=1]-2;[=2]-3;"&-45)显示--45 --45 #VALUE! -45
8)=TEXT(-1,"[=1]-2;[=-1]"&-3&";"&-4)显示-3 -3 -3
9)=TEXT(-1,"[=1]-2;[=2]-3;-你好;欢迎")显示--你好 --你好 #VALUE!
10)=TEXT("-1","-2;[=-1]-3")显示-3 -3 -3
个人解释：第一个参数的“-”作为运算符负号使用；如果第二个参数中[=第一参数]的条件出现，则第二个参数的“-”作为运算符负号使用；
如果第二个参数中[=第一参数]的条件没有出现，则作为字符符号使用并且如果且TEXT的条件只有一部分时，显示原值，有两部分以上时，则多显示一个运算符负号“-”。
2、TEXT函数的参数带两个"-"号的解释，见以下语句：
1)=TEXT(--1,"[=1]-2;[=-1]-3;-4")显示-2 -2 -2 2
2)=TEXT(--1,"[=1]--2;[=-1]--3;-4")显示--2 --2 #VALUE! -2
3)=TEXT("--1","[=1]--2;[=-1]--3;-4")显示--1 --1 #VALUE! -1
4)=TEXT(--1,"[=1]--2;[=-1]--3;-4")显示--2 --2 #VALUE! -2
5)=TEXT(--1,"[=-1]-2;[=1]"&--3)显示3 3 3
个人解释：两个“-”连用，如果“-”没有在引号内，都作为运算符负号使用；如果在引号内，紧挨着数字的一个作为运算符负号使用，另一个“-”作为字符符号使用。
3、TEXT函数的两个参数带两个以上"-"号连用，见以下语句：
1)=TEXT(---1,"[=1]--2;--3")显示---3 ---3 #VALUE! #VALUE! -3
2)=TEXT(----1,"[=1]--2;--3")显示--2 --2 #VALUE! -2 2
3)=TEXT(----1,"[=1]--2;--3")显示--3 --3 #VALUE! -3 3
4)=TEXT(---6,"[=1]-2;--3")显示-2 -2 -2 2 #VALUE!
5）TEXT(---6,"[=1]-2;--3;--4")显示-6 -6 -6 6 #VALUE!
6）=TEXT(----1,"[=-1]--2;--3")显示--3 --3 #VALUE! -3 3
7)=TEXT(---1,"[=1]--2;"&-3)显示--3 --3 #VALUE! -3
8)=TEXT(----1,"[=-1]--2;"&---3)显示-3 -3 -3 3
9)=TEXT("---1","[=1]-2;[=-1]--3;-4")显示---1 ---1 #VALUE! #VALUE! -1
10)=TEXT("----1","[=1]--2;[=-1]--3;-4")显示----1 ----1 #VALUE! #VALUE! #VALUE! -1
概括：
1）第一参数的“-”号如果多于1个且在引号内，则第一参数为字符。
2）第一参数的“-”号如果只有1个或者多于1个但没在引号内，则第一参数为数值。
3）如果第一参数的计算结果为正数，第二个参数的“-“不在引号内,"-"全部为运算符符号使用。第二个参数的“-“在引号内时,紧挨着数字的一个“-”作为运算符负号使用。
其它“-”作为字符符号使用。
4）如果第一参数的计算结果为负数，
4-1)第二个参数的“-“不在引号内时:当“-”为单数时，则有1个作为字符使用，其它作为运算符负号使用，双数时全部“-”作为运算符符号使用。
4-2)第二个参数的“-“在引号内，则紧挨着数字的一个“-”作为运算符负号使用，其它“-”作为字符符号使用；并且如果第二参数中[=第一参数]的条件没有出现，
此时还要看TEXT的条件格式有几部分：有两部分且第二部分没有条件时或者有三部分但第二部分有条件（=非第一参数）时，此时结果值前面还要多显示一个“-”字符。
4、条件格式是区间，第一参数为负数
1）=TEXT(-3,"[<-4]-4;-5")显示-5 -5 -5 5
2）=TEXT(-3,"[<3]4;-5")显示-4 -4 -4 4
3）=TEXT(-3,"[>-4]-4;-5;-6")显示--4 --4 #VALUE! -4
4）=TEXT(-3,"[<3]-4;-5")显示--4 --4 #VALUE! -4
5)=TEXT(-3,"[<-4]-4;"&---5&";-6")显示-5 -5 -5 5
6）=TEXT(-3,"[<-4]-4;[>4]--5;-6")显示--6 --6 #VALUE! -6
对第二参数的个人解释：
4-1）如果条件区间的范围跨度包含正负数（第一部分[]不满足条件且第二部分省略[]除外），就会多显示一个负号；如果条件区间仅在负数范围内，就不会多显示负号。
4-3）如果第一部分[]不满足条件且第二部分省略[]，此时，显示第二部分的原数值。
4-2）数字前的“-”都作为字符使用（第一部分[]不满足条件且第二部分省略[]除外）。

七、关于INDIRECT+TEXT(数字，"r0C00")此类格式的应用
1、解释：TEXT(102,"r0c00") 函数的计算结果为"r1c02"，前面再加个indirect函数，相当于INDIRECT("r1c02",)，计算结果就是等于1行2列即B1的值。
故展开该函数：
=INDIRECT(TEXT(1002,"r00c00"),)--->B10的值
=INDIRECT(TEXT(102,"r00c0"),)---->B10的值,注意前后的变化
=INDIRECT(TEXT(103,"r00c0"),)--->C100的值
=INDIRECT(TEXT(1003,"r00c0"),)--->C100的值
=INDIRECT(TEXT(901,"r00c0"),)--->A90的值
INDIRECT(TEXT({1681;1682;1683;1691;1692;1693;1701;1702;1703},"!r00c0"),)------->
得到一个数组INDIRECT({"r168c1";"r168c2";"r168c3";"r169c1";"r169c2";"r169c3";"r170c1";"r170c2";"r170c3"},)
1 4 7 45 45
2 5 8
3 6 9
2、怎么解决繁体版TEXT函数的R0C000用法问题？
问题：为什么在繁体版的Excel中使用INDIRECT+TEXT(数字，"R0C000")的解法不管用呢？怎样解决？
回答：繁体版的格式代码与R、C有冲突，需要加强制占位符！或\，用TEXT(数字，"!R0!C000")或者TEXT(数字，"\R0\C000")可以解决。

indirect与TEXT联合应用例子，将数字按S型排列：
indirect函数的使用，请参考论坛chenjun版主的文章：函数的深入理解。

本文参考论坛文章：
1、你未曾见过的TEXT函数用法
2、TEXT 函数应用技巧

华草宝木 · 发表于 2010-3-9 09:37

辛苦了。。。。。

zm0115 · 发表于 2010-3-9 09:40

辛苦楼主了，整理的很全面

laowuwyx · 发表于 2010-3-9 11:17

建议lz弄个word文档的，好阅读些。谢谢！

donghan · 发表于 2010-3-9 11:25

原帖由 laowuwyx 于 2010-3-9 11:17 发表
建议lz弄个word文档的，好阅读些。谢谢！

下载1楼附件

gxuan2008 · 发表于 2010-3-9 11:30

text用法比较多，久了就容易忘。

呵呵。谢谢楼主。

		自动登录	找回密码
密码			免费注册

[原创] 变化多端的TEXT函数

评分

点评

评分

浏览过的版块