二维码 购物车
部落窝在线教育欢迎您!

8个常用的Excel万能套路公式

 

作者:小窝来源:部落窝教育发布时间:2023-11-25 21:05:52点击:404

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。
编按:

介绍几个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

IMG_256

相关推荐:

Xlookup用法12

多区域查找R1C1

LOOKUP-LEFT-MID-FIND无往不胜

3关于连续统计方法

版权申明:

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