計算機基礎上機實驗精華版十(共10頁)

1、實驗十 Excel數(shù)據(jù)管理計算機科學導論實驗教程PAGE 76PAGE 81實驗(shyn)十、Excel數(shù)據(jù)管理一、實驗(shyn)目的：1、掌握數(shù)據(jù)表單的建立(jinl)方法。2、掌握數(shù)據(jù)表單的基本編輯方法。3、掌握數(shù)據(jù)表單常用的查詢和統(tǒng)計方法。二、實驗設備和軟件：1、硬件設備：Pentium 以上微型計算機。2、軟件設備：Windows XP、Excel 2002或以上版本。三、實驗預備知識：在Excel中，數(shù)據(jù)表單是包含相似數(shù)據(jù)組的帶標題的一組工作表數(shù)據(jù)行?？梢詫ⅰ皵?shù)據(jù)表單”看作“數(shù)據(jù)庫”，其中行作為數(shù)據(jù)庫中的記錄，列對應數(shù)據(jù)庫中的字段，列標題作為數(shù)據(jù)庫中的字段名稱。借助數(shù)據(jù)表單，Ex

2、cel能將應用于數(shù)據(jù)庫中的數(shù)據(jù)管理功能篩選、排序以及一些分析操作，應用到數(shù)據(jù)表單中的數(shù)據(jù)上。使用Excel的數(shù)據(jù)管理功能，首先必須將表格創(chuàng)建成數(shù)據(jù)表單。數(shù)據(jù)表單是一種特殊的表格，其特殊性在于此類表格至少由兩個必備部分組成表結構和純數(shù)據(jù)。表結構為數(shù)據(jù)表單中的第一行標題，Excel將利用這些標題名對數(shù)據(jù)進行查找、排序以及篩選等操作。純數(shù)據(jù)部分則是Excel實施管理功能的對象，該部分不允許有非法數(shù)據(jù)內(nèi)容出現(xiàn)。正確創(chuàng)建數(shù)據(jù)表單應遵守的準則如下：1、在數(shù)據(jù)表單的第一行中創(chuàng)建列標題，且在同一個數(shù)據(jù)表單中列標題內(nèi)容必須唯一。2、列標題與純數(shù)據(jù)之間不能用空行隔開。3、同一列數(shù)據(jù)的數(shù)據(jù)類型必須相同。4、純數(shù)據(jù)區(qū)

3、域中不允許出現(xiàn)非法數(shù)據(jù)，如空記錄等。5、如果在工作表中還有其它數(shù)據(jù)，要與數(shù)據(jù)表單之間留出至少一個空行和空列。數(shù)據(jù)表單的創(chuàng)建操作和普通表格的創(chuàng)建方法完全相同。首先，根據(jù)數(shù)據(jù)表單內(nèi)容創(chuàng)建表結構，然后移到表結構下的第一個空行，鍵入數(shù)據(jù)信息完成數(shù)據(jù)表單的創(chuàng)建工作。篩選功能實現(xiàn)在數(shù)據(jù)表單中提煉出滿足篩選條件的數(shù)據(jù)，不滿足條件的數(shù)據(jù)只是暫時被隱藏起來（并未真正被刪除掉）；一旦篩選條件被撤走，這些數(shù)據(jù)又重新出現(xiàn)。Excel提供了兩種篩選方式：1、自動篩選，通過隱藏不滿足篩選條件的數(shù)據(jù)記錄方式來顯示滿足篩選條件的數(shù)據(jù)記錄。但這種篩選方式存在一定的局限性，它所構造的篩選條件并不完備，如針對同一字段可構造“與”和

4、“或”條件，但在不同字段間，只能夠造“與”條件。2、高級篩選，這種篩選方式可適應所有的篩選情況，在構造篩選條件時要注意“與”和“或”條件的不同構造形式。字段名在同一行表示“與”關系，在不同行表示“或”關系。在Excel中，主要提供了兩種針對(zhndu)數(shù)據(jù)表單的數(shù)據(jù)進行統(tǒng)計的方法，即分類匯總和數(shù)據(jù)表單統(tǒng)計函數(shù)。1、分類匯總(huzng)，是在利用數(shù)據(jù)管理功能將數(shù)據(jù)表單中大量數(shù)據(jù)明確化和條理化的基礎上，利用Excel提供的函數(shù)進行數(shù)據(jù)匯總。匯總時首先將數(shù)據(jù)分類（排序），然后再將數(shù)據(jù)按類進行匯總分析處理。使用Excel的“分類(fn li)匯總”命令，并不需要創(chuàng)建公式，Excel將自動創(chuàng)建公式、

5、插入分類匯總總和行，并自動分級顯示數(shù)據(jù)，結果數(shù)據(jù)可以打印出來。在進行分類匯總前，要注意先對需要匯總的數(shù)據(jù)進行分類處理，可通過排序操作實現(xiàn)，沒有經(jīng)過排序的匯總結果是沒有意義的。2、數(shù)據(jù)表單統(tǒng)計函數(shù)，Excel除了提供分類匯總功能實現(xiàn)數(shù)據(jù)記錄統(tǒng)計外，還提供了具有等同功能的數(shù)據(jù)表單統(tǒng)計函數(shù)，在使用數(shù)據(jù)表單統(tǒng)計函數(shù)的時候要注意區(qū)分統(tǒng)計對象（第二個參數(shù)指定）和統(tǒng)計條件（第三個參數(shù)指定）的不同。四、實驗內(nèi)容和步驟：1、模仿性實驗實驗任務1 數(shù)據(jù)表單的瀏覽與編輯（1）、打開“職工工資表.XLS”文件，插入一個新的工作表，并將其改名為“數(shù)據(jù)管理”，將“工資表”工作表區(qū)域A2:K22中的內(nèi)容復制到“數(shù)據(jù)管理”工

6、作表中，復制位置：以A1為左上角，復制內(nèi)容：數(shù)值。（2）、在“數(shù)據(jù)管理”工作表中，對工資表中的記錄以“年齡”為主關鍵字，“職稱”為次關鍵字進行排序。（3）、在“數(shù)據(jù)管理”工作表中，以記錄單的方式瀏覽工資表的每一條記錄。（4）、在“數(shù)據(jù)管理”工作表中，以記錄單的方式查找工資表中姓名為“朱麗”的記錄，并將其職稱改為“副教授”。實驗步驟圖10-1 以值方式復制數(shù)據(jù)（1）、數(shù)據(jù)復制。插入新工作表，并重命名為“數(shù)據(jù)管理”，選擇“工資表”工作表區(qū)域A2：K22，單擊“編輯”“復制”，選擇“數(shù)據(jù)管理”工作表A1單元格，單擊“編輯” “選擇性粘貼”，選擇其中的“數(shù)值”單選鈕，單擊【確定】按鈕。如圖10-1所示

7、。圖10-2 數(shù)據(jù)表單排序（2）、數(shù)據(jù)(shj)排序。先單擊“數(shù)據(jù)管理”工作(gngzu)表中工資表范圍內(nèi)的任意單元格，再單擊“數(shù)據(jù)(shj)” “排序”，在排序?qū)υ捒蛑羞x擇“年齡”為主關鍵字，選擇“職稱”為次關鍵字，單擊【確定】按鈕。如圖10-2所示。（3）、數(shù)據(jù)瀏覽。先單擊“數(shù)據(jù)管理”工作表中工資表范圍內(nèi)的任意單元格，再單擊“數(shù)據(jù)” “記錄單”，通過【上一條】和【下一條】按鈕瀏覽記錄。如圖10-3所示。圖10-3 以記錄單方式瀏覽數(shù)據(jù)（4）、數(shù)據(jù)編輯。先單擊“數(shù)據(jù)管理”工作表中工資表范圍內(nèi)的任意單元格，再單擊“數(shù)據(jù)” “記錄單”，通過【上一條】和【下一條】按鈕瀏覽到姓名為“朱麗”的記錄，將

8、其職稱改為“副教授”。如圖10-4所示。圖10-4 以記錄單方式編輯數(shù)據(jù)實驗(shyn)結果圖10-5 數(shù)據(jù)表單基本操作后的結果結果(ji gu)如圖10-5所示。實驗任務2 數(shù)據(jù)(shj)表單查詢自動篩選和高級篩選的使用。（1）、在“數(shù)據(jù)管理”工作表中，使用自動篩選方法，檢索出“職稱”為“副教授”且應發(fā)工資=2000的職工；再進一步找出其中女性職工。（2）、取消自動篩選，使工資表恢復到初始狀態(tài)。（3）、在“數(shù)據(jù)管理”工作表中，檢索出所有年齡=35的講師和工程師（講師和工程師都有年齡要求），將他們輸出到以A27為左上角的區(qū)域，條件建立在從A23開始的區(qū)域。（4）、在“數(shù)據(jù)管理”工作表中，篩選出

9、所有姓張或姓李的職工。（5）、在“數(shù)據(jù)管理”工作表中，篩選出1965年以后出生的職工(含1965年出生者)。實驗(shyn)步驟圖10-6 自定義自動篩選條件（1）、自動(zdng)篩選。先單擊“數(shù)據(jù)管理”工作表中工資表范圍(fnwi)內(nèi)的任意單元格，再單擊“數(shù)據(jù)”“篩選”“自動篩選”，在“職稱”旁邊的下拉列表中選擇“副教授”，進一步在“應發(fā)工資”旁邊的下拉列表中選擇“自定義”，在如圖10-6所示的對話框中輸入篩選條件，最后在“性別”旁邊的下拉列表中選擇“女”。結果如圖10-7所示。圖10-7 自動篩選后的結果（2）、取消自動篩選。單擊“數(shù)據(jù)”“篩選”“全部顯示”，或在“自動篩選”狀態(tài)下，再次

10、單擊“數(shù)據(jù)”“篩選”“自動篩選”。如圖10-8所示。圖10-8 取消自動篩選（3）、高級篩選。從A23區(qū)域開始建立如圖所示的條件區(qū)域，單擊“數(shù)據(jù)”“篩選”“高級篩選”，在“高級篩選”對話框中，“方式”選擇為“將篩選結果復制到其他位置”，“列表區(qū)域”選擇為A1：K22，“條件區(qū)域”選擇為你所設定的區(qū)域，如A23：B25，“復制到”選擇為A27。單擊【確定】按鈕。如圖10-9所示。圖10-9 高級篩選結果1（4）、高級篩選。從A34區(qū)域開始(kish)建立如圖所示的條件(tiojin)區(qū)域，單擊“數(shù)據(jù)(shj)”“篩選”“高級篩選”，在“高級篩選”對話框中，“方式”選擇為“將篩選結果復制到其他位置

11、”，“列表區(qū)域”選擇為A1：K22，“條件區(qū)域”選擇為你所設定的區(qū)域，如A34：A36，“復制到”選擇為A38。單擊【確定】按鈕。如圖10-10所示。圖10-10 高級篩選結果2（5）、高級篩選。從A47區(qū)域開始建立如圖所示的條件區(qū)域，單擊“數(shù)據(jù)”“篩選”“高級篩選”，在“高級篩選”對話框中，“方式”選擇為“將篩選結果復制到其他位置”，“列表區(qū)域”選擇為A1：K22，“條件區(qū)域”選擇為你所設定的區(qū)域，如A47：A48，“復制到”選擇為A50。單擊【確定】按鈕。如圖10-11所示。(注：如果“出生日期”為數(shù)值顯示格式，可將其換為日期顯示格式) 圖10-11 高級篩選結果3實驗(shyn)結果結果

12、(ji gu)如圖10-7、圖10-9、圖10-10、圖10-11所示。實驗任務(rn wu)3 數(shù)據(jù)表單統(tǒng)計分類匯總的使用。用分類統(tǒng)計的方式分別按職稱求各職稱的人數(shù)、實發(fā)工資總額及平均實發(fā)工資，要求三個結果都放在一個匯總表中。實驗步驟圖10-12 分類匯總計數(shù)（1）、求各職稱人數(shù)。先對“數(shù)據(jù)管理”工作表中的“工資表”數(shù)據(jù)按“職稱”排序，然后單擊“數(shù)據(jù)”“分類匯總”，在“分類匯總”對話框中，“分類字段”選擇“職稱”，“匯總方式”選擇“計數(shù)”，“選定匯總項”選擇任意字段，單擊【確定】按鈕。如圖10-12所示。圖10-13 分類匯總求和（2）、求實發(fā)工資總額。單擊“數(shù)據(jù)”“分類匯總”，在“匯總方式

13、”中選擇“求和”，在“選定匯總項”中選擇“實發(fā)工資”，取消“替換當前分類匯總”前的復選框，單擊【確定】按鈕。如圖10-13所示。（3）、求平均(pngjn)實發(fā)工資。單擊“數(shù)據(jù)(shj)”“分類(fn li)匯總”，在“匯總方式”中選擇“平均值”，在“選定匯總項”中選擇“實發(fā)工資”，單擊【確定】按鈕。如圖10-14所示。圖10-14 分類匯總平均值（4）、折疊匯總項。在匯總表左上角單擊顯示級別按鈕“1 2 3 4 5”中的“4”，可以隱藏第5級（原始記錄細節(jié)），而得到僅含匯總項（小計和總計）的表格。如圖10-15所示。實驗結果結果如圖10-15、圖10-16所示。圖10-15 折疊匯總項圖10

14、-16 分類匯總結果實驗任務4 數(shù)據(jù)(shj)表單統(tǒng)計數(shù)據(jù)表單統(tǒng)計函數(shù)的使用。應用數(shù)據(jù)(shj)表單統(tǒng)計函數(shù)，在“工資表”工作(gngzu)表中依如圖10-17所示格式統(tǒng)計出各職稱人員的人數(shù)和相關工資數(shù)據(jù)，其中工資數(shù)據(jù)要求以兩位小數(shù)顯示。圖10-17 各職稱工資統(tǒng)計表實驗步驟（1）、設置條件格式。在“工資表”工作表中的相應區(qū)域輸入如圖10-17所示的數(shù)據(jù)。（2）、數(shù)據(jù)統(tǒng)計。單擊B28單元格，在其中輸入公式：=DCOUNT($A$2:$K$22,B$26:B$27)，拖動該單元格句柄到H28單元格；單擊B29單元格，在其中輸入公式：DSUM($A$2:$K$22,11,B$26:B$27)，拖動

15、該單元格句柄到H29單元格；單擊B30單元格，在其中輸入公式：=DAVERAGE($A$2:$K$22,11,B$26:B$27)，拖動該單元格句柄到H30單元格；單擊B31單元格，在其中輸入公式：=DMAX($A$2:$K$22,11,B$26:B$27)，拖動該單元格句柄到H31單元格；單擊B32單元格，在其中輸入公式：=DMIN($A$2:$K$22,11,B$26:B$27)，拖動該單元格句柄到H32單元格。實驗結果結果如圖10-18所示。圖10-18 各職稱工資統(tǒng)計結果2、設計性實驗實驗任務1 獎學金等級查詢在“獎學金”工作表中，檢索出所有獲獎學金以及“大學英語”課程績點在3.0以上

16、的未獲獎學金的學生記錄，將結果輸出到以F19為左上角的區(qū)域，條件建立在從H2開始的區(qū)域。實驗結果結果如圖10-19所示。圖10-19 獎學金等級查詢結果實驗任務2 足球隊出線(ch xin)的確認 根據(jù)某次足球賽會的出線(ch xin)規(guī)則，通過對某一小組比賽成績的判斷來決定出線球隊。判定時首先判斷積分，如果積分相等，再判斷凈勝球的多少來確定比賽的名次。（1）、插入(ch r)一個新的工作表“比賽出線”，在其中創(chuàng)建如圖10-20所示的小組比賽原始記錄表，并建立同“課程1”工作表中相同的格式。圖10-20 原始比賽紀錄（2）、在“積分”列定義積分計算公式?！皠佟?、“平”、“負”的積分分別為3、1、0。（3）、對各隊積分進行匯總。（4）、顯示僅含匯總項的表格（隱藏原始紀錄細節(jié)），并對匯總后的表格進行適當?shù)母袷骄庉?。?）、排定小組名次。實驗結果結果如圖10-21所示。圖10-21比賽總積分表五、思考題：1、Excel中的數(shù)據(jù)表單（數(shù)據(jù)庫）實際上就是一個工作表中