計算機信息技術(shù)基礎(chǔ)（第2版）課件 項目七 電子表格高級處理

電子表格高級處理

——學生成績統(tǒng)計分析項目七本項目主要通過對學生成績進行統(tǒng)計分析，講述如何利用Excel2010進行簡單的公式和常用函數(shù)的計算，并能夠利用圖表功能直觀地展示數(shù)據(jù)的變化趨勢。

項目目標熟練掌握Excel常用函數(shù)的功能并運用。熟練掌握Excel常用公式的形式。熟練掌握Excel圖表的建立和修改。

項目說明

項目實施期末考試結(jié)束了，現(xiàn)得到某班30名學生的思想品德、高等數(shù)學、大學英語和計算機應用四科的考試成績。要求計算出學生各科成績的總分、平均分和名次，并根據(jù)平均分判斷成績所處等級，從而制作出學生成績表，再通過對學生成績表內(nèi)的數(shù)據(jù)進行統(tǒng)計和分析，計算出不及格人數(shù)占總?cè)藬?shù)的比例，從而得到成績統(tǒng)計表。最后根據(jù)成績統(tǒng)計表中的數(shù)據(jù)制成直觀表明各個分數(shù)段人數(shù)的成績統(tǒng)計圖。工作簿的建立任務一新建一個Excel工作簿，以“成績”為文件名，保存到“桌面”。將Sheet1工作表重命名為“學生成績表”，Sheet2工作表重命名為“成績統(tǒng)計表”，Sheet3工作表重命名為“成績統(tǒng)計圖”。1.建立“成績”工作簿（1）啟動Excel。（2）單擊快速訪問工具欄中的【保存】按鈕，在【另存為】對話框中將文件名由“Book1.xls”改為“成績.xls”，在對話框左側(cè)選擇“桌面”選項，單擊對話框中的【保存】按鈕，將文件保存到桌面。2.重命名工作表（1）將鼠標指針移到“Sheet1”工作表的標簽上，單擊右鍵，在彈出的快捷菜單中選擇【重命名】命令，然后輸入新名稱“學生成績表”。（2）將鼠標指針移到“Sheet2”工作表的標簽上，單擊右鍵，在彈出的快捷菜單中選擇【重命名】命令，然后輸入新名稱“成績統(tǒng)計表”。（3）將鼠標指針移到“Sheet3”工作表的標簽上，單擊右鍵，在彈出的快捷菜單中選擇【重命名】命令，然后輸入新名稱“成績統(tǒng)計圖”。在工作薄中輸入相關(guān)的數(shù)據(jù)任務二1：輸入“學生成績表”中相應數(shù)據(jù)。設置標題格式：字體設置為“仿宋”，字號“18”，“加粗”，標題在“A1:K1”區(qū)域內(nèi)“跨列居中”，標題行行高設置為30。表頭行格式：字體設置為“宋體”，字號“12”，添加“淺綠”底紋，文字對齊設置“水平和垂直居中”。表格外框：外邊框設置為雙細線，內(nèi)邊框設置為單細線。四科成績中所有不及格（小于60）的成績所在單元格底紋設置為“淺黃”。2：輸入“成績統(tǒng)計表”中相應數(shù)據(jù)。設置標題格式：字體設置為“仿宋”，字號“20”，“加粗”，標題在“A1:E1”區(qū)域內(nèi)“跨列居中”。表頭行格式：字體設置為“宋體”，字號“12”，添加“淺黃”底紋，同時為“A3:A9”單元格區(qū)域添加“淺黃”底紋，文字設置“水平居中”。表格所有框線為單細線。1.在“學生成績表”工作表中輸入數(shù)據(jù)

在表格中分別輸入標題行、表頭行、姓名列、性別列和四科成績，如圖7-1所示。

輸入學號列數(shù)據(jù)，鼠標單擊“A3”單元格，在“A3”單元格中輸入學號“2009040101”，按Enter鍵。鼠標指向A3單元格的“填充柄”（位于單元格右下角的小黑塊），此時鼠標指針變?yōu)?/p>

，按住Ctrl鍵的同時，按住鼠標向下拖動填充柄至目標單元格時釋放鼠標。2.設置標題格式（1）選中“A1”單元格，在【開始】選項卡中【字體】組【字體】下拉按鈕選擇“仿宋”，在【字號】下拉按鈕選擇“18”,單擊【加粗】按鈕，進行字體加粗設置。（2）選中“A1:K1”單元格區(qū)域，在所選區(qū)域上右擊，彈出快捷菜單中選擇【設置單元格格式】命令，彈出【設置單元格格式】對話框。單擊【設置單元格格式】對話框中【對齊】標簽，在文本對齊方式中的【水平對齊】下拉列表中選擇“跨列居中”命令。（3）單擊行號“1”，選中標題所在第一行，在【開始】選項卡中，單擊【單元格】組中【格式】下拉按鈕，選【行高】命令，輸入行高為“30”，單擊【確定】按鈕。3.設置表頭格式（1）選中“A2:K2”單元格區(qū)域，在【開始】選項卡中的【字體】組內(nèi)，單擊【字體】下拉按鈕選擇“宋體”，單擊【字號】下拉按鈕選擇“12”。（2）單擊【字體】組中的

按鈕的下拉箭頭，選擇單元格底紋為“淺綠”。（3）在所選區(qū)域上右擊，彈出的快捷菜單中選擇【設置單元格格式】命令，單擊【設置單元格格式】對話框中【對齊】標簽，在【水平對齊】下拉列表中選擇“居中”命令，在【垂直對齊】下拉列表中選擇“居中”命令。4.設置表格邊框線（1）對照圖7-1，選擇“A1:K32”單元格區(qū)域，單擊【字體】組中的下拉按鈕，選擇“所有框線”，為表格設置所有邊框線。（2）在所選區(qū)域上，單擊右鍵，彈出快捷菜單中選擇【設置單元格格式】命令，打開【設置單元格格式】對話框，選擇【邊框】標簽，選擇線條樣式為

，預置處選擇

外邊框，設置好表格外邊框。5.設置條件格式（1）選中單元格區(qū)域“D3:G32”。（2）單擊【樣式】組中的【條件格式】下拉按鈕，選擇“突出顯示單元格規(guī)則”中的“小于”對話框。（3）先在該對話框的

中，設置“單元格數(shù)值”，“小于”，“60”，再單擊“設置為”下拉列表中的“自定義格式”命令，打開“設置單元格格式”中的“填充”標簽，“背景色”顏色選擇“淺黃色”，單擊【確定】按鈕。6.在“成績統(tǒng)計表”工作表中輸入數(shù)據(jù)并設置格式（1）單擊標簽欄的“成績統(tǒng)計表”，在工作表中分別輸入標題行、表頭行、分數(shù)段列的數(shù)據(jù)，如圖7-2所示。（2）選中“A1”單元格，【開始】選項卡中的【字體】組內(nèi)【字體】下拉按鈕選擇“仿宋”，在【字號】下拉按鈕選擇“20”,單擊按鈕，進行字體加粗設置。（3）選中“A1:E1”單元格區(qū)域，在所選區(qū)域上右擊，彈出快捷菜單中選擇【設置單元格格式】命令，彈出【設置單元格格式】對話框。單擊【設置單元格格式】對話框中【對齊】標簽，在文本對齊方式中的【水平對齊】下拉列表中選擇“跨列居中”命令。（4）選中“A2:E2”單元格區(qū)域，選擇【開始】選項卡中的【字體】組，在【字體】下拉按鈕中選擇“宋體”，在【字號】下拉按鈕中選擇“12”。（5）按住鍵盤上的Ctrl鍵的同時，選中“A3:A9”單元格區(qū)域，單擊【開始】選項卡中的【字體】組中的按鈕的下拉箭頭，選擇單元格底紋為“淺黃”。（6）對照圖7-2，選擇“A2:E9”單元格區(qū)域，單擊【開始】選項卡中的【字體】組內(nèi)下拉按鈕，選擇“所有框線”，為表格設置所有邊框線。使用常用函數(shù)進行計算任務三使用計算“學生成績表”中“總分”、“平均分”、“等級”和“名次”列的數(shù)據(jù)。計算“成績統(tǒng)計表”中各科“90-100”和“59以下”分數(shù)段人數(shù)。1.“總分”列單元格計算SUM函數(shù)是計算單元格區(qū)域中所有數(shù)值的和，也就是求和函數(shù)。語法格式為：SUM（number1,number2,…）其只有1個參數(shù)，number1，number2，…1到255個待求和的數(shù)值。2.“平均分”列單元格計算AVERAGE函數(shù)是返回其參數(shù)的算術(shù)平均值，即求平均值函數(shù)。語法格式：AVERAGE(number1,number2,…)其只有1個參數(shù)，number1，number2，…用于計算1到255個數(shù)值參數(shù)的平均值。3.“等級”列單元格計算IF函數(shù)是判斷一個條件是否滿足，如果滿足返回一個值，如果不滿足則返回另一個值。語法格式為：IF（logical_test,value_if_true,value_if_false）其共包括3個參數(shù)，其中：logical_test為任何一個可判斷為真或假的條件；value_if_true為當條件為真時返回的值；value_if_false為條件為假時返回的值。4.“名次”列單元格計算RANK函數(shù)是排定名次的函數(shù)，用于返回一個數(shù)值在一組數(shù)值中的排序，排序時不改變該數(shù)值原來的位置。語法格式為：RANK（number,ref,order）其共包括3個參數(shù)，其中：Number為需要排位的數(shù)字；Ref為數(shù)字列表數(shù)組或?qū)?shù)字列表的引用；Order指明排位的方式，如Order值為0或省略，按照降序排列，如果Order值不為0，按照升序排列。5.各科“90-100”和“59以下”分數(shù)段人數(shù)單元格計算COUNTIF函數(shù)的功能是統(tǒng)計指定區(qū)域內(nèi)滿足給定條件的單元格數(shù)目。語法格式：COUNTIF（Range,Criteria）其中：Range指定單元格區(qū)域，Criteria表示指定的條件表達式。使用簡單公式進行計算任務四使用公式計算“成績統(tǒng)計表”中“80-89”、“70-79”和“60-69”分數(shù)段的人數(shù)，計算“總計”和“不及格比例”行的數(shù)據(jù)。常用運算符：算術(shù)運算符：+（加）、－（減）、*（乘）、/（除）、^（乘方）、%（百分號）等。比較運算符：＝（等于）、<（小于）、>（大于）、<＝（小于等于）、>＝（大于等于）、<>（不等于）。引用運算符：冒號“：”（區(qū)域運算符），用于對指定區(qū)域的引用。如“A1：D3”，引用它們之間矩形區(qū)域內(nèi)的數(shù)據(jù)。逗號“，”（聯(lián)合運算符），如“A1，D3”表示引用A1、D3單元格內(nèi)的數(shù)據(jù)。1.各科“80-89”、“70-79”和“60-69”分數(shù)段人數(shù)單元格計算（1）選擇目標單元格“B4（80-89人數(shù)）”，在編輯欄處輸入“=COUNTIF(學生成績表!D3:D32,">=80")-B3”，如圖7-14所示，按Enter鍵確定。（2）選擇目標單元格“B5（70-79人數(shù)）”，在編輯欄處輸入“=COUNTIF(學生成績表!D3:D32,">=70")-B4-B3”，按Enter鍵確定。（3）選擇目標單元格“B6（60-69人數(shù)）”，在編輯欄處輸入“=COUNTIF(學生成績表!D3:D32,">=60")-B3-B4-B5”，按Enter鍵確定。（4）鼠標指針分別指向“B4”、“B5”和“B6”單元格的填充柄，當鼠標指針變?yōu)闀r，單擊鼠標并向右拖動，將以上單元格中的函數(shù)復制到其余三學科所在的單元格中。2.“總計”行單元格計算（1）選擇目標單元格“B8”。（2）單擊【編輯欄】處的【插入函數(shù)】按鈕，彈出【插入函數(shù)】對話框。（3）在打開的【插入函數(shù)】對話框中選擇函數(shù)“SUM”，單擊【確定】按鈕。彈出【函數(shù)參數(shù)】對話框。（4）在打開的【函數(shù)參數(shù)】對話框中，對函數(shù)參數(shù)進行引用，單擊“Number1”輸入框右側(cè)的按鈕，單擊“B3”單元格，并拖動到“B7”單元格，選定這一單元格區(qū)域后，再次單擊按鈕，返回【函數(shù)參數(shù)】對話框，單擊【確定】按鈕。（5）鼠標指針指向“B8”單元格的填充柄，當鼠標指針變?yōu)闀r，單擊鼠標并向右拖動，將“B8”單元格的函數(shù)自動復制到其他單元格中。3.“不及格比例”行單元格計算（1）選擇目標單元格“B9”。（2）在單元格內(nèi)，或者在上方【編輯欄】處輸入“=B7/B8”。（3）按Enter鍵確認。如圖7-15所示。（4）鼠標指針指向“B9”單元格的填充柄，當鼠標指針變?yōu)闀r，單擊鼠標并向右拖動，將“B9”單元格的函數(shù)自動復制到其他單元格中。數(shù)據(jù)圖表化任務五使用“成績統(tǒng)計表”工作表中各分數(shù)段人數(shù)，創(chuàng)建圖表“成績統(tǒng)計圖”。圖表類型為“簇狀柱形圖”，數(shù)據(jù)系列產(chǎn)生在“行”，圖表標題為“成績統(tǒng)計圖”，橫軸（X）軸為“分數(shù)段”，縱軸（Y）軸為“人數(shù)”。修改圖表“成績統(tǒng)計圖”。將圖表類型改為“三維簇狀柱形圖”，將圖表標題設置為“22號，藍色，加粗”，將背景墻的填充效果設置為“藍色面巾紙”。1.合并居中（1）在標簽欄單擊“通訊錄封面”工作表。（2）選中“A2:I2（A2到I2）”單元格區(qū)域，單擊【開始】選項卡【對齊方式】組的【合并后居中】按鈕，對“公司員工通訊錄”進行“合并及居中”操作。2.跨列居中（1）選中“C8:G8”單元格區(qū)域，在所選區(qū)域上右擊鼠標，在彈出的快捷菜單中選擇【設置單元格格式】命令，如圖6-29所示。或者單擊【開始】選項卡【單元格】組【單元格】的【格式】下拉按鈕，選擇【設置單元格格式】命令，均會彈出【單元格格式】對話框。（2）單擊【設置單元格格式】對話框中【對齊】標簽，在文本對齊方式中的【水平對齊】下拉菜單中選擇【跨列居中】命令，將“2016年04月30日制”進行“跨列居中”操作。3.設置單元格底紋（1）單擊【開始】選項卡【字體】組中的下拉按鈕，選擇單元格底紋為“黃色”。（2）或者在所選區(qū)域上右擊，在彈出的快捷菜單中選擇【設置單元格格式】命令，選擇【填充】標簽，設置單元格底紋為“黃色”，4.“通訊錄內(nèi)容”工作表單元格設置（1）在標簽欄單擊“通訊錄內(nèi)容”工作表。（2）選中“A1:G1”單元格區(qū)域，在所選區(qū)域上右擊，在彈出的快捷菜單中選擇【設置單元格格式】命令，單擊【設置單元格格式】對話框中【對齊】標簽，在【水平對齊】下拉菜單中選擇【跨列居中】命令，將“通訊錄”進行“跨列居中”操作。（3）選中“A3:A6”單元格區(qū)域，單擊【開始】選項卡【對齊方式】組的【合并后居中】按鈕，對“總裁室”進行“合并及居中”操作。單擊【開始】選項卡【字體】組中的下拉按鈕，選擇單元格底紋為“淺綠”。（4）選擇“A7:A10”單元格區(qū)域，單擊【開始】選項卡【對齊方式】組的【合并后居中】按鈕，對“產(chǎn)品部”進行“合并及居中”操作，選擇單元格底紋為“黃色”。（5）依次完成“A”列中其余單元格的設置。（6）選擇“B3:G3”單元格區(qū)域，單擊【開始】選項卡【字體】組中的下拉按鈕，選擇單元格底紋為“綠色”。對照圖6-28,利用【Ctrl】鍵，可以同時選中不相鄰的單元格區(qū)域，統(tǒng)一設置，完成其他區(qū)域單元格“橙色”底紋的填充。表格外觀設置任務六設置“通訊錄內(nèi)容”工作表外觀，除A列文字外，所有文字都水平居中，表格邊框效果如圖6-2所示。1.制作成績統(tǒng)計圖（1）在“成績統(tǒng)計表”工作表中，選擇所需的數(shù)據(jù)單元格區(qū)域“A2:E7”。（2）單擊【插入】選項卡中的【圖表】組命令，單擊【柱形圖】下拉按鈕。（3）在【柱形圖】下拉按鈕中選“二維柱形圖”中的“簇狀柱形圖”可得到“成績統(tǒng)計圖”，（4）為圖表添加標題。圖表插入后會自動在選項卡上方出現(xiàn)【圖表工具】，單擊【布局】選項卡，選擇【標簽】組中的【圖表標題】下拉按鈕，選擇“圖表上方”樣式，如圖7-18。雙擊“圖表標題”添加標題為“成績統(tǒng)計圖”，（5）添加橫軸（X）軸，縱軸（Y）標題。單擊【圖表工具】中的【布局】選項卡，選擇【標簽】組中的【坐標軸標題】下拉按鈕，如圖7-20。在【主要橫坐標軸標題】選擇【坐標軸下方標題】，添加標題為“分數(shù)段”。再選擇【主要縱坐標軸標題】，在下拉列表中選擇“豎排標題”添加標題為“人數(shù)”。2.圖表修改（1）改變圖表類型：鼠標指向圖表區(qū)，單擊鼠標右鍵，在彈出的快捷菜單中選擇【更改圖表類型】命令，如圖7-22所示，在【更改圖表類型】對話框中更改為“柱形圖”，子圖表類型為“三維簇狀柱形圖”，單擊【確定】按鈕（2）更改標題字體：在圖表中，選中圖表標題，單擊【開