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

库存结构分析

---数据透视表操作手册

宝洁公司分销商渠道多功能小组

目录

一、报表准备 (1)

1．下载 (1)

二、报表整理 (1)

2．增加销售数据 (1)

3．输入公式 (1)

4．复制公式 (2)

5．选中数据域 (2)

6．排序 (3)

7．另存新品 (3)

8．输入销量排名 (4)

三、制作库存结构透视表 (4)

9．选中数据区域 (4)

10．进入向导 (4)

11．进入布局界面 (5)

12．布局透视表 (5)

13．调整字段格式 (6)

14．完成布局 (7)

15．调整透视表 (7)

16．组合数据 (8)

17．库存ABC分类 (8)

18．分析 (9)

四、制作库存天数分布透视表 (11)

19．进入布局界面 (11)

20．透视表布局 (11)

21．调整字段格式 (11)

22．组合库存天数 (12)

23．库存天数分段 (13)

24．分析 (13)

25．合并透视表 (14)

26．分析具体数据信息 (14)

五、制作品牌库存分析透视表 (15)

27．参考步骤13 (15)

一、报表准备

1．下载：从DMS下载当月或最新的历史库存明细报表。

二、报表整理

2．增加销售数据：打开报表，在X 1单元格键入“过往2月销售成本”。如下图所示。

3．输入公式：将输入法转换成英文环境，在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．透视表布局：在“布局”界面中，如下图所示，将“销售排名”字段放到“行”区域，将“库存天数”分别放到“列”区域和“数据”区域。