第6章管理工作表中

的數據

除了利用公式和函數對數據進行計算和分析外，Excel還提供了數據清單、數據排序、數據篩選、分類匯總以及合并計算等功能，利用這些功能可以方便地管理、分析各類數據，從而為企事業單位的決策管理提供可靠的依據。本章內容提要建立數據清單

數據排序數據篩選分類匯總合并計算數據分析6.1建立數據清單

在Excel中，數據清單是包含一系列數據的工作表行，清單的第一行具有列標志，緊隨其后的行是數據行，在標題與數據、數據與數據之間不能有空行，而且同一列數據必須具有同樣的類型和含義。

在執行數據操作的過程中，Excel會自動將數據清單視作數據庫，即數據清單中的列被視作數據庫的字段，數據清單的列標記被視作數據庫的字段名稱，數據清單的每一行被認為是數據庫的一條記錄。6.1.1建立數據清單的準則數據清單的大小和位置（1）避免在一個工作表中建立多個數據清單，因為數據清單的某些處理功能（如篩選等），一次只能在同一工作表的一個數據清單中使用。（2）在工作表的數據清單和其他數據間至少留出一個空白列和一個空白行，從而在執行排序、篩選或插入自動匯總等操作時，有利于Excel檢測和選定數據清單。（3）避免在數據清單中放置空白行和列，從而有利于Excel檢測和選定數據清單。（4）避免將關鍵數據放到數據清單的左右兩側。因為這些數據在篩選數據清單時可能會被隱藏。列標志（1）需在數據清單的第一行里創建列標志。Excel將使用這些標志創建報告，并查找和組織數據。（2）列標志使用的字體、對齊方式、格式、圖案、邊框或大小寫樣式，最好與數據清單中其他數據的格式相差別。行和列內容（1）在設計數據清單時，應使同一列中的各行數據具有同樣的類型和含義。（2）在單元格的開始處不要插入多余的空格，因為多余的空格影響排序和查找。（3）不要使用空白行將列標志和第一行數據分開。6.1.2直接輸入數據清單

建立數據清單分以下兩個步驟：（1）建立數據清單的標題：在數據清單的標題行中鍵入各字段名。（2）輸入各項記錄：可以直接在數據清單中鍵人數據（該方式下當數據清單欄目較多時容易出錯）；或使用“記錄單”輸入數據。例如要制作一個通訊錄，首先要規劃好數據清單的結構，即字段，然后在工作表中輸入相應的數據。6.1.3使用記錄單輸入與編輯數據1．使用記錄單輸入數據

2．使用記錄單編輯數據

若要添加新的記錄，可在記錄單對話框中單擊“新建”按鈕，然后輸入各字段內容即可。重復該操作可添加多條記錄。添加完畢，單擊“關閉”按鈕，退出記錄單對話框。若要修改記錄，可單擊“上一條”或“下一條”按鈕查找并顯示出要修改數據的記錄，如圖6-8所示，然后修改該記錄的內容。修改完畢，單擊“關閉”按鈕，退出記錄單對話框。若要刪除記錄，可單擊“上一條”或“下一條”按鈕查找要刪除的記錄，然后單擊“刪除”按鈕，彈出提示對話框，單擊“確定”按鈕，在記錄單中顯示的記錄被刪除。6.2數據排序

排序是對工作表中的數據進行重新組織安排的一種方式。Excel可以對整個工作表或選定的某個單元格區域進行排序。在Excel中，可以對一列或多列中的數據按文本、數字以及日期和時間進行排序，還可以按自定義序列（如大、中和?。┻M行排序。大多數排序操作都是針對列進行的，但是，也可以針對行進行。6.2.1簡單排序

簡單排序是指對工作表中的單列數據按照Excel默認的升序或降序方式進行排列。升序排序數字：按從最小的負數到最大的正數進行排序。日期：按從最早的日期到最晚的日期進行排序。文本：按照特殊字符、數字（0…9）、小寫英文字母（a…z）、大寫英文字母（A…Z）、漢字（以拼音排序）進行排序邏輯值：FALSE排在TRUE之前。錯誤值：所有錯誤值（如#NUM!和#REF!）的優先級相同?？瞻讍卧瘢嚎偸欠旁谧詈?。降序排序與升序排序的順序相反。對“水果名稱”列進行升序排序6.2.2多個關鍵字排序

多關鍵字排序，也就是對工作表中的數據按兩個或兩個以上的關鍵字進行排序，。

下面將水果價格表中的“水果名稱”、“產地/規格”和“最高價”按升序進行排序。將水果價格表中“水果名稱”、“產地/規格”和“最高價”按升序進行排序后的效果6.2.3自定義排序

如果已有的排序規則無法滿足用戶要求，此時可以用自定義排序規則來解決。在該方式下，需首先自定義序列，然后再按自定義的序列排序。

自定義序列

自定義排序結果6.3數據篩選Excel的數據篩選功能為我們提供了一種方便、快捷的數據查詢方法，它只顯示符合條件的數據，而將不符合條件的數據隱藏起來，且隱藏起來的數據不會被打印出來。要進行篩選數據操作，工作表中必須有列標簽。6.3.1自動篩選

自動篩選一般用于簡單的條件篩選，篩選時將不滿足條件的數據暫時隱藏起來，只顯示符合條件的數據。

將水果價格表中“產地/規格”為“國產”的記錄篩選出來了6.3.2自定義篩選

在篩選按鈕下拉列表中選擇“自定義”項，可以限定一個或兩個篩選條件，以顯示出所需的數據。下面將水果價格表中“均價”大于或等于10且小于或等于25的記錄篩選出來。篩選出水果價格表中“均價”大于或等于10且小于或等于25的記錄6.3.3高級篩選

高級篩選用于根據多個條件來查詢數據。要使用高級篩選，應首先在選定工作表中的指定區域創建篩選條件，然后打開“高級篩選”對話框，接下來分別選擇要篩選的單元格區域、篩選條件區域和保存篩選結果的目標區域即可。1．多條件篩選

多條件篩選，即利用高級篩選功能查找出同時滿足多個條件的記錄。

下面篩選“水果價格表中最高價、最低價和均價均大于或等于10的記錄。水果價格表中最高價、最低價和均價均大于或等于10的記錄被篩選出來一定要將列標簽一起選取2．多選一條件篩選

多選一條件篩選，也就是只要滿足幾個條件當中的一個，記錄就會顯示出來。下面查找出水果價格表中“產地/規格”為“國產”或“均價”大于10的記錄。

篩選出水果價格表中“產地/規格”為“國產”或“均價”大于10的記錄6.3.4取消篩選若要在工作表中取消對某一列進行的篩選，只需單擊該列列標右端的篩選箭頭，再在展開的列表中單擊“全部”項即可。若要在工作表中取消對所有列進行的篩選，則選擇“數據”>“篩選”>“全部顯示”菜單。若要刪除工作表中的篩選箭頭，則選擇“數據”>“篩選”>“自動篩選”菜單6.4分類匯總

分類匯總是把工作表中的數據按指定的關鍵字進行各種匯總。它不需要建立公式，Excel會自動對各類別的數據進行求和、求平均值等多種計算，并且分級顯示匯總的結果。

對工作表的數據進行分類匯總時，應注意如下事項：因為分類匯總是按字段名進行的，所以要進行分類匯總的數據表的第一行必須有列標簽，并且數據區域中沒有空行或空列。在分類匯總之前必須先對數據進行排序，將要進行分類匯總的數據信息組合在一起，然后再進行分類匯總操作。進行分類匯總前，應選擇數據區域中的任意一個單元格。在“分類字段”下拉列表框中選擇字段時，該字段必須是已排序的字段，若選擇其他列，則分類匯總結果是不正確的。6.4.1簡單分類匯總

簡單分類匯總主要用于對數據表中的某一列進行排序，然后進行分類匯總。下面對病人登記表中的“手術方式”進行統計。對“手術方式”列進行升序排序

簡單分類匯總結果6.4.2多重分類匯總

對工作表中的某列數據選擇兩種或兩種以上的分類匯總方式或匯總項進行匯總，就叫多重分類匯總，也就是說，多重分類匯總每次用的“分類字段”總是相同的，但匯總方式或匯總項不同。

下面將病人登記表按“性別”對“手術中失血總量”和“手術中輸血量”進行求和。對“性別”列進行降序排序

第一重分類匯總結果

兩重分類匯總結果6.4.3嵌套分類匯總

嵌套分類匯總是指在一個已經建立了分類匯總的工作表中再進行另外一種分類匯總，兩次分類匯總的字段不相同。

下面查看病人登記表中“手術中輸血次數”總計，以及“性別”列男、女個數。

對“手術中輸血次數”和“性別”列進行升序排序

第一次分類匯總結果分類匯總項目設置與第一次不同

嵌套分類匯總結果6.4.4分級顯示或隱藏匯總結果1．分級顯示明細數據

在分級顯示符號中，單擊所需級別的數字，較低級別的明細數據會隱藏起來。其中數值越大的顯示符號表示顯示的匯總結果就越詳細，單擊符號可隱藏分類匯總結果的所有明細數據。2．隱藏與顯示明細數據

要隱藏工作表中某組明細數據行，可以單擊該組行號左側的按鈕。此時按鈕變為按鈕，單擊該按鈕可重新顯示本組中的明細數據。

要隱藏明細數據，也可以單擊某組中的單元格，然后選擇“數據”>“組及分級顯示”>“隱藏明細數據”菜單。隱藏明細數據后，選擇“組及分級顯示”子菜單中的“顯示明細數據”項，可顯示明細數據。3．清除分級顯示

進行分類匯總后，如果只需要保留分類匯總的各項明細數據，而不需要分級顯示符號，此時可選擇“數據”>“組及分級顯示”>“清除分級顯示”菜單。分級顯示符號消失

6.4.5取消分類匯總工作表中的分類匯總結果被清除6.5合并計算6.5.1建立合并計算

所謂合并計算，是指用來匯總一個或多個源區域中數據的方法。

要想合并計算數據，首先必須為合并數據定義一個目標區，用來顯示合并后的信息。此目標區域可位于與源數據相同的工作表中，也可位于另一個工作表中。其次，需要選擇要合并計算的數據源。此數據源可以來自單個工作表、多個工作表或多個工作簿。1．按位置合并計算

按位置合并計算，要求源區域中的數據使用相同的行標簽和列標簽，并按相同的順序排列在工作表中。

下面將幾個銷售部門辦事費用開支的工作表“按位置合并計算”到公司的總開支工作表中。

銷售各部費用表

在工作表“三部”的右側新建一個與“一部”格式一致的工作表“總部”

用同樣的方法將“二部”和“三部”中的單元格區域B3：F6添加到“所用引用位置”列表中得到相同季度各部門各費用的小計及合計2．按分類合并計算

當源區域中的數據沒有相同的組織結構，但有相同的行標簽或列標簽時，需采用按分類合并計算方式進行匯總。

下面將兩個部門的電腦配件費用表合并到“總費用”工作表中。

兩個部門的費用表

按分類合并計算結果6.5.2更改合并計算1．增加源區域

如果增加了新的工作表，要將這個源區域數據添加到一個已存在的合并計算中，只需單擊要放置合并結果工作表單元格區域左上角的單元格，接下來的操作與前面介紹的一樣：將其添加到“所有引用位置”列表中并確定即可。

對來自多個工作表中的數據進行合并計算后，如增加或刪除了源區域中的某些數據，或添加（刪除）了作為數據源區域的某個工作表，此時就需要更改合并計算。需要注意的是：只有對沒有建立與源區域鏈接的合并工作表，才能夠執行更改操作。2．從合并計算中刪除部分源區域重新選擇數據區域

例如，要將“電腦配件費用表”工作簿“甲部”中的“鍵盤”和“鼠標”的有關數據不計算在內，此時就需要更改“總費用”工作表中引用的區域。刪除先前添加的引用區域更改前的合并計算結果更改后的合并計算結果6.6數據分析Excel為數據查詢、制作報表、數據運算等數據處理提供了許多分析工具和實用函數，這些工具和函數在解決工程計算、金融分析和財政決算等方面發揮著巨大作用。

安裝加載宏并添加相應的分析工具6.5.1假設分析Excel2003提供了與假設前提相關的三種簡單的數據分析方法：單變量求解、模擬數據表和方案管理器。它們的共同特點是問題的求解都由兩個部分組成：一個待求解目標模型，一組與模型相關的變量。通過假設求解過程可以獲得兩種類型的結果：一是在指定假設目標結果的制約下與目標相關某因素的可行解，二是能夠在指定變量值域范圍的前提下觀察各變量對于模型所產生的影響。

假設公式（或函數）中的一個或兩個變量有一組替換值，將這些值分別代入公式（或函數）中取得一組結果，這組變量替換值和結果值構成的一個表稱為模擬運算表。若只有一個變量，稱為單變量模擬運算表；若有兩個變量，稱為雙變量模擬運算表。模擬運算表可以將變量取不同值時對應的結果一次計算出來，并按規