1.4 用一個例子說明報表自動化的實現過程

小勤：說了那么多，能不能先舉一個例子來介紹一下Power Query和Power Pivot是怎么實現報表自動化的?

大海：好的。下面舉一個例子。在這個過程里，有些步驟如果暫時不會操作也沒關系，先有一個總體印象，后面深入學習之后再回頭看一下就會覺得很簡單。

小勤：嗯。

大海：比如下面有這些數據：2015-2017年，每年有一個Excel工作簿，每個工作簿里有一個訂單表和一個訂單明細表，如圖1-4所示。

小勤：數據被分成多個文件，每個文件里有多個表的情況真是太常見了，這樣每年一個工作簿的還算少的了，很多時候是每個月有一個工作簿，合并它們是一件麻煩的事。

大海：那你以前碰到這種數據合并的問題是怎么辦的?

小勤：如果量少就手工復制一下，實在太多的話只能用VBA了，但VBA用得熟練的人畢竟少啊。從網上找的程序也不能適用于各種情況，比如有些適用于所有工作表都一樣的合并，在這里就不適用了。如果找人開發，那就面臨時間和成本的問題。

大海：下面看一看用Power Query是怎么解決的。

Step 01 為了避免跟原來的數據混在一起，我們在源數據的文件夾外面建了一個新的工作簿，用來專門進行數據處理，如圖1-5所示。

Step 02 打開新創建的工作簿，切換到“數據”選項卡，單擊“新建查詢”按鈕，在下拉菜單中選擇“從文件”命令，繼續在下一級菜單中選擇“從文件夾”命令，如圖1-6所示。

Step 03 彈出“文件夾”對話框，單擊“瀏覽”按鈕，在彈出的“瀏覽文件夾”對話框中選擇待合并數據所在的文件夾，單擊“確定”按鈕關閉“瀏覽文件夾”對話框，繼續單擊“確定”按鈕關閉“文件夾”選擇對話框，如圖1-7所示。

Step 04 此時，該文件夾內的所有文件都將被識別出來。由于每個文件里有不同的表，不能直接合并，因此，在彈出的對話框中單擊“編輯”按鈕，進入Power Query編輯界面，如圖1-8所示。

Step 05 在Power Query（查詢編輯器）里可以看到，3個Excel工作簿的信息都被讀了進來，包括工作簿的名稱、修改時間等。其中，工作簿內的數據在“Content”列里，如圖1-9所示。

Step 06 用Power Query里的簡單函數“Excel.Workbook”將這些工作簿的數據解析出來：在Power Query查詢編輯器里，切換到“添加列”選項卡，單擊“自定義列”按鈕，在彈出的對話框中輸入公式“=Excel.Workbook（[Content],true）”（提示，輸入時一定要注意區分大小寫），然后單擊“確定”按鈕，如圖1-10所示。

Step 07 展開工作簿數據：單擊上一步驟所添加“自定義”列右側的數據展開按鈕，取消勾選“使用原始列名作為前綴”復選框，單擊“確定”按鈕，如圖1-11所示。

Step 08 篩選出需要合并的“訂單”表：單擊“Item”列右側的數據篩選按鈕，在彈出的對話框中勾選“訂單”復選框，單擊“確定”按鈕，如圖1-12所示。

Step 09 展開訂單表數據：單擊“Data”列右側的數據展開按鈕，在彈出的對話框中取消勾選“使用原始列名作為前綴”復選框，單擊“確定”按鈕，如圖1-13所示。

Step 10 刪除不需要的列：在上一步驟展開數據后，訂單表中的所有數據都處于被選中的狀態，此時，用鼠標右鍵單擊任一列名位置，在彈出的菜單中選擇“刪除其他列”命令，如圖1-14所示。

小勤：這樣就把訂單表的數據都合并好了?

大海：對啊。

小勤：而且在整個過程中還可以按需要選擇工作簿、工作表等。

大海：對，到了這里，如果你只需要把合并的數據發給別人，就可以直接將數據返回Excel里了：切換到“開始”選項卡，單擊“關閉并上載”按鈕，如圖1-15所示。

小勤：這也只是做了一個數據合并啊，怎么就讓報表自動化了?

大海：報表自動化的關鍵是，以后要導入新數據的時候，你不需要再重復做一遍，而是一鍵刷新就自動得到最新的結果。比如，這里你將數據返回到Excel里了，以后源數據有了新的內容，只需要在Excel里用鼠標右擊該結果表的任意位置，在彈出的菜單中選擇“刷新”命令，就可以得到最新的數據，如圖1-16所示。

小勤：這個真是太好用了。如果還要進行進一步的處理呢?

大海：在Power Query里還可以進行各種處理，比如按需要選擇數據、合并、分組等，后面我們再一個功能一個功能地練習。現在我們繼續看一下如何進一步實現數據分析類報表的自動化。

小勤：那把訂單明細表也整合進來吧，這樣才更像一個綜合分析。

大海：好。其實訂單明細表的整合跟前面訂單表的整合方法類似。而且，選擇文件夾、解析工作簿數據等步驟是完全一樣的。

Step 11 在Excel中，切換到“數據”選項卡，單擊“顯示查詢”按鈕，在右側的“工作簿查詢”窗口中雙擊前面步驟創建的查詢，如圖1-17所示，進入Power Query界面。

Step 12 為方便后面與訂單明細表進行區分，在Power Query右側的“查詢設置”窗口中，將查詢名稱修改為“訂單表”，如圖1-18所示。

Step 13 在Power Query左側單擊向右展開按鈕，顯示所有查詢，用鼠標右鍵單擊“訂單表”，在彈出的菜單中選擇“復制”命令（第2個），如圖1-19所示。

Step 14 在“查詢”列表中選中剛復制出來的“訂單表（2）”，在右側查詢設置中將其名稱修改為“訂單明細”，如圖1-20所示。

Step 15 因為訂單明細表和訂單表的整合過程從篩選步驟開始有差異，所以只要修改“篩選的行”以后的步驟即可。在“查詢設置”的“應用的步驟”中，用鼠標右鍵單擊“篩選的行”步驟，在彈出的菜單中選擇“刪除到末尾”命令，如圖1-21所示。

Step 16 重新篩選要整合的數據：單擊“Item”列右側的篩選按鈕，在彈出的對話框中勾選“訂單明細”復選框，單擊“確定”按鈕，如圖1-22所示。

Step 17 單擊“Data”列右側的數據展開按鈕，在彈出的對話框中取消勾選“使用原始列名作為前綴”復選框，單擊“確定”按鈕，如圖1-23所示。

Step 18 刪除不需要的列：保持展開時訂單明細數據列處于被選中狀態，用鼠標右鍵單擊任一列名位置，在彈出的菜單中選擇“刪除其他列”命令，如圖1-24所示。

小勤：這樣把訂單明細表整合好了，真是方便啊！

大海：接下來我們看看怎么將兩個表的數據結合起來做分析。

小勤：這才是關鍵啊。如果是在Excel里那么操作起來可麻煩了，一般得將訂單表里的數據用VLOOKUP等函數讀到訂單明細表里，然后再按需要做篩選、數據透視等，而且，要從訂單表里讀這么多數據到訂單明細表里，那電腦不知道得多“卡”……

大海：現在我們就用Power Query或Power Pivot來徹底解決這個問題。首先看看在Power Query里怎么將訂單表里的數據讀到訂單明細表里。

Step 19 合并查詢：在Power Query中，保持選中“訂單明細表”查詢，切換到“開始”選項卡，單擊“合并查詢”按鈕，在彈出的對話框中選中“訂單明細”中的“訂單ID”列，選擇“訂單表”作為合并來源并選中其中的“訂單ID”列，單擊“確定”按鈕，如圖1-25所示。

Step 20 單擊“訂單表”列右側的數據展開按鈕，在彈出的對話框中取消勾選“使用原始列名作為前綴”復選框（若只需要得到訂單表里的部分列，則可以在其中按需選擇），單擊“確定”按鈕，如圖1-26所示。

小勤：這個操作太方便了，而且速度好快，單擊鼠標結果就出來了！

大海：用Power Query從一個表中讀取數據到另一個表中，既簡單又快捷。這個時候，我們的報表自動化又向前推進了一步，即可以從關聯的表格中匹配匯總相關數據了。但是，這還屬于數據整理的范疇，也就是說，如果某些部門或用戶需要你給出這些數據整合的報表，那么你已經達到目的了。同樣，導入新數據后，刷新一下就自動得到最新結果了。

小勤：怎樣能進一步實現數據分析的自動化?

大海：根據不同的需要，有不同的方法進行數據分析。如果是比較簡單的數據匯總、透視等，那么仍然可以直接基于Power Query已經合并的數據來進行，就像在Excel里將數據從訂單表讀取到訂單明細表后，就可以做數據透視了。但是，如果結合Power Pivot來做，那么會更加簡單，而且還不需要將一個表的數據讀取到另一個表里。

Step 21 為避免混淆，我們在Power Query里把合并查詢相關的步驟刪掉（若保留也不影響后續操作，但會因為訂單明細表里有訂單表里的所有列而顯得重復）：在“訂單明細”的“查詢設置”的“應用的步驟”中，用鼠標右鍵單擊“合并的查詢”步驟，在彈出的菜單中選擇“刪除到末尾”命令，如圖1-27所示。

Step 22 調整數據格式（Power Query和Power Pivot對數據格式要求相對嚴格）：選擇“訂單明細表”里的“單價”“數量”“折扣”等列，切換到“轉換”選項卡，單擊“數據類型”按鈕，在彈出的菜單中選擇“小數”命令，如圖1-28所示。

Step 23 將數據加載到數據模型中：切換到“開始”選項卡，單擊“關閉并上載”按鈕，在彈出的菜單中選擇“關閉并上載至”命令，如圖1-29所示。

在彈出的對話框中，選擇“僅創建連接”單選框（不需要將表的數據直接返回Excel中），勾選“將此數據添加到數據模型”復選框，單擊“加載”按鈕，如圖1-30所示。

Step 24 將“訂單表”也添加到數據模型中：用鼠標右鍵單擊“訂單表”查詢，在彈出的菜單中選擇“加載到”命令，如圖1-31所示。

在彈出的“加載到”對話框中，選中“僅創建連接”單選框（不需要將表的數據直接返回Excel中），勾選“將此數據添加到數據模型”復選框，單擊“加載”按鈕，如圖1-32所示。

Step 25 在Excel中，切換到“Power Pivot”選項卡，單擊“管理數據模型”按鈕，如圖1-33所示。

Step 26 在Power Pivot中，切換到“主頁”選項卡，單擊“關系圖視圖”按鈕，將“訂單明細”表中的“訂單ID”字段（列）拖曳到“訂單表”中的“訂單ID”字段（列）上，如圖1-34所示。

這樣，兩表之間就建立了關聯關系，如圖1-35所示，可以在后續的數據分析中直接調用相關表中的數據了。

小勤：就這樣拉一根線就可以了?

大海：對。根本不需要從一個表里將數據讀到另一個表里。接下來我們做一個簡單的數據透視表就能看出效果了。

Step 27 在Power Pivot中，切換到“主頁”選項卡，單擊“數據透視表”按鈕，如圖1-36所示。

在彈出的對話框中選中“新工作表”單選框，單擊“確定”按鈕，如圖1-37所示。

Step 28 構建數據透視表。比如將“訂單表”的“貨主地區”字段拖曳至“行”，將“訂單明細”表中的“數量”拖曳至“值”，從兩個表中導入的數據就直接結合在一起了，并生成了正確的分析結果，如圖1-38所示。

小勤：這太厲害了！以后再也不用一遍又一遍地從一個表到另一個表來來回回地讀數據了。

大海：這個也要看實際情況。有時候就是需要將不同表的數據整理到一起交給別人，那就得在Power Query里做合并。但是，如果要做的是分析結果，那就將數據加載到Power Pivot里并建立表間的關系，然后直接進行相關的分析。

小勤：對了，這樣生成的數據透視表也可以在源數據有更新的情況下一鍵刷新嗎?

大海：對，你可以用鼠標右鍵單擊數據透視表的任意單元格，在彈出的菜單中選擇“刷新”命令，如圖1-39所示。

這時，如果在“顯示查詢”狀態下可以看到數據透視表，則對數據透視表的刷新也會驅動Power Query中的查詢去刷新以獲取最新的數據，如圖1-40所示。

小勤：終于大致理解為什么Power Query和Power Pivot可以讓Excel普通用戶也能輕松實現報表自動化了，直接通過鼠標操作和一些簡單的函數就能實現原來必須用VBA才能實現的功能，甚至實現起來很困難的功能！

大海：這還只是一個開始。