导 读


你有没有试过,将每月的报表数据都整理在一张原始表格里,然后通过控件的方式随时调取各个月份的利润表,就比如这样。是不是觉得很神奇。



如果你不会,那么你应该来学习一下他是如何制作的,他可不是花招哟,它涉及的知识点在财务日常工作中是绝对用得上的。


1、插入控件

上图中的那个鼠标点击小按钮就是控件。


这个控件与月份的单元格是联动的,我们可以通过点击控件来改变月份,它是制作动态利润表的关键。下面我们一起来看一看这个控件是怎么做吧?


首先我们插入控件,它在开发工具选项卡中的插入功能中,点击下拉菜单第二行第三个按钮即可。



有的同学可能没有找到开发工具栏,一般默认情况下是没有开发工具这个选项卡的,需要在选项的自定义功能区中勾选开发工具,才会出现该选项卡。


然后我们设置控件的格式,右键该控件,点击设置控件格式。



上图中涉及到多个格式的设置,让我们一一来看下。


最小值和最大值可以为控件调节的数值大小,我们设置最小值为1,最大值为12,即1-12月。


步长为点击一下控件,数值变动多少步长。我们设置成1,即表示点击一下数值增加或者减少1.


单元格链接表示该控件调节哪个单元格,我们输入需要控制的单元格(A1)即可。


那么我们的控件就设置好了。


2、设置单元格格式


因为我们是要调取月份,而不是调取数字。其实我们设置下单元格格式,用障眼法就可以做到。


我们打开单元格格式,然后在自定义中输入:2017"年"0"月",就可以实现这个效果,肉眼看上去是月份,实则是数字。




3、设置公式引用本月数据,求和累计数

我们使用INDEX函数,根据月份的变动引用对应月份数据。


使用OFFSET函数,计算本年累计数,即1-N月求和。Offset负责调取1-N月的数组,Sum负责对数组求和,公式如下:



控件、单元格格式和对应函数的原理如果都明白了,那么就可以来做一个简单的模型了。



4、准备原始数据和利润表设置公式


我们准备利润表原始数据,然后每月持续更新,如下图。



其实上图中整理的原始数据,很适合用于数据的对比分析,一般的财务软件并不能实现这个功能(即每个月各个项目的数据排一起)。


我们再做一个简单的利润表模型,然后分别录入公式,公式录入一次即可。下表中展示了各个单元格录入的公式,公式原理同步骤3。



公式设置完毕之后,即可通过控件来调节数值,数值的变动,导致公式引用的月份数据发生变通,同时求和区域也发生变动,最后实现本月数和累计数伴随月份的变动而变动的一个效果。


其实资产负债表、现金流量表以及一些管理报表模块也是可以参考上表中的方法,进行模型制作,实现动态的效果。



作者:陈世杰  微信公众号:24财务excel (ID:caiwuexcel)