一種基于Power Query和透視表的數(shù)據(jù)處理方法

沈麗民 李健

摘要：“大數(shù)據(jù)”已成為當(dāng)今炙手可熱的術(shù)語，“大數(shù)據(jù)”的應(yīng)用服務(wù)著人們生活工作的方方面面。如何進(jìn)行大數(shù)據(jù)處理、大數(shù)據(jù)分析提高辦公效率是當(dāng)前學(xué)生任職后必備的一項基本素質(zhì)，該文以戰(zhàn)略支援部隊信息工程大學(xué)洛陽校區(qū)在資產(chǎn)統(tǒng)計過程中遇到的數(shù)據(jù)處理問題為牽引，提出一種基于Power Query和數(shù)據(jù)透視表的數(shù)據(jù)處理方法，分析解決問題的思路、討論解決問題的辦法，培養(yǎng)學(xué)生解決復(fù)雜數(shù)據(jù)問題的思維方式，進(jìn)而實現(xiàn)高效處理數(shù)據(jù)。

關(guān)鍵詞：數(shù)據(jù)處理;Power Query;數(shù)據(jù)透視表;案例驅(qū)動

中圖分類號：G642.4? ? ? 文獻(xiàn)標(biāo)識碼：A

文章編號：1009-3044（2021）36-0025-03

開放科學(xué)（資源服務(wù)）標(biāo)識碼（OSID）：

1 背景

當(dāng)前“大數(shù)據(jù)”“數(shù)字化”“人工智能”已成為信息社會的熱門話題，人們無時無刻不在享受著信息、數(shù)據(jù)、數(shù)字化給生活方式帶來的便利。付款方式、購物方式、營銷方式和出行方式等發(fā)生變化的背后是信息技術(shù)的巨大發(fā)展和大數(shù)據(jù)處理分析的結(jié)果。從海量數(shù)據(jù)中挖掘有用的數(shù)據(jù)，利用工具對數(shù)據(jù)進(jìn)行處理和分析進(jìn)而實現(xiàn)高效辦公是當(dāng)前學(xué)生應(yīng)具備的素質(zhì)[1-3]。一種基于Power Query和透視表的數(shù)據(jù)處理方法是以戰(zhàn)略支援部隊信息工程大學(xué)洛陽校區(qū)資產(chǎn)統(tǒng)計為案例，進(jìn)行分析、設(shè)計并進(jìn)行實踐檢驗，利用該方法進(jìn)行數(shù)據(jù)處理可以大大節(jié)省時間、人力和物力提高工作效率。

2 項目背景及需求

資產(chǎn)統(tǒng)計對于一個單位而言是一項非常重要而又超級煩瑣復(fù)雜的工作，牽扯得人多、周期長、頻率高、數(shù)據(jù)量大，在我校每年都會進(jìn)行資產(chǎn)統(tǒng)計核對。一直以來戰(zhàn)略支援部隊信息工程大學(xué)洛陽校區(qū)的做法如圖1所示，按照行政分工進(jìn)行逐級下發(fā)大數(shù)據(jù)量的Excel表，具體做法是學(xué)校資產(chǎn)負(fù)責(zé)人通過篩選復(fù)制粘貼把學(xué)校資產(chǎn)按系分成若干個Excel工作表分別發(fā)放給各系資產(chǎn)負(fù)責(zé)人，各系的資產(chǎn)負(fù)責(zé)人按照繼續(xù)按照上述方法把該系的資產(chǎn)按照教研室分成若干各Excel工作表發(fā)給教研室資產(chǎn)負(fù)責(zé)人，教研室負(fù)責(zé)人直接發(fā)放給資產(chǎn)保管人，保管人自行根據(jù)實際添加刪除修改核對然后提交，每個保管人都在大表中進(jìn)行修改自己保管的資產(chǎn)，然后上交給教研室負(fù)責(zé)人，教研室負(fù)責(zé)人根據(jù)保管人提交的內(nèi)容一條一條地修改，然后提交系負(fù)責(zé)人，系負(fù)責(zé)人在復(fù)制粘貼匯總各教研室的資產(chǎn)提交給學(xué)校負(fù)責(zé)人，學(xué)校負(fù)責(zé)人按照同樣的方法匯總各系的資產(chǎn)。整個資產(chǎn)統(tǒng)計的過程用時多工作量極大效率極低，而且是大量重復(fù)性的工作。

3 一種基于Power Query和數(shù)據(jù)透視表的數(shù)據(jù)處理的新方法

如何高效完成學(xué)校大數(shù)據(jù)量的資產(chǎn)統(tǒng)計，確保不漏一人不漏一項進(jìn)行增刪核對，提出了基于Power Query和數(shù)據(jù)透視表相結(jié)合的新方法，實踐結(jié)果表明該方法準(zhǔn)確高效，可以完美解決復(fù)雜的數(shù)據(jù)處理問題。整個過程校資產(chǎn)負(fù)責(zé)人可以直接和保管人對接，由校資產(chǎn)負(fù)責(zé)人把龐大的資產(chǎn)總表按照保管人進(jìn)行數(shù)據(jù)拆分，拆分成以保管人命名的若干工作簿，保管人進(jìn)行修改核對然后提交自己的工作簿，校資產(chǎn)負(fù)責(zé)人收集到所有資產(chǎn)保管人提交的工作簿進(jìn)行匯總即可。因為整個數(shù)據(jù)處理過程只有校資產(chǎn)管理人和保管人直接對接，少了很多中間環(huán)節(jié)，節(jié)省了大量人力、物力，技術(shù)實現(xiàn)方面數(shù)據(jù)拆分巧妙使用Excel的數(shù)據(jù)透視表和VBA編程實現(xiàn)，數(shù)據(jù)匯總則應(yīng)用Excel的Power Query功能。

3.1 數(shù)據(jù)拆分

數(shù)據(jù)拆分就是把如圖2（左）所示的總表，包含資產(chǎn)編號、資產(chǎn)名稱、規(guī)格、單價、保管人、部門和位置7個標(biāo)題項，按照保管人分成以保管人命名的若干個工作簿如圖2（右）所示，然后發(fā)放給每個保管人進(jìn)行修改核對等工作。

1）利用數(shù)據(jù)透視表巧妙拆分工作表

具體操作過程是首先添加輔助列，暫定列名稱為輔助列，該列內(nèi)容等于保管人姓名如圖3（a）所示，然后插入數(shù)據(jù)透視表，把資產(chǎn)總表中的前7項放入行標(biāo)簽，把輔助列作為篩選項，如圖3所示，最后進(jìn)行參數(shù)設(shè)置，包括不顯示分類匯總、對行列禁用如圖3（b）所示，這樣即可把總表拆分成以保管人名命的若干工作表，如圖4所示，但是并沒有達(dá)到拆分成若干工作簿的目標(biāo)，每個保管人一個工作簿才能確保數(shù)據(jù)獨立，方便增刪、修改核對和收集。

2）利用VBA代碼把多個工作表拆分成工作簿

選中利用數(shù)據(jù)透視表拆分完成的若干工作表，右鍵單擊查看代碼打開VBA對話框，完成如圖5（左）代碼編寫，單擊運行選項卡選擇運行子過程，即可實現(xiàn)把工作表拆分成工作簿，至此完成了按照保管人把資產(chǎn)總表拆分成若干個以保管人命名的工作簿，此時可以把工作簿分發(fā)給保管人完成增刪核對等工作了。

3.2 Power Query數(shù)據(jù)匯總

校資產(chǎn)管理人把保管人完善后的工作簿收集好放在一個文件夾中，就可以利用Power Query進(jìn)行一鍵完成匯總[4-5]。Power? Query是微軟提供的一款實現(xiàn)數(shù)據(jù)獲取和數(shù)據(jù)查詢工具，針對Excel 2010和2013版本需要去官網(wǎng)下載，自行安裝使用，2016版開始， 微軟把Power Query內(nèi)置為Excel的一個功能模塊，位于【數(shù)據(jù)】選項卡下的【獲取和轉(zhuǎn)】功能集中。Power Query支持的數(shù)據(jù)類型XLSX、CSV、XML、文本等，常用功能是多表（工作簿）匯總、合并查詢等。針對本文涉及的資產(chǎn)統(tǒng)計問題，按照數(shù)據(jù)-獲取和轉(zhuǎn)換-新建查詢-從文件夾-轉(zhuǎn)換數(shù)據(jù)如圖6所示，打開Power Query數(shù)據(jù)編輯刪除不必要的列，添加自定義列，通過Excel.Workbook把Content 里的內(nèi)容提取出來，如圖7（a）所示，取消“使用原始列名作為前綴”，單擊確定即可完成數(shù)據(jù)表內(nèi)容的匯總?cè)鐖D7（b），選擇關(guān)閉上載即可完成資產(chǎn)統(tǒng)計匯總工作，如果有新的工作簿提交，只需放在該文件夾，刷新即可完成數(shù)據(jù)匯總。

4 結(jié)束語

本文以戰(zhàn)略支援部隊信息工程大學(xué)洛陽校區(qū)每年資產(chǎn)統(tǒng)計過程中遇到的數(shù)據(jù)處理問題為牽引，提出了一種基于Power Query和透視表的數(shù)據(jù)處理方法，利用Excel數(shù)據(jù)透視表進(jìn)行把資產(chǎn)總表按照保管人拆分成若干個以保管人名命的sheet，然后利用簡單的VBA代碼把數(shù)據(jù)透視表拆分的sheet分離成以保管人名命的工作簿，最后利用Power Query進(jìn)行多工作簿匯總，實現(xiàn)高效處理數(shù)據(jù)的目的。經(jīng)實踐檢驗該方法不僅實現(xiàn)了高效拆分?jǐn)?shù)據(jù)，一鍵數(shù)據(jù)匯總，還可以自動更新，如果有新的工作簿提交，只需刷新總表即可把新的內(nèi)容更新至總表，大大節(jié)省了人力、物力和時間，真可謂一勞永逸。

參考文獻(xiàn)：

[1] 郭小鵬.PowerQuery在衛(wèi)星廣播電視播出數(shù)據(jù)統(tǒng)計分析的應(yīng)用[J].電子世界，2021（9）：186-187.

[2] 李蓬實，黎偉.大數(shù)據(jù)背景下面向經(jīng)管類專業(yè)的PowerQuery數(shù)據(jù)處理課程探索[J].教育現(xiàn)代化，2019，6（91）：162-163.

[3] 馮注龍.Excel之光[M].北京：電子工業(yè)出版社.2020：199-223.

[4] 秋葉，黃群金，章慧敏.和秋葉一起學(xué)Excel[M].2版.北京：人民郵電出版社，2020：77-86.

[5] 初道忠，陳瑞鑫.Excel規(guī)劃求解在數(shù)據(jù)分析與處理中的應(yīng)用[J].福建電腦，2021，37（3）：104-106.

【通聯(lián)編輯：謝媛媛】