Question

在Excel中如何设置分条件及分段计算的公式

如图：如果E3单元格选择了“散居五保”或者“城镇三无”，且K3-L3<=3000，那么M3单元格就等于K3-L3；
如果E3单元格选择了“农村低保”或者“城镇低保”，若K3-L3<=1000，那么M3就要等于（K3-L3）*0.5，若K3-L3>1000,K3-L3<=2000，那么M3就要等于500+（K3-L3-1000）*0.3，若K3-L3>2000，那么M3就要等于800+（K3-L3-2000）*0.2.
这种分条件后又分段计算的公式应该怎么设置呢？
可能我没把问题表达清楚或者大家理解错了，再更正一下题：
如图：如果E3单元格选择了“散居五保”或者“城镇三无”，那么M3单元格就等于K3-L3，但M3<=3000；

如果E3单元格选择了“农村低保”或者“城镇低保”，若K3-L3<=1000，那么M3就等于（K3-L3）*0.5；若K3-L3>1000,K3-L3<=2000，那么M3就要等于500+（K3-L3-1000）*0.3；若K3-L3>2000，那么M3就要等于800+（K3-L3-2000）*0.2，但M3<=1500.
这种分条件后又分段计算且限制最大值的公式应该怎么设置呢？

Answer 1

=IF(AND(OR(E3="散居五保",E3="城镇三无"),K3-L3<=3000),MIN(K3-L3,3000),IF(AND(OR(E3="农村低保",E3="城镇低保"),(K3-L3)<=1000),(K3-L3)*0.5,IF(AND(OR(E3="农村低保",E3="城镇低保"),(K3-L3)<=2000),500+(K3-L3-1000)*0.3,IF(AND(OR(E3="农村低保",E3="城镇低保"),(K3-L3)>2000),MIN(800+(K3-L3-2000)*0.2,1500),""))))

追问

补充的内容是如果E3="散居五保","城镇三无，M3最大就只能是3000；如果E3是城镇低保或农村低保，M3最大只能是1500。这个怎么搞啊？

追答

=IF(AND(OR(E3="散居五保",E3="城镇三无"),K3-L32000),MIN(800+(K3-L3-2000)*0.2,1500),""))))

追问

试着检验了哈，错了。在麻烦哈你，检查哈

追答

别的不会错，就是当E3="散居五保",E3="城镇三无",且K3-L3>3000,会显示空，这 个怎么样你没说。

Answer 2

亲，虽然可以解决，但是你的表述是矛盾的啊，（如果E3单元格选择了“散居五保”或者“城镇三无”，且K3-L3<=3000，那么M3单元格就等于K3-L3，但要小于3000；）这话如何解释？

追问

就是说：如果E3单元格选择了“散居五保”或者“城镇三无”，那么M3单元格就等于K3-L3，但要小于3000，也就是说M3最大只能为3000。请问怎么设置公式？

追答

=IF(OR(E3={"散居五保","城镇三无"}),IF(K3-L3<=3000,K3-L3,"大于3000"),IF(K3-L3<=1000,(K3-L3)*0.5,IF(K3-L3<=2000,500+(K3-L3-1000)*0.3,MIN(800+(K3-L3-2000)*0.2,1500))))

这个思路比较清晰

Answer 3

=IF(AND(OR(E3={"散居五保","城镇三无"}),K3-L3<=3000),K3-L3,IF(K3-L3<=1000,(K3-L3)*0.5,IF(K3-L3<=2000,500+(K3-L3-1000)*0.3,800+(K3-L3-2000)*0.2)))

追问

麻烦你看哈补充，要限制M3最大值怎么搞？

追答

=MIN(IF(AND(OR(E3={"散居五保","城镇三无"}),K3-L3<=3000),K3-L3,IF(K3-L3<=1000,(K3-L3)*0.5,IF(K3-L3<=2000,500+(K3-L3-1000)*0.3,800+(K3-L3-2000)*0.2))),3000)

追问

补充的内容是这个意思：如果E3="散居五保","城镇三无，M3最大就只能是3000；如果E3是城镇低保或农村低保，M3最大只能是1500.

追答

=IF(OR(E3={"散居五保","城镇三无"}),MIN(K3-L3,3000),MIN(IF(K3-L3<=1000,(K3-L3)*0.5,IF(K3-L3<=2000,500+(K3-L3-1000)*0.3,800+(K3-L3-2000)*0.2)),1500))