第10章Excel數(shù)據(jù)分析Excel辦公應(yīng)用高級(jí)教程目錄Contents10.1

數(shù)據(jù)統(tǒng)計(jì)表的分析10.2

數(shù)據(jù)統(tǒng)計(jì)表的可視化10.4

疑難解答10.3

數(shù)據(jù)分析報(bào)告10.1.1對(duì)銷售業(yè)績(jī)進(jìn)行排名用戶可以使用公式，對(duì)銷售人員的銷售業(yè)績(jī)進(jìn)行排名，下面將介紹具體的操作方法。（1）打開(kāi)“銷售業(yè)績(jī)統(tǒng)計(jì)表”，在“銷售人員”列后面插入一列“排名”。（2）選擇K2單元格，輸入公式“=RANK(G2,G$2:G$13,0)”，按【Enter】鍵確認(rèn)，即可計(jì)算出排名，并將公式向下填充，計(jì)算出其他銷售人員的排名。10.1.2篩選指定銷售部門(mén)數(shù)據(jù)用戶可以利用Excel的篩選功能，將表格中“銷售2部”銷售額大于6萬(wàn)的銷售數(shù)據(jù)篩選出來(lái)。（1）選擇表格中任意單元格，按【Ctrl+Shift+L】組合鍵，進(jìn)入篩選狀態(tài)，單擊“銷售部門(mén)”篩選按鈕，從列表中取消“全選”的勾選，并勾選“銷售2部”復(fù)選框，單擊“確定”按鈕。（2）接著單擊“銷售額”篩選按鈕，從列表中選擇“數(shù)字篩選”選項(xiàng)，并從其級(jí)聯(lián)菜單中選擇“大于”選項(xiàng)。（3）打開(kāi)“自定義自動(dòng)篩選方式”對(duì)話框，在“大于”后面的文本框中輸入“60000”，單擊“確定”按鈕，即可將“銷售2部”銷售額大于6萬(wàn)的銷售數(shù)據(jù)篩選出來(lái)。10.1.3同時(shí)按部門(mén)和銷售額排序用戶可以使用排序功能，對(duì)“銷售部門(mén)”和“銷售額”進(jìn)行排序，下面將介紹具體的操作方法。（1）選擇表格中任意單元格，在“數(shù)據(jù)”選項(xiàng)卡中單擊“排序”按鈕。（2）打開(kāi)“排序”對(duì)話框，將“主要關(guān)鍵字”設(shè)置為“銷售部門(mén)”，將“次序”設(shè)置為“升序”，將“次要關(guān)鍵字”設(shè)置為“銷售額”，將“次序”設(shè)置為“升序”，單擊“確定”按鈕。（3）此時(shí)，系統(tǒng)按照“銷售部門(mén)”和“銷售額”進(jìn)行“升序”排序。10.1.4使用條件格式突出顯示銷售額如果用戶想要將銷售額大于70000的記錄突出顯示出來(lái)，則可以使用條件格式功能，下面將介紹具體的操作方法。10.1.4使用條件格式突出顯示銷售額（1）選擇數(shù)據(jù)區(qū)域，在“開(kāi)始”選項(xiàng)卡中單擊“條件格式”下拉按鈕，從列表中選擇“新建規(guī)則”選項(xiàng)。（2）打開(kāi)“新建格式規(guī)則”對(duì)話框，選擇“使用公式確定要設(shè)置格式的單元格”，然后在下方的文本框中輸入公式“=$G2>70000”，單擊“格式”按鈕。（3）打開(kāi)“設(shè)置單元格格式”對(duì)話框，在“填充”選項(xiàng)卡中選擇合適的單元格填充顏色，單擊“確定”按鈕，返回“新建格式規(guī)則”對(duì)話框，直接單擊“確定”按鈕。（4）此時(shí)，系統(tǒng)自動(dòng)將銷售額大于70000的記錄用黃色底紋突出顯示出來(lái)。10.1.5按類別匯總銷售業(yè)績(jī)利用分類匯總功能，可以將表格中的數(shù)據(jù)按“產(chǎn)品分類”對(duì)“銷售額”進(jìn)行求和匯總，下面將介紹具體的操作方法。（1）選擇表格中任意單元格，在“數(shù)據(jù)”選項(xiàng)卡中單擊“分類匯總”按鈕。（2）打開(kāi)“分類匯總”對(duì)話框，將“分類字段”設(shè)置為“產(chǎn)品分類”，將“匯總方式”設(shè)置為“求和”，在“選定匯總項(xiàng)”列表框中勾選“銷售額”復(fù)選框，單擊“確定”按鈕。（3）此時(shí)，系統(tǒng)按照類別對(duì)“銷售額”進(jìn)行求和匯總。10.1.6使用數(shù)據(jù)透視表分析數(shù)據(jù)通過(guò)創(chuàng)建數(shù)據(jù)透視表，可以對(duì)銷售數(shù)據(jù)進(jìn)行更復(fù)雜的分析操作，下面將介紹具體的操作方法。（1）選擇表格中任意單元格，在“插入”選項(xiàng)卡中單擊“數(shù)據(jù)透視表”按鈕。（2）打開(kāi)“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框，保持各選項(xiàng)為默認(rèn)狀態(tài)，單擊“確定”按鈕。（3）創(chuàng)建一個(gè)空白數(shù)據(jù)透視表，在“數(shù)據(jù)透視表字段”窗格中將“產(chǎn)品分類”字段拖至“行”區(qū)域，將“銷售部門(mén)”字段拖至“列”區(qū)域，將“銷售額”字段拖至“值”區(qū)域。（4）在數(shù)據(jù)透視表中單擊“列標(biāo)簽”下拉按鈕，從列表中取消“全選”的勾選，并勾選“銷售2部”復(fù)選框，單擊“確定”按鈕，即可將銷售2部的數(shù)據(jù)篩選出來(lái)。目錄Contents10.1

數(shù)據(jù)統(tǒng)計(jì)表的分析10.2

數(shù)據(jù)統(tǒng)計(jì)表的可視化10.4

疑難解答10.3

數(shù)據(jù)分析報(bào)告10.2.1創(chuàng)建產(chǎn)品銷售透視圖用戶創(chuàng)建數(shù)據(jù)透視表后，可以直接根據(jù)數(shù)據(jù)透視表創(chuàng)建數(shù)據(jù)透視圖，下面將介紹具體的操作方法。（1）選擇數(shù)據(jù)透視表任意單元格，在“分析”選項(xiàng)卡中單擊“數(shù)據(jù)透視圖”按鈕。（2）打開(kāi)“插入圖表”對(duì)話框，選擇“柱形圖”選項(xiàng)，并選擇“簇狀柱形圖”，單擊“確定”按鈕。（3）即可創(chuàng)建一個(gè)數(shù)據(jù)透視圖。最后對(duì)數(shù)據(jù)透視圖適當(dāng)美化一下。10.2.2使用“切片器”篩選圖表數(shù)據(jù)用戶可以使用“切片器”篩選數(shù)據(jù)透視圖中的數(shù)據(jù)，例如，按照“產(chǎn)品分類”篩選數(shù)據(jù)，下面將介紹具體的操作方法。（1）選擇數(shù)據(jù)透視圖，在“分析”選項(xiàng)卡中單擊“插入切片器”按鈕。（2）打開(kāi)“插入切片器”對(duì)話框，勾選“產(chǎn)品分類”復(fù)選框，單擊“確定”按鈕。（3）即可插入一個(gè)“產(chǎn)品分類”切片器。在切片器中單擊選擇“C類”選項(xiàng)，即可在數(shù)據(jù)透視圖中將C類數(shù)據(jù)篩選出來(lái)。10.2.3使用控件篩選圖表數(shù)據(jù)除了用切片器篩選圖表數(shù)據(jù)外，用戶還可以使用控件篩選，下面將介紹具體的操作方法。10.2.3使用控件篩選圖表數(shù)據(jù)（1）首先，選擇數(shù)據(jù)透視表中的數(shù)據(jù)區(qū)域，并進(jìn)行復(fù)制。（2）新建一個(gè)工作表，將復(fù)制的數(shù)據(jù)粘貼到該工作表中，并進(jìn)行適當(dāng)修改。（3）在A6:B9單元格區(qū)域輸入輔助數(shù)據(jù)，并選擇B7單元格，輸入公式“=INDEX(B2:D2,B$6)”。（4）按【Enter】鍵確認(rèn)，并將公式向下填充，然后在B6單元格中輸入1，即可計(jì)算出銷售1部的銷售額。（5）在D6:D9單元格區(qū)域輸入“銷售部門(mén)”，然后選擇A7:B9單元格區(qū)域，插入一張簇狀柱形圖。10.2.3使用控件篩選圖表數(shù)據(jù)（6）選擇圖表，輸入圖表標(biāo)題，取消“網(wǎng)格線”顯示，添加數(shù)據(jù)標(biāo)簽，然后適當(dāng)美化一下圖表。（7）打開(kāi)“開(kāi)發(fā)工具”選項(xiàng)卡，單擊“插入”下拉按鈕，從列表中選擇“組合框”選項(xiàng)，在圖表合適位置繪制一個(gè)組合框控件。（8）選擇控件，單擊鼠標(biāo)右鍵，選擇“設(shè)置控件格式”命令，打開(kāi)“設(shè)置對(duì)象格式”對(duì)話框，在“控制”選項(xiàng)卡中設(shè)置“數(shù)據(jù)源區(qū)域”、“單元格鏈接”，單擊“確定”按鈕。（9）此時(shí)，單擊控件下拉按鈕，從列表中選擇“銷售2部”選項(xiàng)。（10）即可將“銷售2部”的銷售額數(shù)據(jù)篩選出來(lái)。在控件列表中選擇“銷售3部”，就可以將其銷售額篩選出來(lái)。10.2.4圖表之間的聯(lián)動(dòng)在對(duì)一些復(fù)雜的數(shù)據(jù)進(jìn)行分析時(shí)，可以使用多個(gè)圖表聯(lián)動(dòng)展示數(shù)據(jù)，例如，單擊切片器篩選銷售額，柱形透視圖和餅形透視圖會(huì)同步發(fā)生變化。10.2.4圖表之間的聯(lián)動(dòng)（1）選擇表格中任意單元格，在“插入”選項(xiàng)卡中單擊“數(shù)據(jù)透視圖”按鈕。（2）打開(kāi)“創(chuàng)建數(shù)據(jù)透視圖”對(duì)話框，直接單擊“確定”按鈕。（3）在“數(shù)據(jù)透視圖字段”窗格中，將所需字段拖至合適位置，即可創(chuàng)建一個(gè)數(shù)據(jù)透視表和數(shù)據(jù)透視圖。（4）按照同樣的方法，創(chuàng)建第二張數(shù)據(jù)透視圖。（5）將柱形透視圖更改為餅形透視圖。（6）新建一個(gè)工作表，將2張數(shù)據(jù)透視圖剪切到該工作表中，并放置合適位置。（7）對(duì)2張數(shù)據(jù)透視圖進(jìn)行美化，并添加相應(yīng)的圖表元素。10.2.4圖表之間的聯(lián)動(dòng)（8）選擇柱形透視圖，在“分析”選項(xiàng)卡中單擊“插入切片器”按鈕，打開(kāi)“插入切片器”對(duì)話框，勾選“產(chǎn)品分類”復(fù)選框，單擊“確定”按鈕，即可插入一個(gè)“產(chǎn)品分類”切片器。（9）選擇“產(chǎn)品分類”切片器，在“選項(xiàng)”選項(xiàng)卡中單擊“報(bào)表連接”按鈕，將“產(chǎn)品分類”切片器關(guān)聯(lián)到“數(shù)據(jù)透視表2”，也就是“餅形透視圖”所在工作表。（10）選擇切片器，在“選項(xiàng)”選項(xiàng)卡中單擊選擇合適的切片器樣式，即可將樣式應(yīng)用到該切片器上。（11）在“選項(xiàng)”選項(xiàng)卡中設(shè)置切片器按鈕的列數(shù)、高度和寬度，并將其移至合適位置。（12）單擊“切片器設(shè)置”按鈕，打開(kāi)“切片器設(shè)置”對(duì)話框，取消勾選“顯示頁(yè)眉”復(fù)選框，單擊“確定”按鈕，即可取消切片器的標(biāo)題顯示。（13）最后，用戶單擊切片器中的選項(xiàng)，可以實(shí)現(xiàn)同步對(duì)柱形透視圖和餅形透視圖進(jìn)行篩選。目錄Contents10.1

數(shù)據(jù)統(tǒng)計(jì)表的分析10.2

數(shù)據(jù)統(tǒng)計(jì)表的可視化10.4

疑難解答10.3

數(shù)據(jù)分析報(bào)告10.3.1使用宏自動(dòng)完成高級(jí)篩選當(dāng)對(duì)數(shù)據(jù)執(zhí)行高級(jí)篩選時(shí)，每執(zhí)行一次都需要重復(fù)相同的操作，此時(shí)，用戶可以通過(guò)錄制宏，來(lái)完成高級(jí)篩選的自動(dòng)操作。10.3.1使用宏自動(dòng)完成高級(jí)篩選（1）首先，打開(kāi)“銷售業(yè)績(jī)統(tǒng)計(jì)表”，然后新建一個(gè)“篩選結(jié)果”工作表，在A1:C3單元格區(qū)域輸入篩選條件，將篩選結(jié)果輸入到A5:J5單元格區(qū)域。（2）在“開(kāi)發(fā)工具”選項(xiàng)卡中單擊“插入”下拉按鈕，選擇“按鈕”選項(xiàng)，在“篩選結(jié)果”工作表中繪制一個(gè)窗體控件。（3）繪制好后彈出一個(gè)“指定宏”對(duì)話框，輸入宏名“篩選”，單擊“錄制”按鈕，彈出“錄制宏”對(duì)話框，直接單擊“確定”按鈕。（4）選擇工作表中任意單元格，在“數(shù)據(jù)”選項(xiàng)卡中單擊“高級(jí)”按鈕，打開(kāi)“高級(jí)篩選”對(duì)話框，選擇“將篩選結(jié)果復(fù)制到其他位置”單選按鈕，并設(shè)置列表區(qū)域、條件區(qū)域、復(fù)制到區(qū)域，單擊“確定”按鈕。（5）高級(jí)篩選完成后，單擊“停止錄制”按鈕，完成錄制過(guò)程，然后修改窗體控件按鈕的名稱即可。（6）當(dāng)用戶修改篩選條件后，單擊“篩選”按鈕，便可以得到篩選結(jié)果。10.3.2使用PowerPivot進(jìn)行關(guān)聯(lián)分析如果用戶想要將“銷售業(yè)績(jī)統(tǒng)計(jì)表”和“銷售人員信息表”中的銷售人員關(guān)聯(lián)起來(lái)，分析什么學(xué)歷的銷售員最多，則可以使用PowerPivot工具。10.3.2使用PowerPivot進(jìn)行關(guān)聯(lián)分析（1）首先，打開(kāi)“銷售人員信息表”，查看相關(guān)數(shù)據(jù)。（2）新建一個(gè)工作簿，在“PowerPivot”選項(xiàng)卡中單擊“管理”按鈕，進(jìn)入PowerPivot界面，在“開(kāi)始”選項(xiàng)卡中單擊“從其他源”按鈕。（3）打開(kāi)“表導(dǎo)入向?qū)А睂?duì)話框，選擇“Excel文件”選項(xiàng)，單擊“下一步”按鈕。（4）在彈出的對(duì)話框中單擊“瀏覽”按鈕，選擇“銷售業(yè)績(jī)統(tǒng)計(jì)表”表，并勾選“使用第一行作為列標(biāo)題”復(fù)選框，單擊“下一步”按鈕，導(dǎo)入該表格。（5）按照上述方法，導(dǎo)入“銷售人員信息表”表。10.3.2使用PowerPivot進(jìn)行關(guān)聯(lián)分析（6）在“銷售業(yè)績(jī)統(tǒng)計(jì)表”中，選擇“銷售人員”任意單元格，在“設(shè)計(jì)”選項(xiàng)卡中單擊“創(chuàng)建關(guān)系”按鈕。（7）打開(kāi)“創(chuàng)建關(guān)系”對(duì)話框，將“表2”設(shè)置為“銷售人員信息表”，并在“列”中選擇“銷售人員”選項(xiàng)，單擊“確定”按鈕。（8）在“開(kāi)始”選項(xiàng)卡中單擊“數(shù)據(jù)透視表”按鈕，打開(kāi)“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框，直接單擊“確定”按鈕。（9）創(chuàng)建一個(gè)數(shù)據(jù)透視表，將“銷售業(yè)績(jī)統(tǒng)計(jì)表”表中的“銷售人員”字段拖至“值”區(qū)域，將“銷售人員信息表”表中的“學(xué)歷”字段拖至“行”區(qū)域。（10）最后，修改數(shù)據(jù)透視表中的標(biāo)題字段名稱，即可得到分析結(jié)果。10.3.3產(chǎn)品銷售多方案分析在經(jīng)營(yíng)環(huán)境中，很多銷售計(jì)劃都需要提供多個(gè)方案，以便從中選擇最好的一種。方案管理器可以允許用戶為某個(gè)組合預(yù)存儲(chǔ)多個(gè)值，這樣可以非常方便的在不同取值之間查看計(jì)算結(jié)果，進(jìn)行多方面比較從而選擇最好的一種方案。例如，分析銷售數(shù)據(jù)在單位成本的不同取值情況下，其他項(xiàng)目的數(shù)據(jù)情況。10.3.3產(chǎn)品銷售多方案分析（1）首先，制作一個(gè)表格框架，在其中輸入基本數(shù)據(jù)。（2）選擇C3:E3單元格區(qū)域，輸入公式“=$B$3”

。（3）按【Ctrl+Enter】組合鍵，引用B3單元格中的內(nèi)容。（4）選擇B6:E6單元格區(qū)域，輸入公式“=B4*B5”

。（5）按【Ctrl+Enter】組合鍵，計(jì)算每個(gè)產(chǎn)品的銷售額。（6）選擇B7:E7單元格區(qū)域，輸入公式“=B3*B5”

。（7）按【Ctrl+Enter】組合鍵，計(jì)算每個(gè)產(chǎn)品的變動(dòng)成本。（8）選擇B8:E8單元格區(qū)域，輸入公式“=B7/B6”

。（9）按【Ctrl+Enter】組合鍵，計(jì)算每個(gè)產(chǎn)品的所占比重。（10）在“開(kāi)始”選項(xiàng)卡中，將“數(shù)字格式”設(shè)置為“百分比”，使所占比重以百分比形式顯示。10.3.3產(chǎn)品銷售多方案分析（11）選擇B9:E9單元格區(qū)域，輸入公式“=B6-B7”。（12）按【Ctrl+Enter】組合鍵，計(jì)算每個(gè)產(chǎn)品的毛利潤(rùn)。（13）選擇B10:E10單元格區(qū)域，輸入公式“=B9/B6”。（14）按【Ctrl+Enter】組合鍵，計(jì)算每個(gè)產(chǎn)品的利潤(rùn)率，并設(shè)置為“百分比”格式。（15）選擇B11:E11單元格區(qū)域，輸入公式“=B9/SUM($B$9:$E$9)”。（16）按【Ctrl+Enter】組合鍵，計(jì)算每個(gè)產(chǎn)品的利潤(rùn)構(gòu)成，并設(shè)置為“百分比”格式。（17）在“數(shù)據(jù)”選項(xiàng)卡中單擊“模擬分析”下拉按鈕，從列表中選擇“方案管理器”選項(xiàng)。（18）打開(kāi)“方案管理器”對(duì)話框，單擊“添加”按鈕。10.3.3產(chǎn)品銷售多方案分析（19）打開(kāi)“添加方案”對(duì)話框，輸入“方案名”，并將“可變單元格”設(shè)置為B3，單擊“確定”按鈕。（20）彈出“方案變量值”對(duì)話框，輸入當(dāng)前方案中可變單元格的值，單擊“確定”按鈕，返回“方案管理器”對(duì)話框，按照上述方法，繼續(xù)添加方案。（21）添加3個(gè)方案后，在“方案”列表框中選擇一種方案，單擊“顯示”按鈕，即可將所選方案應(yīng)用到當(dāng)前工作表中。（22）如果用戶想要?jiǎng)?chuàng)建方案摘要，則在“方案管理器”對(duì)話框中單擊“摘要”按鈕，打開(kāi)“方案摘要”對(duì)話框，選擇“方案摘要”單選按鈕，并選擇結(jié)果單元格，單擊“確定”按鈕，即可生成方案摘要。10.3.4銷售利潤(rùn)額最大化分析通常，一個(gè)企業(yè)都不止生產(chǎn)單一的一種產(chǎn)品，但同時(shí)生產(chǎn)多種產(chǎn)品就會(huì)引發(fā)一個(gè)問(wèn)題：如何確保產(chǎn)品組合可以獲得最大的利潤(rùn)，使用規(guī)劃求解功能，可以輕易的解決這個(gè)問(wèn)題。例如，計(jì)算4種產(chǎn)品的收益情況時(shí)，應(yīng)該如何生產(chǎn)，才能達(dá)到收益最大化。其中公司對(duì)產(chǎn)品的生產(chǎn)有著以下約束條件：4種產(chǎn)品的組合生產(chǎn)能力為每天400星飛帆1段在每天的產(chǎn)量要達(dá)到50君樂(lè)寶1段在每天的產(chǎn)量要達(dá)到40菁摯1段在每天的產(chǎn)量要達(dá)到60普爾萊克1段的產(chǎn)量每天不超過(guò)4010.3.4銷售利潤(rùn)額最大化分析下面將介紹具體的操作方法。（1）首先，計(jì)算產(chǎn)品的收益和總計(jì)。（2）在“數(shù)據(jù)”選項(xiàng)卡中單擊“規(guī)劃求解”按鈕。（3）打開(kāi)“規(guī)劃求解參數(shù)”對(duì)話框，在“設(shè)置目標(biāo)”文本框中輸入“$D$6”，并選擇“最大值”單選按鈕，然后指定可變單元格，單擊“添加”按鈕。（4）在打開(kāi)的“添加約束”對(duì)話框中添加第一個(gè)約束條件，單擊“添加”按鈕，繼續(xù)添加其他約束條件。（5）輸入最后一個(gè)約束條件后，單擊“確定”按鈕，返回“規(guī)劃求解參數(shù)”對(duì)話框，在“遵守約束”列表框中可以看到之前添加的約束條件，單擊“求解”按鈕。（6）彈出“規(guī)劃求解結(jié)果”對(duì)話框，選擇“運(yùn)算結(jié)果報(bào)告”選項(xiàng)，單擊“確定”按鈕，即可生成運(yùn)算結(jié)果報(bào)告。10.3.5打印銷售報(bào)表為了方便傳閱、查看數(shù)據(jù)，有時(shí)需要將報(bào)表以紙質(zhì)的形式打印出來(lái)，下面將介紹具體的操作方法。（1）打開(kāi)“銷售業(yè)績(jī)統(tǒng)計(jì)表”，單擊“文件”按鈕，選擇“打印”選項(xiàng)，在“打印