每次考試結束，都要對學生的成績進行登記、分析，尤其是班主任不僅要分析每個學生還要查看各學科的成績，這是一件非常繁瑣和無可奈何的事情。

如果我們用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依自己的需求做一個簡單模板，相信你的工作回越來越輕松。