基于PQ與數(shù)據(jù)透視表的地方高校業(yè)財數(shù)據(jù)動態(tài)分析實踐

隨著數(shù)字化轉(zhuǎn)型，高校業(yè)財數(shù)據(jù)規(guī)模劇增、多源且異構(gòu)，數(shù)據(jù)驅(qū)動決策，而地方高校財務(wù)人員信息素養(yǎng)參差不齊，非小數(shù)據(jù)分析技能多為短板，亟需快速提升。眾多數(shù)據(jù)分析工具中，編程軟件學(xué)習(xí)成本高易用性不足，專業(yè)BI軟件學(xué)習(xí)門檻略高，通用AI仍面臨安全和隱私問題，Excel傳統(tǒng)分析小數(shù)據(jù)功能面向非小規(guī)模和復(fù)雜類型數(shù)據(jù)時凸顯諸多無力，如重復(fù)勞動多，運行卡頓，函數(shù)公式有速度瓶頸且易錯易忘，簡單匯總數(shù)據(jù)粒度粗，不靈活、實現(xiàn)多維交互可視化動態(tài)分析和數(shù)據(jù)挖掘障礙多，分析效率低下，難滿足管理決策的數(shù)據(jù)需求。

Excel幾十年來不斷迭代升級，如從2016版起已將PowerBI的 PQ/ PowerQuery和PP/PowerPivot等組件整合其中，其部分功能在處理較大規(guī)模和復(fù)雜數(shù)據(jù)時十分強大，易學(xué)易用，卻未得到足夠重視。本文結(jié)合高校實務(wù)案例，圍繞綜合應(yīng)用Excel的 PQ 、數(shù)據(jù)透視表（含基礎(chǔ)版PT和增強版PP）和超級表格之BI功能，對較大規(guī)模單源或多源業(yè)財數(shù)據(jù)實現(xiàn)多維交互細(xì)粒度可視化動態(tài)分析和輕量級數(shù)據(jù)挖掘以支持管理決策數(shù)據(jù)需求。此工具以鼠標(biāo)操作為主，操作路徑不長，易學(xué)易用使用成本低，能顯著降低財務(wù)人員信息技能門檻，適合大多數(shù)地方高校業(yè)財數(shù)據(jù)特點，能克服傳統(tǒng)分析小數(shù)據(jù)功能的不足，大幅提升分析的速度、精度、深度和廣度，技術(shù)賦能業(yè)財深度融合，為科學(xué)管理提供數(shù)據(jù)支撐，助力學(xué)校高質(zhì)量發(fā)展。

一、工具簡介

本數(shù)據(jù)分析工具為綜合應(yīng)用Excel2016或更高版本的 PQ 、數(shù)據(jù)透視表和超級表格之BI功能，能適用較大規(guī)模單源和多源數(shù)據(jù)的分析，以鼠標(biāo)操作為主，學(xué)習(xí)門檻較低。

1.PQPQ是專業(yè)的獲取和預(yù)處理數(shù)據(jù)工具。 PQ 獲取數(shù)據(jù)的路徑為【數(shù)據(jù)】、【獲取數(shù)據(jù)】和數(shù)據(jù)存儲路徑，其支持連接和查詢幾十種常用格式的數(shù)據(jù)源，規(guī)?？芍?GB以內(nèi)的CSV／文本文件，超過Excel百萬行數(shù)限制。對導(dǎo)入的不規(guī)范數(shù)據(jù)能輕松實現(xiàn)傳統(tǒng)Excel中很難完成的各種數(shù)據(jù)預(yù)處理，復(fù)雜情形下可用公式和代碼M語言，還能動態(tài)刷新實現(xiàn)自動化處理。當(dāng)數(shù)據(jù)量更大時，或受內(nèi)存制約，通過篩選、聚合減少數(shù)據(jù)量、使用數(shù)據(jù)庫文件或增量刷新等策略優(yōu)化可予以適度應(yīng)對。

2.數(shù)據(jù)透視表基礎(chǔ)版數(shù)據(jù)透視表/PT：點擊【插入】選項卡的【數(shù)據(jù)透視表】進(jìn)人界面。它是Excel最強大的基礎(chǔ)功能，規(guī)范的數(shù)據(jù)源是其正確使用前提，主要有排序篩選、組合、分類匯總、多維度分析、交互分析、可視化和輕量級數(shù)據(jù)挖掘等數(shù)據(jù)分析功能，可手動或自動刷新。透視表操作簡單快捷，是突破函數(shù)公式速度瓶頸的重要手段。規(guī)范的數(shù)據(jù)源要求列字段名不重復(fù)且只能有一行、無空白數(shù)據(jù)行（列）、無合并單元格、無分類匯總行或總計行、數(shù)據(jù)格式規(guī)范統(tǒng)一，盡可能放置在同一工作表或簿中。下面任一類型都可以是數(shù)據(jù)源Excel數(shù)據(jù)列表清單、外部數(shù)據(jù)源如文本txt/csv或數(shù)據(jù)庫、多個獨立的Excel數(shù)據(jù)列表和其他透視表。

透視表可實現(xiàn)靈活精準(zhǔn)的多維交互細(xì)粒度可視化動態(tài)分析和輕量級數(shù)據(jù)挖掘。將各字段（列名）選擇性拽入其四個區(qū)域（行、列、篩選、值）或進(jìn)一步組合字段，得到多維分析報表。通過選擇維度和排序篩選切片器等實現(xiàn)交互分析。通過選擇細(xì)維度或雙擊合計數(shù)據(jù)可將分析穿透到最細(xì)粒度。一鍵刷新分析結(jié)果，結(jié)合運用超級表格可智能動態(tài)分析。數(shù)據(jù)透視表一一對應(yīng)數(shù)據(jù)透視圖，以柱形條形餅狀環(huán)形等圖形可視化呈現(xiàn)，其圖形篩選工具切片器的報表連接功能能對同一數(shù)據(jù)源下多個透視表或圖實現(xiàn)聯(lián)動

控制，結(jié)合超級表格，可創(chuàng)建智能動態(tài)數(shù)據(jù)看板或可視化看板，分析更直觀靈活全面。借助這些功能可更全面深人認(rèn)識大量業(yè)財數(shù)據(jù)后隱藏的信息，實現(xiàn)輕量級數(shù)據(jù)挖掘，為精細(xì)化科學(xué)管理提供數(shù)據(jù)支撐。當(dāng)分析大數(shù)據(jù)集尤其是復(fù)雜計算和匯總時將大量消耗內(nèi)存，或使運行緩慢甚至崩潰，需轉(zhuǎn)用PP等工具。

增強版數(shù)據(jù)透視表/PP：增強版可簡單理解為基礎(chǔ)版/PT的升級，用于增強Excel的數(shù)據(jù)分析功能，其核心功能是數(shù)據(jù)建模和數(shù)據(jù)分析，幾乎支持所有常用數(shù)據(jù)類型，支持多源數(shù)據(jù)，通過建模將多源數(shù)據(jù)建立起關(guān)系，在此基礎(chǔ)上實現(xiàn)類似PT的分析功能。點擊【PowerPivot】選項卡的【數(shù)據(jù)管理模型】進(jìn)入PP界面，還可創(chuàng)建透視表時勾選【將此數(shù)據(jù)添加到模型】復(fù)選框進(jìn)入，如非重復(fù)計數(shù)時。

PP支持較大規(guī)模數(shù)據(jù)的分析。PP能在Excel中建立快速強大的內(nèi)存數(shù)據(jù)庫，存儲數(shù)千萬甚至億萬行數(shù)據(jù)，龐大的數(shù)據(jù)加載后能高效壓縮到只保留原來數(shù)據(jù)文件體積的 10% 左右，從而使運行幾十萬行數(shù)據(jù)時可能受制于硬件性能出現(xiàn)卡頓的問題迎刃而解，PP支持單表最多約20億行1.6萬列，支持Excel工作簿最大約2GB（32位）或4GB（64位），可用透視表和透視圖，還支持DAX編程語言完成更高級和復(fù)雜的計算與分析。通過減少不必要列、使用DAX語言優(yōu)化計算、優(yōu)化數(shù)據(jù)模型和使用增量刷新等策略，可支持更大規(guī)模的數(shù)據(jù)，若是大數(shù)據(jù)可先使用數(shù)據(jù)庫文件再用PP等工具

3.超級表格點擊【插入】選項卡的【表格】，表格數(shù)據(jù)轉(zhuǎn)化為超級表格。這是一種結(jié)構(gòu)化數(shù)據(jù)工具，能夠?qū)⑵胀ǖ臄?shù)據(jù)區(qū)域轉(zhuǎn)換為具有增強功能的動態(tài)表格，能動態(tài)識別和擴展表格范圍（包括公式、格式等），使數(shù)據(jù)處理效率顯著提升，尤其適合需要持續(xù)更新的數(shù)據(jù)集。

三、業(yè)財數(shù)據(jù)分析基本流程

高校業(yè)財數(shù)據(jù)分析基本流程包括數(shù)據(jù)獲取、預(yù)處理、分析與挖掘及可視化。獲取原始數(shù)據(jù)才能有分析的原料。原始數(shù)據(jù)含臟數(shù)據(jù)，必須預(yù)處理（包括清洗和整理）作為可靠分析的前提，數(shù)據(jù)清洗包括檢查數(shù)據(jù)完備性，驗證和處理缺失值、異常值、錯誤值、重復(fù)值和格式標(biāo)準(zhǔn)化等數(shù)據(jù)質(zhì)量問題，數(shù)據(jù)整理包括合并多源數(shù)據(jù)集、處理甬余、統(tǒng)一命名規(guī)范和聚合數(shù)據(jù)等。

通過統(tǒng)計和業(yè)務(wù)洞察等方法分析數(shù)據(jù)，從中提取有價值信息，以支持決策和優(yōu)化流程，挖掘則是從海量數(shù)據(jù)中自動發(fā)現(xiàn)隱藏模式、關(guān)聯(lián)規(guī)則或預(yù)測趨勢，分析和挖掘相輔相成，前者側(cè)重解釋已知問題，后者側(cè)重探索未知規(guī)律?？梢暬怯脠D形結(jié)構(gòu)對分析挖掘結(jié)果進(jìn)行直觀形象、生動有力的呈現(xiàn)，幫助使用者更清晰地獲取數(shù)據(jù)中隱含的信息，有靜態(tài)、動態(tài)圖表呈現(xiàn)或可視化看板綜合呈現(xiàn)。

高校業(yè)財原始數(shù)據(jù)主要分散在財務(wù)、收費、國資、人事和教務(wù)等信息系統(tǒng)中，多為結(jié)構(gòu)化數(shù)據(jù)，交付格式為xlsx/csv/txt/pdf或數(shù)據(jù)庫，規(guī)模與師生數(shù)量及業(yè)務(wù)頻率強相關(guān)，地方高校師生數(shù)量多在0.5—3萬，業(yè)財數(shù)據(jù)規(guī)模般幾千到幾億行?？捎们笆鼋M合工具完成數(shù)據(jù)分析全流程，用PQ獲取原始數(shù)據(jù)，授權(quán)后還可動態(tài)獲取，結(jié)合運用超級表格，用PQ動態(tài)預(yù)處理為規(guī)范的透視表數(shù)據(jù)源，再用數(shù)據(jù)透視表實現(xiàn)動態(tài)分析、可視化和輕量級數(shù)據(jù)挖掘，亦可對同數(shù)據(jù)源透視表打造智能數(shù)據(jù)看板或可視化看板。

四、A地方高校業(yè)財數(shù)據(jù)動態(tài)分析實踐

下面以A地方高校財務(wù)部門內(nèi)部的多源收費業(yè)財數(shù)據(jù)為對象，運用前述工具動態(tài)分析，揭示大量數(shù)據(jù)隱含的信息，支撐相關(guān)管理科學(xué)決策。

1.情境簡介A校以適應(yīng)數(shù)電票及財政票據(jù)電子化改革為契機引人某收費一體化智慧管理平臺，對收費業(yè)務(wù)模塊實行了初步數(shù)智化轉(zhuǎn)型。平臺按管理需求不同分設(shè)非稅收費和代收費等子平臺，收費業(yè)務(wù)原始數(shù)據(jù)可按xlsx或csv格式導(dǎo)出，自制匯總原始憑證作為核算依據(jù)。各學(xué)生收費數(shù)據(jù)分散在各子平臺，收費管理要求整合各子平臺數(shù)據(jù)，同時這些數(shù)據(jù)含臟數(shù)據(jù)，無論是財務(wù)核算還是業(yè)務(wù)管理都需預(yù)處理。

2.技術(shù)實現(xiàn)導(dǎo)出并管理原始數(shù)據(jù)：按照管理需求在各子平臺導(dǎo)出相關(guān)原始數(shù)據(jù)，如學(xué)生催費和收費風(fēng)險預(yù)警管理，需導(dǎo)出非稅和代收費應(yīng)收及欠費數(shù)據(jù)，然后管理這些數(shù)據(jù)以降低后續(xù)操作難度，如存儲在單一簿、表或文件夾內(nèi)、統(tǒng)一工作表結(jié)構(gòu)和命名等。

用PQ動態(tài)獲取原始數(shù)據(jù)：上述單源和多源數(shù)據(jù)，都可用 PQ 獲取。得到數(shù)據(jù)授權(quán)和管理授權(quán)后， PQ 可與各子平臺數(shù)據(jù)對接，從而動態(tài)獲取原始數(shù)據(jù)。

將數(shù)據(jù)源轉(zhuǎn)換為超級表格：PQ預(yù)處理中會自動將數(shù)據(jù)源轉(zhuǎn)換為超級表格，若直接用透視表，需在創(chuàng)建之前轉(zhuǎn)換，利用其自動識別表格范圍功能打造動態(tài)擴展的數(shù)據(jù)源

用PQ動態(tài)預(yù)處理數(shù)據(jù)：此步驟目標(biāo)是創(chuàng)建規(guī)范且動態(tài)的透視表數(shù)據(jù)源，由于導(dǎo)出的為結(jié)構(gòu)化數(shù)據(jù)，一般使用PQ基礎(chǔ)功能鼠標(biāo)簡單拖拽即可完成，下面以某期間欠費數(shù)據(jù)為例：A.合并多源數(shù)據(jù)。用追加查詢合并

非稅和代收費數(shù)據(jù)，或者導(dǎo)出后就直接合并為單簿多表。B.直接選擇菜單功能處理重復(fù)值錯誤值異常值。C.補齊缺失值。先排序后向下填充可補齊【學(xué)生性質(zhì)】和【學(xué)生類別】等字段的缺失值。D.拆分列。為更好的可視化效果，按分隔符拆分【院系】和【班級】等字段的代碼及名稱。E.格式轉(zhuǎn)換。將各字段數(shù)值轉(zhuǎn)換為標(biāo)準(zhǔn)的格式，如【代碼】【身份證】【學(xué)號】為文本格式、【日期】為日期格式、【金額】為貨幣格式。F.添加【年級】【收費大類】輔助字段并填充數(shù)據(jù)。各年級收費管理難度不同，對非稅和代收費需分類管理，故添加。重復(fù)【收費項目】列，將各收費項目替換為非稅和代收費，重復(fù)【班級代碼】列，提取前兩字符并添加后綴【級】，即可完成。

G.退出界面。若數(shù)據(jù)量超過工作表百萬行數(shù)上限，或超過幾十萬行可能受電腦性能影響而卡頓，須選擇【關(guān)閉并上載至】具體方式，因PQ是一種數(shù)據(jù)連接及查詢，若無需呈現(xiàn)在表格中，只占極少空間。H.動態(tài)預(yù)處理。點擊刷新， PQ 自動重復(fù)之前預(yù)處理步驟。

小規(guī)模數(shù)據(jù)用PT動態(tài)分析：一般幾十萬或百萬行以內(nèi)小規(guī)模數(shù)據(jù)預(yù)處理為規(guī)范的透視表數(shù)據(jù)源后，除了要非重復(fù)計數(shù)（如人數(shù)統(tǒng)計），用PT即可實現(xiàn)動態(tài)分析、多維交互細(xì)粒度可視化分析及輕量級數(shù)據(jù)挖掘，操作如前所述，拖拽幾下就可完成，十分簡單快捷，且透視報表自動美化，常可直接讀取所需數(shù)據(jù)，

可直接讀出各個維度的各種顆粒度欠費數(shù)據(jù)，為精準(zhǔn)催費管理提供數(shù)據(jù)支持。需說明的是，對于同源數(shù)據(jù)并非要做全所有透視報表或圖，滿足管理需要即可。

多源或較大規(guī)模數(shù)據(jù)用PP動態(tài)分析：多源數(shù)據(jù)或較大規(guī)模數(shù)據(jù)或非重復(fù)計數(shù)時，更適合用PP，先建模后分析，建模是將多源數(shù)據(jù)建立關(guān)系，以此整體作為數(shù)據(jù)源，再實現(xiàn)類似PT分析功能。

用透視圖可視化：透視圖與透視表一一對應(yīng)，用透視圖可實現(xiàn)分析結(jié)果可視化。創(chuàng)建時，PT從【分析】選項卡【數(shù)據(jù)透視圖】進(jìn)人，而PP從【主頁】選項卡【數(shù)據(jù)透視圖】進(jìn)人，按提示創(chuàng)建并美化，將同源數(shù)據(jù)創(chuàng)建的多個透視圖用切片器聯(lián)動控制，就可制作出智能可視化看板，可實現(xiàn)圖形化交互多維動態(tài)分析和輕量級數(shù)據(jù)挖掘。

本文分享了綜合應(yīng)用PQ、數(shù)據(jù)透視表和超級表格的BI功能對地方高校較大規(guī)模業(yè)財數(shù)據(jù)實行多維交互可視化動態(tài)分析和輕量級數(shù)據(jù)挖掘的技術(shù)方法。實踐表明，此方法易學(xué)易用使用成本低，能切實降低財務(wù)人員信息技能門檻，容易對接更專業(yè)的BI軟件，獲取分析結(jié)果方便，適合大多數(shù)地方高校業(yè)財數(shù)據(jù)特點，標(biāo)準(zhǔn)化的預(yù)處理及孤島數(shù)據(jù)的打破有效推動了數(shù)據(jù)治理，動態(tài)分析解決了人工重復(fù)勞動，極大提高了分析的速度和準(zhǔn)確度，多維交互可視化分析及輕量級數(shù)據(jù)挖掘可精準(zhǔn)穿透到業(yè)務(wù)問題的各種顆粒度數(shù)據(jù)，智能看板更是全面綜合交互、靈活精準(zhǔn)直觀，顯著提升了分析的廣度和深度，能幫助管理者減少主觀臆斷，顯著提升決策的科學(xué)性，夯實了應(yīng)用管理會計的數(shù)據(jù)基礎(chǔ)，賦能業(yè)財深度融合及管理效能，促進(jìn)會計職能拓展及數(shù)字化轉(zhuǎn)型。未來隨著AI的發(fā)展，這些工具將與之融合，操作更簡單功能更強大，從而推動財務(wù)工作更高效、更智能、更前瞻。

（作者單位：湖南省湘潭市湘潭醫(yī)衛(wèi)職業(yè)技術(shù)學(xué)院）