您当前的位置:首页 > 生活百科 > 职场

Excel里去除重复值、统计数量的2种方法,快点学起来吧

时间:2020-06-11 13:45:01  来源:  作者:

让你的Excel效率开挂~(◦˙▽˙◦)

前段时间遇到这样一个问题,让我很头疼。

Excel里去除重复值、统计数量的2种方法,快点学起来吧

 

头疼的原因有 3 点:

❶ 问题描述不清晰,理解起来困难;

❷ 去重复计算数量,函数公式实现难度大;

❸ 提问的是个男生。

Excel里去除重复值、统计数量的2种方法,快点学起来吧

 

我尝试着把问题精简了一下,是这样的,你就凑活着看吧。

Excel里去除重复值、统计数量的2种方法,快点学起来吧

 

一列「用户 ID」,一列「活动日期」,现在想统计,每个用户参加活动的天数。

因为用户可能在 1 天中参加多次活动,所以要根据「用户 ID」对「活动日期」去除重复,然后再计数。

明白了吗?

明白了,咱们就开始干!

Excel里去除重复值、统计数量的2种方法,快点学起来吧

 

01

方法一

关于去除重复计数,也就是统计唯一值,Excel 中有一个经典的用法。

使用 SUMRODUCT 和 COUNTIF/COUNTIFS 函数完成。

= SUMPRODUCT(1/COUNTIF(统计区域,统计区域))

现在看不懂没关系,我们通过这个案例,一起走一遍这个过程。

❶ COUNTIFS 统计数量。

首先是统计数量,因为这里有「用户 ID」「活动日期」两列数据,所以我们用 COUNTIFS 函数。

Excel里去除重复值、统计数量的2种方法,快点学起来吧

 

完成公式如下:

=COUNTIFS(C2:C16,C2:C16,B2:B16,H2)

计算结果:

= {0;0;0;0;0;0;0;0;0;0;0;1;1;1;1}

注意:这里有一个数组的用法,在判断条件的参数中使用数组,那么计算的结果,也是对应数量的数组。

Excel里去除重复值、统计数量的2种方法,快点学起来吧

 

❷ 数量求倒数。

接下来,用 1 除以计数结果,获取对应的倒数。

Excel里去除重复值、统计数量的2种方法,快点学起来吧

 

完成公式如下:

=1/COUNTIFS(C2:C16,C2:C16,B2:B16,H2)

计算结果:

={#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;1;1;1}

▲左右滑动查看

因为「1/0」会出现「#DIV/0!」的错误,所以公式外面,再加一个 IFERROR 容错:

=IFERROR(1/COUNTIFS(C2:C16,C2:C16,B2:B16,H2),0)

计算结果:

={0;0;0;0;0;0;0;0;0;0;0;1;1;1;1}

这一步是非重复计数的关键操作,结合下一步倒数求和,会更容易理解。

❸ SUMPRODUCT 倒数求和。

因为 SUM 函数不支持数组操作,所以这里使用 SUMPROUDCT 进行求和。

Excel里去除重复值、统计数量的2种方法,快点学起来吧

 

完成公式如下:

=SUMPRODUCT(IFERROR(1/COUNTIFS(C2:C16,C2:C16,B2:B16,H2),0))

计算结果:

= 4

到这一步,你可能就明白求倒数的意义了。

如果相同数据出现了 2 次,那么计数过程就是「1/2 + 1/2 =2」;

如果出现了 3 次,就是 3 个「1/3」相加「=3」;

其他次数以此类推,即实现了非重复计数。

❹ 增加「用户 ID」判断。

但是上一步计算结果,显然是错的,QY1 的去重计数,应该是 1 才对啊。

这是因为计数的过程,没有对用户进行限制。

因为 QY1 有「10/4」的记录,所有的「10/4」都被统计到 QY1 用户上了。

Excel里去除重复值、统计数量的2种方法,快点学起来吧

 

所以需要再增加一个用户条件的判断,这里使用 EXACT 函数实现。

Excel里去除重复值、统计数量的2种方法,快点学起来吧

 

完整公式如下:

=EXACT(B2:B16,H2)

计算结果:

={FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}

▲左右滑动查看

EXACT 的作用,是判断两个数值是否相等。

因为 EXACT 中也是引用了区域,所以计算结果是一个 TRUE 和 FALSE 的数组。

接下来,是把 EXACT 的计算结果,作为条件添加到前面的公式中。

方法很简单,和第 1 步的计数过程相乘就可以了。

Excel里去除重复值、统计数量的2种方法,快点学起来吧

 

完成公式如下:

=SUMPRODUCT(IFERROR(1/COUNTIFS(C2:C16,C2:C16,B2:B16,H2)*EXACT(B2:B16,H2),0))

▲左右滑动查看

计算结果:

=1

这样就把非当前用户的统计给去掉了,也就得到了最终的结果。

02

方法二

方法 1 是传统的方法,经过一番折腾,最终算是圆满完成了需求。

但是正如你所感受到的,传统函数公式的思路太古怪,一般人很难想到用 1/次数的方法,来做去重计数。

 

这也是函数公式难学的主要原因。

正常用户的思路,不应该是这样的嘛?

❶ 筛选用户 ID

❷ 去除重复值

❸ 统计数量

这个正常的思路,用传统公式是很难实现的。

但是 office 365 中新增的 FILTER 和 UNIQUE 函数,让这个过程变的简单,变的正常了。

❶ 筛选用户 ID。

使用新增的 FILTER 函数,可以轻松的根据「用户 ID」筛选对应的记录。

Excel里去除重复值、统计数量的2种方法,快点学起来吧

 

完整公式如下:

=FILTER($C$2:$C$16,$B$2:$B$16=H4)

计算结果:

={43739;43739;43739;43740;43741;43742}

FILTER 的作用就是筛选符合条件的记录。

(日期返回的是数字格式,所以变成了 43739 的样子。)

❷ 去除重复值。

Office 365 中新增的 UNIQUE 函数,就是用来去除重复值的。

Excel里去除重复值、统计数量的2种方法,快点学起来吧

 

完整公式如下:

=UNIQUE(FILTER($C$2:$C$16,$B$2:$B$16=H4))

计算结果:

={43739;43740;43741;43742}

注意到了吗?FILTER 筛选出来的重复值,被 UNIQUE 函数一下子去除掉了。

❸ 统计数量。

有了去重后的筛选结果,统计数量太简单,就是普通的 COUNTA 函数嘛。

Excel里去除重复值、统计数量的2种方法,快点学起来吧

 

完整公式如下:

=COUNTA(UNIQUE(FILTER($C$2:$C$16,$B$2:$B$16=H4)))

计算结果:

= 4

简单的 3 个步骤,符合常规思路,你肯定一下子就学会了,不是吗?

03

总结

温馨提示:

FILTER 和 UNIQUE 函数,目前只有 Office 365 的版本才有,而且需要参加「预览版体验计划」。

今天的非重复计数学会了吧,别忘了点个赞!

私信回复关键词【工具】,获取Excel高效小工具合集!

让你的Excel效率开挂~(◦˙▽˙◦)

Excel里去除重复值、统计数量的2种方法,快点学起来吧


Tags:Excel   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
大家好, 前面我们讲过利用常规的方法,制作二级下拉列表辅助表。 今天我们就来介绍一下,Dax 度量值(Power Pivot)和 Power Query 的 M 函数做法,主要带大家拓展思路。 如下图,我们现...【详细内容】
2021-12-28  Tags: Excel  点击:(1)  评论:(0)  加入收藏
Excel常用电子表格公式大全   一、Excel基本公式   1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。   2、用出生年月来计算年龄公式:=TRUNC((DAYS360(H6,"200...【详细内容】
2021-12-21  Tags: Excel  点击:(11)  评论:(0)  加入收藏
前言越来越多开发者表示,自从用了Python/Pandas,Excel都没有打开过了,用Python来处理与可视化表格就是四个字——非常快速!下面我来举几个明显的例子1.删除重复行和空...【详细内容】
2021-12-16  Tags: Excel  点击:(21)  评论:(0)  加入收藏
哈喽大家好! 前几天一个朋友向我疯狂吐槽。 快到年底了,公司要统计年度数据。 需要把一月到十二月份,十二张表格的数据,全部汇总到一个表格内。 这样的数据汇报每年都会有,每到年...【详细内容】
2021-12-14  Tags: Excel  点击:(23)  评论:(0)  加入收藏
我发现最近不少小叶子的留言都和 Excel 相关,我寻思是时候出一期解决 Excel 疑难杂症的小合集了。于是毛毛在众多问题中,挑了三个被问次数最多的有关 Excel 的问题,今天就来给...【详细内容】
2021-12-14  Tags: Excel  点击:(30)  评论:(0)  加入收藏
当你需要每天对 Excel 做大量重复的操作,如果只靠人工来做既浪费时间,又十分枯燥,好在 Python 为我们提供了许多操作 Excel 的模块,能够让我们从繁琐的工作中腾出双手。今天就和...【详细内容】
2021-12-07  Tags: Excel  点击:(17)  评论:(0)  加入收藏
试想一下,用 Excel 管理项目的时候,会有很严格的日期安排,而且项目中的各细目经常是并行作业的,这就意味着日期不一定是排序的 。 那么事项太多如何更好管理,而不至于遗忘关键节...【详细内容】
2021-11-16  Tags: Excel  点击:(23)  评论:(0)  加入收藏
大家好,我是Python进阶者。前几天给大家分享了一些乱码问题的文章,阅读量还不错,感兴趣的小伙伴可以前往:盘点3种Python网络爬虫过程中的中文乱码的处理方法,UnicodeEncodeError:...【详细内容】
2021-11-01  Tags: Excel  点击:(34)  评论:(0)  加入收藏
经常用 Excel 的表哥表姐们,想必都知道「下拉菜单」这个神器,鼠标点点点,就能轻轻录入数据:▲ 一级下拉菜单 它的制作方法也很简单,用【数据验证】功能可以直接实现! 有小伙伴表示...【详细内容】
2021-10-27  Tags: Excel  点击:(41)  评论:(0)  加入收藏
在工作中, 我们需要对业务人员的业绩进行跟进,会有如下格式的跟进表 每天要进行更新表格的时候,表头都要重新手动的进行输入,比较麻烦,今天教大家的技巧是使用公式进行自动更新首...【详细内容】
2021-10-26  Tags: Excel  点击:(42)  评论:(0)  加入收藏
▌简易百科推荐
毕业后不重视自己的档案,等到考研、考编、考公务员、单位入职等需要用到档案时,才想起来查询自己的档案。但是,很多人查询档案没有经验,不知道该从何查起。下面给大家介绍查询个...【详细内容】
2021-12-23  帮帮团人力资源    Tags:个人档案   点击:(14)  评论:(0)  加入收藏
评职称可谓是工程人事业发展中的一件大事了,可以说一般想要在行业中持续地、更好地发展的人都会选择评个中级职称! 怎么评广东省建筑中级职称? 在评审时工程业绩最为重要。那...【详细内容】
2021-12-23  资深职称老师—小丽    Tags:职称   点击:(4)  评论:(0)  加入收藏
职场中,事情做得漂亮,不一定结局漂亮;但是善于谋人,把人打通了,出手一般就是巅峰。人情社会尤其如此,说到底工作是人定的,好不好也是人说的,有人为你说话,你就是能力强。没人看到你,工...【详细内容】
2021-12-22  胖子说职场经验    Tags:职场   点击:(4)  评论:(0)  加入收藏
一、在国企,能改变命运的只有你自己。你想改变,就总有办法。你认命,就不要埋怨命运不公。多少领导一样是从基层爬上去的。也许你会说,他们背后有人。我也不反对,但总有那么20%左...【详细内容】
2021-12-21  职场真谛    Tags:国企   点击:(6)  评论:(0)  加入收藏
又到年底了,有更好的工作选择?想跳槽?社保咋处理?以及社保需要注意的小问题是什么?一文全理清!一、打工人离职手册之社保全指南 二、需要注意的社保小问题 ...【详细内容】
2021-12-17  恒企会计网校    Tags:离职指南   点击:(6)  评论:(0)  加入收藏
在个案辅导中,也经常遇到公务员面试前的准备和辅导。首先,我其实挺想吐槽公考的笔试和考试机制的,让我先一吐为快。公务员考察的面非常多,从表达能力这种表面的,到价值观这种底层...【详细内容】
2021-12-14  为好优姐姐    Tags:公务员面试   点击:(12)  评论:(0)  加入收藏
公务员面试形式进行了创新,增加了结构化小组面试这一形式,在结构化的基础上增加了考试互评和回应的环节,这一改变增加了考试难度,也给许多考试造成了困惑,那今天就结构化小组的点...【详细内容】
2021-12-14  红河华图教育    Tags:公务员面试   点击:(14)  评论:(0)  加入收藏
在各级党政机构之中,我们经常会听到一个称呼——“常务副职”,例如县政府有常务副县长,组织部有常务副部长等等。其实,常务副职只是一个约定俗成的简称,其准确名称叫做...【详细内容】
2021-12-14  瑛杰小猪  今日头条  Tags:常务副职   点击:(19)  评论:(0)  加入收藏
在职场,什么都可以没有,就是不能没有情商。没有情商的人,在职场注定难成大器。人际关系搞不定,说话口无遮拦,为人处世更是不够圆滑,处处受限,处处是破绽。尤其是和领导相处,连对方的...【详细内容】
2021-12-14  第一桶金学派    Tags:领导   点击:(8)  评论:(0)  加入收藏
在职场,除了个人的工作能力以外,还要学会去不断的积累自己的人际关系。因为有了关系,就有了渠道,有了机会,有了方法,有了财富……越是和厉害的人交往,你自己也会变得越...【详细内容】
2021-12-10  第一桶金学派    Tags:职场   点击:(12)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条