最重要的文本函数text的进阶用法1
1.分数等级判断
如下图所示,根据右表的标准对左表的成绩判断等级。昨天也做过类似的题目,但是只有3个条件,今天这个题目有4个条件。在C2单元格中输入公式=TEXT(B2,"[>=90]优;[>=80]良;"&TEXT(B2,"[>=60]中;差")),向下填充。
![](http://n4.ikafan.com/assetsj/blank.gif)
这里是text的嵌套用法,和if函数的嵌套在作用上是一样的,但是要注意它的写法,用&连接起来。实际上就是将里边的text函数的结果作为外边的text的第3个条件。
如果有更多的条件判断,多嵌套几个text也能搞定。
第2种解法,在D2单元格中输入公式=TEXT(0&B2-60,"[>=30]优;[>=20]良;中;差"),向下填充。这个text函数的第2参数有4部分代码,前面的3部分是对数字设置格式,最后的差是对文本设置格式。
![](http://n4.ikafan.com/assetsj/blank.gif)
第1参数为0&B2-60,这个的作用是将小于60分的转成文本,那么对应的格式就是差。以40分为例说明,0&40-60就是0&-20,最后的结果是"0-20",这样就变成文本。如果是90,那么0&90-60的结果就是"030",但是text会将"030"转成30,然后再对其设置格式。
第1参数减去60,那么第2参数的每个等级也要减去60。这种方法比较奇特,充分运用了text函数的特性和数学的思维方式,但是它只能对4个条件进行判断。如果是更多的条件判断,就要text的嵌套或者是其他方法来完成。
2.提取单个数字并求和
如下图所示,A列是一些字符串,有数字字母和汉字,求每个单元格的数字之和。以A12单元格为例说明,求2+5+0+3+4的和。在B11单元格中输入公式=SUM(--TEXT(MID(A11,ROW($1:$20),1),"0;;0;!0")),按ctrl+shift+enter三键,向下填充。
![](http://n4.ikafan.com/assetsj/blank.gif)
这里用的是数组用法,先用mid将每个字符一一提取出来,作为text的第1参数,然后对其设置格式。这里的关键是text的第2参数,"0;;0;!0"有4部分,正数显示它本身,负数显示为空,0显示为0,文本强制显示为0。
由于提取出来的都是1位,不可能有负数的,而文本通过!0强制显示为0,这样就将文本转为0。由于text得出的结果是文本,直接用sum求和是0,所以用--转为数字,再求和。
3.计算2个日期时间相差几天几小时几分钟
如下图所示,有起始日期的时间和结束日期的时间,计算它们相差多少天多少小时多少分钟。在C17单元格中输入公式=TEXT(B17-A17,"d天")&TEXT(MOD(B17-A17,1),"h小时m分钟"),向下填充。
![](http://n4.ikafan.com/assetsj/blank.gif)
先用结束日期减去起始日期,得到一个数字,这个数字就是它们相差的天数,整数部分就是整天,小数部分就是不满一天的天数,将其化成小时数和分钟数。
TEXT(B17-A17,"d天")这部分就是整天数,B17-A17可能是小数,为什么不用int取整呢?原因就是第2参数的d,它只会取整天数。
TEXT(MOD(B17-A17,1),"h小时m分钟")这部分就是将不满1天的时间转成小时和分钟数。MOD(B17-A17,1)是取2个日期的差的小数部分。
如果对你有所帮助或启发,请打赏或分享一下,你的支持就是我最大的动力!
![](http://n4.ikafan.com/assetsj/blank.gif)
关注解锁更多函数的用法