Excel – 一对多查找，表格还转置，你用哪种方法？
Excel 查找方式千千万，但是下面这种布局的查找，你会吗？
总结一下，这个查找涉及这几个知识点：一对多查找，表格转置，二维变一维（原本两列内容，查找后放在同一行）。
案例：
图 1 是原始表格，需要按班级提取所有人的姓名，向右填充，布局如图 2 的右边所示。
解决方案 1：万金油公式
先将班级列表去重，提取到右边蓝色区域：
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”就不会显示出来了。
解决方案 2：vlookup
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() 函数将错误值显示为空
赞 (0)