当前位置:首页 > 话题广场 > 攻略专题 > 游戏问答

03版excel怎么排除重复看这里!Excel里去除重复值、统计数量的2种方法,快点学起来吧

个人信件回复关键字[工具],获取Excel高效小工具收藏!

打开excel效率~()

不久前发生了这样的问题,头疼。

头疼的原因有 3 点:

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

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

❸ 提问的是个男生。

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

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

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

明白了吗?

明白了,咱们就开始干!

01

方法一

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

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

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

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

❶ COUNTIFS 统计数量。

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

完成公式如下:

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

计算结果:

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

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

❷ 数量求倒数。

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

完成公式如下:

=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 进行求和。

完成公式如下:

=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 用户上了。

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

完整公式如下:

=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 步的计数过程相乘就可以了。

完成公式如下:

=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」筛选对应的记录。

完整公式如下:

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

计算结果:

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

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

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

❷ 去除重复值。

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

完整公式如下:

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

计算结果:

={43739;43740;43741;43742}

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

❸ 统计数量。

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

完整公式如下:

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

计算结果:

= 4

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

03

总结

温馨提示:

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

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

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

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

1.《03版excel怎么排除重复看这里!Excel里去除重复值、统计数量的2种方法,快点学起来吧》援引自互联网,旨在传递更多网络信息知识,仅代表作者本人观点,与本网站无关,侵删请联系页脚下方联系方式。

2.《03版excel怎么排除重复看这里!Excel里去除重复值、统计数量的2种方法,快点学起来吧》仅供读者参考,本网站未对该内容进行证实,对其原创性、真实性、完整性、及时性不作任何保证。

3.文章转载时请保留本站内容来源地址,https://www.lu-xu.com/gl/2217553.html

上一篇

100元人民币如何鉴别真伪?总结很全面速看!有人精明了半辈子,却算错防癌这笔账!肿瘤医生一句话点醒太多人

下一篇

如何成为q堡直播会员?我来告诉你答案访谈︱卡门·玛丽亚·马查多:应该给人物一次滚床单的机会

03版excel怎么排除重复看这里!Excel中去掉重复数据这7个技巧你一定要知道

03版excel怎么排除重复看这里!Excel中去掉重复数据这7个技巧你一定要知道

03版excel怎么排除重复相关介绍,重复数据可能会出现在Excel中。如何消除重复数据? 或者,要快速找到重复的内容,请共享重复数据的7种技术。 一、删除重复项 想要删除表格中的重复项,最简单的方法就是使用Excel自带的删除重复项功能...

03版excel怎么排除重复

03版excel怎么排除重复

03版excel怎么排除重复相关介绍,Excel去除重复值方法很多,有操作技巧法,有公式法。本节使用五种公式法探讨去除重复值,它的好处在于,当你更改其中一个数据时,结果会自动刷新。 如下所示效果: 五个公式方法 大家可以将公式复制至表格,...

03版excel怎么排除重复看这里!删除数据重复项,Excel中的这3种方法简单高效

03版excel怎么排除重复看这里!删除数据重复项,Excel中的这3种方法简单高效

03版excel怎么排除重复相关介绍,在统计数据过程中, 同一份数据可能由于渠道的不同而进行了多次统计, 在输入数据时, 可能因为操作失误重复输入数据。 种种原因造成数据表中的数据存在重复现象, 删除重复数据是数据清洗的首要任务。 今天,...

03版excel怎么排除重复?我来告诉你答案Excel最简单的删除重复数据方法!只需两步

03版excel怎么排除重复?我来告诉你答案Excel最简单的删除重复数据方法!只需两步

03版excel怎么排除重复相关介绍,“hello朋友们你好,我是一个。在本节中,我们将继续介绍Excel Power Query数据处理实用程序的18种方法中的第9-10号——项清理和重复数据删除。 下面,我们来开始今天的学习吧~”#表...

03版excel怎么排除重复看这里!5个Excel快速查找,删除重复值技巧,让你一键解决重复小烦恼!

03版excel怎么排除重复看这里!5个Excel快速查找,删除重复值技巧,让你一键解决重复小烦恼!

03版excel怎么排除重复相关介绍,现在在职场中,少不了要用到Excel技巧,虽然有时很快捷,但不能保证在使用的过程中没有重复的数据的出现,想要排查重复数据,一个个的看,那多浪费时间,所以呀,今天小编就来教给大家5个很实用的查找,删除、...