在 Excel 2007 中,可以用以下公式满足题述要求:
1. 非数组公式:=IF(B2>110,IF(D2<=9%,"不置休",IF(AND(D2>9%,D2<=18%),"2小时",IF(AND(D2>18%,D2<=30%),"4小时",IF(AND(D2>30%,D2<=100%),"6小时","")))),IF(C2<=7,"不置休",IF(AND(C2>7,C2<=12),"2小时",IF(AND(C2>12,C2<=17),"4小时",IF(AND(C2>17,C2<=110),"6小时","")))))
2. 数组公式:=IF(B2>110,LOOKUP(D2,({0,9.01,18.01,30.01})%,{0,2,4,6}),LOOKUP(C2,{0,7.01,12.01,17.01},{0,2,4,6}))&"小时"
注:数组公式虽然简单,但不能完全满足题述的要求,“不置休”显示为“0小时”
公式的结果如下图示: