如图所示,我们事件中提取ABCDE,再根据成绩评价转化表将评价填入评价列中,结果如下图:
方法一:
IF(ISNUMBER(FIND("A",B3)),"优秀","...") 函数
在B3中输入以下语句,并填充
=IF(ISNUMBER(FIND("A",B3)),"优",IF(ISNUMBER(FIND("B",B3)),"良",IF(ISNUMBER(FIND("C",B3)),"中",IF(ISNUMBER(FIND("D",B3)),"及格",IF(ISNUMBER(FIND("E",B3)),"不及格","")))))
优点:容易理解,适合条件分支较少的情况
缺点:可读性差,不易扩展
方法二:
LOOKUP(9^9,FIND({"A","B","C","D","E"},B3),{"优","良","中","及格","不及格"}) 函数
在B3中输入以下语句,并填充
=IF(ISERROR(LOOKUP(9^9,FIND(Sheet1!E$3:E$7,B3),Sheet1!F$3:F$7)), "", LOOKUP(9^9,FIND(Sheet1!E$3:E$7,B3),Sheet1!F$3:F$7))
参考文章:
https://jingyan.baidu.com/article/5d368d1e8e430b3f61c0576c.html