Excel基于跨表数据实现动态自动填充行的解决方案咨询

以下方案均无需使用数据透视表，可适配最多50个业务单元的使用场景，满足人员列表随源数据自动同步更新的需求： #

方案1：Excel 365/2021 动态数组方案（优先推荐） #

直接使用FILTER动态数组函数即可实现自动溢出匹配结果，无需手动拖拽公式：

1. 假设员工源数据工作表名称为员工源数据，A列为员工姓名，B列为所属业务单元（即Function 1/2等标识）
2. 在当前工作表Function 1标题下方的首个空白单元格输入公式：
   =FILTER(员工源数据!A:A, 员工源数据!B:B="Function 1", "无匹配人员")
3. 适配多业务单元场景时，可直接引用上方的业务单元标题单元格简化配置，例如Function 1标题存放在A1单元格，公式可修改为：
   =FILTER(员工源数据!A:A, 员工源数据!B:B=A1, "无匹配人员")

• 效果：公式会自动向下溢出所有匹配的人员姓名，源数据新增/删除对应业务单元的员工时，列表会实时自动更新，无需任何手动操作。

方案2：Excel 2019及更早版本兼容方案 #

旧版本Excel无动态数组功能，可使用INDEX+SMALL+IF组合数组公式实现：

1. 预估单个业务单元最多的人员数量，比如最多100人，就选中Function 1标题下方的100个空白单元格
2. 输入公式：
   =IFERROR(INDEX(员工源数据!A:A,SMALL(IF(员工源数据!B:B="Function 1",ROW(员工源数据!A:A),99999),ROW(A1))),"")
3. 按下Ctrl+Shift+Enter激活数组公式，选中的所有单元格会自动填充结果，多余位置显示空白

• 效果：源数据更新后按F9即可刷新列表，无需手动调整公式范围，适配多业务单元时修改公式内的业务单元标识即可。

方案3：无函数实现方案（高级筛选联动） #

不会写公式可选择高级筛选方案：

• 选中员工源数据整表，点击「数据」选项卡→「高级」筛选
• 筛选规则选择「将筛选结果复制到其他位置」，列表区域选员工源数据整表，条件区域选当前表的业务单元标题单元格，复制到选姓名列表的起始单元格
• 后续源数据更新后，右键筛选结果区域点击「刷新」即可同步，也可搭配简单VBA宏实现打开文件自动刷新。

内容的提问来源于stack exchange，提问作者EmergentEcon