如何使用带多条件的命名范围跨Google Sheets/Excel多工作表求和

需求背景 #

• 所有功能可通过原生Google Sheets公式实现，逻辑兼容Excel，可无缝迁移
• 已预设命名范围Heroes，包含所有英雄名称：Bilbo、Gandalf、Saruman、Wormtongue、Tom Bombadil，后续新增角色仅需更新该命名范围即可
• 每个英雄对应独立工作表，表内固定4列：Date（日期）、Time（时间）、Quest（任务类型）、Count（计数），每行对应一条可通过日期+时间唯一标识的任务记录
• 需在汇总工作表实现两个核心功能：
  1. 自动提取所有英雄工作表中的唯一日期+时间组合，优先通过ARRAYFORMULA或等效函数实现，无需手动录入
  2. 按汇总表每行的日期、时间、任务类型条件，对所有英雄的对应计数跨表求和

现有问题 #

汇总表的日期时间组合不一定存在于所有英雄工作表中，无法直接使用SUMIFS实现；尝试SUMPRODUCT或INDEX(MATCH)方案时，引入命名范围读取多工作表数据仅能累加第一个英雄的数值。

解决方案 #

1. 自动提取唯一日期时间组合 #

使用REDUCE遍历命名范围+UNIQUE去重实现全量数据自动提取，公式可自动溢出填充，无需手动下拉：

=ARRAYFORMULA(UNIQUE(REDUCE(, Heroes, LAMBDA(acc, hero, 
  {acc; FILTER(INDIRECT("'"&hero&"'!A2:B"), INDIRECT("'"&hero&"'!A2:A")<>""))}
)))

公式逻辑：遍历Heroes内的所有工作表名，拼接为工作表引用后提取非空的日期、时间列数据，合并后去重得到所有唯一的日期+时间组合。

2. 跨表按条件求和 #

单格下拉版本

公式放在汇总表的计数列首行，下拉即可应用到所有行：

=SUM(REDUCE(, Heroes, LAMBDA(acc, hero, 
  {acc; SUMIFS(INDIRECT("'"&hero&"'!D:D"), 
    INDIRECT("'"&hero&"'!A:A"), A2, 
    INDIRECT("'"&hero&"'!B:B"), B2,
    INDIRECT("'"&hero&"'!C:C"), C2)}
)))

注：A2对应汇总表日期列、B2对应时间列、C2对应任务类型列，无匹配数据时自动返回0，不会出现报错。

整列自动计算版本

无需手动下拉，新增数据自动计算：

=BYROW(A2:A, LAMBDA(dt, IF(dt="",,
  SUM(REDUCE(, Heroes, LAMBDA(acc, hero, 
    {acc; SUMIFS(INDIRECT("'"&hero&"'!D:D"), 
      INDIRECT("'"&hero&"'!A:A"), dt, 
      INDIRECT("'"&hero&"'!B:B"), OFFSET(dt,0,1),
      INDIRECT("'"&hero&"'!C:C"), OFFSET(dt,0,2))}
  )))
)))

内容的提问来源于stack exchange，提问作者Aleister Tanek Javas Mraz