一对多查找,一直是 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 的字体颜色设置为白色。
联系客服