將表格拆分為工作表

Microsoft Excel 有許多工具可用於從多個表（來自不同的工作表或來自不同的文件）收集數據：直接鏈接、函數 間接 （間接）、Power Query 和 Power Pivot 附加組件等。從路障的這一側看，一切看起來都不錯。

但是如果你遇到一個逆問題——將數據從一張表傳播到不同的工作表——那麼一切都會變得更加糟糕。 遺憾的是，目前 Excel 庫中還沒有用於這種數據分離的文明內置工具。 因此，您將不得不在 Visual Basic 中使用宏，或者使用宏記錄器 + Power Query 組合併稍加“文件優化”。

讓我們仔細看看這是如何實現的。

問題的形成

作為初始數據，我們有一個大小超過 5000 行的銷售表：

將表格拆分為工作表

任務：將表格中的數據按城市分佈在本書的不同頁面上。 那些。 在輸出中，您只需要在每張紙上獲取銷售在相應城市的表中的那些行：

將表格拆分為工作表

Prepare

為了不使宏代碼複雜化並使其盡可能易於理解，讓我們執行幾個準備步驟。

首先， 創建一個單獨的查找表，其中一列將列出您要為其創建單獨工作表的所有城市。 當然，該目錄可能不包含源數據中存在的所有城市，而僅包含我們需要報告的城市。 創建此類表的最簡單方法是使用命令 數據 - 刪除重複 （數據——刪除重複項） 用於列複製 城市 或功能 優尼克 （獨特的） – 如果您擁有最新版本的 Excel 365。

由於 Excel 中的新工作表默認在當前（上一個）工作表之前（左側）創建，因此按降序（從 Z 到 A）對該目錄中的城市進行排序也是有意義的 - 然後在創建後，城市工作表將按字母順序排列。

第二， п將兩個表都轉換為動態 （“智能”）以更輕鬆地與他們合作。 我們使用命令 主頁 – 格式為表格 （首頁——表格格式） 或鍵盤快捷鍵 按Ctrl+T. 在出現的選項卡上 構造函數 （設計） 讓我們打電話給他們 表普羅達吉 и 桌城， 分別：

將表格拆分為工作表

方法 1. 按表劃分的宏

在高級選項卡上 開發人員 （開發商） 點擊按鈕 Visual Basic中 或使用鍵盤快捷鍵 其他+F11. 在打開的宏編輯器窗口中，通過菜單插入一個新的空模塊 插入 - 模塊 並在那裡複製以下代碼：

Sub Splitter() For Each cell In Range("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).Copy Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub

這裡有一個循環 對於每個……下一個 實現了通過目錄單元格的通道 桌城, 對每個城市進行過濾（方法 自動篩選) 在原始銷售表中，然後將結果復製到新創建的工作表中。 一路上，創建的工作表被重命名為與城市相同的名稱，並在其上打開自動調整列的寬度以進行美化。

您可以在選項卡上的 Excel 中運行創建的宏 開發人員 按鍵 宏 （開發者——宏） 或鍵盤快捷鍵 其他+F8.

方法2.在Power Query中創建多個查詢

以前的方法雖然緊湊和簡單，但有一個明顯的缺點——在原始銷售表中進行更改時，宏創建的工作表不會更新。 如果需要即時更新，那麼您將不得不使用 VBA + Power Query 捆綁包，或者更確切地說，使用宏創建不僅包含靜態數據的工作表，而且還使用更新的 Power Query 查詢。

本例中的宏與前一個部分相似（它也有一個循環 對於每個……下一個 遍歷目錄中的城市），但在循環內部將不再進行過濾和復制，而是創建 Power Query 查詢並將其結果上傳到新工作表：

Sub Splitter2() For Each cell In Range("City table") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""更改類型"" = Table.TransformColumnTypes(Source , {{""Category"", type text}, {""Name"", type text}, {""City"", type text}, {""Manager"", type text}, {""Deal date "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""應用過濾器的行"" = Table.Se " & _ "lectRows(#""改變類型"", each ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""應用過濾器的行""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB;提供者 =Microsoft.Mashup.OleDb.1;數據源 =$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1"))。 QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False 。 SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value 下一個單元格 End Sub

啟動後，我們將按城市看到相同的工作表，但已經創建的 Power Query 查詢將形成它們：

將表格拆分為工作表

隨著源數據的任何變化，用鼠標右鍵更新相應的表就足夠了——命令 更新並保存 （刷新） 或使用按鈕一次批量更新所有城市 全部更新 選項卡 數據 （數據——全部刷新）.

  • 什麼是宏，如何創建和使用它們
  • 將工作簿工作表另存為單獨的文件
  • 將書中所有工作表中的數據收集到一個表中

發表評論