Excel – 列出班级名，如何一对多查找出全班人的姓名和成绩？

一对多查找，一直是 Excel 迷们用来比拼水平高下的一个永恒习题。前不久，我也刚以此为案例讲解过万金油公式的用法。

今天要教大家的是一对多查找的另一种解决办法。

案例：

下表 1 是原始表格，要求只要列出班级名称，就能将全班所有人的信息提取到另一张表中。效果如下图 2。

解决方案：

先在 E1 单元格制作班级下拉菜单：

1. 选中 E1 单元格 --> 选择菜单栏的“数据”-->“数据验证”

2. 在弹出的对话框中进行以下设置 --> 点击“确定”：

• 允许：选择“序列”

• 来源：输入“一班,二班,三班”

* 请注意：班级之间的逗号必须为英文半角符号

现在，班级下拉菜单就做好了。

3. 将 D 列用作辅助列，在 D2 单元格输入以下公式，下拉复制公式：

=COUNTIF($A$2:A2,$E$1)

这个公式的作用是统计下拉菜单中的班级在 A 列中第几次出现。

4. 在 E2 单元格输入以下公式，右拉下拉复制公式：

=IFERROR(INDEX(A$2:A$10,MATCH(ROW(A1),$D$2:$D$10,0)),'')

公式释义：

• INDEX(查找的区域，区域中的第几行)

• A$2:A$10：查找的区域，区域的行值是固定的，需要绝对引用；列值要相对引用，这样向右拖动公式的时候，列数会自动递增，即依次取 B 列、C 列的值

• MATCH(搜索的项,搜索区域,绝对匹配)：返回搜索项在区域中的相对位置

• ROW(A1)：A1 的行号，即“1”；向右拖动时，行号不变；向下拖动时，行号会以步长 1 递增

• $D$2:$D$10：辅助列区域，即班级名是第几次出现；比如“一班”一共出现了 3 次，那么 row 函数最多匹配到 row(A3)，再下拉到 row(A4) 的时候，就查找不到“4”了，用此方式可以查找出所有“一班”依次出现在搜索区域的第几行

• 最后在外面套上 iferror 函数，不显示错误值

现在，筛选下拉菜单，就能查找出班级中所有人的完整信息了。为了美观起见，把辅助列 D 的字体颜色设置为白色。