用WPS表格完成片區(qū)成績統(tǒng)計

　　我校在對教師進行績效考核中，需要各位教師所教學(xué)科的人平分、及格率、優(yōu)生率、差生率在片區(qū)幾所學(xué)校所有班級中的排位情況，每到期末我的工作量都相當(dāng)大，為了達到一勞永逸的目的，就制作了一個片區(qū)成績統(tǒng)計表。下面就將此表制作的過程作一簡要說明。望這篇文章能起到拋磚引玉的作用，敬請各位同仁指教。

　　一、制作所需表格

　　首先，將需要的工作表制作出來。為了保證各表間數(shù)據(jù)引用方便，利于修改，我們盡量使幾個工作表的樣式、格式一致。為了減少工作量，示例表中我只做了三所學(xué)校(分別是“學(xué)校甲”、“學(xué)校乙”，“學(xué)校丙”)，每所學(xué)校三個班，實際中我們可以根據(jù)實情進行增減，方法都是一樣的。

　　1.制作學(xué)校甲三個班的成績統(tǒng)計表，如圖1：

　　因為現(xiàn)在的學(xué)籍管理要求每個班人數(shù)不得超過70人，所以我就為每個班預(yù)定了70行(圖1為了完整顯示內(nèi)容，隱藏了部分單元格)，再將每個班學(xué)校名稱列和班次列的數(shù)據(jù)錄入。

　　2.將工作表“學(xué)校甲”復(fù)制出工作表“首頁”，在基本不動表格樣式的情況下，做出如圖2所示表格：

　　3.再將工作表“學(xué)校甲”復(fù)制一個工作表“片區(qū)匯總”，將三個班后的分析部分及空行刪除掉(圖3)，

　　再將“學(xué)校甲”三個班的表格復(fù)制兩次到此表中(不要復(fù)制標(biāo)題行，第一次復(fù)制后將“學(xué)校甲”替換為“學(xué)校乙”，第二次復(fù)制后將“學(xué)校甲”替換為“學(xué)校丙”，如圖4)，同樣刪除各班后分析部分及空行。

　　4.因為我們想要了解本校各科各項指數(shù)在片區(qū)中的排位，所以另外還要制作一張各項指數(shù)的統(tǒng)計表。我們依然可以將工作表“學(xué)校甲”復(fù)制出“片區(qū)統(tǒng)計”，將表格調(diào)整為圖5樣式制作出“人平分”的統(tǒng)計表，再復(fù)制出“及格率”、“優(yōu)生率”、“差生率”的統(tǒng)計表。

　　至此，需要的工作表就全制作好了(為了減少工作量，工作表“學(xué)校乙”、“學(xué)校丙”待工作表“學(xué)校甲”所有需要的公式錄入完成后再復(fù)制)。

　　二、利用數(shù)據(jù)有效性制作下拉列表

　　表格是制作出來了，但表格內(nèi)還有很多地方需要填入數(shù)據(jù)，如標(biāo)題行還需要此次檢測的年份、年級、期段，成績欄還需要顯示各學(xué)科名稱等，為了使工作簿能多次使用，我們可以利用數(shù)據(jù)有效性來制作下拉列表，提供選擇項。

　　首先，在工作表“首頁”任一空白處將年份、年級、期段、學(xué)科的序列錄入。如圖6：

　　接著，選中“首頁”標(biāo)題行中第一個合并的單元格，再點擊菜單欄中的“數(shù)據(jù)”——“有效性”(圖7)，

　　在彈出的對話框“允許”下選擇“序列”(圖8)，

　　在“來源”處輸入年份序列下所有年份的范圍(也可以點擊“來源”處文本框右側(cè)的按鈕后再拖選所有年份的單元格，如圖9)，再點“確定”。

　　這樣，年份的下拉列表就制作完成了(圖10)。

　　用同樣的方法，也將年級、期段、學(xué)科的下拉列表也制作出來(“學(xué)科”的下拉列表可以只做一個再復(fù)制或拖拽填充出來，但前提是在首次輸入學(xué)科序列時，必須在行號、列號前加絕對引用符號“$”，否則，后面的下拉列表就會變)。將所有下拉列表都制作出來后，我們就可以將錄入年份、年級、期段、學(xué)科序列的所在行全部隱藏起來。

　　(未完，2樓繼續(xù))

　　三、利用函數(shù)求人平分、及格率、優(yōu)生率、差生率

　　接下來就將所有表中涉及到的函數(shù)分別進行說明。

　　1.工作表“首頁”中，在“各學(xué)科總分”后的“總分”單元格下用SUM函數(shù)求出所有學(xué)科的總分?jǐn)?shù),在單元格O4中錄入公式：“=SUM(E4:N4)”(其它如“學(xué)校甲”、“片區(qū)匯總”表中“總分”一列都如此，后面就不綴述了)。

　　接著，在“及格分?jǐn)?shù)段”后的單元格內(nèi)求出及格分?jǐn)?shù)段(因為各學(xué)科的總分不確定，所以只能用公式求)，在“及格分?jǐn)?shù)段”后的單元格內(nèi)錄入函數(shù)“=E4*0.6”，再復(fù)制出所有學(xué)科的及格分?jǐn)?shù)段。

　　再接著，在“各科優(yōu)生段”后的單元格內(nèi)求出優(yōu)生分?jǐn)?shù)段(因為我校的各科“優(yōu)生”是指進入全片區(qū)所有學(xué)生前30%的學(xué)生，所以“優(yōu)生段”就是指所有學(xué)生數(shù)的前30%最后一名的分?jǐn)?shù)，例如：片區(qū)某年級共500人，前30%就是150人，那么前第150名的分?jǐn)?shù)就是每個學(xué)科的優(yōu)生段。“各學(xué)科差生段”也類似，只不過改為求后30%第一名的分?jǐn)?shù)為差生段。)，在“各科優(yōu)生段”后第一個單元格內(nèi)錄入公式“=LARGE(片區(qū)匯總!E5:E634,ROUND(COUNT(片區(qū)匯總!E5:E634)*0.3,0))”，這個公式主要是用LARGE函數(shù)求出工作表“片區(qū)匯總”第一個學(xué)科學(xué)生成績的第K個最大值(這個“K”的值就通過COUNT函數(shù)求出“片區(qū)匯總”第一個學(xué)科的總?cè)藬?shù)，再乘以0.3，再用ROUND函數(shù)四舍五入求出的整數(shù)值)，再將這個公式復(fù)制到其它學(xué)科。

　　最后，用SMALL函數(shù)求出“各學(xué)科差生段”，第一個學(xué)科的公式是：“=SMALL(片區(qū)匯總!E6:E634,ROUND(COUNT(片區(qū)匯總!E6:E634)*0.3,0))”，這個公式是用SMALL函數(shù)求出工作表“片區(qū)匯總”第一個學(xué)科學(xué)生成績的第K個最小值(這個“K”的值與上面的 “K”值相同)，再復(fù)制出其它學(xué)科的差生段公式。

　　這樣，工作表“首頁”就完全制作成功了(圖11)，這個表中的及格段、優(yōu)生段、差生段數(shù)據(jù)將作為其它工作表引用的基礎(chǔ)。

　　2.將工作表“學(xué)校甲”制作完成。

　　首先，將標(biāo)題行完善，在第一個合并的單元格中錄入公式“=IF(首頁!$D$1="","",首頁!$D$1)”(公式中的if函數(shù)是為了在表格無數(shù)據(jù)時使該單元格也顯示為空白，純屬美觀需要，并不是必須的，如果只要正確求得數(shù)據(jù)，錄入“=首頁!$D$1”就可以了，本文IF函數(shù)的作用都如此)，在第二個合并的單元格中錄入公式“=IF(首頁!$F$1="","",首頁!$F$1)”，在第三個合并的單元格中錄入公式“=IF(首頁!$I$1="","",首頁!$I$1)”，這樣，“首頁”標(biāo)題選擇了什么年份、年級、期段，“學(xué)校甲”就會顯示相同的內(nèi)容了。

　　接著，用同樣的方法將學(xué)科名稱也與“首頁”同步，為了保證拖拽復(fù)制的準(zhǔn)確，在錄入公式時，就不加絕對引用符號：“=IF(首頁!E3="","",首頁!E3)”。

　　接下來，再將各班“人平分”、“及格率”、“優(yōu)生率”、“差生率”四個指數(shù)的公式錄入，在這里就會引用到“首頁”求出來的各學(xué)科“及格段”、“優(yōu)生段”、“差生段”的數(shù)據(jù)了。分別在第一個學(xué)科下的四個指數(shù)單元格中錄入公式：人平分—— “=IF(ISERROR(AVERAGE(E5:E74)),"",AVERAGE(E5:E74))”，及格率—— “=IF(ISERROR(COUNTIF(E5:E74,">="&首頁!E$5)/COUNTA(E5:E74)),"",COUNTIF(E5:E74,">="&首頁!E$5) /COUNTA(E5:E74))”，優(yōu)生率——“=IF(ISERROR(COUNTIF(E5:E74,">="&首頁!E$6) /COUNTA(E5:E74)),"",COUNTIF(E5:E74,">="&首頁!E$6)/COUNTA(E5:E74))”，差生率——“=IF(ISERROR(COUNTIF(E5:E74,">="&首頁!E$7)/COUNTA(E5:E74)),"",COUNTIF(E5:E74,">="&首頁!E$7) /COUNTA(E5:E74))”。接著再選中剛才錄入數(shù)據(jù)的四個單元格，向右拖拽填充，將公式也復(fù)制到其它學(xué)科的單元格內(nèi)。接著再將所有學(xué)科下“及格率”、“優(yōu)生率”、“差生率”這三項的單元格選中，通過依次點擊“右鍵”——“設(shè)置單元格格式”——“數(shù)字”——“百分比”——“確定”，將其設(shè)置成百分比(如果設(shè)置成百分比后無法正確顯示數(shù)據(jù)，就將其字號減小)。

　　最后，再選中1班四個指數(shù)項的所有單元格，將其復(fù)制到2班、3班。工作表“學(xué)校甲”制作就算完成了(圖12)。

　　3.制作完成工作表“片區(qū)匯總”。

　　首先，按照上述的方法將標(biāo)題與學(xué)科部分的公式錄入完成。

　　為了減少工作量，讓各班分?jǐn)?shù)只錄入一次，可以利用公式將各班的分?jǐn)?shù)引用到“片區(qū)匯總”中來。在1班第一個學(xué)生的第一個學(xué)科成績單元格內(nèi)錄入公式：“=IF(學(xué)校甲!E5="","",學(xué)校甲!E5)”，再拖拽復(fù)制出1班所有學(xué)生各科成績的公式。用同樣的方法我們依次將2班、3班的公式錄入。

　　最后，我們將工作表“學(xué)校甲”復(fù)制出工作表“學(xué)校乙”、工作表“學(xué)校丙”，再按照上面的方法也將學(xué)校乙、學(xué)校丙各班學(xué)生的成績公式錄入。

　　至此，工作表“片區(qū)匯總”也制作完成了(圖13)。

　　四、完成工作表“片區(qū)統(tǒng)計”

　　接下來是制作最麻煩的一個工作表“片區(qū)統(tǒng)計”。

　　1.還是按前面的方法將標(biāo)題行完善。

　　2.將“學(xué)科”行也按前面的方法錄入公式，但這里要注意的是：我們要將各班的某個統(tǒng)計指數(shù)排位，所以，在錄入各學(xué)科名稱的引用公式時，要隔一列錄入一個學(xué)科名稱引用公式。在第一個學(xué)科后的那個單元格錄入公式：“=IF(C4="","","名次")”，這樣，當(dāng)?shù)谝粋�學(xué)科顯示學(xué)科名稱時，該單元格就會顯示“名次”二字，否則就顯示空白，再將這個公式復(fù)制到每個學(xué)科后的單元格內(nèi)。按照同樣的方法，分別將“及格率”、“優(yōu)生率”、“差生率”的“學(xué)科”、“名次”的公式也錄入(因為這個表中列數(shù)太多，為了方便公式的錄入，可以將“學(xué)科”列或“名次”列的填充上顏色)。

　　3.接下來是最麻煩的一步——引用各班的各項指數(shù)，這就不能復(fù)制了，必須得一個單元格一個單元格的錄入公式。例如，在“人平分”項，“學(xué)校甲1 班”第一個學(xué)科單元格中錄入公式：“=學(xué)校甲!E76”，這個公式表示該單元格的數(shù)據(jù)引用工作表“學(xué)校甲”E76單元格的數(shù)據(jù)，工作表“學(xué)校甲”E76單元格就是學(xué)校甲1班第一個學(xué)科的人平分。

　　4.最后，利用RANK函數(shù)求出各項指數(shù)各班各學(xué)科片區(qū)排位——這也是我們最終想要得到的數(shù)據(jù)。在“人平分”指數(shù)項“學(xué)校甲1班”第一個學(xué)科后的 “名次”列錄入公式：“=IF(ISERROR(RANK(C5,C$5:C$13)),"",RANK(C5,C$5:C$13))”(這個公式的意思是：如果用RANK函數(shù)求單元格C5相對于C5至C13的降序排位的結(jié)果是錯誤的——ISERROR函數(shù)就是檢測一個值是否錯誤，此單元格就顯示為空白，否則就顯示用RANK函數(shù)求單元格C5相對于C5至C13的降序排位的結(jié)果)，再拖拽復(fù)制公式到C13單元格，再選中C5：C13后復(fù)制公式到“人平分” 指數(shù)項其它學(xué)科后的“名次”列。再按上述方法將“及格率”、“優(yōu)生率”、“差生率”的名次排位公式錄入(圖14)。

　　“片區(qū)統(tǒng)計”完成了，前面所有工作表的數(shù)據(jù)，都是為得到本表的統(tǒng)計結(jié)果服務(wù)的。

　　五、完善工作簿“片區(qū)成績統(tǒng)計”

　　到此，工作簿“片區(qū)成績統(tǒng)計”已經(jīng)基本完成了，但是，為了防止工作表的格式以及公式不小心被修改或刪掉，可以將以后不需編輯的單元格保護起來。在以后的使用過程中，實際只需要對“首頁”中檢測的年份、年級、期段、學(xué)科名稱、各學(xué)科總分以及各班學(xué)生的考號、姓名、各科成績進行錄入，所以，可以分別將 “首頁”及各校統(tǒng)計表中需要錄入數(shù)據(jù)的單元格選中，再點擊“工具”——“保護”——“允許用戶編輯區(qū)域”(圖15)

　　——“新建”(圖16)

　　——“確定”(圖17)

　　——“保護工作表”(圖18)

　　——輸入密碼后點“確定”，再輸入一次密碼點“確定”(圖19)。

　　這樣，“片區(qū)成績統(tǒng)計”工作簿就算完全制作成功了。最后，將選中工作表“首頁”中“年份”單元格，再將本工作簿保存為模板，以備后用。

　　附件：片區(qū)成績統(tǒng)計示例表.xls 密碼：123。

片區(qū)成績統(tǒng)計示例表.xls
片區(qū)成績統(tǒng)計示例表.xlt