在考務工作中巧用Excel透視表進行數據分析

□韓英杰

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

一、使用透視表制作“考生成績統計表”

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

圖1

1.制作“人數”列。

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

圖2

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

圖3

（3）在工作表的右側出現“數據透視表”的設置區，將字段列表中的“總分”字段拖拽到“行”區域中，將“姓名”拖拽到“值”區域中。

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

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

圖5

這樣“考生成績統計表”中的“總分”和對應的“人數”列就生成了，如圖6所示。

圖6

2.生成“累計人數”列

圖6中已經統計了每個總分的個數，下面將為每個總分生成“累計人數”。

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

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

如圖7，這樣就可以生成“考生成績統計表”中的“累計人數”數據了。

圖7

3.格式編輯，生成標準的“考生成績統計表”

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

圖8

二、使用透視表實現分數段人數統計

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

圖9

制作方法分為三步：

通過透視表對各班的各個分值的考生人數進行統計。

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

圖10

（2）對每個分數對應的人數進行匯總。對成績單執行透視表，在“數據透視表”的設置區域中，將字段列表中的“班級”拖入到“行”，“總分”拖入到“列”，“姓名”拖入到“值”。如圖11所示。

圖11

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

圖12

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

（3）對分數進行分段匯總

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

圖13

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

圖14

點擊“確定”后，表中出現分數分組統計結果，如圖15。

圖15

將上面的結果數據復制后選擇性粘貼為數值到一個新工作表中，經過格式編輯，即可實現最終要求。

三、統計每個校區中不同班級各分數段考生的數量

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

圖16

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

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

圖17

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

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

圖18

綜上所述，當需要對一組數據中的不同集合中不同分類進行統計時，我們可以采用透視表來實現。在透視表設計過程中，大家要分清“行”“列”“值”這三個參數，即可將一個復雜的數據分析結果用透視表清晰地展示出來。