MATCH函数使用终极帖

hercules028 2020-08-26

展开全文

来源：Excel应用之家

MATCH函数是Excel中最常用、最重要的函数之一。MATCH函数和ROW函数、OFFSET函数配合使用，可以取得令人意想不到的结果。

现在我们一起来认识下MATCH函数和INDEX函数、VLOOKUP函数以及OFFSET函数的综合应用。

查找数值的位置

这个例子中我们想知道数字4在数列中的位置，那么在A5单元格中输入“=MATCH(4,$A$3:$E$3,0)”就可以了。函数返回的结果“4”是位置序号，表面数字“4”在这个序列中的位置是第四位。

思路：在这个公式中，参数“0”表示查找等于被查找值“4”的第一个数值的位置。

判定重复值

此例中，单元格区域A9:A18中有重复的数据，我们在C9单元格中输入“=IF(MATCH(A9,$A$9:$A$18,0)=ROW()-8,'','重复')”，下拉即可。

思路

求第n大不重复的值

此例中，单元额区域A24:A33中有重复的数据。我们分别求出第一到第四大的数据。在单元格E24中输入“=LARGE(IF(MATCH($A$24:$A$33,$A$24:$A$33,0)=ROW($A$24:$A$33)-23,$A$24:$A$33),ROW()-23)”，并三键回车，向下拖曳。

思路：

首先，match函数对区域中的每一个值都进行搜索并返回位置，形成一个新的数组
再和每个数据所在行的行号row()减去23的结果相对比（即在此单元格区域A24:A33中当前数据是第几行），并利用if函数返回结果为“相等”所对应的单元额区域A24:A33中的数据
最后用large函数来返回第n大的值。row()-23决定了是第“几”大的数据

反向查找

我们介绍过用if函数利用{1,0}创建一个新的数组，以用来做反向查找。这里，match函数和offset函数配合使用，也可以达到反向查找的目的。

在单元格E39中我们输入“=OFFSET($A$37,MATCH(D38,$B$38:$B$46,0),0)”，三键回车并向下拖曳即可。

思路：

提取不重复清单

在此例中，单元格区域A51:A60中有重复的数据，在单元格C51中输入

“=OFFSET($A$50,SMALL(IF(MATCH($A$51:$A$60,$A$51:$A$60,0)=ROW($A$51:$A$60)-50,ROW($A$51:$A$60)-50),ROW()-50),0)”，并三键回车，向下拖曳即可。

思路：

在中国式排名中，无论有几个并列，之后的排名仍然顺位排列。即无论有多少个第二名并列，之后的排名仍然是第三名。

我们在单元格C2中输入

“=SUM(--IF($A$2:$A$11>=A2,MATCH($A$2:$A$11,$A$2:$A$11,0)=ROW($2:$11)-1))”并向下拖曳即可。

思路：

MATCH($A$2:$A$11,$A$2:$A$11,0)返回{1;2;3;3;5;5;7;8;9;9}；ROW($2:$11)-1返回{1;2;3;4;5;6;7;8;9;10}；MATCH=ROW对比的结果是{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE}
$A$2:$A$11>=A2返回的结果是{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}，它表明在$A$2:$A$11中有10个数据大于等于A2中的数据
利用IF函数逻辑值判断为真时，返回上面MATCH=ROW相比的结果{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE}
利用“--”运算得到{1;1;1;0;1;0;1;1;1;0}
利用SUM函数求和得到“7”，表示A2单元格中的数值排名第七名
其它单元格分析类似
当有重复值时，由于MATCH函数总是返回查找到的第一个值，因此重复值的排名总是和第一个出现的重复值相同。因此实现了中国式排名