本帖最后由 @wsm 于 2012-12-20 08:50 编辑
第一题:=IF(AND(C12>=60,D12>=60,E12>=60),"是","否")
第二题:=IF(AND(C28="男",D28<61),60-D28,IF(AND(C28="女",D28<56),55-D28,0))
第三题1:={INDIRECT("C"&SUM(((F43>=$A$43:$A$49)*(F43<$B$43:$B$49)>0)*ROW($C$43:$C$49)))}
第三题2:=IF(F43>=$A$46,IF(AND(F43>=$A$46,F43<$B$46),$C$46,IF(AND(F43<$B$47,F43>=$A$47),$C$47,IF(AND(F43<$B$48,F43>=$A$48),$C$48,$C$49))),IF(AND(F43>=$A$45,F43<$B$45),$C$45,IF(AND(F43<$B$44,F43>=$A$44),$C$44,$C$43)))
第四题:=IF(MOD(A58,2)=0,"",B58*C58),=SUM(D58:D69)
数组 { =SUM((MOD(A58:A69,2)<>0)*B58:B69*C58:C69)}
数组 { =SUM(IF(MOD(A58:A69,2)<>0,B58:B69*C58:C69,0))}
第三题3:=IF(F43<$B$43,$C$43,IF(F43<$B$44,$C$44,IF(F43<$B$45,$C$45,IF(F43<$B$46,$C$46,IF(F43<$B$47,$C$47,IF(F43<$B$48,$C$48,$C$49))))))其实老师的思路远不止这几种的吧
|