如何用SUMPRODUCT函数返回二维动态数组（Excel）

假设你的原数据结构为：行轴（如日期/类别）在A列（A2:A7），列轴（Alpha/Beta/Delta）在第一行（B1:D1），数值区域为B2:D7。以下是两种可行的实现方式：

方法1：结合LET + MAKEARRAY + SUMPRODUCT（可读性更高） #

直接输入以下公式，会自动生成包含6行3列的动态汇总数组：

=LET(
    row_unique, UNIQUE(A2:A7),
    col_unique, UNIQUE(B1:D1),
    MAKEARRAY(ROWS(row_unique), COLUMNS(col_unique),
        LAMBDA(r,c,
            SUMPRODUCT(
                --(A2:A7=INDEX(row_unique,r)),
                --(B1:D1=INDEX(col_unique,c)),
                B2:D7
            )
        )
    )
)

公式解析： #

• UNIQUE提取行/列轴的唯一值，确保汇总表的行列与原数据唯一值对应
• MAKEARRAY创建指定大小的动态数组，通过LAMBDA遍历每个单元格位置
• 每个交叉点用SUMPRODUCT匹配行/列条件，汇总对应数值（--将布尔判断结果转为1/0，满足SUMPRODUCT的数组运算要求）

方法2：SUMPRODUCT结合数组广播（更简洁） #

如果你的Excel版本支持数组广播（365/2021+），可以用更短的公式：

=SUMPRODUCT(
    --(A2:A7=TRANSPOSE(UNIQUE(A2:A7))),
    --(B1:D1=UNIQUE(B1:D1)),
    B2:D7
)

公式解析： #

• TRANSPOSE(UNIQUE(A2:A7))将行唯一值转为纵向数组，与原行轴区域做广播匹配
• 列唯一值直接与原列轴区域广播匹配
• SUMPRODUCT自动对三个数组对应位置相乘后求和，生成二维汇总结果

注意事项： #

• 仅支持Excel 365/2021及以上版本，因为需要依赖动态数组函数（UNIQUE、MAKEARRAY、LAMBDA）和数组广播特性
• 原数据中的空值会被SUMPRODUCT自动忽略，无需额外处理

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