第1个回答 2013-09-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代表第一行的数据区间。
追问如果公式里不用2000/20,而是用那一列的列数,A1/B1,这样公式下拉时,到第二个人的时候,可以变成A2/B2?
追答可以的
追问不行的哦,什么弄呢,=SUM(IF(A2:H2<=0,0,IF(A2:H2<=10,10,IF(A2:H2<=20,30,IF(A2:H2<=30,A1/B1/2,A1/B1)))))
这样公式是错的,加$这个符号,就一直是A1/B1,不会变成A2/B2!
第2个回答 2013-09-14
假设第一个迟到时间从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)))))
如果能解决你的问题,看在公式这么长的份上,请采纳,谢谢!本回答被提问者采纳