17楼 贵港真小子 | 发表于2019-02-21 22:44:49
=IF((IF(B2<10,B2,IF(AND(B2>=10,B2<20),(1+MOD(B2,10)),IF(AND
(B2>=20,B2<30),(2+MOD(B2,10)),IF(B2>=30,(3+MOD
(B2,10)))))))+(IF(C2<10,C2,IF(AND(C2>=10,C2<20),(1+MOD
(C2,10)),IF(AND(C2>=20,C2<30),(2+MOD(C2,10)),IF(C2>=30,
(3+MOD(C2,10)))))))+(IF(D2<10,D2,IF(AND(D2>=10,D2<20),(1+MOD
(D2,10)),IF(AND(D2>=20,D2<30),(2+MOD(D2,10)),IF(D2>=30,
(3+MOD(D2,10)))))))+(IF(E2<10,E2,IF(AND(E2>=10,E2<20),(1+MOD
(E2,10)),IF(AND(E2>=20,E2<30),(2+MOD(E2,10)),IF(E2>=30,
(3+MOD(E2,10)))))))+(IF(F2<10,F2,IF(AND(F2>=10,F2<20),(1+MOD
(F2,10)),IF(AND(F2>=20,F2<30),(2+MOD(F2,10)),IF(F2>=30,
(3+MOD(F2,10)))))))+(IF(G2<10,G2,IF(AND(G2>=10,G2<20),(1+MOD
(G2,10)),IF(AND(G2>=20,G2<30),(2+MOD(G2,10)),IF(G2>=30,
(3+MOD(G2,10)))))))<=33,(IF(B2<10,B2,IF(AND(B2>=10,B2<20),
(1+MOD(B2,10)),IF(AND(B2>=20,B2<30),(2+MOD(B2,10)),IF
(B2>=30,(3+MOD(B2,10)))))))+(IF(C2<10,C2,IF(AND
(C2>=10,C2<20),(1+MOD(C2,10)),IF(AND(C2>=20,C2<30),(2+MOD
(C2,10)),IF(C2>=30,(3+MOD(C2,10)))))))+(IF(D2<10,D2,IF(AND
(D2>=10,D2<20),(1+MOD(D2,10)),IF(AND(D2>=20,D2<30),(2+MOD
(D2,10)),IF(D2>=30,(3+MOD(D2,10)))))))+(IF(E2<10,E2,IF(AND
(E2>=10,E2<20),(1+MOD(E2,10)),IF(AND(E2>=20,E2<30),(2+MOD
(E2,10)),IF(E2>=30,(3+MOD(E2,10)))))))+(IF(F2<10,F2,IF(AND
(F2>=10,F2<20),(1+MOD(F2,10)),IF(AND(F2>=20,F2<30),(2+MOD
(F2,10)),IF(F2>=30,(3+MOD(F2,10)))))))+(IF(G2<10,G2,IF(AND
(G2>=10,G2<20),(1+MOD(G2,10)),IF(AND(G2>=20,G2<30),(2+MOD
(G2,10)),IF(G2>=30,(3+MOD(G2,10))))))),IF((IF(B2<10,B2,IF
(AND(B2>=10,B2<20),(1+MOD(B2,10)),IF(AND(B2>=20,B2<30),
(2+MOD(B2,10)),IF(B2>=30,(3+MOD(B2,10)))))))+(IF(C2<10,C2,IF
(AND(C2>=10,C2<20),(1+MOD(C2,10)),IF(AND(C2>=20,C2<30),
(2+MOD(C2,10)),IF(C2>=30,(3+MOD(C2,10)))))))+(IF(D2<10,D2,IF
(AND(D2>=10,D2<20),(1+MOD(D2,10)),IF(AND(D2>=20,D2<30),
(2+MOD(D2,10)),IF(D2>=30,(3+MOD(D2,10)))))))+(IF(E2<10,E2,IF
(AND(E2>=10,E2<20),(1+MOD(E2,10)),IF(AND(E2>=20,E2<30),
(2+MOD(E2,10)),IF(E2>=30,(3+MOD(E2,10)))))))+(IF(F2<10,F2,IF
(AND(F2>=10,F2<20),(1+MOD(F2,10)),IF(AND(F2>=20,F2<30),
(2+MOD(F2,10)),IF(F2>=30,(3+MOD(F2,10)))))))+(IF(G2<10,G2,IF
(AND(G2>=10,G2<20),(1+MOD(G2,10)),IF(AND(G2>=20,G2<30),
(2+MOD(G2,10)),IF(G2>=30,(3+MOD(G2,10)))))))>33,(IF
(B2<10,B2,IF(AND(B2>=10,B2<20),(1+MOD(B2,10)),IF(AND
(B2>=20,B2<30),(2+MOD(B2,10)),IF(B2>=30,(3+MOD
(B2,10)))))))+(IF(C2<10,C2,IF(AND(C2>=10,C2<20),(1+MOD
(C2,10)),IF(AND(C2>=20,C2<30),(2+MOD(C2,10)),IF(C2>=30,
(3+MOD(C2,10)))))))+(IF(D2<10,D2,IF(AND(D2>=10,D2<20),(1+MOD
(D2,10)),IF(AND(D2>=20,D2<30),(2+MOD(D2,10)),IF(D2>=30,
(3+MOD(D2,10)))))))+(IF(E2<10,E2,IF(AND(E2>=10,E2<20),(1+MOD
(E2,10)),IF(AND(E2>=20,E2<30),(2+MOD(E2,10)),IF(E2>=30,
(3+MOD(E2,10)))))))+(IF(F2<10,F2,IF(AND(F2>=10,F2<20),(1+MOD
(F2,10)),IF(AND(F2>=20,F2<30),(2+MOD(F2,10)),IF(F2>=30,
(3+MOD(F2,10)))))))+(IF(G2<10,G2,IF(AND(G2>=10,G2<20),(1+MOD
(G2,10)),IF(AND(G2>=20,G2<30),(2+MOD(G2,10)),IF(G2>=30,
(3+MOD(G2,10)))))))-33))
19楼 贵港真小子 | 发表于2019-02-22 09:36:43
=IF(MOD(B2,10)+((B2-(MOD(B2,10)))/10)+MOD(C2,10)+((C2-(MOD
(C2,10)))/10)+MOD(D2,10)+((D2-(MOD(D2,10)))/10)+MOD
(E2,10)+((E2-(MOD(E2,10)))/10)+MOD(F2,10)+((F2-(MOD
(F2,10)))/10)+MOD(G2,10)+((G2-(MOD(G2,10)))/10)<=33,MOD
(B2,10)+((B2-(MOD(B2,10)))/10)+MOD(C2,10)+((C2-(MOD
(C2,10)))/10)+MOD(D2,10)+((D2-(MOD(D2,10)))/10)+MOD
(E2,10)+((E2-(MOD(E2,10)))/10)+MOD(F2,10)+((F2-(MOD
(F2,10)))/10)+MOD(G2,10)+((G2-(MOD(G2,10)))/10),IF(MOD
(B2,10)+((B2-(MOD(B2,10)))/10)+MOD(C2,10)+((C2-(MOD
(C2,10)))/10)+MOD(D2,10)+((D2-(MOD(D2,10)))/10)+MOD
(E2,10)+((E2-(MOD(E2,10)))/10)+MOD(F2,10)+((F2-(MOD
(F2,10)))/10)+MOD(G2,10)+((G2-(MOD(G2,10)))/10)>33,MOD
(B2,10)+((B2-(MOD(B2,10)))/10)+MOD(C2,10)+((C2-(MOD
(C2,10)))/10)+MOD(D2,10)+((D2-(MOD(D2,10)))/10)+MOD
(E2,10)+((E2-(MOD(E2,10)))/10)+MOD(F2,10)+((F2-(MOD
(F2,10)))/10)+MOD(G2,10)+((G2-(MOD(G2,10)))/10)-33))