2岁宝宝发型女短发:TEXT 函数
来源:百度文库 编辑:偶看新闻 时间:2024/05/22 01:44:40
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 donghan 当前离线 4093财富 5鲜花 22技术
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 donghan 当前离线 4093财富 5鲜花 22技术 donghan donghan 当前离线 4093财富 5鲜花 22技术
一、在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 函数应用技巧
说明
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 将秒显示为不带前导零的数字。
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
举报
- 在线时间
- 582 小时
- 经验
- 879
- 威望
- 0
- 性别
- 保密
- 最后登录
- 2012-1-30
- 注册时间
- 2009-4-17
- 阅读权限
- 70
- UID
- 773370
- 积分
- 2270
- 帖子
- 886
- 精华
- 1
- 评选资格
- False
- 等级 5EH铁杆 EH铁杆, 积分 2270, 距离下一级还需 930 积分
- 积分排行
- 403
- 帖子
- 886
- 精华
- 1
- 分享
- 0
- 串个门
- 加好友
- 打招呼
- 发消息
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 编辑 ]
举报
- 在线时间
- 582 小时
- 经验
- 879
- 威望
- 0
- 性别
- 保密
- 最后登录
- 2012-1-30
- 注册时间
- 2009-4-17
- 阅读权限
- 70
- UID
- 773370
- 积分
- 2270
- 帖子
- 886
- 精华
- 1
- 评选资格
- False
- 等级 5EH铁杆 EH铁杆, 积分 2270, 距离下一级还需 930 积分
- 积分排行
- 403
- 帖子
- 886
- 精华
- 1
- 分享
- 0
- 串个门
- 加好友
- 打招呼
- 发消息
举报
- 在线时间
- 582 小时
- 经验
- 879
- 威望
- 0
- 性别
- 保密
- 最后登录
- 2012-1-30
- 注册时间
- 2009-4-17
- 阅读权限
- 70
- UID
- 773370
- 积分
- 2270
- 帖子
- 886
- 精华
- 1
- 评选资格
- False
- 等级 5EH铁杆 EH铁杆, 积分 2270, 距离下一级还需 930 积分
- 积分排行
- 403
- 帖子
- 886
- 精华
- 1
- 分享
- 0
- 串个门
- 加好友
- 打招呼
- 发消息
一、在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 函数应用技巧
求Excel中TEXT函数的用法
表示TEXT文本字符个数的函数是什么?LABEL中的表示CAPTION个数的是什么?
在VB里,要读取TEXT控件中输入字符的长度可以用什么函数?
text是什么
splitfile(txtfilename.text,msg,clng(txtfilelength.text))
函数~~~~~~~~~~~~
函数......
函数!!!!!!!!!!!
函数!!!!!!!!!!!
函数......
text音乐播放软件
html <--> text
val(text2.text)
text/html
text-decortion:none 是什么意思
text-decortion:none 是什么意思!
谁知道text/html
谁知道text/html
com.lowagie.text
什么叫Sample Text
WRONG TEXT!是什么意思?
JS(select联动text)
JS(select联动text)
text-returnable是什么计算机