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

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

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