第5章Excel2010表格處理

《計(jì)算機(jī)應(yīng)用基礎(chǔ)

》課程高等教育出版社HIGHEREDUCATIONPRESS第5章Excel2010表格處理1學(xué)習(xí)目標(biāo)熟悉Excel2010的工作界面掌握工作簿、工作表及單元格的基本操作掌握公式和函數(shù)的應(yīng)用掌握對(duì)數(shù)據(jù)進(jìn)行排序、篩選、分析和匯總操作掌握?qǐng)D表的創(chuàng)建與美化掌握數(shù)據(jù)透視表的操作掌握工作表的打印、預(yù)覽2本章任務(wù)32電子表格的基礎(chǔ)操作122數(shù)據(jù)統(tǒng)計(jì)23圖表制作24數(shù)據(jù)管理與分析5.1電子表格基本操作4任務(wù)描述

本任務(wù)將制作一個(gè)“考勤表”，效果如圖5-1所示。通過(guò)該任務(wù)，介紹Excel2010中工作簿、工作表和單元格的基本操作，數(shù)據(jù)輸入和格式化等內(nèi)容。圖5-1“考勤表”效果圖相關(guān)知識(shí)與技能1.工作界面介紹Excel2010工作界面與Word2010工作界面有很多共同之處，如圖5-2所示。5名稱欄編輯欄工作表標(biāo)簽水平拆分條垂直拆分條全選按鈕活動(dòng)單元格列標(biāo)行標(biāo)相關(guān)知識(shí)與技能2.工作簿的基本操作工作簿，是處理和存儲(chǔ)數(shù)據(jù)的Excel2010文件，工作表是記錄和分析數(shù)據(jù)的工具。一個(gè)工作簿可以包含多個(gè)工作表，一個(gè)工作表由多個(gè)單元格組成。工作簿的基本操作包括創(chuàng)建、保存、打開和關(guān)閉工作簿。6相關(guān)知識(shí)與技能3.工作表的基本操作工作表是Excel2010存儲(chǔ)和處理數(shù)據(jù)的最重要的部分，其中包含排列成行和列的單元格。它是工作簿的一部分，也稱電子表格。使用工作表可以對(duì)數(shù)據(jù)進(jìn)行存儲(chǔ)和分析。工作表的基本操作包括創(chuàng)建、重命名、移動(dòng)和復(fù)制工作表等。4.單元格、行和列的基本操作單元格的操作：包括選擇、復(fù)制與粘貼和插入與合并但與昂。7相關(guān)知識(shí)與技能行和列的基本操作：行和列的基本操作包括，插入、移動(dòng)、復(fù)制、刪除行和列，調(diào)整行高和列寬等。5.輸入數(shù)據(jù)Excel2010提供了12種數(shù)據(jù)類型（常規(guī)、數(shù)值、貨幣、分?jǐn)?shù)、文本、時(shí)間、日期等），最常用的是文本、數(shù)值、時(shí)間、日期、邏輯類型的數(shù)據(jù)。輸入數(shù)據(jù)包括基本輸入數(shù)據(jù)和自動(dòng)填充數(shù)據(jù)。8相關(guān)知識(shí)與技能6.設(shè)置格式在Excel2010工作表中輸入數(shù)據(jù)后，還需要進(jìn)一步對(duì)工作表進(jìn)行格式化操作，如突出顯示某些特定含義的單元格或者數(shù)據(jù)，使工作表更易于閱讀。（1）對(duì)單元格格式的設(shè)置主要包括數(shù)字格式、對(duì)齊方式、字體、單元格邊框和填充等。（2）自動(dòng)套用單元格樣式使用單元格樣式可以快速地為單元格或區(qū)域設(shè)置完全相同的格式，包括字體格式、數(shù)字格式、對(duì)齊方式、邊框和底紋等。9相關(guān)知識(shí)與技能（3）設(shè)置條件格式條件格式是Excel2010中一個(gè)非常強(qiáng)大的功能，使用條件格式可以快速標(biāo)識(shí)符合指定條件的一組數(shù)據(jù)，當(dāng)修改區(qū)域中的數(shù)據(jù)后，Excel2010會(huì)自動(dòng)檢測(cè)新數(shù)據(jù)并重新應(yīng)用條件格式規(guī)則。如可以將區(qū)域中所有大于60的單元格背景填充為藍(lán)色。Excel2010增強(qiáng)了條件格式的功能，提供了大量直接可用的內(nèi)置條件格式選項(xiàng)。10任務(wù)實(shí)現(xiàn)（1）建立工作簿新建Excel2010工作簿“考勤表”。01啟動(dòng)Excel2010。02單擊快速訪問(wèn)工具欄中的【保存】按鈕

，在【另存為】對(duì)話框中，將文件名改為“考勤表.xlsx”，單擊【保存】按鈕，將文件保存到個(gè)人文件夾中。11

任務(wù)實(shí)現(xiàn)（2）輸入數(shù)據(jù)在工作表“Sheet1”中，輸入如圖5-1所示的標(biāo)題及表頭數(shù)據(jù)。01在Sheet1工作表中，選中A1單元格，輸入標(biāo)題“計(jì)算機(jī)工程技術(shù)學(xué)院學(xué)生考勤信息”，按【Enter】鍵。02在A2單元格中輸入“班級(jí)編號(hào):G080001和日期：2013-5-8”。03在A3：G3區(qū)域，依次輸入“序號(hào)、學(xué)號(hào)、姓名、性別，遲到、曠課、請(qǐng)假”等列標(biāo)題。04在A4、A5單元格分別輸入“1”和“2”，然后選中A4：A5區(qū)域，鼠標(biāo)指向填充柄，當(dāng)鼠標(biāo)變成黑十字時(shí)，按住鼠標(biāo)左鍵向下拖動(dòng)填充柄，直至目標(biāo)單元格釋放鼠標(biāo)，如圖5-44所示。12

任務(wù)實(shí)現(xiàn)（2）輸入數(shù)據(jù)在工作表“Sheet1”中，輸入如圖5-1所示的標(biāo)題及表頭數(shù)據(jù)。01在Sheet1工作表中，選中A1單元格，輸入標(biāo)題“計(jì)算機(jī)工程技術(shù)學(xué)院學(xué)生考勤信息”，按【Enter】鍵。02在A2單元格中輸入“班級(jí)編號(hào):G080001和日期：2013-5-8”。03在A3：G3區(qū)域，依次輸入“序號(hào)、學(xué)號(hào)、姓名、性別，遲到、曠課、請(qǐng)假”等列標(biāo)題。04在A4、A5單元格分別輸入“1”和“2”，然后選中A4：A5區(qū)域，鼠標(biāo)指向填充柄，當(dāng)鼠標(biāo)變成黑十字時(shí)，按住鼠標(biāo)左鍵向下拖動(dòng)填充柄，直至目標(biāo)單元格釋放鼠標(biāo)，如圖5-44所示。13

任務(wù)實(shí)現(xiàn)05在B4單元格中輸入第1個(gè)學(xué)生的學(xué)號(hào)“'0102080101”，按【Enter】鍵，然后再次選中B4單元格，使用填充柄向下拖動(dòng)，輸入其他學(xué)生的學(xué)號(hào)。輸入“姓名”列01選中C4單元格，在C4單元格中輸入“蔡喜珊”，按【Enter】鍵。02在C5單元格中，輸入“陳聚將”按【Enter】鍵。03用同樣的方法依次輸入其他姓名。14

任務(wù)實(shí)現(xiàn)輸入“性別”列01先使用填充柄，將所有學(xué)生的性別都輸入“男”。02按下【Ctrl】鍵的同時(shí)選中性別為“女”的單元格，輸入一個(gè)“女”，同時(shí)按【Ctrl+Enter】組合鍵，那么選中的所有單元格中都輸入“女”。輸入“遲到”、“曠課”、“請(qǐng)假”列01選中E4：G66區(qū)域，此時(shí)活動(dòng)單元格為E4。02輸入“0”，按【Tab】鍵，活動(dòng)單元格移到F4，輸入“1”，再次按【Tab】鍵，在G4單元格中輸入“0”；再次按Tab鍵，活動(dòng)單元格自動(dòng)移到下一行E5，等待輸入數(shù)據(jù)，如圖5-45所示。15

任務(wù)實(shí)現(xiàn)03輸入所有考勤數(shù)據(jù)，用鼠標(biāo)單擊任何單元格，關(guān)閉選定單元格區(qū)域。（3）單元格設(shè)置將A1：G1區(qū)域合并后居中，并且應(yīng)用單元格樣式“標(biāo)題1”；將A2：G2區(qū)域合并后居中，并設(shè)置字體格式為“楷體，10號(hào)”。01選中A1：G1區(qū)域。02單擊【開始】選項(xiàng)卡【對(duì)齊方式】組中的【合并后居中】按鈕，然后單擊【開始】選項(xiàng)卡【樣式】組中的【單元格樣式】按鈕

，在彈出的下拉列表中，選擇【標(biāo)題1】樣式。16

任務(wù)實(shí)現(xiàn)選中A2：G2區(qū)域，單擊【合并后居中】按鈕

，然后單擊【開始】選項(xiàng)卡【字體】組中的【字體】列表框，從中選擇【楷體】項(xiàng)；單擊【字號(hào)】列表框，選擇【10】項(xiàng)。為A3：G66區(qū)域套用表格格式“表樣式淺色16”，設(shè)置水平對(duì)齊方式為“居中”，垂直對(duì)齊方式為“垂直居中”。01選中A3：G66區(qū)域，單擊【開始】選項(xiàng)卡【樣式】組中的【套用表格格式】按鈕

，從彈出的下拉列表中，選擇【表樣式淺色16】樣式；02單擊【數(shù)據(jù)】選項(xiàng)卡【排序和篩選】組中的【篩選】按鈕，關(guān)閉單元格篩選。17

任務(wù)實(shí)現(xiàn)03單擊【開始】選項(xiàng)卡【對(duì)齊方式】組中的【居中】按鈕

，設(shè)置選中區(qū)域水平方向居中對(duì)齊；單擊【對(duì)齊方式】組中的【垂直居中】按鈕

，設(shè)置選中區(qū)域垂直方向居中對(duì)齊。E4：G66區(qū)域，利用條件格式，將該區(qū)域單元格內(nèi)數(shù)值大于0的突出顯示為“綠填充色深綠色文本”。01選中E4：G66區(qū)域。02單擊【開始】選項(xiàng)卡【樣式】組中的【條件格式】按鈕

，選擇【突出顯示單元格規(guī)則】子菜單中的【大于】命令，彈出【大于】對(duì)話框，設(shè)置參數(shù)如圖5-46所示。18

任務(wù)實(shí)現(xiàn)（4）工作表更名將當(dāng)前工作表“Sheet1”更名為“考勤表”。01雙擊工作表“Sheet1”的標(biāo)簽。02標(biāo)簽出現(xiàn)反白（黑底白字）時(shí)，輸入新的工作表的名字“考勤表”。03保存工作簿。19

5.2數(shù)據(jù)統(tǒng)計(jì)20任務(wù)描述

本任務(wù)將制作一個(gè)“成績(jī)表”和“成績(jī)分析”表，效果如圖5-47和5-48所示。通過(guò)該任務(wù)，介紹Excel2010中公式和函數(shù)的使用。圖5-47“成績(jī)表”效果圖圖5-48“成績(jī)分析”表效果圖相關(guān)知識(shí)與技能1.使用公式（1）基本概念在Excel2010中使用公式可以對(duì)單元格中的數(shù)據(jù)進(jìn)行加、減、乘、除等運(yùn)算，也可以對(duì)工作表中的數(shù)據(jù)進(jìn)行更加復(fù)雜的運(yùn)算。（2）編輯公式可以在單元格中輸入公式，同時(shí)還可以對(duì)公式進(jìn)行修改、刪除等操作。21

相關(guān)知識(shí)與技能（3）公式中的錯(cuò)誤信息在進(jìn)行公式計(jì)算的時(shí)候，很可能由于公式的錯(cuò)誤導(dǎo)致計(jì)算結(jié)果出現(xiàn)某些意外的數(shù)值，常見錯(cuò)誤信息如表5-1所示。22

錯(cuò)誤值錯(cuò)誤的原因#####單元格的列寬不夠，或者是使用了負(fù)的日期或負(fù)的時(shí)間#DIV/0！除數(shù)為0#N/A函數(shù)或公式中沒有可用的數(shù)值#NAME？Excel2010不能識(shí)別公式中的文本#NULL使用了不正確的單元格或單元格區(qū)域進(jìn)行運(yùn)算#NUM！公式或函數(shù)中使用了無(wú)效數(shù)字#REP單元格引用無(wú)效#VALUE！使用的參數(shù)或操作數(shù)類型錯(cuò)誤相關(guān)知識(shí)與技能2.使用函數(shù)函數(shù)是Excel2010預(yù)定義的內(nèi)置公式，與直接使用公式進(jìn)行計(jì)算相比較，使用函數(shù)進(jìn)行計(jì)算的速度更快，同時(shí)也減少了輸入上的錯(cuò)誤。例如，求單元格E3到I3中的數(shù)字之和，如果用公式計(jì)算是“＝E3+F3+G3+H3+I3”，而用函數(shù)可以簡(jiǎn)化為“＝SUM（E3：I3）”。（1）函數(shù)的輸入函數(shù)的輸入方法有兩種，使用函數(shù)向?qū)л斎牒褪止ぽ斎搿?3

相關(guān)知識(shí)與技能（2）常用函數(shù)Excel2010內(nèi)置的工作表函數(shù)包括財(cái)務(wù)函數(shù)、日期與時(shí)間函數(shù)、數(shù)學(xué)與三角函數(shù)、統(tǒng)計(jì)函數(shù)、數(shù)據(jù)庫(kù)函數(shù)、外部函數(shù)、工程函數(shù)、邏輯函數(shù)、文本與數(shù)據(jù)函數(shù)等。24

任務(wù)實(shí)現(xiàn)打開“第5章Excel2010表格處理\5.2數(shù)據(jù)統(tǒng)計(jì)\學(xué)生成績(jī)統(tǒng)計(jì)與分析(素材).xlsx”文件，將該工作簿另存為“學(xué)生成績(jī)統(tǒng)計(jì)與分析.xlsx”。（1）求平均分計(jì)算每位學(xué)生的平均分01選擇“成績(jī)表”工作表。02選中J3單元格，單擊【公式】選項(xiàng)卡【函數(shù)庫(kù)】組中的【自動(dòng)求和】按鈕

右邊的下三角按鈕

，在彈出的下拉列表中，選擇【平均值】命令，如圖5-55所示。此時(shí)單元格中出現(xiàn)了求平均值函數(shù)AVERAGE，并自動(dòng)選擇了E3：I3區(qū)域，如圖5-56所示，按【Enter】鍵或單擊編輯欄左側(cè)【輸入】按鈕

，確認(rèn)輸入，J3單元格中顯示出第一位學(xué)生的平均值。

任務(wù)實(shí)現(xiàn)

圖5-55常用函數(shù)列表圖5-56求平均值03單擊【開始】選項(xiàng)卡【數(shù)字】組中的【常規(guī)】列表框，在下拉列表中選擇【數(shù)字】項(xiàng)，然后單擊【減少小數(shù)位數(shù)】按鈕

，設(shè)置平均分不顯示小數(shù)。04選中J3單格，拖動(dòng)填充柄向下移動(dòng)到J47，計(jì)算出所有學(xué)生的平均分。任務(wù)實(shí)現(xiàn)（2）求總分計(jì)算每位學(xué)生的總分。01選中K3單元格。02單擊【公式】選項(xiàng)卡【函數(shù)庫(kù)】組中的【自動(dòng)求和】按鈕

，單元格中出現(xiàn)了SUM函數(shù)，并選擇了范圍E3：J3，如圖5-57所示，顯然這個(gè)參數(shù)是錯(cuò)誤的，重新修改參數(shù)為“E3：I3”，按【Enter】鍵，計(jì)算出第1位學(xué)生的總分。03拖動(dòng)填充柄向下移動(dòng)到K47，計(jì)算出所有學(xué)生的總分。

圖5-57求和函數(shù)任務(wù)實(shí)現(xiàn)（3）求排名計(jì)算每位學(xué)生的總分排名。01選中L3單元格。02單擊編輯欄左側(cè)的【插入函數(shù)】按鈕

，彈出【插入函數(shù)】對(duì)話框，在【搜索函數(shù)】框中輸入“rank”，單擊【轉(zhuǎn)到】按鈕，Excel2010自動(dòng)搜索相關(guān)函數(shù)，如圖5-58所示。選擇“RANK”函數(shù)，單擊【確定】按鈕，打開【函數(shù)參數(shù)】對(duì)話框。

任務(wù)實(shí)現(xiàn)03定位到第一個(gè)參數(shù)【Number】框，選擇K3單元格；定位到第二個(gè)參數(shù)【Ref】框，選擇區(qū)域“K3：K47”，如圖5-59所示。

圖5-59【函數(shù)參數(shù)】對(duì)話框任務(wù)實(shí)現(xiàn)05單擊【確定】按鈕，在L3單元格中，計(jì)算出第1個(gè)學(xué)生的排名。雙擊L3單元格中的填充柄，計(jì)算出所有學(xué)生的排名。至此，發(fā)現(xiàn)學(xué)生排名出錯(cuò)，出現(xiàn)多個(gè)第1名，如圖5-60所示。這是因?yàn)?，在用填充柄?duì)函數(shù)進(jìn)行復(fù)制時(shí)，函數(shù)里面的參數(shù)會(huì)隨函數(shù)位置的變化而變化，如L3單元格中的函數(shù)為“=RANK(K3,K3:K47)”，填充柄復(fù)制到L47單元格，函數(shù)變?yōu)椤?RANK(K47,K47:K91)”，這就是相對(duì)引用。

任務(wù)實(shí)現(xiàn)修改每位學(xué)生的總分排名。01選中L3單元格。02在編輯欄中，選中第二個(gè)參數(shù)“K3:K47”，按【F4】鍵，將第二個(gè)參數(shù)變成“$K$3:$K$47”，單擊編輯欄左側(cè)的【輸入】按鈕

確認(rèn)修改，再雙擊L3單元格中的填充柄，計(jì)算出所有學(xué)生的排名。計(jì)算出每位學(xué)生的平均分所對(duì)應(yīng)的等級(jí)。要求：平均分在90分以上，相應(yīng)單元格顯示“優(yōu)秀”，平均分在80~90之間，相應(yīng)單元格顯示“良好”，平均分在60~80之間，相應(yīng)單元格顯示“一般”，否則，顯示“不及格”。

任務(wù)實(shí)現(xiàn)01選中M3單元格。02單擊【公式】選項(xiàng)卡【函數(shù)庫(kù)】組中的【邏輯】按鈕

，在彈出的下拉列表中，選擇【IF】命令，打開【函數(shù)參數(shù)】對(duì)話框，參數(shù)設(shè)置如圖5-61所示。

圖5-61IF函數(shù)任務(wù)實(shí)現(xiàn)03將光標(biāo)定位到【Value_if_false】參數(shù)中，在編輯欄的左邊函數(shù)下拉框中選擇IF函數(shù)，再次打開【函數(shù)參數(shù)】對(duì)話框，同圖5-61所示的函數(shù)參數(shù)對(duì)話框中進(jìn)行相應(yīng)的設(shè)置。04單擊【確定】按鈕，此時(shí)編輯欄中顯示“=IF(J3>=90,"優(yōu)秀",IF(J3>=80,"良好",IF(K3>=60,"一般","不及格")))”公式，按【Enter】鍵確認(rèn)輸入，在M3單元格中顯示相應(yīng)結(jié)果，雙擊填充柄，每個(gè)學(xué)生的等級(jí)就顯示出來(lái)了。

任務(wù)實(shí)現(xiàn)（4）求最高分計(jì)算每門課程的最高分01選中E48單元格。02單擊【公式】選項(xiàng)卡【函數(shù)庫(kù)】組中的【自動(dòng)求和】按鈕

右邊的下三角按鈕

，在彈出的下拉列表中，選擇【最大值】命令，并修改參數(shù)為“E3:E47”，此時(shí)，編輯欄中顯示“=MAX(E3:E47)”，按【Enter】鍵，求出最大值。03向右拖動(dòng)填充柄至I48單元格，求出其他課程的最高分。

任務(wù)實(shí)現(xiàn)（5）求最低分計(jì)算每門課程的最低分01選中E49單元格。02單擊【公式】選項(xiàng)卡【函數(shù)庫(kù)】組中的【自動(dòng)求和】按鈕

右邊的下三角箭頭

，在彈出的下拉列表中，選擇【最小值】命令，并修改參數(shù)為“E3:E47”，此時(shí)，編輯欄中顯示“=MIN(E3:E47)”，按【Enter】鍵，求出最小值。03再次選中E49，向右拖動(dòng)填充柄至I49單元格，求出其他科目的最低分。

任務(wù)實(shí)現(xiàn)（6）二維引用在計(jì)算過(guò)程中，有時(shí)所引用的數(shù)據(jù)不在當(dāng)前工作表中，而在同一個(gè)工作簿的另外一個(gè)工作表中。此時(shí)，可以在引用的地址前面加上工作表的名稱，即“工作表名！地址”，該種引用方式稱為二維引用。從“成績(jī)表”中，將5門課程的最高分和最低分引用到“成績(jī)分析”表01選擇“成績(jī)分析”工作表。02選中B3單元格，并輸入“=”，再單擊“成績(jī)表”工作表標(biāo)簽。

任務(wù)實(shí)現(xiàn)03在“成績(jī)表”中，單擊“程序設(shè)計(jì)”課程最高分對(duì)應(yīng)的單元格“E48”，此時(shí)編輯欄中顯示“=成績(jī)表!E48”。04按【Enter】鍵，返回到“成績(jī)分析”表。05向右拖動(dòng)填充柄至F3單元格，得出其他課程的最高分。06按照相同的方法，得出其他課程的最低分。（7）求應(yīng)考人數(shù)和參考人數(shù)在“成績(jī)分析”表中，計(jì)算出各門課程的應(yīng)考人數(shù)和參考人數(shù)。01選中B5單元格。

任務(wù)實(shí)現(xiàn)02單擊【公式】選項(xiàng)卡【函數(shù)庫(kù)】組中的【其它函數(shù)】按鈕

，彈出【其他函數(shù)】下拉列表，在【統(tǒng)計(jì)】子菜單中選擇【COUNTA】命令，如圖5-64所示。

圖5-64【其他函數(shù)】下拉列表框任務(wù)實(shí)現(xiàn)03彈出【函數(shù)參數(shù)】對(duì)話框，其參數(shù)設(shè)置如圖5-65所示，此時(shí)編輯欄內(nèi)顯示“=COUNTA(成績(jī)表!E3:E47)”。

圖5-65COUNTA參數(shù)設(shè)置任務(wù)實(shí)現(xiàn)04單擊【確定】按鈕，再按【Enter】鍵確認(rèn)輸；使用填充句柄，得出其他課程的應(yīng)考人數(shù)。05選中B6單元格，單擊【統(tǒng)計(jì)】子列表中【COUNT】命令。06在【函數(shù)參數(shù)】對(duì)話框中，定位到第一個(gè)參數(shù)【Value1】處，刪除默認(rèn)參數(shù)，再單擊“成績(jī)表”標(biāo)簽，重新選擇區(qū)域“E3：E47”，此時(shí)編輯欄內(nèi)顯示“=COUNT(成績(jī)表!E3:E47)”，按【Enter】鍵確定輸入。07拖動(dòng)填充柄至F6，計(jì)算出其他科目的參考人數(shù)。

任務(wù)實(shí)現(xiàn)（8）求缺考人數(shù)在“成績(jī)分析”表中，計(jì)算出各門課程的“缺考人數(shù)”。01選中B7單元格，輸入“=”。02單擊B5單元格，然后輸入“-”，再單擊B6單元格，此時(shí)編輯欄顯示“=B5-B6”。03按【Enter】鍵確認(rèn)輸入，使用填充柄計(jì)算出其他課程的缺考人數(shù)。（9）求各分?jǐn)?shù)段人數(shù)和及格率在“成績(jī)分析”表中，計(jì)算出各門課程的“應(yīng)考人數(shù)”和“參考人數(shù)”。

任務(wù)實(shí)現(xiàn)01選中B5單元格。02單擊【統(tǒng)計(jì)】子列表中的【COUNTIF】命令，打開【函數(shù)參數(shù)】對(duì)話框，參數(shù)設(shè)置如圖5-66所示。04單擊【確定】按鈕，計(jì)算出B5單元格值。使用填充柄計(jì)算出其他科目的相關(guān)數(shù)據(jù)。

圖5-66COUNTIF參數(shù)設(shè)置任務(wù)實(shí)現(xiàn)05選中B9單元格，并在編輯欄中輸入“=COUNTIF(成績(jī)表!E3:E47,>=70")-B8”，按【Enter】鍵確認(rèn)06選中B10單元格，在編輯欄中輸入“=COUNTIF(成績(jī)表!E3:E47,">=60")/B6”，按【Enter】鍵確認(rèn)輸入。07單擊【開始】選項(xiàng)卡【數(shù)字】組中的【百分比樣式】按鈕

，按百分比顯示及格率。08選中B9：B10區(qū)域使用填充柄計(jì)算出其他科目的相關(guān)數(shù)據(jù)。

5.3圖表制作44任務(wù)描述

本任務(wù)將制作一個(gè)“各科各分?jǐn)?shù)段人數(shù)對(duì)比”圖表，效果如圖5-67所示。通過(guò)該任務(wù)，介紹圖表的創(chuàng)建、編輯和格式化等操作。圖5-67“各科各分?jǐn)?shù)段人數(shù)對(duì)比”圖表效果圖相關(guān)知識(shí)與技能1.圖表簡(jiǎn)介圖表可以更好地使所處理的數(shù)據(jù)以更加直觀、生動(dòng)、清晰地顯示不同數(shù)據(jù)間的差異，更好地顯示出發(fā)展趨勢(shì)和分布狀況，便于用戶更好地理解各種數(shù)據(jù)之間的相互關(guān)系，使用戶一目了然地看清數(shù)據(jù)的大小、差異和變化趨勢(shì)。同時(shí)當(dāng)工作表區(qū)域的數(shù)據(jù)發(fā)生變化時(shí)，圖表中對(duì)應(yīng)的數(shù)據(jù)也自動(dòng)更新，可以同步顯示數(shù)據(jù)。Excel2010提供了約100種不同格式的圖表，其中包括二維圖表和三維圖表。45

相關(guān)知識(shí)與技能2.創(chuàng)建圖表Excel2010中圖表有兩種放置方式，一種是嵌入圖，即將數(shù)據(jù)和圖表顯示在一張工作表中；另外一種是將圖表單獨(dú)放置在一張工作表中。3.編輯圖表包括選定圖標(biāo)項(xiàng)、調(diào)整圖表大小和位置、更改圖表類型、編輯圖表數(shù)據(jù)源、交換圖表的行與列、添加和修飾圖表標(biāo)題、設(shè)置坐標(biāo)軸及標(biāo)題、設(shè)置圖表樣式、設(shè)置圖表區(qū)與繪圖區(qū)的格式等操作。46

相關(guān)知識(shí)與技能4.頁(yè)面設(shè)置頁(yè)面設(shè)置包括設(shè)置工作表的打印方向，頁(yè)面寬度和高度，打印紙張的大小和打印頁(yè)碼等。5.打印工作表如果對(duì)預(yù)覽的效果比較滿意，就可以正式打印了。打印時(shí)可設(shè)置打印份數(shù)及頁(yè)數(shù)范圍等。47

任務(wù)實(shí)現(xiàn)（1）再次打開“學(xué)生成績(jī)統(tǒng)計(jì)與分析.xlsx”文件（2）建立圖表01切換到“成績(jī)分析”表。按下【Ctrl】鍵選中A2：F4和A8：F8兩個(gè)數(shù)據(jù)區(qū)域。02單擊【插入】選項(xiàng)卡【圖表】組中的【柱形圖】按鈕，從彈出的下拉菜單中選擇【簇狀圓柱圖】類型，如圖5-85所示。此時(shí)，成績(jī)分析表中插入一個(gè)圖表，效果如圖5-86所示。

圖5-85插入簇狀圓柱圖圖5-86簇狀圓柱圖任務(wù)實(shí)現(xiàn)03選中圖表，單擊【設(shè)計(jì)】選項(xiàng)卡【數(shù)據(jù)】組中的【切換行/列】按鈕，調(diào)整數(shù)據(jù)顯示。04單擊【布局】選項(xiàng)卡【標(biāo)簽】組中的【坐標(biāo)軸標(biāo)題】按鈕，在彈出的菜單中選擇【主要橫坐標(biāo)軸標(biāo)題】子菜單中的【坐標(biāo)軸下方標(biāo)題】命令，在生成的文本框中輸入“科目”；選擇【主要縱坐標(biāo)軸標(biāo)題】子菜單中的【旋轉(zhuǎn)過(guò)的標(biāo)題】命令，在生成的文本框中輸入“人數(shù)”。05單擊【布局】選項(xiàng)卡【標(biāo)簽】組中的【圖表標(biāo)題】按鈕，在彈出的菜單中選擇【圖表上方】命令，在生成的文本框中輸入“各分?jǐn)?shù)段人數(shù)對(duì)比圖”。

任務(wù)實(shí)現(xiàn)（3）調(diào)整圖表的大小和位置。01選中圖表，按下鼠標(biāo)左鍵拖動(dòng)圖表到合適的位置。02選中圖表，將鼠標(biāo)置于圖表控制點(diǎn)上按下鼠標(biāo)左鍵調(diào)整圖表的大小。（4）格式化圖表01選中圖表。02切換到【設(shè)計(jì)】選項(xiàng)卡,單擊【圖表樣式】組中的“樣式30”。03選中圖表標(biāo)題，單擊【開始】選項(xiàng)卡【字體】組中的相應(yīng)按鈕修改標(biāo)題的字符格式為“楷體、18號(hào)、加粗”，最終效果如圖5-67所示。

任務(wù)實(shí)現(xiàn)（5）打印工作表01切換到成績(jī)表。02單擊【頁(yè)面布局】選項(xiàng)卡【頁(yè)面設(shè)置】組中的【頁(yè)面設(shè)置】按鈕

，打開【頁(yè)面設(shè)置】對(duì)話框。03在【頁(yè)面】選項(xiàng)卡的【縮放比例】微調(diào)框中，輸入“85”，設(shè)置打印縮放比例。04切換到【頁(yè)邊距】選項(xiàng)卡，設(shè)置上下邊距為“2”，設(shè)置左右邊距為“1.5”。在【居中方式】區(qū)域選中【水平】復(fù)選框。

任務(wù)實(shí)現(xiàn)05切換到【頁(yè)眉/頁(yè)腳】選項(xiàng)卡，在【頁(yè)腳】列表框中，選擇樣式【第1頁(yè)，共？頁(yè)】項(xiàng)。06切換到【工作表】對(duì)話框，在【頂端標(biāo)題行】文本框中，使用折疊填充柄

選中標(biāo)題行第2行，使該框中的內(nèi)容為“$2:$2”。07單擊【打印預(yù)覽】按鈕，預(yù)覽無(wú)誤后，便可打印。

5.4數(shù)據(jù)管理與分析53任務(wù)描述

本任務(wù)將對(duì)數(shù)據(jù)進(jìn)行管理、分析匯總，效果如圖5-87所示。通過(guò)該任務(wù)，介紹排序、篩選、分類匯總和數(shù)據(jù)透視表等內(nèi)容。圖5-87“數(shù)據(jù)管理與分析“效果圖相關(guān)知識(shí)與技能Excel2010具有一定的數(shù)據(jù)庫(kù)管理功能，通過(guò)它可以方便地對(duì)工作表中的數(shù)據(jù)進(jìn)行排序、篩選、分類匯總等操作。1.排序數(shù)據(jù)排序可以使工作表中的數(shù)據(jù)記錄按照規(guī)定的順序排列，從而使工作表?xiàng)l理清晰。排序分為簡(jiǎn)單排序和多關(guān)鍵字排序。54

相關(guān)知識(shí)與技能2.篩選所謂篩選既是將數(shù)據(jù)區(qū)域中滿足條件的記錄顯示出來(lái)，不滿足條件的記錄隱藏起來(lái)。在Excel2010中，篩選分為自動(dòng)篩選和高級(jí)篩選。（1）自動(dòng)篩選自動(dòng)篩選是指按照某一條件進(jìn)行的數(shù)據(jù)篩選。如在“成績(jī)表”中篩選出“計(jì)算機(jī)”專業(yè)的學(xué)生。55

相關(guān)知識(shí)與技能（2）高級(jí)篩選自動(dòng)篩選可以實(shí)現(xiàn)同字段之間的“或”、“與”運(yùn)算，也可以實(shí)現(xiàn)不同字段之間的“與”運(yùn)算，但是不能實(shí)現(xiàn)多字段之間的“或”運(yùn)算。當(dāng)篩選出滿足字段間條件為“或”關(guān)系的記錄時(shí)，必須使用高級(jí)篩選才能實(shí)現(xiàn)。3.分類匯總分類匯總是按照指定的類別將數(shù)據(jù)以指定的方式進(jìn)行統(tǒng)計(jì)，可以對(duì)表格中的數(shù)據(jù)進(jìn)行匯總分析，獲得想要的統(tǒng)計(jì)數(shù)據(jù)。根據(jù)需要分類匯總分為分類匯總和嵌套分類匯總。56

相關(guān)知識(shí)與技能（1）分類匯總在做分類匯總之前，需先按照某個(gè)關(guān)鍵字排序，使相同關(guān)鍵字的行排在相鄰行中。（2）嵌套分類匯總所謂嵌套分類匯總是指用兩個(gè)或兩個(gè)以上關(guān)鍵字段作為分類字段的多次匯總方式。57

相關(guān)知識(shí)與技能4.建立數(shù)據(jù)透視表數(shù)據(jù)透視表是一種交互式的數(shù)據(jù)透視表，可以對(duì)工作表中的數(shù)據(jù)進(jìn)行匯總、分析，主要用于深入分析數(shù)據(jù)。用戶通過(guò)數(shù)據(jù)透視表對(duì)已有的數(shù)據(jù)進(jìn)行交叉制表和匯總，然后重新發(fā)布并立即計(jì)算出結(jié)果。58

任務(wù)實(shí)現(xiàn)01再次打開文件“學(xué)生成績(jī)統(tǒng)計(jì)與分析.xlsx”。02為“成績(jī)表”創(chuàng)建6個(gè)副本，并分別重命名為“簡(jiǎn)單排序”、“多字段排序”、“自動(dòng)篩選”、“高級(jí)篩選”、“分類匯總”、“嵌套分類匯總”。03按【Shift】鍵同時(shí)選中上述6張工作表，再選中A48：M49，單擊【開始】選項(xiàng)卡【編輯】組中的【清除】按鈕，從彈出的下拉菜單中選擇【全部清除】命令，清除6張表中A48：M49區(qū)域的所有屬性。

任務(wù)實(shí)現(xiàn)（1）準(zhǔn)備工作01再次打開文件“學(xué)生成績(jī)統(tǒng)計(jì)與分析.xlsx”。02為“成績(jī)表”創(chuàng)建6個(gè)副本，并分別重命名為“簡(jiǎn)單排序”、“多字段排序”、“自動(dòng)篩選”、“高級(jí)篩選”、“分類匯總”、“嵌套分類匯總”。03按【Shift】鍵同時(shí)選中上述6張工作表，再選中A48：M49，單擊【開始】選項(xiàng)卡【編輯】組中的【清除】按鈕，從彈出的下拉菜單中選擇【全部清除】命令，清除6張表中A48：M49區(qū)域的所有屬性。

任務(wù)實(shí)現(xiàn)（2）簡(jiǎn)單排序在“簡(jiǎn)單排序”表中，按“性別”升序排序。01切換到“簡(jiǎn)單排序”表中，將鼠標(biāo)光標(biāo)置于C列的任一單元格中。02單擊【開始】選項(xiàng)卡【編輯】組中的【排序和篩選】按鈕，從彈出的列表中，選擇【升序】命令，設(shè)置所有數(shù)據(jù)按性別升序排序。

任務(wù)實(shí)現(xiàn)（3）多關(guān)鍵字排序在“多字段排序”表中，按照班級(jí)升序排序，班級(jí)相同時(shí)按照學(xué)號(hào)升序排序。01切換到“多字段排序”表中，選中A2：M47區(qū)域。02再次單擊【開始】選項(xiàng)卡【編輯】組中的【排序和和篩選】按鈕，在彈出的下拉菜單中，選擇【自定義排序】命令，打開【排序】對(duì)話框，參數(shù)設(shè)置如圖5-104所示。

任務(wù)實(shí)現(xiàn)03單擊【確定】按鈕，彈出【排序提醒】對(duì)話框，如圖5-105所示。04這里選擇默認(rèn)項(xiàng)，單擊【確定】按鈕，完成排序。

圖5-105【排序提醒】提示框任務(wù)實(shí)現(xiàn)（4）自動(dòng)篩選在“自動(dòng)篩選”表中，篩選出姓名以李開頭以峰結(jié)尾，總分在380~450之間，排名前十名的男生。01切換到“自動(dòng)篩選”表中，選中A2：M47區(qū)域，單擊【數(shù)據(jù)】選項(xiàng)卡【排序和篩選】組中的【篩選】按鈕。02單擊“性別”列標(biāo)題旁的下拉箭頭，在彈出的下拉框中只勾選【男】復(fù)選框。03單擊“姓名”列標(biāo)題旁的下拉箭頭，在彈出的下拉框中，選擇【文本篩選】子列表中的【開頭是】命令，彈出【自定義自動(dòng)篩選方式】對(duì)話框，參數(shù)設(shè)置如圖5-106所示。

任務(wù)實(shí)現(xiàn)04單擊“總分”列標(biāo)題旁的下拉箭頭，在彈出的下拉框中，選擇【數(shù)字篩選】子列表中的【介于】命令，再次彈出【自定義自動(dòng)篩選方式】對(duì)話框，參數(shù)設(shè)置如圖5-107所示。05單擊“排名”列標(biāo)題旁的下拉箭頭，在彈出的下拉框中，選擇【數(shù)字篩選】子列表中的【小于或等于】命令，參數(shù)設(shè)置如圖5-108所示。

圖5-107設(shè)置”姓名”字段篩選條件圖5-108設(shè)置“排名”字段篩選條件任務(wù)實(shí)現(xiàn)（5）高級(jí)篩選在“高級(jí)篩選”表中，篩選出動(dòng)畫1班前十名的女生或動(dòng)畫2班前十名的男生。01切換到“高級(jí)篩選”表中。制作篩選條件，按照要求，條件區(qū)域應(yīng)和數(shù)據(jù)區(qū)域隔開1行或者1列，因此在B49：D51區(qū)域輸入篩選條件，如圖5-109所示。

圖5-109條件區(qū)域任務(wù)實(shí)現(xiàn)02將鼠標(biāo)光標(biāo)置于數(shù)據(jù)區(qū)域，單擊【數(shù)據(jù)】選項(xiàng)卡【排序和篩選】組中的【高級(jí)】按鈕

，打開【高級(jí)篩選】對(duì)話框，參數(shù)設(shè)置如圖5-110所示。03單擊【確定】按鈕顯示篩選結(jié)果。

圖5-110【高級(jí)篩選】對(duì)話框任務(wù)實(shí)現(xiàn)（6）分類匯總在“分類匯總”表中，使用分類匯總求出每門課程男女生平均分。01切換到“分類匯總”表。按照“性別”字段排序，“升序”或者是“降序”都可以，目的是要把“性別”相同的記錄放到一起。02選中A2：M47區(qū)域，單擊【數(shù)據(jù)】選項(xiàng)卡【分級(jí)顯示】組中的【分類匯總】按鈕，打開【分類匯總】對(duì)話框，參數(shù)設(shè)置如圖5-111所示。

任務(wù)實(shí)現(xiàn)03單擊【確定】按鈕。并設(shè)置計(jì)算出的平均值只顯示兩位小數(shù)。04單擊工作