多年度二维Excel表格按用户汇总：优先取Actual值的公式需求

不需要借助VBA，用公式就能实现需求，以下分两种场景给出解决方案：

一、适合Excel 365/2021（支持动态数组和LAMBDA） #

假设你的表头格式为[年度] [季度] Actual和[年度] [季度] Budget（例如2023 Q1 Actual、2024 Q2 Budget），数据区域为A3:Z10（A列是用户，第2行是表头），要汇总的用户列在A13:A16，对应汇总结果在B13:B16，在B13输入以下公式后下拉即可：

=SUM(
    BYCOL(
        FILTER($C$2:$Z$2,RIGHT($C$2:$Z$2,6)="Actual"),
        LAMBDA(actual_header,
            LET(
                period, LEFT(actual_header,LEN(actual_header)-7),
                budget_header, period&" Budget",
                actual_val, XLOOKUP($A13,$A$3:$A$10,XLOOKUP(actual_header,$C$2:$Z$2,$C$3:$Z$10),0),
                budget_val, XLOOKUP($A13,$A$3:$A$10,XLOOKUP(budget_header,$C$2:$Z$2,$C$3:$Z$10),0),
                IF(actual_val<>0, actual_val, budget_val)
            )
        )
    )
)

公式说明： #

• FILTER($C$2:$Z$2,RIGHT($C$2:$Z$2,6)="Actual")：自动筛选出所有带Actual标识的表头列，支持新增任意年度的季度列。
• BYCOL(..., LAMBDA(...))：遍历每个Actual列，对单个季度数据进行处理。
• period：提取季度+年度标识（例如从2023 Q1 Actual中得到2023 Q1），以此匹配对应的Budget表头。
• XLOOKUP：分别查找当前用户在该季度的Actual和Budget值，找不到则返回0。
• IF(actual_val<>0, actual_val, budget_val)：优先取非零的Actual值，否则取Budget值。
• SUM：将所有季度的有效数值汇总，得到该用户的全年度/多年度总和。

二、适合旧版Excel（无动态数组支持） #

如果你的Excel版本不支持LAMBDA和动态数组，可使用SUMPRODUCT结合数组公式（输入时需按Ctrl+Shift+Enter确认）：

=SUMPRODUCT(
    ($A$3:$A$10=$A13)*
    (
        IFERROR(INDEX($C$3:$Z$10,ROW($A$3:$A$10)-ROW($A$3)+1,TRANSPOSE(MATCH("*Actual",$C$2:$Z$2,0))),0)*
        --(IFERROR(INDEX($C$3:$Z$10,ROW($A$3:$A$10)-ROW($A$3)+1,TRANSPOSE(MATCH("*Actual",$C$2:$Z$2,0))),0)<>0) +
        IFERROR(INDEX($C$3:$Z$10,ROW($A$3:$A$10)-ROW($A$3)+1,TRANSPOSE(MATCH("*Budget",$C$2:$Z$2,0))),0)*
        --(IFERROR(INDEX($C$3:$Z$10,ROW($A$3:$A$10)-ROW($A$3)+1,TRANSPOSE(MATCH("*Actual",$C$2:$Z$2,0))),0)=0)
    )
)

公式说明： #

• TRANSPOSE(MATCH("*Actual",$C$2:$Z$2,0))：获取所有Actual列的列号并转成横向数组，遍历所有季度。
• INDEX：提取每个用户在对应季度的Actual和Budget值。
• --(...)：将逻辑判断转为0/1的数值，实现"非零取Actual，否则取Budget"的逻辑。
• SUMPRODUCT：按用户分组，将符合条件的数值汇总。

注意事项 #

1. 表头需保证Actual和Budget标识统一（避免大小写不一致、拼写错误）。
2. 新增年度季度列时，只需扩展数据区域的引用范围（例如把$C$2:$Z$2改为$C$2:$AA$2），公式会自动识别新的Actual/Budget列。
3. 如果Actual和Budget列的表头格式不同（例如Actual 2023 Q1），只需调整LEFT/RIGHT的截取长度，匹配对应的表头规则即可。

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