对 杜邦公式excel模板 的分析

onlyhuman028

这两天在研究杜邦公式，找到一个 EXCEL 建模（见附件），借鉴其表达方式

以下是对该模板中函数的分析

杜邦测算表函数分析


计算目的，根据选择的月份，计算当月及上月的ROE，并展示杜邦分析动态过程


表格分为 两个子表，“杜邦分析”、“指标表”
原理：
“杜邦分析”子表分析：
说明：“杜邦分析”中，有A,B为  两个辅助列。
一个辅助计算选择月份的单元格地址，一个是把 指标表中的 指标的行号 写死，方便勾稽。

1、第一步：通过N2单元方位的 上下 控件，调整B1单元格的“月份”。实际控制的数据要比 展示出的月份大1，但是对使用人来讲，仅滚动展示，无影响。
控件 最大值13，最小值3，步长1 。实际 最大值12，最小值2 也可以，对应的公式调整即可。最小值如果是1，因为配套函数勾稽关系，无法展示1月数据，会报错。
2、第二步：在的 A3 和B3中，将“B1-1”月份应对应的列号找到。
=LEFT(ADDRESS(1,B1-1,4),1+((B1-1)>26)*1)
---add函数返回一个 绝对地址的单元格地址文本，ADDRESS(1,B1-1,4) 的返回结果是”L1”
然后用LEFT函数，取出字母l
=LEFT(ADDRESS(1,B1,4),1+(B1>26)*1)
---计算 当月，原理如前

3、第三步：根据第二步的列号计算结果，在“指标表”中找到该列并返回对应的 月份。
这步其实不参与后面的计算，只是做一个比较，方便校对。

=INDIRECT("指标表!"&A3&"1")
INDIRECT跨子表相对引用，如果用绝对引用，无法勾稽月份变量

4、第四步：开始杜邦底层计算
比如 营业成本
=INDIRECT("指标表!$"&$A$3&VLOOKUP(D22,$A$5:$B$25,2,0))
先用VLOOKUP函数从辅助列找到 “营业成本”在“指标表”中的 行号
汇总成 一个 单元格地址，从“指标表”中返回该指标的值。

5、把第四步的 公式，复制到每一个 需要取值的但员工，手动调整或者拖动即可。
需要计算的，在本表中直接公式计算。

=(ROUND(H11,4)-ROUND(H12,4))*100
使用了包括 round四舍五入函数等。对 当月和上一月进行环比分析。

6、第六步：为了直观，引入了 动态图标
使用 组合图的 自定义组合
内圈 “圆环图”，预警色带 “圆环图”，外圈 圆环图，权益净利润 用饼图（指针）

增加辅助列，把指标表的 行号写死，好处是 可以规避 基础表中 业务部填写的 指标表
可以用其他函数代替。=VLOOKUP(D22,指标表!$A$1:$M$20,$B$1-1,)。
与财务指标 口径不一致的情况，但是坏处 是一旦变化，就无法自动取。
不过总的来讲，利大于弊，因为 业务部门的 口径 五花八门，财务相对 固定，不能因
基础表 命名的不规范 来 调整 杜邦分析表

493861364

谢谢楼主分享

onlyhuman028

493861364 发表于 2021-6-4 22:03
谢谢楼主分享

不客气，多提意见