Excel數(shù)據(jù)分析與應(yīng)用-銷售情況整體分析

銷售情況整體分析目

錄項(xiàng)目概述任務(wù)1：銷售額分析任務(wù)2：銷售量分析任務(wù)3：毛利潤分析拓展延伸：銷售成本和利潤分析課后技能訓(xùn)練

項(xiàng)目概述項(xiàng)目背景

“和美家”連鎖超市某分店收集了某周的銷售數(shù)據(jù)，為了掌握店鋪的整體營銷水平，現(xiàn)在需要對銷售情況進(jìn)行整體分析，以便發(fā)現(xiàn)問題，提升店鋪銷售和盈利水平。“和美家”連鎖超市某分店一周整體銷售情況。項(xiàng)目演示“和美家”連鎖超市某分店一周整體銷售情況。項(xiàng)目演示思維導(dǎo)圖思政聚焦—大數(shù)據(jù)團(tuán)隊(duì)—“粵省事”團(tuán)隊(duì)任務(wù)4.1銷售額分析任務(wù)描述對3月22日—3月28日一周內(nèi)每天的銷售額。任務(wù)分析（1）統(tǒng)計(jì)每日銷售額（2）分析銷售額環(huán)比（3）分析銷售額目標(biāo)達(dá)成情況4.1任務(wù)1：銷售額分析1.統(tǒng)計(jì)每日銷售額（1）新建數(shù)據(jù)透視表。（2）設(shè)置數(shù)據(jù)透視表字段。（3）格式化表格。4.1.1銷售額統(tǒng)計(jì)2．繪制每日銷售額柱形圖（1）繪制柱形圖。（2）添加數(shù)據(jù)標(biāo)簽。4.1.1銷售額統(tǒng)計(jì)銷售額環(huán)比：環(huán)比是以某一期的數(shù)據(jù)和上期數(shù)據(jù)進(jìn)行比較所得的趨勢百分比，可以觀察數(shù)據(jù)的增減變化情況，反應(yīng)本期比上期增長了多少。對于成長性較強(qiáng)或業(yè)務(wù)受季節(jié)影響較小的公司，其收入或銷售費(fèi)用的數(shù)據(jù)常常使用環(huán)比指標(biāo)進(jìn)行分析，若A代表本期銷售額，B代表上期銷售額，C代表該商品的環(huán)比增長率，則商品銷售額的環(huán)比公式如式所示。

環(huán)比按采用的基期不同，可分為日環(huán)比、周環(huán)比、月環(huán)比和年環(huán)比。本小節(jié)使用日環(huán)比計(jì)算銷售額環(huán)比，即計(jì)算某天銷售和相對于前一天的環(huán)比。4.1.2銷售額環(huán)比分析1.計(jì)算銷售額環(huán)比。（1）新建銷售額環(huán)比工作表。（2）計(jì)算銷售額環(huán)比。（3）設(shè)置銷售額環(huán)比數(shù)字格式。4.1.2銷售額環(huán)比分析2．繪制銷售額環(huán)比組合圖（1）選擇數(shù)據(jù)區(qū)域。（2）繪制組合圖。4.1.2銷售額環(huán)比分析（3）編輯數(shù)據(jù)源。單擊圖表任意位置，使得圖表處于選中狀態(tài)（四周邊框上出現(xiàn)8個(gè)空心圓圈），在【圖表工具】下的【設(shè)計(jì)】選項(xiàng)卡下，單擊【選擇數(shù)據(jù)】命令，彈出【選擇數(shù)據(jù)源】對話框，如圖所示。4.1.2銷售額環(huán)比分析在【選擇數(shù)據(jù)源】對話框中，單擊【水平(分類)軸標(biāo)簽】的【編輯】按鈕，彈出【軸標(biāo)簽】對話框，在【軸標(biāo)簽區(qū)域】下選擇A2到A8單元格，如圖413所示，單擊【確定】，得到效果如圖所示。4.1.2銷售額環(huán)比分析回到【選擇數(shù)據(jù)源】對話框，在【選擇數(shù)據(jù)源】對話框的【圖例項(xiàng)(系列)】中，選中【銷售日期】，單擊【刪除】，完成后的【選擇數(shù)據(jù)源】對話框及圖表如圖所示。4.1.2銷售額環(huán)比分析（4）調(diào)整數(shù)據(jù)系列配色方案。單擊任意柱形數(shù)據(jù)系列，選中所有柱形圖數(shù)據(jù)系列（每個(gè)數(shù)據(jù)系列的四角都出現(xiàn)空心小圓圈），單擊鼠標(biāo)右鍵，選擇【填充】，單擊【主題顏色】的【藍(lán)色】，可以看到所有柱形數(shù)據(jù)系列填充為藍(lán)色，如圖所示。4.1.2銷售額環(huán)比分析（4）調(diào)整數(shù)據(jù)系列配色方案。單擊任意柱形數(shù)據(jù)系列，選中所有柱形圖數(shù)據(jù)系列（每個(gè)數(shù)據(jù)系列的四角都出現(xiàn)空心小圓圈），單擊鼠標(biāo)右鍵，選擇【填充】，單擊【主題顏色】的【藍(lán)色】，可以看到所有柱形數(shù)據(jù)系列填充為藍(lán)色，如圖所示。4.1.2銷售額環(huán)比分析同樣方法，單擊折線任意位置，選中整條折線（折線上的每個(gè)關(guān)鍵數(shù)據(jù)點(diǎn)都出現(xiàn)空心小圓圈），右鍵單擊，在彈出的快捷菜單中，選擇【輪廓】，單擊【主題顏色】的【橙色】，可以看到折線被填充為橙色，完成后效果如圖所示。4.1.2銷售額環(huán)比分析（5）添加數(shù)據(jù)標(biāo)簽。單擊選中“繪圖區(qū)”，單擊右上角

在【圖表元素】快捷菜單中勾選【數(shù)據(jù)標(biāo)簽】前面的復(fù)選框即可，完成后圖表效果如圖所示。4.1.2銷售額環(huán)比分析（6）設(shè)置銷售額環(huán)比數(shù)據(jù)標(biāo)簽填充色。單擊任意銷售額環(huán)比數(shù)據(jù)標(biāo)簽，可以看到所有的銷售額環(huán)比數(shù)據(jù)標(biāo)簽都處于選中狀態(tài)，右鍵單擊任意數(shù)據(jù)標(biāo)簽，在彈出的快捷菜單中，選擇【填充】，單擊【主題顏色】的【綠色】，可以看到所有銷售額環(huán)比數(shù)據(jù)標(biāo)簽被填充為綠色。4.1.2銷售額環(huán)比分析兩次單擊數(shù)據(jù)標(biāo)簽-35.96%可以看到，只有-35.96%數(shù)據(jù)標(biāo)簽處于選中狀態(tài)（數(shù)據(jù)標(biāo)簽輪廓出現(xiàn)6個(gè)空心圓圈），此時(shí)右鍵單擊選擇【填充】，選擇【紅色】，把它的填充為紅色，同樣操作，將其他兩個(gè)為負(fù)值的數(shù)據(jù)標(biāo)簽背景也設(shè)置為紅色，完成后效果如圖所示。4.1.2銷售額環(huán)比分析（7）設(shè)置圖表標(biāo)題。將圖表標(biāo)題修改為“銷售額環(huán)比”，完成后效果如圖所示。4.1.2銷售額環(huán)比分析該店鋪制定了每日銷售額的基本目標(biāo)為3000和理想目標(biāo)為5000，繪制圖表表達(dá)本周銷售額完成情況。（1）復(fù)制工作表。將【01-1銷售額】工作表復(fù)制一份，重命名為【01-3目標(biāo)達(dá)成情況】，刪除多余數(shù)據(jù)，則【01-3目標(biāo)達(dá)成情況】工作表中內(nèi)容如所示。4.1.3銷售額目標(biāo)達(dá)成情況分析（2）添加數(shù)據(jù)。因?yàn)殇N售額目標(biāo)達(dá)成情況圖表中要添加基本目標(biāo)和理想目標(biāo)兩條線，所以要在數(shù)據(jù)中增加相關(guān)內(nèi)容。在原有數(shù)據(jù)的基礎(chǔ)上增加基本目標(biāo)和理想目標(biāo)兩列，并分別填充數(shù)字3000、5000，完成后效果如圖所示，可以看到添加了兩列數(shù)據(jù)后，圖表也隨之發(fā)生了變化。4.1.3銷售額目標(biāo)達(dá)成情況分析（3）修改圖表類型。為了使得表達(dá)效果更明顯，我們把基本目標(biāo)和理想目標(biāo)兩個(gè)數(shù)據(jù)系列修改為使用折線圖來呈現(xiàn)。單擊選中任意一個(gè)基本目標(biāo)的數(shù)據(jù)系列，可見所有基本目標(biāo)數(shù)據(jù)系列全部選中。在【圖表工具】下的【設(shè)計(jì)】標(biāo)簽下，單擊【更改圖表類型】命令，彈出【更改圖表類型】對話框，將【基本目標(biāo)】和【理想目標(biāo)】的圖表類型修改為【折線圖】，單擊【確定】按鈕后，圖表效果如圖所示。4.1.3銷售額目標(biāo)達(dá)成情況分析（4）去除多余數(shù)據(jù)標(biāo)簽。單擊選中橙色直線，單擊圖表右上角在【圖表元素】快捷菜單中去掉勾選【數(shù)據(jù)標(biāo)簽】，即可去掉橙色線條上的數(shù)據(jù)標(biāo)簽；同樣方法去掉灰色直線上的數(shù)據(jù)標(biāo)簽。完成后如圖所示。4.1.3銷售額目標(biāo)達(dá)成情況分析（5）更改折線顏色，添加圖表標(biāo)題。右鍵單擊灰色直線，在彈出的快捷菜單中，選擇【輪廓】，單擊【主題顏色】中的【綠色】，可以看到直線變?yōu)榫G色。單擊圖表區(qū)任意位置，圖表處于選中狀態(tài)，然后單擊圖表右上角

在【圖表元素】快捷菜單中勾選【圖表標(biāo)題】，可以看到Excel為圖表添加了默認(rèn)標(biāo)題“圖表標(biāo)題”，將圖表標(biāo)題修改為“銷售目標(biāo)達(dá)成情況”，完成后如圖所示。4.1.3銷售額目標(biāo)達(dá)成情況分析任務(wù)4.2銷售量分析任務(wù)描述計(jì)算3月22日—3月28日一周內(nèi)每天的銷售量，并使用柱形圖形象化表達(dá)。任務(wù)分析（1）統(tǒng)計(jì)每日銷售量（2）對比分析促銷和非促銷的銷售量4.2

任務(wù)2：銷售量分析因?yàn)槌猩唐贩N類繁多，所以每種商品的單位不盡相同，查看數(shù)據(jù)可知，日配、酒飲、洗化、等類別商品的單位通常為瓶、袋、個(gè)等，數(shù)量均為整數(shù)，而蔬果、肉禽等類別商品的單位通常為千克，數(shù)量多為小數(shù)，為了便于計(jì)算，統(tǒng)計(jì)時(shí)不考慮商品單位。1.統(tǒng)計(jì)每日銷售量統(tǒng)計(jì)銷售量的方法和統(tǒng)計(jì)銷售額方法基本相同。完成后效果如右圖所示。4.2.1銷售量統(tǒng)計(jì)2．繪制每日銷售量柱形圖選擇上個(gè)步驟得到的數(shù)據(jù)，按照4.1.1節(jié)繪制每日銷售額柱形圖方法，完成本節(jié)圖形繪制，效果如圖所示。4.2.1銷售量統(tǒng)計(jì)通過促銷和非促銷商品銷售量的對比，初步掌握促銷在商品銷售中所起的作用。

1.統(tǒng)計(jì)促銷和非促銷商品每日銷量（1）新建數(shù)據(jù)透視表。（2）設(shè)置數(shù)據(jù)透視表字段。（3）格式化表格。（4）設(shè)置數(shù)字格式。4.2.2促銷與非促銷銷售量對比分析2.繪制每日促銷與非促銷商品售量圖表。（1）繪制堆積柱形圖。4.2.2促銷與非促銷銷售量對比分析（2）添加數(shù)據(jù)標(biāo)簽并設(shè)置數(shù)據(jù)格式。單擊選中“繪圖區(qū)”，單擊右上角

，在【圖表元素】快捷菜單中單擊【數(shù)據(jù)標(biāo)簽】命令，可見【數(shù)據(jù)標(biāo)簽】命令前面的復(fù)選框處于選中狀態(tài)，【數(shù)據(jù)標(biāo)簽】命令后出現(xiàn)向左小三角。單擊這個(gè)小三角，選擇【更多選項(xiàng)】，彈出【設(shè)計(jì)數(shù)據(jù)標(biāo)簽格式】對話框。在【設(shè)計(jì)數(shù)據(jù)標(biāo)簽格式】對話框的【標(biāo)簽選項(xiàng)】選項(xiàng)卡中，單擊【數(shù)字】，在【類別】下選擇【數(shù)字】，然后把小數(shù)位數(shù)調(diào)整為“0”，如圖所示，可以看到，默認(rèn)選中的以藍(lán)色柱形表示的非促銷數(shù)據(jù)被修改成了整數(shù)。4.2.2促銷與非促銷銷售量對比分析（3）更改圖表標(biāo)題。將圖表標(biāo)題修改為“促銷與非促銷銷售量對比”，完成后如圖所示。4.2.2促銷與非促銷銷售量對比分析任務(wù)4.3毛利潤分析任務(wù)描述分析3月22日—3月28日一周內(nèi)每天的毛利潤。任務(wù)分析（1）統(tǒng)計(jì)每日毛利潤（2）進(jìn)行銷售額、銷售量、毛利潤組合分析4.3任務(wù)3：毛利潤分析知識準(zhǔn)備：毛利潤是企業(yè)商品銷售收入減去商品原進(jìn)價(jià)后的余額，又稱商品進(jìn)銷差價(jià)。因其尚未減去商品流通費(fèi)和稅金，還不是凈利潤，故稱毛利潤。

毛利潤=銷售金額-成本金額任務(wù)分析4.3.1毛利潤統(tǒng)計(jì)1.統(tǒng)計(jì)每日毛利潤（1）新建數(shù)據(jù)透視表。（2）設(shè)置數(shù)據(jù)透視表字段。(3)格式化表格。4.3.1毛利潤統(tǒng)計(jì)2．繪制每日毛利潤折線圖（1）繪制折線圖。4.3.1毛利潤統(tǒng)計(jì)（2）添加數(shù)據(jù)標(biāo)簽。（3）將折線變?yōu)槠交€。右鍵單擊折線，選擇【設(shè)置數(shù)據(jù)系列格式】命令，彈出【設(shè)置數(shù)據(jù)系列格式】對話框，選擇【填充與線條】選項(xiàng)卡，選擇【線條】，在面板最末端選中【平滑線】選線前面的復(fù)選框，如圖所示，將折線設(shè)置為平滑的曲線。4.3.1毛利潤統(tǒng)計(jì)（4）添加數(shù)據(jù)點(diǎn)標(biāo)記。在【填充與線條】選項(xiàng)卡中，切換到【標(biāo)記】，單擊【數(shù)據(jù)標(biāo)記選項(xiàng)】，選中【內(nèi)置】，為數(shù)據(jù)點(diǎn)添加標(biāo)記，效果如圖所示。4.3.1毛利潤統(tǒng)計(jì)1.統(tǒng)計(jì)每日銷售額、銷售量、毛利潤新建工作表【03-2銷售額-銷售量-毛利潤對比】，從【01-1銷售額】、【02-1銷售量】、【03-1毛利潤】，三個(gè)工作表之中拷貝所需數(shù)據(jù)，修改行高為20，完成后數(shù)據(jù)表如圖所示。4.3.2銷售額、銷售量、毛利潤組合分析2.繪制銷售額、銷售量、毛利潤組合分析圖表。(1)繪制簇狀柱形圖。4.3.2銷售額、銷售量、毛利潤組合分析（2）更改圖表類型。選中圖表，在【圖表工具】下的【設(shè)計(jì)】標(biāo)簽下，單擊【更改圖表類型】命令，彈出【更改圖表類型】對話框，選擇【所有圖表】選項(xiàng)卡，選擇【組合】，將【銷售數(shù)量】更改為【折線圖】，并勾選【次坐標(biāo)軸】復(fù)選框，將【毛利潤】更改為【折線圖】，如圖所示，單擊【確定】按鈕。4.3.2銷售額、銷售量、毛利潤組合分析（3）修改圖表標(biāo)題。在上個(gè)步驟基礎(chǔ)上，將圖表標(biāo)題修改為“銷售額、銷售量、毛利潤對比”，完成后圖表效果如圖所示。4.3.2銷售額、銷售量、毛利潤組合分析拓展延伸銷售成本和利潤分析任務(wù)分析“和美家”超市某分店老板給出了店鋪經(jīng)營的一些基本成本數(shù)據(jù)，希望結(jié)合銷售情況，分析得到本周最終利潤，并繪制精美圖表展示。知識準(zhǔn)備瀑布圖也可以稱為階梯圖或橋圖，由麥肯錫顧問公司獨(dú)創(chuàng)，因?yàn)樾嗡破俨剂魉妹?。瀑布圖具有自上而下的流暢效果，在企業(yè)經(jīng)營分析、財(cái)務(wù)分析中使用較多，用以表示企業(yè)成本的構(gòu)成、變化等情況。4.4拓展延伸：銷售成本和利潤分析1.統(tǒng)計(jì)銷售成本和利潤數(shù)據(jù)（1）計(jì)算每周成本。打開【每月固定成本】工作簿，增加“每周成本”列，單擊C2單元格，輸入公式“=B2/4”，按【Enter】鍵，得到人員工資的每周成本，使用快速計(jì)算方法得到其他項(xiàng)目的每周成本。這里簡單的用每月成本除以4得到每周成本。完成后效果如圖所示。4.4拓展延伸：銷售成本和利潤分析（2）新建工作表，填充已有數(shù)據(jù)。新建【拓展】工作表，創(chuàng)建產(chǎn)品銷售收入、銷售成本、人員工資、房租費(fèi)用、稅費(fèi)、水電等雜費(fèi)幾個(gè)行標(biāo)題。將上個(gè)步驟計(jì)算得到的每周成本對應(yīng)的數(shù)值粘貼到【拓展】工作表的對應(yīng)列。（3）引用銷售額總和。打開【拓展】工作表，單擊B2單元格，輸入“=”，單擊“01-1銷售額”工作表標(biāo)簽，跳轉(zhuǎn)到“01-1銷售額”工作表中，單擊B11單元格（本周銷售額總和），單擊【Enter】，此時(shí)可見B2單元格的數(shù)值為25703.75，單擊選中B2單元格，可見公式“=GETPIVOTDATA("銷售金額",'01-1銷售額'!$A$3)”。（4）計(jì)算銷售成本。銷售成本=銷售額總和-毛利潤總和，在【拓展】工作表中，單擊B3單元格，輸入“=”，單擊【03-1毛利潤】工作表標(biāo)簽，跳轉(zhuǎn)到【03-1毛利潤】工作表中，單擊B11單元格（本周毛利潤總和），單擊【Enter】，此時(shí)可見B3單元格的數(shù)值為11397.62，單擊選中B3單元格，可見公式“=GETPIVOTDATA("毛利潤",'03-1毛利潤'!$A$3)”。修改公式為“=B2-GETPIVOTDATA("毛利潤",'03-1毛利潤'!$A$3)”，得到銷售成本為14306.13。（5）設(shè)置數(shù)據(jù)格式。選中B2:B3單元格，設(shè)置單元格格式為顯示整數(shù)，完成后如4-49所示。（6）已有數(shù)據(jù)處理，計(jì)算最終利潤。在銷售成本、人員工資、房租費(fèi)用、稅費(fèi)、水電等雜費(fèi)五項(xiàng)數(shù)據(jù)前添加“-”號，變成與原來數(shù)字絕對值相等的負(fù)數(shù)。單擊C2單元格，輸入公式“=SUM(B2:B7)”，按【Enter】鍵，得到最終利潤是5663.08。4.4拓展延伸：銷售成本和利潤分析（7）修正最終利潤數(shù)據(jù)，格式化表格。這里要注意瀑布圖的數(shù)據(jù)結(jié)構(gòu)，除了產(chǎn)品銷售收入為正值外，其他數(shù)據(jù)結(jié)構(gòu)都是負(fù)值，所有我們也將最終利潤改成負(fù)值。格式化表格，完成后銷售成本和利潤數(shù)據(jù)如下圖4-50所示。4.4拓展延伸：銷售成本和利潤分析2.繪制銷售成本和利潤分析瀑布圖（1）創(chuàng)建瀑布圖。選中所示數(shù)據(jù)，在【插入】選項(xiàng)卡中的【圖表】命令組中，單擊查看所有圖表的命令，彈出【插入圖表】對話框，切換到【所有圖表】選項(xiàng)卡中，選擇【瀑布圖】，單擊【確定】，適當(dāng)將圖表調(diào)大，得到的圖表如圖所示。4.4拓展延伸：銷售成本和利潤分析（2）設(shè)置最終利潤數(shù)據(jù)系列格式。將最終利潤數(shù)據(jù)系列的填充和輪廓都改為綠色。刪除數(shù)據(jù)系列標(biāo)簽-6478。在【插入】選項(xiàng)卡之中的【文本】命令組中，單擊【文本框】命令。在Excel工作表的任意空白位置單擊，出現(xiàn)一個(gè)文本框，在文本框之中輸入數(shù)字6478，將文本框中的數(shù)字設(shè)置為白色、加粗、14號字，并且將文本框移動(dòng)到最終利潤數(shù)據(jù)系列之上，完成后如圖所示。4.4拓展延伸：銷售成本和利潤分析（3）設(shè)置圖表區(qū)背景及標(biāo)題。單擊選中圖表區(qū)域任意位置，利用右鍵的快捷菜單的【