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 | |
|
|
|
|
补充说明部分: 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的第二参数中想写空白""要敲四下 | |
|
|
|
|
高级应用部分: 一、在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 函数应用技巧 | |
|