让你的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效率开挂~(◦˙▽˙◦)