Excel读书笔记29——贷款管理表——贷款利息计提与查询的全自
六、辅助信息的公式设计方法
虽然“贷款管理统计表”的主体工程已经竣工，但是从资金管理的角度出发，我们还有必要完善更多的信息。
比如，在未来若干天（不超过30天）内，我们将要归还多少本金利息？这就是一个做好资金管理工作必须重点关注的事项。如果你还通过筛选贷款到期日再求和这样的方式，就有点OUT了。下面，我们就来看看如何实现快捷查询未来一定期间内的应付本息。
我们要求：在贷款台账中，由用户在E3单元格录入需要查询的天数（但是不能大于30）。F3单元格显示在该期间内应付总额，G3单元格和H3单元格分别显示该期间内应付的本金和利息。
由此我们可知，F3单元格的公式为：=G3+H3
在这里需要提示的是，虽然E3单元格是由用户手工录入的，但是其录入的信息是具有限定性的，所以我们应该考虑通过数据有效性来限定其录入数值的范围。不过，如果我们仍然采用序列的方式设置30个备选项供用户选择，就比较麻烦了。所以，我们应该利用E3单元格为数字型参数的有利条件，发扬在什么山头唱什么歌的应变精神。采用数据有效性中整数配合介于设置方案来进行录入内容的控制（见图5-94）。
下面，我们来看看G3单元格和H3单元格应该如果设置公式。
图5-94 采用整数配合介于方式设置数据有效性
1.应付本金
不难看出，这仍然是一个多条件求和的问题。具体来说，就是对同时满足以下两个条件的贷款本金进行求和。
1）贷款到期日晚于今天。
2）贷款到期日不晚于今天开始的用户指定的未来天数（A3+E3）。
当然，当用户尚未在E3单元格录入需要查询的期间时，该单元格应显示为空白。
G3单元格的公式为（见图5-95）：
=IF(E3="","",SUMIFS(D7:D16,G7:G16,">"&A3,G7:G16,"<="&(A3+E3)))
图5-95 未来若干天到期的应付本金公式
2.应付利息
应付利息的思路，逻辑上与应付本金基本是一回事。但是，应付利息的计算却有点麻烦。这是因为，支付本金的时点只有一个，而支付利息的时点却有多个。而我们的现有信息，只能提供本月支付利息的时点（本月结息日或本月到期日），对于次月及以后利息的到期时间就无法捕捉了。这个时候，如果查询的期间涵盖了次月及以后，则无法完全统计应付利息的数据。
怎么办？继续补充辅助信息——增加次月应付利息的相关信息。
如果我们需要查询未来30天到期的应付利息，则我们至少需要获悉次月应付利息的时点及金额才是可靠的。而如果我们需要查询未来60天到期的应付利息，就至少需要知道次月及第二个月的应付利息的时点和金额才安全。
下面，我们以最大查询需求30天为例（即至少需要获悉次月的应付利息的时点及金额），来讨论相关公式的设计。
首先，我们在Q列至S列以及U列高仿一组应付利息的相关信息字段。具体包括：次月计息起始日（U7:U16单元格区域）、次月应付利息（Q7:Q16单元格区域）、次月结息日付息（R7:R16单元格区域）以及次月到期日付息（S7:S16单元格区域）。
其次，我们来为这四个与次月付息密切相关的信息设置公式。
由于次月的应付利息相关信息是根据本月应付利息相关信息高仿来的，所以我们的公式也可以跟着高仿。具体方式如下。
（1）次月结息日。
高仿对象：本月结息日（已定义名称，见图5-83）。
高仿逻辑：将本月结息日的月份调整为次月。
据此，我们再补充两个定义名称（见图5-96）。
图5-96 本节定义名称的参数（二）
（2）次月计息起始日。
高仿对象：本月计息起始日。
高仿逻辑：从本月计息起始日的公式中可以看出，该信息牵涉到月份信息的参数是上月结息日次日和本月结息日，所以次月计息起始日的高仿逻辑就是将本月计息起始日公式中的上月结息日次日替换为本月结息日次日，本月结息日替换为次月结息日。
U7单元格的公式为：
=IF(OR(E7="",F7="",I7=""),"",IF(G7<=EOMONTH(次月结息日,-1),"已结清", IF(I7="月度",IF(F7<=本月结息日,本月结息日次日,F7),IF(F7<=DATE(YEAR(次月结息日),MONTH(次月结息日)-IF(MOD(MONTH(次月结息日),3)=0,3, MOD(MONTH(次月结息日),3)),DAY(次月结息日)),DATE(YEAR(次月结息日), MONTH(次月结息日)-IF(MOD(MONTH(次月结息日),3)=0,3,MOD(MONTH(次月结息日),3)),21),F7))))
执行列填充后，高仿完毕（见图5-97）。
图5-97 次月计息起始日的公式
（3）次月应付利息。
高仿对象：本月应付利息。
高仿逻辑：从本月应付利息的公式中可以看出，该信息牵涉到月份信息的参数是本月结息日以及本月计息起始日。所以，次月应付利息的高仿逻辑是将本月应付利息公式中的本月结息日替换为次月结息日，本月计息起始日（T7）替换为次月计息起始日（U7）。
Q7单元格的公式为：
=IF(OR(G7="",I7="",U7="已结清"),0,IF(12*YEAR(G7)+MONTH(G7)=12*YEAR(次月结息日)+MONTH(次月结息日),ROUND(D7*H7*(G7-U7+1)/360,2),IF(AND(I7="季度",MOD(MONTH(次月结息日),3)< >0),0,ROUND(D7*H7*(次月结息日-U7+1)/360,2))))
执行列填充后，高仿完毕（见图5-98）。
图5-98 次月应付利息的公式
（4）次月结息日付息。
高仿对象：本月结息日付息。
高仿逻辑：从本月结息日付息的公式中可以看出，该信息牵涉到月份信息的参数是本月结息日、本月计息起始日以及本月应付利息。所以，次月结息日付息的高仿逻辑是将原版公式中的本月结息日替换为次月结息日，本月计息起始日（T7）替换为次月计息起始日（U7），本月应付利息（K7）替换为次月结息日付息（Q7）。
R7单元格的公式为：
=IF(OR(Q7=0,G7<次月结息日,AND(I7="季度",MOD(MONTH(次月结息日),3)< >0)),0,IF(G7>EOMONTH(次月结息日,0),Q7,ROUND(D7*H7*(次月结息日-U7+1)/360,2)))
执行列填充后，高仿完毕（见图5-99）。
图5-99 次月结息日付息的公式
（5）次月到期日付息的公式依然倒算。
S7单元格的公式为：=Q7-R7
执行列填充后，高仿完毕，这个就不上图啦。
最后，我们来看看未来若干天内，应付利息的公式如何设置。根据贷款台账可以看出，涉及应付利息统计的线索如图5-100所示。
图5-100 应付利息的相关线索
我们来逐个进行分析：
第一步：本月结息日付息。
由于本月结息日是固定的，所以，本月结息日的付息金额要么全部属于查询范围要么都不属于查询范围。所以，面对这个二选一的问题的时候，我们就可以用IF函数解决。
H3单元格的第一步公式为：
=IF(AND(A3+E3>=本月结息日次日,A3<本月结息日次日),SUM(L7:L16),0)
第二步：本月到期日付息。
由于贷款到期日是由每笔贷款的贷款起始日及贷款期限确定的，具有高度的独立性，所以这个时候就属于多条件求和的问题了。
H3单元格的第二步公式为：
=SUMIFS(M7:M16,G7:G16,">"&A3,G7:G16,"<="&A3+E3)
第三步：次月结息日付息。此处参照第一步原理。
H3单元格的第三步公式为：
=IF(AND(A3+E3>=次月结息日次日,A3<次月结息日次日),SUM(R7:R16),0)
第四步：次月到期日付息。
此处参照第二步原理。
H3单元格的第四步公式为：
=SUMIFS(S7:S16,G7:G16,">"&A3,G7:G16,"<="&A3+E3)
将上述四个步骤的公式相加。
H3单元格的完整公式为：
=IF(AND(A3+E3>=本月结息日次日,A3<本月结息日次日),SUM(L7:L16), 0)+SUMIFS(M7:M16,G7:G16,">"&A3,G7:G16,"<="&A3+E3)+IF(AND(A3+E3>=次月结息日次日,A3<次月结息日次日),SUM(R7:R16),0)+SUMIFS(S7:S16,G7:G 16,">"&A3,G7:G16,"<="&A3+E3)
最后，我们让F2单元格的名称能自动匹配查询天数。
F2单元格的公式为= E3&"天内应付总额"
至此，查询未来若干天（最大为30天）应付本息的功能实现（见图5-101）。
图5-101 未来若干天（最大为30天）应付本息的查询
3.到期提示的明细查询
前面我们刚刚实现了查询未来若干天（最大为30天）应付本息的功能。但是，当你兴匆匆地把这个快捷生成的数据报告给领导时。领导却刨根问底要看明细，你该怎么办？
所以，最好我们还是准备一个明细提示功能。具体要求是：如果某笔贷款有幸入围查询期间到期名单，则我们就在其对应的提示标识区域显示“Yes”，这样，我们只需要筛选出标识区域的“Yes”，就能向领导交差了。
（1）本金到期提示（O7:O16单元格区域）。
某笔贷款的本金是否需要提示，其实就是看贷款到期日是否介于“今天”至未来用户指定天数的期限内。
O7格的公式为：=IF(AND(G7>$A$3,G7<=$A$3+$E$3),"Yes","")
执行列填充后，即可完成本金的到期提示标识（见图5-102）。
图5-102 本金到期提示标识的公式
（2）利息到期提示（P7:P16单元格区域）。
某笔贷款的利息是否被查询提示，只要满足下列条件之一，就可以金榜题名了。
1）当本金到期提示被标识为“Yes”时。因为偿还本金的同时必然支付最后一期利息（除非到期日恰好是结息日，但是此种情况下予以提示也不算错误）。所以，本金成了最后一期利息的标杆，我们要向标杆看齐。
2）当本月结息日付息时，考虑本月结息日是否在查询期间内。这就需要同时满足两个条件：本月结息日大于0，本月结息日在查询范围内。
3）当次月结息日付息时，考虑次月结息日是否在查询期间内。这也需要同时满足两个条件：次月结息日大于0，次月结息日在查询范围内。
将上述分析整理成公式。
P7单元格的公式为：
=IF(OR(O7="Yes",AND(L7>0,本月结息日>$A$3,本月结息日<=$A$3+$E$3),
AND(R7>0,次月结息日<=$A$3+$E$3)),"Yes","")
执行列填充后，即可完成利息的到期提示标识（见图5-103）。
图5-103 本金到期提示标识的公式
不过，关于利息的到期提示标识，本案例中采用的办法还存在一定的缺陷。主要表现在利息分布的批次较多时，即使进行了标识，也只能确定是哪笔贷款的利息被提示标识，而不能直接确定具体是该笔贷款下的哪个批次的应付利息被提示标识。
（3）本息到期提示（N7:N16单元格区域）。
这个问题就比较简单了，只要其本金提示标识和利息提示标识同时显示“Yes”，我们就可以跟着“Yes”了。
N7单元格的公式为：=IF(AND(O7="YES",P7="YES"),"Yes","")
执行列填充后，即可完成本息的到期提示标识（见图5-104）。
图5-104 本息到期提示标识的公式
七、逻辑校验信息的公式设计方法
有了本章前三节内容的铺垫，相信大家对逻辑校验有了一定的意识。所以本节我们就只做一个简单的介绍。
针对“贷款管理统计表”来说，我们可以考虑以下逻辑的校验。
1.贷款到期日与授信到期日的逻辑校验
根据授信的规则，每笔授信合约下的贷款到期日均不得超过授信到期日。所以针对这个逻辑，我们仍然可以用IF函数进行两个到期日的合理性判断。
2.同一授信合约下当前已用额度与授信总额度的逻辑校验
这个道理也很简单，如果当前的已用额度都超过授信总额度了，不要以为你占便宜了。因为那肯定是你自己把数据弄错了。其实我们的授信台账已经实现了这个功能，只要可用额度为负数，那么，赶快去检查吧。
