第9章Excel數(shù)據(jù)管理與分析

Excel數(shù)據(jù)管理與分析數(shù)據(jù)排序數(shù)據(jù)篩選分類匯總數(shù)據(jù)透視表合并計算模擬分析1主要內(nèi)容21、按關(guān)鍵字排序單一關(guān)鍵字根據(jù)單個關(guān)鍵字對數(shù)據(jù)進行排序的具體操作方法：首先單擊參與排序的列的某個單元格，然后單擊“數(shù)據(jù)”選項卡—>“排序和篩選”組—>“升序”按鈕

或“降序”按鈕

，即可快速地按所選列數(shù)值升序或降序排序。多關(guān)鍵字首先單擊參與排序的數(shù)據(jù)區(qū)域中的任意單元格，單擊“數(shù)據(jù)”選項卡—>“排序和篩選”組—>“排序”按鈕，彈出“排序”對話框，然后選擇“主關(guān)鍵字”，在“排序的依據(jù)”、“次序”下拉列表中進行相應(yīng)設(shè)置即可。如果一個關(guān)鍵字無法解決排序中的先后問題，可以根據(jù)需要添加“次要關(guān)鍵字”作為排序的第二個、第三個、……依據(jù)，每個次要關(guān)鍵字根據(jù)需要設(shè)置“排序的依據(jù)”和“次序”項。數(shù)據(jù)排序32、按自定義序列排序在Excel中，當按照關(guān)鍵字的升序或降序排列無法滿足用戶需求時，用戶還可以按照自定義的序列順序進行數(shù)據(jù)排序。具體操作方法：首先創(chuàng)建一個自定義序列，然后選擇參與排序的數(shù)據(jù)，接著打開“排序”對話框，在“次序”下拉列表中選擇“自定義序列”，打開“自定義序列”對話框，從中選擇已經(jīng)創(chuàng)建的自定義序列，即可按照用戶自定義的序列順序進行排序。例：在如圖數(shù)據(jù)中費用類別按交通費、通訊費、餐飲費、住宿費、其他的順序排列。數(shù)據(jù)排序41、自動篩選首先單擊數(shù)據(jù)清單中的任意單元格，然后單擊“數(shù)據(jù)”選項卡—>“排序和篩選”組—>“篩選”按鈕

，當前數(shù)據(jù)清單中的每個字段名（即列標題）右側(cè)會出現(xiàn)一個篩選箭頭

，最后單擊篩選箭頭，在彈出的篩選下拉列表根據(jù)需要進行設(shè)置即可。其中，篩選下拉列表顯示的內(nèi)容與當前列的數(shù)據(jù)格式有關(guān)。當數(shù)據(jù)格式為文本時，篩選下拉列表將顯示“文本篩選”命令，如圖所示，當數(shù)據(jù)格式為數(shù)值時，篩選下拉列表將顯示“數(shù)字篩選”命令。數(shù)據(jù)篩選51、自動篩選此外，如果需要設(shè)定篩選條件，那么可以單擊“數(shù)字篩選”命令或“文本篩選”命令，在其子菜單中選擇“自定義篩選”命令，彈出“自定義自動篩選方式”對話框，如圖所示，在其中設(shè)置篩選條件即可。數(shù)據(jù)篩選62、高級篩選當篩選條件比較復(fù)雜，自動篩選無法實現(xiàn)用戶的篩選要求時，可以通過高級篩選來指定篩選條件以實現(xiàn)復(fù)雜條件的數(shù)據(jù)篩選。具體操作方法：首先構(gòu)建篩選條件，然后單擊數(shù)據(jù)清單中的任意單元格，接著單擊“數(shù)據(jù)”選項卡—>“排序和篩選”組—>“高級”按鈕，彈出“高級篩選”對話框，如右上圖所示，進行相應(yīng)設(shè)置即可。篩選條件的構(gòu)建規(guī)則：條件區(qū)域的第一行必須是字段名，且與原數(shù)據(jù)清單的字段名保持一致；需要同時滿足的條件寫在條件區(qū)域的同一行，不需要同時滿足的條件寫在不同行，如右下圖所示，左圖表示不需要同時滿足的條件，平時成績大于90分或期中成績大于90分，右圖則表示需要同時滿足的條件，平時成績大于90分并且期中成績大于90分。數(shù)據(jù)篩選71、建立分類匯總建立分類匯總具體操作方法：首先選擇需要進行分類匯總的數(shù)據(jù)區(qū)域，按照分類字段進行排序，然后單擊“數(shù)據(jù)”選項卡—>“分組顯示”組—>“分類匯總”按鈕，彈出“分類匯總”對話框，如圖所示，分別對“分類字段”、“匯總方式”、“選定匯總項”等進行相應(yīng)設(shè)置，最后單擊“確定”按鈕。注意：建立分類匯總前，應(yīng)該先以分類字段作為主關(guān)鍵字對數(shù)據(jù)清單中的數(shù)據(jù)進行排序。2、刪除分類匯總刪除分類匯總具體操作方法：首先單擊已建立分類匯總的數(shù)據(jù)區(qū)域任意單元格，然后單擊“數(shù)據(jù)”選項卡—>“分組顯示”組—>“分類匯總”按鈕，在彈出的“分類匯總”對話框中單擊“全部刪除”按鈕。分類匯總8例：在如圖所示的原數(shù)據(jù)中匯總出各費用類別的差旅費用金額總額。分類匯總93、數(shù)據(jù)分級顯示自行創(chuàng)建分級顯示在Excel中分類匯總后的數(shù)據(jù)可以自動形成分級顯示，普通數(shù)據(jù)也可以通過分級顯示功能創(chuàng)建數(shù)據(jù)分級顯示。普通數(shù)據(jù)創(chuàng)建分級顯示的具體操作方法：首先單擊需要建立分級顯示的數(shù)據(jù)清單任意單元格，按照分組依據(jù)字段為主關(guān)鍵字進行排序，然后選擇創(chuàng)建組的行或列，單擊“數(shù)據(jù)”選項卡—>“分組顯示”組—>“創(chuàng)建組”按鈕，彈出“創(chuàng)建組”對話框，如圖所示，接著單擊“確定”按鈕，即可按照所選行或列創(chuàng)建組。以同樣的方式依次創(chuàng)建其他組，數(shù)據(jù)可實現(xiàn)分級顯示。分類匯總或創(chuàng)建分級顯示后的工作表最左側(cè)會出現(xiàn)分級顯示符號

、顯示明細數(shù)據(jù)按鈕

和隱藏明細數(shù)據(jù)按鈕

。分類匯總103、數(shù)據(jù)分級顯示顯示或隱藏明細數(shù)據(jù)已創(chuàng)建分級顯示的數(shù)據(jù)可以單擊工作表最左側(cè)的顯示明細數(shù)據(jù)按鈕和隱藏明細數(shù)據(jù)按鈕，顯示或隱藏明細數(shù)據(jù)。單擊分級顯示符號中某一級別的編號，處于較低級別的明細數(shù)據(jù)將被隱藏，單擊最低級別的分級顯示符號，所有明細數(shù)據(jù)都會顯示。清除分級顯示清除分級顯示的具體操作方法：首先單擊已建立分級顯示的數(shù)據(jù)區(qū)域任意單元格，然后單擊“數(shù)據(jù)”選項卡—>“分組顯示”組—>“取消組合”下拉列表中的“清除分級顯示”命令。分類匯總111、創(chuàng)建數(shù)據(jù)透視表創(chuàng)建數(shù)據(jù)透視表的具體操作步驟：步驟1：首先單擊需要創(chuàng)建數(shù)據(jù)透視表的數(shù)據(jù)區(qū)域任意單元格，然后單擊“插入”選項卡—>“表格”組—>“數(shù)據(jù)透視表”按鈕，彈出“創(chuàng)建數(shù)據(jù)透視表”對話框，如圖所示，接著在“選擇一個表或區(qū)域”中指定數(shù)據(jù)源區(qū)域、在“選擇放置數(shù)據(jù)透視表的位置”中選擇數(shù)據(jù)透視表生成新工作表還是在現(xiàn)有工作表中插入。數(shù)據(jù)透視表12步驟2：單擊“確定”按鈕，即可在指定位置插入空的數(shù)據(jù)透視表，如圖所示。步驟3：為空白數(shù)據(jù)透視表添加字段，可以直接將字段列表中的字段拖動到布局區(qū)域的指定區(qū)域（篩選器、列、行、值），如圖所示；也可以在字段列表的字段名上單擊右鍵，在彈出的快捷菜單中選擇相應(yīng)的命令。數(shù)據(jù)透視表13步驟4：刪除已添加的字段，只需要單擊取消已添加字段的字段名前的復(fù)選框選擇即可，如圖所示。步驟5：對已添加的字段進行篩選，單擊已添加字段右側(cè)的篩選箭頭，對當前字段的數(shù)據(jù)進行篩選。數(shù)據(jù)透視表142、數(shù)據(jù)透視表工具更改數(shù)據(jù)源如果數(shù)據(jù)透視表的數(shù)據(jù)源不完全正確，可以通過更改數(shù)據(jù)源功能來更改數(shù)據(jù)區(qū)域，具體操作方法：首先單擊數(shù)據(jù)透視表中任意單元格，然后單擊“數(shù)據(jù)透視表工具|分析”選項卡—>“數(shù)據(jù)”組—>“更改數(shù)據(jù)源”按鈕，彈出“更改數(shù)據(jù)透視表數(shù)據(jù)源”對話框，如圖所示，接著重新選擇或輸入新的數(shù)據(jù)區(qū)域即可。數(shù)據(jù)透視表15更新數(shù)據(jù)透視表數(shù)據(jù)透視表創(chuàng)建后，如果數(shù)據(jù)源中的數(shù)據(jù)進行了更改，可以通過刷新功能更新數(shù)據(jù)透視表的顯示，具體操作方法：首先單擊數(shù)據(jù)透視表中任意單元格，然后單擊“數(shù)據(jù)透視表工具|分析”選項卡—>“數(shù)據(jù)”組—>“刷新”按鈕即可。設(shè)置活動字段數(shù)據(jù)透視表創(chuàng)建后，可以對活動字段進行相應(yīng)設(shè)置。方法一：首先單擊數(shù)據(jù)透視表中任意單元格，然后單擊“數(shù)據(jù)透視表工具|分析”選項卡—>“活動字段”組—>“字段設(shè)置”按鈕，彈出“值字段設(shè)置”對話框，如圖所示。數(shù)據(jù)透視表16設(shè)置活動字段方法二：雙擊數(shù)據(jù)透視表中的活動字段即可彈出“值字段設(shè)置”對話框。（1）修改活動字段名稱：“值字段設(shè)置”對話框中，在“自定義名稱”文本框輸入新的字段名即可。（2）修改值匯總方式：“值字段設(shè)置”對話框中，在“值匯總方式”選項卡可以選擇值匯總的計算類型為求和、計數(shù)、求平均值、最大值、最小值等。（3）修改值顯示方式：“值字段設(shè)置”對話框中，在“值顯示方式”選項卡可以設(shè)置字段的值顯示方式為總計的百分比、列匯總的百分比、行匯總的百分比、百分比、父行匯總的百分比等，如圖所示。數(shù)據(jù)透視表17數(shù)據(jù)透視表選項數(shù)據(jù)透視表創(chuàng)建后，可以對其名稱、布局、匯總和篩選等選項進行修改。具體操作方法：單擊“數(shù)據(jù)透視表工具|分析”選項卡—>“數(shù)據(jù)透視表”組—>“選項”按鈕，彈出“數(shù)據(jù)透視表選項”對話框，可以對數(shù)據(jù)透視表名稱、布局、格式、匯總和篩選等進行設(shè)置。設(shè)置數(shù)據(jù)透視表格式數(shù)據(jù)透視表創(chuàng)建后，可以對其報表布局、數(shù)據(jù)透視表樣式等外觀格式進行設(shè)置。具體操作方法：單擊“數(shù)據(jù)透視表工具|設(shè)計”選項卡，選擇相應(yīng)的格式應(yīng)用到數(shù)據(jù)透視表即可。數(shù)據(jù)透視表183、數(shù)據(jù)透視圖數(shù)據(jù)透視圖是通過圖形形式對數(shù)據(jù)透視表中的匯總數(shù)據(jù)進行描述，與Excel圖表類似，數(shù)據(jù)透視圖是數(shù)據(jù)透視表數(shù)據(jù)更為直觀形象的呈現(xiàn)。創(chuàng)建數(shù)據(jù)透視圖的具體操作方法：首先單擊數(shù)據(jù)透視表中任意單元格，然后單擊“數(shù)據(jù)透視表工具|分析”選項卡—>“工具”組—>“數(shù)據(jù)透視圖”按鈕，彈出“插入圖表”對話框，接著選擇需要的圖表類型，單擊“確定”按鈕即可插入數(shù)據(jù)透視圖。數(shù)據(jù)透視表194、刪除數(shù)據(jù)透視表和數(shù)據(jù)透視圖刪除數(shù)據(jù)透視表的具體操作方法：首先單擊數(shù)據(jù)透視表，然后單擊“數(shù)據(jù)透視表工具|分析”選項卡—>“操作”組—>“選擇”下拉列表中的“整個數(shù)據(jù)透視表”命令，最后按Delete鍵。數(shù)據(jù)透視表刪除后，與之關(guān)聯(lián)的數(shù)據(jù)透視圖將變?yōu)槠胀▓D表。刪除數(shù)據(jù)透視圖的具體操作方法：首先單擊數(shù)據(jù)透視圖，然后按Delete鍵。數(shù)據(jù)透視表20Excel中的合并計算可以將多張工作表的數(shù)據(jù)匯總到指定工作表中，進行合并的工作表與合并后的目標工作表可以在同一工作簿，也可以在不同工作簿。合并計算的具體操作步驟：（1）首先單擊合并后工作表中的開始單元格，然后單擊“數(shù)據(jù)”選項卡—>“數(shù)據(jù)工具”組—>“合并計算”按鈕，彈出“合并計算”對話框，如圖所示。

（2）在“函數(shù)”下拉列表框中選擇合并計算的方式：求和、計數(shù)、平均值、最大值、最小值等。（3）在“引用位置”選擇需要合并的區(qū)域，然后單擊“添加”按鈕，合并區(qū)域就會添加到“所有引用位置”列表框中，如此依次添加多個需要合并的區(qū)域，如圖所示。（4）在“標簽位置”組中根據(jù)需要對標簽所在位置“首行”、“最左列”和“創(chuàng)建指向源數(shù)據(jù)的鏈接”進行選擇。（5）單擊“確定”按鈕，完成數(shù)據(jù)合并。合并計算21Excel模擬分析是通過更改單元格中的值來了解這些更改會如何影響引用這些單元格的公式結(jié)果的過程。1、單變量求解單變量求解的模擬分析是利用公式的計算結(jié)果去分析確定可能產(chǎn)生此結(jié)果的公式中某變量的輸入值。單變量求解的具體操作方法：首先單擊公式所在的目標單元格，然后單擊“數(shù)據(jù)”選項卡—>“預(yù)測”組—>“模擬分析”下拉列表的“單變量求解”命令，彈出“單變量求解”對話框，如圖所示，設(shè)置“目標單元格”（顯示目標值的單元格）、“目標值”（期望達到的值）和“可變單元格”（能夠達到目標值的可變量所在單元格）的值，接著單擊“確定”按鈕，彈出“單變量求解狀態(tài)”對話框，最后單擊“單變量求解狀態(tài)”對話框中的“確定”按鈕。模擬分析222、模擬運算表單變量模擬運算表單變量模擬運算表的具體操作方法：首先輸入數(shù)據(jù)與公式，然后選擇需要創(chuàng)建模擬運算表的數(shù)據(jù)區(qū)域，然后單擊“數(shù)據(jù)”選項卡—>“預(yù)測”組—>“模擬分析”下拉列表的“模擬運算表”命令，彈出“模擬運算表”對話框，接著選擇變量所在單元格，如果變量在一行，則在“輸入引用行的單元格”中選擇行變量公式所在單元格，如果變量在一列，則在“輸入引用列的單元格”中選擇列變量公式所在單元格，最后單擊“確定”按鈕，即可在選定區(qū)域生成模擬運算表。雙變量模擬運算表具體操作步驟：（1）首先輸入數(shù)據(jù)與公式，然后輸入變量，在公式所在行輸入一個變量的系列值，公式所在列輸入另一個變量的系列值。（2）首先選擇需要創(chuàng)