每次考試結(jié)束，都要對(duì)學(xué)生的成績進(jìn)行登記、分析，尤其是班主任不僅要分析每個(gè)學(xué)生還要查看各學(xué)科的成績，這是一件非常繁瑣和無可奈何的事情。
如果我們用excel做出一個(gè)適合于自己的分析模板來excel表格兩個(gè)表格數(shù)字關(guān)聯(lián)如何設(shè)置，那么這項(xiàng)工作就變得非常輕松了。
今天分享的這個(gè)模板是給一位七年級(jí)班主任老師根據(jù)他的要求做的，由于各年級(jí)學(xué)科不同，要求不同、可能不適合大眾化需求，但是用到的函數(shù)、公式、思路是一樣的，我在這里和大家共享，共同探討。
這個(gè)成績分析模板包括成績輸入和成績分析兩個(gè)工作表，下圖是分析結(jié)果，配有圖表，查詢非常方便。
一、成績輸入
成績輸入的這個(gè)表中增加了總分、平均分、名次，這是這位老師要求的。其實(shí)做不做也沒什么影響，因?yàn)榱硪粋€(gè)分析表做了下拉框，可以用姓名查詢的功能。
一般情況下，在源數(shù)據(jù)表中不參加任何運(yùn)算，就是純數(shù)據(jù)。
在成績輸入表中，可以增加人數(shù)至49，現(xiàn)在一個(gè)班不會(huì)超過40人.。
求總分、平均分、名次的公式分別如下：
鎖定總分、平均分、名次單元格，其它編號(hào)、姓名和各學(xué)科成績單元格不需要保護(hù)，然后對(duì)工作表進(jìn)行保護(hù)，設(shè)置密碼即可。
保護(hù)工作表后，鎖定單元格的數(shù)據(jù)不能被篡改，其它單元格可以修改。
二、成績分析
這個(gè)工作表中包含三大塊兒：分別是單個(gè)學(xué)生成績查詢、各學(xué)科最高分、平均分、及格人數(shù)、及格率等、各學(xué)科分?jǐn)?shù)區(qū)間人數(shù)查詢。
分析中都做了動(dòng)態(tài)查詢，用下拉框來選擇查詢項(xiàng)，方便老師操作。
制作動(dòng)態(tài)變化的數(shù)據(jù)是重點(diǎn)，主要用index函數(shù)來實(shí)現(xiàn)。
在這里只演示第一個(gè)，后面兩個(gè)做法一樣，可以自己研究一下，照貓畫虎。
1、制作單個(gè)學(xué)生成績查詢
先把成績輸入表中的字段取過來：
【開發(fā)工具】→【插入】→【下拉框按鈕】，然后按住ALT鍵拖動(dòng)鼠標(biāo)，即可生成下拉框。
右鍵單擊下拉框按鈕，單擊【設(shè)置控件格式】，選擇數(shù)據(jù)源、單元格鏈接，最后確定。
完成后，在下拉框每選擇一個(gè)姓名，A1單元格的數(shù)字就會(huì)發(fā)生變化，這樣就產(chǎn)生了一組動(dòng)態(tài)變化的數(shù)據(jù)。
注意2點(diǎn)：
（1）點(diǎn)擊下拉框按鈕，用鼠標(biāo)拖動(dòng)時(shí)按住ALT鍵，這樣做出來的下拉框剛好占滿整格子，可以是一個(gè)格子、也可以是兩個(gè)格子。
（2）因?yàn)橐葱彰樵儯詳?shù)據(jù)源選擇姓名列，選擇的時(shí)候，預(yù)留49人的單元格，因?yàn)楹竺嬗锌赡苓€要加入新同學(xué)，方便添加。
index函數(shù)可以根據(jù)查找到的位置返回實(shí)際的單元格引用或數(shù)據(jù)。
先看看下圖中index函數(shù)最基本的用法：
=INDEX(A4:A8,3,1)，A4:A8為查找區(qū)域，3,1的含義是查找第三行第一列的數(shù)值。
再看本例中如何從成績分析表關(guān)聯(lián)成績輸入表中的數(shù)據(jù)。
=INDEX(成績輸入!A2:A50,$A$1,1)，其中成績輸入!A2:A50為查找區(qū)域，$A$1即是index函數(shù)需要查找的“行數(shù)”又是控件的單元格鏈接。A1單元格顯示幾，index函數(shù)就查找第幾行。
因此，控件姓名變化引起A1值變化，A1又引起index函數(shù)查找的行數(shù)。
這樣二者就被聯(lián)系成動(dòng)態(tài)變化的數(shù)據(jù)。
最后把A1單元格的數(shù)值隱藏掉，設(shè)置A1單元格格式,自定義中輸入“;;;”即可，切記不能刪除那一行。
2、各學(xué)科情況查詢
各學(xué)科情況查詢中主要涉及平均分、最高分、最低分、及格人數(shù)、及格率，可以直接從成績輸入表中取過來，例如語文的平均分，就用公式：=成績輸入!C52，成績輸入表的C52單元格中就是求出的語文平均分。
其它的直接給出公式：
最高分：=MAX(成績輸入!C2:C50)
最低分：=MIN(成績輸入!C2:C50)
及格人數(shù)：=(成績輸入!C2:C50,">=90")
及格率：=B22/COUNT(成績輸入!C2:C50)
右邊的動(dòng)態(tài)圖表查詢跟上邊的一樣，不再演示。
3、各學(xué)科分?jǐn)?shù)區(qū)間人數(shù)
查詢區(qū)間人數(shù)用函數(shù)：=(成績輸入!C2:C31,I27:I29)，其中成績輸入!C2:C31是查找區(qū)域，I27:I29是查找條件。
輸入公式的時(shí)候先增加一輔助列，寫好條件excel表格兩個(gè)表格數(shù)字關(guān)聯(lián)如何設(shè)置，再選中單個(gè)學(xué)科全部所求區(qū)間的單元格，然后輸入公式，最后再按ctrl+shift+回車結(jié)束，看下圖。
注意：輸完公式后切記一定按ctrl+shift+回車結(jié)束，因?yàn)檫@是數(shù)組公式。
常說適合自己的就是最好的，別人做的不一定能完全適合你的需求，學(xué)會(huì)用excel依自己的需求做一個(gè)簡單模板，相信你的工作回越來越輕松。
本例中的方法可能與你想要的功能有出入，但是大同小異，只要掌握了方法，其實(shí)也不難，自己再研究一下吧！
本文由精彩辦公原創(chuàng)，歡迎關(guān)注，帶你一起長知識(shí)！
（此處已添加圈子卡片，請(qǐng)到今日頭條客戶端查看）