一组常见的Excel套路公式

Excel教程 2023-09-22

分享一组实的套路公式，小白遇到类似的情况也可以依葫芦画瓢直接套用。

关键字查询：VLOOKUP+通配符

如果只知道要查找内容的部分关键字，可以用通配符代替不确定部分。

例如要查找“成都”，而实际数据中是“四川成都店”，可以在关键字的前后加上通配符“*”：

=VLOOKUP("*"&D2&"*",A:B,2,0)

关键字查询：VLOOKUP+通配符

SUMIF或SUMIFS条件求和时，如果只知道条件中的部分关键字，可以用通配符代替不确定部分：

=SUMIF(A:A,"*"&D2&"*",B:B)

关键字条件求和：SUMIF+通配符

根据不同条件输出不同结果，IF嵌套是常规方案，但如果在2019及以上版本中可以用IFS，一个条件对应一个结果，依次读取执行：

=IFS(B2<=60,"不及格",
        B2<=80,"良好",
        B2<=99,"优秀",
        B2=100,"人才")

条件判断：IFS

TEXTJOIN是当前最好用的文本合并函数，第一参数是用于间隔文本的字符，第二参数可以选择是否忽略空值，第三参数极其后面的参数用于存放需要合并的文本。

如下案例中，IF返回满足条件的文本，再用TEXTJOIN合并：

=TEXTJOIN(",",TRUE,IF(B2:B9>2000,A2:A9,""))

文本合并：TEXTJOIN

IFERROR是一个很实用的函数，用于避免错误值的产生。

如下案例中，将0作为除数时会返回错误值，用IFERROR将其转换为指定文本。

=IFERROR(A2/B2,"有误")

屏蔽错误值：IFERROR

UNIQUE第三参数设置为TRUE,返回只出现一次数据。

=UNIQUE(A2:A11,,TRUE)

只出现一次的值：UNIQUE

TEXT是一个灵活性极强的函数，特别是对于日期和时间数据，可以转换为多种格式：

=TEXT(A2,"yyyy年m月d日，h点m分s秒")

日期格式转换：TEXT

Excel 365版本函数TEXTAFTER用于提取指定字符后面的文本：

=TEXTAFTER(A2,"-")

文本提取：TEXTAFTER