如何基于其他单元格内容使用FILTER函数，实现部门受训员工名单联动？

核心解决方案（无需重复写公式/手动下拉） #

假设你的表格结构如下：

• A列：员工姓名（A2:A100，A1为表头「员工姓名」）
• B1:Z1：各部门名称（如「市场部」「技术部」）
• B2:Z100：受训标记（「X」表示已受训）
• G1：部门选择下拉菜单

直接用以下公式就能自动列出选中部门的所有受训员工：

=FILTER(A2:A100, INDEX(B2:Z100,,MATCH(G1,B1:Z1,0))="X", "暂无符合条件的员工")

公式拆解 #

• MATCH(G1,B1:Z1,0)：定位下拉选中的部门在表头中的列序号
• INDEX(B2:Z100,,列序号)：提取该部门对应的整列受训标记数据
• FILTER(...)：筛选出该列标记为「X」的员工姓名，无结果时显示提示文本

关于FILTER作用范围的动态控制 #

完全可以通过其他单元格内容来指定FILTER的作用范围，举两个实用例子：

1. 动态调整员工姓名范围：若在G2输入起始行号、G3输入结束行号，公式可改为：

=FILTER(INDIRECT("A"&G2&":A"&G3), INDEX(INDIRECT("B"&G2&":Z"&G3),,MATCH(G1,B1:Z1,0))="X", "暂无符合条件的员工")

这里用INDIRECT将单元格中的行号文本转换为实际单元格引用，实现范围动态变化。

2. 动态切换数据源区域：如果有多个数据区域（比如不同年份的培训记录），可在G4输入预定义的区域名称（如「2023培训记录」），公式会自动适配对应区域的筛选。

旧版Excel兼容方案（无FILTER函数时） #

如果无法使用动态数组函数，可结合INDEX+SMALL+IF实现下拉筛选，公式需按Ctrl+Shift+Enter作为数组公式输入：

=IFERROR(INDEX(A$2:A$100, SMALL(IF(INDEX(B$2:Z$100,,MATCH(G$1,B$1:Z$1,0))="X", ROW(A$2:A$100)-ROW(A$2)+1), ROW(A1))), "")

下拉该公式即可依次显示结果，无结果时返回空单元格。

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