温州市琥阁电子商务有限公司

用SUMPRODUCT方便

发布日期:2024-06-25 10:16    点击次数:161

用SUMPRODUCT方便

图片

图片

编按:

介绍几个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精确查找的套路中。

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报。