重叠工作日计算难题：基于Excel多表的加权计算需求

核心需求 #

基于三张表（Slots表、重叠日期及所有者表、所有者系数表），为每个Slot完成以下计算：

1. 算出该Slot与每条重叠日期记录的工作日交集时长
2. 将上述时长与对应所有者的系数相乘后求和
3. 后续需执行：Slot总工作日 × 所有者数量 - 上述求和结果（此步骤无需额外处理）

已知问题：

• 使用SUMPRODUCT无法正确计算重叠工作日，且难以嵌入NETWORKINGDAYS
• 考虑过MINIFS/MAXIFS结合LAMBDA/LET，但担心Excel版本限制及大数据量（样本10倍规模）的性能问题

解决方案 #

方案1：Excel 365/2021 溢出公式（推荐） #

适用于支持动态数组、LET和LAMBDA的版本，逻辑清晰且性能更优，支持自动溢出计算所有Slot结果。

假设表格结构：

• Slots表：A=SlotID，B=SlotStart（开始日期），C=SlotEnd（结束日期）
• 重叠日期及所有者表：D=SlotID，E=OverlapStart（重叠区间开始），F=OverlapEnd（重叠区间结束），G=OwnerID（所有者ID）
• 所有者系数表：H=OwnerID，I=Coefficient（系数值）

在Slots表的D2单元格输入以下公式，自动溢出所有结果：

=LET(
    slotIDs, A2:A,
    slotStarts, B2:B,
    slotEnds, C2:C,
    // 匹配当前Slot的所有重叠记录
    getOverlaps, LAMBDA(id, FILTER(重叠日期及所有者表!D:G, 重叠日期及所有者表!D:D=id)),
    // 计算单条Slot的结果
    calcSlot, LAMBDA(id, start, end,
        LET(
            overlapData, getOverlaps(id),
            overlapStarts, INDEX(overlapData,,2),
            overlapEnds, INDEX(overlapData,,3),
            owners, INDEX(overlapData,,4),
            // 匹配所有者系数
            coeffs, XLOOKUP(owners, 所有者系数表!H:H, 所有者系数表!I:I, 0),
            // 计算每条重叠记录的工作日交集
            workdays, BYROW(
                HSTACK(overlapStarts, overlapEnds, start, end),
                LAMBDA(r, NETWORKINGDAYS(MAX(INDEX(r,1), INDEX(r,3)), MIN(INDEX(r,2), INDEX(r,4))))
            ),
            // 求和时长×系数
            SUMPRODUCT(workdays, coeffs)
        )
    ),
    // 批量计算所有Slot
    MAP(slotIDs, slotStarts, slotEnds, calcSlot)
)

性能优化点：

1. 将三张表转换为Excel结构化表格（按Ctrl+T），公式会自动引用数据区域，避免整列遍历
2. 替换整列引用（如D:G）为实际数据范围（如D2:G10000），减少计算量
3. 若需进一步提升性能，可在Power Query中预处理重叠数据，提前计算每个Slot的重叠工作日与系数乘积，再加载回Excel

方案2：旧版Excel 数组公式 #

适用于不支持LAMBDA的Excel版本，需按Ctrl+Shift+Enter作为数组公式输入（Excel 2019及更早版本）。

在Slots表的D2单元格输入公式，下拉填充：

=SUMPRODUCT(
    --(重叠日期及所有者表!$D$2:$D$10000=A2),
    NETWORKINGDAYS(MAX(B2, 重叠日期及所有者表!$E$2:$E$10000), MIN(C2, 重叠日期及所有者表!$F$2:$F$10000)),
    XLOOKUP(重叠日期及所有者表!$G$2:$G$10000, 所有者系数表!$H$2:$H$10000, 所有者系数表!$I$2:$I$10000, 0)
)

注意事项：

• 必须限制数据范围（如D2:D10000），避免整列引用导致性能急剧下降
• 大数据量下（如10万+行），此方案计算速度会明显变慢，建议优先使用方案1或Power Query预处理

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