《Excel數(shù)據(jù)分析與應(yīng)用》課件-項(xiàng)目5 商品分析

主講：馬元元西安電子科技大學(xué)出版社項(xiàng)目5

商品分析目

錄項(xiàng)目概述任務(wù)1：商品銷(xiāo)售額分析任務(wù)2：商品銷(xiāo)售量分析任務(wù)3：商品毛利潤(rùn)分析任務(wù)4：商品促銷(xiāo)情況分析任務(wù)5：工作日和周末商品銷(xiāo)售額對(duì)比任務(wù)6：商品毛利潤(rùn)詳細(xì)分析拓展延伸—預(yù)測(cè)商品銷(xiāo)售量課后技能訓(xùn)練西安電子科技大學(xué)出版社

項(xiàng)目概述西安電子科技大學(xué)出版社西安電子科技大學(xué)出版社項(xiàng)目背景

“和美家”連鎖超市某分店收集了某周的銷(xiāo)售數(shù)據(jù)，現(xiàn)在想對(duì)每種大類(lèi)商品的銷(xiāo)售情況做統(tǒng)計(jì)與對(duì)比分析，以便發(fā)現(xiàn)銷(xiāo)售規(guī)律，提升店鋪銷(xiāo)售和盈利水平。西安電子科技大學(xué)出版社“和美家”連鎖超市某分店一周各大類(lèi)商品銷(xiāo)售情況。項(xiàng)目演示西安電子科技大學(xué)出版社“和美家”連鎖超市某分店一周各大類(lèi)商品銷(xiāo)售情況。項(xiàng)目演示西安電子科技大學(xué)出版社“和美家”連鎖超市某分店一周各大類(lèi)商品銷(xiāo)售情況。項(xiàng)目演示思維導(dǎo)圖西安電子科技大學(xué)出版社思政聚焦—大數(shù)據(jù)抗疫專(zhuān)家—賈曉豐/2020/0909/c4670a172758/page.htm西安電子科技大學(xué)出版社思政聚焦—大數(shù)據(jù)抗疫專(zhuān)家—賈曉豐學(xué)習(xí)賈曉豐同志的抗疫工作，深厚的技術(shù)功底、對(duì)行業(yè)深入的認(rèn)知、對(duì)祖國(guó)、對(duì)人民強(qiáng)烈的責(zé)任感和熱愛(ài)，以及在壓力面前勇于擔(dān)當(dāng)、勇于探索的精神值得我們每一個(gè)大數(shù)據(jù)人學(xué)習(xí)。思考與討論：你還知道哪些大數(shù)據(jù)工作者以及他們的事跡嗎？他們的身上表現(xiàn)出哪些優(yōu)秀的品質(zhì)和精神值得我們學(xué)習(xí)呢？西安電子科技大學(xué)出版社任務(wù)5.1商品銷(xiāo)售額分析西安電子科技大學(xué)出版社任務(wù)描述對(duì)3月22日—3月28日一周內(nèi)各大類(lèi)商品的銷(xiāo)售額進(jìn)行分析。任務(wù)分析（1）商品銷(xiāo)售額排行分析（2）商品銷(xiāo)售額占比分析任務(wù)5.1：商品銷(xiāo)售額分析西安電子科技大學(xué)出版社1.統(tǒng)計(jì)每種大類(lèi)商品的銷(xiāo)售額。（1）新建數(shù)據(jù)透視表。（2）各大類(lèi)銷(xiāo)售額排序。（3）格式化表格。5.1.1商品銷(xiāo)售額排行分析西安電子科技大學(xué)出版社2．繪制各大類(lèi)商品銷(xiāo)售額排行條形圖。（1）繪制柱形圖。（2）添加數(shù)據(jù)標(biāo)簽。5.1.1商品銷(xiāo)售額排行分析西安電子科技大學(xué)出版社本節(jié)要繪制各大類(lèi)商品銷(xiāo)售額占比餅圖，利用5.1.1節(jié)統(tǒng)計(jì)得到的數(shù)據(jù)即可。（1）繪制復(fù)合餅圖。（2）添加數(shù)據(jù)標(biāo)簽。（3）套用圖表樣式。5.1.2商品銷(xiāo)售額占比分析西安電子科技大學(xué)出版社（3）套用圖表樣式。（4）個(gè)性化美化圖表，修改圖表標(biāo)題。在周銷(xiāo)售額占比中，日配占比最多占23%，其次是蔬果占21%，休閑和糧油都占14%，洗化11%，酒飲9%，肉禽和熟食占比很低，分別為6%和2%。5.1.2商品銷(xiāo)售額占比分析西安電子科技大學(xué)出版社任務(wù)5.2商品銷(xiāo)售量分析西安電子科技大學(xué)出版社任務(wù)描述對(duì)3月22日—3月28日一周內(nèi)各大類(lèi)商品的銷(xiāo)售量進(jìn)行分析，要求有數(shù)據(jù)有圖表。任務(wù)分析（1）商品銷(xiāo)售量排行分析（2）商品銷(xiāo)售量占比分析任務(wù)5.2：商品銷(xiāo)售量分析西安電子科技大學(xué)出版社統(tǒng)計(jì)商品銷(xiāo)售量排行的方法和5.1.1節(jié)統(tǒng)計(jì)商品銷(xiāo)售額排行的步驟大致相同，不同的是在新建數(shù)據(jù)透視表之后，在【數(shù)據(jù)透視表字段設(shè)置】面板中，將【大類(lèi)名稱(chēng)】拖動(dòng)到【行】位置，將【銷(xiāo)售數(shù)量】拖動(dòng)到【值】位置。5.2.1商品銷(xiāo)售量占比排行分析西安電子科技大學(xué)出版社使用圖5-12所示數(shù)據(jù)，使用5.1.2節(jié)所示方法繪制復(fù)合餅圖如圖所示。商品銷(xiāo)售額占比最大的是日配類(lèi)，占到35%，其次是蔬果和糧油類(lèi)，分別占19%和14%，這三類(lèi)一共占到68%。接下來(lái)是休閑和酒飲占12%和10%，洗化類(lèi)占6%，熟食和肉禽都只占2%。5.2.2商品銷(xiāo)售量占比分析西安電子科技大學(xué)出版社任務(wù)5.3商品毛利潤(rùn)分析西安電子科技大學(xué)出版社任務(wù)描述對(duì)3月22日—3月28日一周內(nèi)各大類(lèi)商品的毛利潤(rùn)進(jìn)行分析，要求有數(shù)據(jù)有圖表。任務(wù)分析（1）商品毛利潤(rùn)排行分析（2）商品毛利潤(rùn)占比分析任務(wù)5.3：商品毛利潤(rùn)分析西安電子科技大學(xué)出版社1.統(tǒng)計(jì)商品毛利潤(rùn)。（1）新建數(shù)據(jù)透視表。（2）各大類(lèi)銷(xiāo)售額排序。（3）格式化表格。5.3.1商品毛利潤(rùn)排行分析西安電子科技大學(xué)出版社2.繪制各大類(lèi)商品毛利潤(rùn)排行條形圖。毛利潤(rùn)最高的商品是日配類(lèi)，達(dá)到3378.33，接著是蔬果、休閑和酒飲，為2600.69、1630.63、1310.2，洗化和糧油以及肉禽分別是996.8、685.25、523.21，最低是熟食只有272.51。5.3.1商品毛利潤(rùn)排行分析西安電子科技大學(xué)出版社使用圖5-16所示數(shù)據(jù)，使用5.1.2節(jié)所示方法繪制復(fù)合餅圖如圖5-18所示。毛利潤(rùn)占比最大的是日配類(lèi)，占30%，接著是蔬果和休閑，分別占23%和14%，這三者共占67%。酒飲占11%，洗化、糧油分別占9%和6%，肉禽和熟食分別占5%和2%。5.3.2商品毛利潤(rùn)占比分析西安電子科技大學(xué)出版社任務(wù)5.4商品促銷(xiāo)情況分析西安電子科技大學(xué)出版社任務(wù)描述統(tǒng)計(jì)各大類(lèi)商品銷(xiāo)售額中促銷(xiāo)銷(xiāo)售額占比，要求有數(shù)據(jù)有圖表。任務(wù)分析（1）統(tǒng)計(jì)各大類(lèi)商品促銷(xiāo)商品和非促銷(xiāo)商品的銷(xiāo)售額（2）統(tǒng)計(jì)促銷(xiāo)商品的銷(xiāo)售金額在總銷(xiāo)售額中的占比任務(wù)5.4：商品促銷(xiāo)情況分析西安電子科技大學(xué)出版社1.統(tǒng)計(jì)各大類(lèi)商品促銷(xiāo)情況數(shù)據(jù)統(tǒng)計(jì)。使用數(shù)據(jù)透視表統(tǒng)計(jì)各大類(lèi)商品促銷(xiāo)和非促銷(xiāo)商品的銷(xiāo)售額，然后計(jì)算各大類(lèi)商品中促銷(xiāo)商品的銷(xiāo)售額占比。（1）新建數(shù)據(jù)透視表（2）計(jì)算促銷(xiāo)銷(xiāo)售額占比。（3）格式化表格。任務(wù)5.4：商品促銷(xiāo)情況分析西安電子科技大學(xué)出版社2．繪制各大類(lèi)商品促銷(xiāo)情況統(tǒng)計(jì)圖表（1）繪制組合圖。（2）修改圖表類(lèi)型。任務(wù)5.4：商品促銷(xiāo)情況分析西安電子科技大學(xué)出版社2．繪制各大類(lèi)商品促銷(xiāo)情況統(tǒng)計(jì)圖表（3）添加數(shù)據(jù)標(biāo)簽。（4）修改標(biāo)題。促銷(xiāo)銷(xiāo)售額占比最高的是日配類(lèi)，占41.55%，接著是洗化和糧油，分別占39.85%和31.99%，休閑占23.58%，酒飲和蔬果占8.22%和4.02%，熟食和肉禽沒(méi)有促銷(xiāo)。任務(wù)5.4：商品促銷(xiāo)情況分析西安電子科技大學(xué)出版社任務(wù)5.5工作日和周末商品銷(xiāo)售額對(duì)比西安電子科技大學(xué)出版社任務(wù)描述分析3月22日—3月28日一周內(nèi)顧客在工作日和周末的消費(fèi)偏好。任務(wù)分析計(jì)算工作日和周末各大類(lèi)商品日均銷(xiāo)售額，繪制可視化圖表。任務(wù)5.5：工作日和周末商品銷(xiāo)售額對(duì)比西安電子科技大學(xué)出版社1.統(tǒng)計(jì)各大類(lèi)商品在工作日和周末的日均銷(xiāo)售額。使用數(shù)據(jù)透視表統(tǒng)計(jì)各大類(lèi)商品促銷(xiāo)和非促銷(xiāo)商品的銷(xiāo)售額，然后計(jì)算各大類(lèi)商品中促銷(xiāo)商品的銷(xiāo)售額占比。（1）新建數(shù)據(jù)透視表。（2）計(jì)算工作日和周末日均銷(xiāo)售額。（3）設(shè)置數(shù)字格式。（4）格式化表格。任務(wù)5.5：工作日和周末商品銷(xiāo)售額對(duì)比西安電子科技大學(xué)出版社2．各大類(lèi)商品工作日和周末日均銷(xiāo)售額柱形圖（1）繪制柱形圖。（2）添加數(shù)據(jù)標(biāo)簽并設(shè)置數(shù)據(jù)標(biāo)簽數(shù)據(jù)位數(shù)。任務(wù)5.5：工作日和周末商品銷(xiāo)售額對(duì)比西安電子科技大學(xué)出版社（3）修改標(biāo)題。將圖表標(biāo)題修改為“工作日和周末商品銷(xiāo)售額”，完成后圖表如圖所示。本周日配、肉禽、蔬果、洗化、休閑這幾類(lèi)商品工作日的日均銷(xiāo)售額大于周末的日均銷(xiāo)售額，而酒飲、糧油、熟食在周末的日均銷(xiāo)售額大于工作日日均銷(xiāo)售額。任務(wù)5.5：工作日和周末商品銷(xiāo)售額對(duì)比西安電子科技大學(xué)出版社任務(wù)5.6商品毛利潤(rùn)詳細(xì)分析西安電子科技大學(xué)出版社任務(wù)描述找到毛利潤(rùn)最大的大類(lèi)產(chǎn)品。對(duì)于這類(lèi)產(chǎn)品，將商品分析的粒度減小到中類(lèi)，對(duì)比分析這類(lèi)產(chǎn)品中毛利潤(rùn)最大的兩種中類(lèi)產(chǎn)品的銷(xiāo)售情況，分析兩種中類(lèi)產(chǎn)品在銷(xiāo)售過(guò)程中促銷(xiāo)的作用。任務(wù)分析（1）毛利潤(rùn)分析--包括大類(lèi)精確到中類(lèi)（2）毛利潤(rùn)最大的大類(lèi)產(chǎn)品中，對(duì)比分析毛利潤(rùn)最大的兩種中類(lèi)產(chǎn)品的銷(xiāo)售額（3）兩種中類(lèi)產(chǎn)品的促銷(xiāo)與非促銷(xiāo)銷(xiāo)售額分析任務(wù)5.6：商品毛利潤(rùn)詳細(xì)分析西安電子科技大學(xué)出版社1.統(tǒng)計(jì)各中類(lèi)商品毛利潤(rùn)（1）新建數(shù)據(jù)透視表。（2）修改數(shù)據(jù)表現(xiàn)形式。（3）修改表格格式。5.6.1各中類(lèi)毛利潤(rùn)分析西安電子科技大學(xué)出版社2.繪制各中類(lèi)毛利潤(rùn)樹(shù)狀圖。（1）繪制樹(shù)狀圖。（2）添加數(shù)據(jù)標(biāo)簽。（3）修改圖表標(biāo)題。大類(lèi)毛利潤(rùn)排行為日配、蔬果、休閑、洗化、酒飲、糧油、肉禽、熟食。日配類(lèi)毛利潤(rùn)最大的分別是常溫乳品、冷藏乳品、蛋類(lèi)等等。5.6.1各中類(lèi)毛利潤(rùn)分析西安電子科技大學(xué)出版社常溫乳制品和冷藏乳制品是日配類(lèi)銷(xiāo)量毛利潤(rùn)最大的中類(lèi)商品，繪制兩類(lèi)乳制品的銷(xiāo)售額曲線(xiàn)，觀察兩類(lèi)乳制品銷(xiāo)售規(guī)律。1.統(tǒng)計(jì)常溫乳品和冷藏乳品每日銷(xiāo)售數(shù)據(jù)（1）篩選常溫乳品和冷藏乳品數(shù)據(jù)。5.6.2常溫乳品和冷藏乳品的銷(xiāo)售額對(duì)比西安電子科技大學(xué)出版社（2）新建數(shù)據(jù)透視表。（3）格式化表格。5.6.2常溫乳品和冷藏乳品的銷(xiāo)售額對(duì)比西安電子科技大學(xué)出版社2.繪制常溫乳品和冷藏乳品銷(xiāo)售額對(duì)比圖表。（1）繪制折線(xiàn)圖。（2）添加數(shù)據(jù)標(biāo)簽并設(shè)置位置。5.6.2常溫乳品和冷藏乳品的銷(xiāo)售額對(duì)比西安電子科技大學(xué)出版社（3）將折線(xiàn)變?yōu)槠交€(xiàn)。（4）修改圖表標(biāo)題。5.6.2常溫乳品和冷藏乳品的銷(xiāo)售額對(duì)比西安電子科技大學(xué)出版社作為日配商品中毛利潤(rùn)最高的兩個(gè)中類(lèi)，銷(xiāo)售曲線(xiàn)完全不同，推測(cè)因?yàn)榇黉N(xiāo)原因?qū)е庐a(chǎn)品銷(xiāo)售曲線(xiàn)差別較大，繪制圖形比較分析兩類(lèi)乳制品銷(xiāo)售額和促銷(xiāo)的關(guān)系。（1）新建數(shù)據(jù)透視表。（2）組織銷(xiāo)售額數(shù)據(jù)。（3）格式化表格。5.6.3常溫乳品和冷藏乳品銷(xiāo)售額與促銷(xiāo)的關(guān)系西安電子科技大學(xué)出版社2.繪制常溫乳品和冷藏乳品促銷(xiāo)與非促銷(xiāo)銷(xiāo)售額對(duì)比圖表。（1）繪制簇狀柱形圖。（2）將折線(xiàn)變?yōu)槠交€(xiàn)。（3）更改數(shù)據(jù)系列顏色。5.6.3常溫乳品和冷藏乳品銷(xiāo)售額與促銷(xiāo)的關(guān)系西安電子科技大學(xué)出版社（4）添加數(shù)據(jù)標(biāo)簽。（5）修改圖表標(biāo)題。冷藏乳品的促銷(xiāo)和非促銷(xiāo)銷(xiāo)售額曲線(xiàn)都比較平緩，變化不大；常溫乳品的兩條銷(xiāo)售額曲線(xiàn)變化都比較明顯。3月24日周三常溫乳品的促銷(xiāo)銷(xiāo)售額最高，達(dá)到653.3,3月25日的促銷(xiāo)銷(xiāo)售額僅為124.9。非促銷(xiāo)銷(xiāo)售額3月22日周一最高359.9,3月25日周四最低，僅18.4。5.6.3常溫乳品和冷藏乳品銷(xiāo)售額與促銷(xiāo)的關(guān)系西安電子科技大學(xué)出版社拓展延伸預(yù)測(cè)商品銷(xiāo)售量西安電子科技大學(xué)出版社任務(wù)描述“和美家”超市某分店想通過(guò)2021年1月1日到3月28日的水果的銷(xiāo)售量預(yù)測(cè)未來(lái)四周水果銷(xiāo)售量，以便安排進(jìn)貨。任務(wù)分析可以使用函數(shù)、繪制預(yù)測(cè)曲線(xiàn)圖、預(yù)測(cè)工作表等幾種方式通過(guò)以往多個(gè)周期的已有數(shù)據(jù)預(yù)測(cè)未來(lái)周期的數(shù)據(jù)，本節(jié)采用預(yù)測(cè)工作表方式。拓展延伸：預(yù)測(cè)商品銷(xiāo)售量西安電子科技大學(xué)出版社1.制作水果銷(xiāo)量預(yù)測(cè)數(shù)據(jù)表。（1）得到水果銷(xiāo)售數(shù)據(jù)。打開(kāi)【0322-28消費(fèi)數(shù)據(jù)】工作表，利用【篩選】，先在大類(lèi)中選擇【蔬果】，然后在種類(lèi)中選擇【水果】類(lèi)，得到水果的銷(xiāo)售數(shù)據(jù)。（2）新建【拓展-水果銷(xiāo)售數(shù)據(jù)】工作表。創(chuàng)建新的工作表并重命名為【拓展-水果銷(xiāo)售數(shù)據(jù)】，只粘貼值，得到所有水果的銷(xiāo)售數(shù)據(jù)工作表，包括標(biāo)題行共有206行數(shù)據(jù)。拓展延伸：預(yù)測(cè)商品銷(xiāo)售量西安電子科技大學(xué)出版社（3）新建【拓展-水果銷(xiāo)量預(yù)測(cè)】工作表。創(chuàng)建新的工作表并重命名為【拓展-水果銷(xiāo)量預(yù)測(cè)】，打開(kāi)【2021每周水果銷(xiāo)量統(tǒng)計(jì)】，將里面的所有數(shù)據(jù)復(fù)制到【拓展-水果銷(xiāo)量預(yù)測(cè)】數(shù)據(jù)工作表中，則數(shù)據(jù)如圖所示。拓展延伸：預(yù)測(cè)商品銷(xiāo)售量西安電子科技大學(xué)出版社（4）計(jì)算本周水果銷(xiāo)售量?！舅麛?shù)據(jù)工作表】中，，選中N207，輸入公式“=SUM(N2:N206)”得到本周水果的總銷(xiāo)售量為210.891千克。（5）將本周銷(xiāo)售量加入工作表。在【拓展-水果銷(xiāo)量預(yù)測(cè)】增加新行，輸入“13”及日期區(qū)間，將剛才計(jì)算得到的本周水果銷(xiāo)售量210.891到工作表。（6）輸入預(yù)測(cè)周數(shù)和日期數(shù)據(jù)。在【拓展-水果銷(xiāo)量預(yù)測(cè)】增加新行，輸入14、15、16、17以及日期區(qū)間。（7）格式化表格。完成后效果如圖所示。拓展延伸：預(yù)測(cè)商品銷(xiāo)售量西安電子科技大學(xué)出版社2.繪制折線(xiàn)圖預(yù)計(jì)水果銷(xiāo)售量（1）創(chuàng)建預(yù)測(cè)工作表。單擊【拓展-水果銷(xiāo)量預(yù)測(cè)】工作表中有數(shù)據(jù)的任意單元格，在【數(shù)據(jù)】選項(xiàng)卡的【預(yù)測(cè)】命令組中，單擊【預(yù)測(cè)工作表】圖標(biāo)，彈出【創(chuàng)建預(yù)測(cè)工作表】對(duì)話(huà)框如圖所示。拓展延伸：預(yù)測(cè)商品銷(xiāo)售量西安電子科技大學(xué)出版社（2）計(jì)算預(yù)測(cè)值并繪制折線(xiàn)圖。單擊【選項(xiàng)】，將【置信區(qū)間】設(shè)置為50%，如圖5-63所示。單擊【創(chuàng)建】按鈕，創(chuàng)建新工作表并自動(dòng)計(jì)算出序號(hào)為14、15、16、17的預(yù)測(cè)值，同時(shí)自動(dòng)繪制出銷(xiāo)售額的折線(xiàn)圖，如圖5-64所示，將新工作表重命名為“拓展-自動(dòng)預(yù)測(cè)工作表”。拓展延伸：預(yù)測(cè)商品銷(xiāo)售量西安電子科技大學(xué)出版社（3）添加數(shù)據(jù)標(biāo)簽、圖表標(biāo)題。選中圖表區(qū)，單擊圖表區(qū)右上角的綠色按鈕，在彈出的列表框中勾選【圖表標(biāo)題】，【數(shù)據(jù)標(biāo)簽】，【數(shù)據(jù)表】復(fù)選框，如圖所示。拓展延伸：預(yù)測(cè)商品銷(xiāo)售量