如何在MS Excel 2019中用公式筛选多Territory的Zone并生成结果表

方法1：数组公式法（无需手动筛选） #

由于Excel 2019不支持动态数组函数（如FILTER），可以结合INDEX、SMALL、COUNTIF和数组公式实现需求：

提取Zone列（Table2的A2单元格） #

输入以下公式后，按Ctrl+Shift+Enter完成数组公式输入，下拉填充直到出现空值：

=IFERROR(INDEX(Table1[Zone], SMALL(IF(COUNTIF(Table1[Zone], Table1[Zone])>1, ROW(Table1[Zone])-MIN(ROW(Table1[Zone]))+1, ""), ROW(A1))), "")

提取Territory列（Table2的B2单元格） #

同样按Ctrl+Shift+Enter输入，下拉填充：

=IFERROR(INDEX(Table1[Territory], SMALL(IF(COUNTIF(Table1[Zone], Table1[Zone])>1, ROW(Table1[Zone])-MIN(ROW(Table1[Zone]))+1, ""), ROW(A1))), "")

公式说明：

• COUNTIF(Table1[Zone], Table1[Zone])：计算每个Zone对应的Territory数量（即该Zone在列表中出现的次数）
• IF(..., ROW(...) - MIN(ROW(...)) +1, "")：筛选出Territory数量>1的行，返回其在Table1中的相对行号，否则返回空值
• SMALL(..., ROW(A1))：依次提取第1、2、3...个符合条件的行号，下拉时ROW(A1)自动递增为ROW(A2)、ROW(A3)等
• INDEX：根据行号从Table1中提取对应的Zone或Territory
• IFERROR：避免无符合条件行时出现#NUM!错误，返回空值

方法2：高级筛选法（操作更直观） #

如果觉得公式复杂，可通过Excel内置的高级筛选功能快速实现：

1. 设置条件区域：

   • 复制Table1的Zone表头到空白单元格（比如D1）
   • 在D2单元格输入公式：=COUNTIF(Table1[Zone], A2)>1（A2是Table1中第一个Zone数据行）

2. 执行高级筛选：

   • 选中Table1的整个数据区域（包括表头）
   • 点击【数据】选项卡 → 【高级】按钮
   • 在弹出的对话框中：
     • 选择「将筛选结果复制到其他位置」
     • 「列表区域」：选择Table1的完整数据范围（含表头）
     • 「条件区域」：选择刚才设置的D1:D2区域（表头+条件公式）
     • 「复制到」：选择Table2表头下方的第一个单元格（比如G2）
   • 点击确定，所有符合条件的Zone和Territory会自动复制到Table2中

注意：若Table1数据更新，需重新执行高级筛选；公式法则只需刷新或重新下拉填充即可。

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