李森

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

關(guān)鍵詞：數(shù)據(jù)匯總;商務(wù)智能;大數(shù)據(jù)分析;合并歷史數(shù)據(jù);表結(jié)構(gòu);逆透視;規(guī)范化

中圖分類號：TP3-05 文獻(xiàn)標(biāo)識碼：A

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

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

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

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

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

1問題的提出

假如我們需要整理北京市各市區(qū)社會消費品零售額的歷史數(shù)據(jù)，現(xiàn)在有2007年2月-2019年6月的100多張數(shù)據(jù)表f數(shù)據(jù)來源：北京市統(tǒng)計局），我們對比一下第一期和最后一期報表的表結(jié)構(gòu)，如圖1所示。

總結(jié)一下2019年6月期報表中表結(jié)構(gòu)的幾處變化：

1.1從行來看

1）表頭由兩行變?yōu)橐恍?

2）取消了四大功能區(qū);

3）“亦莊開發(fā)區(qū)”更名為“北京經(jīng)濟(jì)技術(shù)開發(fā)區(qū)”;

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

5）密云、延慶兩縣撤縣設(shè)區(qū);

6）門頭溝區(qū)位置提前;

7）市區(qū)名稱前加上了空格。

1.2從列來看

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

由于表結(jié)構(gòu)的變化，所有數(shù)據(jù)的位置均發(fā)生了改變，用VBA取固定位置的數(shù)據(jù)顯然不可取，而用PQ，可以分步實現(xiàn)數(shù)據(jù)匯總。

2第一步：獲取數(shù)據(jù)源

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

1）一共有138個excel文件，統(tǒng)一存放在“根目錄＼數(shù)據(jù)源”文件夾中，根目錄根據(jù)存放路徑自動調(diào)整，比如我現(xiàn)在用的根目錄是：

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

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

3第二步：獲取表頭行數(shù)

從2013年2月開始，數(shù)據(jù)表的表頭由兩行變?yōu)榱艘恍?，先總結(jié)出各甲欄版本的表頭行數(shù)，然后讓各excel文件自動查找對應(yīng)的表頭行數(shù)信息，實現(xiàn)原理類似于excel工作表函數(shù)vlookup。

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

4第三步：批處理表格——二維表轉(zhuǎn)一維表

這是數(shù)據(jù)匯總的最關(guān)鍵一步：維度轉(zhuǎn)換，要想把不同結(jié)構(gòu)的數(shù)據(jù)表匯總在一起，所需要做的不是簡單的表格的拼接（追加），因為不同時期的報表中各行、各列意義不同，比如下面這兩張表拼接在一起就出錯了，因為后一張表的第2、3列數(shù)據(jù)互換了位置，這時候需要先把二維表統(tǒng)一轉(zhuǎn)化為一維表，再進(jìn)行各張表行、列的規(guī)范化。

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

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

PQ中可以對多個數(shù)據(jù)表進(jìn)行批量操作，批量逆透視之后的結(jié)果是：

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

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

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

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

6第五步：規(guī)范甲欄（行號）

規(guī)范甲欄和賓欄首先需要有規(guī)范依據(jù)，以甲欄為例，需要整理出各個甲欄版本間的對應(yīng)關(guān)系，經(jīng)整理發(fā)現(xiàn)甲欄版本共有6次調(diào)整，共有7個版本，每一次調(diào)整都有一定的原因：

需要注意的是，整理表結(jié)構(gòu)需要以最新一版為準(zhǔn)，比如密云縣、延慶縣已撤縣設(shè)區(qū)，所以歷史匯總數(shù)據(jù)中統(tǒng)一稱密云區(qū)、延慶區(qū)，同理不再保留老東城、老西城、崇文、宣武區(qū)數(shù)據(jù)，因為人們已經(jīng)不再關(guān)注這些數(shù)據(jù)了，如果需要的話可以另行添加。

對甲欄規(guī)范表進(jìn)行二維表轉(zhuǎn)一維表操作，可得到如下報表：

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

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

7第六步：規(guī)范賓欄（列號）

同理可對賓欄進(jìn)行規(guī)范化轉(zhuǎn)換，轉(zhuǎn)換后的結(jié)果是：

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

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

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

在甲欄和賓欄的規(guī)范化操作中，包含著模式分解和數(shù)據(jù)清洗的過程，這都是數(shù)據(jù)整理和數(shù)據(jù)庫搭建必不可少的環(huán)節(jié)。

8第七步：單位轉(zhuǎn)換

在對甲欄、賓欄規(guī)范化轉(zhuǎn)換以后其實數(shù)據(jù)匯總表已經(jīng)基本做好了，但還有一個重要的問題需要解決：整理各期數(shù)據(jù)表發(fā)現(xiàn)有的時期數(shù)據(jù)單位是萬元，有的時期是億元，必須統(tǒng)一起來。

規(guī)則很簡單，對于“增速”數(shù)據(jù)不用處理，“絕對值”數(shù)據(jù)中最近5年的年末數(shù)據(jù)因為比較大，當(dāng)期報表中使用了億元單位，不用處理，其他時期的絕對值都是萬元單位，統(tǒng)一乘以0.0001轉(zhuǎn)換為億元單位。轉(zhuǎn)換之后的結(jié)果是：

9數(shù)據(jù)分析

一維表是數(shù)據(jù)分析的基礎(chǔ)，當(dāng)?shù)玫綒v史數(shù)據(jù)的一維表之后，就可以進(jìn)行各類透視，然后可進(jìn)行相應(yīng)數(shù)據(jù)分析，透視操作用PQ或數(shù)據(jù)透視表都可以，比如對“度量”透視得到如下結(jié)果：繼續(xù)透視“數(shù)期”得到：透視“市區(qū)”得到：

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

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

【通聯(lián)編輯：李雅琪】