多Excel工作表值检索及待分配列表去重优化方案咨询

方法1：合并所有已分配区域为单个数组（通用解法） #

核心思路是把所有工作表的已分配区域（H10:H50）纵向堆叠成一个无空白的一维数组，仅用一次XMATCH完成匹配检查，避免重复编写多个判断逻辑。

公式：

=FILTER(TableQuery[Item],
  NOT(ISNUMBER(XMATCH(TableQuery[Item], FILTER(VSTACK(Sheet1!$H$10:$H$50, Sheet2!$H$10:$H$50, Sheet3!$H$10:$H$50), VSTACK(Sheet1!$H$10:$H$50, Sheet2!$H$10:$H$50, Sheet3!$H$10:$H$50)<>""), 0)))
  * ((TableQuery[Criteria1]<>"") + (TableQuery[Criteria2]))
)

• VSTACK：将多个工作表的目标区域纵向合并为一个大数组
• FILTER(..., ...<>"")：剔除合并数组中的空白单元格，避免误匹配空白值
• 新增工作表时，仅需在VSTACK中追加对应的区域引用即可，公式长度不会随工作表数量线性膨胀

方法2：工作表命名有规律时用批量引用（适合Sheet1~SheetN） #

若工作表按Sheet1、Sheet2…Sheet100这类规律命名，可通过INDIRECT+SEQUENCE自动生成所有区域引用，无需手动逐个输入工作表名。

公式：

=FILTER(TableQuery[Item],
  NOT(ISNUMBER(XMATCH(TableQuery[Item], FILTER(VSTACK(INDIRECT("Sheet"&SEQUENCE(100)&"!$H$10:$H$50")), VSTACK(INDIRECT("Sheet"&SEQUENCE(100)&"!$H$10:$H$50"))<>""), 0)))
  * ((TableQuery[Criteria1]<>"") + (TableQuery[Criteria2]))
)

• SEQUENCE(100)：生成1到100的连续序列，对应100个工作表
• INDIRECT：将序列拼接成合法的工作表区域引用，自动批量生成所有目标区域

方法3：定义名称简化公式（适合无规律命名的工作表） #

若工作表命名无规律（如按部门、项目名命名），可通过自定义名称统一管理所有已分配区域，让主公式更简洁。

1. 操作步骤：
   • 打开「公式」选项卡 → 点击「定义名称」
   • 名称设为AllAssignedItems，引用位置输入：

     =FILTER(VSTACK(市场部!$H$10:$H$50,研发部!$H$10:$H$50,销售部!$H$10:$H$50), VSTACK(市场部!$H$10:$H$50,研发部!$H$10:$H$50,销售部!$H$10:$H$50)<>"")
     

2. 主公式简化为：

   =FILTER(TableQuery[Item], NOT(ISNUMBER(XMATCH(TableQuery[Item], AllAssignedItems, 0))) * ((TableQuery[Criteria1]<>"") + (TableQuery[Criteria2])))
   

• 后续新增工作表时，仅需修改自定义名称的引用位置，主公式无需调整

你之前的简化公式失效原因 #

你使用的{Sheet1!$H$10:$H$50;Sheet2!$H$10:$H$50;Sheet3!$H$10:$H$50}写法会生成二维数组，而XMATCH默认仅支持一维数组查找，导致匹配逻辑出错。VSTACK是Excel专门用于纵向堆叠区域、生成一维数组的函数，能解决这个问题。

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