租金计算，现成的公式请收好

在广东生活了这许多年，最羡慕的莫过于本地人吃早茶、看报纸、讲着我听不懂的粤语、过着我暂时过不上的悠闲生活，每月唯一的工作也许就是穿着人字拖、花裤衩，拎上一串钥匙走街串巷收房租（现在也许连这项工作都省了）。

说到房租，如果房子太多，每个租户的月租多少、合同什么时候开始、什么时候结束等信息，只靠脑袋记，肯定是记不住的，这时候就需要Excel来帮忙啦~

首先我们要录入租金相关的基本信息，如图：

我们来捋捋收租的基本规则：

规则1：每月5日收本月租金，合同期在本月即收租

计算逻辑：要想判断合同期是否在本月 ，就需要告诉Excel4个日期，即：合同开始日期、合同结束日期、本月第一天、本月最后一天，有两种情况可以确定合同期不在本月，即：本月第一天大于合同结束日期 或者 本月最后一天小于合同开始日期

规则2：本月内合同期不满一个月的，按 “月租/当月全月天数*合同期内天数” 计算

计算逻辑：这里的重点是合同期内的天数如何能够自动计算，通过将上述4个日期按从小到大排列组合，我们会发现，排除合同期不在本月的两种情况后，还有4种排列方式：

这4种方式的计租期间，都是第3小的日期减第2小的日期+1，你品品，是不是这个理儿？

基本规则和计算逻辑捋差不多了，我们来看一下公式该怎么写：

涉及“本月”的日期的计算：

本月第一天：=EOMONTH(TODAY(),-1)+1

本月最后一天：=EOMONTH(TODAY(),0)

本月天数：=DAY(EOMONTH(TODAY(),0))

这里用到了EOMONTH函数，该函数有两个参数，第一个是指定的日期，第二的参数表示该日期之前/之后的月数，负数表示之前，0表示当月，正数表示之后。本月最后一天的公式好没有拓展、好理解；本月第一天的公式就是用EOMONTH函数计算到上个月的最后一天，再加1，就可以得出了。

而EOMONTH函数返回的是一个日期，我们需要计算天数则应在外面嵌套一个DAY函数。

判断本月是否在合同期内：

=IF(OR(G2>F2,H2<E2),0,'需计算天数')

计算逻辑见上文“规则1”，此处省略10000字……

合同期内天数：

=SMALL(E4:H4,3)-SMALL(E4:H4,2)+1

SMALL函数用于返回指定区域中的第 n 个最小值。需要注意的是：这里的数据区域要连续。

合同期内天数、本月天数都有了，本月租金就可以计算出来啦~

计算本月租金：

=D2/K2*J2

什么？你觉得表格加了5列辅助计算列有点丑？

那我把上面那些公式写在一起不就完啦！

公式的嵌套：

5列中有3列是可以删除的，另外2列隐藏即可：

首先，将G、H列的公式改为随B1变化，即将公式中的TODAY()改为B1，然后就可以把他们隐藏啦~

本月应收租金：

=D3/DAY(H3)*IF(OR(G3>F3,H3<E3),0,SMALL(E3:H3,3)-SMALL(E3:H3,2)+1)

看起来很复杂，其实只是把I-K列的公式写在了一起。

大家平时如果觉得嵌套公式写不来，可以像我一样，一层一层分别写出来，再嵌套进最终的公式中就完成啦~

原载：Office问题粉碎机

作者：Excel大表姐6