商務(wù)數(shù)據(jù)分析與應(yīng)用 課件 項(xiàng)目5、6 商務(wù)數(shù)據(jù)可視化、商品銷售情況統(tǒng)計(jì)與分析

商務(wù)數(shù)據(jù)分析與應(yīng)用本書(shū)目錄項(xiàng)目一商務(wù)數(shù)據(jù)分析基礎(chǔ)項(xiàng)目二認(rèn)識(shí)數(shù)據(jù)表項(xiàng)目三

商務(wù)數(shù)據(jù)錄入與整理項(xiàng)目四

商務(wù)數(shù)據(jù)分析工具項(xiàng)目五

商務(wù)數(shù)據(jù)可視化項(xiàng)目六商品銷售情況統(tǒng)計(jì)與分析項(xiàng)目七商品銷售費(fèi)用分析項(xiàng)目八

商品庫(kù)存分析項(xiàng)目九

銷售市場(chǎng)預(yù)測(cè)分析項(xiàng)目五商務(wù)數(shù)據(jù)可視化

目錄1復(fù)雜數(shù)據(jù)圖經(jīng)濟(jì)適用圖25.1.1柱形圖與條形圖5.1.2餅圖與復(fù)合餅圖5.1.3雷達(dá)圖柱形圖又稱條形圖和直方圖，它是寬度相等的條形以高度或長(zhǎng)度的差異來(lái)顯示一段時(shí)間內(nèi)數(shù)據(jù)的變化。它主要包括5種樣式的子表圖，分別是二維、三維、圓柱、圓錐和棱錐，而且每種樣式都具備3種類型，分別是簇狀、堆積和百分比。5.1.1柱形圖與條形圖簇狀條形圖百分比條形圖

堆積條形圖條形圖是用寬度相同的條形的高度或長(zhǎng)短來(lái)表示數(shù)據(jù)多少的圖形。條形圖可以橫置或縱置，縱置時(shí)也稱為柱形圖。因此，也可以把條形圖看成是柱形圖的橫置。此外，條形圖有簡(jiǎn)單條形圖、復(fù)式條形圖等形式。條形圖的圖表子類型有簇狀條形圖和三維簇狀條形圖、堆積條形圖和三維堆積條形圖、百分比堆積條形圖和三維百分比堆積條形圖。5.1.1柱形圖與條形圖簇狀條形圖堆積條形圖【柱形圖的繪制】任務(wù)描述：將“學(xué)生成績(jī)排序”表中，10位同學(xué)的成績(jī)按從高到低排序，并用柱形圖呈現(xiàn)。5.1.1柱形圖與條形圖STEP1：打開(kāi)“學(xué)生成績(jī)排序表”，選擇A1：B11單元格區(qū)域，如何5-1-1所示。5.1.1柱形圖與條形圖STEP2：在“數(shù)據(jù)”選項(xiàng)卡中選擇“排序”，在打開(kāi)的對(duì)話框中主關(guān)鍵詞選擇“成績(jī)”，次序選擇“降序”，如圖5-1-2所示。5.1.1柱形圖與條形圖STEP3：在“插入”選項(xiàng)卡的“圖表”組中單擊“柱形圖”按鈕，在打開(kāi)的下拉菜單中選擇“簇狀柱形圖”選項(xiàng)，結(jié)果如圖5-1-3所示。STEP4：選中主網(wǎng)格線，右鍵，選擇“設(shè)置網(wǎng)格線格式”，線條格式選擇“無(wú)線條”，，如圖5-1-4所示。5.1.1柱形圖與條形圖STEP5：選中縱坐標(biāo)軸，右鍵，選擇“設(shè)置坐標(biāo)軸格式”，標(biāo)簽位置選擇“無(wú)”，隱藏縱坐標(biāo)軸，如圖5-1-5所示。STEP6：選中任一柱狀，右鍵，選擇“添加數(shù)據(jù)標(biāo)簽”，如圖5-1-6所示。5.1.1柱形圖與條形圖STEP7：選中圖表標(biāo)題，更改圖表標(biāo)題為“10位學(xué)生成績(jī)排序”，如圖5-1-7所示?！緱l形圖的繪制】條形圖的繪制過(guò)程與柱狀圖類似，這里不再贅述。條形圖最終繪制結(jié)果如下圖：5.1.1柱形圖與條形圖餅圖是將一個(gè)圓餅分為若干份，用來(lái)反映事物的構(gòu)成情況，大小/比例。僅排列在表格的某一行或某一列中的數(shù)據(jù)能繪制到餅圖中。餅圖包括二維餅圖和三維餅圖兩種形式。復(fù)合餅圖或復(fù)合條餅圖顯示將用戶定義的數(shù)值從主餅圖中提取并組合到第二個(gè)餅圖或堆積條形圖的餅圖。如果要使主餅圖中的小扇面更易于查看，這些圖表類型非常有用。

5.1.2餅圖與復(fù)合餅圖【餅圖的繪制】將“三月銷售量”表中，5個(gè)城區(qū)的銷售數(shù)據(jù)用餅圖展示出來(lái)，并能反映各區(qū)的銷售占比。STEP1：選中A1:B6單元格區(qū)域，如圖5-1-8所示；5.1.2餅圖與復(fù)合餅圖STEP2：在“插入”選項(xiàng)卡中選擇“圖表”組中的二維餅圖，如圖5-1-9所示；STEP3：選中任一扇區(qū)，右鍵，選擇“添加數(shù)據(jù)標(biāo)簽”，如圖5-1-10所示。5.1.2餅圖與復(fù)合餅圖STEP4：選中數(shù)據(jù)標(biāo)簽，右鍵，選擇“設(shè)置數(shù)據(jù)標(biāo)簽格式”。勾選“類別名稱”、“百分比”。如圖5-1-11所示。STEP5：將圖例刪除。如圖5-1-12所示。5.1.2餅圖與復(fù)合餅圖【復(fù)合餅圖的繪制】

打開(kāi)“各品牌飲料市場(chǎng)份額占比”表，將各品牌飲料的市場(chǎng)份額占比繪制成復(fù)合餅圖。注意市場(chǎng)占比最少的四類飲料繪制到復(fù)合餅圖。STEP1：選中A1:B10單元格區(qū)域，如5-1-13所示。5.1.2餅圖與復(fù)合餅圖STEP2：在“插入”選項(xiàng)卡中選擇“圖表”組中的二維復(fù)合餅圖，如圖5-1-14所示。STEP3：選中任一扇區(qū)，右鍵，選擇“添加數(shù)據(jù)標(biāo)簽”，如圖5-1-15所示。5.1.2餅圖與復(fù)合餅圖STEP4：選中數(shù)據(jù)標(biāo)簽，右鍵，選擇“設(shè)置數(shù)據(jù)標(biāo)簽格式”。勾選“類別名稱”、“百分比”，第二繪圖區(qū)中的值填寫(xiě)“4”，如圖5-1-16所示。STEP5：將圖例刪除，如圖5-1-17所示。5.1.2餅圖與復(fù)合餅圖雷達(dá)圖是以從同一點(diǎn)開(kāi)始的軸上表示的三個(gè)或更多個(gè)定量變量的二維圖表的形式顯示多變量數(shù)據(jù)的圖形方法。軸的相對(duì)位置和角度通常是無(wú)信息的。雷達(dá)圖也稱為網(wǎng)絡(luò)圖，蜘蛛圖，星圖，蜘蛛網(wǎng)圖，不規(guī)則多邊形，極坐標(biāo)圖或Kiviat圖。它相當(dāng)于平行坐標(biāo)圖，軸徑向排列?！纠走_(dá)圖的繪制】打開(kāi)“甲乙兩種產(chǎn)品參數(shù)對(duì)比”表，繪制雷達(dá)圖。STEP1：選中A1:H3單元格區(qū)域，如圖5-1-18所示。

5.1.3雷達(dá)圖STEP2：在“插入”選項(xiàng)卡中選擇“圖表”組中的雷達(dá)圖，如圖5-1-19所示。5.1.3雷達(dá)圖STEP3：更改圖名為“甲乙兩種產(chǎn)品參數(shù)對(duì)比”，如圖5-1-20所示。目錄1復(fù)雜數(shù)據(jù)圖經(jīng)濟(jì)使用圖25.2.1平行線圖5.2.2雙坐標(biāo)圖5.2.3旋風(fēng)圖5.2.4帕累托圖5.2.1平行線圖平均線圖，可以將平均值和具體的數(shù)值一同展現(xiàn)在圖表中，方便比較平均值與數(shù)值之間的差距，可以很明顯的觀察到哪些數(shù)據(jù)是高于／低于平均值。一般而言，做數(shù)據(jù)分析時(shí)，“對(duì)比分析”會(huì)用到平均線圖。做平均線圖時(shí)，原數(shù)據(jù)一般做成柱形圖更易通過(guò)看圖對(duì)數(shù)據(jù)進(jìn)行分析。5.2.1平行線圖任務(wù)描述：用柱狀圖繪制出“估價(jià)二班期末成績(jī)”中所有學(xué)生的成績(jī)分布情況，并通過(guò)繪制平均成績(jī)線圖，展示學(xué)生成績(jī)分布情況。5.2.1平行線圖STEP1：打開(kāi)“估價(jià)二班期末成績(jī)”表，在C1單元格新增“平均成績(jī)”字段，如圖5-2-1所示。5.2.1平行線圖STEP2：在C2:C15單元格范圍內(nèi)，填充學(xué)生的平均成績(jī)數(shù)據(jù)。具體做法為，將光標(biāo)置于C2單元格，在“公式”選項(xiàng)卡“函數(shù)庫(kù)”組內(nèi)，選擇“平均值”。數(shù)據(jù)框選范圍為B2:B15。C2:C15單元格區(qū)域均填充此平均值。如圖5-2-2所示。5.2.1平行線圖STEP3：選中A1:C15單元格區(qū)域，在“插入”選項(xiàng)卡“圖表”組。選擇“柱形圖”；如圖5-2-3所示。STEP4：選中柱狀，右鍵，選擇“更改圖表系列類型”，將“平均成績(jī)”圖表類型更改為“折線”，確定；如圖5-2-4所示。5.2.1平行線圖STEP5：選中圖例，右鍵，設(shè)置圖例格式為“靠上”；如圖5-2-5所示。STEP6：選中網(wǎng)格線，右鍵，設(shè)置網(wǎng)格線格式為“無(wú)線條”；如圖5-2-6所示。5.2.1平行線圖STEP7：在“插入”選項(xiàng)卡“插圖”組中，選擇標(biāo)注圖形，繪制平均線標(biāo)注。將“標(biāo)注”圖形底色設(shè)置為無(wú)填充，右鍵，“編輯文字”，輸入“平均成績(jī)：79"；如圖5-2-7所示。5.2.1平行線圖STEP8：將圖表標(biāo)題更改為”學(xué)生總成績(jī)平均線圖”。如圖5-2-8所示。5.2.2雙坐標(biāo)圖雙坐標(biāo)圖比平常的圖形多了一個(gè)縱坐標(biāo)軸，稱之為次縱坐標(biāo)軸。主坐標(biāo)軸即常用的左軸，簡(jiǎn)稱左軸；次縱坐標(biāo)軸在右軸顯示，簡(jiǎn)稱右軸。一般在圖標(biāo)中有兩個(gè)系列及其以上的數(shù)據(jù)，并且它們的量綱不同或者數(shù)據(jù)差別很大時(shí)，在同一縱坐標(biāo)軸下就無(wú)法很好的展現(xiàn)出數(shù)據(jù)原本的面貌，這是采用雙坐標(biāo)圖來(lái)繪制。5.2.2雙坐標(biāo)圖【雙坐標(biāo)線柱圖的繪制】

任務(wù)描述：將“生產(chǎn)線產(chǎn)品檢測(cè)表”中的數(shù)據(jù)用雙坐標(biāo)圖中的線柱圖展示出來(lái)。要求橫縱坐標(biāo)都有標(biāo)識(shí)，有圖例。5.2.2雙坐標(biāo)圖STEP1：選中A1:C9單元格區(qū)域；如圖5-2-9所示。STEP2：在“插入”選項(xiàng)卡的“圖表”組中單擊“柱形圖”按鈕，在打開(kāi)的下拉菜單中選擇“簇狀柱形圖”選項(xiàng)，繪制出簡(jiǎn)單柱形圖；如圖5-2-10所示。5.2.2雙坐標(biāo)圖STEP3：選中柱狀，右鍵，選擇“更改圖表類型”，將“不良率”設(shè)置為“折線圖”并繪制在次坐標(biāo)，確定；如圖5-2-11所示。STEP4：選中主網(wǎng)格線，右鍵，選擇“設(shè)置網(wǎng)格線格式”，線條格式選擇“無(wú)線條”；如圖5-2-12所示。。5.2.2雙坐標(biāo)圖STEP5：選中圖例，右鍵，“設(shè)置圖例格式”，選擇“靠上”；如圖5-2-13所示。STEP6：選中圖表標(biāo)題，更改圖表標(biāo)題為“不同生產(chǎn)線及不良率”。如圖5-2-14所示。5.2.2雙坐標(biāo)圖【雙坐標(biāo)雙柱圖的繪制】

任務(wù)描述：將“生產(chǎn)線產(chǎn)品檢測(cè)表”中的數(shù)據(jù)用雙坐標(biāo)圖中的雙柱圖展示出來(lái)。要求橫縱坐標(biāo)都有標(biāo)識(shí)，圖例只顯示“產(chǎn)量”和“不良率”。5.2.2雙坐標(biāo)圖STEP1：在“產(chǎn)量”與“不良率”字段之間插入兩列，字段名稱為“產(chǎn)量占位”與“不良率占位“；如圖5-2-15所示。STEP2：“產(chǎn)量占位”與“不良率占位”字段列填充數(shù)據(jù)，所填充數(shù)據(jù)建議介于“產(chǎn)量”與“不良率”數(shù)值之間，大小適中。例如分別填充為“800”、“300”；如圖5-2-16所示。5.2.2雙坐標(biāo)圖STEP3：選中A1:E9單元格區(qū)域；如圖5-2-17所示。STEP4：在“插入”選項(xiàng)卡的“圖表”組中單擊“柱形圖”按鈕，在打開(kāi)的下拉菜單中選擇“簇狀柱形圖”選項(xiàng)，繪制出簡(jiǎn)單柱形圖；如圖5-2-18所示。5.2.2雙坐標(biāo)圖STEP5：選中柱狀，右鍵，選擇“更改圖表類型”，將“不良率”、“不良率占位”設(shè)置為“折線圖”并繪制在次坐標(biāo)，確定；如圖5-2-19所示。5.2.2雙坐標(biāo)圖STEP6：刪除表格中“產(chǎn)量占位”與“不良率占位“的所有數(shù)據(jù)；如圖5-2-20所示。5.2.2雙坐標(biāo)圖STEP7：將圖例中占位的標(biāo)識(shí)刪除，右鍵，“設(shè)置圖例格式”，選擇“靠上”；如圖5-2-21所示。STEP8：選中主網(wǎng)格線，右鍵，選擇“設(shè)置網(wǎng)格線格式”，線條格式選擇“無(wú)線條”；如圖5-2-22所示。5.2.2雙坐標(biāo)圖STEP9：選中右坐標(biāo)軸，右鍵，“設(shè)置坐標(biāo)軸格式”。在坐標(biāo)軸選項(xiàng)中，將數(shù)值格式更改為“百分?jǐn)?shù)”；如圖5-2-23所示。STEP10：選中圖表標(biāo)題，更改圖表標(biāo)題為“不同生產(chǎn)線及不良率”。如圖5-2-24所示。5.2.3旋風(fēng)圖旋風(fēng)圖的學(xué)名是成對(duì)條形圖、對(duì)稱條形圖。對(duì)稱條形圖是條形圖的一種，支持根據(jù)數(shù)據(jù)兩個(gè)分類的對(duì)稱比較樣式，能夠更加清晰智能地展示各數(shù)據(jù)的差異。5.2.3旋風(fēng)圖旋風(fēng)圖的主要運(yùn)用于以下情境：（1）同一事物在某個(gè)活動(dòng)、行為影響前后不同指標(biāo)的變化；（2）同一事物在某個(gè)條件變化下（指標(biāo)A的變化），指標(biāo)B受影響也隨之變化，具有因果關(guān)系；（3）兩個(gè)類比之間不同指標(biāo)的比較。5.2.3旋風(fēng)圖任務(wù)描述：打開(kāi)“各部門(mén)員工男女性別占比”表格，制作旋風(fēng)圖進(jìn)行分析。5.2.3旋風(fēng)圖STEP1：選中選中A1:C8單元格區(qū)域，在“插入”選項(xiàng)卡的“圖表”組中單擊“條形圖”按鈕，在打開(kāi)的下拉菜單中選擇“簇狀條形圖”選項(xiàng)，繪制出簡(jiǎn)單條形圖；如圖5-2-25所示。STEP2：選中其中一組條形，例如紅色的代表“女性”比例的條形，右鍵，“設(shè)置數(shù)據(jù)系列格式”，選擇“繪制在次坐標(biāo)”；如圖5-2-26所示。5.2.3旋風(fēng)圖STEP3：分別選中主要橫坐標(biāo)、次要橫坐標(biāo)，右鍵，“設(shè)置坐標(biāo)軸格式”，在“坐標(biāo)軸選項(xiàng)”里分別設(shè)置主橫坐標(biāo)、次橫坐標(biāo)最大值為1，最小值為-1；如圖5-2-27所示。5.2.3旋風(fēng)圖STEP4：在“坐標(biāo)軸選項(xiàng)”，“數(shù)字”里，自定義數(shù)字格式“0%;0%;0%”，并讓兩個(gè)橫坐標(biāo)軸標(biāo)簽都選擇這種格式，使左側(cè)負(fù)坐標(biāo)軸標(biāo)簽顯示正值；如圖5-2-28所示。5.2.3旋風(fēng)圖STEP5：選中上方橫坐標(biāo)軸，在“坐標(biāo)軸選項(xiàng)”里勾選“逆序刻度值”，主要刻度線類型、坐標(biāo)軸標(biāo)簽都選“無(wú)”；如圖5-2-29所示。5.2.3旋風(fēng)圖STEP6：選中縱坐標(biāo)軸，右鍵，設(shè)置坐標(biāo)軸格式，“坐標(biāo)軸選項(xiàng)”中的“標(biāo)簽”中的“坐標(biāo)軸標(biāo)簽位置”選擇“低”；如圖5-2-30所示。5.2.3旋風(fēng)圖STEP7：選中圖例，右鍵，“設(shè)置圖例格式”，選擇“靠上”；如圖5-2-31所示。STEP8：選中主網(wǎng)格線，右鍵，選擇“設(shè)置網(wǎng)格線格式”，線條格式選擇“無(wú)線條”；如圖5-2-32所示。5.2.3旋風(fēng)圖STEP9：分別選中兩組條形，右鍵，“添加數(shù)據(jù)標(biāo)簽”；如圖5-2-33所示。STEP10：選中圖表標(biāo)題，更改圖表標(biāo)題為“各部門(mén)員工男女性別占比”。如圖5-2-34所示。5.2.4帕累托圖1.二八法則二八法則是指，任何一組事物中，最重要的只占其中的20%，其余的80%雖然是多數(shù)，但卻是次要的。2.帕累托圖帕累托圖又叫排列圖、主次圖，是按照頻率發(fā)生的高低順序繪制的直方圖（無(wú)間距柱形圖）。帕累托圖能區(qū)分“微不足道的大多數(shù)”和“至關(guān)重要的極少數(shù)”，從而方便人們關(guān)注于重要的類別。5.2.4帕累托圖3.帕累托圖的特征（1）帕累托圖是一個(gè)特殊的線柱圖，柱形圖的數(shù)據(jù)按數(shù)值的降序排列，折線圖數(shù)據(jù)為累計(jì)百分比數(shù)據(jù)，并在次坐標(biāo)軸顯示；（2）折線圖的起點(diǎn)數(shù)值為0%，并位于柱形圖第一個(gè)柱子的最左下角；（3）折線圖的第二個(gè)點(diǎn)位于柱形圖第一個(gè)柱子的最右上角；（4）折線圖最后一個(gè)點(diǎn)數(shù)值為100%，位于整張圖形的最右上角。5.2.4帕累托圖任務(wù)描述：打開(kāi)“學(xué)生曠課原因分析表”，繪制帕累托圖。5.2.4帕累托圖STEP1：在C1、D1單元格分別新增“累計(jì)百分比”、“累計(jì)人數(shù)”字段，求出相應(yīng)記錄；如圖5-2-35所示。5.2.4帕累托圖STEP2：選中“曠課原因”、“人數(shù)”、“累計(jì)百分比”三個(gè)字段與記錄，在“插入”選項(xiàng)卡的“圖表”組中單擊“柱形圖”按鈕，在打開(kāi)的下拉菜單中選擇“簇狀柱形圖”選項(xiàng)，繪制出簡(jiǎn)單柱形圖；如圖5-2-36所示。5.2.4帕累托圖STEP3：選中“累計(jì)百分比”生成的柱形，右鍵，選擇“更改圖表系列類型”。將“累計(jì)百分比”柱形更改為”帶標(biāo)記點(diǎn)的折線圖“，并勾選“繪制到次坐標(biāo)；如圖5-2-37所示。5.2.4帕累托圖STEP4：在字段名稱下新插入一行，并在”累計(jì)百分比“字段下方錄入“0%”；如圖5-2-38所示。5.2.4帕累托圖STEP5：選中”累計(jì)百分比折線“，右鍵，選擇數(shù)據(jù)，選擇”累計(jì)百分比“點(diǎn)擊”編輯“，重新選擇系列值范圍，將”0%“框選在范圍內(nèi)。確定；如圖5-2-39所示。5.2.4帕累托圖STEP6：在”圖表設(shè)計(jì)“選項(xiàng)卡中，點(diǎn)擊”添加圖表元素“，選擇添加”次要橫坐標(biāo)軸；如圖5-2-40所示。5.2.4帕累托圖STEP7：選中次要橫坐標(biāo)軸，右鍵，設(shè)置坐標(biāo)軸格式，位置坐標(biāo)軸選擇”在刻度線上“。主要刻度線類型、坐標(biāo)軸標(biāo)簽選擇“無(wú)”；如圖5-2-41所示。5.2.4帕累托圖STEP8：選中柱狀，右鍵，設(shè)置數(shù)據(jù)系列格式，系列選項(xiàng)中”間隙寬度“設(shè)置為”0%“；如圖5-2-42所示。5.2.4帕累托圖STEP9：設(shè)置主要縱坐標(biāo)軸的最大值為100，最小值為0；設(shè)置次要縱坐標(biāo)軸的最大值為100%，最小值為0%；如圖5-如圖5-2-43所示。5.2.4帕累托圖STEP10：選中圖例，右鍵，“設(shè)置圖例格式”，選擇“靠上”；如圖5-2-44所示。STEP11：選中主網(wǎng)格線，右鍵，選擇“設(shè)置網(wǎng)格線格式”，線條格式選擇“無(wú)線條”；如圖5-2-45所示。5.2.4帕累托圖STEP12：選中折線，右鍵，“添加數(shù)據(jù)標(biāo)簽”；如圖5-2-46所示。5.2.4帕累托圖STEP13：選中圖表標(biāo)題，更改圖表標(biāo)題為“學(xué)生曠課原因帕累托圖”。如圖5-2-47所示。商務(wù)數(shù)據(jù)分析與應(yīng)用本書(shū)目錄項(xiàng)目一商務(wù)數(shù)據(jù)分析基礎(chǔ)項(xiàng)目二認(rèn)識(shí)數(shù)據(jù)表項(xiàng)目三

商務(wù)數(shù)據(jù)錄入與整理項(xiàng)目四

商務(wù)數(shù)據(jù)分析工具項(xiàng)目五

商務(wù)數(shù)據(jù)可視化項(xiàng)目六商品銷售情況統(tǒng)計(jì)與分析項(xiàng)目七

銷售費(fèi)用分析項(xiàng)目八

商品庫(kù)存分析項(xiàng)目九

銷售市場(chǎng)預(yù)測(cè)分析項(xiàng)目六

商品銷售情況統(tǒng)計(jì)與分析電商賣家需要對(duì)在線商品的銷售數(shù)據(jù)進(jìn)行定期統(tǒng)計(jì)與整理，便于深入了解各類商品的銷售情況。實(shí)際上，從線上導(dǎo)出的數(shù)據(jù)只是一張記錄基本信息的表格，并不能直接看出表格中數(shù)據(jù)背后呈現(xiàn)的銷售方面的問(wèn)題，更不能體現(xiàn)其他潛在的信息，這時(shí)通過(guò)Excel對(duì)銷售情況數(shù)據(jù)進(jìn)行統(tǒng)計(jì)分析，從中發(fā)現(xiàn)問(wèn)題并解決問(wèn)題，為以后的銷售策略提供數(shù)據(jù)支持。目錄1商品盈利情況統(tǒng)計(jì)與分析商品銷售數(shù)據(jù)統(tǒng)計(jì)與分析2商品退貨退款情況統(tǒng)計(jì)分析36.1.1制作銷售報(bào)表6.1.2暢銷與滯銷商品分析6.1.3商品銷售額排名STEP1：打開(kāi)“素材文件/項(xiàng)目6/商品退款分析表.xlsx/退款清單工作表”，添加“銷售成本”、“銷售總額”、“銷售毛利”三個(gè)字段，如圖6-1-1所示。6.1.1制作銷售報(bào)表STEP2：根據(jù)已有數(shù)據(jù)及三個(gè)字段的內(nèi)涵，分布計(jì)算出三個(gè)字段對(duì)應(yīng)的數(shù)據(jù)。選擇G2單元格，在編輯欄中輸入公式“=E2*F2”，并按回車鍵確認(rèn)，計(jì)算出銷售成本，然后使用填充柄將公式復(fù)制到其他單元格中，如圖6-1-2所示。6.1.1制作銷售報(bào)表STEP3：同第二步，選擇H2單元格，在編輯欄中輸入公式“=E2*F2”，并按回車鍵確認(rèn)，計(jì)算出銷售總額，然后使用填充柄將公式復(fù)制到其他單元格中；選擇I2單元格，在編輯欄中輸入公式“=H2-G2”，并按回車鍵確認(rèn)，計(jì)算出銷售毛利，然后使用填充柄將公式復(fù)制到其他單元格中，結(jié)果如圖6-1-3所示。6.1.1制作銷售報(bào)表賣家通過(guò)對(duì)商品的銷售情況進(jìn)行分析，可以直觀地判斷那些商品是暢銷狀態(tài)，哪些商品是滯銷狀態(tài)，然后針對(duì)不同銷售狀態(tài)的商品制定不同的采購(gòu)計(jì)劃和銷售策略。下面將介紹如何分析店鋪中的暢銷與滯銷商品，具體可參照如下案例：STEP1：打開(kāi)“素材文件/項(xiàng)目6/銷售數(shù)據(jù)分析表.xlsx/店鋪月銷售報(bào)表工作表”，選擇B2:B66單元格區(qū)域，點(diǎn)右鍵選擇復(fù)制，或者按【Ctrl+C】快捷鍵復(fù)制選中數(shù)據(jù)，如圖6-1-4。6.1.2暢銷與滯銷商品分析STEP2：選擇“暢銷與滯銷品分析”工作表，選擇A2單元格，然后按【Enter】鍵粘貼數(shù)據(jù)，如圖6-1-5所示。6.1.2暢銷與滯銷商品分析STEP3：選中A列數(shù)據(jù)，打開(kāi)“數(shù)據(jù)“選項(xiàng)卡，單擊“數(shù)據(jù)工具”組中的“刪除重復(fù)項(xiàng)“按鈕，對(duì)A列數(shù)據(jù)中的重復(fù)數(shù)據(jù)進(jìn)行刪除，過(guò)程如圖6-1-6所示，結(jié)果如圖6-1-7所示。6.1.2暢銷與滯銷商品分析STEP4：運(yùn)用sumif函數(shù)求出不同商品總銷量。在“暢銷與滯銷商品分析”工作表中，選中B2單元格，在編輯欄中輸入公式“=SUMIF(店鋪月銷售報(bào)表!$B$2:$B$66,暢銷與滯銷商品分析!A2,店鋪月銷售報(bào)表!$F$2:$F$66)”，并按回車鍵確認(rèn)，計(jì)算出銷售總量，然后使用填充柄將公式復(fù)制到其他單元格中，如圖6-1-8所示。6.1.2暢銷與滯銷商品分析STEP5：運(yùn)用sumif函數(shù)求出不同商品總銷售額。在“暢銷與滯銷商品分析”工作表中，選中C2單元格，在編輯欄中輸入公式“=SUMIF(店鋪月銷售報(bào)表!$B$2:$B$66,暢銷與滯銷商品分析!A2,店鋪月銷售報(bào)表!$H$2:$H$66)”，并按回車鍵確認(rèn)，計(jì)算出總銷售額，然后使用填充柄將公式復(fù)制到其他單元格中，如圖6-1-9所示。6.1.2暢銷與滯銷商品分析STEP6：運(yùn)用暢滯銷比公式，求出不同商品暢滯銷比率。選擇B2：C9 單元格區(qū)域，點(diǎn)擊“開(kāi)始”選項(xiàng)卡，點(diǎn)“編輯”工具中的“自動(dòng)求和”，對(duì)銷量與銷售額做求和計(jì)算，如圖6-1-10。然后選擇D2單元格，在編輯欄中輸入公式“=B2/$B$10*0.8+C2/$C$10*0.2”，計(jì)算出每種商品的暢滯銷比率，然后使用填充柄將公式復(fù)制到其他單元格中，如圖6-1-11所示。6.1.2暢銷與滯銷商品分析STEP7：運(yùn)用IF函數(shù)分析每種商品的銷售狀態(tài)（具體的評(píng)判標(biāo)準(zhǔn)依據(jù)商品特點(diǎn)變動(dòng)而）。選擇E2單元格，在編輯欄中輸入公式“=IF(D2>15%,"暢銷",IF(D2>10%,"一般","滯銷"))”，計(jì)算出每種商品的銷售狀態(tài)，然后使用填充柄將公式復(fù)制到其他單元格中，如圖6-1-12所示。6.1.2暢銷與滯銷商品分析STEP1：運(yùn)用ROW函數(shù)進(jìn)行排名序號(hào)編輯。選擇H2單元格，在編輯欄中輸入公式“=ROW()-1”，編輯排名序號(hào)，然后使用填充柄將公式復(fù)制到其他單元格中，如圖6-1-13所示。6.1.3商品銷售額排名STEP2：運(yùn)用LARGE函數(shù)將銷售額從大到小添加到“銷售額”字段。選擇J2單元格，在編輯欄中輸入公式“=LARGE($C$2:$C$9,H2)”，按從大到小順序填充銷售額字段，然后使用填充柄將公式復(fù)制到其他單元格中，如圖6-1-14所示。6.1.3商品銷售額排名STEP3：運(yùn)用INDEX嵌套MATCH函數(shù)，根據(jù)銷售額大小匹配出相應(yīng)的商品編碼。選擇I2單元格，在編輯欄中輸入公式“=INDEX($A$2:$A$9,MATCH(J2,$C$2:$C$9,0))”，匹配銷售額對(duì)應(yīng)的商品變化，然后使用填充柄將公式復(fù)制到其他單元格中，如圖6-1-15所示。6.1.3商品銷售額排名目錄1商品盈利情況統(tǒng)計(jì)與分析商品銷售數(shù)據(jù)統(tǒng)計(jì)與分析2商品退貨退款情況統(tǒng)計(jì)分析36.2.1不同商品利潤(rùn)率分析6.2.2不同商品收入與毛利對(duì)比分析6.2.3不同商品分配方案分析6.2.1不同商品利潤(rùn)率分析STEP1：打開(kāi)“素材文件/項(xiàng)目6/銷售數(shù)據(jù)分析表.xlsx/店鋪月銷售報(bào)表工作表”，添加“毛利率”三個(gè)字段，如圖6-2-1所示。6.2.1不同商品利潤(rùn)率分析STEP2：根據(jù)毛利率的內(nèi)涵，計(jì)算各商品毛利率。選擇J2單元格，在編輯欄中輸入公式“=I2/H2”，并按回車鍵確認(rèn)，計(jì)算出毛利率，然后使用填充柄將公式復(fù)制到其他單元格中，如圖6-2-2所示。6.2.1不同商品利潤(rùn)率分析STEP3：打開(kāi)“店鋪月銷售報(bào)表工作表”，選擇任意單元格，按【Ctrl+A】快捷鍵，全選表格數(shù)據(jù)，在“插入”選項(xiàng)卡的“表格”選項(xiàng)組中，單擊“插入數(shù)據(jù)透視表“按鈕，過(guò)程如圖6-2-3所示，彈出“創(chuàng)建工作表”對(duì)話框，如圖6-2-4。6.2.1不同商品利潤(rùn)率分析STEP4：選擇數(shù)據(jù)透視表位置，勾選“現(xiàn)有工作表”按鈕，鼠標(biāo)選擇“不同商品盈利情況”工作表中的B2單元格，然后按【Enter】鍵，點(diǎn)擊“確定”按鈕。建立數(shù)據(jù)透視表框架。結(jié)果如圖6-2-5和6-2-6所示。6.2.1不同商品利潤(rùn)率分析STEP5：在“數(shù)據(jù)透視表字段”窗格中，勾選“商品編碼”、“銷售毛利”、“毛利率”三個(gè)字段，初建數(shù)據(jù)透視表，結(jié)果如圖6-2-7所示。6.2.1不同商品利潤(rùn)率分析STEP6：選擇“毛利率”下任意單元格數(shù)據(jù)，單擊右鍵，彈出的快捷菜單中選擇“值字段設(shè)置”，值字段匯總方式改為“平均值”，單擊“確定”按鈕，過(guò)程如圖6-2-8所示。6.2.1不同商品利潤(rùn)率分析STEP7：選擇“毛利率”下所有單元格數(shù)據(jù)，單擊右鍵，彈出的快捷菜單中選擇“設(shè)置單元格格式”，數(shù)字類型改為“百分比”，單擊“確定”按鈕，結(jié)果如圖6-2-9。6.2.1不同商品利潤(rùn)率分析STEP8：選中創(chuàng)立的數(shù)據(jù)透視表，將銷售毛利、平均毛利率數(shù)據(jù)進(jìn)行可視化處理，成果用雙坐標(biāo)圖展示，具體雙坐標(biāo)圖的制作過(guò)程可參照本教材“項(xiàng)目六商務(wù)數(shù)據(jù)可視化/6.2創(chuàng)建復(fù)雜數(shù)據(jù)圖/雙坐標(biāo)圖”，可視化結(jié)果如圖6-2-10所示：6.2.2不同商品收入與毛利對(duì)比分析STEP1：創(chuàng)建數(shù)據(jù)透視表。打開(kāi)“素材文件/項(xiàng)目6/銷售數(shù)據(jù)分析表.xlsx/店鋪月銷售報(bào)表工作表”，參照上一節(jié)數(shù)據(jù)透視表操作步驟，在“不同商品盈利能力”工作表中，創(chuàng)建不同商品“銷售總額”與“銷售總毛利”的數(shù)據(jù)透視表，創(chuàng)建結(jié)果如圖6-2-11。6.2.2不同商品收入與毛利對(duì)比分析STEP2：選中表格中任意單元格，單擊“插入”選項(xiàng)卡，再單擊”圖表”選項(xiàng)組的“推薦圖表”按鈕，彈出“插入圖表”對(duì)話框，過(guò)程如圖6-2-12，結(jié)果如圖6-2-13。6.2.2不同商品收入與毛利對(duì)比分析STEP3：選擇“組合圖”，并更改銷售總額圖表類型為“折線圖”，單擊“確定”按鈕，繪成“雙線組合圖”，過(guò)程如圖6-2-14，結(jié)果如圖6-2-15。6.2.2不同商品收入與毛利對(duì)比分析STEP4：選中圖中“圖例”，單擊右鍵，通過(guò)“設(shè)置圖例格式”將圖例放置在圖表底部；選擇圖中“求和項(xiàng)：銷售毛利”折線，單擊右鍵，通過(guò)“設(shè)置數(shù)據(jù)系列格式”，更改短劃線類型為“方點(diǎn)”。結(jié)果如圖6-2-16所示。6.2.3不同商品分配方案分析STEP1：打開(kāi)“素材文件/項(xiàng)目6/銷售數(shù)據(jù)分析表.xlsx/商品分配方案工作表”，右鍵單擊“文件”選項(xiàng)卡，選擇“自定義功能區(qū)”命令，如圖6-2-176.2.3不同商品分配方案分析STEP2：在彈出“Excel選項(xiàng)”對(duì)話框中，選擇“加載項(xiàng)”選項(xiàng)，單擊下方“轉(zhuǎn)到”按鈕，如圖6-2-18所示6.2.3不同商品分配方案分析STEP3：彈出“加載宏”對(duì)話框，選中“規(guī)劃求解加載項(xiàng)”復(fù)選框，然后單擊“確定”按鈕，如圖6-2-19。6.2.3不同商品分配方案分析STEP4：在C13單元格中輸入公式“=SUMPRODUCT(E4:E7,F4:F7)”，按【Enter】鍵，計(jì)算商品總收益，如圖6-2-20所示。6.2.3不同商品分配方案分析STEP5：在E11單元格中輸入公式“=SUMPRODUCT(C4:C7,F4:F7)”，按【Enter】鍵，計(jì)算商品實(shí)際投入成本，如圖6-2-21所示。6.2.3不同商品分配方案分析STEP6：在E12單元格中輸入公式“=SUMPRODUCT(C4:C7,F4:F7)”，按【Enter】鍵，計(jì)算商品實(shí)際銷售時(shí)間，如圖6-2-22所示。6.2.3不同商品分配方案分析STEP7：選擇“數(shù)據(jù)”選項(xiàng)卡，在“分析”組中單擊“規(guī)劃求解”按鈕，彈出“規(guī)劃求解參數(shù)”對(duì)話框，設(shè)置“設(shè)置目標(biāo)”參數(shù)為C13單元格，選中“最大”按鈕，過(guò)程如圖6-2-23、圖6-2-24所示。6.2.3不同商品分配方案分析STEP8：在“通過(guò)可更改單元格”選項(xiàng)中，設(shè)置可變單元格為$F$4:$F$7區(qū)域，如圖6-2-25所示。6.2.3不同商品分配方案分析STEP9：在“遵守約束”選項(xiàng)中，單擊“添加”按鈕，彈出“添加約束”對(duì)話框，設(shè)置“單元格引用”為E11單元格，“運(yùn)算符號(hào)”為“<=”，約束為C11單元格，單擊“添加”按鈕；如圖6-2-26所示。6.2.3不同商品分配方案分析STEP10：參照上步操作步驟，添加約束條件“E12<=C12”；添加約束條件“F4:F7>=1”；添加約束條件“F4:F7為int整數(shù)”，然后單擊“確定”按鈕，結(jié)果如圖6-2-27所示。6.2.3不同商品分配方案分析STEP11：選中“使無(wú)約束變量為非負(fù)數(shù)”復(fù)選框，在“選擇求解方法”下拉列表框中選擇“非線性GRG”選項(xiàng)，然后單擊“求解”按鈕，如圖6-2-28所示。6.2.3不同商品分配方案分析STEP12：彈出“規(guī)劃求解結(jié)果”對(duì)話框，在對(duì)話框中選中“保留規(guī)劃求解的解”單選按鈕，然后單擊“確定”按鈕，在F4:F7單元格區(qū)域查看結(jié)果，結(jié)果如圖6-2-29所示目錄1商品盈利情況統(tǒng)計(jì)與分析商品銷售數(shù)據(jù)統(tǒng)計(jì)與分析2商品退貨退款情況統(tǒng)計(jì)分析36.3.1不同商品退貨、退款金額統(tǒng)計(jì)分析6.