1、庫存結構分析 -數據透視表操作手冊寶潔公司分銷商渠道多功能小組目錄一、報表準備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參考步驟13

2、15一、報表準備1下載：從DMS下載當月或最新的歷史庫存明細報表。二、報表整理2增加銷售數據：打開報表，在X 1單元格鍵入“過往2月銷售成本”。如下圖所示。03輸入公式：將輸入法轉換成英文環境，在X 2單元格鍵入公式“=w2*61/u2”（如下圖），回車。注意保證所有輸入內容包括“=”一定都是英文格式。 *公式：庫存天數=庫存金額*61/過往2月銷售成本； >> 過往2月銷售成本=庫存金額*61/庫存天數。4復制公式：選中X2單元格，將鼠標箭頭放在X2單元格右下端，待出現黑色“+”，雙擊鼠標。將X2單元格的公式復制到整列。5選中數據域：選中第2行，按住“Ctrl”鍵和“Shift”

3、鍵的同時，按方向鍵中的“向下鍵”，從而將除第一行的數據選中。從操作菜單中找到“數據”“排序”。6排序：在排序窗口中，主要關鍵字中選“按列X”，并選中“降序”。7另存新品：排序后，將所有X 列單元格中數據為“#DIV/0!”的行選定，復制到另一張sheet中，這些多數應該是當月新品，前2個月沒有銷售。同時在原報表中點擊右鍵，將這些數據刪除，如下圖所示。8輸入銷量排名：在Y1單元格中鍵入“銷售排名”，在Y2中鍵入“1”，然后按住“Ctrl”鍵，向下拖動鼠標，將所有數據按過往2月銷售成本排名次。三、制作庫存結構透視表9選中數據區域：選中A1單元格，同時按住“Ctrl”鍵和“Shift”鍵，依次按方向

4、鍵中的向下鍵和向右鍵，從而選中整個數據區域。10進入向導：在操作菜單中進入“數據”“數據透視表和數據透視圖”。11進入布局界面：連續點擊兩個“下一步”，進入下圖所示環境，選中“布局”。12布局透視表：如下圖所示，在“布局”界面中，在右邊的字段中找到“銷售排名”，拖到“行”區域中，將“過往2月銷售成本”字段和“庫存金額”字段放到“數據”區域。13調整字段格式：雙擊“求和項：過往2月銷售成本”按鈕，出現“數據透視表字段”菜單。在這個菜單中點擊“選項”按鈕，下方出現“數據顯示方式”對話框。在下拉菜單中選中“占總和的百分比”。同時，為了更清晰起見，可以將名稱改為“銷售比例”，點擊“確定”。同樣方法，雙

5、擊“求和項：庫存金額”，在“數據透視表字段”菜單中點擊“選項”按鈕，在“數據顯示方式”下拉菜單中選中“按總和的百分比”。并把上端的名稱改為“庫存比例”，點擊“確定”，回到“布局”界面。14完成布局：在“布局”界面中點擊“確定”，回到數據透視表向導界面，點擊“完成”。15調整透視表：在生成的數據透視表中，用鼠標點中 B3，也就是“數據”所在單元格，向右拖至C3，也就是“匯總”所在單元格。這樣，透視表將從上面的視圖變成如下圖所示的界面。16組合數據：選中A4，即“銷售排名”單元格，點擊右鍵。如下圖所示，選中“組及顯示明細數據”“組合”，彈出分組對話框。17庫存ABC分類：在“分組”對話框中，“起始

6、于”欄中填“71”，在“終止于”欄中填“210”，“依據”欄填“140”，點擊確定。分組之后，可以得到如下的數據透視表。18分析：根據這個透視表，我們可以分析各種規格的庫存的合理性。例如在賣的最多的70個規格銷售比例約為60%，但是庫存比例只有40%，而賣的最少的那些規格銷售比例只有15%，庫存比例卻達到了39%，這說明滯銷庫存還是挺多的。同時如果想得到具體的信息，例如想知道前70個 SKU具體是哪些，可以雙擊相應的單元格“<71”。雙擊之后出現“顯示明細數據”對話框，選擇其中的“商品名稱”。則會出現如下視圖：以上的數據透視表可以對庫存結構的整體情況有個大致的了解，具體的分析問題還需要更

7、準確的看到所有庫存規格的庫存天數的分布。四、制作庫存天數分布透視表19進入布局界面：點回到sheet1，此時整個數據區域應該還處于選中狀態。按照同樣的方法，選中操作菜單中的“數據”“數據透視表和數據透視圖”。依次點擊“下一步”“下一步”“是（Y）”“下一步”“布局”。20透視表布局：在“布局”界面中，如下圖所示，將“銷售排名”字段放到“行”區域，將“庫存天數”分別放到“列”區域和“數據”區域。21調整字段格式：雙擊數據區域的“求和項：庫存天數”，在“數據透視表”對話框中的“匯總方式”的列表中選擇“計數”，“確定”“確定”“完成”“確定”。22組合庫存天數：如圖，在得到的數據透視表中，右鍵點擊B

8、3，即“庫存天數”所在單元格。選擇“組及選擇明細數據”“組合”。23庫存天數分段：在彈出的“組合”對話框中，“起始于”填“3”，“終止于”填“31”，“依據”填“4”，確定。得到庫存天數分布表。24分析：在這個透視表中，我們可以看到以下幾個問題。（1）庫存天數小于3天的，有114個規格，這些非常有可能造成缺貨，尤其是賣的最好的70個規格中，有24個規格是屬于這個危險區域的，需要采取緊急措施。（2）庫存天數大于31天的有211個規格，其中，屬于熱銷規格的有6個，這可以理解是有意的屯貨。但是滯銷規格有196個，這其中一定有很多是死庫存，抓緊時間處理。（3）理想的庫存天數可以認為在3天到11天，根據處于這個區域的規格的比例的多少我們可以大致判斷分銷商庫存控制的優劣。25合并透視表：將上述的兩個透視表放到一張表中，可以更明顯的看到庫存結構。26分析具體數據信息：在透視表中，如果想得到詳細的數據，只要雙擊數據區，就會產生相應的一個新的表。例如，想知道庫存天數小于3天的24個熱銷規格，只要雙擊C12“24”，就會出現如下圖所示的新表。五、制作品牌庫