每次考試結(jié)束都要對學(xué)生的成績進(jìn)行登記和分析,，尤其是班主任不僅要對每個學(xué)生進(jìn)行分析還要檢查各科成績，這是一件非常繁瑣和無奈的事情,。

如果我們用excel做一個適合自己的分析模板,，那么這個工作就會變得非常容易。

今天的模板是根據(jù)七年級一個班主任的要求做的,。由于每個年級的學(xué)科不同,，要求也不一樣，不一定適合普及,。但是,，所用的函數(shù)、公式,、思路都是一樣的,。我在這里和大家分享討論。

這個分?jǐn)?shù)分析模板包括兩個工作表：分?jǐn)?shù)輸入和分?jǐn)?shù)分析,。下圖顯示了分析結(jié)果,，并配有圖表。查詢起來很方便,。

一,、成績輸入

總成績，平均分,，排名加入到輸入表中,，這是這位老師要求的。其實(shí)做不做都無所謂,，因?yàn)榱硪粋€分析表有一個下拉框,，可以按名稱查詢。

一般來說,，它是在源數(shù)據(jù)表中沒有任何操作的純數(shù)據(jù),。

在年級輸入表格中，學(xué)生人數(shù)可以增加到49人?，F(xiàn)在,，一個班不會有超過40個學(xué)生,。

計算總分、平均分和排名的公式如下：

鎖定總分,、平均分,、排名單元格，其他數(shù)字,、名稱,、各學(xué)科分?jǐn)?shù)不需要保護(hù),。然后,，保護(hù)工作表并設(shè)置密碼。

工作表受保護(hù)后,，鎖定單元格的數(shù)據(jù)不能被篡改,，其他單元格可以被修改。

二,、成績分析

該工作表包含三個塊：學(xué)生個人成績的查詢,、各科最高分、平均分,、通過人數(shù),、通過率等。以及各科目成績區(qū)間學(xué)生人數(shù)的查詢,。

分析中進(jìn)行動態(tài)查詢,，使用下拉框選擇查詢項(xiàng)，方便教師操作,。

制作動態(tài)數(shù)據(jù)是重點(diǎn),，主要通過索引函數(shù)來實(shí)現(xiàn)。

這里只演示第一個,，后兩個是一樣的,。你可以自己研究一下，根據(jù)一只貓畫一只老虎,。

1.進(jìn)行單個學(xué)生的成績查詢,。

首先獲取成績輸入表單中的字段：

插入下拉框按鈕并設(shè)置其數(shù)據(jù)源區(qū)域：

【開發(fā)工具】【插入】【下拉框按鈕】，然后按住ALT鍵拖動鼠標(biāo)生成下拉框,。

右鍵單擊下拉框按鈕,，單擊設(shè)置控件格式，選擇數(shù)據(jù)源和單元格鏈接,，最后確認(rèn),。

完成后，下拉框中每選擇一個名稱,，A1單元格中的數(shù)字就會發(fā)生變化,，從而生成一組動態(tài)變化的數(shù)據(jù),。

2個注意點(diǎn)：

(1)點(diǎn)擊下拉框按鈕，用鼠標(biāo)拖動的同時按住ALT鍵,，使下拉框剛好占據(jù)整個網(wǎng)格,，可以是一個網(wǎng)格，也可以是兩個網(wǎng)格,。

(2)因?yàn)樾枰疵Q查詢,，所以數(shù)據(jù)源選擇名稱列。選的時候預(yù)留了49人的牢房,，因?yàn)楹竺婵赡軙黾有峦瑢W(xué),，方便添加。

使用Index函數(shù)關(guān)聯(lián)并應(yīng)用A1單元格,，即上面提到的“控件中的單元格鏈接”,。

index函數(shù)可以根據(jù)找到的位置返回實(shí)際的單元格引用或數(shù)據(jù)。

讓我們看看下圖中index函數(shù)的最基本用法：

=INDEX(A4:A8,，3,，1)，A43360A 8為搜索區(qū)域,，3,，1表示查找第三行第一列的值。

在這個例子中,，看看如何從分?jǐn)?shù)分析表中關(guān)聯(lián)分?jǐn)?shù)輸入表中的數(shù)據(jù),。

=INDEX(分?jǐn)?shù)輸入！A2:A50,，$A$1,，1)，分?jǐn)?shù)輸入的地方,！A2:A50是搜索區(qū)域,，$A$1是index函數(shù)需要查找的“行數(shù)”和控件的單元格鏈接。當(dāng)顯示A1單元格的數(shù)量時,，index函數(shù)將查找行數(shù),。

所以控件名的變化引起A1值的變化，進(jìn)而引起index函數(shù)搜索的行數(shù),。

這樣,，兩者就鏈接成了動態(tài)數(shù)據(jù)。

最后隱藏A1單元格的值,，設(shè)置A1單元格的格式,，輸入""是的，記得不要刪除那一行,。

2.查詢各個學(xué)科的情況,。

各科查詢主要涉及平均分,、最高分、最低分,、及格人數(shù),、及格率，可直接從分?jǐn)?shù)輸入表中取出,。比如用語文平均分,，就用公式：=分?jǐn)?shù)輸入！C52,。語文平均分在成績輸入表的C52單元格中找到,。

給出了其他直接公式：

最高分：=MAX(分?jǐn)?shù)輸入！C2:C50)

最低分：=MIN(分?jǐn)?shù)輸入,！C2:C50)

及格數(shù)：=COUNTIF(成績輸入,！C2:C50，'=90 ')

通過率：=B22/計數(shù)(分?jǐn)?shù)輸入,！C2:C50)

右邊的動態(tài)圖表查詢和上面的一樣，不再演示,。

3.每個科目分?jǐn)?shù)區(qū)間的人數(shù)

用函數(shù)查詢區(qū)間人數(shù)：=頻率(分?jǐn)?shù)輸入,！C2:C31，I27:I29),，其中輸入結(jié)果,！C2:C31是搜索區(qū)域，I27:I29是搜索條件,。

輸入公式時,，先加一個輔助列，寫好條件,，然后選擇單個規(guī)程所有需要區(qū)間的單元格,，再輸入公式，最后按ctrl shift完成,。見下圖,。

注意：輸入公式后，記得按ctrl shift和enter完成,，因?yàn)檫@是一個數(shù)組公式,。

人們常說，適合自己的才是最好的,，別人做的不一定完全符合你的需求,。學(xué)會使用excel根據(jù)自己的需求制作一個簡單的模板，相信你的工作會越來越輕松,。

本例中的方法可能與您想要的函數(shù)不同,，但卻是相似的,。只要掌握方法，其實(shí)并不難,。自己研究吧,！