巧用PowerQuery實現不規則數據整理

◆吳濤／丹江口市審計局

筆者在工作中經常會碰到如企業股東信息、不動產登記信息等，將同一公司或不動產的所有權利人的姓名、身份證號信息集中保存在一個單元格中（如圖1所示），不便于后期的數據分析。無論用常規的哪種方法來進行數據標準化，往往難度較大且費時費力。這里以企業股東信息為例，介紹運用Excel 2016中的Power Query三分鐘完成數據的整理。

圖1

一、Power Query簡介

Power Query是Excel 2016及Power BI Desktop中內置的一個免費工具，主要用于數據獲取、數據轉換、數據處理，入手極快，不需要太多的學習時間，僅使用Power Query圖形界面就足以完成80%的日常任務。

二、數據整理方法

1.如圖2所示，將光標定位到表格中，然后選擇“數據—從表格”，將表格引入Power Query中。

圖2

2.如圖3所示，選中“姓名（股東）、證件號碼（股東）”兩列，選擇“逆透視列”,將二維表轉為一維表。

圖3

3.如圖4所示，選中“值”列，選擇“拆分列—按分隔符”，將每個人的姓名、身份證號拆分出來。其中：分隔符為“逗號”，拆分為“行”。

圖4

4.由于每條記錄除“值”不同外，存在重復，在進行升維操作時會造成取值混亂。為了唯一區分每條記錄，需要給姓名和身份證號分別進行編號加以區分。如圖5所示，選擇“證件號碼（法定代表人）”“屬性”執行“轉換—分組依據”，對“所有行”進行計數分組。

圖5

5.添加自定義列，錄入公式=Table.AddIndexColumn（[計數],"分組編號",1），作用是對每個子表添加從1開始的索引編號，并將該列命名為“分組編號”。刪除“計數”列，然后展開“自定義”列，完成分類索引編號,如圖6所示。

圖6

6.選中“屬性”列，執行“轉換—任意列—透視列”，即可得到所需標準表，如圖7所示。■

圖7