请求将Google Sheets人工成本公式迁移适配至Excel

我在Google Sheets里写了一个复杂公式，但数据量超过80行时Sheets就卡得不行，想迁移到Excel里用。但Excel不支持我用的「MAP嵌套MAP」的数组内数组写法，求帮忙修改公式适配Excel。

背景 #

公司案件负责人会在特定时间段负责案件，一人可同时管多个案件。我需要计算每个案件的人工成本乘数：

• 单案件场景：比如时薪20美元，案件从2025年1月1日14:00到2025年1月8日9:00，按工作日早8到晚5（不含周末）算，工作时长40小时，人工成本就是40*20=800美元，乘数就是40。
• 多案件重叠场景：比如2025年1月7日16:00接手第二个案件，那有2小时同时处理两个案件。此时第一个案件的人工成本是(3820)+(220)/2=780美元，相当于39小时×20美元（38小时/1案件 + 2小时/2案件），我要算的就是这个39的乘数。

Google Sheets中的原公式 #

=LET(
createDate,A2:A8,
retainDate,ARRAYFORMULA(B2:B8-1/24),
names,C2:C8,
morningTimes,SEQUENCE(7,1,ROUND(TIME(8,0,0),3),0),
eveningTimes,SEQUENCE(7,1,ROUND(TIME(17,0,0),3),0),
sundays,SEQUENCE(7,1,1,0),
saturdays,SEQUENCE(7,1,7,0),
MAP(createDate,retainDate,names,LAMBDA(create,retain,name,
SUM(MAP(SEQUENCE((retain-create+1/24)*24,1,create,1/24),LAMBDA(t,IFERROR(1/SUMPRODUCT(names=name,ROUND(t,6)>=ARRAYFORMULA(ROUND(createDate,6)),ROUND(t,6)<=ARRAYFORMULA(ROUND(retainDate,6)),morningTimes<=ROUND(MOD(t,1),3),eveningTimes>ROUND(MOD(t,1),3),saturdays<>WEEKDAY(t),sundays<>WEEKDAY(t)),0))))))

示例数据 #

Excel适配后的公式 #

=LET(
createDate,A2:A8,
retainDate,B2:B8-1/24,
names,C2:C8,
morningTime,ROUND(TIME(8,0,0),3),
eveningTime,ROUND(TIME(17,0,0),3),
MAP(createDate,retainDate,names,LAMBDA(create,retain,name,
SUM(
    BYROW(
        SEQUENCE(INT((retain-create)*24)+1,1,create,1/24),
        LAMBDA(t,
            IFERROR(
                1/COUNT(
                    FILTER(
                        names,
                        names=name,
                        ROUND(t,6)>=ROUND(createDate,6),
                        ROUND(t,6)<=ROUND(retainDate,6),
                        morningTime<=ROUND(MOD(t,1),3),
                        eveningTime>ROUND(MOD(t,1),3),
                        WEEKDAY(t,2)<=5
                    )
                ),
                0
            )
        )
    )
))
)

修改说明 #

• 移除ARRAYFORMULA：Excel中数组运算自动溢出，不需要额外包裹。
• 简化时间与周末判断：原公式重复生成7行时间/周末标识，直接用单个时间值替代，周末判断改用WEEKDAY(t,2)<=5（1-5为工作日），逻辑更简洁。
• 替换内层MAP为BYROW：Excel中用BYROW处理单值序列更稳定，避免数组嵌套的兼容性问题。
• 替换SUMPRODUCT为COUNT+FILTER：用FILTER筛选符合条件的案件，COUNT统计数量，逻辑更清晰，适配Excel数组环境。
• 优化SEQUENCE长度计算：用INT((retain-create)*24)+1避免浮点误差导致的序列长度错误。

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