商務(wù)數(shù)據(jù)分析與應(yīng)用 課件 項目四 商務(wù)數(shù)據(jù)分析工具

商務(wù)數(shù)據(jù)分析與應(yīng)用重慶大學出版社尹愛飛，杜轉(zhuǎn)萍主編本書目錄項目一商務(wù)數(shù)據(jù)分析基礎(chǔ)項目二認識數(shù)據(jù)表項目三

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

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

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

銷售費用分析項目八

商品庫存分析項目九

銷售市場預測分析項目四

商務(wù)數(shù)據(jù)分析工具針對字段多樣、數(shù)據(jù)總量龐大而復雜的表格，在解決多角度綜合分析的問題時，若采用傳統(tǒng)的數(shù)據(jù)運算的方法往往會存在難度大、效率低、易出錯等狀況，這就需要我們掌握一些好用且高效的數(shù)據(jù)分析工具。目錄1數(shù)據(jù)的分類匯總數(shù)據(jù)的篩選2數(shù)據(jù)透視表3其他44.1.1數(shù)據(jù)的自動篩選4.1.2數(shù)據(jù)的高級篩選STEP1：打開“素材文件/項目4/某公司文具銷售明細表.xlsx”，選擇表格中所有字段，即選擇A2:G2單元格，如圖4-1-1所示。4.1.1數(shù)據(jù)自動篩選STEP2：單擊“數(shù)據(jù)”選項卡，再單擊”排序與篩選”選項組的“篩選”，各字段即出現(xiàn)篩選按鈕，如圖4-1-2所示。4.1.1數(shù)據(jù)自動篩選STEP3：點開“地區(qū)”字段旁的篩選按鈕，彈出篩選條件對話框，取消全選，勾選A，即完成對地區(qū)的篩選。然后運用同樣方法，篩選“業(yè)務(wù)員”字段中的張三，兩個操作步驟完成后，即出現(xiàn)“A地區(qū)張三的銷售明細”，過程及結(jié)果如圖4-1-3、4-1-4所示。4.1.1數(shù)據(jù)自動篩選在表格中，若要查找的數(shù)據(jù)受多個條件約束，則可以采用高級篩選的方法完成。具體可參照如下案例：“查看A地區(qū)張三銷量超過100的業(yè)務(wù)數(shù)據(jù)”。STEP1：打開“素材文件/項目4/某公司文具銷售明細表.xlsx”，在表格任何一個空位置填寫涉及篩選條件的字段，如在J2:L2單元格中填寫三個字段：“地區(qū)”、“業(yè)務(wù)員”、“銷量（個）”，如圖4-1-5所示4.1.2數(shù)據(jù)的高級篩選STEP2：在J3、K3、L3單元格中分別填入篩選條件“A”、“張三”、“>100”，如圖4-1-6所示。4.1.2數(shù)據(jù)的高級篩選STEP3：鼠標放在原數(shù)據(jù)表格中任意一個單元格內(nèi)，單擊“數(shù)據(jù)”選項卡，再單擊”排序與篩選”選項組的“高級篩選”，彈出“高級篩選”對話框，過程及結(jié)果如圖4-1-7、圖4-1-8所示。4.1.2數(shù)據(jù)的高級篩選STEP4：對話框中，列表區(qū)域數(shù)據(jù)為默認表格數(shù)據(jù)全選，單擊條件區(qū)域選項，鼠標框選J2:L3單元格，然后按Enter，結(jié)果如圖4-1-9所示。4.1.2數(shù)據(jù)的高級篩選STEP5：勾選“將篩選結(jié)果復制到其他位置”選項，并用鼠標選擇合適的位置如J6單元格，按Enter確認，具體過程及結(jié)果如圖4-1-10、圖4-1-11、圖4-1-12所示。4.1.2數(shù)據(jù)的高級篩選目錄1數(shù)據(jù)的分類匯總數(shù)據(jù)的篩選2數(shù)據(jù)透視表3其他44.2.1創(chuàng)建分類匯總4.2.2分級顯示分類匯總4.2.3復制分類匯總結(jié)果4.2.4刪除分類匯總結(jié)果4.2.1創(chuàng)建分類匯總STEP1：先分類。打開“素材文件/項目4/某公司文具銷售明細表.xlsx”，鼠標選中“地區(qū)”字段下面任一單元格數(shù)據(jù)，單擊右鍵，鼠標指針移動至“排序”快捷方式上，彈出“排序”選項對話框，點擊“升序”（降序也可以），操作過程及結(jié)果如圖4-2-1，圖4-2-2所示。4.2.1創(chuàng)建分類匯總STEP2：再匯總。鼠標放在數(shù)據(jù)表中任意一個單元格內(nèi)，單擊“數(shù)據(jù)”選項卡，再單擊”分級顯示”選項組的“分類匯總”，彈出“分類匯總”對話框，過程及結(jié)果如圖4-2-3所示。4.2.1創(chuàng)建分類匯總STEP3：按地區(qū)統(tǒng)計銷售額。分類匯總對話框中，分類字段選擇“地區(qū)”，匯總方式選擇“求和”，選定匯總選項勾選“銷售額”，并按“確定”按鈕，結(jié)果及過程如圖4-2-4、圖圖4-2-5所示。4.2.1創(chuàng)建分類匯總STEP4：統(tǒng)計各地區(qū)不同業(yè)務(wù)員的訂單數(shù)。鼠標再次放在數(shù)據(jù)表中任意一個單元格內(nèi)，再次點擊“分類匯總”按鈕，在彈出“分類匯總”對話框中重新勾選字段。分類字段選擇“業(yè)務(wù)員”，匯總方式選擇“計數(shù)”，選定匯總選項勾選“業(yè)務(wù)員”，并勾選掉“替換當前分類匯總”選項，最后按“確定”按鈕，過程及結(jié)果如圖4-2-5、圖4-2-6所示。4.2.2分級顯示分類匯總分類匯總完成后，在分類匯總的結(jié)果的左側(cè)出現(xiàn)分級顯示符號和分級標識線，如圖4-2-7所示。通常完成一次分類匯總后，分類匯總的結(jié)果分為3個級別，再進行一次分類匯總后分為4個級別，依次類推。4.2.2分級顯示分類匯總STEP1：1級顯示。單擊1級顯示按鈕，只顯示總的匯總，即總計和總計數(shù)，其他級別的數(shù)據(jù)均被隱藏起來，結(jié)果如圖4-2-8所示。STEP2：2級顯示。單擊2級顯示按鈕，將同時顯示第1級和第2級的數(shù)據(jù)，即總計與總計數(shù)、各地區(qū)匯總，其他級別的數(shù)據(jù)均被隱藏起來，結(jié)果如圖4-2-9所示。4.2.2分級顯示分類匯總STEP3：3級顯示。單擊3級顯示按鈕，將同時顯示第1級、第2級和第3級的數(shù)據(jù)，即總計與總計數(shù)、各地區(qū)匯總、各業(yè)務(wù)員匯總，其他級別的數(shù)據(jù)均被隱藏起來，結(jié)果如圖4-2-10所示。4.2.2分級顯示分類匯總STEP4：4級顯示。單擊4級顯示按鈕，將全部顯示數(shù)據(jù)，結(jié)果如圖4-2-11所示。4.2.3復制分類匯總結(jié)果STEP1：在分類匯總結(jié)果左側(cè)單擊2級顯示按鈕，顯示按“地區(qū)”匯總的結(jié)果值，選擇整個數(shù)據(jù)區(qū)域，即A1:G27，然后按【Ctrl+G】快捷鍵，彈出“定位條件”對話框，結(jié)果如圖4-2-12所示。4.2.3復制分類匯總結(jié)果STEP2：在“定位條件”對話框中選擇“可見單元格”單選按鈕，然后單擊“確定”按鈕關(guān)閉該對話框，過程如圖4-2-13所示。4.2.3復制分類匯總結(jié)果STEP3：執(zhí)行復制操作，然后選定目標位置，執(zhí)行粘貼操作即可，復制結(jié)果如圖4-2-14所示，從圖中可以看出，只復制了分類匯總的結(jié)果，沒有復制明細數(shù)據(jù)。4.2.4刪除分類匯總結(jié)果STEP1：單擊包含分類匯總的數(shù)據(jù)區(qū)域中的任意一個單元格。STEP2：單擊“數(shù)據(jù)”選項卡，再單擊”分級顯示”選項組的“分類匯總”按鈕，彈出“分類匯總”對話框。STEP3：在打開的“分類匯總”對話框中，單擊“全部刪除”按鈕即可刪除全部分類匯總數(shù)據(jù)，過程及結(jié)果如圖4-2-15、圖4-2-16所示。目錄1數(shù)據(jù)的分類匯總數(shù)據(jù)的篩選2數(shù)據(jù)透視表3其他44.3.1創(chuàng)建數(shù)據(jù)透視表4.3.2數(shù)據(jù)透視表基礎(chǔ)運用4.3.3數(shù)據(jù)透視表高階運4.3.1創(chuàng)建數(shù)據(jù)透視表STEP1：打開表格，插入數(shù)據(jù)透視表。打開“素材文件/項目4/某公司文具銷售明細表.xlsx”，選擇數(shù)據(jù)區(qū)域任意單元格，單擊“插入”選項卡，再單擊“表格”選項組中的“數(shù)據(jù)透視表按鈕”，彈出“創(chuàng)建數(shù)據(jù)透視表”對話框，過程如圖4-3-1、圖4-3-2所示。4.3.1創(chuàng)建數(shù)據(jù)透視表STEP2：在“創(chuàng)建數(shù)據(jù)透視表”對話框中，系統(tǒng)一般會自動選擇整個數(shù)據(jù)區(qū)域作為數(shù)據(jù)透視表的數(shù)據(jù)源。如果要透視分析的數(shù)據(jù)區(qū)域與此有出入，可以在“表/區(qū)域”文本框內(nèi)進行選擇修改。STEP3：選擇放置數(shù)據(jù)透視表的位置?？梢苑旁谛鹿ぷ鞅砘颥F(xiàn)有工作表，默認選擇“新工作表”單選按鈕。若選擇“新工作表”單選按鈕，系統(tǒng)會自動創(chuàng)建一個新工作表，并將數(shù)據(jù)透視表放在新工作表中；若選擇“現(xiàn)有工作表”按鈕，必須制定放置數(shù)據(jù)透視表的位置，可以是單元格區(qū)域或第一個單元格位置。本案例數(shù)據(jù)透視表位置選擇放在“現(xiàn)有工作表”的I9單元格，圖4-3-3所示。4.3.1創(chuàng)建數(shù)據(jù)透視表STEP4：單擊確定按鈕，彈出“數(shù)據(jù)透視表字段”窗格，如圖4-3-4所示。4.3.1創(chuàng)建數(shù)據(jù)透視表STEP5：在“數(shù)據(jù)透視表字段”窗格中設(shè)置字段。右側(cè)彈出的是數(shù)據(jù)透視表字段的設(shè)置界面，默認的數(shù)據(jù)透視表字段布局采用字段節(jié)和區(qū)域節(jié)層疊的方式，如圖4-3-5所示。4.3.1創(chuàng)建數(shù)據(jù)透視表STEP6：拖拽字段進行數(shù)據(jù)統(tǒng)計分析。通過拖拽字段在行、列、篩選中的位置，可以改變數(shù)據(jù)透視表的結(jié)構(gòu)，此外，若字段對應(yīng)的數(shù)據(jù)為數(shù)值型數(shù)據(jù)時，該字段會默認到值區(qū)域參與運算。例如計算不同文具的具體銷售額，勾選“品名”字段、“銷售額”字段，可以看出“品名”字段默認在行區(qū)域發(fā)揮標題欄作業(yè)，“銷售額”字段默認在值區(qū)域參與計算，字段勾選過程及數(shù)據(jù)透視表統(tǒng)計結(jié)果如圖4-3-6、圖4-3-7所示。4.3.2數(shù)據(jù)透視表的基礎(chǔ)運用一般情況下，根據(jù)數(shù)據(jù)統(tǒng)計的要求，通過勾選數(shù)據(jù)透視表中相關(guān)字段就可以實現(xiàn)基本的數(shù)據(jù)統(tǒng)計目標。具體操作以“分析某公司銷售文具銷售情況”為例，分析的指標包括以下幾點：①2010年總銷量是多少？總銷售額是多少？②2010年A、B、C三地區(qū)的銷量及銷售額各是多少？③2010年哪種產(chǎn)品銷量最好？哪種產(chǎn)品銷量最差？④2010年B地區(qū)業(yè)務(wù)員王五的鋼筆銷量是多少？4.3.2數(shù)據(jù)透視表的基礎(chǔ)運用STEP1：①2010年總銷量是多少？總銷售額是多少？打開“素材文件/項目4/某公司文具銷售明細表.xlsx”，按照插入數(shù)據(jù)透視表的步驟，在現(xiàn)有工作表中插入數(shù)據(jù)透視表，在“數(shù)據(jù)透視表字段”窗格中勾選“銷量”、“銷售額”兩個字段，即可呈現(xiàn)總銷量、總銷售額統(tǒng)計結(jié)果，過程及結(jié)果如圖4-3-10、圖4-3-11所示。4.3.2數(shù)據(jù)透視表的基礎(chǔ)運用STEP2：②2010年A、B、C三地區(qū)的銷量及銷售額各是多少？打開“素材文件/項目4/某公司文具銷售明細表.xlsx”，在現(xiàn)有工作表中插入數(shù)據(jù)透視表，在“數(shù)據(jù)透視表字段”窗格中勾選“地區(qū)”、“銷量”、“銷售額”三個字段，行、列布局可以選擇默認，過程及結(jié)果如圖4-3-12所示。4.3.2數(shù)據(jù)透視表的基礎(chǔ)運用STEP3：③2010年哪種產(chǎn)品銷量最好？哪種產(chǎn)品銷量最差？打開“素材文件/項目4/某公司文具銷售明細表.xlsx”，在現(xiàn)有工作表中插入數(shù)據(jù)透視表，在“數(shù)據(jù)透視表字段”窗格中勾選“品名”、“銷量”兩個字段，即出現(xiàn)按品名進行銷量統(tǒng)計結(jié)果，如圖4-3-13所示。然后選擇數(shù)據(jù)透視表“匯總”字段下面的任意單元格，單擊右鍵，彈出快捷方式菜單，鼠標指針移動至“排序”快捷方式上，彈出“排序”選項對話框，點擊“升序”（降序也可以），即可看到按升序排序的產(chǎn)品銷量情況，過程及結(jié)果如圖4-3-14、圖4-3-15所示。4.3.2數(shù)據(jù)透視表的基礎(chǔ)運用STEP4：④2010年B地區(qū)業(yè)務(wù)員王五的鋼筆銷量是多少？打開“素材文件/項目4/某公司文具銷售明細表.xlsx”，在現(xiàn)有工作表中插入數(shù)據(jù)透視表，在“數(shù)據(jù)透視表字段”窗格中勾選“地區(qū)”、“業(yè)務(wù)員”、“品名”、“銷量”等字段，即出現(xiàn)初步統(tǒng)計結(jié)果，如圖4-3-16所示。然后將行字段區(qū)域中的“業(yè)務(wù)員”、“地區(qū)”、“品名”三個字段分別拖拽到“篩選”字段區(qū)域，如圖4-3-17所示。然后點開數(shù)據(jù)透視表中的字段“地區(qū)”篩選項，選擇“B”，單擊確定，如圖4-3-18。同樣的操作步驟，分別篩選出業(yè)務(wù)員“王五”、品名“鋼筆”，結(jié)果如圖4-3-19所示。4.3.3數(shù)據(jù)透視表的高階運用簡單的數(shù)據(jù)統(tǒng)計結(jié)果不足以說明數(shù)據(jù)背后的問題，有時需要對統(tǒng)計數(shù)據(jù)作進一步處理方能直觀的展示出統(tǒng)計結(jié)果，如求參數(shù)占比、求數(shù)據(jù)的環(huán)比/同比變動率、以及對數(shù)據(jù)進行分組統(tǒng)計等，具體操作以“分析某公司銷售文具銷售情況”為例，分析的指標包括以下幾點：①分析2010年A、B、C三地區(qū)的銷售額占比②計算2010年每月的銷售額③分析計算2010年每月銷售額環(huán)比數(shù)據(jù)4.3.3數(shù)據(jù)透視表的高階運用STEP1：①分析2010年A、B、C三地區(qū)的銷售額占比，步驟一：打開“素材文件/項目4/某公司文具銷售明細表.xlsx”，在現(xiàn)有工作表中插入數(shù)據(jù)透視表，在“數(shù)據(jù)透視表字段”窗格中勾選“地區(qū)”、“銷售額”兩個字段，然后選中“銷售額”字段，按住鼠標左鍵將其拖拽至“值”區(qū)域字段，這時數(shù)據(jù)透視表中會增加“求和銷售額”字段及其系列數(shù)據(jù)，過程及結(jié)果如圖4-3-20、圖4-3-21所示。4.3.3數(shù)據(jù)透視表的高階運用STEP2：①分析2010年A、B、C三地區(qū)的銷售額占比，步驟二：鼠標放在“求和項:銷售額（元）2”字段上，輸入“銷售額占比”，即可更改字段，結(jié)果如圖4-3-22所示。4.3.3數(shù)據(jù)透視表的高階運用STEP3：①分析2010年A、B、C三地區(qū)的銷售額占比，步驟三：鼠標放在“銷售額占比”字段數(shù)據(jù)任意單元格上，單擊右鍵，彈出快捷方式菜單，鼠標指針移動至“值顯示方式”快捷方式上，彈出“顯示方式”選項對話框，點擊“列匯總百分比”，即可看到不同地區(qū)銷售數(shù)據(jù)及其占比，過程及結(jié)果如圖4-3-23、圖4-3-24所示。4.3.3數(shù)據(jù)透視表的高階運用STEP4：②計算2010年每月的銷售額。打開“素材文件/項目4/某公司文具銷售明細表.xlsx”，在現(xiàn)有工作表中插入數(shù)據(jù)透視表，在“數(shù)據(jù)透視表字段”窗格中勾選“日期”、“銷售額”兩個字段，即出現(xiàn)按時間統(tǒng)計銷售額的數(shù)據(jù)透視表。選中“日期”字段下面任意單元格數(shù)據(jù)，單擊右鍵，彈出快捷方式菜單，鼠標指針移動至“組合”快捷方式上，彈出“組合”選項對話框，“起始于”、“終止于”選擇默認時間，“步長”選擇“月”，單擊“確定”按鈕，即可出現(xiàn)按月統(tǒng)計的結(jié)果，過程及結(jié)果如圖4-3-25、圖4-3-26、圖4-3-27所示。4.3.3數(shù)據(jù)透視表的高階運用STEP5：③分析計算2010年每月銷售額環(huán)比數(shù)據(jù)。