excel2016怎么做数据分析？Excel2016做仓库统

　　本文介绍如何应用Excel的PowerPivot组建搭建简易的规范的进销存系统，本文重点在于如何数据分析和输出，而是不原始表单的设计和录入。近来很多人不管是不是IT人事，都把大数据、云计算、数据挖掘挂嘴边，好像不说这些就跟时代脱节了。不管你愿不愿意，数据库管理已经进入到生活的方方面面。初学者对于数据库很迷茫，特别是用过Excel的，热衷于简单的电子表格，一提到数据库的名词概念就觉得复杂。自从Excel2013以来，安装时自动增加了PowerPivot这组应用程序和服务，强大的分析功能可以取代Access数据库的一些基本功能，也简化了很多运算。
　　应用场景描述：管理员小云每天都要登记本企业生产的产品，产品名称有上百种，平均每种产品有10个左右的规格，实际就是要管理上千个库存单品（SKU）。每天要记录各SKU的进库数，出库数，每月进行盘点核查，每月要找出库存低于安全库存的SKU提交生产部门。
　　需求分析：规范的进出库原始台账；输出报表：计算月末库存、计算安全库存；盘盈盘亏的调整记录。
　　1、建三张基础数据表。
　　表设计要规范，不能直接拿进出仓单的表式，规范的标准是符合数据库范式，有兴趣就上网搜索，没空闲就按照图示去做吧。规范要求：首行是标题行，2行起是数据行，每一行就是一条记录。如图，建立：
　　编码表（SKU号、产品名称、型号规格、单位）
　　年初库存表（SKU号、年份、年初库存）
　　进出仓表（SKU号、日期、进仓数、出仓数）
　　这里的SKU号是关键字段（标签），有了它，就可以打通三张表的关联。这里有2个容易犯错的地方：编码表的SKU号不可重复；进出仓表的日期用用日期格式，注意是用减号ldquo；rdquo；连接年月日。
　　2、使用PowerPivot的数据模型功能导入表。选择ldquo；编码表rdquo；的数据rarr；点选菜单的PowerPivotrarr；点添加到数据模型。而后会出现数据模型界面（多弹出一个对话窗），显示刚才添加的编码表的数值。
　　注意：
　　第一次启动PowerPivot的工具或组件，会很慢，要耐心等待，不要急于操作下一步；
　　数据表不能重复添加，添加一次就够了；
　　数据模型里面的表是链接表，是只读的，要修改就要回到Excel主界面进行工作表的修改；
　　选择数据最好是整列整列地选择，不要仅选择数据区域，因为当以后增加数据的时候，如果是选择区域的话就要修改链接表的选择范围。
　　然后，回到Excel主界面，同样操作添加ldquo；年初库存表rdquo；和ldquo；进出仓表rdquo；到数据模型。这三个表链接过来后，默认是叫表1、表2、表3，为方便使用，改名为ldquo；编码表rdquo；、ldquo；库存表rdquo；、ldquo；进出仓rdquo；。
　　3、在数据模型里面建立关系。
　　ldquo；关系rdquo；是关系型数据库里面一个很重要的概念，这里不展开，有兴趣可自己上网查。这里应用ldquo；关系rdquo；，起到数据从一个表传递到另一个表的作用。回到PowerPivot界面，右下角点击关系视图。将ldquo；编码表rdquo；的SKU号拖到ldquo；库存表rdquo；，再将ldquo；编码表rdquo；的SKU号拖到ldquo；进出仓rdquo；。这样，就建立了2个一对多的关系。
　　4、用数据模型建数据透视表。新建一个工作表ldquo；统计表rdquo；，插入rarr；数据透视表rarr；选择ldquo；使用此工作表的数据模型rdquo；，由于之前建立了数据模型，所以这个选项没有致灰rarr；位置选现有工作表，统计表！A8，确认。
　　5、用数据透视表显示各SKU进出仓情况。
　　之前虽然改了名字，但数据透视表中显示的还是表1表2表3，这里只好把这个Bug放一放，期待office升级解决吧。拖拉表2的年份到ldquo；筛选器rdquo；，拖拉SKU码到ldquo；行rdquo；，拖拉表2的年初库存、表3的进仓数和出仓数到ldquo；值rdquo；。这样，数据透视表就按每一个SKU输出了其合计进仓数和出仓数，也将期初库存显示出来了。注意：系统会对值增加汇总方式的描述，例如：以下字段求和汇总：进仓数，我嫌太长，手工改成进仓数了。
　　6、用度量值计算期末库存。
　　Excel界面下，菜单rarr；PowerPivotrarr；管理数据模型，进入PowerPivot界面。选进出仓表，点选该链接表下方的非数据区域某一个单元格，在公式栏敲上
　　期末库存：sum（〔进仓数〕）sum（〔出仓数〕）SUM（39；库存表39；〔年初库存〕）
　　为了计算安全库存，再选择非数据区域某一个单元格，在公式栏敲上
　　最大出仓：sum（〔出仓数〕）
　　注意：公式栏对中文输入法可能不大接受，我是在文本文件打好中文再复制粘贴上去的；〔进仓数〕等字段名字，可以不手工敲，而是用鼠标点选那一列；公式可以跨表引用列，如期末库存就应用了库存表的年初库存列。
　　理解度量值。完成了上述公式后，系统会立刻显示结果，例如：135。大家也许会疑问，这样的求和有什么意义？有意义！现在的求和结果是基于没有分类的条件下的求和。应用到刚才建立的数据透视表，就会按SKU分类求和。下来还会讲到ldquo；日程表rdquo；，就会既按SKU求和，又按时间分段（如：月、季）求和。
　　7、添加日程表。回到Excel界面，选择数据透视表，在值里面增加刚才建立的度量值ldquo；期末库存rdquo；。在点选了已制作好了的数据透视表前提下，菜单rarr；分析rarr；筛选，插入日程表。用这个日程表，就可以自由选择14月的进出仓量，112的进出仓量了，也可以看到期末库存量随着时间段变化而变化。
　　8、用每月出仓数计算安全库存。安全库存的计算方法很多，这里只用最简单的一种，求出历史以来单月出仓数的最大值，若当前库存量低于这个值，就需要补充进仓其中的差值。步骤六已经建立了出仓数求和公式了。下面就插入新数据透视表，选择日期为列标题（增加日程表后，就会多了日期（月）的度量值，系统自动将这个度量值一同放到列标题），出仓数的求和为值，SKU号为行。将日程表与这个新的数据透视表关联起来。
　　点选新数据透视表rarr；设计rarr；总计rarr；选择仅对列启用。在N24格（根据新透视表的实际位置而定）写上标题：最大出货量，O24写上标题：需补进仓。在N25输入公式MAX（B25：M25），在O25输入公式N25VLOOKUP（A25，A9：E17，5）。其中A9：E17的区域根据第一个透视表实际区域而定。
　　9、盘盈盘亏怎么办？答案：修改年初库存表。所以这里为什么每年设一次年初库存，就是应对每年盘点后库存的变化。而且，用年份做筛选条件，也是这个原因。
　　10、如何显示产品名称。光看SKU码不直观，要将名称、规格加进去怎么做？进入PowerPivot界面。选进编码表，在数据表区域，新增一列名叫ldquo；名称型号单位rdquo；，在该列1行的单元格输入〔SKU号〕amp；，amp；〔产品名称〕amp；〔型号规格〕amp；，amp；〔单位〕选择。系统会自动填充整列。回到Excel界面，数据透视表的行标题统统用ldquo；名称型号单位rdquo；就可以解决这个问题了。
　　注意事项：
　　1、上述操作过程几乎没有在原始表上操作，能保证原始表数据不会被破坏。
　　2、上述表格式是最基本的格式，可自行添加修改字段。也可根据ERP导出的表格修改。
　　3、非数据区域的度量值，必须用聚合函数，如：sum（），max（），min（），count（）等等。