1、第七講 數據的管理與分析通過對工作表和圖表的學習，我們了解了 Excel2000 有很強的表格處理和圖 表功能。在數據的管理分析方面，盡管 Excel 并不是一個真正的數據庫管理系統， 但在管理數據、 操作數據、 作數據分析透視等方面， Excel 卻能實現數據庫的管理 職能。實際上， Excel 是將數據列表用作數據庫的。一、數據列表所謂數據列表是包含相關數據的一系列工作表數據行，例如一批學生成績、 一組商品發貨單數據，或一組單位名稱和聯系電話等等。在數據列表中，第一行 數據通常用來作為數據列表的列標題，對列表的內容進行說明，它相當于數據庫 中的字段名稱；其余各行的內容都是由列標題所標識的具

2、體數據組成，每一行就 象是數據庫中的一個記錄。在執行數據列表中數據的排序、篩選、分類匯總時， Excel 會自動將數據列表視作一個數據庫。數據列表不同與一般的工作表，在數據列表中每列必須有列名，且每一列必 須是同類型的數據。可以說數據列表是一種特殊的工作表。例如，上一節中介紹 過的“學生公共課成績表”就可以看作是一個數據列表，如圖 7-1 所示。圖 7-1 數據列表示意圖1. 數據列表數據的輸入在工作表中建立一張數據列表后， 就可以輸入數據了。 在建立數據列表時需要注意以下幾點：圖7-2 “記錄單”對話框一個工作表上最好只建一個數據列表； 數據列表中最好不要有空白行和空白列； 在數據列表中的第

3、一行一定是列表頭（字 段名），名字必須唯一；數據列表中同一列上的數據類型要完全查看、更改、添加及刪除數據列表中的記錄。在圖7-1所示的數據列表中選定任一單元格，選擇“數據”菜單t“記錄單”命令，屏幕顯示圖7-2所示記錄單對話框，一個記錄單一次只顯示一個完整的記錄。如果在記錄單上輸入或編輯數據， Excel將在數據列表中及時更改相應的單元格內容。2. 列表數據的編輯數據列表的編輯可以和一般的工作表數據的編輯一樣進行，由于數據列表的 特殊性，對數據修改后，要注意保證數據列表中數據的完整性。所以數據列表通 常是以記錄（一行）或字段（一列）為單位進行編輯的。（1）添加新記錄可以在數據列表的末尾依次輸入

4、數據，添加一條新記錄，不過要保持每一列 數據類型的一致性。（2）插入記錄可以先插入空白的行，然后再在空白行中輸入數據。（3）刪除記錄選中要刪除的一個或多個記錄（行），然后選擇t“編輯”菜單t“刪除”命令項（注意！不是用“清除”）。（4）增加字段可以先插入空白的列，然后在空白的列中輸入字段名及具體的數據。（5）刪除字段選中要刪除的一個或多個字段（列），然后選擇t“編輯”菜單t“刪除”命令項，刪除字段實際上是刪除字段所在的列。需要注意的是：如果在一張工作表中有多個數據列表，那么，在插入和刪除 記錄、字段時，就不能簡單地插入或刪除行和列，而只能是插入或刪除局限在本 數據列表范圍的單元格區域中的數據。

5、二、記錄（行）的排序Excel2000的排序功能允許對數據列表中的所有記錄（行）按照某種約定重新安排次序，從而實現用戶按自己的要求對數據列表進行組織和管理。1. 簡單排序直接使用“常用”工具欄上的“升序”、“降序”按鈕就可以實現簡單的數據排 序。例如要對圖4-93中“計算機”成績作重新排序，可以這樣操作：單擊“學生公共課成績表”中“計算機”列上的任一單元格-選擇“常用”工具欄-“降序”（“升序”）按鈕（如圖7-3所示），即可將“計算機”成績從高（低） 到低（高）重新排列。“升序”按鈕| “降序”按鈕圖7-3“常用”工具欄上的“降序” | / “升序”按鈕2. 復雜數據排序如果排序要求復雜一點，

6、就需要使用“數據”菜單的“排序”命令了。例如 在“學生公共課成績表”中先按“總分”降序排序，若總分相等，再按“語文” 降序排序，語文相等，再按“數學”降序排序。這個排序過程已不再局限于單列 之中了，所以必須使用“數據”菜單的“排序”命令。操作步驟如下：第一步單擊數據列表區域內的任何一個單元格;第二步 選擇“數據”菜單t“排序”命令項-彈出對話框，如圖7-4所示；第三步 在“排序”對話框中設置排序條件；第四步 單擊“確定”按鈕，完成排序操作完成。圖7-4“排序”對話框第三步中排序條件的設置：Excel在排序時以“主要關鍵字”作為排序的依據， 當主要關鍵字相同時按 “次要關鍵字”排序，如果次要關鍵

7、字又相同， 再考慮“第 三關鍵字”。每個關鍵字有“遞增”和“遞減”兩種排列順序。在Excel中，用字段名作為排序的關鍵字。在排序中必須指明主要關鍵字。其他的關鍵字可以沒有。可以在對話框的“當前數據清單”區設置“有標題行”或“沒有標題行”。Excel 據此確定數據列表是否有標題行（字段名）。一般Excel會自動判別數據庫中是否 有標題行，所以通常也不需要設置。如果有標題行，而指定的是“無標題行”，就會將標題行作為數據記錄排序到數據列表中。請記住：如果排序結果不對，要立刻用菜單命令“編輯”菜單-“撤消排序”， 把剛才的操作撤消，恢復數據列表原來的樣子。Excel默認狀態為按字母順序排序，如果需要按

8、時間順序（對月份或星期）數據排序，圖7-5 “排序選項”，對話框可以自定義排序次序：單擊“排序”對話框-“選 項”按鈕，然后在彈出的“排序選項”對話框（見 圖7-5 ）中進行設置。對話框中的“自定義排序次序”只能作用于“主如果希望根據行的數據對數據列排序，則可用下面的方法:第一步 在“排序”對話框中，單擊“選項”按鈕;，單擊“確第二步 在“排序選項”對話框的“方向”框中設置“按行排序” 定”；第三步 在“主要關鍵字”和“次要關鍵字”中，選定需要排序的數據行， 單擊“確定”三、數據的篩選要顯示出數據列表中滿足給定條件的記錄行數據，把不滿足條件的數據暫時 隱藏起來，可用自動篩選來達到要求。 Exc

9、el 提供了自動篩選和高級篩選兩種方法， 其中自動篩選較簡單，而高級篩選的功能較強，可以利用復雜的篩選條件進行篩 選。1自動篩選 在“學生公共課成績表”中插入“性別” 、“出生年月”兩列數據：如圖 7-6 所示。圖 7-6 插入“性別” 、“出生年月”兩列數據的“學生公共課成績表”（l ）自動篩選操作 自動篩選的操作步驟如下：第一步 選定數據列表中的任一單元格；第二步 選擇“數據”菜單T“篩選”命令項T“自動篩選”命令。此時在 字段名（列標題）的右下角顯示一個下拉控制箭頭，如圖 7-7 所示。第三步 單擊某一字段名的下拉控制箭頭，出現下拉列表。在下拉列表中， 通常包括該字段中每一獨有的選項，另

10、外還有五個選項：全部、前十個、自定義、 空白、非空白。第四步 在下拉列表中單擊某一個具體的值，這時符合條件的記錄被顯示， 不符合篩選條件的記錄均隱藏起來。例如，單擊“數學” 字段名的下拉箭頭按鈕，然后在列表中選擇87，則所有 數學成績是 87 分的學生記錄都篩選出來了。 調選出的記錄所在加列號變成了藍色。 如果在下拉列表中選定 “空白”，就僅顯示該字段為空白的那些記錄， 選定“非 空白”則僅顯示該字段不為空白的那些記錄。 恢復隱藏的記錄恢復的方法有：圖 7-7 “自動篩選”窗口單擊“數據”菜單T“篩選”命令項T“全部顯示”命令，可恢復顯示數據庫所有的記錄，這時菜單中“自動篩選”命令旁的“V”仍

11、存在，所以篩選的下拉 按鈕也在，仍可以繼續篩選。選擇“數據”菜單 1“篩選”命令項1“自動篩選”命令，此時“自動篩選”旁的“V”消失，結束自動篩選，數據列表恢復成原樣。 最簡單的方法是：只須在字段名的下拉菜單中選擇“全部”即可。 （2）自動篩選的“自定義”在圖 4-99 中，如果單擊 “數學” 字段下拉表中的 “自定義” 項，就會彈出 “自 定義自動篩選方式”對話框，如圖 7-8 所示。在對話框中可以自定義自動篩選的 條件，這些條件當然不像是選擇某一個值那么簡單。在對話框的左下拉列表中可以規定關系操作符（大于、等于、小于等等） ，在 右下拉列表中則可以規定字段值，而且兩個比較條件還能以“或者”

12、或“并且” 的關系組合起來形成復雜的條件。例如，可以自定義篩選條件為數學成績在 85 分和 95 分之間（大于等于 85 并 且小于等于 95），見圖 4-100 。圖 7-8 “自定義自動篩選方式”對話框通過對多個字段的依次自動篩選，就可以疊加為復雜一些的篩選操作。例如要篩選出英語和數學成績都在 85分以上的學生的記錄，可以先篩選出“英語成績在85分以上”的學生記錄，然后在已經篩選出的記錄中繼續篩選“數學成績在85分以上”的記錄。2.高級篩選對于復雜的篩選條件，可以多次使用“自動篩選”來疊加完成。但如果使用“高級篩選”功能，既直觀又方便，使用效率上事半功倍。使用“高級篩選”的 關鍵是學會如何

13、設置由用戶自定義的復雜組合條件，這些組合條件常常是放在一 個稱為條件區域的單元格區域中。（1）篩選的條件區域使用“高級篩選”時需要一個“條件區域”，條件區域包括兩個部分：標題行 （也稱字段名）；一行或多行的條件行。創建一個條件區域的操作步驟如下：第一步 在數據列表記錄的下面準備好一個空白區域；第二步 在此空白區域的第一行輸入字段名作為條件名行，最好是把字段名行整個都復制過來，以免輸入時因大小寫或有多余的空格而造成不一致；第三步 在字段名的下行幵始輸入條件。（2）篩選的條件簡單比較條件簡單條件是指只用一個簡單的比較運算（二、=、二、 ）表示條件。在條件區域字段名正下方的單元格內輸入條件，如：姓名

14、英語數學 80=85當是等于（二）關系時。等號“二”可以省略。當某個字段名下沒有條件時, 允許空白，但是不能加上空格，否則將得不到正確的篩選結果。對于字符型字段，在條件行中可以使用通配符“*”及“”。字符的大小比較按照字母順序進行。對于漢字，則以拼音為順序。如果是漢字、字符串用于比較 條件中，那么必須用雙引號“”括起來（如“男”、“ Beijing ”等）。例題一：在“學生公共課成績表”中篩選出滿足條件“數學=85分且英語80 分”的記錄，條件區域的設置如圖 7-9所示。圖7-9條件區域的設置 組合條件對于需要使用多重條件在數據列表中篩選記錄，就必須把條件組合起來。基本的形式有兩種：在同一行內

15、的條件表示 AND（“與”）關系，也就是“所有條件為真（成立）結論才真”。例如：要篩選出“所有姓劉并且計算機成績不低于86分”的記錄，條件表示為：如果要建立一個條件為某姓名計算機劉*=86字段的值的范圍，必須在同一行的不同列中為每一個條件建立字段名。例如：要篩選出“所有姓劉并且英語成績在70至79之間”的記錄，條件表示為:有條件中只要有一個條件為姓名英語英語劉*=70=8060語分數低于60分”。在條件區由以上的例子可以總結出規則一：當在數據列表件時，必須在同一行的不同姓名英語劉*60組合條件的表示規則如下:的不同字段中使用多重條列中輸入；S W K 弘 0 倉1401011L昭：3-|呂 n

16、re.-ifi悖劊 出蘭巳牛 jr iur-12 3r rmxHUILUS5-JL-Z IW-S-IT* Si盤 i葉m 押 lsk號驢 與 i娜侖&話芟當HE*刃篇一丈 B Q I 7BI 7- D 9K 矗 hBLtf-M 黑w*?iT恥站 C- h- - A- r - n- K- _u E -H.- 鋁!:(.H471Z;汕t- aJK 聃 WUT3.LD伍 JBK4B4QG 衆 F J 3 3 T- J 3 1S 1M ill 立 El 胡JLf lifiM聶甘平咅校圖 7-10條件“數學=85分或語文85分或英語80分”的設置-上Ml閱叫出“和 Eh： jEtTBM13*jgM Vf

17、HJ tfitl IV li 斥此血 IftiJ trfliji 音口葉 時 ft口 空bltJbGX號 &陽電 * 門， 170。在條件名行增加條件名“語文數學”，在 其下輸入計算條件。表示為：語文數學=F2+E2170圖7-11 置處理過程如圖7-11圖7-13所示。(3)高級篩選的操作高級篩選的操作步驟為：第一步按照前面所講的方法建立條件區域；第二步 在數據列表區內選定任一個單元格；第三步 選擇“數據”菜單t“篩選”命令項t“高級篩選”命令，彈出“高級篩選”圖7-12利用“計算條件”作“高級篩選”圖7-13滿足“計算條件”的篩選結果對話框，如圖7-14所示；第四步 在“高級篩選”對話框中

18、選中“在原有區域顯示篩選結果”選項；第五步 輸入“條件區域”。“數據區域”是自動獲取的，如果不正確，可以 更改；第六步 單擊“確定”按鈕，屏幕顯示篩選出符合條件的記錄。若希望把篩選出的結果復制到一個新的位置，則在“高級篩選”對話框中選 定“將篩選結果復制到其地位置”選項，并且還要在“復制到”區域中輸入要復 制到的目的區域的首單元地址。注意，以首單元地址為左上角的區域必須足夠多 的空位存放篩選結果，否則將覆蓋該區域的原有數據。若希望把篩選的結果復制到另外的工作表中，則必須首先激活目標工作表，然后再選“數據”菜單篩選”命令項高級篩選”項，在“高級篩選”對 話框中，輸入“數據區域”和“條件區域”時要

19、注意加上工作表的名稱，如數據區域為Sheetl ! A1:G15,條件區域為 Sheetl ! A20:B22，而復制到的區域直接為A1。這個A1是當前的活動工作表（比如 Sheet2 ）的A1,而不是源數據區域所在的工作表Sheet1 0如果不想從一個數據列表中提取全部字段，就必須先定義一個提取區域。在提取區域的第一行中給出要提取的字段及字段的順序。這個提取區域就作為高級篩選的結果“復制到”的目的區域的地址，Excel2000會自動在該區域中所要求的字段下面列出篩選結果。 例如把A15:C15作為提取區域，區域中輸入的內容如下:單元格A15B15C15內容姓名總分平均分圖7-15提取數據列表

20、的部分字段的數據到新區域然后在“復制到”區域中輸入 A15:C15，由條件區域所指定的記錄的姓名、總 分、平均分三個字段的數據將被復制到提取區域A15:C15下面的新位置上。例如，把“學生公共課成績表”中的姓名、總分、平均分三個字段的數據復 制到提取區域A15:C15下面的新位置，執行結果如圖 7-15所示。如果要刪除某些符合條件的記錄，可以在篩選后（在原有區域顯示篩選結果） 選中這些篩選結果，選“編輯”菜單t“刪除”命令項即可。在“高級篩選”對話框中，選中“選擇不重復的記錄”復選框后再篩選，得 到的結果中將剔除相同的記錄（但必須同時選擇“將篩選結果復制到其地位置” 此操作才有效） 。這個特性

21、使得用戶可以將兩個相同結構的數據列表合并起來，生 成一個不含有重復記錄的新數據列表。 此時篩選的條件為 “無條件”，具體做法是： 在條件區域只寫一個條件名，條件名下面不要寫任何的條件，這就是所謂的“無 條件”。四、分類匯總 在對數據列表中的數據進行分析處理時，有時需要多次重復輸入公式或調用 函數來計算這些數據的總和、平均值以及乘積等。如果利用 Excel 中的分類匯總 命令在數據列表中插入分類匯總值，那么可以使工作效率再次提高。1. 為數據列表插入分類匯總 分類匯總是指將數據列表中的記錄先按某個字段進行排序分類， 然后再對另一 字段進行匯總統計。匯總的方式包括求和、求平均值、統計個數等等。為便

22、于討論問題，現在“學生公共課成績表”中再插入“籍貫”列標題及數 據。例題一：在“學生公共課成績表”中按“性別”分類統計出男、女學生四門 課程各自的平均成績，操作步驟如下：第一步 按“性別”字段將記錄行排序；第二步 選擇“數據”菜單T“分類匯總”命令項T彈出“分類匯總”對話框，如圖 7-16 ；圖7-16 “分類匯總”對話框第三步 在“分類字段”下拉列表框中選擇“性別”字段；注意這里選擇的字段就是在第1步排序時的主關鍵字。第四步 在“匯總方式”下拉列表框中選擇分類匯總的結果如圖7-17所示。如果要撤消分類匯總，可以選“數據”菜單t“分圖7-17“分類匯總”后的結果類匯總”命令項-進入“分類匯總”

23、對話框-單擊“全部刪除”按鈕，即可恢復原來的數據列表清單。2.分級顯示工作表數據在對數據進行了分類匯總之后，如何來查看數據列表中的明細數據或單獨查看匯總總計呢在圖4-106中分類匯總數據的左端出現了一個區域和一些帶有“ 1”、“2”、“ +”、“-”等符號的按鈕，單擊這些按鈕符號，數據列表中顯示的數據就會 發生改變。帶有“+”的符號按鈕被稱為顯示明細數據按鈕，單擊它可以在數據列表中顯示出數據列表的明細數據，事實上，圖7-17所顯示的就是明細數據。帶有“-”的符號按鈕被稱為隱藏明細數據按鈕，單擊它可以在數據列表中隱藏數據列表的明細數據，如圖 7-18所顯示的就是隱藏了“女”學生四門課程平均值的明

24、細數據。如果要指定分級顯示的級別，可單擊左側上方標有“1”、“2”、“3”字樣的按鈕，例如，如果要在分級顯示中包括一個顯示級別，單擊標有“1”符號的按鈕，則數圖7-18隱藏了“女”學生四門課程平均值的明細數據圖7-19包含一個顯示級別的分級顯示五、數據透視表分類匯總適合于按一個字段進行分類，對一個或多個字段進行匯總的問題，在日常工作中，很多問題要求按多個字段進行分類并且匯總，此時如果用前面介紹 的用分類匯總方法來解決就顯得無能為力。為此，Excel為用戶提供了用數據透視表功能來解決問題。數據透視表是一種對大量數據快速匯總和建立交叉列表的交互式表格。它可以轉換行和列以查看源數據的不同匯總結果，可

25、以顯示不同頁面以篩選數據，還可 以根據需要顯示區域中的明細數據。我們先建立一個“學生身體素質表”，列標題有“編號”、“姓名”、“性別”、“出 生年月”、“籍貫”、“專項”、“身高”、“體重” 等,列標題下有15條記錄（如圖7-20 所示），結合這一數據列表，利用數據透視表的向導功能，了解一下數據透視表的 基礎知識。圖7-20“學生身體素質表”1.數據透視表的建立比如要統計“學生身體素質表”中各專項中男、女生的人數，此時既要按所 學專項分類，又要按性別分類，用數據透視表來解決問題，操作步驟是：第一步打幵“學生身體素質表”，用鼠標單擊數據列表中任一單元格；第二步 選擇“數據”菜單數據透視表和圖表報

26、告”命令T顯示“數據 透視表向導-3步驟之I”對話框，如圖7-21所示；“步驟之1”對話框主要是用 來指定數據源（默認為“ Microsoft Excel數據清單或數據庫”，一般選擇該項）和創建的報表類型（默認為數據透視表，一般也選擇該項）；圖7-21 “數據透視表向導-3步驟之I ”對話框單擊“下一步”按鈕，出現如圖7-22所示“數據透視表向導-3步驟之2”對話 框。第三步 “步驟之 2”對話框主要用來選定數據區域， 一般情況下選定整個列 表，圖 7-22 “數據透視表向導 -3 步驟之 2 ”對話框 用戶可鍵入新值重新選定區域，單擊“下一步”按鈕，出現如 7-23 所示“數據透 視表向導

27、-3 步驟之 3”對話框。圖 7-23 “數據透視表向導 -3 步驟之 3 ”對話框圖 7-24 數據透視表布局示意圖第四步 “步驟之 3”對話框決定數據透視表的放置位置， 選擇“新建工作表” 單選鈕，將數據透視表放置在工作薄新建的工作表中，并成為活動工作表。選擇 “現有工作表”則還須指定數據透視表在現有工作表放置的位置。選“新建工作 表”，單擊“完成”按鈕，此時屏幕顯示數據透視表布局示意圖，如圖 7-24 所示第五步 在數據透視表的布局示意圖的旁邊，彈出的“數據透視表”工具欄 中列出了所有字段，將要分類的字段可拖入示意圖的行、列位置，就成為透視表 的行、列標題。將需要匯總的字段拖入示意圖的數

28、據區，拖入頁位置的字段將成 為分頁顯示的依據。例題中將“性別”字段拖曳到行位置， “專項”字段拖曳到列位置，數據項區 中拖入的是“姓名”字段，這樣操作之后就出現如圖 7-25 所示的結果。圖 7-25 生成“數據透視表”拖入數據區的匯總對象如果是非數字型字段，則默認為對其計數，如為數字 型字段則默認為求和。2. 透視表的編輯修改 數據透視表建好之后，根據實際情況，隨時可作出修改、編輯，具體操作步驟是：選擇“視圖”菜單T“工具欄”命令項T“數據透視表”命令T彈出“數據透視表”工具欄（更簡捷的辦法是：鼠標指向屏幕上工具欄的空白區，單擊右鍵， 彈出工具欄名稱框，選中其中的“數據透視表”即可） ，使用

29、該工具欄中的按鈕可 用于對數據透視表的修改。（ 1）增刪數據透視表中的字段例如，將圖 7-25 之中“數據透視表”的行字段“性別”刪去，增加一個“籍 貫”行字段，具體操作步驟如下：第一步 用上述方法將“數據透視表”工具欄顯示在屏幕上；第二步 將“性別”行字段拖出數據透視表，將“籍貫”拖入數據透視表的行 字段。如圖 7-26 所示，將行、列、頁、數據字段移出表示刪除該字段，移入表示 增加該字段。圖 7-26 拖出“性別”移入“籍貫”后的“數據透視表”（2）改變匯總方式 不同類型的數據有不同的默認匯總方式。 用戶如果不想使用默認的匯總方式， 可以改變為其它如平均值、最大 （ 小） 值的匯總方式。比如前面曾對“體重”求和。 現在希望改為統計體重的平均值。其操作步驟如下：圖 7-27 從“數據透視表”下拉式菜單中選定“字段設置”第一步 選擇數據透視表的數據區中任一單元格，如求和數據所在單元格；第二步 單擊“數據透視表”工具欄T“數據透視表”下拉式按鈕T彈出下拉式菜單-“字段設置”命令項-顯示“數據透視表字段”對話框，如圖7-27所示;第三步 對話框-從匯總方式列表框中選擇“平均值” ，如圖7-28所示；圖 7-28 “數據透視表字段”對話框第四步 單擊“確定”按鈕，改變匯總方式完成，結果如圖 7-29 所示。圖 7-29 改變匯總方式后的“數據透視表”第