综合素质评价范文【Excel】引⽤⼈民银⾏同期贷款利率（基准利率）和LPR利率的利息计算器
需求产⽣
因为LPR出台，做了LPR房贷计算器。
偶然接触到需要计算民间借贷利息的朋友，做了LPR利息计算器，仅⽀持LPR公布（2019/8/20）后的⽇期的利率计算，认为版1（2020/5/9）。
添加了2019/8/20之前的利息计算功能，认为版2（2020/7/8）。
最近，有朋友提出同时计算⼈民银⾏同期贷款利率和LPR利率下的利息的需求。前后⼤概花了3个⼩时（怪⾃⼰对match函数不够熟悉，不然应该更快），成了现在的版3（2020/8/4）。
思路分析
zhongzhuangjibing计息计算，关键参数：借贷⾦额，借贷起⽌时间，利率。
⾦额、起⽌时间，定值，输⼊就好。
利率，三个问题：
终于被你推到心碎的边缘什么歌
1. 根据借贷时间，利率档位不同，⼈⾏同期分为五档，LPR分为两档；
2. 不同的借贷起⽌时间，引⽤的利率类别不同，LPR公布以前引⽤⼈⾏同期利率，之后引⽤LPR利率。
3. 利率不定期更新，存在利率-时间变量。
解决⽅案：
1. 建⽴⼈⾏同期利率和LPR利率分段时间查询表，利⽤ index+match函数查所属档位；
2. 建⽴计息时间段查询表，确定借贷起⽌时间下⼈⾏同期和LPR分别计算的时间段；
3. 建⽴⼈⾏同期利率表、LPR利率表、利息计算明细表，根据利率更新的时间段，利⽤ index+match函数（匹配时间段）和ifs函数
（匹配档位），查需要引⽤的利率。
实现步骤
制作利息计算总览表
设置主要输⼊参数的单元格：借贷⾦额、起⽌时间。
设置需要输出结论的单元格：利息等（这⾥设计可能需要使⽤的利息-⼈⾏同期、利息-LPR和本⾦+利息）。
主要功能：更新三个输⼊参数后，不⽤查看其它表单，就可以得到想要的结果（前提：后续表单中利率输⼊⽆误）。
制作⼈⾏同期利息计算表
分为三个表单：利率表，计息时间、利息明细。前两者是输⼊，后者是输出。
清除内存多前⾯有说到为什么是它们三个放在⼀起：⽅便引⽤。
一家十一口打一字
利率表：数据⽹上搜索后填⼊，并设计了“⽣效时间-起”“⽣效时间-⽌”两列（⾃动计算）。“⽣效时间-起”直接等于同期的调整时间，“⽣效时间-⽌”等于下⼀⾏的调整时间减1。
注：最开始⽤的date(year(xx),month(xx),day(xx)-1)，结果⼀样，过程复杂，后来改⽤直接减1，清爽多了。这⾥如果对excel的时间和⽇期有⼀定了解，就能理解了。⽇期在excel内存⾥是以数字的形式储存，减⼀也可以达到同样的⽬的。
计息时间：引⽤计息时间段查询表，放在辅助表单中，后续介绍。
利息明细：以利率表的更新周期为⼀期，引⽤利率表的起⽌时间、利率，得到该期的利息。
这⾥需要处理的内容⽐较多，总结如下：
起始⽇期：第⼀个起始⽇期引⽤计息时间的起始⽇期。当计息时间的起始⽇期为“”，它也为“”。第⼆个开始，=上⼀期期末⽇期+1
期末⽇期：当上⼀⾏期末⽇期=贷款时间-⽌，为空；当贷款时间-⽌<=利率表中⽣效时间-⽌，=贷款时间-⽌；当贷款时间-⽌>利率表中⽣效时间-⽌，=利率表中⽣效时间-⽌。
利率：根据贷款时长，利⽤ifs函数选择引⽤的档位（office 2016的朋友反馈说函数不兼容导致计算失效，最后下载office 365解决了），利⽤index+match函数选择引⽤的期数（调整时间）。
注：利⽤if函数多层嵌套也可实现⽬的，但没有ifs简洁。
利息：数学问题，注意年利率换算成⽇利率（除以360）即可。
制作LPR利息计算表
步骤同上，只有两档，更简单啦。
制作辅助查询表
1. 档位判断：根据贷款起始时间，计算不同档位的起⽌时间，建⽴分段时间查询表。通过index+match函数，判断贷款终⽌时间落⼊查
询表的哪⼀个区间，输出档位名称。
2. 引⽤时间段计算：根据贷款起始时间，计算引⽤⼈⾏同期利率和LPR利率的时间段。（因LPR是2019/8/20公布，在该⽇期前，引
⽤⼈⾏同期利率，之后，引⽤LPR利率。）
经验总结
为了⽅便，⼈⾏同期的利率、时间段、利息明细⼀张表单，LPR的利率、时间段、利息明细⼀张表单，辅助的查询表⼀张表单，相互引⽤，免得表单之间翻来翻去。（之前想按利率、利息明细分表单做，引⽤得我烦躁。）
为了美观，很多地⽅需要加⼀层if(XX=“”,“”,YY）或者⽤iferror，正⽂没有阐述这⼀点，⽤的时候出现#N/A⾃然会知道啦。
根据利率公布⽇期判断⾃动⽣成利息计算表的起始⽇期、期末⽇期时，⽤到的index+match函数。版1、版2时因为对match函数的了解不够透彻，最初多列了⼀⾏，将公布时间当⽉的第⼀天作为时间线来引⽤。做版3时才想起最近看的excel书中有类似的例⼦，研究了下，发现match的match_type为1或省略时，会查⼩于或等于 lookup_value 的最⼤值。经过尝试，发现已经满⾜要求。这⾥摸索浪费了⼀些时间，不然应该可以更快完成。
情人节祝福的话更新时间：2020/8/6
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论