中職高職計(jì)算機(jī)課件中文版電子表格-實(shí)例與操作：第6章-管理工作表中數(shù)據(jù)

第6章管理工作表中

的數(shù)據(jù)

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

數(shù)據(jù)排序數(shù)據(jù)篩選分類匯總合并計(jì)算數(shù)據(jù)分析6.1建立數(shù)據(jù)清單

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

在執(zhí)行數(shù)據(jù)操作的過程中，Excel會(huì)自動(dòng)將數(shù)據(jù)清單視作數(shù)據(jù)庫，即數(shù)據(jù)清單中的列被視作數(shù)據(jù)庫的字段，數(shù)據(jù)清單的列標(biāo)記被視作數(shù)據(jù)庫的字段名稱，數(shù)據(jù)清單的每一行被認(rèn)為是數(shù)據(jù)庫的一條記錄。6.1.1建立數(shù)據(jù)清單的準(zhǔn)則數(shù)據(jù)清單的大小和位置（1）避免在一個(gè)工作表中建立多個(gè)數(shù)據(jù)清單，因?yàn)閿?shù)據(jù)清單的某些處理功能（如篩選等），一次只能在同一工作表的一個(gè)數(shù)據(jù)清單中使用。（2）在工作表的數(shù)據(jù)清單和其他數(shù)據(jù)間至少留出一個(gè)空白列和一個(gè)空白行，從而在執(zhí)行排序、篩選或插入自動(dòng)匯總等操作時(shí)，有利于Excel檢測和選定數(shù)據(jù)清單。（3）避免在數(shù)據(jù)清單中放置空白行和列，從而有利于Excel檢測和選定數(shù)據(jù)清單。（4）避免將關(guān)鍵數(shù)據(jù)放到數(shù)據(jù)清單的左右兩側(cè)。因?yàn)檫@些數(shù)據(jù)在篩選數(shù)據(jù)清單時(shí)可能會(huì)被隱藏。列標(biāo)志（1）需在數(shù)據(jù)清單的第一行里創(chuàng)建列標(biāo)志。Excel將使用這些標(biāo)志創(chuàng)建報(bào)告，并查找和組織數(shù)據(jù)。（2）列標(biāo)志使用的字體、對(duì)齊方式、格式、圖案、邊框或大小寫樣式，最好與數(shù)據(jù)清單中其他數(shù)據(jù)的格式相差別。行和列內(nèi)容（1）在設(shè)計(jì)數(shù)據(jù)清單時(shí)，應(yīng)使同一列中的各行數(shù)據(jù)具有同樣的類型和含義。（2）在單元格的開始處不要插入多余的空格，因?yàn)槎嘤嗟目崭裼绊懪判蚝筒檎?。?）不要使用空白行將列標(biāo)志和第一行數(shù)據(jù)分開。6.1.2直接輸入數(shù)據(jù)清單

建立數(shù)據(jù)清單分以下兩個(gè)步驟：（1）建立數(shù)據(jù)清單的標(biāo)題：在數(shù)據(jù)清單的標(biāo)題行中鍵入各字段名。（2）輸入各項(xiàng)記錄：可以直接在數(shù)據(jù)清單中鍵人數(shù)據(jù)（該方式下當(dāng)數(shù)據(jù)清單欄目較多時(shí)容易出錯(cuò)）；或使用“記錄單”輸入數(shù)據(jù)。例如要制作一個(gè)通訊錄，首先要規(guī)劃好數(shù)據(jù)清單的結(jié)構(gòu)，即字段，然后在工作表中輸入相應(yīng)的數(shù)據(jù)。6.1.3使用記錄單輸入與編輯數(shù)據(jù)1．使用記錄單輸入數(shù)據(jù)

2．使用記錄單編輯數(shù)據(jù)

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

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

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

多關(guān)鍵字排序，也就是對(duì)工作表中的數(shù)據(jù)按兩個(gè)或兩個(gè)以上的關(guān)鍵字進(jìn)行排序，。

下面將水果價(jià)格表中的“水果名稱”、“產(chǎn)地/規(guī)格”和“最高價(jià)”按升序進(jìn)行排序。將水果價(jià)格表中“水果名稱”、“產(chǎn)地/規(guī)格”和“最高價(jià)”按升序進(jìn)行排序后的效果6.2.3自定義排序

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

自定義序列

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

自動(dòng)篩選一般用于簡單的條件篩選，篩選時(shí)將不滿足條件的數(shù)據(jù)暫時(shí)隱藏起來，只顯示符合條件的數(shù)據(jù)。

將水果價(jià)格表中“產(chǎn)地/規(guī)格”為“國產(chǎn)”的記錄篩選出來了6.3.2自定義篩選

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

高級(jí)篩選用于根據(jù)多個(gè)條件來查詢數(shù)據(jù)。要使用高級(jí)篩選，應(yīng)首先在選定工作表中的指定區(qū)域創(chuàng)建篩選條件，然后打開“高級(jí)篩選”對(duì)話框，接下來分別選擇要篩選的單元格區(qū)域、篩選條件區(qū)域和保存篩選結(jié)果的目標(biāo)區(qū)域即可。1．多條件篩選

多條件篩選，即利用高級(jí)篩選功能查找出同時(shí)滿足多個(gè)條件的記錄。

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

多選一條件篩選，也就是只要滿足幾個(gè)條件當(dāng)中的一個(gè)，記錄就會(huì)顯示出來。下面查找出水果價(jià)格表中“產(chǎn)地/規(guī)格”為“國產(chǎn)”或“均價(jià)”大于10的記錄。

篩選出水果價(jià)格表中“產(chǎn)地/規(guī)格”為“國產(chǎn)”或“均價(jià)”大于10的記錄6.3.4取消篩選若要在工作表中取消對(duì)某一列進(jìn)行的篩選，只需單擊該列列標(biāo)右端的篩選箭頭，再在展開的列表中單擊“全部”項(xiàng)即可。若要在工作表中取消對(duì)所有列進(jìn)行的篩選，則選擇“數(shù)據(jù)”>“篩選”>“全部顯示”菜單。若要?jiǎng)h除工作表中的篩選箭頭，則選擇“數(shù)據(jù)”>“篩選”>“自動(dòng)篩選”菜單6.4分類匯總

分類匯總是把工作表中的數(shù)據(jù)按指定的關(guān)鍵字進(jìn)行各種匯總。它不需要建立公式，Excel會(huì)自動(dòng)對(duì)各類別的數(shù)據(jù)進(jìn)行求和、求平均值等多種計(jì)算，并且分級(jí)顯示匯總的結(jié)果。

對(duì)工作表的數(shù)據(jù)進(jìn)行分類匯總時(shí)，應(yīng)注意如下事項(xiàng)：因?yàn)榉诸悈R總是按字段名進(jìn)行的，所以要進(jìn)行分類匯總的數(shù)據(jù)表的第一行必須有列標(biāo)簽，并且數(shù)據(jù)區(qū)域中沒有空行或空列。在分類匯總之前必須先對(duì)數(shù)據(jù)進(jìn)行排序，將要進(jìn)行分類匯總的數(shù)據(jù)信息組合在一起，然后再進(jìn)行分類匯總操作。進(jìn)行分類匯總前，應(yīng)選擇數(shù)據(jù)區(qū)域中的任意一個(gè)單元格。在“分類字段”下拉列表框中選擇字段時(shí)，該字段必須是已排序的字段，若選擇其他列，則分類匯總結(jié)果是不正確的。6.4.1簡單分類匯總

簡單分類匯總主要用于對(duì)數(shù)據(jù)表中的某一列進(jìn)行排序，然后進(jìn)行分類匯總。下面對(duì)病人登記表中的“手術(shù)方式”進(jìn)行統(tǒng)計(jì)。對(duì)“手術(shù)方式”列進(jìn)行升序排序

簡單分類匯總結(jié)果6.4.2多重分類匯總

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

下面將病人登記表按“性別”對(duì)“手術(shù)中失血總量”和“手術(shù)中輸血量”進(jìn)行求和。對(duì)“性別”列進(jìn)行降序排序

第一重分類匯總結(jié)果

兩重分類匯總結(jié)果6.4.3嵌套分類匯總

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

下面查看病人登記表中“手術(shù)中輸血次數(shù)”總計(jì)，以及“性別”列男、女個(gè)數(shù)。

對(duì)“手術(shù)中輸血次數(shù)”和“性別”列進(jìn)行升序排序

第一次分類匯總結(jié)果分類匯總項(xiàng)目設(shè)置與第一次不同

嵌套分類匯總結(jié)果6.4.4分級(jí)顯示或隱藏匯總結(jié)果1．分級(jí)顯示明細(xì)數(shù)據(jù)

在分級(jí)顯示符號(hào)中，單擊所需級(jí)別的數(shù)字，較低級(jí)別的明細(xì)數(shù)據(jù)會(huì)隱藏起來。其中數(shù)值越大的顯示符號(hào)表示顯示的匯總結(jié)果就越詳細(xì)，單擊符號(hào)可隱藏分類匯總結(jié)果的所有明細(xì)數(shù)據(jù)。2．隱藏與顯示明細(xì)數(shù)據(jù)

要隱藏工作表中某組明細(xì)數(shù)據(jù)行，可以單擊該組行號(hào)左側(cè)的按鈕。此時(shí)按鈕變?yōu)榘粹o，單擊該按鈕可重新顯示本組中的明細(xì)數(shù)據(jù)。

要隱藏明細(xì)數(shù)據(jù)，也可以單擊某組中的單元格，然后選擇“數(shù)據(jù)”>“組及分級(jí)顯示”>“隱藏明細(xì)數(shù)據(jù)”菜單。隱藏明細(xì)數(shù)據(jù)后，選擇“組及分級(jí)顯示”子菜單中的“顯示明細(xì)數(shù)據(jù)”項(xiàng)，可顯示明細(xì)數(shù)據(jù)。3．清除分級(jí)顯示

進(jìn)行分類匯總后，如果只需要保留分類匯總的各項(xiàng)明細(xì)數(shù)據(jù)，而不需要分級(jí)顯示符號(hào)，此時(shí)可選擇“數(shù)據(jù)”>“組及分級(jí)顯示”>“清除分級(jí)顯示”菜單。分級(jí)顯示符號(hào)消失

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

所謂合并計(jì)算，是指用來匯總一個(gè)或多個(gè)源區(qū)域中數(shù)據(jù)的方法。

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

按位置合并計(jì)算，要求源區(qū)域中的數(shù)據(jù)使用相同的行標(biāo)簽和列標(biāo)簽，并按相同的順序排列在工作表中。

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

銷售各部費(fèi)用表

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

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

當(dāng)源區(qū)域中的數(shù)據(jù)沒有相同的組織結(jié)構(gòu)，但有相同的行標(biāo)簽或列標(biāo)簽時(shí)，需采用按分類合并計(jì)算方式進(jìn)行匯總。

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

兩個(gè)部門的費(fèi)用表

按分類合并計(jì)算結(jié)果6.5.2更改合并計(jì)算1．增加源區(qū)域

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

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

例如，要將“電腦配件費(fèi)用表”工作簿“甲部”中的“鍵盤”和“鼠標(biāo)”的有關(guān)數(shù)據(jù)不計(jì)算在內(nèi)，此時(shí)就需要更改“總費(fèi)用”工作表中引用的區(qū)域。刪除先前添加的引用區(qū)域更改前的合并計(jì)算結(jié)果更改后的合并計(jì)算結(jié)果6.6數(shù)據(jù)分析Excel為數(shù)據(jù)查詢、制作報(bào)表、數(shù)據(jù)運(yùn)算等數(shù)據(jù)處理提供了許多分析工具和實(shí)用函數(shù)，這些工具和函數(shù)在解決工程計(jì)算、金融分析和財(cái)政決算等方面發(fā)揮著巨大作用。

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

假設(shè)公式（或函數(shù)）中的一個(gè)或兩個(gè)變量有一組替換值，將這些值分別代入公式（或函數(shù)）中取得一組結(jié)果，這組變量替換值和結(jié)果值構(gòu)成的一個(gè)表稱為模擬運(yùn)算表。若只有一個(gè)變量，稱為單變量模擬運(yùn)算表；若有兩個(gè)變量，稱為雙變量模擬運(yùn)算表。模擬運(yùn)算表可以將變量取不同值時(shí)對(duì)應(yīng)的結(jié)果一次計(jì)算出來，并按規(guī)