text函数是excel中功能极为强大的文本函数,它不仅能对数据进行计算,也可以对文本进行格式的调整,它的作用可能远比你想象的要多,更灵活和有效。
对text函数的详解,一篇文章是远远不够的,这里,作者先介绍一下使用text函数来进行多条件判断的用法。
在下面表格数据中,作者使用了lookup函数来引用分数与成绩水平的匹配区间,并得到了平均成绩相对应的成绩水平。
但lookup函数需要建立一个辅助列,也就是右侧红框中的两列数据,而且这个函数的应用,相对复杂一点,是excel中属于常用但不大好理解的函数,尤其在条件判断的场景中。
那我们如果用text函数来匹配平均成绩的成绩水平,该如何操作?
我们先来看下text函数的语法:
看上去比较简单,就两个参数,第1参数是值,第2参数是要设置的数值格式。
所以它的作用就是将数值转换为指定的数值格式所表示的文本,因此它实际还是一个文本函数。
按照text函数的语法,我们先输入一个公式:=TEXT(F3,”[>90]优秀”)
值为F3中的数值,要调整的格式为”“[>90]优秀”“,这串文本啥意思?
我们可以将其视为一个固定的表达,[>90]的含义是单元格数值大于90,”优秀”这个文本的含义是代表前面条件返回的结果,也就是将当值大于90,则结果为”优秀“。
但以上只是一个条件,如果要设置多个条件,又要怎么写?
如上图所示公式:=TEXT(F3,”[>90]优秀;[>80]良好;及格”)
每个条件都用”;“进行分隔,也就是当大于90分,为优秀,大于80分,为良好,否则为及格。
但这个逻辑,漏掉了当大于60分时,为及格的条件。
然而text函数第2参数中第4个条件如果按照上面的写法添加,其计算结果是错误的。
那第4个条件该怎么解决呢?
可以通过嵌套一个if函数,即:=IF(F3<60,”不及格”,TEXT(F3,”[>90]良好;[>80]及格;及格”))
所以text函数可以完美解决三个条件的判断,来返回对应的文本,但进行四个条件的判断,则更适合在其他的场景中,如下图所示,当有学生缺考时,其成绩显示为缺考,而且分数只设置了三个区间,如图中右侧所示:
在这个场景中,前3个条件按照之前的写法输入,第4个条件,也就是缺考时,返回”无成绩“。
公式为:=TEXT(F3,”[>85]良好;[>60]及格;不及格;无成绩”)
为什么当成绩为缺考,text函数4个条件判断又可以输出结果了呢?!
因为text函数第2参数最基本的表达式其实有一个固定写法,即=text(值,”大于0;小于0;0;文本“)。作者将它称为4个值格式。
也就是当数值大于0时,将返回第2参数中的第1个值格式,小于0时,返回第2个值格式,等于0则返回第3个值格式,而当数据为任意的文本时,则返回我们设定的一个文本值。
所以,当学生缺考,结果返回第4个值格式的结果,即为”无成绩“这个文本结果。
但原本的固定写法不是讲一定要大于或小于等于0,而是要关注到前3个条件都是数值,不管大于0还是小于0,它都是数值,只有第4个条件是文本。
所以我们更要注意的是条件对应的值的格式,这样我们再回到表格数据中,通过前3个数值格式的条件设置和最后文本值格式的条件设置,来判断数据要返回的结果。
尽管从上述文字中或许无法清楚地理解它的含义,但我们只要记住它的固定表达,即前2个值格式可以设置数值的条件区间,第3个值格式返不符合条件区间的结果,第4个值格式返回数据为文本时的结果。
以上就是今天关于text函数多条件判断的介绍,重点在于第2参数的写法和应用,童鞋们可以手动练习,其实还颇有意思。