應用Excel數據透視表進行學籍管理與分析

羅凌江等

[摘 要] 結合SQL語句，應用Excel數據透視表可以更加廣泛、深入地進行數據分析和匯總，可以在學籍數據管理分析和公司的銷售業績、貨物管理等方面取得較好的實際效果。

[關鍵詞] Excel； SQL；數據透視表；管理；教學

doi ： 10 . 3969 / j . issn . 1673 - 0194 . 2014 . 16. 083

[中圖分類號] G64 [文獻標識碼] A [文章編號] 1673 - 0194（2014）16- 0140- 06

教學管理中，經常要對大量的數據進行分析、匯總，由此也產生了大量的管理軟件，但是，軟件再多，畢竟無法解決層出不窮的新問題。因此，管理人員掌握常用的數據庫軟件的使用，可以及時、較好地解決實際工作中的各種問題，提升工作效率。在眾多的數據庫軟件中，Excel作為普及度最高的軟件，擁有著強大的數據管理分析功能，而且隨著軟件的升級，加入了更多、更強大的功能，掌握好Excel的使用、充分運用其功能，將成為解決實際問題的“利刃”。本文以多次考試成績的匯總、分析為例，提出應用Excel解決類似問題的思路，并詳細講解實際操作方法。還要指出，本問題的解決，還可應用于公司銷售業績、進出貨物管理等多種情形，因此具有相當的普適性。

1 提出問題

大量來自不同地區的人員參加考試，每次組織綜合科目和不同的專業科目考試，同時通過綜合科目和某一專業科目才能取得該專業執業證書，沒通過相關科目的人員可能再參加后面批次的考試，要求匯總分析每一次考試和幾次考試的人員通過情況和地區通過情況，整理出幾次考試后可取得執業注冊證書的人員名單，分析每一考試科目的總體通過情況。

2 思路分析

解決該問題的難點在于整理執業證書人員名單時，必須通過綜合和專業考試科目，而這兩個科目不一定是在一次考試通過，而且考試的專業比較多。

在分析問題時，也應當看到，結果并不要求對每次考試的具體成績進行層次分析，這樣即可簡化我們解題思路，即將通過、不通過定義為1、0即可，而不需要分析具體的考試分數（當然也可通過其他辦法來進行考試成績的定量分析）。

基于以上兩個主要方面的分析，我們可以得出，利用Excel的數據透視表來解決這一問題是一個比較適當的方法。因為數據透視表是用來從Excel數據列表、關系數據庫文件或OLAP多維數據集等數據源的特定字段中總比信息的分析工具。它是一種交互性報表，可以快速分類匯總比較大量的數據，并可以隨時選擇其中頁、行和列中的不同元素，以達到快速查看數據源的不同統計結果，同時還可以隨意顯示和打印出用戶感興趣的區域和明細數據。數據透視表有機地綜合了數據排序、篩選和分類匯總等數據分析方法的優點，可以方便地調整分類匯總的方式，靈活地以多種不同方式展示數據的特征。

3 實際操作

在本節中，以分析解決問題為主線，穿插了Excel數據處理中的常用方法。考慮到Excel 2010在數據透視表方面顯著的功能升級和強化，本文以Excel 2010版本為平臺進行操作。

3.1 整理數據源

準確、規范的數據源是高效分析、正確結果的基礎，因此，這項基礎工作應當引起足夠重視。根據實際問題，將每一次考試數據放在一個Excel文件（工作簿）中，其中的多個工作表分別容納這次考試中某一考試科目的數據，然后將幾次考試的數據（幾個Excel文件）放在同一個文件夾內。為方便操作和表達，這里幾個文件放在“D：＼成績分析”文件夾中。

以第一次考試為例，建立一個Excel文件命名為“第1次”，并將相應的工作表命名為“綜合科目”“建筑一級”“建筑二級”……如圖1所示。

需要指出的是，每張表的結構應當一致，即第一行標題需相同，且不能出現多余的列，否則，在后面的步驟中無法正常進行。