用Excel進行數(shù)據(jù)匯總，再平常不過的需求

單表匯總：最常用操作的應(yīng)該是分類匯總、透視表等。這是最理想化的方式，所有數(shù)據(jù)都在一張工作表，匯總 so easy。但理想很豐滿，現(xiàn)實卻很骨感，這種方式在工作中不多見，多表匯總是我們不得不面對的需求。

多表匯總：不管是本工作簿還是跨工作簿，多重合并計算數(shù)據(jù)區(qū)域也能解決。用這種方式創(chuàng)建的透視表一個明顯的短板就是行字段只有一個且只能是數(shù)據(jù)源的左側(cè)第一列。如果只是針對一個視角進行匯總，這也算是一個解決辦法。但N個工作表的數(shù)據(jù)都整合到一塊了，又只能統(tǒng)計一個視角多少有點遺憾。

你當然可以在數(shù)據(jù)源設(shè)輔助列將多列信息連接在一個單元格做為行字段，但歸根到底還是只有一個行字段，起不到轉(zhuǎn)換統(tǒng)計視角的作用，治標不治本。

SQL連接語句：這當然稱得上多表匯總的殺手锏，不管個工作表是否跨工作簿，用這種方式創(chuàng)建的透視表就和單表透視一樣簡單。隨意轉(zhuǎn)換統(tǒng)計視角，生成多種報表，似乎很完美。

還是舉例來說吧。五個銷售部，分為五個工作簿，每個工作簿按月填寫銷售明細，要求進行多視角匯總。這個要求應(yīng)該不算過份，不管是OLEDB還是Microsoft Query,寫入SQL連接語句完美解決。

但是，寫入的語句得有這么多↓

你已經(jīng)看出來了，5個工作簿只有15個工作表，如果是一年的數(shù)據(jù)呢？就是60個工作表，也就是說，SQL連接語句得有60條。

如果用power query完成呢？只用一條公式就夠了

其實，power query就是一款數(shù)據(jù)抓取工具，微軟內(nèi)置到Excel也不是一兩年了。就剛才那個例子，別說5個工作簿15個工作表，再增加十倍百倍的數(shù)據(jù)也就是一條公式的事。把power query稱為SQL連接語句的終結(jié)者也不算太夸張。

我用的版本是office365訂閱版，Excel已經(jīng)內(nèi)置power query，名字有點長，我們叫他為PQ吧。如果你的Excel沒有PQ怎么辦呢？

我們首先領(lǐng)略一下PQ的強大，文章最后才附PQ安裝方法，要不要安裝PQ當然你說了算。

文末有本章演示數(shù)據(jù)及成果獲取方式

聲明：本章演示數(shù)據(jù)均為虛構(gòu)，并非真實數(shù)據(jù)

首先介紹一下待匯總的數(shù)據(jù)

這是5個銷售部門的銷售數(shù)據(jù)，分別位于5個工作簿，每個工作簿的工作表按月進行記錄。銷售部成立時間不一，所以有的記錄是3個月，有的記錄是4個月，有的記錄是6個月?，F(xiàn)在要求對5個銷售部進行以月度、各銷售部、銷售地區(qū)、產(chǎn)品名稱等不同視角的匯總。

面對這樣的數(shù)據(jù)，這樣的要求，你想到了什么？對，數(shù)據(jù)透視表，快速轉(zhuǎn)換統(tǒng)計視角不正是它的強項么！

將所有的分表快速整合到一個工作表，并且數(shù)據(jù)同步分表的更新。都到這一步了用分類匯總還是用數(shù)據(jù)透視表還不是隨你高興么。

這想想都激動！

我們開始吧！

建立匯總工作簿

新建一個Excel文檔，命名為“匯總”。

打開“匯總”工作簿，添加文件夾。

操作路徑：數(shù)據(jù)→獲取數(shù)據(jù)→來自文件→從文件夾→瀏覽到“E:\銷售數(shù)據(jù)記錄”→確定。

確定后在彈出的對話框中已經(jīng)加載文件夾中的所有工作簿，點擊“轉(zhuǎn)換數(shù)據(jù)”。

這是自動打開的power query編輯器，在“Name”列執(zhí)行篩選，只保留幾個銷售部的勾選。

可以看到數(shù)據(jù)有N列，其實我們真正需要的就是前兩列。其它列顯示的是文件創(chuàng)建時間，加載時間及后綴名等信息，刪除他們。

操作路徑：Ctrl鍵配合選中前兩列→刪除列→刪除其他列。

添加自定義列，輸入公式，整個操作只需要輸入這一個公式。

操作路徑：添加列→自定義列→輸入公式：=Excel.Workbook([Content])→確定

展開“Data”，自段名自動顯示為“自定義.Data”。

操作路徑：點擊自定義旁的按鈕→只選擇Data→確保單選為“展開”→確定

展開自定義.Data

操作路徑：點擊展開自定義.Data旁按鈕→單選展開→選擇所有列→確定

數(shù)據(jù)合并成功，但是在中間行有N處分表的字段，這些無用行將影響我們的匯總，有多少個分表就有多少無用行，下一步解決這個問題。

隱藏多余行

操作路徑：修改自定義列的字段名→對任意一列執(zhí)行篩選→取消不需要內(nèi)容的勾選

好了，數(shù)據(jù)清爽了，上載至Excel.

操作路徑：主頁→關(guān)閉并上載

我們來看看Excel中的情況，5個銷售部的數(shù)據(jù)全部整合，日期最大為六月，估且稱這張表為“總表”。

剛才說過，分表更新同步到總表，我們來增加一張工作表試試。剛才最大月份為六月，通過篩選得知，該數(shù)據(jù)屬銷售二部?，F(xiàn)在比如銷售二部增加了七月份的數(shù)據(jù)。

關(guān)閉銷售二部工作簿，來到總表執(zhí)行刷新。通過篩選，七月份數(shù)據(jù)已同步到總表。如果是采用SQL連接，還得再增加一行語句才能更新，而現(xiàn)在，我們要做的只是刷新一下而已。

如果你利用這個整合的數(shù)據(jù)制作了多個透視圖表，分表有更新時執(zhí)行全部刷新，則所有的圖表呈現(xiàn)最新的結(jié)果。

好吧，剛才說過，都到這一步了，怎么操作就看我們心情了，我就用透視表吧。整個過程只寫了一個公式而已，你說PQ是不是要上天。

PQ如何安裝

好了，你決定要安裝PQ了？如下圖所示，在今日頭條搜索“powerquery安裝”即可。

安裝過程不用再說了吧，互聯(lián)網(wǎng)時代，一切就是這么簡單。