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

商務(wù)數(shù)據(jù)分析與應(yīng)用重慶大學(xué)出版社尹愛飛，杜轉(zhuǎn)萍主編本書目錄項(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)的銷售方面的問題，更不能體現(xiàn)其他潛在的信息，這時(shí)通過Excel對(duì)銷售情況數(shù)據(jù)進(jìn)行統(tǒng)計(jì)分析，從中發(fā)現(xià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：打開“素材文件/項(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)表賣家通過對(duì)商品的銷售情況進(jìn)行分析，可以直觀地判斷那些商品是暢銷狀態(tài)，哪些商品是滯銷狀態(tài)，然后針對(duì)不同銷售狀態(tài)的商品制定不同的采購(gòu)計(jì)劃和銷售策略。下面將介紹如何分析店鋪中的暢銷與滯銷商品，具體可參照如下案例：STEP1：打開“素材文件/項(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ù)，打開“數(shù)據(jù)“選項(xiàng)卡，單擊“數(shù)據(jù)工具”組中的“刪除重復(fù)項(xiàng)“按鈕，對(duì)A列數(shù)據(jù)中的重復(fù)數(shù)據(jù)進(jìn)行刪除，過程如圖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)擊“開始”選項(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：打開“素材文件/項(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：打開“店鋪月銷售報(bào)表工作表”，選擇任意單元格，按【Ctrl+A】快捷鍵，全選表格數(shù)據(jù)，在“插入”選項(xiàng)卡的“表格”選項(xiàng)組中，單擊“插入數(shù)據(jù)透視表“按鈕，過程如圖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è)置”，值字段匯總方式改為“平均值”，單擊“確定”按鈕，過程如圖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)圖的制作過程可參照本教材“項(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ù)透視表。打開“素材文件/項(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ì)話框，過程如圖6-2-12，結(jié)果如圖6-2-13。6.2.2不同商品收入與毛利對(duì)比分析STEP3：選擇“組合圖”，并更改銷售總額圖表類型為“折線圖”，單擊“確定”按鈕，繪成“雙線組合圖”，過程如圖6-2-14，結(jié)果如圖6-2-15。6.2.2不同商品收入與毛利對(duì)比分析STEP4：選中圖中“圖例”，單擊右鍵，通過“設(shè)置圖例格式”將圖例放置在圖表底部；選擇圖中“求和項(xiàng)：銷售毛利”折線，單擊右鍵，通過“設(shè)置數(shù)據(jù)系列格式”，更改短劃線類型為“方點(diǎn)”。結(jié)果如圖6-2-16所示。6.2.3不同商品分配方案分析STEP1：打開“素材文件/項(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單元格，選中“最大”按鈕，過程如圖6-2-23、圖6-2-24所示。6.2.3不同商品分配方案分析STEP8：在“通過可更改單元格”選項(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：選中“使無約束變量為非負(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：打開“素材文件/項(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è)置”，值字段匯總方式改為“平均值”，單擊“確定”按鈕。過程如圖6-3-5所示。6.3.1不同商品退貨、退款金額統(tǒng)計(jì)分析STEP6：選擇“退款率”下任意單元格數(shù)據(jù)，單擊右鍵，彈出的快捷菜單中選擇“值字段設(shè)置”，值字段匯總方式改為“平均值”，單擊“確定”按鈕。過程如圖6-3-5所示。6.3.2不同商品退貨、退款原因統(tǒng)計(jì)分析STEP1：打開“素材文