每次考試結束,都要對學生的成績進行登記、分析,尤其是班主任不僅要分析每個學生還要查看各學科的成績,這是一件非常繁瑣和無可奈何的事情。
如果我們用excel做出一個適合於自己的分析模板來,那麼這項工作就變得非常輕鬆了。
今天分享的這個模板是給一位七年級班主任老師根據他的要求做的,由於各年級學科不同,要求不同、可能不適合大眾化需求,但是用到的函數、公式、思路是一樣的,我在這裡和大家共享,共同探討。
這個成績分析模板包括成績輸入和成績分析兩個工作表,下圖是分析結果,配有圖表,查詢非常方便。
一、成績輸入
成績輸入的這個表中增加了總分、平均分、名次,這是這位老師要求的。其實做不做也沒什麼影響,因為另一個分析表做了下拉框,可以用姓名查詢的功能。
一般情況下,在源數據表中不參加任何運算,就是純數據。
在成績輸入表中,可以增加人數至49,現在一個班不會超過40人.。
求總分、平均分、名次的公式分別如下:
鎖定總分、平均分、名次單元格,其它編號、姓名和各學科成績單元格不需要保護,然後對工作表進行保護,設置密碼即可。
保護工作表後,鎖定單元格的數據不能被篡改,其它單元格可以修改。
二、成績分析
這個工作表中包含三大塊兒:分別是單個學生成績查詢、各學科最高分、平均分、及格人數、及格率等、各學科分數區間人數查詢。
分析中都做了動態查詢,用下拉框來選擇查詢項,方便老師操作。
製作動態變化的數據是重點,主要用index函數來實現。
在這裡只演示第一個,後面兩個做法一樣,可以自己研究一下,照貓畫虎。
1、製作單個學生成績查詢
先把成績輸入表中的字段取過來:
- 插入下拉框按鈕,並設置其數據源區域:
【開發工具】→【插入】→【下拉框按鈕】,然後按住ALT鍵拖動鼠標,即可生成下拉框。
右鍵單擊下拉框按鈕,單擊【設置控件格式】,選擇數據源、單元格鏈接,最後確定。
完成後,在下拉框每選擇一個姓名,A1單元格的數字就會發生變化,這樣就產生了一組動態變化的數據。
注意2點:
(1)點擊下拉框按鈕,用鼠標拖動時按住ALT鍵,這樣做出來的下拉框剛好佔滿整格子,可以是一個格子、也可以是兩個格子。
(2)因為要按姓名查詢,所以數據源選擇姓名列,選擇的時候,預留49人的單元格,因為後面有可能還要加入新同學,方便添加。
- 用Index函數關聯應用A1單元格,即上面提到的“控件中的單元格鏈接”。
index函數可以根據查找到的位置返回實際的單元格引用或數據。
先看看下圖中index函數最基本的用法:
=INDEX(A4:A8,3,1),A4:A8為查找區域,3,1的含義是查找第三行第一列的數值。
再看本例中如何從成績分析表關聯成績輸入表中的數據。
=INDEX(成績輸入!A2:A50,$A$1,1),其中成績輸入!A2:A50為查找區域,$A$1即是index函數需要查找的“行數”又是控件的單元格鏈接。A1單元格顯示幾,index函數就查找第幾行。
因此,控件姓名變化引起A1值變化,A1又引起index函數查找的行數。
這樣二者就被聯繫成動態變化的數據。
最後把A1單元格的數值隱藏掉,設置A1單元格格式,自定義中輸入“;;;”即可,切記不能刪除那一行。
2、各學科情況查詢
各學科情況查詢中主要涉及平均分、最高分、最低分、及格人數、及格率,可以直接從成績輸入表中取過來,例如語文的平均分,就用公式:=成績輸入!C52,成績輸入表的C52單元格中就是求出的語文平均分。
其它的直接給出公式:
最高分:=MAX(成績輸入!C2:C50)
最低分:=MIN(成績輸入!C2:C50)
及格人數:=COUNTIF(成績輸入!C2:C50,">=90")
及格率:=B22/COUNT(成績輸入!C2:C50)
右邊的動態圖表查詢跟上邊的一樣,不再演示。
3、各學科分數區間人數
查詢區間人數用函數:=FREQUENCY(成績輸入!C2:C31,I27:I29),其中成績輸入!C2:C31是查找區域,I27:I29是查找條件。
輸入公式的時候先增加一輔助列,寫好條件,再選中單個學科全部所求區間的單元格,然後輸入公式,最後再按ctrl+shift+回車結束,看下圖。
注意:輸完公式後切記一定按ctrl+shift+回車結束,因為這是數組公式。
常說適合自己的就是最好的,別人做的不一定能完全適合你的需求,學會用excel依自己的需求做一個簡單模板,相信你的工作回越來越輕鬆。
本例中的方法可能與你想要的功能有出入,但是大同小異,只要掌握了方法,其實也不難,自己再研究一下吧!