跨多工作表按日期匹配求和对应单元格的Excel公式咨询

核心前提 #

先在汇总表的任意空白区域（比如Z1:Z300），准确录入所有300个房间工作表的名称，名称需与实际工作表完全一致（含空格、特殊字符）。

方案一：Excel 365/2021 动态数组公式 #

假设汇总表的日期列在A列（从A2开始），在B2单元格输入以下公式，回车后下拉填充至所有日期行：

=SUM(BYROW(Z$1:Z$300,LAMBDA(sheet,LET(
    all_data,INDIRECT("'"&sheet&"'!A:XFD"),
    match_row,XMATCH(A2,all_data,0),
    IF(ISNUMBER(match_row),SUM(INDEX(all_data,match_row,XMATCH(A2,all_data,0)+1):INDEX(all_data,match_row,XMATCH(A2,all_data,0)+2)),0)
))))

公式说明 #

• BYROW遍历每个房间工作表名称
• INDIRECT("'"&sheet&"'!A:XFD")引用当前工作表的全部数据区域
• XMATCH(A2,all_data,0)定位当前日期在该工作表中的行号
• 若找到匹配行，自动取日期所在列右侧1-2列的数值求和；无匹配则返回0
• 最终用SUM汇总所有工作表的结果

方案二：兼容旧版Excel（无动态数组） #

在B2单元格输入以下数组公式，按Ctrl+Shift+Enter完成输入，再下拉填充：

=SUMPRODUCT(IFERROR(
    SUM(INDEX(INDIRECT("'"&Z$1:Z$300&"'!A:XFD"),
        MATCH(A2,INDIRECT("'"&Z$1:Z$300&"'!A:XFD"),0),
        MATCH(A2,INDIRECT("'"&Z$1:Z$300&"'!A:XFD"),0)+1):
    INDEX(INDIRECT("'"&Z$1:Z$300&"'!A:XFD"),
        MATCH(A2,INDIRECT("'"&Z$1:Z$300&"'!A:XFD"),0),
        MATCH(A2,INDIRECT("'"&Z$1:Z$300&"'!A:XFD"),0)+2)
    ),0)

公式说明 #

• 用MATCH替代新版的XMATCH定位日期行与列
• IFERROR处理无匹配日期的工作表，避免返回错误值
• SUMPRODUCT完成多工作表结果的汇总

注意事项 #

• 工作表名称必须准确无误，否则公式会返回错误
• 若某工作表的日期列存在重复值，公式会匹配第一个出现的日期对应的右侧数值
• 公式引用整列区域（A:XFD）会自动适配任意位置的日期列与用水量列，无需指定固定单元格

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