导 读

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

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

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）