《Excel數(shù)據(jù)分析基礎(chǔ)與實(shí)戰(zhàn)》第9章 分析商品庫存

分析商品庫存21-12月-231分析庫存的商品類別占比目錄分析庫存的存銷比2存銷比是指在一個(gè)周期內(nèi)，期末庫存與周期內(nèi)總銷售的比值。存銷比的意義在于：它揭示了一個(gè)單位的銷售額需要多少個(gè)單位的庫存來支持。存銷比過高意味著庫存總量或銷售結(jié)構(gòu)不合理，資金效率低；存銷比過低意味著庫存不足，生意難以最大化。存銷比還是反映庫存周轉(zhuǎn)率的一個(gè)常用的指標(biāo)，越是暢銷的商品，其存銷比值越小，說明商品的庫存周轉(zhuǎn)率越高；越是滯銷的商品，其存銷比值就越大，說明商品的庫存周轉(zhuǎn)率越低。存銷比的計(jì)算公式如下。計(jì)算存銷比在【庫存數(shù)據(jù)】工作表中，通過透視表的方式計(jì)算2018年8月27日的庫存數(shù)量，具體操作步驟如下。（1）打開【創(chuàng)建數(shù)據(jù)透視表】對(duì)話框。打開【庫存數(shù)據(jù)】工作表，單擊數(shù)據(jù)區(qū)域內(nèi)任一單元格，在【插入】選項(xiàng)卡的【表格】命令組中，單擊【數(shù)據(jù)透視表】圖標(biāo)，彈出【創(chuàng)建數(shù)據(jù)透視表】對(duì)話框，如圖所示。計(jì)算存銷比1.計(jì)算庫存數(shù)量在【庫存數(shù)據(jù)】工作表中，計(jì)算存銷比，需要計(jì)算庫存數(shù)量和銷售數(shù)量，具體操作步驟如下。（2）創(chuàng)建空白數(shù)據(jù)透視表。單擊【確定】按鈕，將創(chuàng)建一個(gè)空白數(shù)據(jù)透視表，并顯示【數(shù)據(jù)透視表字段】窗格，如圖所示。計(jì)算存銷比（3）添加“日期”“商品類別”和“庫存數(shù)量”字段。將“日期”字段和“商品類別”字段拖曳至【行】區(qū)域，“庫存數(shù)量”字段拖曳至【值】區(qū)域，如左圖所示，創(chuàng)建的數(shù)據(jù)透視表計(jì)算結(jié)果如右圖所示。計(jì)算存銷比（4）計(jì)算2018年8月27日各類商品的庫存數(shù)量。單擊單元格A1的倒三角符號(hào)，在彈出【選擇字段：】對(duì)話框中選擇“2018/8/27”，如左圖所示，單擊【確定】按鈕，所得結(jié)果即為2018年8月27日各類商品的庫存數(shù)量，如右圖所示。計(jì)算存銷比在【訂單數(shù)據(jù)】工作表中，通過透視表的方式計(jì)算銷售數(shù)量，具體的步驟如下。（1）打開【創(chuàng)建數(shù)據(jù)透視表】對(duì)話框。打開【訂單數(shù)據(jù)】工作表，單擊數(shù)據(jù)區(qū)域內(nèi)任一單元格，在【插入】選項(xiàng)卡的【表格】命令組中，單擊【數(shù)據(jù)透視表】圖標(biāo)，彈出【創(chuàng)建數(shù)據(jù)透視表】對(duì)話框，單擊【確定】按鈕，即可創(chuàng)建一個(gè)空白數(shù)據(jù)透視表。計(jì)算存銷比2.計(jì)算銷售數(shù)量（2）求各商品類別的銷售數(shù)量。將“商品類別”字段拖曳至【行】區(qū)域，“購(gòu)買數(shù)量.個(gè).”字段拖曳至【值】區(qū)域，如左圖所示，創(chuàng)建的數(shù)據(jù)透視表，并將數(shù)據(jù)透視表的字段重命名為“商品類別”和“銷售數(shù)量”，結(jié)果如右圖所示。計(jì)算存銷比通過庫存數(shù)量與銷售數(shù)量計(jì)算存銷比，具體的步驟如下。（1）創(chuàng)建【存銷比】工作表。創(chuàng)建新的工作表并重命名為【存銷比】，將計(jì)算庫存數(shù)量的結(jié)果和將計(jì)算銷售數(shù)量的結(jié)果復(fù)制到【存銷比】工作表中，將“庫存數(shù)量”字段改為“期末庫存數(shù)量”，并在單元格D1的位置添加“存銷比”輔助字段，如圖所示。計(jì)算存銷比3.計(jì)算存銷比（2）計(jì)算存銷比。在單元格D2輸入“=B2/C2”，按Enter按鍵即可計(jì)算餅干類的存銷比，將鼠標(biāo)指針移到D2的右下角，當(dāng)指針變?yōu)楹谏哟值摹?”指針時(shí)，雙擊左鍵即可計(jì)算其他商品類別的存銷比，如圖所示。計(jì)算存銷比（3）設(shè)置單元格格式。右鍵單擊存銷比計(jì)算結(jié)果的單元格區(qū)域D2:D17，在彈出的快捷菜單中選擇【設(shè)置單元格格式】命令，在【設(shè)置單元格格式】對(duì)話框中，選擇【分類】列表框下的【百分比】選項(xiàng)，并將【小數(shù)位數(shù)】設(shè)為2，如左圖所示，單擊【確定】按鈕，設(shè)置單元格格式后的結(jié)果如右圖所示。計(jì)算存銷比基于存銷比單元格格式更改結(jié)果的數(shù)據(jù)，繪制簇狀柱形圖和折線圖，具體操作步驟如下。（1）打開【插入圖表】對(duì)話框。在存銷比單元格格式更改結(jié)果所示的工作表中選擇單元格區(qū)域A1:D17，在【插入】選項(xiàng)卡的【圖表】命令組中，單擊按鈕，彈出【插入圖表】對(duì)話框，如圖所示。繪制簇狀柱形圖和折線圖分析庫存的存銷比（2）選擇繪圖類型。切換至【插入圖表】對(duì)話框所示的【所有圖表】選項(xiàng)卡，單擊【組合圖】選項(xiàng)，選擇【簇狀柱形圖-次坐標(biāo)軸上的折線圖】，設(shè)置次坐標(biāo)軸為存銷比，如圖所示。繪制簇狀柱形圖和折線圖分析庫存的存銷比（3）繪制組合圖。單擊【確定】按鈕，即可繪制組合圖，如圖所示。繪制簇狀柱形圖和折線圖分析庫存的存銷比（4）修改圖表元素。修改繪制組合圖所示的圖表元素，具體步驟如下。單擊【圖表標(biāo)題】文本激活圖表標(biāo)題文本框，更改圖表標(biāo)題為“各商品類別的期末庫存數(shù)量、銷售數(shù)量和存銷比”。選中組合圖，單擊組合圖右邊的按鈕，在彈出的快捷菜單中勾選【坐標(biāo)軸標(biāo)題】，將橫坐標(biāo)標(biāo)題改為“商品類別”，縱坐標(biāo)標(biāo)題分別改為“數(shù)量”和“存銷比”。圖表標(biāo)題、坐標(biāo)軸標(biāo)題的字體改為宋體，圖表標(biāo)題、坐標(biāo)軸標(biāo)題及其標(biāo)簽、圖例的字體顏色改為黑色。將圖例移至右上角。繪制簇狀柱形圖和折線圖分析庫存的存銷比由圖可知，糖果甜食和膨化食品的存銷比較高，而庫存數(shù)量和銷售數(shù)量卻相對(duì)較低；飲料的存銷比最低，且其庫存數(shù)量和銷售數(shù)量最大，因此在一定程度上可說明飲料類商品屬于暢銷的商品。繪制簇狀柱形圖和折線圖分析庫存的存銷比1分析庫存的商品類別占比目錄分析庫存的存銷比2在現(xiàn)實(shí)生活中運(yùn)用到占比指標(biāo)的領(lǐng)域非常廣泛，如股份占比、地區(qū)占比、庫存占比等。占比的意義都是在于計(jì)算某個(gè)個(gè)體數(shù)在總數(shù)中所占的比重?？偟膩碚f，占比是指目標(biāo)個(gè)數(shù)占總數(shù)的比例，其計(jì)算公式如下。計(jì)算庫存的商品類別占比

在【庫存數(shù)據(jù)】工作表中，通過透視表的方式計(jì)算各類商品的庫存數(shù)量，具體操作步驟如下。（1）對(duì)“商品類別”字段進(jìn)行降序。打開【庫存數(shù)據(jù)】工作表，單擊“商品類別”字段的數(shù)據(jù)區(qū)域內(nèi)任一單元格，在【開始】選項(xiàng)卡的【編輯】命令組中，單擊【排序和篩選】圖標(biāo)，選擇【降序】命令。（2）創(chuàng)建分類匯總。單擊數(shù)據(jù)區(qū)域內(nèi)任一單元格，在【數(shù)據(jù)】選項(xiàng)卡的【分級(jí)顯示】命令組中，單擊【分類匯總】圖標(biāo)，彈出【分類匯總】對(duì)話框，如圖所示。計(jì)算庫存的商品類別占比1.計(jì)算庫存數(shù)量在【庫存數(shù)據(jù)】工作表中，計(jì)算商品類別的占比，需要計(jì)算各商品類別的庫存數(shù)量，具體步驟如下。（3）選擇“商品類別”和“庫存數(shù)量”字段。在【分類匯總】對(duì)話框中的【分類字段】下拉框中選擇“商品類別”，【選定匯總項(xiàng)】列表框中選擇“庫存數(shù)量”，如圖所示。計(jì)算庫存的商品類別占比（4）計(jì)算商品類別的庫存數(shù)量。單擊【確定】按鈕，即可計(jì)算出商品類別的庫存數(shù)量，如左圖所示，單擊左圖左上方的數(shù)字2，即可計(jì)算商品類別的庫存數(shù)量，如右圖所示。計(jì)算庫存的商品類別占比計(jì)算各商品類別的庫存數(shù)量占比，需要通過各商品類別庫存數(shù)量與總庫存數(shù)量進(jìn)行計(jì)算，具體操作步驟如下。（1）添加“占比”輔助字段。在單元格E1添加“占比”輔助字段，如圖所示。計(jì)算庫存的商品類別占比2.計(jì)算占比（2）設(shè)置單元格格式。右鍵單擊添加“占比”輔助字圖所示的單元格區(qū)域E18:E1741，在彈出的快捷菜單中選擇【設(shè)置單元格格式】命令，在【設(shè)置單元格格式】對(duì)話框中，選擇【分類】列表框下的【百分比】選項(xiàng)，并將【小數(shù)位數(shù)】設(shè)為2，單擊【確定】按鈕。計(jì)算庫存的商品類別占比（3）計(jì)算各商品類別的庫存數(shù)量占比。在添加“占比”輔助字圖所示的單元格E18中輸入“=D18/$D$1736”，按Enter按鍵即可計(jì)算紙巾類商品的占比，將鼠標(biāo)指針移到單元格E18的右下角，當(dāng)指針變?yōu)楹谏哟值摹?”指針時(shí)，雙擊左鍵即可計(jì)算其他商品類比的庫存數(shù)量占比，如圖所示。注：由于設(shè)置了小數(shù)位數(shù)，所以全部商品類別的占比總和會(huì)產(chǎn)生一定的誤差，如此處的全部商品類別的占比總和為99.99%。計(jì)算庫存的商品類別占比基于計(jì)算各商品類別的庫存數(shù)量占比所示的數(shù)據(jù)，繪制庫存商品類別占比的餅圖，具體步驟如下。（1）選擇數(shù)據(jù)。選擇計(jì)算各商品類別的庫存數(shù)量占比中“商品類別”字段的單元格區(qū)域C1:C1735和“占比”字段的單元格區(qū)域E1:E1735。（2）繪制餅圖。在【插入】選項(xiàng)卡的【圖表】命令組中單擊按鈕，彈出【插入圖表】對(duì)話框，切換至【所有圖表】選項(xiàng)卡，選擇【餅圖】選項(xiàng)，單擊【確定】按鈕，即可繪制餅圖，如圖所示。繪制餅圖分析庫存的商品類別占比（3）修改圖表元素。修改繪制餅圖的圖表元素，具體步驟如下。單擊【占比】文本激活圖表標(biāo)題文本框，更改圖表標(biāo)題為“各商品類別的庫存數(shù)量”。選中餅圖，單擊餅圖右邊的按鈕，在彈出的快捷菜單中選擇【添加數(shù)據(jù)標(biāo)簽】命令，即可添加數(shù)據(jù)標(biāo)簽。圖表標(biāo)題的字體改為宋體，圖表標(biāo)題、數(shù)據(jù)標(biāo)簽和圖例的字體顏色改為黑色。將圖例移至