用WPS表格完成片區成績統計

2024-08-23 07:20:58

字體：大中小

來源：轉載

供稿：網友

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

　　一、制作所需表格

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

　　1.制作學校甲三個班的成績統計表，如圖1：

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

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

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

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

　　4.因為我們想要了解本校各科各項指數在片區中的排位，所以另外還要制作一張各項指數的統計表。我們依然可以將工作表“學校甲”復制出“片區統計”，將表格調整為圖5樣式制作出“人平分”的統計表，再復制出“及格率”、“優生率”、“差生率”的統計表。

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

　　二、利用數據有效性制作下拉列表

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

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

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

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

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

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

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

　　(未完，2樓繼續)

　　三、利用函數求人平分、及格率、優生率、差生率

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

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

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

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

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

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

　　2.將工作表“學校甲”制作完成。

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

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

　　接下來，再將各班“人平分”、“及格率”、“優生率”、“差生率”四個指數的公式錄入，在這里就會引用到“首頁”求出來的各學科“及格段”、“優生段”、“差生段”的數據了。分別在第一個學科下的四個指數單元格中錄入公式：人平分—— “=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))”，優生率——“=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))”。接著再選中剛才錄入數據的四個單元格，向右拖拽填充，將公式也復制到其它學科的單元格內。接著再將所有學科下“及格率”、“優生率”、“差生率”這三項的單元格選中，通過依次點擊“右鍵”——“設置單元格格式”——“數字”——“百分比”——“確定”，將其設置成百分比(如果設置成百分比后無法正確顯示數據，就將其字號減小)。

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

　　3.制作完成工作表“片區匯總”。

　　首先，按照上述的方法將標題與學科部分的公式錄入完成。

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

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

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

　　四、完成工作表“片區統計”

　　接下來是制作最麻煩的一個工作表“片區統計”。

　　1.還是按前面的方法將標題行完善。

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

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

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

　　“片區統計”完成了，前面所有工作表的數據，都是為得到本表的統計結果服務的。

　　五、完善工作簿“片區成績統計”

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