《商務數(shù)據(jù)分析與應用》課件 尹愛飛 項目5-9 商務數(shù)據(jù)可視化- 銷售市場預測分析

項目五商務數(shù)據(jù)可視化

18-10月-24學習目標：①掌握柱形圖與條形圖的制作方法；②掌握繪制餅圖與復合餅圖的方法；③掌握繪制雷達圖的方法；④掌握繪制平行線圖、雙坐標圖的方法；⑤掌握繪制旋風圖與帕累托圖的方法。重點：①柱形圖與條形圖的制作方法；②繪制餅圖與復合餅圖的方法；③繪制雷達圖的方法；④繪制平行線圖、雙坐標圖的方法；⑤繪制旋風圖與帕累托圖的方法。難點：①繪制平行線圖、雙坐標圖的方法；②繪制旋風圖與帕累托圖的方法。目錄1復雜數(shù)據(jù)圖經(jīng)濟適用圖25.1.1柱形圖與條形圖5.1.2餅圖與復合餅圖5.1.3雷達圖柱形圖又稱條形圖和直方圖，它是寬度相等的條形以高度或長度的差異來顯示一段時間內(nèi)數(shù)據(jù)的變化。它主要包括5種樣式的子表圖，分別是二維、三維、圓柱、圓錐和棱錐，而且每種樣式都具備3種類型，分別是簇狀、堆積和百分比。5.1.1柱形圖與條形圖簇狀條形圖百分比條形圖

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

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

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

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

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

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

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

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

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

銷售費用分析項目八

商品庫存分析項目九

銷售市場預測分析項目六

商品銷售情況統(tǒng)計與分析18-10月-24學習目標：①了解銷售數(shù)據(jù)統(tǒng)計與分析的方法；②熟悉不同商品銷售的統(tǒng)計與分析的方法；③

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

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

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

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

商品庫存分析項目九

銷售市場預測分析項目七

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

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

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

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

商品庫存分析項目九

銷售市場預測分析項目八

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

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

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

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

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

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

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

商品庫存分析項目九

銷售市場預測分析項目九

銷售市場預測分析18-10月-24學習目標：①熟悉趨勢線的基本類型；②熟悉線性趨勢線的基本操作步驟；③掌握移動平均法、指數(shù)平滑法的方法原理；④熟悉季節(jié)指數(shù)法的計算步驟。重點：線性趨勢線的基本操作步驟。難點：①移動平均法、指數(shù)平滑法的方法原理；②季節(jié)指數(shù)法的計算步驟。

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

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