1 设计背景

原始需求：一张表单，近100个条目，已知金额、计息起止时间，求各条目在该时间段内按人行同期和LPR利率计算的利息。 之前做过《引用人民银行同期贷款利率（基准利率）和LPR利率的利息计算器》，多个表单协同作用，可实现一项借贷的利息计算。故原理上讲，这个需求是可以实现的。不同的地方在于，这次需要同时计算近100项，不可能再用多个表单交互引用，需要更加简洁有效的方法。 难点分析：

利率引用——对人行同期和LPR，一次调整期为一行，计息时间段不同，引用的利息的调整期的开始的单元格和行数也不同；计息起止时间——借贷起止时间跟利息调整时间几乎双随机，计息时需要在大的利息调整周期内“掐头去尾”，或者在小的调整周期基础上加上首尾“零头”；利息计算——按常规思路，需计算每一个调整期内的时间（不规律）、引用当期的利率，得到每一期的利息，再求和，得该条目的总利息；利息计算通用公式——需要同时实现多项借贷的利息计算，相当于同一个模型，输入多个参数，输出多个参数。

2 解决方案

利率引用——参考《引用人民银行同期贷款利率（基准利率）和LPR利率的利息计算器》，利用index+match函数查找匹配的起始时间，利用if函数判断引用的档位（ifs函数更简洁，但担心有的office版本识别不了，还是用的if多重嵌套）；计息起止时间——采用在大的利息调整周期内“掐头去尾”（其实两种都可，差别不大）；利息计算——采用先计算综合利率（我自己引入的概念，不知道有没有更准确的叫法），即计算在该时间段里加权平均的利率，好处是在计算利息时可以直接用金额、利率（即这里的综合利率）、时长三者相乘，把主要矛盾全部浓缩到综合利率里面了；利息通用公式的设计——同上，主要问题转换成了综合利率的公式设计。

3 实现步骤

3.1 制作利率表

这里把人行同期和LPR放在了同一个表内，方便后续引用。因为人行同期分五档，LPR分两档，为了不用单独处理，此处把LPR还是按五档输入。

注：前四挡LPR利率相同，可选中单元格后，在公式栏输入数字，按ctrl+enter，可实现快速输入。

3.2 制作利息计算表

建立输入金额、计息起止时间单元格，作为待输入的变量；设置天数单元格，计算计息天数——公式：截止时间-起始时间+1（注意需+1，易遗漏）；设置跨越调整期数单元格，方便后面计算综合利率时引用——利用match函数，将起止时间与利率表中的生效时间比较，两者相减再+1；设置利率档位单元格——通过起止时间，判断，此处因截止时间都是2019/12/4，偷了个小懒，做了个查询表，直接划分出时间节点，利用index+match判断利率档位； 设置综合利率单元格——主要矛盾，后面详讲；设置利息单元格——金额×天数×综合利息，数学问题。

（更正：金额×天数×综合利息/360，因综合利息计算的是年利息，此处按天数算，需要换算为日利息。截图仍为旧版，未更新。2020/9/18注。）

3.3 计算综合利率

根据前面的分析，综合利率即在该时间段里加权平均的利率，以第一行数据为例，分析如下：

开始时间为2012/1/1，查找利率表，应引用生效时间-起为2011/7/7（R35）的利率，根据档位，得7.05%；截止时间为2019/12/4，查找利率表，应引用生效时间-止为2019/12/19（R47）的利率，根据档位，得4.08%；先计算大范围、即2011/7/7-2019/12/19期间的各期利率×各期天数，再减去2011/7/7-2011/12/31、2019/12/5-2019/12/19收尾两段的利率×天数，最后除以总天数，即得该时间段内的综合利率（数学问题，可以公式稍稍推导，帮助理解）；各期利率×各期天数的结果输出在同一个单元格，想到用数组公式，最为简单高效，也是本次工作的亮点所在（个人感受，数组公式需要一定的基础才能理解，在二维的单元格里展开了三维的世界，神奇&强大&有趣）。 具体公式展示如下：

4 总结感想

因为之前做了《引用人民银行同期贷款利率（基准利率）和LPR利率的利息计算器》，以为升级一下很简单。后来发现，因为对数组的认识还不够深，还是走了不少弯路。 但是最后做成了，还是很开心的。 因为【Excel】多元一次方程组求解计算器的经验，一开始是想到了综合利率的公式用offset。后来尝试过程，又主要放在了index+match，走了不少弯路。放一张截图，作个纪念。 这次之后，感觉自己能独立应用数组公式了。之前只是知道有这么回事，或者跟着别人的思路依葫芦画瓢。现在可以宣布，我也会用数组啦（当然可能只是管中窥豹）。

【原创内容，引用请注明出处：https://blog.csdn.net/truly121314/article/details/108391302】