辦公自動化與運用第04章

1、第 4 章辦公中的數(shù)據(jù)分析與處理辦公自動化實用技術(shù)電子教案1水利水電出版社【本章內(nèi)容容介紹】在辦公實實踐中，經(jīng)常需需要對大大量的數(shù)數(shù)據(jù)進(jìn)行行分析和和處理。如職工工工資表表中工資資的計算算、匯總總、分析析，公司司銷售表表中銷售售額的統(tǒng)統(tǒng)計、匯匯總、合合并計算算以及人人事考勤勤表中職職工請假假、曠工工、出勤勤等情況況的分析析統(tǒng)計等等。本章采用用實例形形式，以以Excel軟軟件操作作為例介介紹辦公公實踐中中常見的的數(shù)據(jù)分分析與處處理方法法，包括括數(shù)據(jù)的的排序、篩選、分類匯匯總、公公式與函函數(shù)運用用以及數(shù)數(shù)據(jù)關(guān)聯(lián)聯(lián)表格的的操作等等。辦公自自動化實實用技術(shù)術(shù)電子子教案2水利水電電出版社社【本章主要要知識

2、點點】Excel數(shù)據(jù)據(jù)庫表格格的基本本要求Excel表格格中內(nèi)容容填充的的常用技技巧Excel數(shù)據(jù)據(jù)庫表格格的特殊殊格式設(shè)設(shè)置Excel表格格中數(shù)據(jù)據(jù)有效性性的設(shè)置置Excel數(shù)據(jù)據(jù)庫表格格中的排排序Excel數(shù)據(jù)據(jù)庫表格格中的篩篩選Excel數(shù)據(jù)據(jù)庫表格格的分類類匯總Excel表格格中公式式和函數(shù)數(shù)的使用用Excel中的的關(guān)聯(lián)表表格操作作Excel公式式使用中中的常見見錯誤信信息辦公自自動化實實用技術(shù)術(shù)電子子教案3水利水電電出版社社4.1Excel數(shù)據(jù)分分析與處處理概述述在辦公業(yè)業(yè)務(wù)中，除了文文字和表表格之外外，還需需要經(jīng)常常進(jìn)行有有關(guān)數(shù)據(jù)據(jù)處理，有時候候還需要要將處理理的數(shù)據(jù)據(jù)結(jié)果用用一種

3、圖圖表來進(jìn)進(jìn)行表現(xiàn)現(xiàn)。1Excel中的數(shù)數(shù)據(jù)庫表表格簡介介所謂數(shù)據(jù)據(jù)庫就是是與特定定主題和和目標(biāo)相相聯(lián)系的的信息集集合。在Excel中中，可以以通過首首先創(chuàng)建建數(shù)據(jù)庫庫表格，然后再再利用其其提供的的功能菜菜單進(jìn)行行數(shù)據(jù)的的分析與與處理。如圖4-1示，Excel中中數(shù)據(jù)庫庫實際上上就是工工作表中中的一個個區(qū)域，是一個個二維表表。辦公自自動化實實用技術(shù)術(shù)電子子教案4水利水電電出版社社圖4-1Excel中的數(shù)據(jù)據(jù)庫表格格樣式說明：數(shù)數(shù)據(jù)庫表表格最好好單獨占占據(jù)一個個工作表表，并且且不能跟跟其他內(nèi)內(nèi)容（包包括數(shù)據(jù)據(jù)庫標(biāo)題題）直接接相連，至少需需要一個個空行或或空列隔隔開，如如圖4-1中，第2行作為一一

4、個空行行，將數(shù)數(shù)據(jù)庫表表格與其其標(biāo)題進(jìn)進(jìn)行了隔隔離。同同時，也也不要使使用空白白行將列列標(biāo)志和和第一條條記錄分分開。辦公自自動化實實用技術(shù)術(shù)電子子教案5水利水電電出版社社2Excel中的數(shù)據(jù)據(jù)分析與與處理Excel中提供了了很多種種數(shù)據(jù)分分析和數(shù)數(shù)據(jù)處理理功能，包括數(shù)數(shù)據(jù)排序序、數(shù)據(jù)據(jù)篩選、分類匯匯總、數(shù)數(shù)據(jù)透視視、合并并計算、關(guān)聯(lián)表表格處理理等。本章將對對上一章章的實例例進(jìn)一步步進(jìn)行介介紹，包包括對報報名匯總總表進(jìn)行行數(shù)據(jù)分分析，例例如：按按照某個個主題排排序、按按照指定定條件篩篩選、按按照指定定字段進(jìn)進(jìn)行分類類匯總、對整個個表格進(jìn)進(jìn)行數(shù)據(jù)據(jù)透視分分析等；同時，本章還還將利用用Excel中的

5、公式式和函數(shù)數(shù)進(jìn)行決決賽階段段成績的的計算，并將幾幾項不同同的比賽賽成績進(jìn)進(jìn)行關(guān)聯(lián)聯(lián)表格的的綜合處處理等。3Excel中的數(shù)據(jù)據(jù)圖表制制作利用Excel，可以輕輕松、快快捷地制制作各種種數(shù)據(jù)圖圖表。這這些數(shù)據(jù)據(jù)圖表可可將枯燥燥的數(shù)字字形象化化，并且且通過數(shù)數(shù)據(jù)圖表表可以了了解到數(shù)數(shù)據(jù)之間間的相互互關(guān)系和和變化趨趨勢。數(shù)數(shù)據(jù)圖表表的類型型很多，在辦公公實踐中中，使用用較多的的主要有有柱形圖圖、條形形圖、折折線圖、餅圖、散點圖圖等。（1）柱形圖圖用于顯示示某一段段時間內(nèi)內(nèi)數(shù)據(jù)的的變化，或比較較各數(shù)據(jù)據(jù)項之間間的差異異。分類類在水平平方向組組織，而而數(shù)值在在垂直方方向組織織，以強(qiáng)強(qiáng)調(diào)相對對于時間間的

6、變化化。其中中堆積柱柱形圖顯顯示了單單個數(shù)據(jù)據(jù)項與整整體的關(guān)關(guān)系，三三維透視視的柱形形圖可比比較兩個個坐標(biāo)軸軸上的數(shù)數(shù)據(jù)點。辦公自自動化實實用技術(shù)術(shù)電子子教案6水利水電電出版社社（2）條形圖圖用于顯示示各數(shù)據(jù)據(jù)之間的的比較。分類在在垂直方方向，而而數(shù)值在在水平方方向，以以使觀察察者的注注意力集集中在數(shù)數(shù)值的比比較上，而不在在時間上上。堆積積條形圖圖顯示單單個數(shù)據(jù)據(jù)與整體體的關(guān)系系。（3）折線圖圖用于顯示示各數(shù)據(jù)據(jù)之間的的變化趨趨勢。分分類在水水平方向向組織，而數(shù)值值在垂直直方向組組織，以以強(qiáng)調(diào)相相對于時時間的變變化。（4）餅圖用于顯示示組成數(shù)數(shù)據(jù)系列列的各數(shù)數(shù)據(jù)項與與數(shù)據(jù)項項總和的的比例。當(dāng)只

7、有有一個數(shù)數(shù)據(jù)系列列，并且且用于強(qiáng)強(qiáng)調(diào)整體體中的某某一重要要元素時時，餅狀狀圖十分分有效。如果要要使小扇扇區(qū)更容容易查看看，可將將這些小小扇區(qū)組組織為餅餅狀圖中中的一個個數(shù)據(jù)項項，然后后將該數(shù)數(shù)據(jù)項在在主圖表表旁邊的的小餅狀狀圖或小小條形圖圖中拆分分顯示。（5）XY散點圖既可用于于顯示若若干數(shù)據(jù)據(jù)系列中中的數(shù)字字值的關(guān)關(guān)系，也也可將兩兩組數(shù)字字分別當(dāng)當(dāng)作單個個系列的的x坐標(biāo)和y坐標(biāo)進(jìn)行行繪制，常用于于科技數(shù)數(shù)據(jù)處理理。制作數(shù)據(jù)據(jù)圖表時時，圖表表類型的的選取最最好與源源數(shù)據(jù)表表內(nèi)容以以及制作作目的相相結(jié)合，對于不不同的數(shù)數(shù)據(jù)表，一定要要選擇最最適合的的圖表類類型，這這樣才能能使表現(xiàn)現(xiàn)的數(shù)據(jù)據(jù)更生

8、動動、形象象。辦公自自動化實實用技術(shù)術(shù)電子子教案7水利水電電出版社社4.2“報名信息息匯總表表”數(shù)據(jù)據(jù)庫表格格的建立立上一章我我們介紹紹了Excel中空白報報名匯總總表的制制作。本本節(jié)主要要介紹在在Excel中如何制制作該表表格，效效果如圖圖4-2樣式所示示（版面面所限，只是部部分效果果）。圖4-2“報名信息息匯總表表”數(shù)據(jù)據(jù)庫表格格樣式辦公自自動化實實用技術(shù)術(shù)電子子教案8水利水電電出版社社4.2.1建立“報報名匯總總表”數(shù)數(shù)據(jù)庫框框架將上一章章制作好好的匯總總表框架架打開，按照數(shù)數(shù)據(jù)庫表表格的要要求進(jìn)行行必要的的內(nèi)容編編輯和格格式設(shè)置置，并調(diào)調(diào)整好顯顯示方式式，如圖圖4-3所示，等等待進(jìn)行行

9、各列數(shù)數(shù)據(jù)的輸輸入。圖4-3空白的“報名信信息匯總總表”表表格辦公自自動化實實用技術(shù)術(shù)電子子教案9水利水電電出版社社4.2.2“編號”列列的自動動序列填填充對于“編編號”列列，因為為輸入的的數(shù)據(jù)是是有一定定規(guī)律的的，輸入入時可以以考慮使使用自動動序列的的填充方方法。操操作時，只要在在圖4-3所示表格格的A3、A4分別輸入入1和2，然后選選取這兩兩個單元元格，再再拖動右右下角的的拖動柄柄到需要要的位置置即可。4.2.3定義單元元格數(shù)據(jù)據(jù)的有效效性在創(chuàng)建Excel數(shù)據(jù)庫的的過程中中，有些些單元格格中輸入入的數(shù)據(jù)據(jù)沒有限限制，而而有些單單元格中中輸入的的數(shù)據(jù)具具有有效效范圍。例如：圖4-3的報名匯匯

10、總表中中，“性性別”列列只能輸輸入“男男”、“女”之之一；“年齡”列按照照報名規(guī)規(guī)定只能能輸入1635之間的數(shù)數(shù)字；而而“唱法法類型”列只能能輸入“通俗”、“民民族”、“美聲聲”中的的一種。為了保證證數(shù)據(jù)庫庫中輸入入的數(shù)據(jù)據(jù)都在其其有效范范圍內(nèi)，并且設(shè)設(shè)置用戶戶選擇對對應(yīng)單元元格時能能夠提供供提示信信息，錯錯誤時可可以給出出錯誤提提示，可可以考慮慮使用Excel提供的“有效性性”來為為單元格格設(shè)置條條件。下下面僅以以設(shè)置“唱法類類型”一一列的數(shù)數(shù)據(jù)有效效性為例例，說明明設(shè)置數(shù)數(shù)據(jù)有效效性的操操作方法法。操作作步驟如如下：辦公自自動化實實用技術(shù)術(shù)電子子教案10水利水電電出版社社（1）選定需需要設(shè)

11、置置數(shù)據(jù)有有效性的的單元格格區(qū)域E3：E62（根據(jù)實實際報名名人數(shù)，后者中中行號可可以適當(dāng)當(dāng)擴(kuò)大，作為教教學(xué)用例例，此處處假設(shè)報報名人數(shù)數(shù)只有60人，下面面一樣處處理）。（2）選擇“數(shù)據(jù)”|“有效性”命令，打開圖圖4-4所示的“數(shù)據(jù)有有效性”對話框框。圖4-4“數(shù)據(jù)有效效性”對對話框辦公自自動化實實用技術(shù)術(shù)電子子教案11水利水電電出版社社（3）選擇“設(shè)置”選項卡卡，在“允許”下拉列列表中，選擇“序列”項。（4）在隨之之出現(xiàn)“來源”文本框框中輸入入“通俗俗，民族族，美聲聲”自定定義序列列。注意意：序列列中各項項中間的的逗號必必須為英英文狀態(tài)態(tài)符號。（5）根據(jù)需需要，可可以對其其余選項項卡進(jìn)行行適

12、當(dāng)設(shè)設(shè)置。說明：數(shù)數(shù)據(jù)有效效性設(shè)置置之前，必須首首先選取取所需單單元格或或區(qū)域；另外，數(shù)據(jù)有有效性應(yīng)應(yīng)該在輸輸入之前前設(shè)置，否則不不會自動動起作用用。數(shù)據(jù)有效效性設(shè)置置之后，單擊“唱法類類型”列列中單元元格，則則會出現(xiàn)現(xiàn)如圖4-5所示的列列表框，從而可可以實現(xiàn)現(xiàn)快速的的選取錄錄入。圖4-5“唱法類型型”字段段數(shù)據(jù)有有效性的的設(shè)置效效果辦公自自動化實實用技術(shù)術(shù)電子子教案12水利水電電出版社社辦公實際際使用的的數(shù)據(jù)庫庫中字段段數(shù)往往往很多，如果用用滾屏的的方法來來處理數(shù)數(shù)據(jù)很不不方便。Excel為此專門門提供了了“記錄錄單”功功能，選選擇“數(shù)數(shù)據(jù)”|“記錄單”命令，系統(tǒng)即即可彈出出如圖4-6所示對

13、話話框，它它顯示數(shù)數(shù)據(jù)記錄錄的所有有字段，提供了了增加、修改、刪除等等功能。當(dāng)數(shù)據(jù)據(jù)庫很大大時，記記錄單將將會顯示示出很大大的優(yōu)勢勢，利用用它可以以簡捷、精確地地輸入記記錄。圖4-6Excel中“記錄錄單”樣樣式辦公自自動化實實用技術(shù)術(shù)電子子教案13水利水電電出版社社4.3對“報名名信息匯匯總表”進(jìn)行數(shù)數(shù)據(jù)分析析本節(jié)介紹紹利用Excel中的有關(guān)關(guān)功能，對“報報名信息息匯總表表”進(jìn)行行數(shù)據(jù)分分析的操操作方法法，主要要包括數(shù)數(shù)據(jù)的排排序、篩篩選、分分類匯總總與數(shù)據(jù)據(jù)透視等等功能。4.3.1對“報名名信息匯匯總表”內(nèi)容進(jìn)進(jìn)行排序序在報名匯匯總表中中，各個個參賽選選手的資資料一般般是按照照其報名名的先

14、后后順序輸輸入的。為了提提高查找找效率，有時需需要對輸輸入的數(shù)數(shù)據(jù)重新新進(jìn)行整整理，對對此有效效的方法法就是排排序。Excel可以根據(jù)據(jù)數(shù)據(jù)庫庫中的內(nèi)內(nèi)容對數(shù)數(shù)據(jù)記錄錄進(jìn)行排排序。排排序時，Excel將利用指指定的順順序重新新排列行行、列或或各單元元格?？煽梢愿鶕?jù)據(jù)一列或或多列的的內(nèi)容按按升序或或降序?qū)?shù)據(jù)庫庫排序。1排序的的依據(jù)在按升序序排序時時，Excel使用如下下順序：（1）數(shù)字從從最小的的負(fù)數(shù)到到最大的的正數(shù)排排序；（2）文本以以及包含含數(shù)字的的文本，按下列列順序排排序： 辦公自自動化實實用技術(shù)術(shù)電子子教案14水利水電電出版社社0 1 2 3 4 5 6 7 8 9 - (空格) !

15、 # $ % & ( ) * , . / : ; ? _ | + A B C D E F G H I J K L M N O P Q R S T U V W X Y Z .（3）在邏輯輯值中，F(xiàn)ALSE排在TRUE之前；（4）所有錯錯誤值的的優(yōu)先級級等效；（5）空格總總是排在在最后；（6）漢字的的排序可可以按筆筆畫，也也可按字字典順序序排序，這可以以通過有有關(guān)關(guān)操作設(shè)設(shè)置。2按單個個字段排排序按單個字字段排序序就是只只根據(jù)某某一個字字段的內(nèi)內(nèi)容進(jìn)行行排序。操作方法法為：將將光標(biāo)置置于待排排序列中中；單擊擊工具欄欄上“升升序”或或“降序序”按鈕鈕即可。說明：按按數(shù)據(jù)庫庫某一列列數(shù)據(jù)排排序時，只需

16、單單擊列中中任一單單元格而而不用全全選該列列。辦公自自動化實實用技術(shù)術(shù)電子子教案15水利水電電出版社社3按多級級字段排排序按多級字字段排序序就是根根據(jù)兩列列或三列列的內(nèi)容容對數(shù)據(jù)據(jù)庫排序序，它主主要適用用于第一一或第二二排序字字段出現(xiàn)現(xiàn)相同內(nèi)內(nèi)容時，為了排排序合理理再加上上第二或或第三排排序字段段。操作步驟驟如下：（1）將光標(biāo)標(biāo)定位在在待排序序數(shù)據(jù)庫庫的任一一單元格格中，之之后選擇擇“數(shù)據(jù)據(jù)”|“排序”命命令，打打開如圖圖4-7所示對話話框。（2）在“主主要關(guān)鍵鍵字”、“次要要關(guān)鍵字字”和“第三關(guān)關(guān)鍵字”下拉列列表框中中，分別別選擇需需要排序序的字段段，并且且設(shè)置“遞增”或者“遞減”即可，本例

17、不不太需要要該設(shè)置置。（3）選定所所需的其其他排序序選項，然后單單擊“確確定”按按鈕。說明：按按數(shù)據(jù)庫庫多列數(shù)數(shù)據(jù)排序序時，只只需單擊擊數(shù)據(jù)庫庫中任一一單元格格而不用用全選整整個數(shù)據(jù)據(jù)庫表格格，否則則會引起起數(shù)據(jù)的的混亂。4特殊排排序方式式除上述基基本排序序功能外外，Excel還提供了了一些特特殊的排排序功能能。它們們的操作作方法為為：（1）將光標(biāo)標(biāo)放在所所要排序序的數(shù)據(jù)據(jù)區(qū)域中中的任一一位置；（2）選擇“數(shù)據(jù)”菜單中中的“排排序”項項；在打打開的對對話框中中選擇“選項”按鈕；辦公自自動化實實用技術(shù)術(shù)電子子教案16水利水電電出版社社（3）在打開開如圖4-8所示的“選項”對話框框中，分分別設(shè)置置

18、：自定定義排序序次序、排序方方向（按按行或按按列排序序）、排排序方法法（按字字母或按按筆畫排排序）（4）按“確確定”按按鈕完成成操作。圖4-7數(shù)據(jù)庫按按照多字字段排序序圖圖4-8其他排序序選項說明：在在“排序序選項”對話框框中，若若不選擇擇“方向向”和“方法”，系統(tǒng)統(tǒng)默認(rèn)的的排序方方向為“按列排排序”，默認(rèn)的的排序方方式為“字母排排序”。如選擇擇“筆畫畫排序”方法，就可實實現(xiàn)在開開會代表表名單、教材編編寫人員員名單中中經(jīng)?？纯吹降陌窗葱帐瞎P筆畫排序序效果。辦公自自動化實實用技術(shù)術(shù)電子子教案17水利水電電出版社社4.3.2對“報名名信息匯匯總表”內(nèi)容進(jìn)進(jìn)行篩選選報名信息息匯總表表制作好好之后，有

19、時還還需要根根據(jù)指定定條件從從眾多數(shù)數(shù)據(jù)中篩篩選特定定的記錄錄，比如如：篩選選職業(yè)是是“教師師”的報報名人員員，篩選選年齡在在30以上的人人員等等等。Excel中提供了了兩種篩篩選方法法：“自自動篩選選”和“高級篩篩選”，它們可可以將那那些符合合條件的的記錄顯顯示在工工作表中中，而將將其他不不滿足條條件的記記錄從視視圖中隱隱藏起來來；或者者將篩選選出來的的記錄送送到指定定位置存存放，而而原數(shù)據(jù)據(jù)不動。1自動篩篩選利用自動動篩選，可以很很方便地地從報名名匯總表表中篩選選出職業(yè)業(yè)是“教教師”的的報名人人員信息息。下面面以此為為例，說說明自動動篩選的的操作。操作步步驟如下下：（1）將鼠標(biāo)標(biāo)定位到到需

20、要篩篩選的數(shù)數(shù)據(jù)庫中中任一單單元格。（2）選擇“數(shù)據(jù)”|“篩選”|“自動篩選選”，使使“自動動篩選”項為選選中狀態(tài)態(tài)（打上上對號），這時時在每個個字段名名旁出現(xiàn)現(xiàn)篩選器器箭頭，如圖4-9所示。（3）本例要要篩選出出來職業(yè)業(yè)是“教教師”的的報名人人員的情情況，需需要單擊擊“職業(yè)業(yè)”字段段名旁的的篩選器器箭頭，從彈出出的菜單單中選擇擇“教師師”，然然后單擊擊鼠標(biāo)左左鍵即可可。辦公自自動化實實用技術(shù)術(shù)電子子教案18水利水電電出版社社圖4-9使用自動動篩選器器篩選記記錄說明：在在該菜單單中，選選擇“升升序排列列”和“降序排排列”，則篩選選結(jié)果將將按照指指定順序序排列；選擇具具體學(xué)歷歷名稱，將只顯顯示對

21、應(yīng)應(yīng)學(xué)歷的的記錄；單擊“全部”顯示所所有記錄錄；單擊擊“前10個”可以以顯示最最高或最最低的一一些（默默認(rèn)10個，可以以自行設(shè)設(shè)置）記記錄。（4）在圖4-9中，如果果下拉列列表太長長，不容容易找到到“教師師”項時時，還可可以選擇擇“自定定義”項，從出出現(xiàn)圖4-10所示的對對話框中中，按照照圖中樣樣式設(shè)置置本例的的篩選條條件。辦公自自動化實實用技術(shù)術(shù)電子子教案19水利水電電出版社社圖4-10“自定義自自動篩選選方式”對話框框說明：圖圖4-10中，運算算符除了了圖中所所示外,還包括各各種其他他的數(shù)學(xué)學(xué)關(guān)系運運算,以及“始始于”、“止于于”、“包含”、“并并非起始始于”、“并非非結(jié)束于于”、“不包含

22、含”等字字符關(guān)系系運算。圖中“或”表表示兩個個條件中中一個成成立即可可，而“與”要要求兩個個同時成成立。（5）按“確確定”按按鈕，完完成操作作，篩選選結(jié)果如如圖4-11所示。圖4-11篩選出來來的職業(yè)業(yè)為教師師的選手手情況辦公自自動化實實用技術(shù)術(shù)電子子教案20水利水電電出版社社說明：取取消數(shù)據(jù)據(jù)篩選的的操作方方法：如要取消消對某一一列的篩篩選，單單擊該列列篩選器器箭頭，然后再再單擊“全部”。如要取消消對所有有列的篩篩選，可可選擇“數(shù)據(jù)”|“篩選”|“全部顯示示”命令令。如要撤消消數(shù)據(jù)清清單中的的篩選箭箭頭，可可選擇“數(shù)據(jù)”|“篩選”|“自動篩選選”命令令。2高級篩篩選從上面的的講解可可以看到到

23、，自動動篩選可可以實現(xiàn)現(xiàn)同一字字段之間間的“與與”運算算和“或或”運算算，通過過多次進(jìn)進(jìn)行自動動篩選也也可以進(jìn)進(jìn)行不同同字段之之間的“與”運運算，但但是它無無法實現(xiàn)現(xiàn)多個字字段之間間的“或或”運算算。這時時就需要要使用高高級篩選選。需要說明明的是：對于數(shù)數(shù)據(jù)清單單，如果果要進(jìn)行行高級篩篩選，必必須首先先設(shè)置篩篩選條件件區(qū)域。為此，必須在在該數(shù)據(jù)據(jù)清單上上方留出出若干空空行，以以便作為為條件區(qū)區(qū)域。另外，為為了使讀讀者可以以更好地地理解高高級篩選選，首先先列出一一個表格格，如表表4-1所示，該該表用來來對高級級篩選條條件區(qū)域域的邏輯輯關(guān)系進(jìn)進(jìn)行定義義。辦公自自動化實實用技術(shù)術(shù)電子子教案21水利水

24、電電出版社社（a）篩選字段A中符合A1條件或A2條件的所有記錄（b）篩選字段A中符合A1條件并且字段B中符合B1條件的所有記錄（c）篩選字段A中符合A1條件或字段B中符合B2條件的所有記錄（d）篩選字段A中符合A1條件且字段B中符合B1條件以及字段A中符合A2條件且字段B中符合B2條件的所有記錄ABA1A2ABA1B1ABA1B2ABA1B1A2B2表4-1高高級篩選選條件區(qū)區(qū)域設(shè)置置的幾種種邏輯關(guān)關(guān)系下面以篩篩選出來來職業(yè)為為“教師師”、唱唱法類型型為“美美聲”或或者“民民族”的的報名選選手情況況操作為為例，說說明高級級篩選的的使用方方法。操操作步驟驟如下：（1）在“報報名信息息匯總表表”表

25、格格的上方方插入幾幾個空行行，根據(jù)據(jù)本例篩篩選的實實際需要要以及表表4-1介紹的邏邏輯關(guān)系系，建立立高級篩篩選的條條件區(qū)域域，如圖圖4-12所示A1:B3區(qū)域。辦公自自動化實實用技術(shù)術(shù)電子子教案22水利水電電出版社社圖4-12設(shè)置高級級篩選的的“條件件區(qū)域”（2）單擊“報名信信息匯總總表”數(shù)數(shù)據(jù)庫表表格中的的任一單單元格。（3）選擇“數(shù)據(jù)”|“篩選”|“高級篩選選”命令令，出現(xiàn)現(xiàn)“高級級篩選”對話框框。（4）如圖4-13所示，在在 “方方式”選選項區(qū)中中，單擊擊選中“將篩選選結(jié)果復(fù)復(fù)制到其其他位置置”按鈕鈕；在“列表區(qū)區(qū)域”框框中，軟軟件自動動輸入了了要篩選選的數(shù)據(jù)據(jù)區(qū)域“$A$6:$H$66

26、”，也就是是整個數(shù)數(shù)據(jù)清單單區(qū)域；在“條件區(qū)區(qū)域”框框中，輸輸入設(shè)置置好的包包含篩選選條件的的區(qū)域“$A$1:$B$3”（可直接接在該文文本框中中輸入?yún)^(qū)區(qū)域引用用，也可可用鼠標(biāo)標(biāo)在工作作表中選選定條件件區(qū)域）；在“復(fù)制到到”文本本框中輸輸入時，首先將將光標(biāo)在在其中定定位，然然后用鼠鼠標(biāo)在數(shù)數(shù)據(jù)清單單下方指指定一個個單元格格（本例例為“$A$71”），該單單元格將將作為放放置篩選選結(jié)果區(qū)區(qū)域左上上角的位位置，篩篩選結(jié)果果將在它它的下方方和右方方進(jìn)行排排列。辦公自自動化實實用技術(shù)術(shù)電子子教案23水利水電電出版社社（5）單擊“確定”按鈕，高級篩篩選結(jié)果果如圖4-14所示（原原來數(shù)據(jù)據(jù)我們選選擇的“隱

27、藏”操作，以便大大家能夠夠看到篩篩選出來來的效果果）。圖4-13“高級篩選選”對話話框以及及輸入內(nèi)內(nèi)容設(shè)置置圖4-14篩選出職職業(yè)為“教師”、唱法法類型為為“美聲聲”或“民族”的選手手情況辦公自自動化實實用技術(shù)術(shù)電子子教案24水利水電電出版社社3高級篩篩選時的的注意事事項使用高級級篩選時時，需要要注意以以下幾個個問題：（1）高級篩篩選必須須指定一一個條件件區(qū)域，它可以以與數(shù)據(jù)據(jù)庫表格格在一張張工作表表上，但但是必必須與數(shù)數(shù)據(jù)庫之之間有空空白行隔隔開；條條件區(qū)域域也可以以與數(shù)據(jù)據(jù)庫表格格不在一一張工作作表上。（2）條件區(qū)區(qū)域中的的字段名名必須與與數(shù)據(jù)庫庫中的完完全一樣樣，最好好通過復(fù)復(fù)制得到到。

28、（3）如果“條件區(qū)區(qū)域”與與數(shù)據(jù)庫庫表格在在一張工工作表上上，在篩篩選之前前，最好好把光標(biāo)標(biāo)放置到到數(shù)據(jù)庫庫中某一一單元格格上，這這樣數(shù)據(jù)據(jù)區(qū)域就就會自動動填上數(shù)數(shù)據(jù)庫所所在位置置，省去去再次鼠鼠標(biāo)選擇擇或者重重新輸入入的麻煩煩。（4）執(zhí)行“將篩選選結(jié)果復(fù)復(fù)制到其其他位置置”時，在“復(fù)復(fù)制到”文本框框中輸入入或選取取將來要要放置位位置的左左上角單單元格即即可，不不要指定定某區(qū)域域（因為為事先無無法確定定篩選結(jié)結(jié)果）。（5）根據(jù)需需要，條條件區(qū)域域可以定定義多個個條件，以便用用來篩選選符合多多個條件件的記錄錄。這這些條件件可以輸輸入到條條件區(qū)域域的同一一行上，也可以以輸入到到不同行行上。但但是必

29、須須記住：兩個字字段名下下面的同同一行中中的各個個條件之之間為“與”的的關(guān)系，也就是是條件必必須同時時成立才才算符合合條件；兩個字字段名下下面的不不同行中中的各個個條件之之間為“或”的的關(guān)系，也就是是條件只只要有一一個成立立就算符符合條件件。辦公自自動化實實用技術(shù)術(shù)電子子教案25水利水電電出版社社4.3.3對“報名名信息匯匯總表”中數(shù)據(jù)據(jù)分類匯匯總使用分類類匯總，能夠在在數(shù)據(jù)庫庫適當(dāng)位位置加上上統(tǒng)計結(jié)結(jié)果，使使數(shù)據(jù)庫庫變得清清晰易懂懂。對于本例例，可以以對“唱法類類型”進(jìn)進(jìn)行分類類匯總，以便了了解唱法法類型的的報名人人數(shù)。在執(zhí)行分分類匯總總命令之之前，首首先應(yīng)該該對數(shù)據(jù)據(jù)庫按照照待匯總總字段進(jìn)

30、進(jìn)行排序序。下面以按按照“唱唱法類型型”分類類匯總為為例來說說明分類類匯總的的操作。操作步步驟如下下：（1）對需要要分類匯匯總的字字段進(jìn)行行排序，從而使使相同的的記錄集集中。本本例將光光標(biāo)定位位到“唱唱法類型型”列中中某一單單元格，然后運運行工具具欄上“排序”按鈕即即可。（2）選定數(shù)數(shù)據(jù)庫中中任意一一個單元元格。（3）選擇“數(shù)據(jù)”|“分類匯總總”命令令，出現(xiàn)現(xiàn)圖4-15所示的“分類匯匯總”對對話框。圖4-15“分類匯總總”對話話框辦公自自動化實實用技術(shù)術(shù)電子子教案26水利水電電出版社社（4）單擊“分類字字段”下下拉列表表按鈕，選擇“唱法類類型”作作為分類類匯總的的字段。（5）在“匯匯總方式式”

31、下拉拉列表中中，選擇擇需要的的統(tǒng)計函函數(shù)。分分類匯總總可以支支持求和和、平均均數(shù)、最最大、最最小、計計數(shù)、乘乘積等共共計11種函數(shù)。本例需需要選擇擇“計數(shù)數(shù)”。（6）在“選選定匯總總項”列列表中，選中需需要對其其匯總計計算的字字段前面面的復(fù)選選框。本本例中選選上“姓姓名”復(fù)復(fù)選框即即可。（7）可以根根據(jù)需要要選中相相應(yīng)的復(fù)復(fù)選框，指定匯匯總結(jié)果果的顯示示位置。（8）單擊“確定”按鈕，可以得得到分類類匯總結(jié)結(jié)果。如如圖4-16所示。圖4-16分類匯總總的結(jié)果果顯示辦公自自動化實實用技術(shù)術(shù)電子子教案27水利水電電出版社社從圖4-16可以看出出，在顯顯示分類類匯總結(jié)結(jié)果的同同時，分分類匯總總的左側(cè)側(cè)

32、自動顯顯示一些些分級顯顯示按鈕鈕。其中中：單擊擊左側(cè)的的“+”形狀按鈕鈕圖標(biāo)和和“-”形狀按鈕鈕圖標(biāo)分分別可以以展開和和隱藏細(xì)細(xì)節(jié)數(shù)據(jù)據(jù)；“1”、“2”、“3”形狀按鈕鈕表示顯顯示數(shù)據(jù)據(jù)的層次次，“1”只顯示總總計數(shù)據(jù)據(jù)，“2”顯示部分分?jǐn)?shù)據(jù)以以及匯總總結(jié)果，“3”顯示所有有數(shù)據(jù)；“|”形狀為級級別條，用來指指示屬于于某一級級別的細(xì)細(xì)節(jié)行或或列的范范圍。圖4-17所示就是是將分類類匯總后后細(xì)節(jié)數(shù)數(shù)據(jù)隱藏藏后的顯顯示效果果。圖4-17分類匯總總后部分分細(xì)節(jié)數(shù)數(shù)據(jù)隱藏藏的顯示示效果說明：分分類匯總總效果可可以清除除，操作作時，先先打開如如圖4-15所示的對對話框，然后單單擊“全全部刪除除”即可可；

33、但是是為了保保險，在在匯總之之前最好好進(jìn)行數(shù)數(shù)據(jù)庫備備份。辦公自自動化實實用技術(shù)術(shù)電子子教案28水利水電電出版社社4.4利用Excel的關(guān)聯(lián)表表格制作作歌唱比比賽計分分表在辦公實實踐中的的表格制制作時，有些數(shù)數(shù)據(jù)的存存放和計計算需要要使用多多個工作作表，并并且相互互之間需需要相互互引用和和參照。這就需需要使用用Excel的關(guān)聯(lián)表表格操作作來實現(xiàn)現(xiàn)數(shù)據(jù)的的處理。某市舉辦辦的“青青年歌手手大獎賽賽”，經(jīng)經(jīng)過初賽賽、復(fù)賽賽，目前前已經(jīng)進(jìn)進(jìn)入決賽賽。在決賽階階段，為為了體現(xiàn)現(xiàn)公平、公正、科學(xué)、合理的的比賽原原則，組組委會擬擬定了如如下的比比賽計分分規(guī)則。每一個參參加決賽賽選手的的得分滿滿分為100分，

34、包括括以下三三大部分分：1、歌唱得得分：每每一位參參賽選手手自行選選擇一首首歌曲演演唱，滿滿分90分，12個裁判分分別打分分，總分分減去最最高分和和最低分分之后的的平均分分為該項項分?jǐn)?shù)。2、素質(zhì)得得分：兩兩個題目目，每題題0.5分，共1分，各由由一個評評委評分分。3、聲樂得得分：選選手自己己從指定定歌曲中中選擇一一首歌曲曲進(jìn)行聲聲樂表演演，歌曲曲有不同同的難度度系數(shù)，分A、B、C三個級別別，滿分分9分，12個裁判打打分，方方法同上上，但是是在得到到的平均均分?jǐn)?shù)的的基礎(chǔ)上上再乘上上難度系系數(shù)（A為1，B為0.8，C為0.6），才能能得到該該項分?jǐn)?shù)數(shù)。以上三項項分?jǐn)?shù)之之和為該該選手的的總得分分，按

35、照照該成績績的名次次確定最最終的獲獲獎等級級。辦公自自動化實實用技術(shù)術(shù)電子子教案29水利水電電出版社社參加決賽賽的選手手共20人，比賽賽前通過過抽簽確確定出場場順序，比賽結(jié)結(jié)束需要要確定綜綜合成績績一等獎獎1人，二等等獎3人，三等等獎5人，其余余為優(yōu)秀秀獎；同同時綜合合成績最最好的3名將推薦薦到省里里參加全全省比賽賽；另外外，為了了比較單單項成績績，還需需要將單單項成績績的前6名評出。本節(jié)目標(biāo)標(biāo)就是制制作一套套用來計計算選手手分?jǐn)?shù)的的表格，以便在在現(xiàn)場快快速計算算選手成成績。問題分析析和操作作提示從上面的的規(guī)則可可以看到到，需要要進(jìn)行幾幾個單項項成績的的計算和和排名，同時需需要進(jìn)行行幾項成成績

36、之和和作為綜綜合分的的計算和和排名，所以可可以考慮慮利用Excel的關(guān)聯(lián)表表格操作作。所謂關(guān)聯(lián)聯(lián)表格，是指在在一個或或者多個個工作簿簿中有一一定關(guān)聯(lián)聯(lián)關(guān)系的的工作表表的總稱稱。本實例制制作時，還需要要注意以以下幾個個問題：1各個表表格中要要盡量減減少現(xiàn)場場數(shù)據(jù)錄錄入和計計算的工工作量。2可以在在比賽中中間隨時時查看比比賽過的的選手的的各個單單項成績績，確定定對應(yīng)的的暫時名名次。3單項成成績和綜綜合成績績的計算算，需使使用公式式和函數(shù)數(shù)實現(xiàn)，并且公公式要具具有容錯錯性。辦公自自動化實實用技術(shù)術(shù)電子子教案30水利水電電出版社社4綜合分分?jǐn)?shù)排名名為最后后名次，根據(jù)該該名次利利用公式式確定獲獲獎等級級

37、。5多個工工作表之之間進(jìn)行行關(guān)聯(lián)操操作時，注意數(shù)數(shù)據(jù)要能能夠隨時時實時變變化。6為了使使各個表表格之間間切換方方便，可可以制作作一個主主界面工工作表，在上面面利用有有關(guān)圖形形建立超超級鏈接接，以便便可以快快速切換換到對應(yīng)應(yīng)的工作作表。按照以上上的分析析，本問問題的解解決可以以通過制制作8張關(guān)聯(lián)的的工作表表來實現(xiàn)現(xiàn)，分別別用來作作為“主主界面”工作表表、“計分規(guī)規(guī)則”工工作表、“選手手情況” 工作作表、“歌唱得得分”工工作表表、“素素質(zhì)得分分”工工作表、“聲樂樂得分” 工作作表、“綜合得得分”工工作表表以及“評獎結(jié)結(jié)果”工工作表表。下面面就介紹紹這些工工作表的的格式制制作和公公式設(shè)計計。4.4.

38、1工作表的的添加和和更名從上面的的的分析析可知，該計分分系統(tǒng)需需要設(shè)置置8個工作表表，而Excel默認(rèn)的工工作表個個數(shù)為3個。所以以，本實實例操作作時，首首先需要要工作表表個數(shù)的的添加以以及名稱稱的更改改。操作步驟驟如下：1啟動Excel，屏幕上上有默認(rèn)認(rèn)的Sheet1、Sheet2、Sheet3三個工作作表。辦公自自動化實實用技術(shù)術(shù)電子子教案31水利水電電出版社社2在工作作表標(biāo)簽簽上，單單擊選擇擇Sheet1，然后單單擊鼠標(biāo)標(biāo)右鍵，從彈出出的快捷捷菜單中中選擇“插入”，根據(jù)據(jù)隨后出出現(xiàn)的系系統(tǒng)提示示操作，添加了了一個新新工作表表Sheet4。說明：上上述方法法一次只只能添加加一個工工作表，如

39、果想想添加多多個工作作表，請請先選取取多個工工作表，然后再再單擊右右鍵，選選擇“插插入”，則會快快速添加加與選取取個數(shù)一一樣多的的工作表表。3根據(jù)上上面的說說明，借借助于Shift鍵選擇全全部四個個工作表表，單擊擊鼠標(biāo)右右鍵，選選擇“插插入”，則快速速添加了了四個工工作表Sheet 5、Sheet6、Sheet7、Sheet8。4分別雙雙擊工作作表名稱稱，將8個工作表表的名稱稱依次更更該為“主界面面”、“計分規(guī)規(guī)則”、“選手手情況”、“歌歌唱得分分”、“素質(zhì)得得分”、“聲樂樂得分”、“綜綜合得分分”和“評獎結(jié)結(jié)果”。5將工作作簿文件件以“歌歌手大獎獎賽記分分”為名名存盤。4.4.2“主界面”工

40、作表表的制作作 “主界面面”工作作表效果果如圖4-18所示，利利用“主主界面”工作表表，可以以快速看看到整個個記分方方案的完完整組成成，同時時根據(jù)需需要將來來可以快快速切換換到需要要的其他他工作表表。 辦公自自動化實實用技術(shù)術(shù)電子子教案32水利水電電出版社社圖4-18“主界面”工作表表效果該工作表表制作中中要用到到工作表表網(wǎng)格線線的取消消、單元元格填充充顏色設(shè)設(shè)置、藝藝術(shù)字標(biāo)標(biāo)題設(shè)置置、橢圓圓形按鈕鈕繪制以以及超級級鏈接建建立等知知識。操作步驟驟如下：1單擊工工作表標(biāo)標(biāo)簽上的的“主界界面”，選取該該工作表表。辦公自自動化實實用技術(shù)術(shù)電子子教案33水利水電電出版社社2單擊擊“工具具”|“選項”命

41、令，彈出圖圖4-19所示示的“選選項”對對話框，從中選選取“視視圖”標(biāo)標(biāo)簽，將將“網(wǎng)格格線”復(fù)復(fù)選框設(shè)設(shè)置為不不選中狀狀態(tài)，這這樣就取取消了工工作表中中的網(wǎng)格格線。3全選選整個工工作表，然后利利用格式式工具欄欄上的按按鈕設(shè)置置工作表表填充顏顏色為淺淺黃色。圖4-19在Excel的“選項項”對對話框中中取消網(wǎng)網(wǎng)格線辦公自自動化實實用技術(shù)術(shù)電子子教案34水利水電電出版社社4利用與與Word中輸入藝藝術(shù)字類類似的方方法，輸輸入藝術(shù)術(shù)字標(biāo)題題“青年年歌手大大獎賽計計分方案案”，并并進(jìn)行適適當(dāng)格式式設(shè)置。5利用“繪圖”工具欄欄上的橢橢圓工具具繪制橢橢圓，并并進(jìn)行填填充效果果、線條條顏色以以及圖形形大小的

42、的設(shè)置。輸入過過藝術(shù)字字標(biāo)題和和繪制橢橢圓圖形形后,“主界面”工作表表如圖4-20所示。圖4-20輸入過藝藝術(shù)字標(biāo)標(biāo)題和繪繪制橢圓圓圖形后后的“主主界面”工作表表辦公自自動化實實用技術(shù)術(shù)電子子教案35水利水電電出版社社6將上面面制作的的橢圓圖圖形再復(fù)復(fù)制6個，并將將他們放放置到適適當(dāng)位置置，作后后一個需需要放大大。操作作時，可可以借助助“繪圖圖”工具具欄上的的“繪圖圖”|“對齊和分分布”命命令來輔輔助完成成。7依次右右擊各個個橢圓，從彈出出的菜單單中選擇擇“添加加文字”，分別別輸入相相應(yīng)的文文字，效效果如圖圖4-21所示。圖4-21在橢圓中中輸入文文字后的的界面效效果辦公自自動化實實用技術(shù)術(shù)電

43、子子教案36水利水電電出版社社8繪制橢橢圓的目目的，就就是為了了將來單單擊橢圓圓，就能能快速打打開對應(yīng)應(yīng)的工作作表。為為此，需需要為他他們設(shè)置置超級鏈鏈接。操操作步驟驟如下：（1）選中第第一個橢橢圓“計計分規(guī)則則”，執(zhí)執(zhí)行“插插入”|“超級鏈接接”命令令，彈出出如圖4-22所示的“編輯超超級鏈接接”對話話框。圖4-22“編輯超級級鏈接”對話框框說明：選選中橢圓圓后，按按組合鍵鍵Ctrl+K也可以快快速打開開“編輯輯超級鏈鏈接”對對話框。辦公自自動化實實用技術(shù)術(shù)電子子教案37水利水電電出版社社（2）在“編編輯超級級鏈接”對話框框中，在在“鏈接接到：”區(qū)域中中選擇“本文檔檔中的位位置”，從右側(cè)側(cè)的

44、“在在這篇文文檔中選選擇位置置”中選選擇鏈接接的對象象，即“計分規(guī)規(guī)則”工工作表。說明：在在圖4-22對話框中中，在“鏈接到到：”區(qū)區(qū)域中，選擇“原有文文件或Web頁”，可可以建立立到本地地計算機(jī)機(jī)上或者者因特網(wǎng)網(wǎng)上的文文件上的的超級鏈鏈接；選選擇“新新建文檔檔”或“電子子郵件地地址”，可以分分別建立立到一個個新建文文件上或或者電子子郵件上上的超級級鏈接。（3）在“編編輯超級級鏈接”對話框框中，單單擊“屏屏幕提示示”按鈕鈕，將彈彈出“設(shè)設(shè)置超級級鏈接屏屏幕提示示”對話話框，如如圖4-23所示，在在“屏幕幕提示文文字”框框中輸入入相應(yīng)文文字，該該文字在在將來光光標(biāo)停留留在橢圓圓上時，會以操操作時

45、的的提示文文字形式式自動出出現(xiàn)。圖4-23 “設(shè)置超級級鏈接屏屏幕提示示”對話話框辦公自自動化實實用技術(shù)術(shù)電子子教案38水利水電電出版社社（4）按照上上述方法法，依次次為各個個橢圓對對象設(shè)置置相應(yīng)的的超級鏈鏈接。9對該工工作表中中各個對對象的位位置、大大小再進(jìn)進(jìn)行適當(dāng)當(dāng)調(diào)整，“主界界面”工工作表完完成。4.4.3“計分規(guī)則則”工作作表的制制作“計分規(guī)規(guī)則”工工作表如如圖4-24所示，在在其中輸輸入了比比賽計分分規(guī)則的的說明文文字，以以便將來來觀眾或或選手咨咨詢時能能夠快速速查詢。制作本本工作表表時，主主要難點點是如何何解決大大塊文字字在Excel中的輸入入問題。圖4-24“計分規(guī)則則”工工作表

46、辦公自自動化實實用技術(shù)術(shù)電子子教案39水利水電電出版社社一般情況況下Excel單元格中中很少輸輸入大量量文字，特別是是多行文文字。而而對于本本工作表表，確實實現(xiàn)在需需要這樣樣處理。解決的的方法有有以下幾幾種：1拉大單單元格方方法。比比如將A1單元格的的寬度、高度均均設(shè)置為為足夠大大，然后后在其中中輸入文文字，分分段換行行時按AltEnter（不能像像Word一樣直接接按Enter）2設(shè)置單單元格文文字自動動換行。利用上上述方法法將單元元格拉大大后，選選中單元元格，單單擊右鍵鍵，選擇擇“設(shè)置置單元格格格式”，如圖圖4-25所示，從從彈出的的對話框框中，將將“對齊齊”標(biāo)簽簽下的“自動換換行”復(fù)復(fù)選

47、框選選上，則則以后再再輸入文文字一旦旦到單元元格右端端將會自自動換行行。圖4-25設(shè)置單元元格內(nèi)文文本自動動換行辦公自自動化實實用技術(shù)術(shù)電子子教案40水利水電電出版社社3利用文文本框的的方法。可以在在工作表表上繪制制一個文文本框，并將其其調(diào)整到到適當(dāng)大大小，在在文本框框?qū)⑿枰奈淖肿咒浫?，本實例例中采用用的就是是該方式式?.4.4“選手情況況”工作作表的制制作“選手情情況”工工作表效效果如圖圖4-26所示，制制作時按按照圖中中樣式進(jìn)進(jìn)行設(shè)置置即可。圖4-26 “選手情況況”工工作表辦公自自動化實實用技術(shù)術(shù)電子子教案41水利水電電出版社社制作該工工作表的的目的有有兩個：一是可可以隨時時查看各

48、各個選手手的基本本情況；二是將將選手的的編號、姓名在在該表輸輸入之后后，以后后在其它它工作表表需要選選手姓名名時，只只要輸入入編號，姓名利利用查表表法即可可確認(rèn)。另外，本本工作表表中“編編號”一一列的輸輸入可以以考慮利利用序列列填充，“性別別”和“唱法類類型”兩兩列可以以考慮使使用選擇擇列表法法輸入。4.4.5“歌唱得分分”工作作表的制制作“歌唱得得分”工工作表效效果如圖圖4-27所示，因因為選手手出場順順序是通通過抽簽簽產(chǎn)生，以后的的打分是是按照出出場順序序而不是是按照選選手編號號進(jìn)行，所以需需要設(shè)置置“出場場序號”一列（該列通通過序列列填充輸輸入）。圖4-27 “歌唱得分分”工工作表辦公自

49、自動化實實用技術(shù)術(shù)電子子教案42水利水電電出版社社另外，本本工作表表只有到到現(xiàn)場比比賽時，才能輸輸入選手手編號和和裁判打打分，從從而確定定選手姓姓名，然然后計算算選手分分?jǐn)?shù)，最最后確定定選手名名次。所所以，大大家在圖圖4-27中只看到到一行數(shù)數(shù)據(jù)，這這只是測測試數(shù)據(jù)據(jù)，用來來驗證函函數(shù)和公公式的正正確性。在圖4-27中，姓名名、分?jǐn)?shù)數(shù)、名次次三列中中每個單單元格中中都有函函數(shù)或公公式，一一旦編號號和裁判判打分輸輸入之后后，他們們都會自自動出現(xiàn)現(xiàn)結(jié)果。下面說說明其該該工作表表的制作作過程：1.工作表表表格框架架的制作作按照圖4-27所示樣式式，制作作“歌唱唱得分”工作表表的表格格框架，將“出出場

50、序號號”一列列利用序序列填充充方法輸輸入完整整，對整整個表格格并做好好格式設(shè)設(shè)置。2.利用VLOOKUP函數(shù)根據(jù)據(jù)編號確確定選手手姓名該表中，“選手手編號”列也就就是C列按照抽抽簽順序序在比賽賽時錄入入，“姓姓名”一一列可以以事先設(shè)設(shè)置好公公式，以以便將來來錄入選選手編號號后，可可以自動動產(chǎn)生，這就需需要使用用垂直查查找函數(shù)數(shù)VLOOKUP。（1）VLOOKUP函數(shù)的使使用方法法和簡單單舉例VLOOKUP函數(shù)的作作用在表格或或首列查查找指定定的數(shù)值值，并由由此返回回表格或或數(shù)組當(dāng)當(dāng)前行中中指定列列處的數(shù)數(shù)值。辦公自自動化實實用技術(shù)術(shù)電子子教案43水利水電電出版社社VLOOKUP函數(shù)的格格式VL

51、OOKUP(lookup_value,table_array,col_index_num,range_lookup)。其中：Lookup_value為需要在在數(shù)據(jù)表表第一列列中查找找的數(shù)值值,可以為數(shù)數(shù)值、引引用或文文字串；Table_array為需要在在其中查查找數(shù)據(jù)據(jù)的數(shù)據(jù)據(jù)表或數(shù)數(shù)據(jù)區(qū)域域；Col_index_num為table_array中待返回回的匹配配值的列列序號。Range_lookup為一邏輯輯值，指指明函數(shù)數(shù)VLOOKUP返回時是是精確匹匹配還是是近似匹匹配。如如果為TRUE或省略，則返回回近似匹匹配值，也就是是說，如如果找不不到精確確匹配值值，則返返回小于于lookup_v

52、alue的最大數(shù)數(shù)值；如如果range_value為FALSE，函數(shù)VLOOKUP將返回精精確匹配配值。如如果找不不到，則則返回錯錯誤值#N/A。VLOOKUP函數(shù)的使使用舉例例比如：如如圖4-28所示，如如果想根根據(jù)D2的代號到到A2：B7數(shù)據(jù)區(qū)域域中查找找E2單元格中中的對應(yīng)應(yīng)名稱，則E2單元格中中的函數(shù)數(shù)形式應(yīng)應(yīng)該為：=VLOOKUP(D2,$A$2:$B$7,2)其中，$A$2:$B$7為采用引引用格式式，原因因是如果果該單元元格需要要向下拖拖動時，應(yīng)該要要求該數(shù)數(shù)據(jù)區(qū)域域不能變變化，所所以公式式中該數(shù)數(shù)據(jù)區(qū)域域要使用用絕對引引用。辦公自自動化實實用技術(shù)術(shù)電子子教案44水利水電電出版社

53、社圖4-28VLOOKUP函數(shù)使用用舉例（2）函數(shù)中中跨工作作表以及及跨工作作簿的單單元格引引用在辦公實實踐的許許多情況況下，有有時公式式中都可可能要用用到另一一工作表表單元格格中的數(shù)數(shù)據(jù)，如如Sheet1工作表F4的公式如如果為：（C4D4E4）Sheet2！B1其中“Sheet2！B1”表示工作作表Sheet2中的B1單元格地地址。這這個公式式表示計計算當(dāng)前前工作表表Sheet1中的C4、D4和E4單元格數(shù)數(shù)據(jù)之和和與Sheet2工作表的的B1單元格數(shù)數(shù)據(jù)的乘乘積，結(jié)結(jié)果存入入當(dāng)前工工作表Sheet1中的F4單元格。辦公自自動化實實用技術(shù)術(shù)電子子教案45水利水電電出版社社函數(shù)中還還可以進(jìn)進(jìn)

54、行跨工工作簿的的單元格格引用，此時地地址的一一般形式式為：工作簿簿名工工作表名名！單元元格地址址綜上所述述，到跨跨工作簿簿、工作作表的單單元格地地址引用用的方法法分別如如下： 在當(dāng)當(dāng)前工作作表中引引用本工工作表中中單元格格，只需需輸入單單元格的的地址即即可。 在當(dāng)當(dāng)前工作作表中引引用本工工作簿中中其它工工作表中中單元格格時，需需首先輸輸入被引引用的工工作表名名和一個個感嘆號號“！”，然后后再輸入入那個工工作表中中的單元元格地址址。 在當(dāng)當(dāng)前工作作表中引引用另外外工作簿簿中工作作表的單單元格時時，需要要首先輸輸入由中中括號“ ”包圍的引引用的工工作簿名名稱，然然后輸入入被引用用的工作作表名稱稱和

55、一個個感嘆號號“！”，最后后再輸入入那個工工作表中中的單元元格的地地址。例如：圖圖4-29中，用來來計算三三門課程程總成績績的單元元格E3中的公式式為：=B3*$G$3+C3*$G$4+D3*$G$5+離散數(shù)學(xué)學(xué)!B3+計算機(jī)成成績.xls計算機(jī)成成績!$B$4請各位讀讀者分析析其中跨跨工作表表、工作作簿引用用的方式式以及該該公式的的意義。辦公自自動化實實用技術(shù)術(shù)電子子教案46水利水電電出版社社圖4-29公式中跨跨工作表表和跨工工作簿的的單元格格引用（3）根據(jù)編編號利用用VLOOKUP查找姓名名根據(jù)上面面對VLOOKUP函數(shù)的使使用說明明，圖4-27“歌唱得分分”工作作表中用用來存放放姓名的的

56、D5單元格的的公式應(yīng)應(yīng)該為:“VLOOKUP(C5,選手情況況!$A$3:$B$21,2)”辦公自自動化實實用技術(shù)術(shù)電子子教案47水利水電電出版社社但是，此此時如果果將D5向下拖動動時，下下面的單單元格中中將出現(xiàn)現(xiàn)如圖4-30所示的“#N/A”的錯誤信信息，其其原因主主要是因因為前面面對應(yīng)“編號”一列還還沒有輸輸入內(nèi)容容。圖4-30因為公式式引用的的單元格格沒有內(nèi)內(nèi)容引起起的“#N/A”錯誤辦公自自動化實實用技術(shù)術(shù)電子子教案48水利水電電出版社社（4）利用IF函數(shù)避免免 “#N/A”錯誤要解決圖圖4-30出現(xiàn)的這這種“#N/A”錯誤，可可以在使使用該函函數(shù)之前前，先用用一個IF函數(shù)控制制一下，

57、比如可可以將D5單元格的的公式變變?yōu)?“=IF(C5=,VLOOKUP(C5,選手情況況!$A$3:$B$21,2)”。這樣，當(dāng)當(dāng)編號一一列沒有有輸入時時，則姓姓名什么么也不顯顯示（但但函數(shù)確確實已經(jīng)經(jīng)存在），而當(dāng)當(dāng)編號一一旦輸入入，姓名名一列就就會自動動利用VLOOKUP函數(shù)自動動查找。（5）函數(shù)的的復(fù)制D5的函數(shù)確確定好之之后，通通過鼠標(biāo)標(biāo)拖動將將其一直直拖動到到最后一一行記錄錄，則確確定姓名名一列的的公式復(fù)復(fù)制完成成。說明：本本節(jié)以后后我們用用到的公公式和函函數(shù)，為為了避免免某些單單元格沒沒有輸入入內(nèi)容引引起“#N/A”錯誤，都都采用了了這種利利用IF函數(shù)進(jìn)行行公式控控制的方方法。3利用

58、公公式計算算選手歌歌唱得分分分?jǐn)?shù)的計計算就需需要使用用公式來來進(jìn)行運運算。下下面介紹紹公式的的有關(guān)知知識：公式是對對工作表表中的數(shù)數(shù)據(jù)進(jìn)行行計算和和分析的的一種等等式，它它可以對對工作表表數(shù)值進(jìn)進(jìn)行算術(shù)術(shù)運算、關(guān)系運運算和邏邏輯運算算，同樣樣也可以以對字符符數(shù)據(jù)以以及其它它數(shù)據(jù)進(jìn)進(jìn)行運算算。公式可以以引用同同一工作作表中的的其它單單元格、同一工工作簿不不同工作作表中的的單元格格，或者者其它工工作簿的的工作表表中的單單元格。辦公自自動化實實用技術(shù)術(shù)電子子教案49水利水電電出版社社在進(jìn)行公公式輸入入時，應(yīng)應(yīng)該遵循循以下規(guī)規(guī)則：（1）公式輸輸入時必必須以“=”開頭，公公式中間間不能包包括空格格。（2

59、）公式中中的單元元格引用用，可以以在編輯輯區(qū)直接接輸入，也可以以通過單單擊該單單元格或或拖動單單元格區(qū)區(qū)域從而而實現(xiàn)自自動填入入。（3）用算術(shù)術(shù)運算符符號：+（加）、（減）、*（乘乘）、/（除）、（乘方），字符符運算符符號&(連接字符符串)以及關(guān)系系運算符符號（大于）、（小于）、=（等于）等來說說明公式式的操作作類型。（4）在公式式中可以以使用圓圓括號，也只能能使用圓圓括號，并且允允許圓括括號嵌套套，但是是應(yīng)該注注意嵌套套時前后后括號的的匹配關(guān)關(guān)系，計計算順序序是先內(nèi)內(nèi)層再外外層。（5）單元格格中的公公式內(nèi)容容顯示在在編輯欄欄中，計計算結(jié)果果則顯示示在單元元格內(nèi)，如果希希望直接接在單元元格中顯

60、顯示為公公式，可可以使用用Ctrl+鍵（在Tab鍵的上面面），再再次鍵入入，則取取消公式式顯示，重新顯顯示結(jié)果果。根據(jù)計分分規(guī)則，圖4-37“歌唱得分分”工作作表中用用來存放放分?jǐn)?shù)的的Q5單元格的的公式應(yīng)應(yīng)該為:“=IF(SUM(E5:P5)0,(SUM(E5:P5)-MIN(E5:P5)-MAX(E5:P5)/(COUNT(E5:P5)-2),)”。D5中的公式式輸入完完成之后后，通過過鼠標(biāo)拖拖動將其其一直拖拖動到最最后一行行記錄，則計算算選手歌歌唱分?jǐn)?shù)數(shù)一列的的公式復(fù)復(fù)制完成成。辦公自自動化實實用技術(shù)術(shù)電子子教案50水利水電電出版社社4利用RANK函數(shù)確定定選手名名次名次的確確認(rèn)對于于本例