首页天道酬勤excel怎么统计不重复个数(找出重复项的公式)

excel怎么统计不重复个数(找出重复项的公式)

admin 12-02 03:21 327次浏览

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

释放你的卓越效率~ ()

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

头痛有三个原因:

问题描述不清,难以理解;

如果反复计算量,很难实现函数公式。

提问者是个男孩。

我试图简化这个问题。好吧,就活着看吧。

一列“用户标识”和一列“活动日期”。现在,我想统计一下每个用户参加活动的天数。

因为用户一天可能会参加很多活动,所以需要根据“用户ID”去掉“活动日期”的重复,然后进行统计。

明白吗?

我明白了,我们开始吧!

01

方法一

Excel中有一个关于删除重复计数的经典用法,即计算唯一值。

配有SUMRODUCT和COUNTIF/COUNTIFS函数。

=SUMPRODUCT(1/COUNTIF)。

现在不懂也没关系。让我们通过这个案例一起经历这个过程。

COUNTIFS统计量。

首先统计数量,因为有两列数据:“用户ID”和“活动日期”,所以我们使用COUNTIFS函数。

完成公式如下:

=COUNTIFS(H2 C2:C16、c 2: C16、B2:B16)

计算结果:

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

注意:下面是数组的用法。如果判断条件的参数中使用了数组,那么计算的结果也是相应数量的数组。

倒计时数量。

接下来,将计数结果除以1,得到相应的倒数。

完成公式如下:

=1/COUNTIFS(H2 C2:C16、c 2: C16、B2:B16)

计算结果:

={#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效率开挂~(◦˙▽˙◦)

DeepRust匹配:有更好的方法吗?C++构造析构赋值运算函数怎么应用雷士灯具管理系统
5g是什么意思(5g nr 站内切换 站间切换) 微信(图片微信朋友圈)
相关内容