让我们来看一个实际的工作例子:
首先展示左侧的原始表格,其中包含了部门、工号、姓名及工资数据。
右侧需要根据部门的条件提取所有员工的姓名。
面对部门与员工之间的一对多关系,我们可以使用以下四种方法来快速解决这一问题。前两种方法适用于所有版本,后两种则需要新版的公式支持。
在数据源的最左侧插入一个辅助列。
然后输入如下公式:
=COUNTIFS($B$2:B2,B2)&B2
记得使用F4键固定第一个B2单元格引用。
这个公式通过计数部门列的出现次数,为每一行添加一个顺序号。
接着,我们可以使用以下公式进行匹配:
=IFERROR(VLOOKUP(COLUMN(A1)&$G2,$A:$D,4,0),"")
其中,VLOOKUP的参数含义如下:
G2单元格标记列固定,A:D列数据固定,查找结果为第4列,0表示精确查找。
结合IFERROR函数,当找不到结果时返回空白值,即可实现一次性匹配。
如果不允许插入辅助列,可以使用万能公式:
=INDEX($C:$C,SMALL(IF($A$2:$A$30=$F2,ROW($2:$30),10000),COLUMN(A1)))&""
虽然公式看起来复杂,但其实逻辑简单:
首先通过IF判断A列是否等于查找值,返回对应的行号,否则返回一个非常大的数字。
使用SMALL函数提取行号,并用INDEX函数获取对应行的结果。
需要注意的是,最新版本才支持此函数公式。
在新版本中,我们只需输入以下公式:
=TOROW(FILTER(C:C,A:A=F2))
FILTER公式筛选出A列中符合条件的C列数据,TOROW公式将竖向数据转换为横向展示。
这样就可以轻松得到结果。
另一个简便的方法是:
=TOROW(IF(A:A=F2,C:C,小h),3)
同样可以得到结果。
计算过程如下,首先是IF公式:
=IF(A:A=F2,C:C,小h)
当A列值等于查找值时,返回C列结果;否则返回“大小h”,由于Excel没有此公式,显示为错误值#NAME?
这里的“小h”可以替换为任意文本,甚至可以是你的名字。
TOROW函数将竖向数据转换为横向,第二参数3表示忽略错误值进行计算,从而得到期望结果。
你是否掌握了这个技巧?不妨动手试试看吧!