如何将多份Excel工作表数据合并至带筛选的指定列表格？

直接将公式中的单个工作表替换为'Electric:Container'!这类跨表范围无法生效，因为HSTACK和FILTER函数不支持批量引用多个工作表的区域。以下是两种可行的实现方案：

方案1：逐个工作表合并（适合工作表数量少的场景） #

直接用VSTACK将8个工作表的指定列数据合并，再应用筛选和排序：

=SORT(FILTER(
  VSTACK(
    HSTACK(Electric!A7:A250,Electric!B7:B250,Electric!C7:C250,Electric!D7:D250,Electric!E7:E250,Electric!F7:F250,Electric!K7:K250,Electric!M7:M250,Electric!N7:N250,Electric!O7:O250,Electric!P7:P250,Electric!W7:W250,Electric!Y7:Y250,Electric!AA7:AA250),
    HSTACK(Sheet2!A7:A250,Sheet2!B7:B250,Sheet2!C7:C250,Sheet2!D7:D250,Sheet2!E7:E250,Sheet2!F7:F250,Sheet2!K7:K250,Sheet2!M7:M250,Sheet2!N7:N250,Sheet2!O7:O250,Sheet2!P7:P250,Sheet2!W7:W250,Sheet2!Y7:Y250,Sheet2!AA7:AA250),
    HSTACK(Sheet3!A7:A250,Sheet3!B7:B250,Sheet3!C7:C250,Sheet3!D7:D250,Sheet3!E7:E250,Sheet3!F7:F250,Sheet3!K7:K250,Sheet3!M7:M250,Sheet3!N7:N250,Sheet3!O7:O250,Sheet3!P7:P250,Sheet3!W7:W250,Sheet3!Y7:Y250,Sheet3!AA7:AA250),
    HSTACK(Sheet4!A7:A250,Sheet4!B7:B250,Sheet4!C7:C250,Sheet4!D7:D250,Sheet4!E7:E250,Sheet4!F7:F250,Sheet4!K7:K250,Sheet4!M7:M250,Sheet4!N7:N250,Sheet4!O7:O250,Sheet4!P7:P250,Sheet4!W7:W250,Sheet4!Y7:Y250,Sheet4!AA7:AA250),
    HSTACK(Sheet5!A7:A250,Sheet5!B7:B250,Sheet5!C7:C250,Sheet5!D7:D250,Sheet5!E7:E250,Sheet5!F7:F250,Sheet5!K7:K250,Sheet5!M7:M250,Sheet5!N7:N250,Sheet5!O7:O250,Sheet5!P7:P250,Sheet5!W7:W250,Sheet5!Y7:Y250,Sheet5!AA7:AA250),
    HSTACK(Sheet6!A7:A250,Sheet6!B7:B250,Sheet6!C7:C250,Sheet6!D7:D250,Sheet6!E7:E250,Sheet6!F7:F250,Sheet6!K7:K250,Sheet6!M7:M250,Sheet6!N7:N250,Sheet6!O7:O250,Sheet6!P7:P250,Sheet6!W7:W250,Sheet6!Y7:Y250,Sheet6!AA7:AA250),
    HSTACK(Sheet7!A7:A250,Sheet7!B7:B250,Sheet7!C7:C250,Sheet7!D7:D250,Sheet7!E7:E250,Sheet7!F7:F250,Sheet7!K7:K250,Sheet7!M7:M250,Sheet7!N7:N250,Sheet7!O7:O250,Sheet7!P7:P250,Sheet7!W7:W250,Sheet7!Y7:Y250,Sheet7!AA7:AA250),
    HSTACK(Container!A7:A250,Container!B7:B250,Container!C7:C250,Container!D7:D250,Container!E7:E250,Container!F7:F250,Container!K7:K250,Container!M7:M250,Container!N7:N250,Container!O7:O250,Container!P7:P250,Container!W7:W250,Container!Y7:Y250,Container!AA7:AA250)
  ),
  (INDEX(VSTACK(Electric!O7:O250,Sheet2!O7:O250,Sheet3!O7:O250,Sheet4!O7:O250,Sheet5!O7:O250,Sheet6!O7:O250,Sheet7!O7:O250,Container!O7:O250),,1) <= 'Portfolio Dashboard'!$D$3) *
  (INDEX(VSTACK(Electric!Y7:Y250,Sheet2!Y7:Y250,Sheet3!Y7:Y250,Sheet4!Y7:Y250,Sheet5!Y7:Y250,Sheet6!Y7:Y250,Sheet7!Y7:Y250,Container!Y7:Y250),,1) <> "C") *
  (INDEX(VSTACK(Electric!C7:C250,Sheet2!C7:C250,Sheet3!C7:C250,Sheet4!C7:C250,Sheet5!C7:C250,Sheet6!C7:C250,Sheet7!C7:C250,Container!C7:C250),,1) <> "")
),1,1)

注意：将公式中的Sheet2至Sheet7替换为实际的8个工作表名称，确保所有工作表的列结构一致。

方案2：动态遍历工作表（适合工作表数量多的场景） #

用MAP函数遍历工作表名称数组，结合INDIRECT动态引用每个工作表的指定列，再合并筛选：

=SORT(FILTER(
  TOCOL(MAP({"Electric","Sheet2","Sheet3","Sheet4","Sheet5","Sheet6","Sheet7","Container"}, 
    LAMBDA(sheet, HSTACK(
      INDIRECT(sheet&"!A7:A250"), INDIRECT(sheet&"!B7:B250"), INDIRECT(sheet&"!C7:C250"),
      INDIRECT(sheet&"!D7:D250"), INDIRECT(sheet&"!E7:E250"), INDIRECT(sheet&"!F7:F250"),
      INDIRECT(sheet&"!K7:K250"), INDIRECT(sheet&"!M7:M250"), INDIRECT(sheet&"!N7:N250"),
      INDIRECT(sheet&"!O7:O250"), INDIRECT(sheet&"!P7:P250"), INDIRECT(sheet&"!W7:W250"),
      INDIRECT(sheet&"!Y7:Y250"), INDIRECT(sheet&"!AA7:AA250")
    ))
  ),3),
  (INDEX(TOCOL(MAP({"Electric","Sheet2","Sheet3","Sheet4","Sheet5","Sheet6","Sheet7","Container"}, LAMBDA(sheet, INDIRECT(sheet&"!O7:O250"))),1) <= 'Portfolio Dashboard'!$D$3) *
  (INDEX(TOCOL(MAP({"Electric","Sheet2","Sheet3","Sheet4","Sheet5","Sheet6","Sheet7","Container"}, LAMBDA(sheet, INDIRECT(sheet&"!Y7:Y250"))),1) <> "C") *
  (INDEX(TOCOL(MAP({"Electric","Sheet2","Sheet3","Sheet4","Sheet5","Sheet6","Sheet7","Container"}, LAMBDA(sheet, INDIRECT(sheet&"!C7:C250"))),1) <> "")
),1,1)

注意：此方案依赖Excel 365或Google Sheets的动态数组函数，同样需要替换数组中的工作表名称为实际名称。

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