學(xué)生成績分析報告

學(xué)生成績分析報告Office應(yīng)用:學(xué)生成績分析報告學(xué)習(xí)目的:1.掌握Excel中圖表的操作方法

2.掌握Excel中IF函數(shù)的使用方法知識要點:1.Excel中篩選的使用方法

2.Excel中條件格式的使用方法

3.Excel中分類匯總的使用方法技能要點：

1.圖表分析

2.數(shù)據(jù)管理:學(xué)生成績分析報告教學(xué)重點：

圖表操作數(shù)據(jù)篩選條件格式分類匯總教學(xué)難點：

數(shù)據(jù)管理和分析的意義

教學(xué)內(nèi)容一、任務(wù)綜述：教務(wù)員張三要根據(jù)李四的成績統(tǒng)計結(jié)果寫一份成績分析報告，除了李四提供的基本統(tǒng)計數(shù)據(jù)外，在分析中還需要每門課程各分?jǐn)?shù)段成績分布圖、全科合格（五門成績都及格）的學(xué)生清單、有一門及以上課程成績不及格的學(xué)生清單、成績不及格加紅色標(biāo)記和90分以上加綠色標(biāo)記四方面的素材。

教學(xué)內(nèi)容二、任務(wù)實施1.利用Excel圖表功能，繪制每門課程各分?jǐn)?shù)段成績分布圖，效果圖如下所示。

教學(xué)內(nèi)容①先打開包含所需統(tǒng)計信息的數(shù)據(jù)表②選擇要進(jìn)行數(shù)據(jù)分析的數(shù)據(jù)區(qū)域，橫坐標(biāo)、縱坐標(biāo)③單擊“常用”工具欄中的按鈕，彈出如圖5.1所示的“圖表向?qū)?4步驟之1-圖表類型”對話框。選擇“柱形圖”，在“子圖表類型”選項區(qū)域中選擇“簇狀柱形圖”選項，然后單擊“下一步”按鈕，彈出如圖5.2所示

教學(xué)內(nèi)容

④單擊“系列”標(biāo)簽切換到“系列”選項卡，如圖5.3所示。⑤在“系列”選項卡中單擊“添加”按鈕，在對應(yīng)的“名稱”輸入框中輸入“＝"社會"”，在“值”輸入框中輸入“=Sheet1!$D$143:$D$146”；同理，繼續(xù)按以上操作步驟，添加其他4門課程的“名稱”和“值”。在“分類（X）軸標(biāo)志”輸入框中輸入“=Sheet1!$A$143:$A$146”后單擊“下一步”按鈕，彈出如圖5.4所示教學(xué)內(nèi)容⑥單擊“標(biāo)題”標(biāo)簽切換到“標(biāo)題”選項卡，如圖5.5所示。⑦在“標(biāo)題”選項卡內(nèi)根據(jù)提示，輸入圖表標(biāo)題內(nèi)容、分類（X）軸內(nèi)容、數(shù)值（Y）軸內(nèi)容，然后單擊“下一步”按鈕，彈出如圖5.6所示。⑧單擊“完成”按鈕，即可完成本任務(wù)所需的“分?jǐn)?shù)段人數(shù)分布柱形圖”。

實踐內(nèi)容（1）要求：

①根據(jù)任務(wù)4提供的數(shù)據(jù)表-成績分析表.xls中已經(jīng)劃分的各分?jǐn)?shù)段學(xué)生人數(shù)，要求進(jìn)行如下操作：思考：

如果用課程名稱作為橫坐標(biāo)，分?jǐn)?shù)段劃分作為圖例，那么應(yīng)該如何操作？教學(xué)內(nèi)容2．制作全科成績合格與至少有一門課程成績不及格的學(xué)生清單（方法1）

提示：篩選出全科成績都合格的學(xué)生，剩余的學(xué)生就是至少有一門課程成績不及格的學(xué)生。

建議：為了不破壞原始數(shù)據(jù)，建議不要直接在“Sheet1”工作表中進(jìn)行，而在另一張工作表“Sheet2”中進(jìn)行操作。

完成兩表間的有效數(shù)據(jù)復(fù)制教學(xué)內(nèi)容①在備份的“Sheet2”工作表中，單擊單元格A3，執(zhí)行菜單欄中的“數(shù)據(jù)”→“篩選”→“自動篩選”命令，在表欄目中出現(xiàn)“自動篩選”后的工作表，如圖5.8所示。教學(xué)內(nèi)容②單擊下拉列表中的“自定義”選項，彈出“自定義自動篩選方式”對話框，如圖5.9所示。③在“自定義自動篩選方式”對話框中，通過單擊左邊輸入框的下拉按鈕，在下拉列表中選擇“大于或等于”，再在右邊輸入框中輸入“60”，單擊“確定”按鈕，然后將其余四門課程對應(yīng)的篩選用同樣方式輸入完畢。教學(xué)內(nèi)容觀察：注意最左側(cè)的行號，顏色是藍(lán)色的，而且行號是不連續(xù)的，說明這已經(jīng)是篩選的結(jié)果。

教學(xué)內(nèi)容④篩選出來的記錄建議放在另一張表中。選定經(jīng)篩選后的記錄和表頭，單擊“復(fù)制”按鈕，切換到“Sheet3”工作表，在單元格A1處單擊“粘貼”按鈕，就將五門課程都及格的學(xué)生清單復(fù)制了過來。然后修改表頭內(nèi)容為“××學(xué)校初三段課程全部及格學(xué)生一覽表”，如圖5.11所示。

教學(xué)內(nèi)容⑤回到上述的“Sheet2”工作表中，選定篩選后的數(shù)據(jù)行（不包括表頭和表標(biāo)題），也就是五門課程都及格的學(xué)生數(shù)據(jù)行，執(zhí)行菜單欄中的“編輯”→“刪除行”命令，此時會發(fā)現(xiàn)表中除了表頭和表標(biāo)題外，沒有數(shù)據(jù)行了，接下來執(zhí)行菜單欄中的“數(shù)據(jù)”→“篩選”→“自動篩選”命令，即可恢復(fù)至正常數(shù)據(jù)狀態(tài)。此時，數(shù)據(jù)表上的內(nèi)容就是至少有一門課程不及格的學(xué)生記錄。思考：

分析“××學(xué)校初三段課程全部及格學(xué)生一覽表”和“××學(xué)校初三段至少有一門不及格學(xué)生一覽表”中的班名次和段名次，是否有別？原因在哪里？

教學(xué)內(nèi)容3．制作全科成績合格與至少有一門課程成績不及格的學(xué)生清單（方法2）方法1采用了“自動篩選”功能

方法2采用“高級篩選”功能①執(zhí)行菜單欄中的“數(shù)據(jù)”→“篩選”→“高級篩選”命令，在空的區(qū)域創(chuàng)建一個條件區(qū)域。比如選定第149行，將“D3:H3”內(nèi)容復(fù)制，在單元格D149，單擊“粘貼”按鈕，在149行對應(yīng)的課程下輸入“<60”內(nèi)容，并相互錯開行數(shù)。教學(xué)內(nèi)容②執(zhí)行菜單欄中的“數(shù)據(jù)”→“篩選”→“高級篩選”命令，在彈出的“高級篩選”對話框中將“方式”設(shè)置為“將篩選結(jié)果復(fù)制到其他位置”，“列表區(qū)域”選擇“A3:L139”，“條件區(qū)域”選擇剛剛建立的“D149:H154”，“復(fù)制到”設(shè)置為“A160”，“A160”即篩選結(jié)果的起始單元格位置，如圖5.14所示。③單擊“確定”按鈕后，就會以“A160”為起始單元格顯示滿足“至少有一門課程不及格”篩選條件的結(jié)果。教學(xué)內(nèi)容

如果要高級篩選全科成績都合格的學(xué)生，只要將條件格式設(shè)為“與”的方式，即在一行中設(shè)置條件即可，如圖5.15所示。

多種篩選條件同時成立的情況與或

多種篩選條件滿足其一成立的情況

各篩選條件

并排羅列

各篩選條件錯行羅列

多種篩選條件同時成立的情況與或

各篩選條件

并排羅列

各篩選條件錯行羅列

多種篩選條件滿足其一成立的情況

多種篩選條件同時成立的情況與或

各篩選條件

并排羅列

各篩選條件錯行羅列

實踐內(nèi)容（2）要求：

①根據(jù)提供的數(shù)據(jù)表-成績分析表.xls分別進(jìn)行自動篩選和高級篩選，求得全科成績合格與至少有一門課程成績不及格的學(xué)生清單。

②用自動篩選功能和高級篩選功能求得所有成績都不及格的學(xué)生清單。

③篩選總分小于300分的學(xué)生清單。思考：

如果只要求篩選出語文或數(shù)學(xué)或英語這三門課有一門以上不及格的學(xué)生清單應(yīng)該如何操作？教學(xué)內(nèi)容4．將成績不及格用紅色標(biāo)注，90分（包括90分）以上用綠色標(biāo)注。

成績管理中為了清楚地分辨學(xué)生成績的高低，于是用不同顏色給不及格者和90分以上者進(jìn)行區(qū)分，利用“條件格式”就可以輕松完成。①先復(fù)制一份有效數(shù)據(jù)到一張空表中，選定單元格區(qū)域D4:H139，執(zhí)行菜單欄中的“格式”→“條件格式”命令，系統(tǒng)彈出“條件格式”對話框，根據(jù)成績管理的要求在“條件1”中設(shè)置“單元格數(shù)值小于60”，單擊“格式”按鈕，彈出“單元格格式”對話框，在“顏色”位置選擇“紅色”，在“字形”位置選擇“加粗”，單擊“確定”按鈕回到“條件格式”對話框，完成對不及格成績的條件設(shè)定。教學(xué)內(nèi)容②單擊“添加”按鈕，出現(xiàn)“條件2”，繼續(xù)在“條件2”中設(shè)置“單元格數(shù)值大于或等于90”，單擊“格式”按鈕，彈出“單元格格式”對話框，在“顏色”位置選擇“綠色”，在“字形”位置選擇“加粗”，單擊“確定”按鈕回到“條件格式”對話框，完成對90分以上成績的條件設(shè)定，如圖5.17所示。③再單擊“確定”按鈕，即可在“Sheet3”工作表中，將選定單元格區(qū)域D4:H139內(nèi)的數(shù)據(jù)按不及格分?jǐn)?shù)用紅色粗體顯示，90分（包括90分）以上用綠色粗體顯示。

實踐內(nèi)容（3）要求：

①根據(jù)提供的數(shù)據(jù)表-成績分析表.xls進(jìn)行“條件格式”

設(shè)定，90分以上綠色，75分-90之間藍(lán)色，60分以下紅色。思考：

如何清除已設(shè)置的條件格式？教學(xué)內(nèi)容5．各班各門課程平均值統(tǒng)計

要求對“Sheet1”工作表中的數(shù)據(jù)進(jìn)行分類匯總，求出初三（1）、初三（2）、初三（3）三個班級社會、語文、數(shù)學(xué)、科學(xué)、英語五門課程的班級平均分。小技巧：用鼠標(biāo)單擊任一數(shù)據(jù)單元格，按Ctrl+Shift+8組合鍵，即可選中整個數(shù)據(jù)清單①選中整個數(shù)據(jù)清單，然后執(zhí)行菜單欄中的“數(shù)據(jù)”→“分類匯總”命令，系統(tǒng)彈出“分類匯總”對話框，如圖5.18所示

②按照要求需各個班級進(jìn)行如下設(shè)置：“分類字段”設(shè)置為“班級”；“匯總方式”設(shè)置為“平均值”；“選定匯總項”列表框中勾選社會、語文、數(shù)學(xué)、科學(xué)、英語五門課程對應(yīng)的復(fù)選框，如圖5.19所示

教學(xué)內(nèi)容教學(xué)內(nèi)容③單擊“確定”按鈕，即可求出初三（1）、初三（2）、初三（3）各個班級，社會、語文、數(shù)學(xué)、科學(xué)、英語五門課程的班級平均分，結(jié)果如圖5.20所示。

實踐內(nèi)容（4）要求：

①根據(jù)提供的數(shù)據(jù)表-成績分析表.xls進(jìn)行分類匯總，求出初三（1）、初三（2）、初三（3）三個班級社會、語文、數(shù)學(xué)、科學(xué)、英語五門課程的班級平均分。

②根據(jù)提供的數(shù)據(jù)表-成績分析表.xls進(jìn)行分類匯總，分別求出初三（1）、初三（2）、初三（3）三個班級社會、語文、數(shù)學(xué)、科學(xué)、英語五門課程的班級最高分和最低分。思考：

如何清除已設(shè)置的分類匯總？教學(xué)內(nèi)容6．將每位學(xué)生的平均分成績分等級

按學(xué)校要求對初三年級的三個班級進(jìn)行平均分成績分等級統(tǒng)計管理，規(guī)定將學(xué)生平均分的百分制轉(zhuǎn)換成不同的等級。有多種不同的劃分方法，這里要求在原數(shù)據(jù)清單中增加“M3”單元格并輸入“等級”，將J列學(xué)生的平均分從百分制的分?jǐn)?shù)轉(zhuǎn)換成M列的五個等級A（90～100分）B（80～89分）C（70～79分）D（60～69分）E（低于60分）

解決這類分級問題用邏輯判斷函數(shù)IF即可教學(xué)內(nèi)容

如果簡單地將成績分成兩個等級A（>=60）和B（<60）的話，只需要在指定的單元格如M4中輸入

“=IF(J4>=60,”A“,”B“)”即可。如果分成多個等級，只要將邏輯判斷的條件進(jìn)行嵌套，在M4中輸入“=IF(J4>=90,"A",IF(J4>=80,"B",IF(J4>=70,"C",IF(J4>=60,"D","E"))))”。教學(xué)內(nèi)容

實踐內(nèi)容（5）要求：

①根據(jù)提供的數(shù)據(jù)表-成績分析表.xls中的平均分進(jìn)行等級劃分，將學(xué)生平均分的百分制轉(zhuǎn)換成不同的等級。思考：

如何在嵌套多IF函數(shù)的時候不易出錯？討論內(nèi)容1.Excel圖表主要包含哪幾個要素?2.自動篩選與高級篩選哪個靈活度高?3.If函數(shù)嵌套,嵌套函數(shù)應(yīng)該是“是”還是“否”處？4.分類匯總的前提是什么?

總結(jié)（一）Excel具有強大的圖表自動生成功能，生成圖表的操作操作步驟如下：1）選擇圖表類型及子類。2）選擇圖表源數(shù)據(jù)。3）“標(biāo)題”、“坐標(biāo)軸”、“圖例”、“數(shù)據(jù)標(biāo)志”、“數(shù)據(jù)表”等圖表選項的設(shè)置。4）圖表位置的確定，即圖表是放在原工作表中，還是放在新工作表中。

總結(jié)（二）

在Excel中對數(shù)據(jù)篩選可使用“自動篩選”與“高級篩選”兩種方法。

“自動篩選”可對某列設(shè)置單一篩選條件，也可以用“與”關(guān)系或“或”關(guān)系設(shè)定條件；多列設(shè)置，其列與列條件成“與”的關(guān)系。

“高級篩選”功能，要先設(shè)置條件區(qū)域，條件區(qū)域由標(biāo)題行和條件行組成。

總結(jié)（三）

“條件格式”要做兩項操作：一是設(shè)定條件（表達(dá)式）；二是設(shè)定格式（字體、字號、顏色、下劃線等）。

該功能操作的結(jié)果是將滿足條件的數(shù)據(jù)用指定的格式顯示，如不及格者用“紅色”標(biāo)注，以示醒目