多条件if嵌套计算房租费用，差点蒙了！ / 四六文摘

小伙伴们好，最近一段时间在学习pq，所以没怎么更新，大家如果有问题可以加群提问。今天要分享的是一位群友的问题，他要计算公司员工当月的房租费用，但是有很多条件。具体问题看下面截图：

其中C2和D2单元格分别是每月的月初和月末日期，每过一个月都要更改。费用中有很多类别，他只要求算房租的，并把计算的各种情况给出来了，一共有3种，如紫色区域所示。每种情况又分为3种情况。我刚开始看起来觉得挺复杂的，挺蒙的，主要是没有分清这几种情况的关系。后面理清逻辑就好办了。

还是先把公式放出来，再慢慢说明。在E5单元格输入公式=IF(AND(D$2=EOMONTH(C5,0),D$2<>EOMONTH(D5,0)),IF(DAY(C5)<=10,1,IF(DAY(C5)<=20,0.5,0))*500,IF(AND(D$2=EOMONTH(D5,0),D$2<>EOMONTH(C5,0)),IF(DAY(D5)<=10,0,IF(DAY(D5)<=20,0.5,1))*500,IF(AND(D$2=EOMONTH(C5,0),D$2=EOMONTH(D5,0)),IF(D5-C5<=10,0,IF(D5-C5<=20,0.5,1))*500,"")))，向下填充。公式有点长，条件比较多。

对于第1种情况，虽然说的是“当月1-10日入职，房租*100%；11-20日入职，房租*50%；20以后入职，房租*0%”3种情况，实际还有一个前提条件——就是当月入职且不是当月离职。当满足了这个提前条件才对下面的3个条件再一一判断。这样就构成了一个if函数的嵌套用法。

那如何判断是当月入职且不是当月离职呢？我们用的是eomonth函数，它是返回某月最后一天的日期。函数写法为EOMONTH(start_date, months)，有2个参数。第一参数为起始日期，第2参数为推迟的月数，可以是正数，负数，0。如果第2参数是0，就返回本月的最后一天；如果第2参数是1（正数），就返回下个月（往后推1个月）的最后一天；如果第2参数是-1（负数），就返回上个月（往前推1个月）的最后一天。

大家可以看下图，以倒数第2个“2019/6/11”为例说明，推迟的月数是-3，就是向前推3个月，就是2019年3月，然后返回2019年3月的最后一天就是2019年3月31日。

了解了eomonth的用法，就可以继续看上面的判断了。要判断是否是当月入职，可以用=EOMONTH(C5,0)=D$2，就是判断入职日期的当月最后一天是否和D2单元格相等。如果相等就是当月入职。同时还要判断不是当月离职，可以用=EOMONTH(D5,0)<>D$2，就是判断离职日期的当月最后一天不等于D2单元格。这2个条件要同时成立，所以用=AND(D$2=EOMONTH(C5,0),D$2<>EOMONTH(D5,0))。

当这2个条件同时成立后，才判断入职日期是1-10日是什么情况，否则11-20日是什么情况，否则20日以后是什么情况。就要用到if的嵌套，=IF(AND(D$2=EOMONTH(C5,0),D$2<>EOMONTH(D5,0)),IF(DAY(C5)<=10,1,IF(DAY(C5)<=20,0.5,0))*500,第二种情况)。

但如果前提那2个条件不满足的话，也就是if函数的第1参数不成立的话，就不会进行后续更小分类的判断，这时就会转为第二种大的情况——是当月离职且不是当月入职。同样还是用eomonth判断，像第一种情况一样，我就不再详细说明了。=AND(D$2=EOMONTH(D5,0),D$2<>EOMONTH(C5,0))这部分是前提条件。=IF(AND(D$2=EOMONTH(D5,0),D$2<>EOMONTH(C5,0)),IF(DAY(D5)<=10,0,IF(DAY(D5)<=20,0.5,1))*500,第三种情况)这部分是更进一步的判断。如果第二种情况的前提条件不满足，就转为第三种大的情况。

第三种情况是当月入职且当月离职，=AND(D$2=EOMONTH(C5,0),D$2=EOMONTH(D5,0))这部分是2个前提条件，=IF(AND(D$2=EOMONTH(C5,0),D$2=EOMONTH(D5,0)),IF(D5-C5<=10,0,IF(D5-C5<=20,0.5,1))*500,"")这部分是进一步的判断。如果第3种的前提条件也不满足，最后返回空。

最后把这三种情况再嵌套在if函数里就可以了。主要是搞清楚它们之间的关系。先分为3大类，如果第一类的条件满足，就进行进一步的判断；如果第一类的条件不满足，就转为第二类，第二类的条件满足，进行进一步的判断；如果第二类的条件不满足，转为第三类，第三类的条件满足，进一步的判断，不满足，返回空。

链接：

https://pan.baidu.com/s/1UmoyKiIylB6ufof9ewztnA

提取码：0o3a

多条件if嵌套计算房租费用，差点蒙了！

相关推荐