問題的形成
數據透視表是 Excel 中最令人驚嘆的工具之一。 但到目前為止，不幸的是，沒有任何版本的 Excel 可以即時執行簡單且必要的操作，例如為位於不同工作表或不同表中的多個初始數據范圍構建摘要：
在開始之前，讓我們澄清幾點。 先驗地，我認為我們的數據滿足以下條件：
- 表可以有任意數量的包含任何數據的行，但它們必須具有相同的標題。
- 帶有源表的工作表上不應有額外的數據。 一張紙——一張桌子。 要控制，我建議您使用鍵盤快捷鍵 按Ctrl+結束，這會將您移動到工作表中最後使用的單元格。 理想情況下，這應該是數據表中的最後一個單元格。 如果當你點擊 按Ctrl+結束 表格右側或下方的任何空單元格都會突出顯示 - 刪除表格右側或表格下方的這些空列或表格後的行並保存文件。
方法 1：使用 Power Query 為數據透視表構建表
從 Excel 2010 版本開始，有一個免費的 Power Query 加載項可以收集和轉換任何數據，然後將其作為構建數據透視表的源。 在這個插件的幫助下解決我們的問題一點也不難。
首先，讓我們在 Excel 中創建一個新的空文件——將在其中進行組裝，然後將在其中創建一個數據透視表。
然後在選項卡上 數據 （如果您有 Excel 2016 或更高版本）或在選項卡上 電源查詢 （如果您有 Excel 2010-2013）選擇命令 創建查詢 - 從文件 - Excel （獲取數據——從文件——Excel） 並指定要收集的表的源文件：
在出現的窗口中，選擇任何工作表（不管是哪一個），然後按下面的按鈕 更改 （編輯）:
Power Query 查詢編輯器窗口應在 Excel 頂部打開。 在面板上窗口的右側 請求參數 刪除除第一個以外的所有自動創建的步驟 – 資源 （資源）:
現在我們看到所有工作表的一般列表。 如果文件中除了數據表之外還有一些其他的側表，那麼在此步驟中，我們的任務是僅選擇需要從中加載信息的那些表，使用表頭中的過濾器排除所有其他表：
刪除除列之外的所有列 數據通過右鍵單擊列標題並選擇 刪除其他列 （消除 其他欄目）:
然後，您可以通過單擊列頂部的雙箭頭（複選框 使用原始列名稱作為前綴 你可以把它關掉）：
如果你做的一切都正確，那麼此時你應該看到收集的所有表格的內容一個在另一個之下：
仍然使用按鈕將第一行提升到表頭 使用第一行作為標題 （使用第一行作為標題） 選項卡 首頁 （家） 並使用過濾器從數據中刪除重複的表頭：
保存使用命令完成的所有內容 關閉並加載 – 關閉並加載… （關閉並加載 - 關閉並加載到…） 選項卡 首頁 （家），然後在打開的窗口中，選擇選項 僅連接 （僅限連接）:
一切。 它仍然只是建立一個摘要。 為此，請轉到選項卡 插入 - 數據透視表 （插入 - 數據透視表）, 選擇選項 使用外部數據源 （使用外部數據源）然後點擊按鈕 選擇“連接”，我們的要求。 通過將我們需要的字段拖到行、列和值區域中，以完全標準的方式進一步創建和配置樞軸：
如果將來源數據發生變化或添加了更多存儲表，那麼使用命令更新查詢和我們的摘要就足夠了 全部刷新 選項卡 數據 （數據——全部刷新）.
方法二、我們在宏中用UNION SQL命令聯合表
我們的問題的另一種解決方案是由這個宏表示，它使用命令為數據透視表創建一個數據集（緩存） 統一 SQL 查詢語言。 此命令組合數組中指定的所有表 工作表名稱 將書頁合併到一個數據表中。 也就是說，我們不是從不同的工作表物理複製和粘貼範圍，而是在計算機的 RAM 中執行相同的操作。 然後宏添加一個具有給定名稱的新工作表（變量 結果表名稱) 並根據收集的緩存在其上創建一個完整的 (!) 摘要。
要使用宏，請使用選項卡上的 Visual Basic 按鈕 開發人員 （開發商） 或鍵盤快捷鍵 其他+F11. 然後我們通過菜單插入一個新的空模塊 插入 - 模塊 並在那裡複製以下代碼：
Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Variant '結果透視表將顯示的工作表名稱 ResultSheetName = "Pivot" '工作表數組帶有源表的名稱 SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") '我們為來自 SheetsNames 的工作表的表和 ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Next i Set objRS = CreateObject("ADODB.Recordset") objRS .Open Join$(arSQL, "UNION ALL"), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) End With '重新創建工作表以顯示生成的數據透視表 On Error Resume Next Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo 噸。 Name = ResultSheetName '在此工作表上顯示生成的緩存摘要 Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivot objPivotCache.CreatePivotTable TableDestination:=wsPivot.Range("A3") Set objPivotCache = Nothing Range("A3").Select End With End Sub 然後可以使用鍵盤快捷鍵運行完成的宏 其他+F8 或選項卡上的宏按鈕 開發人員 （開發者——宏）.
這種方法的缺點：
- 數據未更新，因為緩存與源表沒有連接。 如果更改源數據，則必須再次運行宏並再次構建摘要。
- 更改張數時，需要編輯宏代碼（數組 工作表名稱).
但最終我們得到了一個真正成熟的數據透視表，它建立在來自不同工作表的多個範圍之上：
瞧！
技術說明： 如果您在運行宏時收到類似“未註冊提供程序”的錯誤，那麼很可能您安裝了 64 位版本的 Excel 或安裝了不完整的 Office 版本（無 Access）。 要解決此問題，請替換宏代碼中的片段：
提供商=Microsoft.Jet.OLEDB.4.0；
到：
提供者=Microsoft.ACE.OLEDB.12.0；
並從 Microsoft 網站的 Access 下載並安裝免費的數據處理引擎 – Microsoft Access Database Engine 2010 Redistributable
方法 3：從舊版本的 Excel 合併數據透視表嚮導
這種方法有點過時，但仍然值得一提。 正式地說，在直到 2003 年（包括 XNUMX 年）的所有版本中，數據透視表嚮導中都有一個選項可以“為多個合併範圍構建數據透視表”。 然而，不幸的是，以這種方式構建的報告只會是一個真正的完整摘要的可憐表象，並且不支持傳統數據透視表的許多“籌碼”：
在這樣的數據透視表中，字段列表中沒有列標題，沒有靈活的結構設置，使用的函數集是有限的，總的來說，這一切都與數據透視表不太相似。 也許這就是為什麼從 2007 年開始，Microsoft 在創建數據透視表報告時從標準對話框中刪除了此功能。 現在此功能僅可通過自定義按鈕使用 數據透視表嚮導（數據透視表嚮導），如果需要，可以通過以下方式將其添加到快速訪問工具欄 文件——選項——自定義快速訪問工具欄——所有命令 （文件——選項——自定義快速訪問工具欄——所有命令）:
單擊添加的按鈕後，您需要在嚮導的第一步選擇適當的選項：
然後在下一個窗口中，依次選擇每個範圍並將其添加到常規列表中：
但是，同樣，這不是一個完整的總結，所以不要期望太多。 我只能在非常簡單的情況下推薦這個選項。
- 使用數據透視表創建報表
- 在數據透視表中設置計算
- 什麼是宏，如何使用它們，在哪裡複製 VBA 代碼等。
- 從多張紙到一張的數據收集（PLEX 插件）