ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 效率神器,一键搞定繁琐工作
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
让更多数据处理,一键完成 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 181740|回复: 303

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

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2010-3-9 09:29 | 显示全部楼层 |阅读模式
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖已被收录到知识树中,索引项:TEXT
虽然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 编辑 ]

变化多端的TEXT.rar

54.86 KB, 下载次数: 11397

评分

14

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2010-3-9 09:32 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
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                       

TA的精华主题

TA的得分主题

 楼主| 发表于 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 编辑 ]

TA的精华主题

TA的得分主题

 楼主| 发表于 2010-3-9 09:35 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
格式举例部分参考附件:

TA的精华主题

TA的得分主题

 楼主| 发表于 2010-3-9 09:36 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
高级应用部分:
一、在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 函数应用技巧

点评

笔误: =INDIRECT(TEXT(103,"r00c0"),)--->C100的值 应是C10的值  发表于 2012-6-27 17:19

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2010-3-9 09:37 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2010-3-9 09:40 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
辛苦楼主了,整理的很全面

TA的精华主题

TA的得分主题

发表于 2010-3-9 11:17 | 显示全部楼层
建议lz弄个word文档的,好阅读些。谢谢!

TA的精华主题

TA的得分主题

 楼主| 发表于 2010-3-9 11:25 | 显示全部楼层
原帖由 laowuwyx 于 2010-3-9 11:17 发表
建议lz弄个word文档的,好阅读些。谢谢!

下载1楼附件

TA的精华主题

TA的得分主题

发表于 2010-3-9 11:30 | 显示全部楼层
text用法比较多,久了就容易忘。
呵呵。谢谢楼主。
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-12-23 15:20 , Processed in 0.039800 second(s), 9 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表