...求如果每次迟到1-10分钟扣10元,迟到10-20分钟扣30元,迟到20-30分...

发布网友发布时间：2024-09-26 20:14

共2个回答

热心网友时间：2024-10-05 20:17

假设第一个迟到时间从B2开始，参考下面公式，并下拉
=SUM(IF(ISNUMBER(B2),IF(B2<=10,10,IF(B2<=20,20,IF(B2<=30,$A$1/$B$1/2,$A$1/$B$1)))),(IF(ISNUMBER(C2),IF(C2<=10,10,IF(C2<=20,20,IF(C2<=30,$A$1/$B$1/2,$A$1/$B$1))))),(IF(ISNUMBER(D2),IF(D2<=10,10,IF(D2<=20,20,IF(D2<=30,$A$1/$B$1/2,$A$1/$B$1))))),(IF(ISNUMBER(E2),IF(E2<=10,10,IF(E2<=20,20,IF(E2<=30,$A$1/$B$1/2,$A$1/$B$1))))),(IF(ISNUMBER(F2),IF(F2<=10,10,IF(F2<=20,20,IF(F2<=30,$A$1/$B$1/2,$A$1/$B$1))))),(IF(ISNUMBER(G2),IF(G2<=10,10,IF(G2<=20,20,IF(G2<=30,$A$1/$B$1/2,$A$1/$B$1))))),IF(ISNUMBER(H2),IF(H2<=10,10,IF(H2<=20,20,IF(H2<=30,$A$1/$B$1/2,$A$1/$B$1)))),IF(ISNUMBER(I2),IF(I2<=10,10,IF(I2<=20,20,IF(I2<=30,$A$1/$B$1/2,$A$1/$B$1)))))
如果能解决你的问题，看在公式这么长的份上，请采纳，谢谢！

热心网友时间：2024-10-05 20:14

直接在迟到扣款下输入
=SUM(IF(A2:H2<=0,0,IF(A2:H2<=10,10,IF(A2:H2<=20,30,IF(A2:H2<=30,2000/20/2,2000/20)))))
然后同时按ctrl+shift+enter。
A2:H2代表第一行的数据区间。