Tìm hiểu cách tự động tạo bảng tổng hợp trong Excel với VBA
Bảng tổng hợp trong Excel đóng vai trò quan trọng trong việc làm cho dữ liệu dễ hiểu và dễ hiểu hơn. Một bảng tổng hợp có thể cô đọng và kết hợp dữ liệu thành các cấu trúc có ý nghĩa. Người dùng MS Excel đã áp dụng chúng rộng rãi trong ngành công nghiệp dữ liệu.
Bạn có biết rằng bạn có thể tự động hóa bảng tổng hợp của mình trong Excel và tạo chúng bằng một cú nhấp chuột không? MS Excel tích hợp tốt với VBA và đã trở thành một công cụ tuyệt vời để tự động hóa các tác vụ lặp đi lặp lại.
Đây là cách bạn có thể tự động hóa bảng tổng hợp bằng macro trong MS Excel VBA.
Mục Lục
Sử dụng Tập dữ liệu thực hành
Bạn có thể tải xuống và sử dụng tập dữ liệu giả từ Tableau để làm theo tập lệnh VBA trong hướng dẫn này. Mã VBA sẽ hoạt động với bất kỳ tập dữ liệu nào khác, với một vài chỉnh sửa cơ bản. Trước khi bắt đầu, hãy đảm bảo rằng bạn đã bật macro trong sổ làm việc Excel của mình.
Có một số cột cần thiết mà bạn có thể sử dụng trong bảng tổng hợp. Để hiểu các sắc thái và cấu trúc cuối cùng của bảng, bạn có thể tạo một bảng tổng hợp cơ bản theo cách thủ công với các phần tử sau:
- Lọc: Vùng đất
- Hàng: Hạng mục phụ
- Cột: Tiểu bang
- Giá trị: Việc bán hàng
Trụ cuối sẽ trông như sau:
Tuy nhiên, bạn có thể để VBA làm điều đó tự động thay vì chuẩn bị thủ công.
Cách tự động tạo bảng tổng hợp trong Excel
Để tự động hóa bảng tổng hợp của bạn bằng VBA, hãy mở tệp Excel mới và đổi tên trang tính như sau:
- Trang đầu tiên: Macro
- Tờ thứ hai: Dữ liệu
Các Macro trang tính chứa tập lệnh macro, trong khi Dữ liệu trang tính chứa dữ liệu của bạn. Trên trang macro, bạn có thể chèn bất kỳ hình dạng nào bạn chọn và gán macro cho nó. Nhấp chuột phải vào hình dạng và nhấp vào Chỉ định Macro.
Trong hộp thoại sau, hãy nhấp vào tên macro của bạn và nhấp vào Ok. Bước này chỉ định macro cho hình dạng.
1. Mở Trình soạn thảo mã hóa VBA trong Excel
Nhấn Alt + F11 để mở trình chỉnh sửa mã. Khi bạn đang ở trong trình chỉnh sửa mã, hãy nhấp chuột phải vào tên tệp, tiếp theo là Chèn và Mô-đun. Điều quan trọng cần nhớ là bạn sẽ viết tất cả mã VBA trong một mô-đun trước khi thực thi nó.
Bạn nên sử dụng tên mô-đun phù hợp với mục đích của mã. Vì đây là bản demo, bạn có thể xác định tên mô-đun như sau:
sub pivot_demo()Tên mô-đun kết thúc bằng Kết thúc Sublà lệnh kết thúc của một mô-đun:
End Sub2. Khai báo các biến
Trong mô-đun, hãy bắt đầu bằng cách khai báo các biến để lưu trữ một số giá trị do người dùng xác định mà bạn sẽ sử dụng trong tập lệnh. Bạn có thể dùng Lờ mờ câu lệnh để khai báo các biến, như sau:
Dim PSheet As Worksheet, DSheet As Worksheet
Dim PvtCache As PivotCache
Dim PvtTable As PivotTable
Dim PvtRange As Range
Dim Last_Row As Long, Last_Col As Long
Dim sht1 as VariantBạn sẽ sử dụng các biến này cho những việc sau:
- Bảng tính: Trang tính đích, nơi VBA sẽ tạo một trục xoay.
- DSheet: Bảng dữ liệu.
- PvtCache: Một bộ nhớ đệm pivot giữ pivot.
- PvtTable: Đối tượng bảng tổng hợp.
- PvtRange: Một phạm vi dữ liệu cho trục.
- Last_Row và Last_Col: Hàng và cột được điền cuối cùng trong bảng dữ liệu (DSheet).
- Sht1: Biến này là một biến thể.
3. Ngăn chặn cảnh báo và tin nhắn
Các lỗi, cảnh báo và thông báo không cần thiết làm chậm mã VBA của bạn. Bằng cách chặn các thông báo như vậy, bạn có thể tăng tốc quá trình đáng kể.
Sử dụng mã sau:
On Error Resume NextWith Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
Ở đâu:
- Khi có lỗi Tiếp tục tiếp theo: Điều khoản này ngăn chặn bất kỳ lỗi thời gian chạy nào.
- Đăng kí: Ứng dụng đề cập đến MS Excel.
- DisplayAlerts: Thuộc tính DisplayAlerts xác định có hiển thị cảnh báo hay không.
- Cập nhật màn hình: Thuộc tính này xác định có cập nhật các thay đổi trong thời gian thực hay chỉ khi mã đã chạy xong.
Khi mã này chạy, nó sẽ chặn mọi cảnh báo, cảnh báo và thông báo mà Excel sẽ hiển thị theo cách khác. Bạn có thể tắt các thông số DisplayAlerts và ScreenUpdating bằng cách đặt giá trị của chúng thành Sai.
Đến cuối mã, bạn có thể bật lại chúng bằng cách đặt giá trị là ĐÚNG VẬY.
4. Xóa mọi trang tính Pivot hiện có
Để tạo một bảng tổng hợp mới, bạn có hai tùy chọn. Đầu tiên, xóa trang tính tổng hợp hiện có và sử dụng VBA để tạo trang tính mới để lưu trữ trang tính tổng hợp. Ngoài ra, bạn có thể sử dụng một trang tính hiện có để giữ trục xoay.
Trong hướng dẫn này, hãy tạo một trang tính tổng hợp mới để lưu trữ bảng tổng hợp.
Các cho mỗi vòng lặp chuyển động qua từng trang tính trong sổ làm việc và lưu trữ tên trang tính trong sht1 Biến đổi. Bạn có thể sử dụng bất kỳ tên biến nào (sht1) để giữ tên trang tính. Vòng lặp xoay vòng qua mọi trang tính trong sổ làm việc hiện tại, tìm kiếm một trang có tên cụ thể (Trục).
Khi tên trang tính khớp, nó sẽ xóa trang tính và chuyển sang trang tính tiếp theo. Khi mã kiểm tra tất cả các trang tính, nó sẽ thoát khỏi vòng lặp và chuyển sang phần tiếp theo của mã, phần này sẽ thêm một trang tính mới, Trục.
Đây là cách bạn có thể làm điều đó:
For Each sht1 In ActiveWorkbook.Worksheets
If sht1.Name = "Pivot" Then
sht1.Delete
End If
Next sht1Worksheets.Add.Name = "Pivot"
5. Xác định Nguồn dữ liệu và Pivot Sheets
Điều cần thiết là tạo các biến để lưu trữ các tham chiếu của Pivot và Data sheet. Chúng hoạt động như các phím tắt mà bạn có thể tham khảo trong suốt phần còn lại của mã.
Set PSheet = Worksheets("Pivot")
Set DSheet = Worksheets("Data")6. Xác định hàng và cột được sử dụng cuối cùng
Phần này của mã hoạt động động, vì nó tăng kích thước hàng và cột được điền cuối cùng trong dữ liệu.
Last_Row = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
Last_Col = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PvtRange = DSheet.Cells(1, 1).Resize(Last_Row, Last_Col)Ở đâu:
- Hàng cuối cùng: Biến để lưu trữ số hàng được điền cuối cùng, tức là 9995
- Last_Col: Biến để lưu trữ số cột được điền cuối cùng, tức là 21
- PvtRange: PvtRange tham chiếu toàn bộ phạm vi dữ liệu cho trục
7. Tạo Pivot Cache và Pivot Table
Bộ đệm tổng hợp giữ bảng tổng hợp; do đó, bạn cần tạo bộ đệm ẩn trước khi tạo bảng tổng hợp. Bạn phải sử dụng các tham chiếu cú pháp của VBA để tạo bộ đệm tổng hợp trong Pivot sheet.
Bằng cách tham chiếu bộ đệm tổng hợp, bạn cần tạo một bảng tổng hợp. Là một phần của bảng tổng hợp, bạn có thể xác định trang tính, tham chiếu ô và tên của bảng tổng hợp.
Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PvtRange).CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), TableName:="MUODemoTable")
Set PvtTable = PvtCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="MUODemoTable")Ở đâu:
- ActiveWorkbook: Sổ làm việc hiện tại nơi bạn có Trang dữ liệu và Tổng hợp.
- PivotCaches.Create: Cú pháp mặc định để tạo bộ đệm tổng hợp.
- SourceType: Vì bạn có dữ liệu trong sổ làm việc, bạn có thể định nghĩa nó là xlDatabase. Một số tùy chọn khác bao gồm xlConsolidation, xlExternalhoặc xlPivotTable.
- Nguồn dữ liệu: Bạn có thể tham chiếu phạm vi tổng hợp trước đó làm dữ liệu nguồn.
- CreatePivotTable: Lệnh mặc định để tạo bảng tổng hợp.
- TableDestination: Bạn cần chỉ định tham chiếu trang tính và ô mà bạn muốn tạo trục xoay.
- TableName: Chỉ định tên bảng tổng hợp.
- CreatePivotTable: Lệnh mặc định để tạo bảng tổng hợp trong bộ đệm tổng hợp.
8. Chèn hàng, cột, bộ lọc và giá trị
Vì bảng tổng hợp đã sẵn sàng, bạn cần bắt đầu thêm các tham số trong bộ lọc, hàng, cột và giá trị tổng hợp. Bạn có thể sử dụng VBA trục trường lệnh để bắt đầu khai báo các chi tiết.
Để thêm giá trị bộ lọc:
With ActiveSheet.PivotTables("MUODemoTable").PivotFields("Region")
.Orientation = xlPageField
End With Để thêm giá trị hàng:
With ActiveSheet.PivotTables("MUODemoTable").PivotFields("Sub-Category")
.Orientation = xlRowField
End WithĐể thêm giá trị cột:
With ActiveSheet.PivotTables("MUODemoTable").PivotFields("State")
.Orientation = xlColumnField
End WithĐể thêm giá trị tổng hợp:
With ActiveSheet.PivotTables("MUODemoTable").PivotFields("Sales")
.Orientation = xlDataField
.Function = xlSum
End WithĐiều cần lưu ý là bạn phải tham chiếu bảng hoạt động (Pivot sheet), theo sau là tên bảng tổng hợp và tên biến. Khi bạn cần thêm (các) bộ lọc, (các) hàng và (các) cột, bạn có thể chuyển đổi giữa các cú pháp khác nhau, bao gồm các cú pháp sau:
- xlPageField: Để thêm bộ lọc.
- xlRowField: Để thêm hàng.
- xlRowField: Để thêm cột.
Cuối cùng, bạn có thể sử dụng xlDataField lệnh để tính tổng giá trị. Bạn có thể sử dụng các hàm tổng hợp khác như xlSum, xlAverage, xlCount, xlMax, xlMin và xlProduct.
9. Chạy Mã VBA Excel để tạo các Pivots tự động
Cuối cùng, khi toàn bộ chương trình đã sẵn sàng, bạn có thể chạy nó bằng cách nhấn F5 hoặc nhấp vào chơi cái nút. Khi bạn quay lại Pivot sheet trong sổ làm việc của mình, bạn sẽ thấy rằng một bảng pivot mới đã sẵn sàng để bạn xem lại.
Nếu bạn muốn xem thực thi từng bước về cách lệnh mã phát ra từng dòng, bạn có thể điều hướng đến trình chỉnh sửa mã và nhấn F8 vài lần. Bằng cách này, bạn có thể xem từng dòng mã hoạt động như thế nào và cách VBA tự động tạo các trục của bạn.
Tự động học cách viết mã bảng Pivot
Pivots không bị giới hạn chỉ trong MS Excel. Các ngôn ngữ lập trình như Python cho phép bạn tạo các trục được tối ưu hóa chỉ với một vài dòng mã.
Tối ưu hóa dữ liệu không thể dễ dàng hơn thế này. Bạn có thể chọn và chọn các lệnh của mình một cách hiệu quả bằng Python và hoàn thành cấu trúc trục xoay giống Excel tương tự một cách dễ dàng.