Bảng Pivot trong Excel đóng vai trò then chốt 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à phân tích dữ liệu thành các cấu trúc có ý nghĩa. Người dùng MS Excel đã áp dụng rộng rãi chúng trong ngành dữ liệu.
Bạn có biết rằng bạn có thể tự động hóa các bảng tổng hợp của mình trong Excel và tạo chúng chỉ 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.
Sử dụng Tập dữ liệu thực hành
Bạn có thể tải xuống và sử dụng bộ 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 điều chỉnh 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 thiết yếu 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 bảng tổng hợp cơ bản theo cách thủ công với các thành phần sau:
- Lọc: Khu vực
- Hàng: Danh mục phụ
- Cột: Tiểu bang
- giá trị: Việc bán hàng
Trục cuối sẽ trông như sau:
Tuy nhiên, bạn có thể để VBA tự động làm thay vì chuẩn bị thủ công.
Cách tự động tạo Pivot Table trong Excel
Để tự động hóa các bảng tổng hợp của bạn bằng VBA, hãy mở một tệp Excel mới và đổi tên các trang tính như sau:
- Tờ đầu tiên: vĩ mô
- Tờ thứ hai: Dữ liệu
Các vĩ mô trang tính chứa tập lệnh macro, trong khi Dữ liệu sheet chứa dữ liệu của bạn. Trên trang tính 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 Gán Macro.
Trong hộp thoại sau, nhấp vào tên macro của bạn và nhấp vào Ok. Bước này gán macro cho hình dạng.
1. Mở Trình chỉnh sửa mã hóa VBA của Excel
Nhấn Tổ hợp phím + 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, theo sau 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 nên bạn có thể đặt tên mô-đun như sau:
sub pivot_demo()
Tên mô-đun kết thúc bằng kết thúc phụlà lệnh kết thúc của một mô-đun:
End Sub
2. Khai báo 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 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 Variant
Bạn sẽ sử dụng các biến này cho các mục sau:
- Trang tính: Trang đích, nơi VBA sẽ tạo một trục.
- Trang tính: Bảng dữ liệu.
- PvtCache: Bộ nhớ đệm trục giữ trục.
- Bảng Pvt: Đối tượng bảng tổng hợp.
- Phạm vi Pvt: 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. Loại bỏ 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 một cách đáng kể.
Sử dụng đoạn mã sau:
On Error Resume Next
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
Ở đâu:
- Khi Lỗi Tiếp tục Tiếp theo: Mệnh đề này triệt tiêu mọi lỗi thời gian chạy.
- Đăng kí: Ứng dụng đề cập đến MS Excel.
- Cảnh báo hiển thị: Thuộc tính DisplayAlerts xác định xem 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 xem có cập nhật các thay đổi trong thời gian thực hay chỉ sau 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 tham số DisplayAlerts và ScreenUpdating bằng cách đặt giá trị của chúng thành Sai.
Ở cuối mã, bạn có thể bật lại chúng bằng cách đặt giá trị là Thật.
4. Xóa mọi Pivot Sheet 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 bảng tổng hợp hiện có và sử dụng VBA để tạo một trang tính mới để lưu trữ bảng 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.
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 chạy qua từng trang tính trong sổ làm việc và lưu 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 chạy qua mọi trang tính trong sổ làm việc hiện tại, tìm kiếm một trang tính 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. Sau 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 sht1
Worksheets.Add.Name = "Pivot"
5. Xác định nguồn dữ liệu và Pivot Sheet
Đ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 lối 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 gần đây nhất
Phần mã này hoạt động linh hoạt vì nó tăng kích thước cho 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
- Phạm vi Pvt: PvtRange tham chiếu toàn bộ phạm vi dữ liệu cho trục
7. Tạo Bộ đệm ẩn Pivot và Bảng Pivot
Bộ đệm ẩn giữ bảng tổng hợp; do đó, bạn cần tạo bộ đệm trước khi tạo bảng tổng hợp. Bạn phải sử dụng tham chiếu cú pháp của VBA để tạo bộ nhớ đệm trục trong trang tính Pivot.
Bằng cách tham chiếu bộ nhớ đệ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 tính Dữ liệu và Pivot.
- PivotCaches.Tạo: Cú pháp mặc định để tạo bộ đệm trục.
- Loại nguồn: Vì bạn có dữ liệu trong sổ làm việc, nên bạn có thể định nghĩa nó là xlCơ sở dữ liệu. Một số tùy chọn khác bao gồm xlCủng cố, xlBên ngoàihoặc xlPivotTable.
- Nguồn dữ liệu: Bạn có thể tham chiếu phạm vi trục trước đó làm dữ liệu nguồn.
- TạoPivotTable: Lệnh mặc định để tạo bảng tổng hợp.
- Bảng điểm đến: Bạn cần chỉ định trang tính và tham chiếu ô mà bạn muốn tạo trục.
- Tên bảng: Chỉ định tên bảng tổng hợp.
- TạoPivotTable: Lệnh mặc định để tạo bảng tổng hợp trong bộ đệm ẩn 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 nên 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ường trục 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 các giá trị tổng hợp:
With ActiveSheet.PivotTables("MUODemoTable").PivotFields("Sales")
.Orientation = xlDataField
.Function = xlSum
End With
Cần lưu ý rằng bạn phải tham chiếu bảng kích hoạt (bảng Pivot), 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 toán các tập hợp 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 Pivot 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 trang tính Pivot trong sổ làm việc của mình, bạn sẽ thấy rằng một bảng tổng hợp mới đã sẵn sàng để bạn xem lại.
Nếu bạn muốn xem từng bước thực thi 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ể thấy 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ã Pivot Table
Pivot không chỉ giới hạn ở 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ỉ bằng 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 trong Python một cách hiệu quả và thực hiện cấu trúc trục tương tự như Excel một cách dễ dàng.
