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

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

或“降序”按鈕

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

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

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

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

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

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

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