如何简化每日新增日期工作表的SUM公式？提升汇总效率

方法1：用INDIRECT+TEXT函数动态生成引用 #

如果你的工作表命名规则固定（比如Shift 1 (May 2)这种格式），可以用动态公式自动匹配日期对应的工作表，不用手动添加引用：

假设起始统计日期是2024年5月2日，要统计到当日的所有工作表，直接在汇总单元格输入：

=SUMPRODUCT(SUM(INDIRECT("'Shift 1 ("&TEXT(DATE(2024,5,2)+ROW(INDIRECT("1:"&TODAY()-DATE(2024,5,2)+1)),"MMM D")&")'!AY142:AY145")))

说明： #

• DATE(2024,5,2)替换成你实际的统计起始日期
• TODAY()-DATE(2024,5,2)+1自动计算从起始日到今天的天数，生成连续日期序列
• TEXT(..., "MMM D")把日期转换成和工作表名一致的格式（比如May 2、Jun 1）
• INDIRECT将文本格式的表名转换成有效的单元格区域引用
• SUMPRODUCT嵌套SUM实现多区域批量求和

方法2：用名称管理器定义动态工作表列表 #

通过名称管理器批量抓取符合规则的工作表，让公式自动识别新增的表：

1. 点击「公式」选项卡→「名称管理器」→「新建」
2. 名称设为DailySheets，引用位置输入（根据情况二选一）：
   • 若支持宏表函数：

     =FILES("'Shift 1 (*)'")
     

   • 通用替代方案（适配更多Excel版本）：

     =FILTER(GET.WORKBOOK(1),ISNUMBER(SEARCH("Shift 1 (",GET.WORKBOOK(1))))
     

3. 在汇总单元格输入公式：

   =SUMPRODUCT(SUM(INDIRECT("'"&DailySheets&"'!AY142:AY145")))
   

新增符合命名规则的工作表后，公式会自动纳入统计，无需手动修改。

方法3：用Power Query一键刷新汇总 #

适合需要长期自动化统计的场景，一次设置永久生效：

1. 点击「数据」选项卡→「获取数据」→「自文件」→「自工作簿」，选择当前工作簿
2. 在导航器里勾选所有以Shift 1 (开头的工作表，选择「仅创建连接」
3. 进入Power Query编辑器，对每个工作表保留AY142:AY145区域，或直接计算该区域的求和值
4. 合并所有工作表的求和结果，加载回Excel汇总表
5. 后续新增工作表后，只需点击汇总表的「刷新」按钮，数据会自动更新

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