财务：期初本金公式怎么写？
前两天接了一个Excel的定制，给钱的那种。
问题不复杂，半个多小时就搞定了。但是整个沟通过程花了我近4个小时。
这也是我不太愿意接Excel定制的原因。
会Excel的人，不懂业务流程。懂业务流程的人，不会Excel。
Excel的定制很花时间，不是花在做表格上，而是花在沟通上。
所以，如果不是每天闲的难受，我劝你别做Excel定制。
1- 问题描述
回到文章的主题。
今天收到一个财务同学的提问：「期初本金」的公式太长了，怎么能改的简单一点？
图片放大一点看
完整的公式是这样的：
=IF(Z3=2,Y3+AE3,IF(Z3=3,Y3+AE3+AG3,IF(Z3=4,Y3+AE3+AG3+AI3,IF(Z3=5,Y3+AE3+AG3+AI3+AK3,IF(Z3=6,Y3+AE3+AG3+AI3+AK3+AM3,IF(Z3=7,Y3+AE3+AG3+AI3+AK3+AM3+AO3,IF(Z3=8,Y3+AE3+AG3+AI3+AK3+AM3+AO3+AQ3,IF(Z3=9,Y3+AE3+AG3+AI3+AK3+AM3+AO3+AQ3+AS3,IF(Z3=10,Y3+AE3+AG3+AI3+AK3+AM3+AO3+AQ3+AS3+AU3,IF(Z3=11,Y3+AE3+AG3+AI3+AK3+AM3+AO3+AQ3+AS3+AU3+AW3,IF(Z3=1,Y3)))))))))))
做完定制刚平复下来的心情，再次掀起波澜。
2- 原因分析
解决问题没有头绪的时候，使用框架，按照步骤一步一步去分析，会更快一些。
按照2W1H的框架套一下，思路梳理如下。
WHAT，什么是期初本金
什么是「期初本金」？
直接根据公式可以大概猜出「期初本金」的计算规则。
1- 确认已还款期数
确认Z列中的「已还款期数」。
2- 计算期数对应的本金之和
假如期数是3，那么就把这个期数之前的「本金」1和2都加起来，最后和Y列的本金相加。
相加的结果，就是「期初本金」
直接百度出来的结果，在没有清楚的解释「期初本金」之前，又丢出了一堆的财务术语。
WHY，为什么公式那么长
因为不同的期数，求和的区域不一样，所以要使用IF函数：
1- 对每个期初进行判断
2- 根据期数不同，求和区域也不同
所以IF公式的判断条件，就变成了这样：
其中Z3代表的是「期数」，把这些条件合并起来，就是开始我们看到的公式：
=IF(Z3=2,Y3+AE3,IF(Z3=3,Y3+AE3+AG3,IF(Z3=4,Y3+AE3+AG3+AI3,IF(Z3=5,Y3+AE3+AG3+AI3+AK3,IF(Z3=6,Y3+AE3+AG3+AI3+AK3+AM3,IF(Z3=7,Y3+AE3+AG3+AI3+AK3+AM3+AO3,IF(Z3=8,Y3+AE3+AG3+AI3+AK3+AM3+AO3+AQ3,IF(Z3=9,Y3+AE3+AG3+AI3+AK3+AM3+AO3+AQ3+AS3,IF(Z3=10,Y3+AE3+AG3+AI3+AK3+AM3+AO3+AQ3+AS3+AU3,IF(Z3=11,Y3+AE3+AG3+AI3+AK3+AM3+AO3+AQ3+AS3+AU3+AW3,IF(Z3=1,Y3)))))))))))
HOW，怎么把公式改短些
这个问题的本质，就是根据「期数」不同，对不同区域进行求和。
所以这里涉及到两个问题：
1- 根据条件进行求和，多条件求和。
2- 根据条件更新求和区域，动态区域。
具体请看第3个部分。
3- 解决方法
明确了问题的需求，接下来检索一下对应的函数公式。
1- 只对标题中的「本金」进行计算，这是条件求和，用SUMIF函数
2- 选取根据期数，选择求和的范围不同，这是一个动态的区域，用OFFSET函数。
结合这两个需求，重新编写后的公式如下：
=Y3+SUMIF(OFFSET($AD$2,0,0,1,MATCH(Z3,$AD$1:$BX$1,0)-2),$N$2,OFFSET(AD3,0,0,1,MATCH(Z3,$AD$1:$BX$1,0)-2))
简单的拆解一下公式
(点击查看大图)
1- Y3代表的是本金
2- SUMIF函数，根据「期数」不同，对「期数」内的本金进行求和
3- OFFSET函数，根据「期数」动态计算求和区域。
4- 总结
总结一下这个案例。有几点值得借鉴：
1- 梳理需求
根据IF函数梳理清楚需求，把每个条件梳理到表格或思维导图中。
2- 转换思路
把需求转换成函数公式的功能，条件判断？数据查询？条件求和？等等
3- 匹配函数
根据公式需求，去匹配对应的函数，这一步需要有扎实的函数积累。
4- 编写公式
知道了用哪些函数，知道了需求的逻辑，接下来动手编写公式即可。
5- 提问
讲到这里了，大家说一说，这位财务同学，为什么会被这么长的IF函数给困住？
我是拉小登，一个会设计表格的Excel老师