跨多工作表按日期范围求和的Excel公式求助

嘿，这个需求我太懂了，跨这么多工作表还要按日期筛选求和，确实比普通的SUM函数要绕一点。我给你准备了两种方案，分别适配不同版本的Excel，你根据自己的情况选就行～

方案一：Excel 365/2021及以上（推荐，更灵活） #

如果你用的是支持动态数组的新版本Excel，这个方案更简洁，还能自动适配所有姓名，不用一个个改公式。

单个姓名的公式（比如Sheet1的B2，对应Abc的Total） #

直接在Sheet1的B2单元格输入下面的公式：

=SUM(
    BYCOL(
        HSTACK(Sheet2:Sheet53!B1:H1, Sheet2:Sheet53!B2:H2),
        LAMBDA(col,
            IF(INDEX(col,1)<TODAY()-2, INDEX(col,2), 0)
        )
    )
)

公式解释：

• Sheet2:Sheet53!B1:H1 会一次性引用所有数据表里的日期行（每个表7个日期，从B1到H1），Sheet2:Sheet53!B2:H2是Abc对应的数值行；
• HSTACK 把每一个日期和它对应的Abc数值配对成一列，比如把15/03/2024和7放在同一列，16/03/2024和3放在同一列，以此类推；
• BYCOL 遍历每一对“日期+数值”，如果日期小于TODAY()-2就保留数值，否则换成0；
• 最后用SUM把所有符合条件的数值加起来，就是你要的结果。

自动适配所有姓名的动态公式 #

如果Sheet1的A列已经列好了所有姓名（比如A2是Abc，A3是Def），可以在Sheet1的B2输入下面的公式，它会自动溢出到下面的行，帮你算出所有姓名的总和：

=BYROW(A2:A3, LAMBDA(name,
    SUM(
        BYCOL(
            HSTACK(Sheet2:Sheet53!B1:H1, XLOOKUP(name, Sheet2:Sheet53!A:A, Sheet2:Sheet53!B:H)),
            LAMBDA(col,
                IF(INDEX(col,1)<TODAY()-2, INDEX(col,2), 0)
            )
        )
    )
))

额外解释：

• XLOOKUP(name, Sheet2:Sheet53!A:A, Sheet2:Sheet53!B:H) 会根据当前姓名，自动找到所有表里对应姓名的数值区域，不用手动指定行号；
• 后面的逻辑和单个姓名的公式一样，BYROW 会遍历A列的每个姓名，逐一计算总和。

方案二：旧版Excel（2019及以下，不支持动态数组） #

如果你的Excel版本比较旧，没法用动态数组，就用这个基于SUMPRODUCT和INDIRECT的方案，虽然要写长一点，但效果一样。

单个姓名的公式（Sheet1的B2，Abc的Total） #

=SUMPRODUCT(
    --(INDIRECT("Sheet"&ROW(INDIRECT("2:53"))&"!B1:H1")<TODAY()-2),
    INDIRECT("Sheet"&ROW(INDIRECT("2:53"))&"!B2:H2")
)

公式解释：

• ROW(INDIRECT("2:53")) 生成2到53的数字，对应你说的Sheet2到Sheet53；
• INDIRECT("Sheet"&...&"!B1:H1") 会依次引用每个Sheet的日期行，--(...) 把“日期是否符合条件”的TRUE/FALSE转成1/0（符合条件是1，不符合是0）；
• 第二个INDIRECT引用每个Sheet里Abc的数值行，SUMPRODUCT会把“符合条件的标记”和对应数值相乘，最后把所有结果加起来，就是符合条件的总和。

适配不同姓名的公式 #

如果要给Def（Sheet1的A3）也计算，把公式里的B2:H2换成B3:H3就行，或者用MATCH自动匹配行号：

=SUMPRODUCT(
    --(INDIRECT("Sheet"&ROW(INDIRECT("2:53"))&"!B1:H1")<TODAY()-2),
    INDIRECT("Sheet"&ROW(INDIRECT("2:53"))&"!B"&MATCH(A2, Sheet2!A:A, 0)&":H"&MATCH(A2, Sheet2!A:A, 0))
)

把这个公式放在Sheet1的B2，下拉到B3，就会自动计算Def的总和了。

注意事项 #

1. 所有数据Sheet的结构必须完全一致：姓名列在A列，日期从B1开始占7列，每个姓名的行号在所有Sheet里要相同（比如Abc都在第2行）；
2. 确保所有日期是Excel能识别的真正日期格式，不是文本！如果是文本的话，TODAY()-2的比较会出错，你可以选中日期单元格，看公式栏是不是显示类似45368的序列值（这是Excel内部的日期存储格式）；
3. 旧版方案里的INDIRECT是易失函数，打开文件或者修改数据时会重新计算，52个Sheet的话可能会有点卡，新版的动态数组方案性能会好很多。

备注：内容来源于stack exchange，提问作者miller75