商品库存结构分析方法(用数据透视表)

库存结构分析 -数据透视表操作手册宝洁公司分销商渠道多功能小组目录一、报表准备11下载1二、报表整理12增加销售数据13输入公式14复制公式25选中数据域26排序37另存新品38输入销量排名4三、制作库存结构透视表49选中数据区域410进入向导411进入布局界面512布局透视表513调整字段格式614完成布局715调整透视表716组合数据817库存ABC分类818分析9四、制作库存天数分布透视表1119进入布局界面1120透视表布局1121调整字段格式1122组合库存天数1223库存天数分段1324分析1325合并透视表1426分析具体数据信息14五、制作品牌库存分析透视表1527参考步骤131516一、报表准备1下载：从DMS下载当月或最新的历史库存明细报表。二、报表整理2增加销售数据：打开报表，在X 1单元格键入“过往2月销售成本”。如下图所示。03输入公式：将输入法转换成英文环境，在X 2单元格键入公式“=w2*61/u2”（如下图），回车。注意保证所有输入内容包括“=”一定都是英文格式。 *公式：库存天数=库存金额*61/过往2月销售成本； 过往2月销售成本=库存金额*61/库存天数。 4复制公式：选中X2单元格，将鼠标箭头放在X2单元格右下端，待出现黑色“+”，双击鼠标。将X2单元格的公式复制到整列。5选中数据域：选中第2行，按住“Ctrl”键和“Shift”键的同时，按方向键中的“向下键”，从而将除第一行的数据选中。从操作菜单中找到“数据”“排序”。6排序：在排序窗口中，主要关键字中选“按列X”，并选中“降序”。7另存新品：排序后，将所有X 列单元格中数据为“#DIV/0!”的行选定，复制到另一张sheet中，这些多数应该是当月新品，前2个月没有销售。同时在原报表中点击右键，将这些数据删除，如下图所示。8输入销量排名：在Y1单元格中键入“销售排名”，在Y2中键入“1”，然后按住“Ctrl”键，向下拖动鼠标，将所有数据按过往2月销售成本排名次。三、制作库存结构透视表9选中数据区域：选中A1单元格，同时按住“Ctrl”键和“Shift”键，依次按方向键中的向下键和向右键，从而选中整个数据区域。10进入向导：在操作菜单中进入“数据”“数据透视表和数据透视图”。11进入布局界面：连续点击两个“下一步”，进入下图所示环境，选中“布局”。12布局透视表：如下图所示，在“布局”界面中，在右边的字段中找到“销售排名”，拖到“行”区域中，将“过往2月销售成本”字段和“库存金额”字段放到“数据”区域。13调整字段格式：双击“求和项：过往2月销售成本”按钮，出现“数据透视表字段”菜单。在这个菜单中点击“选项”按钮，下方出现“数据显示方式”对话框。在下拉菜单中选中“占总和的百分比”。同时，为了更清晰起见，可以将名称改为“销售比例”，点击“确定”。同样方法，双击“求和项：库存金额”，在“数据透视表字段”菜单中点击“选项”按钮，在“数据显示方式”下拉菜单中选中“按总和的百分比”。并把上端的名称改为“库存比例”，点击“确定”，回到“布局”界面。14完成布局：在“布局”界面中点击“确定”，回到数据透视表向导界面，点击“完成”。15调整透视表：在生成的数据透视表中，用鼠标点中 B3，也就是“数据”所在单元格，向右拖至C3，也就是“汇总”所在单元格。这样，透视表将从上面的视图变成如下图所示的界面。16组合数据：选中A4，即“销售排名”单元格，点击右键。如下图所示，选中“组及显示明细数据”“组合”，弹出分组对话框。17库存ABC分类：在“分组”对话框中，“起始于”栏中填“71”，在“终止于”栏中填“210”，“依据”栏填“140”，点击确定。分组之后，可以得到如下的数据透视表。18分析：根据这个透视表，我们可以分析各种规格的库存的合理性。例如在卖的最多的70个规格销售比例约为60%，但是库存比例只有40%，而卖的最少的那些规格销售比例只有15%，库存比例却达到了39%，这说明滞销库存还是挺多的。同时如果想得到具体的信息，例如想知道前70个 SKU具体是哪些，可以双击相应的单元格“71”。双击之后出现“显示明细数据”对话框，选择其中的“商品名称”。则会出现如下视图：以上的数据透视表可以对库存结构的整体情况有个大致的了解，具体的分析问题还需要更准确的看到所有库存规格的库存天数的分布。四、制作库存天数分布透视表19进入布局界面：点回到sheet1，此时整个数据区域应该还处于选中状态。按照同样的方法，选中操作菜单中的“数据”“数据透视表和数据透视图”。依次点击“下一步”“下一步”“是（Y）”“下一步”“布局”。20透视表布局：在“布局”界面中，如下图所示，将“销售排名”字段放到“行”区域，将“库存天数”分别放到“列”区域和“数据”区域。21调整字段格式：双击数据区域的“求和项：库存天数”，在“数据透视表”对话框中的“汇总方式”的列表中选择“计数”，“确定”“确定”“完成”“确定”。22组合库存天数：如图，在得到的数据透视表中，右键点击B3，即“库存天数”所在单元格。选择“组及选择明细数据”“组合”。23库存天数分段：在弹出的“组合”对话框中，“起始于”填“3”，“终止于”填“31”，“依据”填“4”，确定。得到库存天数分布表。24分析：在这个透视表中，我们可以看到以下几个问题。（1）库存天数小于3天的，有114个规格，这些非常有可能造成缺货，尤其是卖的最好的70个规格中，有24个规格是属于这个危险区域的，需要采取紧急措施。（2）库存天数大于31天的有211个规格，其中，属于热销规格的有6个，这可以理解是有意的屯货。但是滞销规格有196个，这其中一定有很多是死库存，抓紧时间处理。（3）理想的库存天数可以认为在3天到11天，根据处于这个区域的规格的比例的多少我们可以大致判断分销商库存控制的优劣。25合并透视表：将上述的两个透视表放到一张表中，可以更明显的看到库存结构。26分析具体数据信息：在透视表中，如果想得到详细的数据，只要双击数据区，就会产生相应的一个新的表。例如，想知道库存天数小于3天的24个热销规格，只要双击C12“24”，就会出现如下图所示的新表。五、制作品牌库