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

比较提取不重复项的5个常用套路和最强UNIQUE函数法

 

作者:郅龙来源:部落窝教育发布时间:2021-06-09 09:46:30点击:5229

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

编按:

在日常工作中,我们如何提取和计算不重复项?下面,小E将给大家盘点解决此类问题的六大操作方法,除了筛选法、删除法、数据透视表法、常用公式套路法等,更有一个office-Excel 365发布的最强函数,让你1秒得到精准结果!

 

做数据统计的朋友平时经常遇到求和、平均值、最大值、最小值这些核算问题,了不起再来个条件求和、条件计数等等。这些问题用对应的统计函数都很容易搞定,即便函数不太熟练,用透视表也都是分分钟的事情。

 

但是,笔者最近从小伙伴们在群里的提问中发现,大家觉得“统计不重复项的计数问题”很难。所以,笔者今天用案列详细讲解一下这个问题。

 

通常对于不重复项计数有两种途径:操作+公式、纯公式。

 

例如,图中这个数据源,B列有一组姓名,实际上只涉及到3个人,需要怎么计算?

 

 

下面,笔者将用不同的方法把这个问题聊透。

 

第一类途径:操作+公式

逻辑分析:先把剔除重复项后的数据单独列出来,然后用最简单的计数函数统计(有时直接用眼睛也能看出结果)。因此只要明白了删除重复项的方法,得到结果就不是问题。

 

通常有三种方法:高级筛选、删除重复项、数据透视表,它们都是比较基础的操作。

 

下面,开始逐个演示。

 

1.高级筛选法。

 

注意:只选择姓名所在的单元格区域。

 

2.删除重复项法。

 

注意:因为不能破坏数据源,所以需要先把姓名这一列单独复制出来,再删除重复项。

 

3.透视表法。

 

以上三种方法,都能一眼看出正确结果,但是如果不重复项数量过多,还需要大家进行求和操作。那么,有没有可以一步完成汇总的操作呢?有,那就是公式法!下面介绍2个常用公式套路,和一个最强的函数——UNIQUE的使用!

 

第二类途径:纯公式

根据Excel版本不同,公式法也有至少三个思路。

 

1.一对非常经典的函数组合套路,SUMPRODUCT函数与COUNTIF函数组合。

 

就本例而言,公式=SUMPRODUCT(1/COUNTIF(B2:B15,B2:B15))统计出不重复的人数,结果如图所示。

 

 

公式解析:

①公式中,COUNTIF(B2:B15,B2:B15)统计出了每个人在区域中出现的次数。

COUNTIF的第二参数使用的不是一个单元格而是一个区域,所以得到的结果也是多个值(需特别注意)。

 

 

③1/COUNTIF(B2:B15,B2:B15)这部分是对每个姓名出现的次数进行平均。

例如,夏淼一共出现了5次,那么每次的平均值都是1/50.2),最后将五个1/5相加为1,也就是一个人。

对每个人都按这样计算一遍,最后得到的就是实际不重复的人数了。这个计算过程可以用F9功能键去进行分析,如图所示。

 

 

这个方法其实是一种数学逻辑的应用,除此之外,还有一个经典的套路,也能统计不重复数据的个数

 

2.套路2,使用COUNTMATCH函数进行组合。

公式为:=COUNT(0/(MATCH(B2:B15,B2:B15,0)=ROW(1:14)))。这个公式是数组公式,需要按CtrlShiftEnter完成输入,结果如图所示。

 

 

公式解析:

①公式中MATCH(B2:B15,B2:B15,0)的作用是对B2:B15中的每一个姓名做了一次定位,会得到一组数字{1;2;1;2;1;6;1;2;6;1;2;6;1;2}

如果有重复的姓名,得到的都是这个姓名第一次出现的位置序号,如图所示。

 

 

②ROW(1:14)的作用是得到与数据源姓名行数相同的自然数序列,本例有14行数据,所以是1:14

③MATCH(B2:B15,B2:B15,0)=ROW(1:14)得到一组逻辑值,通过下图可以看出,相同姓名只有第一次出现时得到TRUE

 

 

④0/(MATCH(B2:B15,B2:B15,0)=ROW(1:14))则得到一组包含0和错误值的数据,只有TRUE对应的位置是0FALSE对应的位置都是错误值,如下图所示。

 

 

⑤最后由COUNT统计出数字的个数。

 

3.如果你使用的是Excel365版本,那么恭喜你,你可以直接使用最新的函数来1秒完成统计。

UNIQUE搭配COUNTA轻松实现不重复项的计数,公式格式为:=COUNTAUNIQUE(单元格区域))。

 

在这里写作:=COUNTA(UNIQUE(B2:B15))

 

注意:除了365版本之外,都不能用这个公式!Excel2016版可能不报错,但是结果是不对的

 

 

此外,关于office-Excel 365更多的新函数教程,大家可以参阅之前的教程:

12个 Office 365版本新增功能,速度围观!Excel粉丝们看过来!

 

如果大家还没有office-Excel 365,还是需要掌握上面两个比较传统的公式套路及其原理哦~

 

总结:

与套路1不同,套路2是非常经典的一种数组计算应用,也是高手们非常喜欢的用法。可是纵观以上所有的方法,最简单高效的方法是用最新的函数UNIQUE并搭配COUNTA

 

不知道今天的教程你收获了多少,欢迎分享。

 

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

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

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

IMG_256

相关推荐:

新函数UNIQUE提取不重复项比透视表更简单高效

两个神仙技巧,带你看破excel统计不重复数的秘密

两个神仙技巧,带你看破excel统计不重复数的秘密(下篇)

1分钟搞定不重复数统计

版权申明:

本文作者郅龙;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。