多条件双表下Excel SUMPRODUCT函数计算问题求助

需求说明 #

需制作报表，计算数据表中金额与查找表匹配因子的SUMPRODUCT()值，因子选取满足多条件，且不能使用辅助行列（实际表规模大）：

• 范围等于单元格B1的值（"Report A"）；
• 资产为"B"时计算"B"和"GB"，资产为"E"仅算"E"，资产为"F"仅算"F"；
• 因子匹配规则：
  • 匹配报表首行列标识与查找表"column"列；
  • 资产为GB/F直接匹配对应因子；资产为B/E且entity_type为B/I时匹配对应entity_type因子，否则匹配对应资产因子。

预期结果示例：D2=1000*.21+2000*.2+1200*.2+3000*.33

已尝试的问题公式 #

第一个公式 #

=SUMPRODUCT(
    --(Data[Scope] = $B$1);
    --(IF(
        $A2 = "B";
        (Data[asset] = "B") + (Data[asset] = "GB");
        Data[asset] = $A2
    ));
    INDEX(
        Data;
        0;
        MATCH(
            INDEX(
                LookupTable[lookup_column];
                MATCH(
                    1;
                    (LookupTable[asset] = IF($A2 = "B"; "B";$A2)) *
                    (LookupTable[entity_type] = IF(OR(Data[entity_type] = "B";Data[entity_type] = "I");Data[entity_type];"") *
                    (LookupTable[column] = D1);
                    0
                )
            );
            Data[#Headers];
            0
        )
    ) ; Data[Amount]
) 

问题：公式求值发现第二个INDEX(MATCH())返回数组长度与数据表行数不符，加IFNA(;0)后结果为0且仅匹配Factor1，不符合需求。

第二个公式 #

=SUMPRODUCT(
    --(Data[Scope] = $B$1);
    --(IF(
        OR($A2 = "B"; $A2 = "E");
        (Data[asset] = $A2) + (Data[asset] = IF($A2 = "B"; "GB"; ""));
        Data[asset] = $A2
    ));
    Data[Amount]*
    IFERROR(
        --(
            INDEX(
                Data;
                0;
                MATCH(
                    IF(
                        OR($A2="B"; $A2="E");
                        IF(
                            OR(Data[entity_type] ="B"; Data[entity_type] ="I");
                            INDEX(
                                LookupTable[lookup_column];
                                MATCH(
                                    1;
                                    (LookupTable[entity_type] = Data[entity_type]) *
                                    (LookupTable[column] = D$1);
                                    0
                                )
                            );
                            INDEX(
                                LookupTable[lookup_column];
                                MATCH(
                                    1;
                                    (LookupTable[asset] = $A2) *
                                    (LookupTable[column] = D$1);
                                    0
                                )
                            )
                        );
                        INDEX(
                            LookupTable[lookup_column];
                            MATCH(
                                1;
                                (LookupTable[asset] = $A2) *
                                (LookupTable[column] = D$1);
                                0
                            )
                        )
                    );
                    Data[#Headers];
                    0
                )
            )
        );
        0
    )
)

问题：始终仅返回INDEX()匹配的第一个因子值，不符合要求。

解决方案 #

问题根源 #

1. 两个公式中的MATCH函数默认仅返回第一个匹配项，无法生成对应数据表行数的数组，导致计算时数组长度不匹配；
2. 逻辑判断未针对数据表每一行的asset和entity_type做动态适配，因子匹配逻辑未逐行生效。

适用Excel 365/2021的公式 #

用XLOOKUP实现逐行动态匹配，满足所有条件：

=SUMPRODUCT(
    --(Data[Scope]=$B$1),
    --(IF($A2="B",(Data[asset]="B")+(Data[asset]="GB"),Data[asset]=$A2)),
    Data[Amount],
    XLOOKUP(
        IF(
            (Data[asset]="GB")+(Data[asset]="F"),
            Data[asset],
            IF(
                (Data[asset]="B")+(Data[asset]="E"),
                IF((Data[entity_type]="B")+(Data[entity_type]="I"),Data[entity_type],Data[asset]),
                Data[asset]
            )
        )&D$1,
        LookupTable[匹配标识列]&LookupTable[column],
        LookupTable[因子值列],
        0
    )
)

公式说明

• 先构造每一行的匹配标识：如果是GB/F直接用资产；如果是B/E且entity_type为B/I则用entity_type，否则用资产；
• 将匹配标识与表头D$1拼接成唯一键，用XLOOKUP在查找表中匹配对应因子，返回逐行的因子数组；
• 最后通过SUMPRODUCT把范围条件、资产筛选、金额、因子数组相乘求和。

旧版Excel兼容方案 #

如果无法使用XLOOKUP，用INDEX/MATCH数组公式（需按Ctrl+Shift+Enter确认）：

=SUMPRODUCT(
    --(Data[Scope]=$B$1),
    --(IF($A2="B",(Data[asset]="B")+(Data[asset]="GB"),Data[asset]=$A2)),
    Data[Amount],
    INDEX(
        LookupTable[因子值列],
        MATCH(
            IF(
                (Data[asset]="GB")+(Data[asset]="F"),
                Data[asset],
                IF(
                    (Data[asset]="B")+(Data[asset]="E"),
                    IF((Data[entity_type]="B")+(Data[entity_type]="I"),Data[entity_type],Data[asset]),
                    Data[asset]
                )
            )&D$1,
            LookupTable[匹配标识列]&LookupTable[column],
            0
        )
    )
)

注意事项

• 查找表需新增匹配标识列，将资产/entity_type列与column列拼接（如=A2&C2）；
• 必须按Ctrl+Shift+Enter输入公式，否则无法生成正确的数组结果。

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