私信回复关键词【工具】，获取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效率开挂~(◦˙▽˙◦)

平时除了vlookup(点击查看详解)外，恐怕被问起最多的就属去除重复数据了，重复数据产生的原因很多种，录入重复 、数据合并后重复等等。最后的数据形式就是有一个关键字重复或者整行数据都有重复。整行重复的，用高级筛选很容易去除，对于关键字重复，只需保留一行的情况 我这里有一个比较简便易学的办法，就是用辅助列公式处理， 网上有些用countif的不少做法 ，那样选出来的是所有重复数据， 而在有多个数据的关键字行留下一行， 需要手动去留下多个重复数据的一行， 我这里介绍的方法， 可以一次性筛选出所有非重复数据 ，而且留下重复数据中的一行。原理(能看懂就看，看不懂可忽略)：首先对关键字进行排序，然后用match函数对当前行关键字对整列数据进行匹配，返回当前数据匹配到的行标。
当数据重复的时候 每个关键字就会匹配到第一个，所以当前辅助列公式返回的值就会不等于本行位置，这样就可以标识出非重复的关键字行，示例如下。
在去重列， ture即表示非重复数据， false即多余的重复数据， 然后利用自动筛选， 筛选出false行 删除即可得到非重复数据(或者筛选出ture复制到新表也可达到非重复数据)
当然，现在excel2007版本以上可以用系统提供的去重功能，但是看不到中间去重过程，我这个方法一是可以用在2003版本上，二就是理解透彻的话，结合其他公式可以更精准的控制去重。
2017-08-17 by excel880

    
假设A列中数据如下，去除重复项后的结果数据如B列，
在B2单元格输入公式：=IFERROR(INDEX(A:A,MATCH(,COUNTIF(B$1:B1,IF(A$2:A$99="",$A$1,A$2:A$99)),)+1),"")
然后按下Ctrl+Shift+Enter组合键激活数组公式，下拉数组公式即可。

 

转载于:https://www.cnblogs.com/xmqlv20081008/p/8078760.html

去除重复内容，就用这个EXCEL公式，1分钟搞定！

一份高中录取名册中，上千条学生信息，生源校有七八十所，如何从表中轻松提取出不重复的生源校名单呢？

有人或许会这样做：复制录取名册中所有生源校到一个空白EXCEL表中，再按学校名排序，最后分段再删除重复的部分。这样做，不仅费时、费力，最重要的，还有可能误删了数据。

其实，我们可以运用一个数组公式，就可以轻松提取出不重复内容。



在上图，F2单元格中，写下数组公式：=INDEX(D:D,1+MATCH(,COUNTIF(F$1:F1,D$2:D$21),))&""，编辑完成后，按Ctrl+Shift+回车（一定要按这三个键噢！），然后往下复制，直至没有新学校名称出现为止。

现在，为大家讲解一下这个数组公式：

1）COUNTIF(F$1:F1,D$2:D$21)：用COUNTIF函数，从F1单元格开始的扩展区域，查找D$2:D$21区域（生源校）每个数据的个数，返回一个由0和1构成的数组，如果D$2:D$21区域的数值在公式扩展区域中出现过，结果就是1；如果没出现，结果就是0。这个公式中，COUNTIF函数的第一参数，采用了行绝对引用（F$1）和相对引用（F1），向下复制，依次会产生：F$1:F2、F$1:F3、F$1:F4、。。。公式提取结果就会被重复利用。

2）利用MATCH函数，在COUNTIF函数返回的数组中查找第一个0的位置，也就是查找首次出现的数据所在的位置。由于生源校的标题行占了1行，所以，将这个值+1，就得到需要提取的不重复值在查找区域中行的位置。

3）最后，利用INDEX函数，以MATCH函数的计算结果作为索引值，提取D列对应位置上的不重复值。用&" "，避免显示错误值。

这个公式，可能有一点点理解难度，不过，不影响你使用，到时，你只要会复制公式，改一下参数就行了。今后，对于类似提取不重复内容的工作，就会既轻松，又准确、高效。

每天学习一点点，每天进步一点点，积硅步，必能致千里，得大成。