Excel 2016中MATCH函数遇重复值返回重复车道号的问题求解

问题根源 #

你用MATCH函数匹配车道号时，它默认返回第一个匹配值的位置，所以当多个车道计数相同时，会重复返回同一个车道号。以下是两种兼容Excel 2016及更早版本的解决办法：

方法一：数组公式法（无需辅助列） #

假设：

• 计数数据在B2:B19
• TOP6数值已用=LARGE(B2:B19,1)至=LARGE(B2:B19,6)填入E2:E7
• 车道号对应行号（即第2行是LANE 1，第3行是LANE 2…）

在D2单元格输入以下公式，按Ctrl+Shift+Enter完成数组输入（输入后公式会自动被大括号包裹），再下拉到D7：

="LANE "&SMALL(IF($B$2:$B$19=E2,ROW($B$2:$B$19)-ROW($B$2)+1),COUNTIF($E$2:E2,E2))

如果你的车道号已在A2:A19（比如A2是"LANE 1"），公式改为：

=INDEX($A$2:$A$19,SMALL(IF($B$2:$B$19=E2,ROW($B$2:$B$19)-ROW($B$2)+1),COUNTIF($E$2:E2,E2)))

公式说明 #

• IF($B$2:$B$19=E2,ROW(...)...)：筛选出所有等于当前TOP数值的计数所在行的相对位置
• COUNTIF($E$2:E2,E2)：统计当前TOP数值在已处理区域出现的次数，确保相同数值依次返回不同车道
• SMALL+INDEX/直接拼接：根据位置返回对应的车道号

方法二：辅助列法（更易上手） #

这种方法通过给计数添加微小偏移，让每个数值唯一，避免MATCH重复匹配：

1. 添加辅助列C，在C2输入公式并下拉到C19：

   =B2+ROW()/10000
   

   （给每个计数加一个极小的行号偏移，相同计数会因行号不同产生微小差异，不影响数值显示）
2. 更新TOP6数值公式（E2:E7），改用辅助列取数：

   =INT(LARGE($C$2:$C$19,ROW()-1))
   

   （INT用于还原整数计数，若计数是小数可改用ROUND(E2,2)等）
3. 匹配车道号（D2:D7），直接用MATCH匹配辅助列：

   ="LANE "&MATCH(E2,$C$2:$C$19,0)
   

   若车道号在A2:A19，公式改为：

   =INDEX($A$2:$A$19,MATCH(E2,$C$2:$C$19,0))
   

内容的提问来源于stack exchange，提问作者Reese