以B2D2为例，夜班时间等于，三个小方块之和减去3.5。那么夜班时间这个公式该怎么写？合计天数为:

Question

以B2D2为例，夜班时间等于，三个小方块之和减去3.5。那么夜班时间这个公式该怎么写？合计天数为:只要每天的三个小方框内，有数字出现即为一天，公式该怎么写？？？

Answer 1

请在BB4输入公式：
=IF(SUM(B4:D4)-3.5<0,0,SUM(B4:D4)-3.5)
+IF(SUM(E4:G4)-3.5<0,0,SUM(E4:G4)-3.5)
+IF(SUM(H4:J4)-3.5<0,0,SUM(H4:J4)-3.5)
+IF(SUM(K4:M4)-3.5<0,0,SUM(K4:M4)-3.5)
+IF(SUM(N4:P4)-3.5<0,0,SUM(N4:P4)-3.5)
+IF(SUM(Q4:S4)-3.5<0,0,SUM(Q4:S4)-3.5)
+IF(SUM(T4:V4)-3.5<0,0,SUM(T4:V4)-3.5)
+IF(SUM(W4:Y4)-3.5<0,0,SUM(W4:Y4)-3.5)
+IF(SUM(Z4:AB4)-3.5<0,0,SUM(Z4:AB4)-3.5)
+IF(SUM(AC4:AE4)-3.5<0,0,SUM(AC4:AE4)-3.5)
+IF(SUM(AF4:AH4)-3.5<0,0,SUM(AF4:AH4)-3.5)
+IF(SUM(AI4:AK4)-3.5<0,0,SUM(AI4:AK4)-3.5)
+IF(SUM(AL4:AN4)-3.5<0,0,SUM(AL4:AN4)-3.5)
+IF(SUM(AO4:AQ4)-3.5<0,0,SUM(AO4:AQ4)-3.5)
+IF(SUM(AR4:AT4)-3.5<0,0,SUM(AR4:AT4)-3.5)
+IF(SUM(AU4:AW4)-3.5<0,0,SUM(AU4:AW4)-3.5)

请在AZ4中输入公式：
=IF(SUM(B4:D4)>0,1,0
+IF(SUM(E4:G4)>0,1,0
+IF(SUM(H4:J4)>0,1,0
+IF(SUM(K4:M4)>0,1,0
+IF(SUM(N4:P4)>0,1,0
+IF(SUM(Q4:S4)>0,1,0
+IF(SUM(T4:V4)>0,1,0
+IF(SUM(W4:Y4)>0,1,0
+IF(SUM(Z4:AB4)>0,1,0
+IF(SUM(AC4:AE4)>0,1,0
+IF(SUM(AF4:AH4)>0,1,0
+IF(SUM(AI4:AK4)>0,1,0
+IF(SUM(AL4:AN4)>0,1,0
+IF(SUM(AO4:AQ4)>0,1,0
+IF(SUM(AR4:AT4)>0,1,0
+IF(SUM(AU4:AW4)>0,1,0
并下拉填充复制即可。追问

你好，测试了下，不对啊…

追答

我为了你方便看不要密密的，看懂了就行，所以在每个处理间加了换行，你在BB4直接粘贴可用下公式：

=IF(SUM(B4:D4)-3.5<0,0,SUM(B4:D4)-3.5)+IF(SUM(E4:G4)-3.5<0,0,SUM(E4:G4)-3.5)+IF(SUM(H4:J4)-3.5<0,0,SUM(H4:J4)-3.5)+IF(SUM(K4:M4)-3.5<0,0,SUM(K4:M4)-3.5)+IF(SUM(N4:P4)-3.5<0,0,SUM(N4:P4)-3.5)+IF(SUM(Q4:S4)-3.5<0,0,SUM(Q4:S4)-3.5)+IF(SUM(T4:V4)-3.5<0,0,SUM(T4:V4)-3.5)+IF(SUM(W4:Y4)-3.5<0,0,SUM(W4:Y4)-3.5)+IF(SUM(Z4:AB4)-3.5<0,0,SUM(Z4:AB4)-3.5)+IF(SUM(AC4:AE4)-3.5<0,0,SUM(AC4:AE4)-3.5)+IF(SUM(AF4:AH4)-3.5<0,0,SUM(AF4:AH4)-3.5)+IF(SUM(AI4:AK4)-3.5<0,0,SUM(AI4:AK4)-3.5)+IF(SUM(AL4:AN4)-3.5<0,0,SUM(AL4:AN4)-3.5)+IF(SUM(AO4:AQ4)-3.5<0,0,SUM(AO4:AQ4)-3.5)+IF(SUM(AR4:AT4)-3.5<0,0,SUM(AR4:AT4)-3.5)+IF(SUM(AU4:AW4)-3.5<0,0,SUM(AU4:AW4)-3.5)

下拉。

AZ4公式在附件中，这里字数超了。

Answer 2

公式会很长
=IF(COUNTA(B2:D2)>0,SUM(B2:D2)-3.5,0)+IF(COUNTA(E2:G2)>0,SUM(E2:G2)-3.5,0)+IF(COUNTA(H2:J2)>0,SUM(H2:J2)-3.5,0)+IF(COUNTA(K2:M2)>0,SUM(K2:M2)-3.5,0)+IF(COUNTA(N2:P2)>0,SUM(N2:P2)-3.5,0)+IF(COUNTA(Q2:S2)>0,SUM(Q2:S2)-3.5,0)+IF(COUNTA(T2:V2)>0,SUM(T2:V2)-3.5,0)+IF(COUNTA(W2:Y2)>0,SUM(W2:Y2)-3.5,0)+IF(COUNTA(Z2:AB2)>0,SUM(Z2:AB2)-3.5,0)+IF(COUNTA(AC2:AE2)>0,SUM(AC2:AE2)-3.5,0)+IF(COUNTA(AF2:AH2)>0,SUM(AF2:AH2)-3.5,0)+IF(COUNTA(AI2:AK2)>0,SUM(AI2:AK2)-3.5,0)+IF(COUNTA(AL2:AN2)>0,SUM(AL2:AN2)-3.5,0)+IF(COUNTA(AO2:AQ2)>0,SUM(AO2:AQ2)-3.5,0)+IF(COUNTA(AR2:AT2)>0,SUM(AR2:AT2)-3.5,0)+IF(COUNTA(AU2:AW2)>0,SUM(AU2:AW2)-3.5,0)

或者
=SUM(B2:AW2)-3.5*(IF(COUNTA(B2:D2)>0,1,0)+IF(COUNTA(E2:G2)>0,1,0)+IF(COUNTA(H2:J2)>0,1,0)+IF(COUNTA(K2:M2)>0,1,0)+IF(COUNTA(N2:P2)>0,1,0)+IF(COUNTA(Q2:S2)>0,1,0)+IF(COUNTA(T2:V2)>0,1,0)+IF(COUNTA(W2:Y2)>0,1,0)+IF(COUNTA(Z2:AB2)>0,1,0)+IF(COUNTA(AC2:AE2)>0,1,0)+IF(COUNTA(AF2:AH2)>0,1,0)+IF(COUNTA(AI2:AK2)>0,1,0)+IF(COUNTA(AL2:AN2)>0,1,0)+IF(COUNTA(AO2:AQ2)>0,1,0)+IF(COUNTA(AR2:AT2)>0,1,0)+IF(COUNTA(AU2:AW2)>0,1,0))追问

这个公式对应的是哪个

追答

原先的理解有误
以上两个公式都是BB2的公式
如果是要在BB4开始的话
要将公式里的数字2 改成4

Answer 3

你可以用sum(b2:d2)-3.5
和count（b2：d2）追问

不对

追答

你要的不是B2到D2的累计吗，说明白点帮忙改下，早上手机写的

追问

合计天数的公式:当天有上班就计一天，半天也算一天

夜班小时:当天时间减去3.5就是夜班小时

Answer 4

会不会有三个加起不足3.5的情况追问

几乎不会

追答

如果不存在不足的情况，总时间为：
=SUM(B4:AW4)-SUMPRODUCT(--(COUNTBLANK(OFFSET(A4,,ROW($A$1:$A$16)*3-2,,3))<3))*3.5
总天数为：
=SUMPRODUCT(--(COUNTBLANK(OFFSET(A4,,ROW($A$1:$A$16)*3-2,,3))<3))
将上面的公式粘贴到表格，下拉即可

Answer 5

你在说明白点，我还是不太懂