用Excel函数实现库存分析和供应链预测

来源：市场资讯

（来源：JitLogistics）

图表动态显示物料消耗

之前有人问我，我做的那个库存管理的Excel报告怎么做出来的，特别是这个图表，选物料，修改显示月份，下面的图表会跟着一起做相应的变化？我不记得当时我怎么回答的，只记得说几句，对方只是觉得好复杂好神奇，今天整理硬盘，再发现这个报告的时候，我问我自己，我怎么做出来的，说实话有些遗忘了，为了保证将来不会再忘记，将记忆和知识锁定在脑海里。从今天开始，这个号将开始热爱学习，下面就一步步的讲一下这个图表是如何实现的。

当输入显示月份数量，选择物料后，下面的图表会跟着变化

比如，我输入月份数量10，物料A，下图的图表将跟着做相应的变化

为了信息安全，这里我们用Rand()函数生成几年的销售数据，rand()函数是可以生成一个小于1的随机数，结合取整函数Int()来生成销售数量。(这里插一个小故事，这两个函数超级有用，还记得孩子小的时候，我这个懒人为了让孩子练习口算，懒得自己一个个的出题目，就是用这两个函数生成了一张张的卷子，我只需要打一个回车，就会出一张卷子，而且我自己不需要算，答案同时产生，自己乐呵呵的坐在边上跟娃说，你看妈妈跟你一起算，超好整娃利器，不知道娃知道后会不会恨我一辈子)

数据准备

打开一个空白Excel，生成两张表，表Source我们将存放所有的物料销售信息，表Chart我们会存放生成的图表

1) 在A2数据格里输入公式=INT(Rand()*1000)，并拷贝到全部数据格

2) 为了防止数据改变，我们拷贝所有数据，并Paste到原来的地方。全部选中有数据的单元格，点击右键copy，并选择paste options 中的paste value（下面有123的那个图标）

3) 生成报表

选择ABC列，生成折线图形报表

点击鼠标右键，将新生成的图表移动表Chart下，以防止图表和原始数据源相互干扰

定义变量

变量的定义是实现图表动态跟随选择变化的关键，变量的定义可以运用到更广泛的地方以实现更多的可能。

1）选择菜单/,定义单元格C2的名为NumMonths，来作为显示月份数量，定义单元格C3名Select_material来作为选择哪个物料显示图形

定义完成后，在Name Manager中你应该可以看到如下两个变量

2）同理，我们需要定义BlankRange=0，后面的其他设置需要这个常变量，在referes to字段里面输入=0

3）接下来我们定义时间轴需要的日期范围变量，设定这个变量是Date，这里用到了函数offset，这个函数是返回对单元格或单元格区域中指定行数和列数的区域的引用。在我们要做的表格中的日期需要引用指定的多少个月的确定的日期；也就是说，如果我输入24，就是要从source表中最后一个月，往前倒推24个月；如果是12，那就是要倒推12个月。Offset可以帮我们实现，这个函数是整个报表的关键

参考微软的函数定义：

OFFSET(reference, rows, cols,[height], [width])

OFFSET 函数语法具有下列参数：

Reference ：必需。要作为偏移基准的参照。引用必须引用单元格或相邻单元格区域。否则,OFFSET 返回#VALUE! 。跟月份相临近的就是年，我们这里就是Source!$A$1

Rows ：必需。需要左上角单元格引用的向上或向下行数。使用 5 作为 rows 参数，可指定引用中的左上角单元格为引用下方的5 行。Rows 可为正数（这意味着在起始引用的下方）或负数（这意味着在起始引用的上方）。我们是往下引用，要利用这个参数选出从哪个月开始往下计数，所以这里正数，用COUNTA返回全部月份的数量，Nummonths就是我们要从最近的日期往前显示多少个月份的数量，所以，从Source!$B:$B)-NumMonths行开始往下引用

Cols ：必需。需要结果的左上角单元格引用的从左到右的列数。使用 5 作为 cols 参数，可指定引用中的左上角单元格为引用右方的5 列。Cols 可为正数（这意味着在起始引用的右侧）或负数（这意味着在起始引用的左侧）。我们因为引用当前Column，所以是0

高度：可选。需要返回的引用的行高。Height 必须为正数。高度就是我们多少个月，这里就是NumMonths

宽度 ：可选。需要返回的引用的列宽。Width 必须为正数。因为日期包括年，所以是两列，宽度为2

最后得出日期定义如下，在Name Manager中加入以下对变量Date的定义=OFFSET(Source!$A$1,COUNTA(Source!$B:$B)-NumMonths,0,NumMonths,2)

4）定义物料

接下来我们定义需要显示的物料Material A，对应的所要显示的月份数据

=OFFSET(Source!$C$1,COUNTA(source!$C:$C)-NumMonths,0,NumMonths,1)

并加入到Name Manager中，同理，我们定义其他的物料。

E）定义图形

要定义选择不同物料显示不同物料对应的图形,这里我们用到IF函数，在Excel中，IF函数运用相当广泛，当我们选中某个物料的时候，我们就要显示这个物料的图形，Select_Material这个变量是我们要选择的物料，图形要显示的区域就是source!$C$1,如果不是，那么不显示.所以定义如下

S_Material_A=IF(Select_material=source!$C$1,material_A,BlankRange)

同理，我们对有的物料对应的图形进行定义，做完这基本，我们已经完成了最困难的部分，接下来，我们需要显示出图形来。

图表编辑

1）我们先在Chart表的单元格C2输入月份数量24,在单元格C3我们设置下拉选择框，菜单里选择/选择List

在source里面选择我们所有的物料，输入=Source!$C$1:$G$1,完成后，可以下拉选择不同的物料。

2）打开图形DataSource 对话框，对之前做的图形进行X轴和Y轴数据定义，在左面Legend Entries对话框里面选择Edit

出现如下对话框，在Seriesvalue中我们输入

='Sample_MaterialConsumption.xlsx'!S_Material_A，

我的excel文件名是Sample_materialConsumption，可以根据你自己的Excel文件名不同修改文件名

点击，在右边的Horizontal对话框点击Edit，出现下图，并在Axis label range 对话框中输入='Sample_MaterialConsumption.xlsx'!Date，就是我们之前定义的时间区间。

点击，一个物料的图表完成，我们先在Excel里面试验下，看下结果如何：

我们选月份24个，物料A，显示如下

再输入月份12个，图形变化如下，确实是正确显示了12个月份。

证明，之前我们的定义完全正确，接下来，我们可以选择Add把其他物料的图形一一定义好后，datasource对话框里面的情况应该如下：

这里的Series名字没有定义看起来不舒服，我们可以通过Edit来定义好每个图形的名字，方便将来自己修改

这样这个表的主体已经基本完成了，这个时候，你输入月份数量选择物料，图形应该会根据你选择的物料和月份数量变化。

图表表头设置

到现在，这个图表，基本已经大功告成，只是美中不足的是，少了表头，我们希望表头可以显示物料，比如:

Material A 24 Months Consumption

我们依然可以运用Name Manager ，定义新的变量 Title

=Select_material & " " &NumMonths & " Months Consumption"

鼠标选择表头，并在fx输入框中输入

="'Sample_Material Consumption.xlsx'!Title"

让表头的数值等于变量Title的值。

设置完成后，我们的表名会跟随你的输入变化

图表设置完成。

这个设置可以帮助我们对一些重点物料的消耗和预测做出及时的跟踪，判断和更正，我们可以进一步优化图表，加入预测曲线，订单数量曲线和库存曲线或者几个物料曲线对比等等，可以实现对重点物料，库存等的可视化分析。

欢迎留言，看懂或者没看懂，欢迎反馈，后续根据需要继续推出新的内容