Excel數據透視表在高校學生貸款數據分析中的應用

龍金昌 陳斌

摘要：本文闡述了Excel數據透視表在高校學生貸款數據分析中的具體應用方法，為教育和科研進行數據透視數據分析提供了參考思路。

關鍵詞：數據透視表;數據分析;方法

中圖分類號：TP317 文獻標識碼：A 文章編號：1007-9416（2020）01-0073-02

經濟飛速發展、信息技術全面應用，給各行各業帶來了海量的數據，大數據時代數據爆炸式增長。盡管如ERP等大型軟件和行業系統管理平臺提供了強大的分析功能和報表輸出功能，但它們提供的都是通用的報表分析，而以Excel為代表的電子表格軟件卻能靈活地滿足企業個性化的數據分析需求，所以很多行業的管理人員在日常工作中始終離不開Excel，與工作息息相關，使用Excel的熟練程度，直接決定了數據分析效率。

在生源地貸款數據分析中，如使用函數進行統計分析，一旦布局改變，就要重新設計函數，在需要快速呈現報表的情況下，函數的效率大打折扣，在這種函數不擅長的時候，正可以用數據透視表彌補函數的不足，通過數據透視表簡單的拖拽，就能組織大量的數據統計分析，縷出并呈現大量復雜數據的本質聯系。

1 數據透視表的概念

Excel數據透視表（Pivot Table）是一種交互式的表，是從Excel數據列表、關系數據庫文件等數據集的字段中總結信息的分析工具。所謂“透視”，是可以動態地改變它們的版面布置，以便按照不同方式分析數據，也可以重新安排行號、列標和頁字段。每一次改變版面布置時，數據透視表會立即按照新的布置，從不同的視角分析并重新計算數據，也就是可以從復雜的數據背后找到數據的本質聯系，從而將看似雜亂的數據轉化為有價值的信息，快速的生成各種類型的報表。

2 數據透視表的功能與用途

Excel數據透視表因具有強大的交互性，可以通過通過簡單的布局改變，全方位、多角度、動態地統計和分析數據，綜合了數據排序、篩選、分類匯總等功能，以及計算平均數或標準差、建立列聯表、計算環比同比、建立新的數據子集等等，從大量數據中快速提取有價值信息。因此，在分析相關匯總數據，尤其是大量的數據需要統計分析的時候，需要用到數據透視表，其功能和用途主要有：一是快速的查詢、統計數據，從多種視角查看數據的不同匯總;二是對數值數據進行分類匯總聚合，創建自定義計算和公式;三是展開、折疊所關注的結果的數據級別，導出關注匯總數據的明細;四是提供簡明的聯機報表和打印報表等。

3 創建數據透視表的方法

3.1 準備數據源

數據源可以是本地Excel文件，也可以通過聯機獲取外部文件，如文本文件、Microsoft SQL Server數據庫、Microsoft Access數據庫、Dbase數據庫等。數據源可以是多個獨立的Excel數據列表，數據透視表在創建過程中可以將多個獨立的Excel數據列表中的信息匯總到一起，也可以是其他的數據透視表作為數據源來創建另外一個數據透視表。

作為數據源的數據表格，要將缺失的數據補充完整，將錯誤的數據糾正或者刪除，將重復、多余的數據篩選清除，整理成為標準的、干凈的、連續的數據，以方便后期的數據處理。比如數據表格不能使用中國傳統的斜線表頭、不能有合并單元格;數據表格中列標題不能為空，不能有空行空列;數據表格中不要大范圍的使用條件格式、數據有效性、數組公式等等。

3.2 數據透視表創建方法

創建數據透視表常用方法有兩種：

方法1：在“插入”選項卡中“表”組中，單擊數據透視表圖標，或者單擊“數據透視表”右下方箭頭，在單擊“數據透視表”。

方法2：使用快捷鍵，依次按Alt、D和P鍵，啟動“數據透視表和數據透視圖向導”，按向導提示，三步完成透視表的創建。

4 數據透視表在生源地貸款數據分析中的應用

準備好數據源，按照數據透視表創建方法，創建數據透視表。本例選取2012-2017年貸款學生8512人的貸款信息進行數據統計分析（如圖1所示）。

4.1 統計同名同姓學生人數、判斷身份證號碼是否重復

將光標定位在任一數據透視表數據區域，彈出“數據透視表字段列表”窗口，將“學生姓名”拖入“行標簽”區域，將“學生姓名”拖入“Σ數值”區域。光標定位B5單元格并按右鍵，在彈出的菜單中點擊“排序”，后點擊“降序”，同名同姓的人數即完成統計，并按照降序排列。

同樣操作，將“身份證號碼”拖入“行標簽”區域，將“身份證號碼”拖入“Σ數值”區域。光標定位B5單元格并按右鍵，在彈出的菜單中點擊“排序”，后點擊“降序”，相同身份證號碼的人數即完成統計，并按照降序排列。如身份證號碼統計數均應為1，如為2或2以上的數字，說明數據源表中，學生的身份證號碼有重復，錄入有誤，應復核后進行更正。

4.2 院系、專業學生人數以及各院系所屬各專業人數統計

將光標定位在在任一數據透視表數據區域，在彈出的“數據透視表字段列表”窗口，將“院系名稱”拖入“行標簽”區域，將“學生姓名”拖入“Σ數值”區域。將光標定位在B5單元格并按右鍵，在彈出的菜單中點擊“排序”，后點擊“降序”，各院系的人數即完成統計，并按照降序排列。同樣操作，將“專業名稱”拖入“行標簽”區域，將“學生姓名”拖入“Σ數值”區域。光標定位B5單元格并按右鍵，在彈出的菜單中點擊“排序”，后點擊“降序”，各專業的人數即完成統計，并按照降序排列（如圖2所示）。

將光標定位在在任一數據透視表數據區域，在彈出的“數據透視表字段列表”窗口，將“院系名稱”拖入“行標簽”區域，將“專業名稱”拖入“行標簽”區域，并確保專業名稱”在“院系名稱”下方，將“學生姓名”拖入“Σ數值”區域。將光標定位在C5單元格并按右鍵，在彈出的菜單中點擊“排序”，后點擊“降序”，各院系、各專業的人數即完成統計，并按照降序排列。

4.3 各院系所屬各專業對應人數及貸款金額統計

將光標定位在任一數據透視表數據區域，在彈出的“數據透視表字段列表”窗口，將“院系名稱”拖入“行標簽”區域，將“專業名稱”拖入“行標簽”區域，并確保專業名稱”在“院系名稱”下方，將“學生姓名”“高校轉賬金額”拖入“Σ數值”區域。將光標定位在C5單元格并按右鍵，在彈出的菜單中點擊“排序”，后點擊“降序”，各院系、各專業對應的人數及貸款金額即完成統計，并按照降序排列（如圖3所示）。

4.4 通過報表篩選進行單獨匯總或多項匯總，并可生成具體名單

在上面的數據透視表的基礎上繼續進行操作。如將“貸款年度”拖拽到“報表篩選”區域，可以對各年度的貸款學生的人數和金額進行單獨匯總或選擇多項匯總。如將“發放到高校日期”拖拽到“報表篩選”區域，可按到賬日期統計對貸款學生的人數和金額進行單獨匯總或選擇多項匯總。在統計匯總的學生人數或金額數字上單擊，即可快速獲得學生的具體名單。

5 結語

數據透視表交互性強，計算和統計快速高效，修改數據的內容和布局操作簡便，能從各種視角快速變更不同的數據分析維度，實時數據變更后通過刷新功能即反映到數據透視表中，提高了統計分析的效率。

參考文獻

[1] 羅惠民，錢勇.“偷懶”的技術：打造財務Excel達人[M].北京：機械工業出版社，2017.

[2]安偉星，裴雷.競爭力：玩轉職場Excel，從此不加班[M].北京：電子工業出版社，2017.