介绍几个Excel万能套路公式，包括求和、查找、拆分提取、去重统计、排名等方面。

隔列汇总

按条件隔列累加，用SUM或者SUMPRODUCT方便。

=SUMPRODUCT((B25:E25="a")*B26:E34)

=SUM((B25:E25="a")*B26:E34)(低版本要三键输入)

说明：将条件判断作为其中一个数组，然后乘以求和区域，最后累加。

统计不重复个数

=SUMPRODUCT(1/COUNTIF(A38:A46,A38:A46))

说明：先用COUNTIF获得每个数据的重复次数；然后用1除以各个重复次数；最后累加。

身份证等超长数字查重

超过15位的数字在查重的时候用COUNTIF不好使，用SUMPRODUCT方便。

=IF(SUMPRODUCT(($B$50:$B$57=B50)*1)>1,"重复","")

说明：用比较符号进行判断可以规避数字超过15位最后几位都当作0进行处理的不足，从而准确判断是否存在重复。

中国式排名

中国式排名即便有并列，名次也是连续的。

说明：实际就是统计大于等于当前成绩的不重复分数的个数。所以把统计不重复数公式的分子1变成了一个条件判断。

水平和垂直双向查找

一个条件需在水平方向上查找，一个条件需在垂直方向上查找。

低版本：=VLOOKUP($F$72,$A$72:$D$79,MATCH(G71,$A$71:$D$71,0),)

高版本：=XLOOKUP($F$72,$A$72:$A$79,XLOOKUP(G71,$B$71:$D$71,$B$72:$D$79))

提取汉字和字母

如果数据中只有字母和汉字，可以判断是否比“啊”大来拆分汉字和英文。

提取英文：=TRIM(CONCAT(IF(MID(A83,ROW($1:$50),1)>="啊"," ",MID(A83,ROW($1:$50),1))))

提取汉字：=TRIM(CONCAT(IF(MID(A83,ROW($1:$50),1)<"啊"," ",MID(A83,ROW($1:$50),1))))

说明：在Excel中所有中文大于英文，中文中最小的又是“啊”字，因此可以逐个拆出字符与啊字比较，从而判断出是英文还是中文。

提取任意位置的一串数字

提取字符中任意位置的一串数字。

=CONCAT(IFERROR(--MID(A89,ROW($1:$50),1),""))

说明：逐个提取字符并做双负运算，数字保留，不是数字的变成空。

多对一查找

多对一查找，低版本用LOOKUP套路最方便；高版本就用XLOOKUP的条件合并。

=LOOKUP(1,0/((A95:A102=E95)*(B95:B102=F95)),C95:C102)

=XLOOKUP(E95&F95,A95:A102&B95:B102,C95:C102)

说明：用LOOKUP多条件查找，主要就是把多个条件判断进行相乘，并用在LOOKUP精确查找的套路中。

本文配套的练习课件请加入QQ群：902294808下载。

做Excel高手，快速提升工作效率，部落窝教育Excel精品好课任你选择！

扫下方二维码关注公众号，可随时随地学习Excel：

相关推荐：

Xlookup用法12例

多区域查找用R1C1

LOOKUP-LEFT-MID-FIND无往不胜

3种关于连续的统计方法

版权申明：

本文作者小窝；部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。