項目四MicrosoftExcel2003應(yīng)用任務(wù)4.1MicrosoftExcel的數(shù)據(jù)錄入任務(wù)4.2MicrosoftExcel簡單文檔制作任務(wù)4.3MicrosoftExcel數(shù)據(jù)分析任務(wù)4.4MicrosoftExcel高級應(yīng)用

MicrosoftExcel是微軟公司的辦公軟件Microsoftoffice的組件之一，是由Microsoft為Windows和AppleMacintosh操作系統(tǒng)的電腦而編寫和運行的一款表格計算軟件。直觀的界面、出色的計算功能和圖表工具，再加上成功的市場營銷，使Excel成為最流行的計算機數(shù)據(jù)處理軟件，Excel不僅可以制作電子表格、完成許多復(fù)雜的數(shù)據(jù)運算、進行數(shù)據(jù)的分析和預(yù)測，并且提供了強大的制作圖表的功能，使復(fù)雜的數(shù)據(jù)直觀生動。任務(wù)4.1MicrosoftExcel的數(shù)據(jù)錄入

MicrosoftExcel是數(shù)據(jù)處理軟件，在使用Excel之前，首先要錄入數(shù)據(jù)，良好的數(shù)據(jù)錄入習(xí)慣可以提高工作效率，下面介紹常規(guī)數(shù)據(jù)錄入方法。

1．大量數(shù)據(jù)的錄入

(1)在錄入大量數(shù)據(jù)之前，首先要選擇需要錄入數(shù)據(jù)的區(qū)域：光標置于要錄入數(shù)據(jù)的第一個單元格，按住鼠標左鍵，拖動鼠標至需要的最后一個單元格，錄入?yún)^(qū)域即被選定，如圖4-1所示。圖4-1選擇錄入數(shù)據(jù)的區(qū)域

(2)水平錄入數(shù)據(jù)時，可以使用Tab鍵移動到下一單元格，如果需要修改或者重新錄入上一單元格的數(shù)據(jù)，使用Shift+Tab鍵。如圖4-2所示，在D1單元格錄入數(shù)據(jù)后，按下Tab鍵，光標將自動轉(zhuǎn)入A2單元格，按下Shift+Tab鍵，光標將回到D1單元格。

(3)垂直錄入數(shù)據(jù)時，可以使用Enter鍵移動到下一單元格，如果需要修改或者重新錄入上一單元格的數(shù)據(jù)，使用Shift+Enter鍵。如圖4-3所示，在A7單元格錄入數(shù)據(jù)后按下Enter鍵，光標將自動轉(zhuǎn)入B1單元格，按下Shift+Enter鍵，光標將回到A7單元格。圖4-2水平錄入數(shù)據(jù)圖4-3垂直錄入數(shù)據(jù)

2．快速錄入相同數(shù)據(jù)

1)在同一工作表中錄入數(shù)據(jù)

按住Ctrl鍵的同時，分別單擊鼠標左鍵，選中同一工作表的多個單元格后，輸入字符，如圖4-4所示，然后按Ctrl+Enter組合鍵，即可在選中的每個單元格中錄入相同的字符，如圖4-5所示。圖4-4選擇錄入相同數(shù)據(jù)的多個單元格圖4?5快速錄入結(jié)果

2)在不同工作表的相同單元格錄入

在不同工作表的相同單元格錄入相同的數(shù)據(jù)，首先選定需要錄入數(shù)據(jù)的工作表：按住Ctrl鍵的同時，分別選中不同工作表，如圖4-6所示，然后在需要錄入數(shù)據(jù)的單元格錄入數(shù)據(jù)，按Enter鍵即可。圖4-6不同工作表錄入數(shù)據(jù)

3．快速復(fù)制上一單元格

選中下面的單元格，按Ctrl+‘(’為西文的單引號）組合鍵，即可將上一單元格的內(nèi)容快速復(fù)制下來，如圖4-7所示。

4．快速輸入系統(tǒng)日期

按下Ctrl+;?組合鍵，即可快速輸入系統(tǒng)日期。按下Ctrl+Shift+;?組合鍵即可快速輸入系統(tǒng)時間，如圖4-8所示。

5．分數(shù)輸入

如果事先將Excel單元格設(shè)置為“分數(shù)”格式，即可在其中輸入2/3、5/7這樣的分數(shù)數(shù)據(jù)。除此以外，可以在“常規(guī)”單元格中按“0?+?空格?+?分數(shù)”的格式輸入分數(shù)，如輸入的“0□5/7”(□表示空格）即可顯示分數(shù)“5/7”，并按15位的數(shù)字精度進行數(shù)據(jù)處理，如圖4-9所示。圖4-7快速復(fù)制上一單元格圖4-8快速錄入時間、日期圖4-9分數(shù)錄入

6．輸入前面帶0的數(shù)字

如果直接在單元格輸入前面帶0的數(shù)字如“001”，則確定后單元格內(nèi)顯示“1”，前面的0被丟掉了。解決的一般辦法是先選中所有需要輸入前面帶0數(shù)字的單元格，再在任一被選中的單元格上單擊右鍵，選“設(shè)置單元格格式”，在彈出的對話框中單擊“數(shù)字”選項卡，再在“分類”框中選“文本”即可。但這樣操作無疑是比較麻煩的，最簡單的辦法是：在輸入數(shù)據(jù)時先輸入一個英文狀態(tài)的單引號，再輸入前面帶0的數(shù)字，前面的0就不會被丟掉了。如：要輸入“001”則在單元格內(nèi)鍵入“'001”即可，如圖4-10所示。圖4-10錄入帶0數(shù)字

7．自動更正

選擇“工具”——“自動更正”，打開“自動更正”對話框，在“替換”下面填入“數(shù)”，在“替換為”下面填入“數(shù)字信息技術(shù)系”，然后按“確定”按鈕，如圖4-11所示。以后只要在單元格中輸入“數(shù)”及后續(xù)文本(或按“Enter”鍵）后，系統(tǒng)會自動將其更正為“數(shù)字信息技術(shù)系”。如果不需要輸入“數(shù)字信息技術(shù)系”，可以按“Ctrl+Z”組合鍵取消。

Excel數(shù)據(jù)錄入技巧很多，請同學(xué)們在日常學(xué)習(xí)過程中注意收集總結(jié)練習(xí)，從而更快更好的掌握Excel的使用。圖4-11自動更正任務(wù)4.2MicrosoftExcel簡單文檔制作

4.2.1案例分析

本案例以學(xué)生成績的數(shù)據(jù)處理為例，介紹Excel的數(shù)據(jù)采集、數(shù)據(jù)處理和數(shù)據(jù)輸出。其中包括數(shù)據(jù)錄入、單元格設(shè)置、公式和函數(shù)、多工作表操作、圖表制作、數(shù)據(jù)排序、數(shù)據(jù)篩選等內(nèi)容。

1．問題的提出

學(xué)期結(jié)束時，班主任老師遇到一個難題：教務(wù)處將各位任課教師所給出的成績表(提供的素材為三個Excel工作薄文件，包括：大學(xué)英語?.xls，應(yīng)用文寫作?.xls，高等數(shù)學(xué).xls）和一張《計算機應(yīng)用》課程成績表的內(nèi)容(如圖4-12所示)提供給班主任，并要求根據(jù)該工作表和三個素材文件的數(shù)據(jù)，得到“各科成績表”(如圖4-13所示）、“成績統(tǒng)計表”(如圖4-14所示）及“各科等級表”(如圖4-15所示），還要制作“成績統(tǒng)計圖”(如圖4-16所示），以便對全班的成績進行各種統(tǒng)計分析，并查找滿足條件的記錄(如圖4-17所示）。圖4-12“計算機應(yīng)用”課程成績表圖4-13各科成績表圖4-14成績統(tǒng)計表圖4-15各科等級表圖4-16成績統(tǒng)計圖圖4-17自動篩選記錄開始，他覺得很簡單，但當他輸完《計算機應(yīng)用》的課程成績，并將其他三個成績表工作薄都打開，希望把各科成績粘貼到“各科成績表”工作表中時，發(fā)現(xiàn)粘貼后的單元格顯示了出錯信息，他手忙腳亂了半天，都沒有達到要求，就更別提后續(xù)的工作了。于是他只好硬著頭皮逐個輸入，但這極容易出錯，工作量又很大。不得已，他向熟悉Excel的李老師請教，經(jīng)過李老師的指點，他快速準確地完成了所有工作表的制作，并順利地對全班成績進行了統(tǒng)計分析工作。下面是他的解決方案。

2．解決方案

李老師告訴他，利用Excel進行數(shù)據(jù)處理和分析的過程一般分為三步，即：數(shù)據(jù)采集、數(shù)據(jù)處理和數(shù)據(jù)輸出。

1)數(shù)據(jù)采集

數(shù)據(jù)的采集包括原始數(shù)據(jù)的錄入和復(fù)制。

首先建立一個新的Excel工作薄，并在一張空白的工作表中輸入原始數(shù)據(jù)。接著打開所有的成績表工作薄，將相應(yīng)的工作表復(fù)制到新建工作薄中。要得到“各科成績表”工作表，應(yīng)該按要求將內(nèi)容復(fù)制到一個空白的工作表中。特別注意，在復(fù)制成績時，必須使用“選擇性粘貼”或者引用其他工作表相應(yīng)單元格中的數(shù)據(jù)，否則就會顯示出錯信息。

2)數(shù)據(jù)處理

至于要得到統(tǒng)計結(jié)果，就必須使用各種函數(shù)(如：CouNT、Max、Min、Count)及使用公式通過計算來進行數(shù)據(jù)處理。

3)數(shù)據(jù)輸出

Excel提供的圖表功能可以直觀的反映一組數(shù)據(jù)之間的關(guān)系，制作圖表時，要特別注意數(shù)據(jù)源的選取。

Excel提供的數(shù)據(jù)篩選功能可以按指定的條件，快速地從數(shù)據(jù)庫中篩選出滿足條件的記錄。篩選記錄時，要注意正確設(shè)置篩選條件。

3．相關(guān)知識點

(1)常用數(shù)據(jù)的輸入方法。

(2)單元格的格式化設(shè)置。

(3)多工作表的操作：

①工作表的命名

②工作表的復(fù)制、移動、插入、刪除

③工作表之間數(shù)據(jù)的復(fù)制、粘貼，單元格的引用

(4)公式和函數(shù)的使用。

(5)圖表的制作。

(6)數(shù)據(jù)的排序。

(7)數(shù)據(jù)的篩選。4.2.2實現(xiàn)步驟

在本節(jié)中，我們將利用Excel完成圖4-12～4-17的創(chuàng)建工作。

(1)先建立圖4-12中的“《計算機應(yīng)用》課程成績表”。

(2)根據(jù)已有的“大學(xué)英語.xls”、“應(yīng)用文寫作.xls”、“高等數(shù)學(xué).xls”中的數(shù)據(jù)建立如圖4-13所示的“各科成績表”。

(3)通過對“各科成績表”中的數(shù)據(jù)進行統(tǒng)計，得到圖4-14所示“成績統(tǒng)計表”。

(4)將“各科成績表”中的各科分數(shù)轉(zhuǎn)化為相應(yīng)的等級，得到如圖4-15所示的“各科等級表”。

(5)根據(jù)圖4-14“成績統(tǒng)計表”中的源數(shù)據(jù)建立如圖4-16所示的“成績統(tǒng)計圖”。

(6)從圖4-14“成績統(tǒng)計表”數(shù)據(jù)中，篩選出滿足條件的記錄，得到如圖4-17所示的自動篩選的結(jié)果。

1．建立簡單的Excel工作表

1)工作簿的建立

新建Excel工作簿“成績表.xls”，操作步驟如下：

①啟動Excel。

②單擊“常用”工具欄的“保存按鈕”，在“另存為”對話框中將文件名由“Book1.xls”改成“成績表.xls”，單擊對話框中的“保存”按鈕，將文件保存在個人文件夾里。說明：

一個Excel文件就是一個工作簿，在Excel中處理的各種數(shù)據(jù)最終都是以工作簿文件的方式儲存在磁盤上。

每個工作簿通常都由多個工作表組成，在啟動Excel時，自動創(chuàng)建的工作簿“Book1”中包含“Sheet1”、“Sheet2”、“Sheet3”三張工作表。用戶可以根據(jù)實際需求插入或者刪除工作表。每個工作表都是一個由若干行和列組成的二維表格、行號用數(shù)字表示(1～65536行)，列標由大寫英文字母表示(A，B，…，Z，AA，AB，…，AZ，BA，…，IV共256行)。在每個行列的交叉點成為單元格。每個單元格用其所在的行標和列標標示，稱為單元格地址。如：工作表第一行、第一列的單元格用A1表示。工作表第六行、第三列的單元格用C6表示。Excel的工作界面如圖4-18所示。圖4-18Excel工作界面

2)在工作表Sheet1中輸入數(shù)據(jù)

輸入如圖4-12所示的標題及表頭數(shù)據(jù)，操作步驟如下：

①在當前工作表Sheet1中，選中單元格A1，輸入標題“《計算機應(yīng)用》課程學(xué)生成績登記表”，按Enter鍵。

②在單元格A2中，輸入“學(xué)號”，再按向右光標→，或Tab鍵，使得B2單元格成為當前單元格。

③輸入“姓名”，按下Tab鍵，使C2單元格成為當前單元格。依此方法依次輸入表頭的其他內(nèi)容。輸入“學(xué)號”列數(shù)據(jù)，操作步驟如下：

①鼠標單擊A3單元格，在A3單元格中，輸入學(xué)號“20091901001”。

②鼠標指針指向A3單元格的“填充柄”(位于單元格右下角的小黑塊)，此時鼠標指針變成黑十字，按住鼠標向下拖曳填充柄，在拖曳的過程中填充柄的右下角出現(xiàn)填充數(shù)據(jù)，拖至目標單元格時釋放鼠標，填充效果如圖4-19所示。圖4-19用填充柄填充數(shù)據(jù)說明：

①在實際工作中，像學(xué)號、電話號碼、身份證號、銀行賬號等數(shù)字信息并不是真正意義上需要參與數(shù)學(xué)運算，但又需要將數(shù)字顯示出來，對于這類數(shù)字，將以“文本”的形式對待。除了在數(shù)字前加上西文單引號“’”外，還可以先選擇要改變數(shù)字格式的單元格，在菜單欄中選擇“格式”—“單元格”命令，打開“單元格格式”對話框，選擇“數(shù)字”選項卡，在“分類”欄中選擇“文本”選項，將單元格的格式設(shè)置為“文本”。②使用Excel提供的“自動填充”功能，可以極大地減少數(shù)據(jù)輸入的工作量。通過拖曳填充柄，就可以激活“自動填充”功能。利用自動填充可以進行文本、數(shù)字、日期等序列的填充和數(shù)據(jù)的復(fù)制，也可以進行公式復(fù)制等。

輸入“姓名”列數(shù)據(jù)，操作步驟如下：

①選擇B3單元格，在B3單元格中，輸入姓名“申黎瑩”，按Enter鍵。

②再在B4單元格中，輸入姓名“王璐媛”，按Enter鍵。

③用同樣的方法依次輸入姓名列的其他內(nèi)容。小技巧：

在一個班級的各科成績表中，每次都要輸入學(xué)號、姓名等內(nèi)容。對于這種重復(fù)的工作，如果用“自定義序列”的方法完成，就可以達到一次輸入數(shù)據(jù)多次重復(fù)使用的目的?！白远x序列”可以用以下兩種方法實現(xiàn)。

方法一：如果已經(jīng)在單元格區(qū)域中輸入了姓名序列，那么先選定該單元格區(qū)域，然后在菜單欄中選擇“工具”—“選項”命令，選擇“自定義序列”選項卡，單擊“導(dǎo)入”按鈕，就可以將選定區(qū)域的數(shù)據(jù)轉(zhuǎn)化為自定義序列數(shù)據(jù)，如圖4-20所示。方法二：也可以直接在“自定義序列”選項卡中輸入新序列。方法是：先在圖4-20選項對話框“自定義序列”列表框中選擇“新序列”，然后在“輸入序列”編輯列表框中輸入所有姓名序列，每個姓名輸入完成后，必須按Enter鍵結(jié)束，再輸入下一個姓名。整個姓名序列輸入完畢后，單擊“添加”按鈕，則新定義的姓名序列就會出現(xiàn)在“自定義序列”區(qū)域，單擊“確定”按鈕。

自定義序列建立后，在任何時候只要在單元格中輸入該序列中的起始數(shù)據(jù)，就可以按規(guī)定的序列進行填充。如：已建立了圖4-20所示的序列后，只要在起始單元格中輸入“楊靜”，并按住鼠標向下拖曳填充柄，就可以自動填充該序列的所有姓名。圖4-20自定義填充序列的建立輸入“性別”列數(shù)據(jù)，操作步驟如下：

①選擇C3單元格，在C3單元格中，輸入“女”。

②鼠標指針指向C3單元格的“填充柄”，當鼠標指針變?yōu)楹谑謺r，雙擊“填充柄”，這時“性別”列的內(nèi)容全部填充為“女”。但在實際情況下，“性別”列有些單元格的內(nèi)容應(yīng)該修改為“男”。

③選擇第一個應(yīng)該修改為“男”的單元格如C4，在按住Ctrl鍵的同時，分別單擊其他應(yīng)該修改為“男”的單元格。

④在被選中的最后一個單元格中輸入“男”。

⑤同時按住Ctrl+Enter鍵。可以看到，所有被選中單元格的內(nèi)容同時變?yōu)椤澳小?。如圖4-21所示。圖4-21多個單元格內(nèi)輸入相同的數(shù)據(jù)說明：

①雙擊活動單元格的“自動填充”柄，若有空白的豎列單元格則活動單元格上的內(nèi)容就在豎列單元格內(nèi)復(fù)制。自動產(chǎn)生的序列數(shù)由前一列向下直到遇到空白單元格為止的單元格個數(shù)決定。這種方法對于要填充的單元格區(qū)域較大（如超過一屏），顯得極為方便。

②如果想在多個單元格中輸入相同的數(shù)據(jù)，只要選擇所有需要包含此信息的單元格，在輸入數(shù)值、文本或公式后，同時按住Ctrl+Enter鍵，同樣的信息會輸入到被選擇的所有單元格中。輸入“平時成績”、“作業(yè)設(shè)計”、“期末考試”列的數(shù)據(jù)，操作步驟如下：

①鼠標單擊D3單元格，此時鼠標指針在單元格上以空心十字光標出現(xiàn)，按住鼠標左鍵向右拖曳3列至“期末考試”列后，繼續(xù)向下拖曳至最后一條記錄。

②直接在D3單元格輸入“90”，按下Tab鍵后，活動單元格移動到右邊的E3單元格，輸入“88”；用同樣的方法移動到F3輸入“95”，再次按下Tab鍵后，活動單元格就不再向右移動，而是自動移到下一行的D4單元格，如圖4-22所示，等待輸入數(shù)據(jù)。依此方法，就可以快速輸入所有學(xué)生的成績。圖4-22選定單元格區(qū)域塊速批量輸入數(shù)據(jù)③所有成績輸入完畢，只要移動方向鍵或者用鼠標單擊某個單元格，就可以關(guān)閉選定的單元格區(qū)域，否則活動單元格就會在該區(qū)域中不斷循環(huán)移動。

小技巧：

為了快速批量地輸入數(shù)據(jù)，可以先選定輸入數(shù)據(jù)的單元格區(qū)域，以保證活動單元格只能在該區(qū)域內(nèi)移動。通過不斷按Tab鍵就可以自動在選定的區(qū)域中進行換行或換列。

注意：

在選定單元格區(qū)域進行快速輸入時，只能使用Enter鍵(向下)、Shift+Enter鍵(向上)、Tab鍵(向右)和Shift+Tab鍵(向左)。而不能使用方向鍵↓、↑、→、←，也不能用鼠標單擊任何單元格，否則將破壞單元格區(qū)域的選定。

3)單元格計算

計算所有學(xué)生的“總成績”(其中：總成績=平時成績*20%+作業(yè)設(shè)計*30%+期末考試*50%)，操作步驟如下：

①選擇目標單元格G3，在單元格中先輸入“=”，表明后面輸入的內(nèi)容是公式。

②單擊D3單元格，該單元格周圍出現(xiàn)閃爍的虛線框，表示引用此單元格中的數(shù)據(jù)，再輸入“*0.2+”；③單擊E3單元格后，輸入“*0.3+”；再單擊F3單元格后，輸入“*0.5”。此時G3單元格及公式編輯欄中的公式為：“=D3*0.2+E3*0.3+F3*0.5”，如圖4-23所示。

④按Enter鍵或單擊編輯欄中的“輸入”按鈕√確定。此時G3單元格中將顯示計算結(jié)果。

⑤鼠標指針指向G3單元格的“填充柄”，當鼠標指針變?yōu)楹谑謺r，雙擊填充柄，將G3單元格的計算公式自動復(fù)制到G4、G5……等其他單元格中。圖4-23輸入公式注意：

算術(shù)運算符中的乘號和除號與數(shù)學(xué)中使用的符號不同，乘號要用“*”，除號要用“/”，乘方用“^”，這是初學(xué)者經(jīng)常容易忽視的問題。

在Excel的公式中，最好包含單元格引用，如：“=D3*0.2+E3*0.3+F3*0.5”，因為在Excel中，用公式進行自動計算時，公式中的單元格引用都會被替換成相應(yīng)的數(shù)值進行計算，這樣具有較強的通用性。即使對公式進行復(fù)制后，也仍然適用，不用調(diào)整公式。但如果在公式中直接使用數(shù)值，則復(fù)制公式后，必須修改公式中的數(shù)值。在按Enter鍵或單擊編輯欄中的“輸入”按鈕√完成公式計算之前，不要用鼠標單擊公式中不應(yīng)包含的其他單元格，否則Excel會自動用選擇的單元格來替換現(xiàn)有的公式引用。

說明：

Excel中的“公式”是指在單元格執(zhí)行計算功能的等式。公式可以在單元格中直接輸入，也可以在編輯欄中輸入。Excel中的所有公式都必須以“=”號開頭，若無等號，Excel將其理解為正文，所以“=”是公式中最不可缺少的一個運算符?！?”后面是參與計算的運算數(shù)和運算符，每一個運算數(shù)可以是常量、單元格或區(qū)域引用、單元格名稱或函數(shù)等。

Excel提供了四種類型的運算符：算術(shù)運算符、比較運算符、文本運算符和引用運算符。

算術(shù)運算符包括：+(加)、-(減)、*(乘)、/(除)、^(乘方)、%(百分號)等，用于完成基本的數(shù)學(xué)運算。

比較運算符包括：=(等于)、<(小于)、>(大于)、<=(小于等于)、>=(大于等于)、<>(不等于)，用于完成對兩個數(shù)值的比較，并返回一個邏輯值TRUE或FALSE。

文本運算符包括：&，用于連接字符串。引用運算符包括：冒號“：”(區(qū)域運算符)、逗號“，”(聯(lián)合運算符)，用于對指定的區(qū)域引用進行合并計算。如：“A1：B2”表示A1、A2、B1、B2共四個單元格參加運算；“A1，B2”表示A1，B2兩個單元格參加運算。

運算符的優(yōu)先級由高到低依次為：引用運算符、算術(shù)運算符、文本運算符、比較運算符。如果是相同優(yōu)先級的運算符，按照從左至右的順序進行運算；如果要改變運算順序可以采用括號。

4)單元格格式設(shè)置

將標題字體設(shè)置為“幼圓”、“加粗”、“18”號，并使標題在A1:G1列合并及居中，操作步驟如下：

①選擇A1單元格，執(zhí)行“格式”—“單元格”命令，選擇“字體”選項卡，在“字體”列表框中，選擇“幼圓”，在“字形”列表框中，選擇“加粗”，在“字號”列表框中，選擇“18”，如圖4-24所示。單擊“確定”按鈕。

②選擇單元格區(qū)域A1：G1，在“格式”工具欄中，單擊“合并及居中”按鈕。這時，被選中的單元格區(qū)域A1：G1合并為一個單元格，并且居中顯示單元格中包含的內(nèi)容。圖4-24設(shè)置標題字體說明：

選擇單元格區(qū)域A1：G1后，執(zhí)行“格式”—“單元格”命令，選擇“對齊”選項卡，單擊“水平對齊”下拉按鈕，選擇“跨列居中”，也可以將標題居中。但“跨列居中”與“合并及居中”還是有區(qū)別的：“合并及居中”是將選取的單元格區(qū)域合并成為一個單元格后，內(nèi)容居中顯示；而“跨列居中”只是將單元格區(qū)域中的內(nèi)容居中顯示，它的顯示效果與“合并及居中”相同，但并沒有將單元格合并。

將數(shù)據(jù)區(qū)域所有單元格的字號設(shè)置為“10”；垂直對齊方式和水平對齊方式都設(shè)置為“居中”，操作步驟如下：①選擇單元格區(qū)域A3：G39，從“格式”工具欄中，單擊“字號”列表框右側(cè)的下拉按鈕，選擇字號為“10”。

②執(zhí)行“格式”—“單元格”命令，選擇“對齊”選項卡，在“垂直對齊”下拉列表框中，選擇“居中”；在“水平對齊”下拉列表框中，選擇“居中”。如圖4-25所示。

③單擊“確定”按鈕。圖4-25設(shè)置數(shù)字區(qū)域?qū)R方式將表格的外邊框設(shè)置為雙細線，內(nèi)邊框為單細線，操作步驟如下：

①選擇單元格區(qū)域A2:G39。

②執(zhí)行“格式”—“單元格”命令，選擇“邊框”選項卡，在“線條”區(qū)的“樣式”列表框中，選擇雙細線；在“預(yù)置”欄中，單擊“外邊框”按鈕，為表格添加外邊框。

③在“線條”區(qū)的“樣式”列表框中，選擇單細線，在“預(yù)置”欄中，單擊“內(nèi)部”按鈕，為表格添加內(nèi)邊框。設(shè)置效果如圖4-26所示。

④單擊“確定”按鈕。圖4-26設(shè)置邊框為表格列標題區(qū)域添加淺綠色底紋，并設(shè)置垂直對齊方式和水平對齊方式為“居中”，操作步驟如下：

①選擇表格列標題單元格區(qū)域A2：G2。

②執(zhí)行“格式”—“單元格”命令，選擇“圖案”選項卡，在“單元格底紋”的“顏色”欄中選擇“淺綠”，如圖4-27所示。

③選擇“對齊”選項卡，單擊“水平對齊”下拉按鈕，選擇“居中”；單擊“垂直對齊”下拉按鈕，選擇“居中”。

④單擊“確定”按鈕。圖4-27設(shè)置底紋將表格列標題行的行高設(shè)置為30.00(40像素)，將“平時成績”列的列寬適當調(diào)窄，操作步驟如下：

①移動鼠標指針到列標題行(第2行)的下邊框，此時鼠標指針形狀為“”，向下拖曳鼠標，鼠標所在位置出現(xiàn)一條水平虛線，并且出現(xiàn)一個顯示行高的標簽，當標簽中顯示“高度：30.00(40像素)”時，釋放鼠標，完成列標題行行高的設(shè)置。如圖4-28所示。圖4-28快速調(diào)整行高②移動鼠標指針到“平時成績”列的右邊框，當鼠標指針形狀為“”，向左拖曳鼠標，到適當位置時釋放鼠標，列寬變窄。

這時會發(fā)現(xiàn)“平時成績”列的文字不能完全顯示。為了在列寬變窄的同時能夠正常顯示單元格內(nèi)的文字，可以在一個單元格內(nèi)將文本分兩行顯示。但當按Enter鍵后，會跳至下一個單元格，那么如何在一個單元格內(nèi)將文本分多行顯示呢？將列標題“平時成績”、“作業(yè)設(shè)計”、“期末成績”在單元格內(nèi)分兩行顯示，操作步驟如下：

①選定“平時成績”所在的單元格D2，雙擊D2單元格，使該單元格處于編輯狀

態(tài)，將閃動的插入指針定位在“平時”之后(此處為需要換行的位置，也可直接在編輯欄中定位)。

②同時按下Alt+Enter鍵，單元格中的文本被分為兩行。

③重復(fù)步驟①、②，可以對“作業(yè)設(shè)計”、“期末考試”進行相同的換行操作。結(jié)果如圖4-29所示。說明：

也可以執(zhí)行“格式”—“單元格”命令，選擇“對齊”選項卡，在“文本控制”欄中，選中“自動換行”復(fù)選框，如圖4-30所示。將超出單元格長度的文本自動換到下一行。但這種方法與使用組合鍵Alt+Enter換行的不同之處在于，前者只有當文本超出單元格寬度時才換行，后者則不管文本是否超出單元格寬度，都會在指定位置強行換行。

還可以執(zhí)行“格式”—“單元格”命令，選擇“對齊”選項卡，在“文本控制”欄中，選中“縮小字體填充”復(fù)選框，使文本自動調(diào)節(jié)字號大小與單元格的寬度相匹配。

“自動換行”與“縮小字體填充”的不同在于前者不改變字體，只調(diào)整行高；而后者則調(diào)節(jié)字號大小，而不改變行高。兩者是互斥選項，只能取其一。圖4-29在單元格內(nèi)換行將“平時成績”、“作業(yè)設(shè)計”、“期末考試”三列的列寬調(diào)整為最適合的列寬，操作步驟如下：

①移動鼠標指針到“平時成績”列的右邊框，當鼠標指針變形為時，雙擊鼠標，可以將該列的列寬調(diào)整到一個最合適的列寬(和每列中輸入最多內(nèi)容的單元格相匹配)。

②同時選取“作業(yè)設(shè)計”、“期末考試”兩列，執(zhí)行“格式”—“列”—“最適合的列寬”命令，可以同時將多列調(diào)整到一個最合適的列寬。說明：

①執(zhí)行“格式”—“行”—“行高”命令或執(zhí)行“格式”—“列”—“列寬”命令可以精確地設(shè)置行高或列寬。

②如果要同時調(diào)整多行的行高或多列的列寬，可以選中要改變行高的所有行或要改變列寬的所有列，只要用鼠標調(diào)整一行的行高或任何一列的列寬，或執(zhí)行菜單命令，就可以將選中的行高或列寬同時改變。通過減少小數(shù)的位數(shù)，將“總成績”的結(jié)果以整數(shù)位呈現(xiàn)，操作步驟如下：

①選擇單元格區(qū)域G3：G39。

②執(zhí)行“格式”—“單元格”命令，選擇“數(shù)字”選擇卡，在“分類”列表框中選擇“數(shù)值”，在“小數(shù)位數(shù)”數(shù)字框中輸入或選擇0，如圖4-31所示。

③單擊“確定”按鈕。圖4-31“數(shù)字”選項卡說明：

在選擇單元格區(qū)域后，也可以通過單擊“格式”工具欄中的“減少小數(shù)位數(shù)”按鈕來減少小數(shù)位數(shù)，每單擊一次按鈕，將以四舍五入的方式減少一位小數(shù)位數(shù)。

使用“格式”工具欄的其他數(shù)字格式按鈕，也可以快速設(shè)置數(shù)字格式：

貨幣樣式：將單元格的數(shù)字設(shè)置為會計專用格式，自動加上貨幣符號、兩位小數(shù)及千位分隔符“，”。

百分比樣式：將原數(shù)字乘以100后，再在數(shù)字后加上百分號。

千位分隔樣式：在數(shù)字中加入千位分隔符。

增加小數(shù)位數(shù)：每按一次將增加一位小數(shù)位數(shù)。

5)工作表名稱的更改

將當前工作表的名稱“Sheet1”更改為“計算機應(yīng)用”，操作步驟如下：

①雙擊工作表“Sheet1”的標簽。

②當工作表標簽變出現(xiàn)反白(黑底白字)時，輸入新的工作表名“計算機應(yīng)用”，按“Enter”鍵確認。

③保存工作薄文件“成績表.xls”。

2．數(shù)據(jù)匯總

1)工作表的復(fù)制和移動

將“大學(xué)英語.xls”工作簿中的“Sheet1”工作表，復(fù)制到“成績表.xls”工作簿中的“計算機應(yīng)用.xls”工作表之前，并將復(fù)制后的目標工作表“Sheet1”更名為“大學(xué)英語”，操作步驟如下：

①分別打開“大學(xué)英語.xls”。

②選擇“大學(xué)英語.xls”工作簿中的“Sheet1”工作表作為當前工作表(源表)，在工作表“Sheet1”標簽上單擊鼠標右鍵，彈出快捷菜單，從中選擇“移動或復(fù)制工作表”命令，出現(xiàn)如圖4-32所示的“移動或復(fù)制工作表”對話框。圖4-32“移動或復(fù)制工作表”對話框③在“移動或復(fù)制工作表”對話框中的“工作簿”下拉列表中，選擇“成績表.xls”(目標表)，在“下列選定工作表之前”列表框中，選擇“計算機應(yīng)用.xls”工作表，同時選中“建立副本”復(fù)選框，如圖4-33所示。

④單擊“確定”按鈕。這時，當前工作表已經(jīng)變成“成績表.xls”工作薄中的“Sheet1”。

⑤鼠標右鍵單擊當前的工作表標簽“Sheet1”，從快捷菜單中選擇“重命名”命令，輸入新的工作表名“大學(xué)英語”，按Enter鍵確認。⑥重復(fù)步驟①～④，用相同的方法，分別將“應(yīng)用文寫作.xls”工作簿中的“應(yīng)用文寫作”工作表、“高等數(shù)學(xué).xls”工作簿中的“高等數(shù)學(xué)”工作表復(fù)制到“成績表.xls”工作簿中的“Sheet2”工作表之前。

說明：

如果在“移動或復(fù)制工作表”對話框中，不選中“建立副本”復(fù)選框，那么執(zhí)行的結(jié)果就是移動工作表，而不是復(fù)制工作表。在“成績表.xls”工作薄中，將前四個工作表的排列順序調(diào)整為“大學(xué)英語”、“計算機應(yīng)用”、“高等數(shù)學(xué)”、“應(yīng)用文寫作”，操作步驟如下：

①鼠標右鍵單擊“高等數(shù)學(xué)”工作表標簽。

②按住鼠標左鍵，這時工作表標簽左上角出現(xiàn)一個黑色三角形，指示了工作表的位置。

③按住鼠標指針向左拖曳，當黑色三角形到達目標位置后釋放鼠標，如圖4-34所示，這時，就把“高等數(shù)學(xué)”工作表移動到“應(yīng)用文寫作”工作表之前了。圖4-34用鼠標拖曳方式移動工作表說明：

①用鼠標拖曳的方式復(fù)制工作表與移動工作表的操作相似，只是在拖曳的過程中要同時按住Ctrl鍵，這時鼠標的文檔標記上會增加一個小“+”號，釋放時要注意，應(yīng)先松開鼠標，再放開Ctrl鍵。

②如果使用鼠標拖曳的方法在不同工作簿中復(fù)制或移動工作表，必須同時打開兩個工作簿窗口，在源工作簿中選擇工作表后，按住鼠標指針向目標工作簿的位置拖曳，當黑色三角形到達目標位置之后釋放鼠標。

③在同一工作簿中復(fù)制或移動工作表，使用鼠標拖曳的方法比較方便；而在不同工作簿中復(fù)制或移動工作表，則使用復(fù)制或移動工作表命令更可靠。

2)工作表的刪除和插入

刪除“成績表.xls”工作簿中的工作表“Sheet2”、“Sheet3”，操作步驟如下：

①單擊工作表“Sheet2”標簽。

②執(zhí)行“編輯”—“刪除工作表”命令，將“Sheet2”工作表從當前工作簿中刪除。

③鼠標右鍵單擊當前工作簿中的工作表標簽“Sheet3”。

④從快捷菜單中選擇“刪除”命令，將“Sheet3”工作表從當前工作簿中刪除。在“成績表.xls”工作簿中的工作表“大學(xué)英語”之前插入一張新的工作表，并將新工作表更名為“各科成績表”，操作步驟如下：

①選定工作表“大學(xué)英語”。

②執(zhí)行“插入”—“工作表”命令，在當前工作表“大學(xué)英語”之前，添加一張新的工作表，同時新的工作表成為當前工作表。

③選擇新工作表標簽，單擊鼠標右鍵，選擇“重命名”命令，輸入新工作表名“各科成績表”，按Enter確定。小技巧：

①按住Ctrl鍵同時選擇多個要刪除的工作表，執(zhí)行“刪除工作表”命令后，就可以一次性刪除它們。

②同時添加多個工作表，應(yīng)首先選定與要添加工作表數(shù)目相同的工作表標簽，再執(zhí)行“插入”—“工作表”命令，就可以在工作簿中添加與選定工作表標簽數(shù)目相同的工作表。

說明：

選定多個連續(xù)工作表的方法是：單擊第一個工作表標簽之后，按住Shift鍵，然后單擊最后一個工作表標簽。

選定多個不連續(xù)工作表的方法是：單擊第一個工作表標簽之后，按住Ctrl鍵，然后分別單擊其余需要選定的工作表標簽。

3)單元格數(shù)據(jù)的復(fù)制與粘貼

將“成績表.xls”工作簿中“計算機應(yīng)用”工作表的“學(xué)號”、“姓名”、“性別”以及“總成績”列的數(shù)據(jù)復(fù)制到“各科成績表”工作表中，操作步驟如下：

①選擇“計算機應(yīng)用”工作表，選定要復(fù)制的單元格區(qū)域A2：C39。

②在“常用”工具欄中，單擊“復(fù)制”按鈕，被選擇單元格區(qū)域的四周會出現(xiàn)一個閃爍的虛線框。

③切換到“各科成績表”工作表，單擊A1單元格。

④在“常用”工具欄中，單擊“粘貼”按鈕，學(xué)號”、“姓名”、“性別”列數(shù)據(jù)被粘貼到目標單元格。⑤用相同方法，在“計算機應(yīng)用”工作表中，選擇單元格區(qū)域G2：G39后，復(fù)制“總成績”列數(shù)據(jù)，在“各科成績表”工作表中，選擇目標單元格E2進行粘貼。但復(fù)制后目標單元格中卻出現(xiàn)了錯誤的結(jié)果“#VALUE!”，如圖4-35所示。

⑥單擊“常用”工具欄的“撤銷”按鈕，取消剛才進行的錯誤粘貼。

為什么用相同的方法進行數(shù)據(jù)復(fù)制，前三列的結(jié)果正確，而最后一列的結(jié)果卻出錯了呢？這是因為前三列單元格中只包含單純的文字或數(shù)據(jù)，而最后一列單元格中卻含有計算公式，復(fù)制與移動包含有公式的單元格將會對目標單元格產(chǎn)生影響。圖4-35復(fù)制單元格數(shù)據(jù)后出現(xiàn)錯誤結(jié)果將公式復(fù)制到粘貼區(qū)域后，Excel會自動將粘貼區(qū)域的公式調(diào)整為與該區(qū)域有關(guān)的相對位置。如：復(fù)制之前，在“計算機應(yīng)用”工作表的G3單元格中，包含的公式為：=D3*0.2+E3*0.3+F3*0.5，用于同一行左邊三列單元格的求和結(jié)果，粘貼后在“各科成績表”工作表的目標單元格E2中，包含的公式為：=B2*0.2+C2*0.3+D2*0.5，同樣用于計算同一行左邊三列單元格的求和結(jié)果，但由于左邊三列的數(shù)據(jù)類型都是文本型，不具備數(shù)值計算的條件，于是產(chǎn)生“#VALUE!”的錯誤信息。對于包含公式的單元格來說，通常具有“公式”和“值”兩種屬性。如圖4-35所示，在“計算機應(yīng)用”工作表的G3單元格中顯示的是公式的計算值“92”，而公式編輯欄中顯示的卻是計算公式“=D3*0.2+E3*0.3+F3*0.5”。那么在復(fù)制單元格時，能否只挑選其中的某種屬性來復(fù)制呢？使用選擇性粘貼的方法，就可以任意選擇是只復(fù)制“值”屬性，還是只復(fù)制“公式”屬性。小技巧：

①在選取粘貼區(qū)域時，只選取一個單元格，Excel會以該單元格為粘貼區(qū)域的左上角，并依據(jù)源數(shù)據(jù)的范圍決定粘貼區(qū)域。

②當數(shù)據(jù)復(fù)制到粘貼區(qū)域后，源數(shù)據(jù)區(qū)域的四周仍存在閃爍的虛線框，可按Esc鍵取消閃爍的虛線框。

③也可以用鼠標拖動的方法復(fù)制單元格數(shù)據(jù)，方法是：先選中復(fù)制區(qū)域，將鼠標指針移至該區(qū)域的邊框，當鼠標指針變?yōu)樾螤詈螅侔醋trl鍵的同時，拖動該區(qū)域到目標位置后，先釋放鼠標，再放開Ctrl鍵。④對于相鄰的單元格，也可以通過填充柄將數(shù)據(jù)復(fù)制到相鄰的單元格中。

說明：

在Excel中輸入計算公式或函數(shù)后，經(jīng)常會遇到Excel的錯誤信息，如圖4-35中的“#VALUE!”等。這主要是執(zhí)行了錯誤的操作，Excel會根據(jù)不同的錯誤類型給出不同的錯誤提示，便于檢查和排除錯誤?，F(xiàn)將Excel中最常見的錯誤信息以及錯誤原因和處理方法在表4-1中列出。表4-1常見錯誤信息以及錯誤原因和處理方法

利用選擇性粘貼，將“計算機應(yīng)用”工作表中的“總成績”列的數(shù)據(jù)復(fù)制到“各科成績表”的目標單元格中，并分別將“應(yīng)用文寫作”列數(shù)據(jù)、“大學(xué)英語”列數(shù)據(jù)、“高等數(shù)學(xué)”列數(shù)據(jù)，復(fù)制到“各科成績表”中的相應(yīng)位置，操作步驟如下：

①選定“計算機應(yīng)用”工作表，選中單元格區(qū)域G2：G38。

②執(zhí)行“編輯”—“復(fù)制”命令。

③選擇“各科成績表”工作表中的目標單元格D1。④在“常用”工具欄中，單擊“粘貼”按鈕旁邊的下拉按鈕，選擇“值”命令。檢查目標單元格區(qū)域，可以發(fā)現(xiàn)已經(jīng)成功地復(fù)制了“總成績”數(shù)據(jù)，但是源單元格的格式并未復(fù)制過來。

⑤繼續(xù)選定目標單元格E1，執(zhí)行“編輯”—“選擇性粘貼”命令，或單擊“常用”工具欄中“粘貼”按鈕旁的下拉按鈕，選擇“選擇性粘貼”，如圖4-36所示。出現(xiàn)“選擇性粘貼”對話框。圖4-36執(zhí)行“選擇性粘貼”命令⑥在“選擇性粘貼”對話框中的“粘貼”欄中，選擇“格式”選項，如圖4-37所示，單擊“確定”按鈕。再次檢查目標單元格區(qū)域，發(fā)現(xiàn)數(shù)據(jù)格式、數(shù)據(jù)值與源單元格一致。

⑦將E1單元格中的“總成績”改為“計算機應(yīng)用”。

⑧用相同的方法，分別將“應(yīng)用文寫作”工作表中的“應(yīng)用文寫作”列數(shù)據(jù)、“大學(xué)英語”工作表中的“大學(xué)英語”列數(shù)據(jù)、“高等數(shù)學(xué)”工作表中的“高等數(shù)學(xué)”列數(shù)據(jù)，復(fù)制到“各科成績表”中的相應(yīng)位置。復(fù)制結(jié)果如圖4-38所示。圖4-37“選擇性粘貼”對話框圖4-38“各科成績表”工作表數(shù)據(jù)

4)單元格數(shù)據(jù)的移動

在如圖4-38所示的“各科成績表”工作表中，將各列成績的排列順序調(diào)整為“大學(xué)英語”、“計算機應(yīng)用”、“高等數(shù)學(xué)”、“應(yīng)用文寫作”，操作步驟如下：

①鼠標移到工作表的最上方，指向F列“列標”按鈕后單擊，選擇F列。

②執(zhí)行“編輯”—“剪切”命令。

③選定D1單元格。

④執(zhí)行“插入”—“剪切單元格”命令。成績列數(shù)據(jù)的排列順序調(diào)整為“大學(xué)英語”、“計算機應(yīng)用”、“應(yīng)用文寫作”、“高等數(shù)學(xué)”。⑤選擇G列數(shù)據(jù)“高等數(shù)學(xué)”，將鼠標指針移至選定區(qū)域的邊框，按住Shift鍵的同時，拖動該區(qū)域到目標位置(F：F)后，先釋放鼠標，再放開Shift鍵，也可以實現(xiàn)插入移動數(shù)據(jù)的功能。此時各列成績的排列順序調(diào)整為“大學(xué)英語”、“計算機應(yīng)用”、“高等數(shù)學(xué)”、“應(yīng)用文寫作”。

5)函數(shù)及單元格引用

Excel函數(shù)是一種預(yù)定義的內(nèi)置公式，它使用一些稱為參數(shù)的特定數(shù)值按特定的順序或結(jié)構(gòu)進行計算，然后返回結(jié)果。使用函數(shù)可以簡化和縮短工作表中的公式，特別適用于執(zhí)行復(fù)雜計算的表格。

在“各科成績表”工作表中，增加“總分”列，計算每位學(xué)生的總分，操作步驟如下：

①選擇單元格H1，在H1單元格中輸入“總分”。

②選擇單元格G1，單擊“格式刷”按鈕，當鼠標變?yōu)闀r，單擊H1單元格，將單元格G1的格式復(fù)制到H1中。③選擇目標單元格H2，單擊“常用”工具欄中的“自動求和”按鈕，單元格中出現(xiàn)了求和函數(shù)SUM，Excel自動選擇了范圍D2：G2，在單元格下方還會有函數(shù)的輸入格式提示，如圖4-39所示，按Enter鍵或單擊“輸入”按鈕確認。H2單元格中顯示出計算結(jié)果。

④鼠標指向H2單元格右下角的填充柄，當鼠標指針變成時，雙擊填充柄。圖4-39自動求和過程說明：

求和是表格中一種最常用的數(shù)據(jù)運算，因此Excel提供了快捷的自動求和方法，就是使用“常用”工具欄中的“自動求和”按鈕。它將自動地對活動單元格上方或左側(cè)的數(shù)據(jù)進行求和計算。其實，自動求和按鈕對應(yīng)的就是一個SUM函數(shù)。

所有函數(shù)都包含三部分：函數(shù)名、參數(shù)和圓括號，以求和函數(shù)SUM來說明，如圖4-40所示。

SUM：是函數(shù)名稱，從名稱大略可知該函數(shù)的功能及用途是求和。圖4-40函數(shù)說明

圓括號：用來括起參數(shù)，在函數(shù)中圓括號是不可以省略的。

參數(shù)：是函數(shù)在計算時所必須使用的數(shù)據(jù)。函數(shù)的參數(shù)不僅可以是數(shù)字類型，它還可以是字符型、邏輯值或是單元格引用，如：SUM(B1，C3)，SUM(D2：G2)。

在“各科成績表”工作表中，增加“名次”列，計算每位學(xué)生的總分排名，操作步驟如下：

①選擇單元格I1，在I1單元格中輸入“名次”。用“格式刷”復(fù)制單元格G1的格式到I1中。

②選擇目標單元格I2，單擊編輯欄左邊的“插入函數(shù)”按鈕，出現(xiàn)“插入函數(shù)”對話框，在“或選擇類別”下拉列表框中，選擇“統(tǒng)計”，如圖4-41所示。

③在“選擇函數(shù)”列表框中，選擇“RANK”函數(shù)，如圖4-42所示。單擊“確定”按鈕。出現(xiàn)“函數(shù)參數(shù)”對話框。

④在“函數(shù)參數(shù)”對話框中，將插入點定位在第一個參數(shù)“Number”處，從工作表中選擇“H2”單元格；再將插入點定位在第二個參數(shù)“Ref”處，從工作表中選擇單元格區(qū)域“H2：H38”，如圖4-43所示。

⑤單擊“確定”按鈕。在I2單元格中返回計算結(jié)果“12”。

⑥鼠標指向I2單元格右下角的填充柄，當鼠標指針變成＋時，雙擊填充柄。圖4-41使用“插入函數(shù)”按鈕插入函數(shù)圖4-42“插入函數(shù)”對話框

圖4-43“函數(shù)參數(shù)”對話框請注意圖4-42中的函數(shù)功能描述和有關(guān)該函數(shù)的幫助。

說明：

RANK函數(shù)是專門進行排定名次的函數(shù)，用于返回一個數(shù)值在一組數(shù)值中的排序，排序時不改變該數(shù)值原來的位置。語法格式為：

RANK(Number，Ref，Order)

共包括三個參數(shù)，其中：

Number為需要找到排位的數(shù)字。

Ref為數(shù)字列表數(shù)組成對數(shù)字列表的引用。Ref中的非數(shù)值型參數(shù)將被忽略。

Order為一數(shù)字，指明排位的方式。如果Order為0或省略，按照降序排列；如果Order不為零，按照升序排列。

函數(shù)RANK對重復(fù)數(shù)的排位相同。但重復(fù)數(shù)的存在將影響后續(xù)數(shù)值的排位。例如，在一列按升序排列的整數(shù)中，如果整數(shù)10出現(xiàn)兩次，其排位為5，則11的排位為7（沒有排位為6的數(shù)值）。

在圖4-44中，檢查“各科成績表”工作表中“名次”列的排名結(jié)果，細心的讀者會發(fā)現(xiàn)存在的問題。雖然RANK函數(shù)允許對重復(fù)數(shù)的排位相同，但工作表中的兩位第1名、兩位第18名以及兩位第25名的分數(shù)顯然不同，為什么他們卻具有相同的排名呢？再檢查最后一位學(xué)生的排名，你會驚訝地發(fā)現(xiàn)，他居然也是第1名。問題究竟出在哪里呢？圖4-44總分排名結(jié)果先選定首位第1名所在目標單元格I5，編輯欄中的公式為“=RANK(H5，H5：H41)”；再選定后一位第1名所在目標單元格I6，編輯欄中的公式為“=RANK(H6,H6:H42)”，如4-45所示。第一個參數(shù)為需要比較的總分所在的單元格，參數(shù)選擇正確；而第二個參數(shù)本來應(yīng)該是所有學(xué)生的總數(shù)單元格區(qū)域“H2：H38”，但為什么到了I5單元格卻變成了“H5：H41”，在I6單元格又變成了“H6：H42”呢？圖4-45兩位第一名的比較這是因為在設(shè)置了目標單元格I2的排名后，使用了填充柄。填充柄的作用就是對公式進行復(fù)制，Excel會自動將粘貼區(qū)域的公式調(diào)整為與該區(qū)域有關(guān)的相對位置。例如：I2中使用的公式為“=RANK(H2，H2：H38)”，表示將同一行左邊一列的總分(H2)，在從同一行左邊一列(H2)開始，到向下37行(H38)結(jié)束的所有學(xué)生的總分(H2：H38)中進行排名，返回該總分在所有總分中的排名結(jié)果；在I5中使用的公式為“=RANK(H5，H5：H41)”，同樣表示將同一行左邊一列的總分(H5)，在從同一行左邊一列(H5)開始，到向下37行(H41)結(jié)束的區(qū)域(H5：H41)中進行排名。這說明一旦公式所在的位置發(fā)生了變化，公式中的單元格也會有相應(yīng)的變化。這就是“相對引用”，是Excel默認的引用方式。在RANK函數(shù)中，第二個參數(shù)的單元格區(qū)域“H2：H38”表示的是所有學(xué)生的總分，它不應(yīng)該隨著單元格的復(fù)制而變化。所以在這個區(qū)域中，不應(yīng)該使用“相對引用”。要保證在復(fù)制公式時單元格區(qū)域“H2：H38”固定不變，就必須使用“絕對引用”。

“絕對引用”總是指向固定的單元格或單元格區(qū)域，無論公式怎么復(fù)制也不會改變引用位置。使用“絕對引用”的方法很簡單，只要在“相對引用”的基礎(chǔ)上，在字母和數(shù)字之前分別加上美元符號“$”就行了。例如：把“H2：H38”改為“$H$2：$H$38”。在“各科成績表”工作表中，修改每位學(xué)生的總分排名，操作步驟如下：

①選擇目標單元格I2。

②激活編輯欄，在函數(shù)輸入格式提示中，單擊“Ref”，選擇對應(yīng)區(qū)域“H2：H38”。

③按F4鍵，將選定區(qū)域切換成絕對引用“$H$2：$H$38”，如圖4-46所示。

④單擊編輯欄中的“輸入”按鈕確認。

⑤鼠標指向I2單元格右下角的填充柄，當鼠標指針變成“”時，雙擊填充柄。仔細觀察排名I2單元格右下角的填充柄，雙擊I5單元格，單元格中顯示的公式為：“=RANK(H5，$H$2：$H$38)”；再雙擊I6單元格，其公式也改為“=RANK(H6,$H$2:$H$38)”。

在RANK函數(shù)中，即使用了相對引用，又使用了絕對引用。由此可以總結(jié)出這樣的規(guī)律：復(fù)制公式時，當公式中使用的單元格引用需要隨著所在位置的不同而改變時，應(yīng)該使用“相對引用”，如：RANK函數(shù)中的第一個參數(shù)，復(fù)制公式后，分別為H2，…H5，H6；當公式中使用的單元格引用不隨所在位置而改變時，應(yīng)該使用“絕對引用”，如：RANK函數(shù)中的第二個參數(shù)，復(fù)制公式后，仍然為“$H$2:$H$38”。圖4-46從“相對引用”到“絕對引用”間的切換小技巧：

輸入單元格地址后，選中該單元格地址，按F4鍵，就可以在相對地址、絕對地址和混合地址之間進行切換。如：輸入H2后，選中“H2”，每按一次F4鍵，改變一次地址表示方法，依次在“H2”—“$H$2”—“H$2”—“$H2”—“H2”之間循環(huán)變化。

說明：

單元格引用是指公式中指明的一個單元格或一組單元格，公式中對單元格的引用分為相對引用、絕對引用和混合引用。①相對引用。用“H2”這樣的方式來引用單元格是相對引用。相對引用是指當公式在復(fù)制或移動時，公式中引用單元格的地址會隨著移動的位置自動改變。例如：在單元格I2中、引用地址為“H2：H38”，將公式復(fù)制到單元格I3時，引用地址自動改變?yōu)椤癏3：H39”。

②絕對引用。在行號和列號前均加上“$”，如：“$H$2”這樣的方式來引用單元格是絕對引用。當公式在復(fù)制或移動時，公式引用單元格的地址不會隨著公式的位置而改變。例如：在單元格I2中，引用地址為“$H$2：$H$38”，將公式復(fù)制到單元格I3時，引用地址仍然為“$H$2：$H$38”。③混合引用。混合引用是指單元格地址中既有相對引用，也有絕對引用。

“$H2”，表示具有絕對列和相對行，當公式在復(fù)制或移動時，保持列不變，而行變化；如：在單元格I2中，引用地址為“H$2”，將公式復(fù)制到單元格J3時，引用地址為“H$3”；

“H$2”表示具有相對列和絕對行，當公式在復(fù)制或移動時，保持行不變，而列變化。如：在單元格I2中，引用地址為“H$2”，將公式復(fù)制到單元格J3時，引用地址為“I$2”。在“各科成績表”工作表中，計算出各門課程的“班級平均分”，操作步驟如下：

①選擇目標單元格A39，輸入文字“班級平均分”。

②選擇目標單元格D39。

③單擊“常用”工具欄中“自動求和”按鈕旁邊的下拉按鈕，從彈出的菜單中選擇“平均值”命令，單元格中出現(xiàn)了求平均值函數(shù)AVERAGE，Excel自動選擇了參數(shù)范圍D33：D38，如圖4-47所示，顯然這個范圍是錯誤的，直接用鼠標在工作表中重新選擇參數(shù)范圍D2：D38，單擊編輯欄中的“輸入”按鈕確認。在D39單元格中顯示出計算結(jié)果。圖4-47快速計算平均數(shù)④鼠標指向D39單元格右下角的填充柄，當鼠標指針變成“”時，向右拖曳至G39單元格。這樣其他三門課程的班級平均分也計算出來了。

在“各科成績表”工作表中，計算出各門課程的“班級最高分”及“班級最低分”，操作步驟如下：

①選擇目標單元格A40，輸入文字“班級最高分”。

②選擇目標單元格D40。

③單擊“常用”工具欄中“自動求和”按鈕旁邊的下拉按鈕，從彈出的菜單中選擇“最大值”命令，直接用鼠標在工作表中選擇參數(shù)范圍D2：D38，此時編輯欄中的函數(shù)為MAX(D2:D38)，單擊編輯欄中的“輸入”按鈕確認。在D40單元格中顯示出計算結(jié)果。④鼠標指向D40單元格右下角的填充柄，當鼠標指針變成“”時，向右拖曳至G40單元格。這樣其他三門課程的班級最高分也計算出來了。

⑤選擇目標單元格A41，輸入文字“班級最低分”。

⑥選擇目標單元格D41。

⑦重復(fù)步驟③、④，只是將下拉按鈕中的“最大值”改為“最小值”(MIN)，就可在D41～G41單元格中顯示出“班級最低分”的計算結(jié)果。如圖4-48所示。圖4-48班級平均分、最高分、最低分計算結(jié)果說明：

求平均值、最大值、最小值以及計數(shù)計算，除了可以使用“自動求和”按鈕完成外，也可以使用“插入函數(shù)”的方法完成?？梢栽凇安迦牒瘮?shù)”對話框“統(tǒng)計”類中分別選擇AVERAGE(平均值)，MAX(最大值)，最小值(MIN)，COUNT(計數(shù))實現(xiàn)。

在如圖4-48所示的“各科成績表”工作表中，將各門課程的“班級平均分”結(jié)果四舍五入,保留2位小數(shù)，操作步驟如下：

①選擇目標單元格D39，此時，在編輯欄中顯示“=AVERAGE(D2：D38)”。

②選中“AVERAGE(D2：D38)”，按組合鍵Ctrl+X，將選定內(nèi)容“剪貼”到剪貼板上。③單擊“常用”工具欄中“自動求和”按鈕旁邊的下拉按鈕，從彈出的菜單中選擇“其他函數(shù)”命令，出現(xiàn)“插入函數(shù)”對話框，在“搜索函數(shù)”處，輸入“四舍五入”，單擊“轉(zhuǎn)到”按鈕，Excel自動搜索相關(guān)函數(shù)，找到后將近似函數(shù)在“選擇函數(shù)”列表框中列出，選擇“ROUND”函數(shù)，如圖4-49所示。單擊“確定”按鈕。

④在“函數(shù)參數(shù)”對話框中，將插入點放置在第一個參數(shù)處，按組合鍵Ctrl+V，將剪貼板中的內(nèi)容粘貼到該處；在第二個參數(shù)處輸入“2”，如圖4-50所示。單擊“確定”按鈕。D39單元格中顯示結(jié)果“69.23”，編輯欄中的公式為“=ROUND(AVERAGE(G2：G38)，2)”。圖4-49搜索四舍五入函數(shù)圖4-50設(shè)置函數(shù)參數(shù)⑤鼠標指向D39單元格右下角的填充柄，當鼠標指針變成“”時，向右拖曳至G39單元格。

說明：

(1)?ROUND函數(shù)返回某個數(shù)字按指定位數(shù)四舍五入后的數(shù)字。

語法格式為：ROUND(Number，Unm_digits)

共包括兩個參數(shù)，其中：

Number：需要進行四舍五入的數(shù)字。

Num_digits：指定的位數(shù)，按此位數(shù)進行四舍五入。

如果Unm_digits大于0，則四舍五入到指定的小數(shù)位。如：ROUND(35.385，1)=35.4如果Unm_digits等于0，則四舍五入到最接近的整數(shù)。如：ROUND(35.385，0)=35

如果Unm_digits小于0，則在小數(shù)點左側(cè)進行四舍五入。如：ROUND(35.385，-1)=40

(2)在對班級平均分進行四舍五入的計算中，使用了一個嵌套函數(shù)。所謂嵌套函數(shù)，就是指在某些情況下，將某個函數(shù)作為另一函數(shù)的參數(shù)使用。例如：在圖4-50中，AVERAGE函數(shù)就被當作ROUND函數(shù)的第一個參數(shù)使用，其意義就是：將平均值的計算結(jié)果進行四舍五入，保留兩位小數(shù)。函數(shù)公式為“=ROUND(AVERAGE(D2：D38)，2)”。也可以直接在D39單元格中輸入該公式。

(3)當嵌套函數(shù)作為參數(shù)使用時，它返回的數(shù)值類型必須與參數(shù)使用的數(shù)值類型相同。例如，如果參數(shù)返回一個TRUE或FALSE值，那么嵌套函數(shù)也必須返回一個TRUE或FALSE值。否則，Excel將顯示“#VALUE！”錯誤值。

(4)公式可包含多達七級的嵌套函數(shù)。當函數(shù)B在函數(shù)A中用作參數(shù)時，函數(shù)B則為第二級函數(shù)。本例中，AVERAGE函數(shù)是第二級函數(shù)，因為它是ROUND函數(shù)的參數(shù)。若在AVERAGE函數(shù)中嵌套函數(shù)則為第三級函數(shù)，以此類推。

注意：

如果選擇一個已包含函數(shù)的單元格后，再次“插入函數(shù)”，即會展開“函數(shù)參數(shù)”對話框用于重新設(shè)置參數(shù)。

3．公式與函數(shù)

在“成績表.xls”工作簿中的工作表“各科成績表”之前插入一張新的工作表，并將新工作表更名為“成績統(tǒng)計表”，并輸入如圖4-51所示的內(nèi)容，操作步驟如下：

①鼠標右鍵單擊“各科成績表”工作表標簽，從快捷菜單中選擇“插入”命令，出現(xiàn)“插入”對話框。

②在“插入”對話框的“常用”選項卡中，選擇“工作表”，單擊“確定”按鈕，在“各科成績表”之前添加一張新的工作表，同時新的工作表為當前工作表。

③選擇新工作表標簽，執(zhí)行“格式”—“工作表”—“重命名”命令，輸入新工作表名“成績統(tǒng)計表”，按Enter鍵確認。

④在“成績統(tǒng)計表”中輸入如圖4-51所示的內(nèi)容。圖4-51成績統(tǒng)計表

1)三維引用

從“各科成績表”中，將四門課程的“班級平均分”、“班級最高分”和“班級最低分”的數(shù)據(jù)引用到“成績統(tǒng)計表”中的相應(yīng)單元格中，操作步驟如下：

①在“成績統(tǒng)計表”中，選擇目標單元格B3，在B3單元格中先輸入“=”。

②再單擊“各科成績表”工作表標簽。

③在“各科成績表”中，單擊“班級平均分”對應(yīng)的單元格D39，如圖4-52所示。圖4-52引用其他工作表中的數(shù)據(jù)④按Enter鍵確認。在“成績統(tǒng)計表”中，B3單元格中的值為“69.23”，而編輯欄中的公式為“=各科成績表！D39”，如圖4-53所示。

⑤鼠標指向B3單元格右下角的填充柄，當鼠標指針變成“”時，向右拖曳至E3單元格。得到四門課程的“班級平均分”。

⑥選擇單元格區(qū)域B3:E3，鼠標指向E3單元格右下角的填充柄，當鼠標指針變成+時，向下拖曳至E5單元格。分別得到四門課程的“班級最高分”和“班級最低分”。圖4-53計算結(jié)果和引用公式說明：

如果要引用同一個工作薄中其他工作表的單元格，可以在單元格地址前面加上工作表名稱，這種引用稱為“三維引用”。三維引用的一般格式為：工作表名！單元格地址。例如：在“成績統(tǒng)計表“中的B3單元格中輸入”=各科成績表！D39“，表明要引用工作表”各科成績表“中的單元格D39的內(nèi)容。

注意：

在“成績統(tǒng)計表”中，能夠利用填充柄復(fù)制四門課程“班級平均分”的前提條件是：在“成績統(tǒng)計表”中，四門課程的排列順序必須與“各科成績表”中四門課程的排序相同。否則將會導(dǎo)致錯誤的結(jié)果。

2)統(tǒng)計函數(shù)COUNT及COUNTA的使用

將“各科成績表”中各門課程的“應(yīng)考人數(shù)”和“參考人數(shù)”的統(tǒng)計結(jié)果放置到“成績統(tǒng)計表”相應(yīng)單元格中，操作步驟如下：

①在“成績統(tǒng)計表”中，選擇目標單元格B6。

②單擊編輯欄左邊的“插入函數(shù)”按鈕，出現(xiàn)“插入函數(shù)”對話框，在“或選擇類別”下拉列表框中，選擇“統(tǒng)計”，在“選擇函數(shù)”列表框中，選擇“COUNTA”函數(shù)，單擊“確定”按鈕。出現(xiàn)“函數(shù)參數(shù)”對話框。③在“函數(shù)參數(shù)”對話框中，將插入點定位在第一個參數(shù)“Valuel”處，刪除此處的默認參數(shù)，再單擊“各科成績表”工作表標簽，在“各科成績表”中，直接用鼠標重新選擇參數(shù)范圍D2：D38，此時編輯欄中的函數(shù)為：=COUNTA(各科成績表！D2：D38)，單擊編輯欄中的“輸入”按鈕確認。在B6單元格中顯示出計算結(jié)果。

④鼠標指向B6單元格右下角的填充柄，當鼠標指針變成“”時，向右拖曳至E6單元格，得到四門課程的“應(yīng)考人數(shù)”。

⑤在“成績統(tǒng)計表”中，選擇目標單元格B7。⑥單擊“常用”工具欄中的旁的下拉按鈕，選擇“計數(shù)”命令。

⑦單擊“各科成績表”工作表標簽，重新選擇參數(shù)范圍D2：D38，此時編輯欄中的函數(shù)為：=COUNT(各科成績表！D2:D38)，按Enter鍵確認。

⑧拖曳B7單元格右下角的填充柄至E7單元格，得到四門課程的“參考人數(shù)”。

說明：

COUNT函數(shù)與COUNTA函數(shù)的功能類似，都能返回指定范圍內(nèi)單元格的個數(shù)。但在使用時它們還是有以下區(qū)別：

①COUNT函數(shù)返回包含數(shù)字以及包含參數(shù)列表中的數(shù)字單元格的個數(shù)。

②COUNTA函數(shù)返回參數(shù)列表中非空值的單元格個數(shù)，單元格的類型不限。

3)條件統(tǒng)計函數(shù)COUNTIF的使用

用COUNTIF函數(shù)將“各科成績表”中各門課程的“缺考人數(shù)”以及各分數(shù)段人數(shù)的統(tǒng)計結(jié)果放置到“成績統(tǒng)計表”相應(yīng)的單元格中，操作步驟如下：

①在“成績統(tǒng)計表”中，選擇目標單元格B8。②單擊編輯欄左邊的“插入函數(shù)”按鈕，出現(xiàn)“插入函數(shù)”對話框，在“或選擇類別”下拉列表框中，選擇“統(tǒng)計”，在“選擇函數(shù)”列表框中，選擇“COUNTIF”函數(shù)，單擊“確定”按鈕。出現(xiàn)“函數(shù)參數(shù)”對話框。

③在“函數(shù)參數(shù)”對話框中，將插入點定位在第一個參數(shù)“Range”處，再單擊“各科成績表”工作表標簽，在“各科成績表”中，選擇參數(shù)范圍D2：D38。將插入點定位在第二個參數(shù)“Criteria”處，輸入統(tǒng)計條件“缺考”，如圖4-54所示，單擊“確定”按鈕。此時編輯欄中的函數(shù)為=COUNTIF(各科成績表！D2：D38，“缺考”)。圖4-54Countif函數(shù)參數(shù)對話框④在“成績統(tǒng)計表”中，選擇目標單元格B9(統(tǒng)計90～100分數(shù)段的人數(shù))。

⑤也可以直接在編輯欄輸入：=COUNTIF(各科成績表！D2：D38，“>=90”)，按Enter鍵確認。

⑥在“成績統(tǒng)計表”中，選擇目標單元格B13。(59以下人數(shù))

⑦在編輯欄輸入：=COUNTIF(各科成績表！D2：D38，“60”)。

⑧在“成績統(tǒng)計表”中，選擇目標單元格B10(80～89人數(shù))。⑨直接在編輯欄處輸入：=COUNTIF(各科成績表！D2:D38，“>=80”)，按Enter鍵確認，檢查統(tǒng)計結(jié)果后發(fā)現(xiàn)其中包含“90～100”的人數(shù)，減去這部分人數(shù)，才能得到“80～89”分的人數(shù)，因此，編輯欄中的公式應(yīng)改為：=COUNTIF(各科成績表！D2：D38，“>=80”)-B9。

⑩在“成績統(tǒng)計表”中，選擇目標單元格B11(70～79人數(shù))。

編輯欄處輸入：=COUNTIF(各科成績表！D2：D38，“>=70”)-B9-B10。

在“成績統(tǒng)計表”中，選擇目標單元格B12(60～90人數(shù))。

編輯欄處輸入：=COUNTIF(各科成績表！D2：D38，“>=60”)-B9-B10-B11。

從“成績統(tǒng)計表”中，選擇單元格區(qū)域“B8：B13”，鼠標指向B13單元格右下的填充柄，當鼠標指針變成“”時，向右拖曳至E13單元格，就可以統(tǒng)計出其他三門課程的缺考人數(shù)及各分數(shù)段人數(shù)。小技巧：

在統(tǒng)計80～89、70～79、60～69這三個分數(shù)段的人數(shù)時，可以雙擊B10單元格，直接輸入：=COUNTIF（各科成績表！D$2：D$38，“>=80”）-COUNTIF（各科成績表！D$2：D$38，“>=90”），按Enter鍵確認后，拖曳B10單元格的填充柄至B11單元格，將公式中第一個條件改為“>=70”，第二個條件改為“>=80”，修改后的公式為：=COUNTIF（各科成績表！D$2：D$38，“>=70”）-COUNTIF（各科成績表！D$2：D$38，“>=80”）。用同樣的方法將B12單元格中的第一個條件改為“>=60”，第二個條件改為“>=70”。請大家思考，在指定單元格區(qū)域中使用混合引用“D$2：D$38”的目的是什么？

說明:

COUNTIF函數(shù)用來統(tǒng)計指定區(qū)域內(nèi)滿足給定條件的單元格數(shù)目。

語法格式：

COUNTIF（Range,Criteria）

其中：Range指定單元格區(qū)域，Criteria表示指定的條件表達式。

條件表達式的形式可以為數(shù)字、表達式或文本。例如，條件可以表示為60、“60”、“>=90”或“缺考”等。

4)公式計算

在“成績統(tǒng)計表”中，計算各門課程的及格率和優(yōu)秀率，操作步驟如下：

①在“成績統(tǒng)計表”中，選擇目標單元格B14(計算及格率)。

②編輯欄處的公式為：=COUNTIF(各科成績表！D$2：D$38，“>=60”)/COUNT(各科成績表！D$2：D$38)。

③單擊“格式”工具欄中的“百分比樣式”按鈕%，將及格率設(shè)置成百分比樣式，單擊“格式”工具欄中的“增加小數(shù)位數(shù)”按鈕，將及格率保留兩位小數(shù)。④拖曳B14單元格的填充柄，將公式復(fù)制到B15單元格(計算優(yōu)秀率)。

⑤在編輯欄處，將公式中的條件由“>=60”改為“>=90”，修改后的公式為：=COUNTIF(各科成績表！D$2：D$38，“>=90”)/COUNT(各科成績表！D$2：D$38)。

⑥選擇單元格區(qū)域“B14：B15”，拖曳B15單元格右下角的填充柄，至E15單元格，統(tǒng)計出其他三門課程的及格率和優(yōu)秀率。

說明：

也可以在B14單元格中輸入公式“=1-B13/B7”來計算及格率（其中：B13為不及格人數(shù)，B7為參考人數(shù)）。在B15單元格中輸入公式“=B9/B7”來計算優(yōu)秀率。（其中：B9為90～100分人數(shù)）。

4．制作“各科等級表”

1)復(fù)制工作表

將“各科成績表”工作表復(fù)制一份，并將復(fù)制后的工作表“各科成績表(2)”更名為“各科等級表”，操作步驟如下：

①鼠標左鍵單擊“各科