excel查找出另个表格的内容（Excel）

Excel 查找方式千千万，但是下面这种布局的查找，你会吗？

总结一下，这个查找涉及这几个知识点：一对多查找，表格转置，二维变一维（原本两列内容，查找后放在同一行）。

图 1 是原始表格，需要按班级提取所有人的姓名，向右填充，布局如图 2 的右边所示。

先将班级列表去重，提取到右边蓝色区域：

1. 将 D 列作为辅助列，在 D2 输入以下公式，下拉复制公式：

=COUNTIF(A$2:A2,A2)

公式释义：

• 统计每个班级是第几次出现
• A$2:A2：第一个单元格的行必须固定，第二个活动，这样随着公式下拉，始终统计 A$2 到当前行所在单元格区域内，班级名称的重复次数，即第几次出现

2. 在 E2 单元格输入以下公式，按 Ctrl Shift Enter 使数组公式生效，下拉复制公式：

=INDEX($A$2:$A$10,SMALL(IF($D$2:$D$10=1,ROW($A$2:$A$10),4^8),ROW(A1)))

公式释义：

• index small if row 就是传说中的万金油公式，什么都能查
• $A$2:$A$10：要查找的数据区域
• $D$2:$D$10=1：表示班级名称第一次出现，即去重值
• ROW($A$2:$A$10)：如果满足上述去重条件，则返回班级名所在的行值
• 4^8：如果不满足，则返回 4 的 8 次方，即 65536，这是 Excel 2003 的最大行数，通常用来表示找不到就返回最后一个空单元格
• ROW(A1)：a1 的行值，为“1”，随着公式下拉，会产生步长为 1 的序列值
• small(...,row(a1))：依次取出数组中第 n 小的值，这个 n 就是 row() 函数返回的值；这一组第 n 小的值就是每个班级名第一次出现时的行值
• 最后用 index 函数根据行值，查找出班级
• 这是个数组公式，所以最后一定要按 Ctrl Shift Enter 结束

现在根据班级一对多查找姓名：

3. 在 F2 单元格输入以下公式，按 Ctrl Shift Enter 使数组公式生效，向右向下拖动复制公式：：

=INDEX($B:$B,SMALL(IF($A$2:$A$10=$E2,ROW($A$2:$A$10),4^8),COLUMN(A1)))&""

公式释义：

再次使用万金油公式，挑不同之处解释

• $A$2:$A$10=$E2：将 A 列中的班级名与 E2 匹配
• COLUMN(A1)：因为这次表格转置了，向右拖动的时候 row 函数结果不会递增，所以改用 column 函数
• &""：当找到 65536 行时，index 会返回“0”，为了不显示无意义的“0”值，&"" 的作用是把数值转换为文本，“0”就不会显示出来了。

1. 在“班级”左边增加一个辅助列，公式如下：

=B2&E2

• E 列就是解决方案 1 中的辅助列 D，只是增加了 A 列之后顺序右移了
• F 列也是在解决方案 1 中用第一个万金油公式提取出的班级名

2. 在 G2 单元格输入以下公式，向右向下拖动复制公式：

=iferror(VLOOKUP($F2&COLUMN(A1),$A:$C,3,0),"")

公式释义：

• $F2&COLUMN(A1)：向右拖动到时候，column 函数会返回步长为 1 的序列值，结果就为“一班1”，“一班2”……这就与 A 列的值匹配上了
• vlookup 是大家最熟悉的配方，就不多解释了
• 最后用 iferror() 函数将错误值显示为空