3分鐘學(xué)會(huì)做數(shù)據(jù)分析表 excel怎樣做數(shù)據(jù)分析表 _怎樣

每次考試結(jié)束，都要對(duì)學(xué)生的成績(jī)進(jìn)行登記、分析，尤其是班主任不僅要分析每個(gè)學(xué)生還要查看各學(xué)科的成績(jī)，這是一件非常繁瑣和無(wú)可奈何的事情。
如果我們用excel做出一個(gè)適合于自己的分析模板來(lái)，那么這項(xiàng)工作就變得非常輕松了。

今天分享的這個(gè)模板是給一位七年級(jí)班主任老師根據(jù)他的要求做的，由于各年級(jí)學(xué)科不同，要求不同、可能不適合大眾化需求，但是用到的函數(shù)、公式、思路是一樣的，我在這里和大家共享，共同探討。
這個(gè)成績(jī)分析模板包括成績(jī)輸入和成績(jī)分析兩個(gè)工作表，下圖是分析結(jié)果，配有圖表，查詢非常方便。

3分鐘學(xué)會(huì)做數(shù)據(jù)分析表 excel怎樣做數(shù)據(jù)分析表

文章插圖
一、成績(jī)輸入成績(jī)輸入的這個(gè)表中增加了總分、平均分、名次，這是這位老師要求的。其實(shí)做不做也沒(méi)什么影響，因?yàn)榱硪粋€(gè)分析表做了下拉框，可以用姓名查詢的功能。
一般情況下，在源數(shù)據(jù)表中不參加任何運(yùn)算，就是純數(shù)據(jù) 。

文章插圖
在成績(jī)輸入表中，可以增加人數(shù)至49，現(xiàn)在一個(gè)班不會(huì)超過(guò)40人. 。
求總分、平均分、名次的公式分別如下：

文章插圖
鎖定總分、平均分、名次單元格，其它編號(hào)、姓名和各學(xué)科成績(jī)單元格不需要保護(hù)，然后對(duì)工作表進(jìn)行保護(hù)，設(shè)置密碼即可。
保護(hù)工作表后，鎖定單元格的數(shù)據(jù)不能被篡改，其它單元格可以修改。

文章插圖
二、成績(jī)分析這個(gè)工作表中包含三大塊兒：分別是單個(gè)學(xué)生成績(jī)查詢、各學(xué)科最高分、平均分、及格人數(shù)、及格率等、各學(xué)科分?jǐn)?shù)區(qū)間人數(shù)查詢。
分析中都做了動(dòng)態(tài)查詢，用下拉框來(lái)選擇查詢項(xiàng)，方便老師操作。
【3分鐘學(xué)會(huì)做數(shù)據(jù)分析表 excel怎樣做數(shù)據(jù)分析表】制作動(dòng)態(tài)變化的數(shù)據(jù)是重點(diǎn)，主要用index函數(shù)來(lái)實(shí)現(xiàn) 。
在這里只演示第一個(gè)，后面兩個(gè)做法一樣，可以自己研究一下，照貓畫(huà)虎。
1、制作單個(gè)學(xué)生成績(jī)查詢

文章插圖
先把成績(jī)輸入表中的字段取過(guò)來(lái)：

文章插圖

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

【開(kāi)發(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鍵，這樣做出來(lái)的下拉框剛好占滿整格子，可以是一個(gè)格子、也可以是兩個(gè)格子。
（2）因?yàn)橐葱彰樵儯詳?shù)據(jù)源選擇姓名列，選擇的時(shí)候，預(yù)留49人的單元格，因?yàn)楹竺嬗锌赡苓€要加入新同學(xué)，方便添加。

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

index函數(shù)可以根據(jù)查找到的位置返回實(shí)際的單元格引用或數(shù)據(jù) 。
先看看下圖中index函數(shù)最基本的用法：
=INDEX(A4:A8,3,1)，A4:A8為查找區(qū)域，3,1的含義是查找第三行第一列的數(shù)值。

文章插圖
再看本例中如何從成績(jī)分析表關(guān)聯(lián)成績(jī)輸入表中的數(shù)據(jù) 。
=INDEX(成績(jī)輸入!A2:A50,$A$1,1)，其中成績(jī)輸入!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ù)、及格率，可以直接從成績(jī)輸入表中取過(guò)來(lái)，例如語(yǔ)文的平均分，就用公式：=成績(jī)輸入!C52，成績(jī)輸入表的C52單元格中就是求出的語(yǔ)文平均分。
其它的直接給出公式：
最高分：=MAX(成績(jī)輸入!C2:C50)
最低分：=MIN(成績(jī)輸入!C2:C50)
及格人數(shù)：=COUNTIF(成績(jī)輸入!C2:C50,”>=90″)
及格率：=B22/COUNT(成績(jī)輸入!C2:C50)

文章插圖
右邊的動(dòng)態(tài)圖表查詢跟上邊的一樣，不再演示。
3、各學(xué)科分?jǐn)?shù)區(qū)間人數(shù)
查詢區(qū)間人數(shù)用函數(shù)：=FREQUENCY(成績(jī)輸入!C2:C31,I27:I29)，其中成績(jī)輸入!C2:C31是查找區(qū)域，I27:I29是查找條件。