datagridview实时更新数据_Excel动态报表：如何实时监控和分析库存

一、提出问题

如何实时监控产品库存，分析进货时间

二、理解数据

1.数据来源

这是某平台的一张进销存报表，此表格被处理过，此处用于练习（ 提取码：myeb）。

练习专用-进销存报表​pan.baidu.com

2.字段含义

3.数据类型

这张表中的库存日期不是标准的时间格式。入库凭证是文本型的数字。正品库存数量，即正常商品列的数据也是文本型的数字。这些数据需要进一步处理。至于其他列，数据的类型和格式是匹配的。因此，不需要处理。

三、数据清洗

1.重命名

为了方便查看，这里把原始的字段做简化处理。完整的优化结果，请参考上文。

商品名称太长，并且开头都是统一的品牌名，可以优化。先插入列，并命名为商品简称。通过函数公式=MID(D2,5,LEN(D2)-4)，就可以得到不含商品品牌的商品简称“华夫面包1000g/箱”。

2.缺失值，重复值和异常值

通过检查，每一列的计数都相同。这说明，数据不存在缺失值。这里也可以用定位条件来查看数据是否存在空值。在数据菜单栏里，使用删除重复值功能，查看整张表或商品编码列，并没有发现重复值。用排序和筛选，也并未发现异常的数据。所以，这里不需要任何操作。

3.一致化处理

经过检查发现，库存日期列不存在单元格的格式问题。这里使用分列功能来进行一致化处理。操作路径：选择该列数据—数据菜单栏—分列—下一步—下一步—列数据格式选择日期YMD—确定。

在表格中，入库凭证列和正常商品列的数字格式是文本形式。这里要把它转换成数值形式的数字。方法有多种，比如可以先插入列，使用选择性黏贴，选择数值项，再使用分列功能，就可以得到数值形式的结果。如果结果还是文本型数据，就把单元格格式设置为数值即可。

四、构建模型

1.创建实时库存透视表

因为是实时监控库存，所以库存日期选择最新的2018/7/31。

2.创建产品月销透视表

3.创建库存监控表格

新建一张表格，并命名为库存监控，表格标题如下所示

用vlookup函数查找“实时库存表”和“产品月销表”中的“正常商品”和“出库数”，分别返回到“实时库存”和“产品月销”列。另外，周转时间=实时库存/产品月销。实时库存vlookup函数=VLOOKUP(B4,实时库存!A3:B16,2,0)，产品月销vlookup函数=VLOOKUP(B4,产品月销!A3:B18,2,0)。

接下来，设置单元格格式，把周转时间列的数值保留1位小数点。在开始菜单栏，使用条件格式对周转时间列进行色彩差异化处理。红色代表库存过多，需要尽快促销，绿色代表库存过少，需要尽快补货。注意：色彩设定要参照库存标准，库存标准要参考商品保质期等。

4.用切片器做动态报表

在实时库存透视表单击任一单元格，插入切片器，插入透视图表，并剪切到库存监控表。切片器右键，报表连接，勾选两张表。

对显示为#N/A的结果，这里使用iferror函数进行优化。周转时间列优化函数：=IFERROR(C3/D3,"")，实时库存列函数：=IFERROR(VLOOKUP(B3,实时库存!A3:B16,2,0),"")，产品月销列函数：=IFERROR(VLOOKUP(B3,产品月销!A3:B18,2,0),"")。

五、数据可视化

通过点击仓库，在左边的动态表格中，可以看到各个商品的周转时间，红色文本表示库存积压，绿色文本就表示商品周转时间很短，需要尽快补货。在透视图表中，可以看到各个商品的实时库存量，以及各个商品的库存对比情况。不断更新进销存报表，就可以实时监控库存，分析进货时间。