Excel多相邻Project-Section-Amount数据集批量SUMIFS求和求助

假设你的数据结构如下：

• A列为发票日期
• B:D为第1组（Project1, Section1, Amount1）
• E:G为第2组（Project2, Section2, Amount2）
• ...最多30组，每组占3列

指定条件存放在：

• J1：目标Project
• J2：目标Section
• J3：目标月份（日期格式，如2023/10/1）

方案1：兼容旧版Excel的数组公式 #

使用SUMPRODUCT结合OFFSET遍历所有30组数据，同时过滤日期条件：

=SUMPRODUCT(
    --(MONTH($A$2:$A$100)=MONTH($J$3)),
    --(YEAR($A$2:$A$100)=YEAR($J$3)),
    SUMPRODUCT(
        --(OFFSET($B$2:$B$100,,(ROW($1:$30)-1)*3)=$J$1),
        --(OFFSET($C$2:$C$100,,(ROW($1:$30)-1)*3)=$J$2),
        OFFSET($D$2:$D$100,,(ROW($1:$30)-1)*3)
    )
)

• 外层SUMPRODUCT：筛选出日期属于指定年月的行
• 内层SUMPRODUCT：遍历30组数据，每组的Project列（B/E/H...）、Section列（C/F/I...）匹配条件时，对对应Amount列（D/G/J...）求和
• 旧版Excel需按Ctrl+Shift+Enter确认数组公式

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

利用TOCOL合并多组列，再用FILTER筛选求和，公式更简洁：

=SUM(
    FILTER(
        TOCOL(D2:ZZ100,3,1),
        (TOCOL(B2:YY100,3,1)=J1)*
        (TOCOL(C2:ZZ100,3,1)=J2)*
        (MONTH(A2:A100)=MONTH(J3))*
        (YEAR(A2:A100)=YEAR(J3))
    )
)

• TOCOL(...,3,1)：按列合并多组数据，忽略空值
• FILTER：同时匹配Project、Section、日期条件，筛选出符合要求的Amount值
• 直接按回车即可，无需数组公式确认

方案3：Power Query（适合大量数据/重复使用） #

如果数据量较大或需要反复更新结果，Power Query更高效：

1. 选中数据区域，点击「数据」→「从表格/区域」导入编辑器
2. 选中日期列，然后选中所有Project/Section/Amount列，点击「转换」→「逆透视列」→「逆透视其他列」
3. 添加自定义列提取组号：= Text.AfterDelimiter([Attribute], "Project", 0)，命名为「组号」
4. 修改属性列保留类型：= Text.BeforeDelimiter([Attribute], [组号])
5. 点击「转换」→「透视列」，以「属性」为透视列，「值」为值列，选择「不要聚合」
6. 添加年月列：= Date.Month([日期])、= Date.Year([日期])
7. 筛选Project=J1、Section=J2、月份=MONTH(J3)、年份=YEAR(J3)，对Amount列求和后加载回Excel

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