Google Sheets中使用ARRAYFORMULA计算工时结果不一致问题

问题背景 #

我有一个计算两个日期之间工作时长的公式，单独引用少量行时完全正常，但用ARRAYFORMULA批量计算（适配新增行）时结果出现不可预测的差异。最终用MAP+LAMBDA解决了问题，下面分析差异产生的具体原因。

相关公式 #

• 单个行正常公式（C列）：

=TEXT((NETWORKDAYS.INTL(A2,B2,1,F$7:F$9)-1)*($G$2-$F$2)+IF(NETWORKDAYS.INTL(B2,B2,1,F$7:F$9),MEDIAN(MOD(B2,1),$F$2,$G$2),$G$2)-MEDIAN(NETWORKDAYS.INTL(A2,A2,1,F$7:F$9)*MOD(A2,1),$F$2,$G$2),"[hh]:mm:ss")

• ARRAYFORMULA失效公式（D列）：

=ARRAYFORMULA(
    IF(ROW(B:B)=1, "Array Formula Duration", 
        IF(ISBLANK(B:B),"",
(NETWORKDAYS.INTL(A2:A,B2:B,1,F$7:F$9)-1)*($G$2-$F$2)+IF(NETWORKDAYS.INTL(B2:B,B2:B,1,F$7:F$9),MEDIAN(MOD(B2:B,1),$F$2,$G$2),$G$2)-MEDIAN(NETWORKDAYS.INTL(A2:A,A2:A,1,F$7:F$9)*MOD(A2:A,1),$F$2,$G$2)
  )
)
)

• MAP函数解决公式（E列）：

{"Array Formula Duration";map(A2:A,B2:B,lambda(a,b,if(b="",,TEXT((NETWORKDAYS.INTL(a,b,1,F7:F9)-1)*(G2-F2)+IF(NETWORKDAYS.INTL(b,b,1,F7:F9),MEDIAN(MOD(b,1),F2,G2),G2)-MEDIAN(NETWORKDAYS.INTL(a,a,1,F7:F9)*MOD(a,1),F2,G2),"[hh]:mm:ss"))))}

差异原因分析 #

1. ARRAYFORMULA对部分函数的数组处理逻辑不兼容 #

NETWORKDAYS.INTL和MEDIAN在数组模式下的行为和单个单元格调用时完全不同：

• NETWORKDAYS.INTL(A2:A,B2:B,1,F$7:F$9)：单个公式调用时，每一行都会使用完整的F$7:F$9假期范围判断工作日；但用ARRAYFORMULA时，它会把F$7:F$9的假期和A2:A/B2:B的日期逐行配对，相当于每一行只对应一个假期单元格，导致假期判断完全错误，工作日数计算自然不准。
• MEDIAN(MOD(B2:B,1),$F$2,$G$2)：单个公式是对该行的时间值、上班时间、下班时间取中位数；但ARRAYFORMULA会把所有行的时间值和两个固定时间放在一起算全局中位数，这直接导致时间部分的计算结果彻底偏离预期。

2. ARRAYFORMULA的条件判断逻辑失效 #

IF(NETWORKDAYS.INTL(B2:B,B2:B,1,F$7:F$9),...)这部分，NETWORKDAYS.INTL返回的是一个布尔数组，但IF在ARRAYFORMULA中不会逐行判断每个布尔值，而是把整个数组做隐式转换，导致条件判断逻辑混乱，无法正确识别当天是否为工作日，进而影响时长计算。

3. MAP+LAMBDA的逐行迭代完全匹配单个公式逻辑 #

MAP(A2:A,B2:B,LAMBDA(a,b,...))是逐行遍历每一对日期，把每个单元格的值单独传入LAMBDA函数里计算：

• 每一行都独立调用NETWORKDAYS.INTL，用完整的F7:F9假期范围；
• 每一行单独计算MEDIAN，确保是该行的局部中位数；
• 条件判断也是逐行独立执行，和单个公式的逻辑完全一致，所以结果能和C列完全匹配。

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