在考務(wù)工作中巧用Excel透視表進行數(shù)據(jù)分析

□韓英杰

作為一名考務(wù)工作人員，經(jīng)常會面對大量的分類統(tǒng)計工作，尤其是對多個集合中的多個類別進行統(tǒng)計，如果使用常規(guī)方法，那就需要逐個集合進行分類統(tǒng)計，最后再將統(tǒng)計結(jié)果合并，這樣的操作既繁瑣又容易出錯。筆者經(jīng)過實踐，發(fā)現(xiàn)這類問題可以借助Excel透視表來處理，透視表通過可視化的操作，將相關(guān)字段定義到“行”“列”“計數(shù)項”三項中，可以快速完成各類復(fù)雜的統(tǒng)計工作。筆者將結(jié)合幾個考務(wù)工作中常見的典型實例，談一談如何使用Excel的“透視表”實現(xiàn)統(tǒng)計工作。

一、使用透視表制作“考生成績統(tǒng)計表”

“考生成績統(tǒng)計表”實際上是以“某一個總分”為一個類，統(tǒng)計考得該分數(shù)的考生人數(shù)和大于等于該分的累計人數(shù)，“考生成績統(tǒng)計表”是考生填報志愿的重要依據(jù)，一般“考生成績統(tǒng)計表”包含三列，分別是“總分”“人數(shù)”“累計人數(shù)”（如圖1所示），下面我們分別講解如何制作“人數(shù)”和“累計人數(shù)”兩列數(shù)據(jù)。

圖1

1.制作“人數(shù)”列。

（1）打開成績單，保留“姓名”和“總分”兩列必要的數(shù)據(jù)。先用鼠標點擊任意一個非空的單元格（例如圖2中的A1單元格），然后點擊“數(shù)據(jù)”菜單中的“數(shù)據(jù)透視表”。

圖2

（2）屏幕上顯示透視表對話框，確認單元格區(qū)域參數(shù)是全部有效數(shù)據(jù)的區(qū)域后，點擊“確定”按鈕（如圖3）。

圖3

（3）在工作表的右側(cè)出現(xiàn)“數(shù)據(jù)透視表”的設(shè)置區(qū)，將字段列表中的“總分”字段拖拽到“行”區(qū)域中，將“姓名”拖拽到“值”區(qū)域中。

完成拖拽后，左側(cè)會出現(xiàn)兩列數(shù)據(jù)，其中“總分”是成績單中所有總分，默認是從小到大排序，“計數(shù)項：姓名”是對應(yīng)每個總分的人數(shù)，也就是考得相同的總分總共有幾個人，這個人數(shù)是通過對姓名數(shù)據(jù)的個數(shù)進行統(tǒng)計的（注：即使里面有相同姓名的人也不會影響統(tǒng)計結(jié)果，因為系統(tǒng)是按照姓名的個數(shù)進行統(tǒng)計的）。

（4）為了符合從高分到低分的排序，可以點擊“總分”右邊的向下箭頭，從中選取“降序”（如圖5）。

圖5

這樣“考生成績統(tǒng)計表”中的“總分”和對應(yīng)的“人數(shù)”列就生成了，如圖6所示。

圖6

2.生成“累計人數(shù)”列

圖6中已經(jīng)統(tǒng)計了每個總分的個數(shù)，下面將為每個總分生成“累計人數(shù)”。

第一步：在C4單元格輸入“=B4”；第二步：在C5單元格輸入“=C4+B5”，表示該行總分對應(yīng)的累計人數(shù)是上一累計人數(shù)加上當(dāng)前總分對應(yīng)的人數(shù)，回車確認后得到了對應(yīng)554分的累計人數(shù)；第三步：雙擊C5的右下角“十”字，自動填充C列剩余的單元格。

這里需要注意的是，上面的“=C4+B5”公式中“B5”字符需要手工輸入，不要使用使用鼠標點擊B5單元格，否則會出現(xiàn)公式調(diào)用錯誤。

如圖7，這樣就可以生成“考生成績統(tǒng)計表”中的“累計人數(shù)”數(shù)據(jù)了。

圖7

3.格式編輯，生成標準的“考生成績統(tǒng)計表”

將上表中全部數(shù)據(jù)進行復(fù)制，然后右鍵點擊新建工作表中的空白單元格，點擊“選擇性粘貼”-“粘貼為數(shù)值”的方式粘貼到一個新建的工作表中，即可得到標準的“考生成績統(tǒng)計表”。如圖8所示。

圖8

二、使用透視表實現(xiàn)分數(shù)段人數(shù)統(tǒng)計

如下圖所示，需要統(tǒng)計一個年級各班總分500分以下，以及500分至600分之間，每隔20分一個分數(shù)段的人數(shù)。統(tǒng)計的結(jié)果如圖9所示。

圖9

制作方法分為三步：

通過透視表對各班的各個分值的考生人數(shù)進行統(tǒng)計。

（1）成績單要保留必要的姓名、班級、總分三個字段。如圖10所示。

圖10

（2）對每個分數(shù)對應(yīng)的人數(shù)進行匯總。對成績單執(zhí)行透視表，在“數(shù)據(jù)透視表”的設(shè)置區(qū)域中，將字段列表中的“班級”拖入到“行”，“總分”拖入到“列”，“姓名”拖入到“值”。如圖11所示。

圖11

當(dāng)前列表中的數(shù)值的含義是“某班考取某分數(shù)的人數(shù)”。例如H8單元格中的1代表“4班考取168分有1名考生”。如圖12所示。

圖12

這是對每一個分數(shù)對應(yīng)的人數(shù)進行匯總，那么如何按照分數(shù)段進行人數(shù)匯總呢？我們還需要下面的操作。

（3）對分數(shù)進行分段匯總

點擊“總分”右側(cè)的向下箭頭，在彈出的菜單中選擇“組合”，如圖13所示。

圖13

在彈出的對話框中將“起始于”填上500，將“終止于”填寫600，將“步長”填寫20。如圖14所示。

圖14

點擊“確定”后，表中出現(xiàn)分數(shù)分組統(tǒng)計結(jié)果，如圖15。

圖15

將上面的結(jié)果數(shù)據(jù)復(fù)制后選擇性粘貼為數(shù)值到一個新工作表中，經(jīng)過格式編輯，即可實現(xiàn)最終要求。

三、統(tǒng)計每個校區(qū)中不同班級各分數(shù)段考生的數(shù)量

當(dāng)前很多大一些的學(xué)校都有多個校區(qū)，如圖16中這個學(xué)校的成績單中有“東校區(qū)”“南校區(qū)”兩個校區(qū)，每個校區(qū)中又有多個班，那么我們是否可以統(tǒng)計出兩個校區(qū)中各班的分段成績統(tǒng)計表。

圖16

按照常規(guī)思維，先將東校區(qū)的數(shù)據(jù)篩選出來組成一個表，再將南校區(qū)篩選出來新建一個表，然后對這兩個表分別進行分段統(tǒng)計，最后將兩個表復(fù)制粘貼到一起。但是，利用透視表可以一次性完成多個校區(qū)、班級的分數(shù)段統(tǒng)計工作。

如圖17所示，將“校區(qū)”和“班級”依次拖入“行”中，要保證“校區(qū)”在“班級”的上面，然后將“總分”拖入“列”中，將“姓名”拖入“值”中。

圖17

這時，可以看到系統(tǒng)將所有考生按照校區(qū)分成了上下兩個表格，然后在每個校區(qū)中又按照班進行了統(tǒng)計，這是因為在右邊的透視表設(shè)置區(qū)域中，“行”里面“校區(qū)”在“班級”的上面，所以就有了先按照“校區(qū)”劃分，再按照“班級”劃分表的結(jié)構(gòu)。

現(xiàn)在我們可以點擊左邊表中的“總分”進行分組，這一步類似本文“二、使用透視表實現(xiàn)分數(shù)段人數(shù)統(tǒng)計”，進一步統(tǒng)計各分數(shù)段的人數(shù)，設(shè)置起點500分，終止600分，每20分一個組，那么就有下面這個表格。如圖18。

圖18

綜上所述，當(dāng)需要對一組數(shù)據(jù)中的不同集合中不同分類進行統(tǒng)計時，我們可以采用透視表來實現(xiàn)。在透視表設(shè)計過程中，大家要分清“行”“列”“值”這三個參數(shù)，即可將一個復(fù)雜的數(shù)據(jù)分析結(jié)果用透視表清晰地展示出來。