問題的形成
對於大多數 Excel 用戶遲早會面臨的一種非常標準的情況，讓我們看一個漂亮的解決方案：您需要快速、自動地將大量文件中的數據收集到一個最終表格中。
假設我們有以下文件夾，其中包含幾個文件，其中包含來自分支城市的數據：
文件的數量無關緊要，將來可能會發生變化。 每個文件都有一個名為 銷售額數據表所在的位置：
當然，表中的行數（訂單）是不同的，但列的集合在任何地方都是標準的。
任務：將所有文件中的數據收集到一本書中，並在添加或刪除城市文件或表中的行時自動更新。 根據最終的合併表，那麼就可以構建任何報表、數據透視表、過濾排序數據等。主要是要能夠收集。
我們選擇武器
對於解決方案，我們需要最新版本的 Excel 2016（默認情況下已內置必要的功能）或安裝了免費插件的早期版本的 Excel 2010-2013 電源查詢 來自 Microsoft（在此處下載）。 Power Query 是一個超級靈活且超級強大的工具，用於將數據從外部加載到 Excel 中，然後對其進行剝離和處理。 Power Query 支持幾乎所有現有數據源——從文本文件到 SQL 甚至 Facebook 🙂
如果您沒有 Excel 2013 或 2016，那麼您將無法進一步閱讀（開個玩笑）。 在舊版本的 Excel 中，這樣的任務只能通過在 Visual Basic 中編寫宏（這對初學者來說非常困難）或單調的手動複製（這需要很長時間並且會產生錯誤）來完成。
步驟 1. 導入一個文件作為樣本
首先，讓我們以從一個工作簿中導入數據為例，讓 Excel “領悟”。 為此，請創建一個新的空白工作簿並...
- 如果您有 Excel 2016，則打開選項卡 數據 進而 創建查詢 - 從文件 - 從書 （數據 - 新查詢 - 來自文件 - 來自 Excel）
- 如果您有安裝了 Power Query 加載項的 Excel 2010-2013，則打開選項卡 電源查詢 並選擇它 從文件 - 從書 （來自文件 - 來自 Excel）
然後，在打開的窗口中，轉到我們的帶有報告的文件夾並選擇任何城市文件（哪個都沒有關係，因為它們都是典型的）。 幾秒鐘後，應該會出現 Navigator 窗口，您需要在左側選擇我們需要的工作表（Sales），其內容將顯示在右側：
如果單擊此窗口右下角的按鈕 下載 （加載），然後表格將立即以其原始形式導入工作表。 對於單個文件，這很好，但是我們需要加載很多這樣的文件，所以我們會稍微不同，點擊按鈕 更正 （編輯）. 之後，Power Query 查詢編輯器應該顯示在一個單獨的窗口中，其中包含我們來自書中的數據：
這是一個非常強大的工具，可以讓您將表格“完成”到我們需要的視圖。 即使是對其所有功能的表面描述也需要大約一百頁，但是，如果非常簡短，使用此窗口您可以：
- 過濾掉不必要的數據、空行、有錯誤的行
- 按一列或多列對數據進行排序
- 擺脫重複
- 按列劃分粘性文本（按分隔符、字符數等）
- 將文本按順序排列（刪除多餘的空格、正確的大小寫等）
- 以各種可能的方式轉換數據類型（將文本等數字轉換為普通數字，反之亦然）
- 轉置（旋轉）表並將二維交叉表擴展為平面表
- 使用 Power Query 中內置的 M 語言向表中添加其他列並使用其中的公式和函數。
- ...
例如，讓我們在表格中添加一個帶有月份文本名稱的列，以便以後更容易構建數據透視表報告。 為此，請右鍵單擊列標題 日期並選擇命令 列重複 （重複列），然後右鍵單擊出現的重複列的標題並選擇命令 轉換 - 月份 - 月份名稱:
應形成一個新列，其中包含每一行的月份文本名稱。 通過雙擊列標題，您可以將其重命名為 複製日期 為了更舒適 每月，例如。
如果在某些列中程序不能完全正確地識別數據類型，那麼您可以通過單擊每列左側的格式圖標來幫助它：
您可以使用簡單的過濾器排除有錯誤或空行的行，以及不必要的經理或客戶：
此外，所有執行的轉換都固定在右側面板中，它們始終可以回滾（交叉）或更改其參數（齒輪）：
輕盈優雅，不是嗎？
步驟 2. 讓我們將請求轉換為函數
為了隨後重複為每本導入的書籍所做的所有數據轉換，我們需要將創建的請求轉換為一個函數，然後將其依次應用於我們的所有文件。 要做到這一點實際上非常簡單。
在查詢編輯器中，轉到查看選項卡並單擊按鈕 高級編輯器 （查看 - 高級編輯器）. 應該打開一個窗口，我們之前的所有操作都將以 M 語言的代碼形式編寫。 請注意，我們為示例導入的文件的路徑在代碼中是硬編碼的：
現在讓我們做一些調整：
他們的意思很簡單：第一行 （文件路徑）=> 把我們的過程變成一個帶參數的函數 文件路徑，下面我們將固定路徑更改為此變量的值。
全部。 點擊 完 應該看到這個：
不要害怕數據消失了——事實上，一切正常，一切都應該是這樣的. 仍然要給它一個更易於理解的名稱（例如 獲取數據) 在字段右側的面板中 名字 你可以收穫 主頁 — 關閉並下載 （首頁 - 關閉並加載）. 請注意，我們為示例導入的文件的路徑在代碼中是硬編碼的。 您將返回到 Microsoft Excel 主窗口，但右側應出現一個面板，其中包含已創建的與我們的函數的連接：
步驟 3. 收集所有文件
所有最困難的部分都已過去，愉快和容易的部分仍然存在。 轉到選項卡 數據 - 創建查詢 - 從文件 - 從文件夾 （數據 - 新查詢 - 從文件 - 從文件夾） 或者，如果您有 Excel 2010-2013，則類似於選項卡 電源查詢. 在出現的窗口中，指定我們所有源城市文件所在的文件夾，然後單擊 OK. 下一步應該打開一個窗口，其中將列出在此文件夾（及其子文件夾）中找到的所有 Excel 文件以及每個文件的詳細信息：
點擊 更改 （編輯） 我們再次進入熟悉的查詢編輯器窗口。
現在我們需要使用我們創建的函數向表中添加另一列，該函數將從每個文件中“提取”數據。 為此，請轉到選項卡 添加列 - 自定義列 （添加列 - 添加自定義列） 在出現的窗口中，輸入我們的函數 獲取數據，為其指定每個文件的完整路徑作為參數：
點擊後 OK 創建的列應添加到右側的表中。
現在讓我們刪除所有不必要的列（如在 Excel 中，使用鼠標右鍵 - 清除)，只留下添加的列和帶有文件名的列，因為這個名稱（更準確地說，城市）對於每行的總數據很有用。
現在是“令人驚嘆的時刻”——使用我們的功能單擊添加列右上角帶有自己箭頭的圖標：
…取消選中 使用原始列名稱作為前綴 （使用原始列名作為前綴）並點擊 OK. 我們的函數將加載和處理來自每個文件的數據，遵循記錄的算法並將所有內容收集在一個公用表中：
為了完整美觀，您還可以從帶有文件名的第一列中刪除 .xlsx 擴展名——通過標準替換為“nothing”（右鍵單擊列標題—— 替代) 並將此列重命名為 城市. 並更正日期列中的數據格式。
全部！ 點擊 主頁 – 關閉並加載 （首頁 - 關閉並加載）. 查詢收集到的所有城市的所有數據都會以“智能表”格式上傳到當前的 Excel 工作表中：
創建的連接和我們的組裝函數不需要以任何方式單獨保存 - 它們以通常的方式與當前文件一起保存。
將來，文件夾（添加或刪除城市）或文件（更改行數）中的任何更改，直接右鍵單擊表格或右側面板中的查詢並選擇命令 更新並保存 （刷新） – Power Query 將在幾秒鐘內再次“重建”所有數據。
PS
修正案。 在 2017 年 XNUMX 月更新後，Power Query 學會瞭如何自行收集 Excel 工作簿，即不再需要製作單獨的函數——它會自動發生。 因此，不再需要本文中的第二步，整個過程變得明顯更簡單：
- 選擇 創建請求 - 從文件 - 從文件夾 - 選擇文件夾 - 確定
- 出現文件列表後，按 更改
- 在查詢編輯器窗口中，用雙箭頭展開二進制列，然後選擇要從每個文件中獲取的工作表名稱
就這樣！ 歌曲！
- 將交叉表重新設計為適合構建數據透視表的平面表
- 在 Power View 中構建動畫氣泡圖
- 將不同 Excel 文件中的工作表組合成一個的宏