轻轻松松分辨重复数据

宋永成

在Excel中，有时我们会根据关键字从其它工作表中查找与之相对应的数据，如根据姓名从图1所示的全年级的学生成绩表中查找某一学生的总分成绩，通常我们会使用VLOOKUP函数完成任务。但是VLOOUP只能查找到符合指定条件的第一个数据，对于其它符合条件的数据就无能为力了。所以上表中如果有学生重名，那么根据姓名查找就总是返回第一个同学的成绩。那么像这种有重复数据的情况，又如何来解决呢？在Excel中，虽然VLOOKUP函数本身有它的局限性，但是转换—下思路，要解决这个问题其实还是有办法的。

一、改换关键字，仍用VLOOKUP函数

VLOOKUP函数之所以有问题，关键原因是有重复数据（姓名有重复）的存在。如果换个没有重复的关键字，那这个问题也就不是问题了。观察本例发现，所有重名的学生并不在同一个班级中，也就是说“班级+姓名”是不可能重复的。那么我们就以“班级+姓名”为关键字。在A列之前添加一个空列，这样“班级”和“姓名”列就延至B列和c列了。将鼠标定位于A1单元格，输入公式“=B1&C1”，拖动其填充句柄至最后一行。现在A列的中数据就变成无重复的数据了。

假定要查询成绩的学生姓名位于B16：B19单元格区域，A16：A19单元格为学生们的相应班级，如图2所示。在C16单元格输入公式“=VLOOKUP（$A16&$B16，$A$1：$I$13，9，FALSE）”，然后拖动其填充句柄至c19单元格就可以得到结果了，那些重名的同学成绩也可以轻松分辨出来。

二、另辟蹊径，改用其它函数

上面的方法固然简单，却也有局限性，你必须先知道要查询的学生是哪个班级的。假定只有姓名，如何才能查询正确的结果呢？或者能不能把这个姓名的所有同学成绩都列出来呢？这个要求，恐怕就不是某单一函数所能做的了，得多个函数才行。

在C16单元格输入公式“=INDEX（$I$1：$I$13，SMALL（IF（$C$2：$C$13=B16，ROW（$C$2：8C$13），65536），COUNTIF（$B$16：$B16，B16）））”，将鼠标定位于编辑栏，然后按下“Ctrl+Shlft+Enter”组合键产生数组公式（特征即是公式外层的一对花括号）。拖动c16单元格的填充句柄向下填充公式至c19单元格，可以看到各个名为“张三”的学生总分成绩了。

对于公式，我们不妨这样理解：内层的IF结构的计算结果，也就是判断C2：C13区域，如果等于B16，就返回对应的所在行号，不相等的话，就返回65536（以此数代表一个极大值）。单纯计算“IFf8c$2：$c$13=B16，ROW（$C$2：$C$13），65536）”部分，会得到结果“f2；65536；4；65536；65536；65536；8；65536；65536；65536；65536；65536l”。公式中的COUNTIF（$B$16：$B16，B16）用于计算这是第几个重复数据，向下拉动公式后会根据B16及以下的数据返回结果1，2，3等。于是利用SMALL函数就可以提取到前面数据中最小的三个数“2，4，8”。这三个数正是“张三”同学所在的行数。然后我们就可以利用INEDX函数在I列中提取相应行数的数据了。

应用此公式，对其稍加改造，我们可以实现两个有意思的功能：

1.提取所有重名学生的总分

我们只在B16单元格提供查询学生的姓名，然后用公式提取出所有该姓名的学生总分成绩。这样我们的公式就应该写做“=INDEX（$I$1：$I$13，SMALL（IF（$C$2：$C$13=$B$16，ROW（$C$2：$C$13），65536），ROW（A1）））”，仍然按“Ctrl+Shift+Enter”组合键结束。拖动该单元格填充句柄向下至出现错误提示为止。这样就能提取所有重复数据了。

2.自动按名次排列成绩表

成绩表可以用RANK函数排出名次，之后我们通常要进行排序才能按名次进行升序显示。利用上面的公式可以自动完成这个任务，保持了成绩表的原貌。

成绩表稍加改造，增加了几个平行的名次，用RANK函数排出名次。

在第15行复制表头。在116单元格写下公式“=SMALL（$152：$I$13，ROW（A1））”，按回车键结束。拖动其填充句柄向下至出现错误提示为止。此公式可以得到成绩表中名次的升序排列。相同名次的也可以全部列出。

在A16单元格输入公式“=INDEX（A$1：ASK3，SMALL（IF（$I$2：$I$13=$116，ROW（$I$2：$I$13），65536），COUNTIF（$I$16：$I16，$116））），按“Ctrl+Shift+Enter”组合键结束，产生数组公式。拖动A16单元格的填充句柄向右复制公式至H16单元格。再选中A16：H16单元格区域，向下拖动其填充句柄至出现错误提示为止。

删除所有的错误提示，就会出现按名次排列的成绩表了，如图3所示。

成绩表中的重复数据是不可避免的，比如重名、名次相同、分数相同等。只要我们掌握并灵活运用函数，那么重复数据也可以轻松分辨。