合并匯總大量數據表的一般方法

李森

摘要：系統的、規范的數據庫是大數據分析的基礎，但在實際中，很多政府機構和企業都沒有建立數據庫的條件，大量數據以Excel表格的形式存放在管理人員的電腦上，查詢、匯總極為不便。如何整理規范統計數據是一個常見的、典型的棘手問題，在整理大量歷史數據的時候，表結構的變化給數據使用者造成了很大的麻煩，傳統的Excel函數、VBA編程等方法面對這種情況也是束手無策，這個時候我們可以使用微軟的商務軟件PowerBI，從分析袁結構入手，方便地進行數據的匯總和整理工作。

關鍵詞：數據匯總;商務智能;大數據分析;合并歷史數據;表結構;逆透視;規范化

中圖分類號：TP3-05 文獻標識碼：A

文章編號：1009-3044（2019）33-0257-04

在工作中我們經常會遇到合并多個數據表的情況，比如多個歷史時期數據表的合并，多個分公司報表的匯總，當數量不多時，我們可以依次打開各個數據表，手動復制粘貼至一張表中，但如果涉及的數據表太多時，這個方法就行不通了，或者說過于煩瑣，比如說有時候剛整理好各月工資，又需要整理各月獎金，那么之前進行的所有步驟又需要再做一遍了。當這樣的重復性工作我們做了好多年之后，就應該考慮其他的方法了，我們需要在不打開文件的情況下提取其中的數據，把人從煩瑣的數據整理工作中解放出來。

那么怎么辦呢？過去我曾嘗試通過VBA編程提取數據，對于結構完全一致的報表這個方法是行得通的，但是這里有一個問題，由于制表人不同或者表結構變化等原因，每張表中的同一個數據可能并不在同一個位置，比如說有的表里工資在單元格B2，有的表里可能在B3，或者C2，或者其他什么地方，有時候發給分公司的工整的報表，收上來的時候卻變得花花綠綠的，有的甚至面目全非，填表人會隨意地刪行、加行、刪列、加列、調整行列順序、合并單元格、修改單元格格式，等等，不一而足，原因是他那里有特殊情況，或者為了方便填報等，但是無規矩不成方圓，隨意更改表結構方便了填表人，卻給數據整理工作帶來了大麻煩，需要匯總整理的數據表必須嚴格限定報表格式，最好是在下發報表的時候就鎖定表結構，但是我們不能指望每一個報表設計者都會鎖定操作，或者數據表的匯總者和設計者不是同一個人，匯總人收到各張報表的時候發現表結構已經不一致了，這個時候，VBA也是無可奈何的。

這時候，我們可以用Excel插件power query解決這個問題。PQ是微軟公司的商務智能Power BI工具之一，它和powerpivot構成了PBI的核心，它的操作原理和語法類似于SQL語言和OLEDB數據連接，但使用起來更加靈活簡單，是面向普通的數據工作者的大眾化BII具，不需要編寫太多復雜煩瑣的代碼，就可以輕松實現數據匯總分析。PQ是一種入門級的大數據分析工具，我認為以后會是數據工作者必備的技能之一，就像現在的Excel一樣基礎。

在這篇文章中我以一個例子通俗易懂地介紹一下用PQ進行數據表合并的一般思路和操作過程。

1問題的提出

假如我們需要整理北京市各市區社會消費品零售額的歷史數據，現在有2007年2月-2019年6月的100多張數據表f數據來源：北京市統計局），我們對比一下第一期和最后一期報表的表結構，如圖1所示。

總結一下2019年6月期報表中表結構的幾處變化：

1.1從行來看

1）表頭由兩行變為一行;

2）取消了四大功能區;

3）“亦莊開發區”更名為“北京經濟技術開發區”;

4）崇文區、宣武區分別合并到東城區、西城區;

5）密云、延慶兩縣撤縣設區;

6）門頭溝區位置提前;

7）市區名稱前加上了空格。

1.2從列來看

第2、3列數據互換位置，從“本月、累計、本月增速、累計增速”變成了“本月、本月增速、累計、累計增速”。

由于表結構的變化，所有數據的位置均發生了改變，用VBA取固定位置的數據顯然不可取，而用PQ，可以分步實現數據匯總。

2第一步：獲取數據源

首先需要整理出“數據源表”，這張表里列出了各個數據表的基本情況，需要說明幾點：

1）一共有138個excel文件，統一存放在“根目錄＼數據源”文件夾中，根目錄根據存放路徑自動調整，比如我現在用的根目錄是：

PQ會自動從“F：＼20190810合并數據表＼數據源＼200702.xl-sx”“F：＼20190810合并數據表＼數據源＼200703.xlsx”等文件中取數，而下載壓縮包后可解壓存在任一地址，比如放在“D：＼P0練習/合并數據表”中，PQ就會自動從“D：＼PQ練習＼合并數據表＼數據源＼200702.xlsx”“D：＼PQ練習＼合并數據表＼數據源＼200703.x1-sx”等文件中取數，不用手動修改。

2）在十多年的時間里，報表結構經過了多次變化，二維表的表結構包括主欄（甲欄）、賓欄（乙欄、表頭）兩個方面，數據源表中列出了每一個excel文件中報表的甲欄版本和賓欄版本，這需要用戶自己去總結，PQ就是根據文件位置和甲欄、賓欄版本去各個文件中的特定單元格取數。

3第二步：獲取表頭行數

從2013年2月開始，數據表的表頭由兩行變為了一行，先總結出各甲欄版本的表頭行數，然后讓各excel文件自動查找對應的表頭行數信息，實現原理類似于excel工作表函數vlookup。

這一步是為了便于理解接下來的處理過程，當PQ熟練以后可省略這一步。

4第三步：批處理表格——二維表轉一維表

這是數據匯總的最關鍵一步：維度轉換，要想把不同結構的數據表匯總在一起，所需要做的不是簡單的表格的拼接（追加），因為不同時期的報表中各行、各列意義不同，比如下面這兩張表拼接在一起就出錯了，因為后一張表的第2、3列數據互換了位置，這時候需要先把二維表統一轉化為一維表，再進行各張表行、列的規范化。

首先需要去掉表結構區域，僅保留數據區域。2007年2月報表需要去掉第一列和前兩行，2019年6月報表需要去掉第一列和第一行，這時候步驟二中的表頭行數就派上了用場（不過在熟練以后其實這一步可省略，不影響結果）。

二維表轉一維表所用的方法叫作“逆透視”，也就是把多行、多列的數據轉化為一列，道理很簡單，把橫向放置的數據轉置，豎向排列在一起就可以了，excel中進行此項操作較麻煩，在PQ中可以很方便地進行逆透視操作。

PQ中可以對多個數據表進行批量操作，批量逆透視之后的結果是：

這時候就可以對不同時期的報表進行拼接（追加）了。

5第四步：展開（拼接）報表

這一步很簡單，把規范化之后的二維表拼接在一起就可以了。

但是對于表中的每個數據我們還不知道是什么意思，所以下一步需要進行甲欄（行號）和賓欄（列號）的轉換（規范化）。

6第五步：規范甲欄（行號）

規范甲欄和賓欄首先需要有規范依據，以甲欄為例，需要整理出各個甲欄版本間的對應關系，經整理發現甲欄版本共有6次調整，共有7個版本，每一次調整都有一定的原因：

需要注意的是，整理表結構需要以最新一版為準，比如密云縣、延慶縣已撤縣設區，所以歷史匯總數據中統一稱密云區、延慶區，同理不再保留老東城、老西城、崇文、宣武區數據，因為人們已經不再關注這些數據了，如果需要的話可以另行添加。

對甲欄規范表進行二維表轉一維表操作，可得到如下報表：

以此為依據可以對第四步展開的報表中的行號進行規范化整理，比如甲欄版本1-5的第3行是北京市數據，而版本6和7的第2行是北京市數據，版本2的第28行是東城區數據，版本7的第3行是東城區數據，版本1的第4行是“首都功能核心區”數據，最新版中已不再保留，所以在轉化結果就順便把它去掉了。

甲欄規范化之后，甲欄版本列也就沒用了，可以刪除。規范化過程用到的方法叫作“合并查詢”，類似于excel工作表函數vlookup，但好處是可以設置多個查詢依據（多列），其實就是關系型數據庫中所說的主鍵，只有同時指定“甲欄版本”和“行號”才能確定唯一的“市區”，可以理解為一個二元函數，f（x1，x2）由x1、x2共同決定的，兩個條件缺一不可。

7第六步：規范賓欄（列號）

同理可對賓欄進行規范化轉換，轉換后的結果是：

需要注意的是，賓欄中包含著4個屬性，比如2019年6月報表的第2列數據，它表示：2019年當年（屬性3）的2月當月（屬性2）的社會消費品零售額（屬性1）的絕對值（屬性4）數據，而第4列表示：2019年當年（屬性3）的2月累計（屬性2）的社會消費品零售額（屬性1）的絕對值（屙陛4）數據。

對于數據的某些屬性，我們在特定情況下可以省略說明，比如這個例子中的所有數據都是關于“社會消費品零售額”這個指標的，用戶都知道，所以這個屬性可以省略，可以把文件名命名為“社會消費品零售額歷史數據”就可以了，用戶就知道這個文件里面的所有數據都是關于這個指標的，這就是所謂數據的“上下文context”。

但是你不能把文件命名為“海淀區社零額”，因為這里面有非海淀區的數據，但是可以命名為“北京市各區數據”“北京市各區歷史數據”等，從這里也可以看出，數據表的文件名就是其中所有數據的一個或幾個共同的屬性名。

在甲欄和賓欄的規范化操作中，包含著模式分解和數據清洗的過程，這都是數據整理和數據庫搭建必不可少的環節。

8第七步：單位轉換

在對甲欄、賓欄規范化轉換以后其實數據匯總表已經基本做好了，但還有一個重要的問題需要解決：整理各期數據表發現有的時期數據單位是萬元，有的時期是億元，必須統一起來。

規則很簡單，對于“增速”數據不用處理，“絕對值”數據中最近5年的年末數據因為比較大，當期報表中使用了億元單位，不用處理，其他時期的絕對值都是萬元單位，統一乘以0.0001轉換為億元單位。轉換之后的結果是：

9數據分析

一維表是數據分析的基礎，當得到歷史數據的一維表之后，就可以進行各類透視，然后可進行相應數據分析，透視操作用PQ或數據透視表都可以，比如對“度量”透視得到如下結果：繼續透視“數期”得到：透視“市區”得到：

可以看出，我們平時看到的大部分數據表都是一種數據透視表，透視表是對抽象事物和數據的特征的高度概括和呈現，通過序列數據的對比，使人們很容易抓住同類事物的本質和特征，例如，通過時間序列數據對比可總結出事物的變化規律，通過空間序列數據對比可概括出事物分布規律，通過時空數據對比可抓住事物的薄弱點、突破口、差距所在，更好地促進整體發展。

在數據分析報告中，透視表比單純的文字描述效果要好得多，很多所謂的數據分析報告就是對透視表的文字描述，比如某某指標實現了多少、增長了多少、占比是多少之類，幾大段亂七八糟的文字可能還不如一張表講得清楚，因此說一表勝千言，而進一步講，數據圖又比數據表的表現能力更好，因此還有人說文不如表，表不如圖，確實如此。在數據透視表的基礎上，可以在excel中制作數據圖，而用微軟PBI的組件powerview、百度ECharts等工具可實現更多更豐富的數據圖表展現形式。

【通聯編輯：李雅琪】