跟我一起，穿越时间！
今天是Excel升级之路的连载9，我们研究的是Excel中的重复值、不重复值的问题，主要解决的是通过公式判断是否有重复值，并且计算出不重复值的个数。
（IF、MATCH、ROW综合运用）、（COUNT、MATCH、ROW综合运用）、（COUNT、FREQUENCY综合运用）、（COUNTIF、SUMPRODUCT综合运用）。
如果此时你对Excel数组的知识还不甚了解，可以先点击头像或链接学习之前的连载：
走到
一、Match函数
1、Match函数初相识
在指定区域中的位置
MATCH(lookup_value, lookup_array, [match_type])
MATCH(要查找匹配的，单元格区域，[匹配方式])
位置编号，而不是数据本身
=MATCH(3.5,A1:A6,0)
可以看到，Excel计算的结果返回4
第一次出现的位置
=MATCH(3.8,A1:A6,0)
你是否好奇最后的代码0是什么意思？我们接着往下看。
2、查找匹配方式：
要查找匹配的值
MATCH(要查找匹配的值，单元格区域，[匹配方式])
具体的含义如下：
上面的表格中最常用的就是代码0，它表示精确匹配，而且对查找区域中的数值没有排序的要求；
查找的
的
这里比较绕，可以这样记：
1为正数，要求升序，越来越大小于或等于大
-1为负数，要求降序，越来越小大于或等于小
而且时刻需要记住Match函数返回的是要查找到值所在的位置序号，并不是值本身。
那么如上图：
=MATCH(5.5,{1;2;3;4;5;6;7;8;9},1)
在{1;2;3;4;5;6;7;8;9}中查找小于或等于5.5的最大值所在的位置，小于等于5.5的最大值是5，位置也是5
=MATCH(5.5,{1;2;3;4;5;6;7;8;9},-1)
=MATCH(5.5,{9;8;7;6;5;4;3;2;1},-1)
在{9;8;7;6;5;4;3;2;1}中查找大于或等于5.5的最小值所在的位置，大于或等于5.5的最小值是6，其位置是4，所以结果是4
=MATCH(5.5,{9;8;7;6;5;4;3;2;1},1)
3、大小写与通配符
查找文本型字符时，match函数不区分大小写
=MATCH("B",{"A","B","C"},0)返回2
=MATCH("b",{"A","B","C"},0) 返回2
其
练习一下：
=MATCH("~*",C1:C6,0) 查找星号*第一次出现的位置，结果为4
=MATCH("~*~*~?",C1:C6,0) 查找星号星号问号**？第一次出现的位置，结果为6
4、数组
Match函数也可以支持数组运算，例如：
=MATCH(A1:A6,A1:A6,0)
结果是一个数组{1;2;3;3;5;6}
为什么？
第一次
“穿越时间”在A1:A6中第一次出现的位置是1；
“穿越”在A1:A6中第一次出现的位置是2；
“时间”在A1:A6中第一次出现的位置是3；
“时间”在A1:A6中第一次出现的位置是3；
“Excel”在A1:A6中第一次出现的位置是5；
“连载”在A1:A6中第一次出现的位置是6；
返回一个数组{1;2;3;3;5;6}
注意match对于重复值的处理，它返回的只是该值第一次出现时的位置。
理解了这个，match函数可以引入一种高级用法，那就是判断是否存在重复值。
在下一部分实际应用中，我通过身份证号来举一个实例。
二、Match判断是否有重复值
效验
因此在输入身份证号、银行卡号时需要将单元格格式设置为“文本”型。
我们看一下下面的表格：
在销售文创纪念品的时候，周婉悦、琼英、王孙药师等人的信息都多次出现，那么E列身份证号中肯定是存在重复的。我们以此为例，检测E列中身份证号是否有重复值。
1、存在缺陷的方法：条件格式
原本没有重复的身份证号也会被突出显示为重复
条件格式-突出显示单元格规则-重复值
设置重复值的格式为“浅红填充色深红色文本”
为什么原本不重复的身份证号（例如370600199901010808）也会被判定为重复？
Excel条件格式只能比对前15位
下面我们改进一下，通过几种办法来准确判断身份证号有没有重复。
2、辅助列标记法（IF、MATCH、ROW综合运用）
我们可以在E列身份证号的后面插入一列，作为“辅助列”，通过辅助列标记一下是否重复。
这里需要用到一个公式，先来看看你明不明白它的意思：
=IF(MATCH(E2,$E$1:$E$18,0)=ROW(E2),"不重复","重复")
在F2单元格中输入公式，按Enter键计算，然后拖动填充即可完成判断。
分析一下：
=IF(MATCH(E2,$E$1:$E$18,0)=ROW(E2),"不重复","重复")的原始形式是：=IF(MATCH(E2,E1:E18,0)=ROW(E2),"不重复","重复")
首先说明ROW函数是用来返回单元格所在的行号，例如：=ROW(E2)=2
MATCH(E2,E1:E18,0)=ROW(E2)$E$1:$E$18
实际
第一行公式：
=IF(MATCH(E2,$E$1:$E$18,0)=ROW(E2),"不重复","重复")
E2是周婉悦的身份证号，在E1:E18中第一次出现的位置是2，和E2的行号ROW(E2)相等，故E2不重复；
第二行公式：
=IF(MATCH(E3,$E$1:$E$18,0)=ROW(E3),"不重复","重复")
E3是琼英的身份证号，在E1:E18中第一次出现的位置是3，和E3的行号ROW(E3)相等，故E3不重复；
第三行公式：
=IF(MATCH(E4,$E$1:$E$18,0)=ROW(E4),"不重复","重复")
E4是周婉悦的身份证号，在E1:E18中第一次出现的位置是2（
……
就是这么一个原理。
实质是比较第一次出现的位置和行号的关系。
写法的关键在于需要根据判断区域的实际情况调整ROW函数的大小，例如：
$E$2:$E$18ROW(E2)-1
ROW(E2)-1，然后
当然，如果你清楚了原理，以下写法都可以：
整列引用的写法：
=IF(MATCH(E2,E:E,0)=ROW(E2),"不重复","重复")
Match结果+1的写法：
=IF(MATCH(E2,$E$2:$E$18,0)+1=ROW(E2),"不重复","重复")
使用不等号的反向写法：
=IF(MATCH(E2,E:E,0)<>ROW(E2),"重复","不重复")
=IF(MATCH(E2,$E$1:$E$18,0)<>ROW(E2),"重复","不重复")
=IF(MATCH(E2, $E$2: $E$18,0)<>ROW(E2)-1,"重复","不重复")
=IF(MATCH(E2, $E$2: $E$18,0)+1<>ROW(E2),"重复","不重复")
仅使用小于号的反向写法也是可以的，例如：=IF(MATCH(E2,E:E,0)<ROW(E2),"重复","不重复")
这就是很多人写公式出现ROW函数减数的原因，根据match比较区域的不同，后面ROW函数到底减几需要根据实际的情况来判断。
这个时候有了辅助列的标记，我们就知道谁重复谁不重复了。
当然，我们可以采用数组运算使用以下公式按Ctrl+Shift+Enter键直接得到结果：
=IF(MATCH(E2:E18,E2:E18,0)=ROW(E2:E18)-1,"不重复","重复")
MATCH(E2:E18,E2:E18,0)返回E2、E3、E4在E2:E18中第一次出现的位置，结果是一个数组{1;2;1;4……17}
ROW(E2:E18)-1返回E2:E18的行号减1，结果为一个数组{1;2;3;4……17}
MATCH(E2:E18,E2:E18,0)=ROW(E2:E18)-1执行判断，两个纵向一维数组执行是否相等的逻辑判断运算，遵循数组的运算法则，依次执行判断，得到一个由逻辑值组成的数组（如果不理解请看前面的连载）{TRUE;TRUE;FALSE;TRUE……TRUE}，其中TRUE就代表该项不重复，FALSE代表该项重复，上面我们已经讲过原理。
三、不重复值计数
上面部分的内容仅仅是判断是否重复，下面我们要更进一步，数出个数来，实现的是不重复值计数。
问题：不重复的身份证号一共有几个？
下面的内容属于Excel中的高级用法，十分巧妙，第一个想出这些办法的人绝对是天才。
1、方法一：（COUNT、MATCH、ROW综合运用）
我们要直接求出不重复的身份证号的个数，可以通过以下公式实现：
=COUNT(0/(MATCH(E2:E18,E2:E18,0)=ROW(E2:E18)-1))
按Ctrl+Shift+Enter执行数组运算，结果为8
通过筛选功能，可以数一数，不重复的身份证号确实有8个。
那么公式是什么意思你能想明白吗？
=COUNT(0/(MATCH(E2:E18,E2:E18,0)=ROW(E2:E18)-1)) 中
MATCH(E2:E18,E2:E18,0)返回E2、E3、E4在E2:E18中第一次出现的位置，结果是一个数组{1;2;1;4……17}
ROW(E2:E18)-1返回E2:E18的行号减1，结果为一个数组{1;2;3;4……17}
MATCH(E2:E18,E2:E18,0)=ROW(E2:E18)-1两个纵向一维数组执行是否相等逻辑判断运算，遵循数组的运算法则，依次执行判断（如果不理解请看前面的连载5：数组的理想照进现实），得到一个由逻辑值组成的数组{TRUE;TRUE;FALSE;TRUE……TRUE}，其中TRUE就代表该项不重复，FALSE代表该项重复。
0/(MATCH(E2:E18,E2:E18,0)=ROW(E2:E18)-1)就相当于0/
所以最外层通过count计数即可（count函数会忽略错误值，只计算数字的个数）。
这个办法比较巧妙，其中如果理解原理，那么不一定非要用0除：
=COUNT(5/(MATCH(E2:E18,E2:E18,0)=ROW(E2:E18)-1))
=COUNT(1/(MATCH(E2:E18,E2:E18,0)=ROW(E2:E18)-1))
这里拓展一下：
只是数值型数据的话
=COUNT(0/FREQUENCY(E2:E18,E2:E18))
FREQUENCY函数可以计算数值在某个区域内的出现频率，然后返回一个垂直数组，FREQUENCY(E2:E18,E2:E18)实质计算的是E2:E18每个单元格在E2:E18区域中出现的频率（即出现的次数，而且frequency统计频率时，第二次出现会被认定为0）
这样最后用0/，然后数出0的个数就得到不重复值的个数。
只是这种办法具有局限性（frequency函数只能统计数值型数据的频率），只能计算数值型数据里非重复值的个数。
2、方法二：（COUNTIF、SUMPRODUCT综合运用）
在连载6：神级函数SUMPRODUCT的惊鸿一瞥
通过COUNTIF和SUMPRODUCT综合运用，SUMPRODUCT函数也可以直接计算出不重复值的个数。
首先提一下，countif函数和最初提及的条件格式有个同样的问题，countif也只能分辨前15位，比如我们用countif来看E2在E2:E18中出现了几次，countif会判断为17次，明显是错误的。
=COUNTIF(E2:E18,E2)
&"*"
=COUNTIF(E2:E18, E2&"*") =COUNTIF(E2:E18,"*"&E2)
以上面的为基础，我们使用公式：
=SUMPRODUCT(1/COUNTIF(E2:E18,E2:E18&"*")) 或
=SUMPRODUCT(1/COUNTIF(E2:E18,"*"&E2:E18))
按Enter键即可得到不重复身份证号的个数。
什么原理？
解释一下：
数组运算
即：E2周婉悦的身份证号在E2:E18中出现了3次，上面数组中就会有三个元素3 ；E3琼英的身份证号在E2:E18中出现了3次，上面数组中也会有三个元素3，但是需要区分一下他们背后代表的意思不同。
实质就是对应某一身份证号，其出现的次数被转化为n，当然其他与之不同的身份证号出现的次数也可以为n、m、p、q……
1/(COUNTIF(E2:E18,E2:E18&"*")将上面的数组变为{1/3;1/3;1/3;1/2……1/1}，那么对于在E2:E18中出现了3次的E2，上面数组里原来的三个元素3就会变成三个1/3 ；对于在E2:E18中出现了3次的E3，上面数组里原来的三个元素3就会变成三个1/3 ；
实质就是对应某一身份证号，数组中的元素变成了n个1/n，当然其他与之不同的身份证号在数组中的元素变成了n个1/n、m个1/m、p个1/p、q个1/q
n个1/n、m个1/m、p个1/p、q个1/q会再次被合起来，
这个办法也是一个很巧妙的办法，由于sum也可以执行数组运算，因此我们也可以用sum函数来计算
=SUM(1/COUNTIF(E2:E18,E2:E18&"*"))
好了，以上就是连载9的全部内容，这次的难度怎么样?
如果有不理解的可以先看前面的连载打牢基础。
点击头像跳转
更多精彩，敬请关注，投币赞赏，感谢支持。
（原创连载，个人观点保留，禁止任何未经授权的非本人账号复制文章到其他平台发布）