用Excel函数实现库存分析和供应链预测|offset|文件名|表头

来源：市场资讯

（来源：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)

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