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

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

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

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

一,、成績(jī)輸入

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

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

在年級(jí)輸入表格中,，學(xué)生人數(shù)可以增加到49人。現(xiàn)在,，一個(gè)班不會(huì)有超過(guò)40個(gè)學(xué)生,。

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

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

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

二,、成績(jī)分析

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

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

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

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

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

首先獲取成績(jī)輸入表單中的字段：

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

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

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

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

2個(gè)注意點(diǎn)：

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

(2)因?yàn)樾枰疵Q查詢,，所以數(shù)據(jù)源選擇名稱列,。選的時(shí)候預(yù)留了49人的牢房，因?yàn)楹竺婵赡軙?huì)增加新同學(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表示查找第三行第一列的值,。

在這個(gè)例子中，看看如何從分?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ù)量時(shí)，index函數(shù)將查找行數(shù),。

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

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

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

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

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

給出了其他直接公式：

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

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

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

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

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

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

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

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

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

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

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