Nội dung
Microsoft Excel có nhiều công cụ để thu thập dữ liệu từ một số bảng (từ các trang tính khác nhau hoặc từ các tệp khác nhau): liên kết trực tiếp, hàm GIÁN TIẾP (GIÁN TIẾP), Power Query và các tiện ích bổ sung Power Pivot, v.v. Từ phía này của chướng ngại vật, mọi thứ có vẻ tốt.
Nhưng nếu bạn gặp phải một vấn đề ngược - trải dữ liệu từ một bảng sang các trang khác nhau - thì mọi thứ sẽ buồn hơn nhiều. Hiện tại, không có công cụ tích hợp sẵn văn minh nào để tách dữ liệu như vậy trong kho vũ khí của Excel, rất tiếc. Vì vậy, bạn sẽ phải sử dụng macro trong Visual Basic hoặc sử dụng kết hợp trình ghi macro + Power Query với một chút "tinh chỉnh tệp" sau đó.
Chúng ta hãy xem xét kỹ hơn cách điều này có thể được thực hiện.
Công thức của vấn đề
Chúng tôi có dữ liệu ban đầu như một bảng với kích thước hơn 5000 hàng để bán hàng:
Nhiệm vụ: phân phối dữ liệu từ bảng này theo thành phố trên các trang riêng biệt của cuốn sách này. Những thứ kia. ở đầu ra, bạn chỉ cần lấy trên mỗi tờ những hàng đó từ bảng nơi bán hàng ở thành phố tương ứng:
Chuẩn bị
Để không làm phức tạp mã macro và làm cho nó dễ hiểu nhất có thể, chúng ta hãy thực hiện một vài bước chuẩn bị.
Đầu tiên, tạo một bảng tra cứu riêng, trong đó một cột sẽ liệt kê tất cả các thành phố mà bạn muốn tạo các trang tính riêng biệt. Tất nhiên, thư mục này có thể không chứa tất cả các thành phố có trong dữ liệu nguồn, mà chỉ chứa những thành phố mà chúng tôi cần báo cáo. Cách dễ nhất để tạo một bảng như vậy là sử dụng lệnh Dữ liệu - Xóa các bản sao (Dữ liệu - Loại bỏ các bản sao) cho bản sao cột City hoặc chức năng ĐỘC NHẤT (ĐỘC NHẤT) - nếu bạn có phiên bản Excel 365 mới nhất.
Vì các trang tính mới trong Excel được tạo theo mặc định trước (bên trái) trang hiện tại (trước đó), nên việc sắp xếp các thành phố trong thư mục này theo thứ tự giảm dần (từ Z đến A) cũng có ý nghĩa. các trang tính sẽ được sắp xếp theo thứ tự bảng chữ cái.
Thứ hai, пchuyển đổi cả hai bảng thành động (“Thông minh”) để làm việc với họ dễ dàng hơn. Chúng tôi sử dụng lệnh Trang chủ - Định dạng dưới dạng bảng (Trang chủ - Định dạng dưới dạng Bảng) hoặc phím tắt Ctrl+T. Trên tab xuất hiện xây dựng (Thiết kế) hãy gọi cho họ bảngProdaji и BànThành Phố, tương ứng:
Phương pháp 1. Macro để chia theo trang tính
Trên tab Nâng cao nhà phát triển (Nhà phát triển) nhấn nút Visual Basic hoặc sử dụng phím tắt Khác+F11. Trong cửa sổ trình chỉnh sửa macro mở ra, hãy chèn một mô-đun trống mới thông qua menu Chèn - Mô-đun và sao chép mã sau vào đó:
Bộ tách phụ () Cho mỗi ô trong phạm vi ("таблГорода") Phạm vi ("таблПродажи"). Trường tự động lọc: = 3, Criteria1: = cell.Value Range ("таблПродажи [#All]"). SpecialCells (xlCellTypes) Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets ("Данные"). ShowAllData End Sub Đây với một vòng lặp Đối với mỗi… Tiếp theo triển khai đoạn văn qua các ô của thư mục BànThành Phố, nơi mà nó được lọc cho mỗi thành phố (phương pháp Bộ lọc tự động) trong bảng doanh số ban đầu và sau đó sao chép kết quả vào trang tính mới tạo. Trên đường đi, trang tính đã tạo được đổi tên thành cùng tên của thành phố và tính năng tự động điều chỉnh chiều rộng của các cột cho đẹp được bật trên đó.
Bạn có thể chạy macro đã tạo trong Excel trên tab nhà phát triển nút Macros (Nhà phát triển - Macro) hoặc phím tắt Khác+F8.
Phương pháp 2. Tạo nhiều truy vấn trong Power Query
Phương pháp trước đó, vì tất cả sự nhỏ gọn và đơn giản của nó, có một nhược điểm đáng kể - các trang tính được tạo bởi macro không được cập nhật khi các thay đổi được thực hiện trong bảng bán hàng ban đầu. Nếu việc cập nhật nhanh chóng là cần thiết, thì bạn sẽ phải sử dụng gói VBA + Power Query, hoặc đúng hơn, tạo bằng macro không chỉ trang tính có dữ liệu tĩnh mà còn cả các truy vấn Power Query đã cập nhật.
Macro trong trường hợp này tương tự một phần với macro trước đó (nó cũng có chu kỳ Đối với mỗi… Tiếp theo để lặp qua các thành phố trong thư mục), nhưng bên trong vòng lặp sẽ không còn lọc và sao chép nữa, mà tạo truy vấn Power Query và tải kết quả của nó lên một trang tính mới:
Sub Splitter2 () Cho Mỗi ô Trong Phạm vi ("Bảng thành phố") ActiveWorkbook.Queries.Add Name: = cell.Value, Formula: = _ "let" & Chr (13) & "" & Chr (10) & "Nguồn = Excel.CurrentWorkbook () {[Name = "" TableSales ""]} [Nội dung], "& Chr (13) &" "& Chr (10) &" # "" Changed Type "" = Table.TransformColumnTypes (Nguồn , {{"" Category "", type text}, {"" Name "", type text}, {"" City "", type text}, {"" Manager "", type text}, {"" Thỏa thuận date "", type datetime}, {"" Cost "", type number}}), "& Chr (13) &" "& Chr (10) &" # "" Các hàng có bộ lọc được áp dụng "" = Table.Se "& _" lectRows (# "" Đã thay đổi loại "", mỗi ([Thành phố] = "" "& ô.Value &" "")) "& Chr (13) &" "& Chr (10) &" trong "& Chr (13) &" "& Chr (10) &" # "" Các hàng có áp dụng bộ lọc "" "ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add (SourceType: = 0, Source: = _" OLEDB; Nhà cung cấp = Microsoft.Mashup.OleDb.1; Nguồn dữ liệu = $ Workbook $; Location = "& cell.Value &"; Extended Properties = "" "" "" _, Destination: = Range ("$ A $ 1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array ("CHỌN *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 Ô tiếp theo End Sub Sau khi ra mắt, chúng ta sẽ thấy các trang tính giống nhau theo thành phố, nhưng các truy vấn Power Query đã được tạo sẽ tạo thành chúng:
Với bất kỳ thay đổi nào trong dữ liệu nguồn, chỉ cần chuột phải cập nhật bảng tương ứng là đủ - lệnh Cập nhật & Lưu (Làm mới) hoặc cập nhật hàng loạt tất cả các thành phố cùng một lúc bằng cách sử dụng nút Cập nhật Tất cả chuyển hướng Ngày (Dữ liệu - Làm mới tất cả).
- Macro là gì, cách tạo và sử dụng chúng
- Lưu các trang tính trong sổ làm việc thành các tệp riêng biệt
- Thu thập dữ liệu từ tất cả các trang của cuốn sách vào một bảng