ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

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

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2010-3-9 11:35 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖已被收录到知识树中,索引项:TEXT
昨天我还在找Text 函数的基本用法呢, 今天你就发出来了

真是及时雨啊,谢谢!! 晚上回去认真研究!

TA的精华主题

TA的得分主题

发表于 2010-3-9 12:30 | 显示全部楼层
楼主,麻烦详细解析下
=TEXT(A1-60,"[>=20]优秀;不及格;及格")

然后再进一步解析下
=TEXT(TEXT(D4-60,"[>=30]优秀;不及格;0"),"[>=10]良好;及格")

这两个公式版权属大理版主。
为什么条件格式和[正数;负数;0;文本]可以交互使用,交互使用的规则是什么?麻烦楼主解释下,我看你的条件格式那块,没有考虑到这种情况。

TA的精华主题

TA的得分主题

发表于 2010-3-9 13:04 | 显示全部楼层

回复 3楼 donghan 的帖子

时间分隔符、日期分隔符确实和系统设置有关,不过似乎不影响text函数吧,这里会严格按照指定的数字格式显示。除通用格式外,能想到的,也就是长日期格式代码“[$-F800]”还有时间格式代码“[$-F400]”是与系统设置密切相关。其他还有什么明显的例子么?

e,与语言设定有关的,并不是必然等于yyyy,所以不确定打开文件的Excel环境的话,最好不要用e代替yyyy。
没有提到g,这个是年号,在简体中文版中没有相关内容可用来显示,给个例子“[$-411]ggge”,这个格式可以显示日本年号纪年,比如1991年的日期会显示成“平成3”,两个g就用年号的简写,一个g使用一个英文字母表示年号
(上面的例子在我的系统中可以实现,具体需要哪些要求,是否会失效不太清楚)
还有aaa和aaaa,这就是本地化的ddd和dddd,当指定了区域语言后,就和d没区别了

关于b,这个大小写无关,除了佛历外,B1和B2也是有特殊含义的,他们本身不显示任何东西,但是控制后面的y,m,d等所采用的日历,B1是Gregorian历,也就是现行的公历,忽略区域语言设置,B2是一种回历(伊斯兰历法)

关于千位分隔符,这个要写在小数点左边的0或#之间,写在小数点之前或者写在末尾,都表示除以1000的意思,国际单位制的词头大多是1000倍一个,此项功能用于更改单位。写在数字最前面,就直接显示“,”,写在小数点后但不是结尾,则忽略,至少我现在手中的Office2003sp3和2007都是如此,以前好像也有版本写在小数点后的0或#间也表示千位分隔符。

!未必就相当于\,好像和语言版本有关,尽量用\

@和&解释错误,用了VBA中的FORMAT函数的说明。在这里,@就表示原来的整个文本串,写在哪里,就是把原先的文本串显示在哪里,&没有特殊作用

科学记数只能用大写的“E+"、“E-”,小写不行,估计是M$增加了"e"的含义后,忘了改帮助文件

G/通用格式 ,这个表达方式与语言版本有关,尽量不用。

[DBnum1]~[DBnum4]应该是与系统有关的四套数字表示,这里的说明应该都是针对简体中文版的。
下面的列表也未必对或者全面,忘了出处了(测试也不方便,也需要有相应的系统才行):
DBnum1:
Chinese: Chinese lower case characters;
Japanese: short Kanji characters;
Korean: Korean lower case characters ;
DBnum2:
Chinese: Chinese upper case characters;
Japanese: traditional Kanji text;
Korean: Korean upper case characters;
DBnum3:
fullwidth Arabic digits
DBnum4:
Korean: formal Hangul text

关于区域代码,曾在微软网站上查到过相关说明,这里简述代码的结构
比如[$-24030412]
24是数字格式代码, 03是日历类型代码,最后四位是LCID(区域及语言代码)

可见日历是比较乱的,早期版本只有y,m,d,后来逐渐考虑各地不同,97版开始用unicode,应该在那之后才考虑的这些,B1,B2什么的,应该是较早时候定下的方案,DBnum1~Dbnum4估计也是,用[$-xxxxxxxx]形式则是最新的统一方案。(数字格式代码里面,1B~1D是日语1~3,1E~20表示简体中文1~3,21~23是繁体中文1~3,24~27是朝鲜语1~4,这些应该就是以前的DBnum1~DBnum4,DBnum4应该主要用于韩语版本,在我们这里无效,不过[$-27xxxxxx]有效)

[ 本帖最后由 子元女王 于 2010-3-9 16:04 编辑 ]

TA的精华主题

TA的得分主题

 楼主| 发表于 2010-3-9 13:05 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
=TEXT(A1-60,"[>=20]优秀;不及格;及格")
如果A1-60>=20显示优秀,如果A1-60为负数(即A1<60)显示不及格,其他值显示及格

TA的精华主题

TA的得分主题

发表于 2010-3-9 13:17 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2010-3-9 13:42 | 显示全部楼层
=TEXT(TEXT(D4-60,"[>=30]优秀;不及格;0"),"[>=10]良好;及格") 这里假设分数全都是整数,没有小数
这是TEXT函数嵌套的经典例子,首先解释里面的TEXT:
如果D4-60>=30显示优秀,D4-60<0显示不及格,其他值显示为D4-60的四舍五入整数结果
里面的TEXT条件将数值分为D4分为D4>=90、D4<60、60<=D4<90三个范围
再解释外面的TEXT:
如果里面的结果为“优秀”或者“不及格”,则为文本,最后显示为“优秀”或者“不及格”;
如果为数值(即60<=D4<90),则当TNT(D4-60+0.5)>=10时显示良好,否则(即TNT(D4-60+0.5)<10)显示及格
经过外面的TEXT后又把D4的范围分为70<=D4<90和60<=D4<70

TA的精华主题

TA的得分主题

 楼主| 发表于 2010-3-9 13:46 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
带[  ]的格式和[正数;负数;0;文本]格式可以交互使用,交互使用的规则是:如果有[  ],则按[  ]里面的条件判定(每个TEXT仅限前两部分带[  ]有效),如果没有[  ],则按默认的[正数;负数;0;文本]格式判定

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

TA的精华主题

TA的得分主题

发表于 2010-3-9 13:52 | 显示全部楼层

TA的精华主题

TA的得分主题

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

TA的精华主题

TA的得分主题

 楼主| 发表于 2010-3-9 14:33 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

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

GMT+8, 2024-12-23 19:49 , Processed in 0.037329 second(s), 5 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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