Excel數(shù)據(jù)透視表在高校學(xué)生貸款數(shù)據(jù)分析中的應(yīng)用

龍金昌 陳斌

摘要：本文闡述了Excel數(shù)據(jù)透視表在高校學(xué)生貸款數(shù)據(jù)分析中的具體應(yīng)用方法，為教育和科研進(jìn)行數(shù)據(jù)透視數(shù)據(jù)分析提供了參考思路。

關(guān)鍵詞：數(shù)據(jù)透視表;數(shù)據(jù)分析;方法

中圖分類號(hào)：TP317 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1007-9416（2020）01-0073-02

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

在生源地貸款數(shù)據(jù)分析中，如使用函數(shù)進(jìn)行統(tǒng)計(jì)分析，一旦布局改變，就要重新設(shè)計(jì)函數(shù)，在需要快速呈現(xiàn)報(bào)表的情況下，函數(shù)的效率大打折扣，在這種函數(shù)不擅長的時(shí)候，正可以用數(shù)據(jù)透視表彌補(bǔ)函數(shù)的不足，通過數(shù)據(jù)透視表簡單的拖拽，就能組織大量的數(shù)據(jù)統(tǒng)計(jì)分析，縷出并呈現(xiàn)大量復(fù)雜數(shù)據(jù)的本質(zhì)聯(lián)系。

1 數(shù)據(jù)透視表的概念

Excel數(shù)據(jù)透視表（Pivot Table）是一種交互式的表，是從Excel數(shù)據(jù)列表、關(guān)系數(shù)據(jù)庫文件等數(shù)據(jù)集的字段中總結(jié)信息的分析工具。所謂“透視”，是可以動(dòng)態(tài)地改變它們的版面布置，以便按照不同方式分析數(shù)據(jù)，也可以重新安排行號(hào)、列標(biāo)和頁字段。每一次改變版面布置時(shí)，數(shù)據(jù)透視表會(huì)立即按照新的布置，從不同的視角分析并重新計(jì)算數(shù)據(jù)，也就是可以從復(fù)雜的數(shù)據(jù)背后找到數(shù)據(jù)的本質(zhì)聯(lián)系，從而將看似雜亂的數(shù)據(jù)轉(zhuǎn)化為有價(jià)值的信息，快速的生成各種類型的報(bào)表。

2 數(shù)據(jù)透視表的功能與用途

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

3 創(chuàng)建數(shù)據(jù)透視表的方法

3.1 準(zhǔn)備數(shù)據(jù)源

數(shù)據(jù)源可以是本地Excel文件，也可以通過聯(lián)機(jī)獲取外部文件，如文本文件、Microsoft SQL Server數(shù)據(jù)庫、Microsoft Access數(shù)據(jù)庫、Dbase數(shù)據(jù)庫等。數(shù)據(jù)源可以是多個(gè)獨(dú)立的Excel數(shù)據(jù)列表，數(shù)據(jù)透視表在創(chuàng)建過程中可以將多個(gè)獨(dú)立的Excel數(shù)據(jù)列表中的信息匯總到一起，也可以是其他的數(shù)據(jù)透視表作為數(shù)據(jù)源來創(chuàng)建另外一個(gè)數(shù)據(jù)透視表。

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

3.2 數(shù)據(jù)透視表創(chuàng)建方法

創(chuàng)建數(shù)據(jù)透視表常用方法有兩種：

方法1：在“插入”選項(xiàng)卡中“表”組中，單擊數(shù)據(jù)透視表圖標(biāo)，或者單擊“數(shù)據(jù)透視表”右下方箭頭，在單擊“數(shù)據(jù)透視表”。

方法2：使用快捷鍵，依次按Alt、D和P鍵，啟動(dòng)“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?，按向?qū)崾荆酵瓿赏敢暠淼膭?chuàng)建。

4 數(shù)據(jù)透視表在生源地貸款數(shù)據(jù)分析中的應(yīng)用

準(zhǔn)備好數(shù)據(jù)源，按照數(shù)據(jù)透視表創(chuàng)建方法，創(chuàng)建數(shù)據(jù)透視表。本例選取2012-2017年貸款學(xué)生8512人的貸款信息進(jìn)行數(shù)據(jù)統(tǒng)計(jì)分析（如圖1所示）。

4.1 統(tǒng)計(jì)同名同姓學(xué)生人數(shù)、判斷身份證號(hào)碼是否重復(fù)

將光標(biāo)定位在任一數(shù)據(jù)透視表數(shù)據(jù)區(qū)域，彈出“數(shù)據(jù)透視表字段列表”窗口，將“學(xué)生姓名”拖入“行標(biāo)簽”區(qū)域，將“學(xué)生姓名”拖入“Σ數(shù)值”區(qū)域。光標(biāo)定位B5單元格并按右鍵，在彈出的菜單中點(diǎn)擊“排序”，后點(diǎn)擊“降序”，同名同姓的人數(shù)即完成統(tǒng)計(jì)，并按照降序排列。

同樣操作，將“身份證號(hào)碼”拖入“行標(biāo)簽”區(qū)域，將“身份證號(hào)碼”拖入“Σ數(shù)值”區(qū)域。光標(biāo)定位B5單元格并按右鍵，在彈出的菜單中點(diǎn)擊“排序”，后點(diǎn)擊“降序”，相同身份證號(hào)碼的人數(shù)即完成統(tǒng)計(jì)，并按照降序排列。如身份證號(hào)碼統(tǒng)計(jì)數(shù)均應(yīng)為1，如為2或2以上的數(shù)字，說明數(shù)據(jù)源表中，學(xué)生的身份證號(hào)碼有重復(fù)，錄入有誤，應(yīng)復(fù)核后進(jìn)行更正。

4.2 院系、專業(yè)學(xué)生人數(shù)以及各院系所屬各專業(yè)人數(shù)統(tǒng)計(jì)

將光標(biāo)定位在在任一數(shù)據(jù)透視表數(shù)據(jù)區(qū)域，在彈出的“數(shù)據(jù)透視表字段列表”窗口，將“院系名稱”拖入“行標(biāo)簽”區(qū)域，將“學(xué)生姓名”拖入“Σ數(shù)值”區(qū)域。將光標(biāo)定位在B5單元格并按右鍵，在彈出的菜單中點(diǎn)擊“排序”，后點(diǎn)擊“降序”，各院系的人數(shù)即完成統(tǒng)計(jì)，并按照降序排列。同樣操作，將“專業(yè)名稱”拖入“行標(biāo)簽”區(qū)域，將“學(xué)生姓名”拖入“Σ數(shù)值”區(qū)域。光標(biāo)定位B5單元格并按右鍵，在彈出的菜單中點(diǎn)擊“排序”，后點(diǎn)擊“降序”，各專業(yè)的人數(shù)即完成統(tǒng)計(jì)，并按照降序排列（如圖2所示）。

將光標(biāo)定位在在任一數(shù)據(jù)透視表數(shù)據(jù)區(qū)域，在彈出的“數(shù)據(jù)透視表字段列表”窗口，將“院系名稱”拖入“行標(biāo)簽”區(qū)域，將“專業(yè)名稱”拖入“行標(biāo)簽”區(qū)域，并確保專業(yè)名稱”在“院系名稱”下方，將“學(xué)生姓名”拖入“Σ數(shù)值”區(qū)域。將光標(biāo)定位在C5單元格并按右鍵，在彈出的菜單中點(diǎn)擊“排序”，后點(diǎn)擊“降序”，各院系、各專業(yè)的人數(shù)即完成統(tǒng)計(jì)，并按照降序排列。

4.3 各院系所屬各專業(yè)對(duì)應(yīng)人數(shù)及貸款金額統(tǒng)計(jì)

將光標(biāo)定位在任一數(shù)據(jù)透視表數(shù)據(jù)區(qū)域，在彈出的“數(shù)據(jù)透視表字段列表”窗口，將“院系名稱”拖入“行標(biāo)簽”區(qū)域，將“專業(yè)名稱”拖入“行標(biāo)簽”區(qū)域，并確保專業(yè)名稱”在“院系名稱”下方，將“學(xué)生姓名”“高校轉(zhuǎn)賬金額”拖入“Σ數(shù)值”區(qū)域。將光標(biāo)定位在C5單元格并按右鍵，在彈出的菜單中點(diǎn)擊“排序”，后點(diǎn)擊“降序”，各院系、各專業(yè)對(duì)應(yīng)的人數(shù)及貸款金額即完成統(tǒng)計(jì)，并按照降序排列（如圖3所示）。

4.4 通過報(bào)表篩選進(jìn)行單獨(dú)匯總或多項(xiàng)匯總，并可生成具體名單

在上面的數(shù)據(jù)透視表的基礎(chǔ)上繼續(xù)進(jìn)行操作。如將“貸款年度”拖拽到“報(bào)表篩選”區(qū)域，可以對(duì)各年度的貸款學(xué)生的人數(shù)和金額進(jìn)行單獨(dú)匯總或選擇多項(xiàng)匯總。如將“發(fā)放到高校日期”拖拽到“報(bào)表篩選”區(qū)域，可按到賬日期統(tǒng)計(jì)對(duì)貸款學(xué)生的人數(shù)和金額進(jìn)行單獨(dú)匯總或選擇多項(xiàng)匯總。在統(tǒng)計(jì)匯總的學(xué)生人數(shù)或金額數(shù)字上單擊，即可快速獲得學(xué)生的具體名單。

5 結(jié)語

數(shù)據(jù)透視表交互性強(qiáng)，計(jì)算和統(tǒng)計(jì)快速高效，修改數(shù)據(jù)的內(nèi)容和布局操作簡便，能從各種視角快速變更不同的數(shù)據(jù)分析維度，實(shí)時(shí)數(shù)據(jù)變更后通過刷新功能即反映到數(shù)據(jù)透視表中，提高了統(tǒng)計(jì)分析的效率。

參考文獻(xiàn)

[1] 羅惠民，錢勇.“偷懶”的技術(shù)：打造財(cái)務(wù)Excel達(dá)人[M].北京：機(jī)械工業(yè)出版社，2017.

[2]安偉星，裴雷.競爭力：玩轉(zhuǎn)職場(chǎng)Excel，從此不加班[M].北京：電子工業(yè)出版社，2017.