項(xiàng)目五商務(wù)數(shù)據(jù)可視化

18-10月-24學(xué)習(xí)目標(biāo)：①掌握柱形圖與條形圖的制作方法；②掌握繪制餅圖與復(fù)合餅圖的方法；③掌握繪制雷達(dá)圖的方法；④掌握繪制平行線圖、雙坐標(biāo)圖的方法；⑤掌握繪制旋風(fēng)圖與帕累托圖的方法。重點(diǎn)：①柱形圖與條形圖的制作方法；②繪制餅圖與復(fù)合餅圖的方法；③繪制雷達(dá)圖的方法；④繪制平行線圖、雙坐標(biāo)圖的方法；⑤繪制旋風(fēng)圖與帕累托圖的方法。難點(diǎn)：①繪制平行線圖、雙坐標(biāo)圖的方法；②繪制旋風(fēng)圖與帕累托圖的方法。目錄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)“各部門員工男女性別占比”表格，制作旋風(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)題為“各部門員工男女性別占比”。如圖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)用重慶大學(xué)出版社尹愛(ài)飛，杜轉(zhuǎn)萍主編本書(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ì)與分析18-10月-24學(xué)習(xí)目標(biāo)：①了解銷售數(shù)據(jù)統(tǒng)計(jì)與分析的方法；②熟悉不同商品銷售的統(tǒng)計(jì)與分析的方法；③

熟悉同類商品銷售統(tǒng)計(jì)與分析的方法；④掌握商品退貨、退款情況的統(tǒng)計(jì)與分析的方法。重點(diǎn)：不同商品銷售的統(tǒng)計(jì)與分析的方法。難點(diǎn)：商品退貨、退款情況的統(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.3.2不同商品退貨、退款原因統(tǒng)計(jì)分析6.3.1不同商品退貨、退款金額統(tǒng)計(jì)分析STEP1：打開(kāi)“素材文件/項(xiàng)目6/商品退款分析表.xlsx/退款清單工作表”，添加“退款率”字段，如圖6-3-1所示。6.3.1不同商品退貨、退款金額統(tǒng)計(jì)分析STEP2：根據(jù)退款率的內(nèi)涵，計(jì)算各商品退款率。選擇G2單元格，在編輯欄中輸入公式“=F2/C2”，并按回車鍵確認(rèn)，計(jì)算出退款率，然后使用填充柄將公式復(fù)制到其他單元格中，如圖6-3-2所示。6.3.1不同商品退貨、退款金額統(tǒng)計(jì)分析STEP3：創(chuàng)建數(shù)據(jù)透視表。選擇表格中任意單元格，單擊“插入”選項(xiàng)卡，在“表格”選項(xiàng)組中，單擊“插入數(shù)據(jù)透視表“按鈕。選擇數(shù)據(jù)透視表位置J4單元格，然后按【Enter】鍵，點(diǎn)擊“確定”按鈕。結(jié)果如圖6-3-3所示。6.3.1不同商品退貨、退款金額統(tǒng)計(jì)分析STEP4：在“數(shù)據(jù)透視表字段”窗格中，勾選“商品名稱”、“退款金額”、“退款率”三個(gè)字段，初建數(shù)據(jù)透視表，結(jié)果如圖6-3-4所示。6.3.1不同商品退貨、退款金額統(tǒng)計(jì)分析STEP5：選擇“退款率”下任意單元格數(shù)據(jù)，單擊右鍵，彈出的快捷菜單中選擇“值字段設(shè)置”，值字段匯總方式改為“平均值”，單擊“確定”按鈕。過(guò)程如圖6-3-5所示。6.3.1不同商品退貨、退款金額統(tǒng)計(jì)分析STEP6：選擇“退款率”下任意單元格數(shù)據(jù)，單擊右鍵，彈出的快捷菜單中選擇“值字段設(shè)置”，值字段匯總方式改為“平均值”，單擊“確定”按鈕。過(guò)程如圖6-3-5所示。6.3.2不同商品退貨、退款原因統(tǒng)計(jì)分析STEP1：打開(kāi)“素材文件/項(xiàng)目6/商品退款分析表.xlsx/退款清單工作表”，選擇E2:E27單元格區(qū)域，點(diǎn)右鍵選擇“復(fù)制”，或者按【Ctrl+C】快捷鍵復(fù)制選中數(shù)據(jù)，如圖6-3-7所示。6.3.2不同商品退貨、退款原因統(tǒng)計(jì)分析STEP2：選擇J15單元格，按【Enter】鍵粘貼數(shù)據(jù)。打開(kāi)“數(shù)據(jù)“選項(xiàng)卡，單擊“數(shù)據(jù)工具”組中的“刪除重復(fù)項(xiàng)“按鈕，刪除新粘貼數(shù)據(jù)中的重復(fù)數(shù)據(jù)，結(jié)果如圖6-3-8所示。6.3.2不同商品退貨、退款原因統(tǒng)計(jì)分析STEP3：選中K15單元格，在編輯欄中輸入公“=COUNTIF($E$2:$E$27,J15)”，并按回車鍵確認(rèn)，計(jì)算出退款次數(shù)，然后使用填充柄將公式復(fù)制到其他單元格中，結(jié)果如圖6-3-9所示。6.3.2不同商品退貨、退款原因統(tǒng)計(jì)分析STEP4：選中J14:K21區(qū)域內(nèi)任意單元格，點(diǎn)擊“插入”選項(xiàng)卡，再單擊”圖表”選項(xiàng)組，插入餅狀圖，如圖6-3-10所示。6.3.2不同商品退貨、退款原因統(tǒng)計(jì)分析STEP5：添加數(shù)據(jù)標(biāo)簽。選中餅狀圖，單擊右鍵，在彈出的快捷菜單中選擇“添加數(shù)據(jù)標(biāo)簽”，然后通過(guò)設(shè)置“數(shù)據(jù)表格格式”，將“類別名稱”、“百分比”均添加到圖中，結(jié)果如圖6-3-11所示。6.3.2不同商品退貨、退款原因統(tǒng)計(jì)分析STEP6：調(diào)整圖例位置。選中餅狀圖中的圖例，單擊右鍵，在彈出的快捷菜單中選擇“設(shè)置圖例格式”，在彈出的右側(cè)窗格中，勾選圖例位置“靠左”按鈕，效果如圖6-3-12所示。商務(wù)數(shù)據(jù)分析與應(yīng)用重慶大學(xué)出版社尹愛(ài)飛，杜轉(zhuǎn)萍主編本書(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)目七

商品銷售費(fèi)用分析18-10月-24學(xué)習(xí)目標(biāo)：①掌握計(jì)算銷售費(fèi)用的相關(guān)指標(biāo)的方法；②掌握對(duì)不同指標(biāo)進(jìn)行相關(guān)性分析的方法。重點(diǎn)：計(jì)算銷售費(fèi)用的相關(guān)指標(biāo)的方法。難點(diǎn)：對(duì)不同指標(biāo)進(jìn)行相關(guān)性分析的方法。銷售費(fèi)用企業(yè)銷售商品和材料、提供勞務(wù)過(guò)程中發(fā)生的各項(xiàng)費(fèi)用。銷售費(fèi)用投入得當(dāng)可以促進(jìn)商品的銷售，提高商品的銷售利潤(rùn)，但若投入不夠或過(guò)多，可能會(huì)影響商品的銷售情況，進(jìn)而影響商家的收入及利潤(rùn)。通過(guò)分析商品收入、利潤(rùn)與銷售費(fèi)用的相關(guān)性、商品銷售費(fèi)用的具體支出等情況，可以對(duì)銷售費(fèi)用的投入做評(píng)估與調(diào)整。目錄1各項(xiàng)費(fèi)用支出對(duì)比各項(xiàng)費(fèi)用相關(guān)性分析27.1.1完善相關(guān)數(shù)據(jù)7.1.2回歸分析銷售收入與成本、毛利潤(rùn)與銷售費(fèi)用STEP1：打開(kāi)“素材文件/項(xiàng)目7/銷售費(fèi)用分析表.xlsx”，完善表格中數(shù)據(jù)。根據(jù)已有數(shù)據(jù)及“毛利潤(rùn)”“銷售成本率”“銷售費(fèi)用率”“毛利率”四個(gè)字段的內(nèi)涵，分別計(jì)算出四個(gè)字段對(duì)應(yīng)的數(shù)據(jù)。選中F4單元格，在編輯欄中輸入函數(shù)“=B4-C4-D4”，并按回車鍵確認(rèn)，計(jì)算出銷售成本，然后使用填充柄將公式復(fù)制到其他單元格中，結(jié)果如圖7-1-1所示。7.1.1完善相關(guān)數(shù)據(jù)STEP2：同上步，選中G4單元格，在編輯欄中輸入函數(shù)“=C4/B4”，并按回車鍵確認(rèn)，計(jì)算出銷售成本率，然后使用填充柄將公式復(fù)制到其他單元格中；選中H4單元格，在編輯欄中輸入函數(shù)“=D4/B4”，并按回車鍵確認(rèn)，計(jì)算出銷售費(fèi)用率，然后使用填充柄將公式復(fù)制到其他單元格中；選中I4單元格，在編輯欄中輸入函數(shù)“=E4/B4”，并按回車鍵確認(rèn)，計(jì)算出銷售稅金率，然后使用填充柄將公式復(fù)制到其他單元格中；選中J4單元格，在編輯欄中輸入函數(shù)“=F4/B4”，并按回車鍵確認(rèn)，計(jì)算出毛利率，然后使用填充柄將公式復(fù)制到其他單元格中，結(jié)果如圖7-1-2所示。7.1.1完善相關(guān)數(shù)據(jù)STEP1：打開(kāi)“素材文件/項(xiàng)目7/銷售費(fèi)用分析表.xlsx”，選擇A12單元格，輸入文本“銷售收入與銷售成本相關(guān)性分析”，按【Enter】鍵；選擇A13單元格，輸入文本“回歸函數(shù)參數(shù)”，按【Enter】鍵；選擇A15單元格，輸入文本“回歸函數(shù)”，按【Enter】鍵，如圖7-1-3所示。7.1.2回歸分析銷售收入與成本、毛利潤(rùn)與銷售費(fèi)用STEP2：求取銷售收入與銷售成本回歸參數(shù)。選擇B13:C13單元格，在編輯欄中輸入公“=LINEST(B4:B9,C4:C9)”，如圖7-1-4所示。7.1.2回歸分析銷售收入與成本、毛利潤(rùn)與銷售費(fèi)用STEP3：按【Ctrl+Shift+Enter】組合鍵，得到銷售收入與銷售成本存在的線性關(guān)系下的直線表達(dá)式的斜率和截距兩個(gè)參數(shù)，結(jié)果如圖7-1-5所示。7.1.2回歸分析銷售收入與成本、毛利潤(rùn)與銷售費(fèi)用STEP4：運(yùn)用鏈接函數(shù)CONCATENATE構(gòu)建回歸函數(shù)。選擇B15單元格，在編輯欄中輸入函數(shù)“CONCATENATE("Y=",B13,"X+",C13)”，按【Enter】鍵，得出銷售收入與銷售成本之間的回歸函數(shù)，其中Y表示銷售收入，X表示銷售成本，結(jié)果如圖7-1-6所示。7.1.2回歸分析銷售收入與成本、毛利潤(rùn)與銷售費(fèi)用STEP5：分別在A17、B17單元格輸入文本“相關(guān)系數(shù)”“相關(guān)情況”，然后選擇A17單元格，下編輯欄中輸入公式“=CORREL(B4:B9,C4:C9)”，按【Enter】鍵，得到銷售收入與銷售成本相關(guān)系數(shù)，結(jié)果如圖7-1-7所示。7.1.2回歸分析銷售收入與成本、毛利潤(rùn)與銷售費(fèi)用STEP6：根據(jù)相關(guān)性標(biāo)準(zhǔn)判斷銷售收入與銷售成本之間的相關(guān)性情況。選擇B17單元格，在編輯欄中輸入公式“=IF(ABS(A18)>=0.3,"相關(guān)","不相關(guān)")”，按【Enter】鍵，結(jié)果如圖7-1-8所示。7.1.2回歸分析銷售收入與成本、毛利潤(rùn)與銷售費(fèi)用STEP7：參照步驟七、步驟八，分析毛利潤(rùn)與銷售費(fèi)用的相關(guān)性，結(jié)果如圖7-1-9所示。7.1.2回歸分析銷售收入與成本、毛利潤(rùn)與銷售費(fèi)用STEP8：參照教材項(xiàng)目五知識(shí)點(diǎn)，將毛利潤(rùn)、銷售費(fèi)用數(shù)據(jù)進(jìn)行可視化展示，結(jié)果如圖7-1-10所示。7.1.2回歸分析銷售收入與成本、毛利潤(rùn)與銷售費(fèi)用目錄1各項(xiàng)費(fèi)用支出對(duì)比各項(xiàng)費(fèi)用相關(guān)性分析27.2.1統(tǒng)計(jì)各項(xiàng)費(fèi)用7.2.2數(shù)據(jù)透視圖分析產(chǎn)品成本7.2.1統(tǒng)計(jì)各項(xiàng)費(fèi)用STEP1：打開(kāi)“素材文件/項(xiàng)目8/銷售費(fèi)用分析表.xlsx/各項(xiàng)費(fèi)用明細(xì)表工作表”，增加“平均值”字段，選擇I3單元格，在編輯欄中輸入函“=AVERAGE(C3:H3)”，并按回車鍵確認(rèn)，計(jì)算出產(chǎn)品基礎(chǔ)成本平均值，然后使用填充柄將公式復(fù)制到其他單元格中，如圖7-2-1所示。7.2.1統(tǒng)計(jì)各項(xiàng)費(fèi)用STEP2：選中表格中任意單元格，單擊“插入”選項(xiàng)卡，再單擊“圖表”選項(xiàng)組的“數(shù)據(jù)透視圖”，彈出“創(chuàng)建數(shù)據(jù)透視圖”選項(xiàng)卡，如圖7-2-2所示。7.2.1統(tǒng)計(jì)各項(xiàng)費(fèi)用STEP3：選擇數(shù)據(jù)透視圖位置，勾選“現(xiàn)有工作表”按鈕，鼠標(biāo)選擇K9單元格，然后按【Enter】鍵，點(diǎn)擊“確定”按鈕。建立數(shù)據(jù)透視圖框架，結(jié)果如圖7-2-3所示。7.2.1統(tǒng)計(jì)各項(xiàng)費(fèi)用STEP4：在“數(shù)據(jù)透視圖字段”窗格中，勾選“費(fèi)用類型”“平均值”兩個(gè)字段，構(gòu)建費(fèi)用類型數(shù)據(jù)透視圖，結(jié)果如圖7-2-4所示。7.2.1統(tǒng)計(jì)各項(xiàng)費(fèi)用STEP5：創(chuàng)建數(shù)據(jù)透視表，統(tǒng)計(jì)各項(xiàng)費(fèi)用。選中表格中任意單元格，單擊“插入”選項(xiàng)卡，再單擊“表格”選項(xiàng)組的“數(shù)據(jù)透視表”，彈出“創(chuàng)建數(shù)據(jù)透視表”選項(xiàng)卡。選擇數(shù)據(jù)透視表位置，勾選“現(xiàn)有工作表”按鈕，鼠標(biāo)選擇K20單元格，然后按【Enter】鍵，點(diǎn)擊“確定”按鈕。建立數(shù)據(jù)透視表框架，結(jié)果如圖7-2-5所示。7.2.1統(tǒng)計(jì)各項(xiàng)費(fèi)用STEP6：在“數(shù)據(jù)透視表字段”窗格中，勾選“費(fèi)用類型”“費(fèi)用名稱”“單肩包”“手拎包”“斜挎包”五個(gè)字段，構(gòu)建三種包包費(fèi)用明細(xì)數(shù)據(jù)透視表，結(jié)果如圖7-2-6所示。7.2.2數(shù)據(jù)透視圖分析產(chǎn)品成本STEP1：打開(kāi)“素材文件/項(xiàng)目7/銷售費(fèi)用分析表.xlsx/各項(xiàng)費(fèi)用明細(xì)表工作表”，在工作表中選擇任意單元格，在“插入”選項(xiàng)卡的“圖表”組中單擊“數(shù)據(jù)透視圖”，彈出“數(shù)據(jù)透視圖”對(duì)話框，選擇放置數(shù)據(jù)透視圖位置欄中勾選“現(xiàn)有工作表”單選按鈕，用鼠標(biāo)選擇B25單元格，按【Enter】鍵，然后單擊“確定”按鈕，構(gòu)建數(shù)據(jù)透視圖框架，過(guò)程如圖7-2-7所示，結(jié)果如圖7-2-8所示。7.2.2數(shù)據(jù)透視圖分析產(chǎn)品成本STEP2：在“數(shù)據(jù)透視圖窗格”中勾選除“平均值”以外的所有字段，此時(shí)“費(fèi)用類型”“費(fèi)用名稱”字段自動(dòng)出現(xiàn)在“軸字段（分類）”列表框，其余帶有數(shù)值的字段自動(dòng)到“數(shù)值”列表框參與計(jì)算，如圖7-2-9所示。7.2.2數(shù)據(jù)透視圖分析產(chǎn)品成本STEP3：?jiǎn)螕簟皵?shù)據(jù)透視圖分析”選項(xiàng)卡，在“隱藏/顯示”組中單擊“字段按鈕”下拉按鈕，在打開(kāi)的下拉列表中取消選中“顯示圖例字段按鈕”復(fù)選框，按照同樣的操作方法，繼續(xù)取消選中“顯示值字段按鈕”，如圖7-2-10所示。7.2.2數(shù)據(jù)透視圖分析產(chǎn)品成本STEP4：?jiǎn)螕魯?shù)據(jù)透視圖中的“費(fèi)用類型”按鈕，在打開(kāi)的列表中取消選中“全選”復(fù)選框，然后在列表中勾選“推廣費(fèi)用”復(fù)選框，然后單擊“確定”按鈕，過(guò)程如圖7-2-11所示，結(jié)果如圖7-2-12所示。商務(wù)數(shù)據(jù)分析與應(yīng)用重慶大學(xué)出版社尹愛(ài)飛，杜轉(zhuǎn)萍主編本書(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)目八

商品庫(kù)存分析18-10月-24學(xué)習(xí)目標(biāo)：①掌握單一商品信息查詢表的制作方法；②掌握庫(kù)存商品數(shù)量統(tǒng)計(jì)分析的相關(guān)方法；③掌握庫(kù)存周轉(zhuǎn)率、庫(kù)存周轉(zhuǎn)天數(shù)的概念及計(jì)算公式；④掌握存銷比、動(dòng)銷率的概念及計(jì)算公式。重點(diǎn)：①單一商品信息查詢表的制作方法；②庫(kù)存商品數(shù)量統(tǒng)計(jì)分析的相關(guān)方法；難點(diǎn)：①庫(kù)存周轉(zhuǎn)率、庫(kù)存周轉(zhuǎn)天數(shù)的計(jì)算公式；②

存銷比、動(dòng)銷率的計(jì)算公式。賣家對(duì)于商品庫(kù)存的分析有助于正確判斷銷售商品的供給能力，同時(shí)也可以判斷商品銷售的暢銷與滯銷狀況，及時(shí)調(diào)整商品的進(jìn)貨種類，處理積壓的庫(kù)存商品，補(bǔ)給缺貨的商品，保證線上線下商品銷售的順利進(jìn)行。請(qǐng)根據(jù)店鋪“商品庫(kù)存表”相關(guān)數(shù)據(jù)計(jì)算庫(kù)存周轉(zhuǎn)率、存銷比、動(dòng)銷率等指標(biāo)，判斷庫(kù)存商品的結(jié)構(gòu)與供給狀況，同時(shí)制定庫(kù)存商品查詢表，以便快速查詢庫(kù)存商品的基本信息。目錄1庫(kù)存指標(biāo)分析商品庫(kù)存數(shù)據(jù)分析28.1.1單一庫(kù)存商品信息查詢8.1.2商品庫(kù)存數(shù)量統(tǒng)計(jì)分析STEP1：打開(kāi)素材文件/項(xiàng)目8/單一商品查詢表，在表格空白處編制單一商品庫(kù)存信息查詢表的框架，包含“產(chǎn)品名稱”、“采購(gòu)金額”、“廠商”、“保質(zhì)期”、“成本單價(jià)”等需要查詢和顯示的庫(kù)存商品信息，如圖8-1-1所示。8.1.1單一庫(kù)存商品信息查詢STEP2：進(jìn)行數(shù)據(jù)有效性的設(shè)置。選中“產(chǎn)品名稱”下面空白單元格A24，點(diǎn)擊“數(shù)據(jù)”選項(xiàng)卡，選擇“數(shù)據(jù)驗(yàn)證”，如圖8-1-2所示。在彈出的對(duì)話框里面設(shè)置驗(yàn)證條件。點(diǎn)擊“允許”下拉菜單，選擇“序列”，點(diǎn)擊“來(lái)源”處箭頭，選中“A6：A20”區(qū)域范圍，即選中產(chǎn)品名稱一列，如圖8-1-3所示。點(diǎn)擊A24單元格，產(chǎn)品名稱全部已經(jīng)列在下拉菜單中。8.1.1單一庫(kù)存商品信息查詢STEP3：進(jìn)行相關(guān)信息的匹配。在A24單元格中任選一種產(chǎn)品名稱如“蜜桃汁飲料”，在B24單元格中輸入“=VLOOKUP”，依次輸入函數(shù)的各個(gè)部分，如圖8-1-4所示。函數(shù)語(yǔ)法第一部分中“A24”為查找的值；函數(shù)第二部分語(yǔ)法為查找的區(qū)域范圍“A2：J20”，查找的區(qū)域范圍首列應(yīng)為包含“A24”產(chǎn)品名稱所在的列數(shù)；函數(shù)第三部分為匹配的列數(shù)。由于B24單元格對(duì)應(yīng)的商品信息為“成本單價(jià)”，而“成本單價(jià)”位于選中區(qū)域的第7列，因此函數(shù)語(yǔ)法第三部分為“7”；函數(shù)第四部分為精確匹配，即“FALSE”。按“Enter”鍵，則單元格“B24”顯示結(jié)果為“40.5”元。8.1.1單一庫(kù)存商品信息查詢STEP4：進(jìn)行公式復(fù)制。選中B24單元格，將公式中的前兩部分語(yǔ)法鎖定。然后按“Enter”回車鍵，如圖8-1-5所示。選中B24單元格向右拖動(dòng)鼠標(biāo)，進(jìn)行公式的初始復(fù)制，并依次修改復(fù)制的函數(shù)中匹配的列數(shù)，結(jié)果如圖8-1-6所示。8.1.1單一庫(kù)存商品信息查詢STEP5：進(jìn)行信息查詢。點(diǎn)擊A24單元格，任選一類產(chǎn)品，當(dāng)產(chǎn)品名稱變化時(shí)，表中的信息也進(jìn)行了相應(yīng)的變化，單一商品查詢表就制作完成了，結(jié)果如圖8-1-7所示。8.1.1單一庫(kù)存商品信息查詢STEP1：打開(kāi)素材文件/項(xiàng)目8/“庫(kù)存數(shù)據(jù)分析”.xlsx/庫(kù)存數(shù)據(jù),點(diǎn)擊“數(shù)據(jù)”選項(xiàng)卡，選擇“分類匯總”功能。在彈出的對(duì)話框中分類字段選擇“產(chǎn)品名稱”，匯總方式選擇“求和”，匯總項(xiàng)選擇“期末庫(kù)存數(shù)量”，如圖8-1-8所示。分類匯總的結(jié)果如圖8-1-9所示。8.1.2商品庫(kù)存數(shù)量統(tǒng)計(jì)分析STEP2：打開(kāi)素材文件/項(xiàng)目8/“庫(kù)存數(shù)據(jù)分析”.xlsx/庫(kù)存狀態(tài)，根據(jù)分類匯總的結(jié)果統(tǒng)計(jì)各個(gè)產(chǎn)品系列的期末庫(kù)存數(shù)量，與標(biāo)準(zhǔn)庫(kù)存量進(jìn)行比較判斷各產(chǎn)品系列的庫(kù)存狀態(tài)。在H8單元格中輸入IF公式：

=IF(G8>F8,"庫(kù)存充足","庫(kù)存不足"),按Enter鍵結(jié)束。選中H8單元格，按住鼠標(biāo)左鍵向下拖動(dòng)，進(jìn)行公式的復(fù)制。每種產(chǎn)品系列的庫(kù)存狀態(tài)則顯示在單元格中，如圖8-1-10所示。8.1.2商品庫(kù)存數(shù)量統(tǒng)計(jì)分析STEP3：選中E7：G11表格區(qū)域，插入柱形圖，如圖8-1-11所示。從圖中可知，飲料產(chǎn)品系列的庫(kù)存嚴(yán)重不足，要馬上進(jìn)行補(bǔ)貨；自熱火鍋產(chǎn)品系列庫(kù)存相對(duì)充足；火鍋調(diào)料和腌制品產(chǎn)品系列的庫(kù)存量接近標(biāo)準(zhǔn)庫(kù)存量，要注意及時(shí)補(bǔ)貨。8.1.2商品庫(kù)存數(shù)量統(tǒng)計(jì)分析目錄1庫(kù)存指標(biāo)分析商品庫(kù)存數(shù)據(jù)分析2庫(kù)存周轉(zhuǎn)率是反映企業(yè)庫(kù)存周轉(zhuǎn)快慢程度的一個(gè)指標(biāo)，指的是庫(kù)存在一定周期內(nèi)周轉(zhuǎn)的次數(shù)。庫(kù)存周轉(zhuǎn)率越高，說(shuō)明存貨的周轉(zhuǎn)速度越快，從成本投入到商品銷售再到資金回流的周期越短，銷售情況越好。庫(kù)存周轉(zhuǎn)率是分析庫(kù)存非常重要的一個(gè)指標(biāo)，庫(kù)存周轉(zhuǎn)率提高，庫(kù)存周轉(zhuǎn)加快，庫(kù)存占用資金減少，企業(yè)現(xiàn)金流提高，資金流通速度加快；同時(shí)庫(kù)存持有成本下降，企業(yè)利潤(rùn)得到提升，因此投入資金一定的情況下，庫(kù)存周轉(zhuǎn)率越高，企業(yè)的投資回報(bào)率越高。同時(shí)庫(kù)存周轉(zhuǎn)率是一個(gè)相對(duì)系數(shù)，可以和外部企業(yè)進(jìn)行橫向?qū)Ρ?，從而了解自己企業(yè)庫(kù)存周轉(zhuǎn)的合理性。

庫(kù)存周轉(zhuǎn)率=銷貨成本/平均存貨余額8.2.1庫(kù)存周轉(zhuǎn)率與周轉(zhuǎn)天數(shù)庫(kù)存周轉(zhuǎn)天數(shù)是企業(yè)從取得存貨開(kāi)始，直到消耗、銷售為止所經(jīng)歷的天數(shù)。庫(kù)存周轉(zhuǎn)天數(shù)=周期天數(shù)/庫(kù)存周轉(zhuǎn)率=（周期天數(shù)*平均存貨余額）/銷貨成本8.2.1庫(kù)存周轉(zhuǎn)率與周轉(zhuǎn)天數(shù)STEP1：打開(kāi)素材文件/項(xiàng)目8/庫(kù)存周轉(zhuǎn)率與周轉(zhuǎn)天數(shù).xlsx，計(jì)算期末庫(kù)存數(shù)量，期末庫(kù)存數(shù)量=期初庫(kù)存數(shù)量+入庫(kù)數(shù)量-出庫(kù)數(shù)量。在H3單元格輸入公式“=E3+F3-G3”,并按回車鍵。選中H3單元格，當(dāng)光標(biāo)變?yōu)楹谑謺r(shí)向下拖動(dòng)鼠標(biāo)進(jìn)行公式復(fù)制，計(jì)算出不同商品的期末庫(kù)存數(shù)量，結(jié)果如圖8-2-1所示。8.2.1庫(kù)存周轉(zhuǎn)率與周轉(zhuǎn)天數(shù)STEP2：計(jì)算平均庫(kù)存余額，平均庫(kù)存余額=（期初庫(kù)存余額+期末庫(kù)存余額）/2。期初庫(kù)存余額=期初庫(kù)存數(shù)量*成本單價(jià)，期末庫(kù)存余額=期末庫(kù)存數(shù)量*成本單價(jià)，因此，在I3單元格中輸入公式“=(E3*D3+H3*D3)/2”，并按回車鍵。選中I3單元格，當(dāng)光標(biāo)變?yōu)楹谑謺r(shí)向下拖動(dòng)鼠標(biāo)進(jìn)行公式復(fù)制，計(jì)算出不同商品的平均庫(kù)存余額，結(jié)果如圖8-2-2所示。8.2.1庫(kù)存周轉(zhuǎn)率與周轉(zhuǎn)天數(shù)STEP3：計(jì)算銷貨成本，銷貨成本=銷貨數(shù)量*成本單價(jià)。這里的銷貨數(shù)量等于出庫(kù)數(shù)量。在J3單元格中輸入公式“=G3*D3”，并按回車鍵。選中J3單元格，當(dāng)光標(biāo)變?yōu)楹谑謺r(shí)向下拖動(dòng)鼠標(biāo)進(jìn)行公式復(fù)制，計(jì)算出不同商品的銷貨成本，結(jié)果如圖8-2-3所示。8.2.1庫(kù)存周轉(zhuǎn)率與周轉(zhuǎn)天數(shù)STEP4：計(jì)算庫(kù)存周轉(zhuǎn)率，庫(kù)存周轉(zhuǎn)率=銷貨成本/平均庫(kù)存余額。在K3單元格中輸入公式“=J3/I3”，并按回車鍵。選中K3單元格，當(dāng)光標(biāo)變?yōu)楹谑謺r(shí)向下拖動(dòng)鼠標(biāo)進(jìn)行公式復(fù)制，計(jì)算出不同商品的庫(kù)存周轉(zhuǎn)率，結(jié)果如圖8-2-4所示。8.2.1庫(kù)存周轉(zhuǎn)率與周轉(zhuǎn)天數(shù)STEP5：計(jì)算庫(kù)存周轉(zhuǎn)天數(shù)，庫(kù)存周轉(zhuǎn)天數(shù)=周期天數(shù)/庫(kù)存周轉(zhuǎn)率。在L3單元格中輸入公式“=30/K3”，并按回車鍵。選中L3單元格，當(dāng)光標(biāo)變?yōu)楹谑謺r(shí)向下拖動(dòng)鼠標(biāo)進(jìn)行公式復(fù)制，計(jì)算出不同商品的庫(kù)存周轉(zhuǎn)天數(shù)，結(jié)果如圖8-2-5所示。8.2.1庫(kù)存周轉(zhuǎn)率與周轉(zhuǎn)天數(shù)存銷比（庫(kù)銷比）是指在一個(gè)周期內(nèi)，商品平均庫(kù)存或本周期期末庫(kù)存與周期內(nèi)總銷售的比值，是用來(lái)反映商品即時(shí)庫(kù)存狀況的相對(duì)數(shù)。而更為精確的法則是使用日均庫(kù)存和日均銷售的數(shù)據(jù)來(lái)計(jì)算，從而反映當(dāng)前的庫(kù)存銷售比例。存銷比=期末庫(kù)存金額（or數(shù)量）/周期內(nèi)商品銷售金額（or數(shù)量）公式中的周期可以是周、月、季度等等。存銷比過(guò)高，庫(kù)存資金占用過(guò)大，存銷比過(guò)低，則容易備貨不足，損失銷售機(jī)會(huì)。8.2.2存銷比與動(dòng)銷率動(dòng)銷率是指銷售的商品種數(shù)與倉(cāng)庫(kù)所有商品種數(shù)的比值。動(dòng)銷率=（動(dòng)銷品種數(shù)/倉(cāng)庫(kù)總品種數(shù)）*100%一般情況下，動(dòng)銷率越高，企業(yè)商品周轉(zhuǎn)就越快；動(dòng)銷率越低則代表周轉(zhuǎn)速度越慢。動(dòng)銷率能夠作為商品經(jīng)營(yíng)深度與寬度的參考，動(dòng)銷率低的商品企業(yè)則不需要花費(fèi)大量時(shí)間和精力去經(jīng)營(yíng)。8.2.2存銷比與動(dòng)銷率STEP1：第一種計(jì)算方式：存銷比=期末庫(kù)存數(shù)量/周期內(nèi)商品銷售數(shù)量打開(kāi)“素材文件/項(xiàng)目8/存銷比與動(dòng)銷率.xlsx，在L3單元格中輸入公式“=K3/J3”，然后按Enter鍵，結(jié)果顯示為1.8，如圖8-2-6所示。選中L3單元格，按下鼠標(biāo)左鍵拖動(dòng)公式至L4:L20區(qū)域，通過(guò)公式的復(fù)制計(jì)算每種產(chǎn)品的存銷比，結(jié)果如圖8-2-7所示。8.2.2存銷比與動(dòng)銷率STEP2：第二種計(jì)算方式：存銷比=期末庫(kù)存金額/周期內(nèi)商品銷售金額打開(kāi)“素材文件/項(xiàng)目8/庫(kù)存數(shù)據(jù)分析.xlsx，在M3單元格中輸入公式“=(K3*G3)/(J3*E3)”，然后按Enter鍵，如圖8-2-8所示。選中M3單元格，按下鼠標(biāo)左鍵拖動(dòng)公式至M4:M20區(qū)域，通過(guò)公式的復(fù)制計(jì)算每種產(chǎn)品的存銷比，結(jié)果如圖8-2-9所示。其中，K3*G3為期末庫(kù)存金額的計(jì)算，等于期末庫(kù)存數(shù)量與產(chǎn)品成本單價(jià)的乘積；J3*E3為周期內(nèi)商品銷售金額的計(jì)算，等于出庫(kù)數(shù)量與銷售單價(jià)的乘積。在此，進(jìn)行了出庫(kù)數(shù)量等于銷售數(shù)量的假設(shè)。8.2.2存銷比與動(dòng)銷率STEP1：首先，點(diǎn)擊“開(kāi)始”選項(xiàng)卡下面的“條件格式”，在下拉菜單中選擇“突出顯示單元格規(guī)則”中的“等于”選項(xiàng)。設(shè)置值為0，顏色可選擇默認(rèn)顏色或者自行進(jìn)行顏色的設(shè)置，如圖8-2-10所示。點(diǎn)擊“確定”，庫(kù)存數(shù)據(jù)分析表中出庫(kù)數(shù)量為0的對(duì)應(yīng)單元格則被標(biāo)記出來(lái)，結(jié)果如圖8-2-11所示。8.2.2存銷比與動(dòng)銷率STEP2：在庫(kù)存數(shù)據(jù)分析表中查看庫(kù)存商品種類的總個(gè)數(shù)為18種，如果商品種類較多，可通過(guò)數(shù)據(jù)透視表進(jìn)行產(chǎn)品數(shù)量的統(tǒng)計(jì)。利用數(shù)據(jù)透視表分析的結(jié)果如下圖8-2-12所示。8.2.2存銷比與動(dòng)銷率STEP3：進(jìn)行動(dòng)銷率的計(jì)算。在18種商品中有2種商品沒(méi)有產(chǎn)生過(guò)銷售記錄，則動(dòng)銷品種數(shù)為16種，帶入動(dòng)銷率計(jì)算公式：動(dòng)銷率=16/18=88.89%8.2.2存銷比與動(dòng)銷率商務(wù)數(shù)據(jù)分析與應(yīng)用重慶大學(xué)出版社尹愛(ài)飛，杜轉(zhuǎn)萍主編本書(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)目九

銷售市場(chǎng)預(yù)測(cè)分析18-10月-24學(xué)習(xí)目標(biāo)：①熟悉趨勢(shì)線的基本類型；②熟悉線性趨勢(shì)線的基本操作步驟；③掌握移動(dòng)平均法、指數(shù)平滑法的方法原理；④熟悉季節(jié)指數(shù)法的計(jì)算步驟。重點(diǎn)：線性趨勢(shì)線的基本操作步驟。難點(diǎn)：①移動(dòng)平均法、指數(shù)平滑法的方法原理；②季節(jié)指數(shù)法的計(jì)算步驟。

基于店鋪以往銷售數(shù)據(jù)，同時(shí)運(yùn)用科學(xué)的數(shù)據(jù)預(yù)測(cè)分析方法，能夠有效的進(jìn)行商品銷售趨勢(shì)預(yù)測(cè)，對(duì)企業(yè)的經(jīng)營(yíng)管理提供有力的決策根據(jù)。在EXCEL中可以根據(jù)數(shù)據(jù)的不同分布特征運(yùn)用不同類型的趨勢(shì)線進(jìn)行商品銷售預(yù)測(cè)，同時(shí)還可以運(yùn)用移動(dòng)平均法、指數(shù)平滑法、季節(jié)波動(dòng)法等進(jìn)行預(yù)測(cè)分析。

根據(jù)“商品銷售信息表”中數(shù)據(jù)，運(yùn)用合適的數(shù)據(jù)預(yù)測(cè)方法，對(duì)商品未來(lái)銷售數(shù)量和銷售趨勢(shì)進(jìn)行預(yù)測(cè)分析。目錄1市場(chǎng)銷量預(yù)測(cè)分析店鋪銷量分析39.1.1認(rèn)識(shí)趨勢(shì)線的類型9.1.2利用線性趨勢(shì)線預(yù)測(cè)商品銷售額Excel中的趨勢(shì)線分為6種不同的類型：9.1.1趨勢(shì)線的基本類型指數(shù)趨勢(shì)線線性趨勢(shì)線對(duì)數(shù)趨勢(shì)線多項(xiàng)式趨勢(shì)線冪趨勢(shì)線移動(dòng)平均趨勢(shì)線STEP1：打開(kāi)素材/項(xiàng)目9/線性趨勢(shì)線.xlsx，表中已知2010-2022年的歷年銷售金額。觀察數(shù)據(jù)，可見(jiàn)13年的銷售金額數(shù)據(jù)呈平穩(wěn)增長(zhǎng)態(tài)勢(shì)，如圖9-1-1所示。STEP2：拖動(dòng)鼠標(biāo)，選中A1:B15數(shù)據(jù)區(qū)域，點(diǎn)擊“插入”菜單下的“散點(diǎn)圖”按鈕。單擊鼠標(biāo)左鍵，選擇散點(diǎn)圖下的“僅帶數(shù)據(jù)標(biāo)記的散點(diǎn)圖”。得到如圖9-1-2所示的歷年銷售額數(shù)據(jù)散點(diǎn)圖。9.1.2利用線性趨勢(shì)線進(jìn)行銷售額預(yù)測(cè)STEP3：?jiǎn)螕羰髽?biāo)左鍵，選中散點(diǎn)圖的數(shù)據(jù)點(diǎn)，右鍵，選擇“添加趨勢(shì)線”。在“設(shè)置趨勢(shì)線格式”窗口中，選擇“線性”趨勢(shì)線類型。單擊關(guān)閉按鈕，即可得到線性趨勢(shì)線，如圖9-1-3所示。STEP4：雙擊插入的趨勢(shì)線，在彈出的對(duì)話框中將趨勢(shì)預(yù)測(cè)設(shè)置為前推1，勾選“顯示公式”復(fù)選框，在圖上即可顯示線性預(yù)測(cè)回歸公式，如圖9-1-4所示。STEP5：帶入公式進(jìn)行數(shù)據(jù)預(yù)測(cè)計(jì)算。公式中的x為第幾個(gè)年份對(duì)應(yīng)的數(shù)據(jù)點(diǎn)，Y為對(duì)應(yīng)年份的銷售額。由于2023年是第14個(gè)年份，所以X為14，帶入公式，計(jì)算出2023年的銷售預(yù)測(cè)金額為1816.9萬(wàn)元。9.1.2利用線性趨勢(shì)線進(jìn)行銷售額預(yù)測(cè)目錄1市場(chǎng)銷量預(yù)測(cè)分析店鋪銷量分析2季節(jié)水平模型為：Yt=Y×ft式中，Y為時(shí)序的平均水平，可以是預(yù)測(cè)前一年的月（或季）平均水平，也可以是已知年份所有數(shù)據(jù)月（或季）的平均水平；ft為季節(jié)指數(shù)，表示季節(jié)變動(dòng)的數(shù)量狀態(tài)。ft=同月（或季）平均數(shù)/已知年份月（或季）總平均數(shù)9.2.1利用季節(jié)波動(dòng)預(yù)測(cè)店鋪銷量STEP1：打開(kāi)“素材文件/項(xiàng)目9/季節(jié)指數(shù)分析表.xlsx，添加“合計(jì)”、“月平均”、“年平均”、“季節(jié)指數(shù)”等字段，如圖9-2-1所示。STEP2：選擇G5單元格，在編輯欄中輸入公式“=D5+E5+F5”或者“=SUM（D5：F5）”并按回車鍵確認(rèn)，計(jì)算不同年同月份合計(jì)，然后使用填充柄將公式復(fù)制到單元格G6到G16區(qū)域中，如圖9-2-2所示。