《Excel數(shù)據(jù)分析與應用》課件-項目4 銷售情況整體分析

主講：馬元元西安電子科技大學出版社項目4銷售情況整體分析目

錄項目概述任務1：銷售額分析任務2：銷售量分析任務3：毛利潤分析拓展延伸：銷售成本和利潤分析課后技能訓練西安電子科技大學出版社

項目概述西安電子科技大學出版社西安電子科技大學出版社項目背景

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

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

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

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

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

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

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

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