穿越时间Excel升级之路连载9:MATCH函数及SUMPRODUCT不重复值计数

跟我一起，穿越时间！

今天是Excel升级之路的连载9，我们研究的是Excel中的重复值、不重复值的问题，主要解决的是通过公式判断是否有重复值，并且计算出不重复值的个数。

本期内容涉及MATCH函数的介绍和应用，涉及数组和众多函数的综合，例如（IF、MATCH、ROW综合运用）、（COUNT、MATCH、ROW综合运用）、（COUNT、FREQUENCY综合运用）、（COUNTIF、SUMPRODUCT综合运用）。

如果此时你对Excel数组的知识还不甚了解，可以先点击头像或链接学习之前的连载：

穿越时间·Excel升级之路连载6：神级函数SUMPRODUCT的惊鸿一瞥

穿越时间Excel升级之路连载7：SUMPRODUCT函数挑战Excel条件计数

穿越时间Excel升级之路连载8：SUMPRODUCT函数决胜Excel条件求和

今天，让我们走到 Excel升级之路连载9：MATCH 函数及SUMPRODUCT不重复值计数，来看不同的函数如何实现三位一体各显神通。

一、Match函数

1、Match函数初相识

Match函数可以在指定区域中查找指定值，然后返回要查找的值在指定区域中的位置（第一次出现的位置），其语法为：

MATCH(lookup_value, lookup_array, [match_type])

MATCH(要查找匹配的值，单元格区域，[匹配方式])

需要注意的是，Match函数返回的是要查找的值在指定区域（单元格区域、数组）中的位置编号，而不是数据本身，如果要返回实际的数据，需要使用lookup、vlookup、hlookup等函数。

=MATCH(3.5,A1:A6,0)

可以看到，Excel计算的结果返回4

解释一下就是在A1：A6中查找3.5出现的位置，其第一次出现的位置是4

当match找不到的时候，例如=MATCH(3.8,A1:A6,0)则会报错#N/A

你是否好奇最后的代码0是什么意思？我们接着往下看。

2、查找匹配方式：

在Match函数的语法中，Lookup_value是要查找匹配的值，Lookup_array是指定的区域，match_type是匹配方式，可以指定1、0、-1，如果省略的话则默认为1

MATCH(要查找匹配的值，单元格区域，[匹配方式])

具体的含义如下：

上面的表格中最常用的就是代码0，它表示精确匹配，而且对查找区域中的数值没有排序的要求；

代码1或省略，表示查找小于或等于查找值的最大值，区域要求升序排列（越来越大）；

代码-1表示查找大于或等于查找值的最小值，区域要求降序排列（越来越小）。

这里比较绕，可以这样记：

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) 如果升序排序但却使用-1，函数报错#N/A

=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) 如果降序排序但却使用1，函数报错#N/A

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)，按下Ctrl+Shift+Enter键计算

结果是一个数组{1;2;3;3;5;6}

为什么？

解释一下：Match执行数组运算后，会查找A1:A6中每一个值在区域A1:A6中第一次出现的位置：

“穿越时间”在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判断是否有重复值

在处理员工信息时，由于重名的存在，我们常常需要通过身份证号来对员工的身份进行效验，检查有没有输入重复的员工，由于身份证号码长度为18位，如果直接在Excel单元格中输入18位身份证号，输入完毕回车确认后，身份证号会自动变为科学计数法，尾数变为0从而产生错误（产生的原因是Excel会对12位及以上的大数采用科学计数法）。

因此在输入身份证号、银行卡号时需要将单元格格式设置为“文本”型。

我们看一下下面的表格：

在销售文创纪念品的时候，周婉悦、琼英、王孙药师等人的信息都多次出现，那么E列身份证号中肯定是存在重复的。我们以此为例，检测E列中身份证号是否有重复值。

1、存在缺陷的方法：条件格式

相信很多人采用Excel中内置的条件格式功能快速检测是否存在重复值，不过你会发现当通过条件格式快速突出显示重复的身份证号时，原本没有重复的身份证号也会被突出显示为重复，这是为什么？

条件格式-突出显示单元格规则-重复值

设置重复值的格式为“浅红填充色深红色文本”

为什么原本不重复的身份证号（例如370600199901010808）也会被判定为重复？

原因在于Excel条件格式只能比对前15位，如果参与比对的单元格前15相同，那么Excel就会认为他们是相同的。

下面我们改进一下，通过几种办法来准确判断身份证号有没有重复。

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)作为if函数的条件，判断E2在E1:E18中第一次出现的位置是否和E2的行号相同，如果相同，表示E2第一次出现，此时if条件为真，返回“不重复”；如果不相同，表示E2不是第一次出现，if条件为假，返回“重复”。为了拖动自动填充，所以锁定了E1:E18为$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（因为match函数返回重复值第一次出现的位置号），和E4的行号ROW(E4)不相等，而且必然是MATCH(E4,$E$1:$E$18,0)< ROW(E4)，说明E4的值在前面已经出现过了，所以E4重复；

……

就是这么一个原理。

实质是比较第一次出现的位置和行号的关系。

写法的关键在于需要根据判断区域的实际情况调整ROW函数的大小，例如：

=IF(MATCH(E2,$E$2:$E$18,0)=ROW(E2)-1,"不重复","重复")

前面使用的判断区域是E2:E18的话，后面就要使用ROW(E2)-1，因为判断区域是E2:E18的话，E2第一次出现的位置号就是1，和它的行号差了1，所以要ROW(E2)-1，然后判断要查找的值在区域中第一次出现的位置是否和行号减1的值相等，如果相等，则第一次出现，不重复；如果不相等，说明match函数找到了该值更早时第一次出现的位置，该值不是第一次出现，标记为重复。

当然，如果你清楚了原理，以下写法都可以：

整列引用的写法：

=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函数减数的原因，根据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/{TRUE;TRUE;FALSE;TRUE……TRUE }，把由逻辑值构成的数组转换为数值型数组，得到一个由0和错误值#DIV/0!构成的数组（0/TRUE=0,0/FALSE=#DIV/0，逻辑值和数值型数据的运算，如果不理解请看前面的连载），而其中0的个数就是不重复的身份证号的个数。

所以最外层通过count计数即可（count函数会忽略错误值，只计算数字的个数）。

这个办法比较巧妙，其中如果理解原理，那么不一定非要用0除：

可以改一下：=COUNT(5/(MATCH(E2:E18,E2:E18,0)=ROW(E2:E18)-1))，按Ctrl+Shift+Enter执行数组运算，最终count数的是5的个数，而5的个数就是不重复的身份证号的个数。

可以再改一下：=COUNT(1/(MATCH(E2:E18,E2:E18,0)=ROW(E2:E18)-1))，按Ctrl+Shift+Enter执行数组运算，最终count数的是1的个数，而1的个数就是不重复的身份证号的个数。

这里拓展一下：（COUNT、FREQUENCY综合运用）

如果要计算非重复值个数的单元格里不是文本型数据（例如不是以文本方式存储的身份证号），而只是数值型数据的话，还可以借助frequency函数。

=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的惊鸿一瞥中，我们将SUMPRODUCT函数称为神级函数，试问还有多少是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键即可得到不重复身份证号的个数。

什么原理？

解释一下：

COUNTIF(E2:E18,E2:E18&"*")执行了数组运算，返回E2在E2:E18中出现的个数、E3在E2:E18中出现的个数……结果是一个数组{3;3;3;2……1}

即：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

然后SUMPRODUCT对单参数数组内的元素求和，n个1/n、m个1/m、p个1/p、q个1/q会再次被合起来，有多少个1就说明有多少个不重复值。

这个办法也是一个很巧妙的办法，由于sum也可以执行数组运算，因此我们也可以用sum函数来计算

=SUM(1/COUNTIF(E2:E18,E2:E18&"*")) 按Ctrl+Shift+Enter计算

好了，以上就是连载9的全部内容，这次的难度怎么样?

如果有不理解的可以先看前面的连载打牢基础。

点击头像或链接跳转：

穿越时间•Excel升级之路连载5：数组的理想照进现实

穿越时间·Excel升级之路连载6：神级函数SUMPRODUCT的惊鸿一瞥

穿越时间Excel升级之路连载7：SUMPRODUCT函数挑战Excel条件计数

穿越时间Excel升级之路连载8：SUMPRODUCT函数决胜Excel条件求和

更多精彩，敬请关注，投币赞赏，感谢支持。

（原创连载，个人观点保留，禁止任何未经授权的非本人账号复制文章到其他平台发布）