Bảng tổng hợp VBA trong Excel
Pivot Table là trung tâm của việc tóm tắt báo cáo của một lượng lớn dữ liệu. Chúng tôi cũng có thể tự động hóa quá trình tạo bảng tổng hợp thông qua mã hóa VBA. Chúng là một phần quan trọng của bất kỳ báo cáo hoặc bảng điều khiển nào, trong excel có thể dễ dàng tạo bảng bằng một nút nhưng trong VBA chúng ta phải viết một số mã để tự động hóa bảng tổng hợp của mình, trước excel 2007 và các phiên bản cũ hơn trong VBA chúng ta không cần phải tạo bộ đệm cho bảng tổng hợp nhưng trong excel 2010 và các phiên bản mới hơn thì cần phải có bộ đệm.
VBA có thể tiết kiệm rất nhiều thời gian cho chúng tôi tại nơi làm việc. Mặc dù thành thạo nó không phải là dễ dàng nhưng đáng để dành thời gian để học điều này. Tôi đã mất 6 tháng để hiểu quy trình tạo bảng tổng hợp thông qua VBA. Bạn biết những gì 6 tháng đó đã làm nên điều kỳ diệu cho tôi bởi vì tôi đã mắc rất nhiều sai lầm khi cố gắng tạo bảng xoay.
Nhưng thực tế là tôi đã học được từ những sai lầm của mình và bây giờ tôi viết bài này để hướng dẫn bạn cách tạo bảng tổng hợp bằng cách sử dụng mã.
Chỉ với một cú nhấp chuột, chúng tôi có thể tạo báo cáo.
Các bước tạo Pivot Table trong VBA
Để tạo bảng tổng hợp, điều quan trọng là phải có dữ liệu. Đối với điều này, tôi đã tạo một số dữ liệu giả. Bạn có thể tải xuống sổ làm việc để theo dõi với tôi với cùng một dữ liệu.
Bước 1: Pivot Table là một đối tượng để tham chiếu bảng tổng hợp khai báo biến là PivotTables.
Mã:
Sub PivotTable () Dim PTable As PivotTable End Sub
Bước 2: Trước khi tạo bảng tổng hợp, đầu tiên chúng ta cần tạo bộ đệm tổng hợp để xác định nguồn dữ liệu.
Trong các trang tính thông thường, bảng tổng hợp mà không làm phiền chúng tôi sẽ tạo một bộ đệm tổng hợp trong nền. Nhưng trong VBA, chúng ta phải tạo.
Đối với điều này, hãy xác định biến một PivotCache.
Mã:
Làm mờ PCache dưới dạng PivotCache
Bước 3: Để xác định phạm vi dữ liệu tổng hợp, hãy xác định biến là một phạm vi.
Mã:
Dim PRange As Range
Bước 4: Để chèn bảng pivot, chúng ta cần một sheet riêng để thêm các trang tính cho bảng pivot để khai báo biến dưới dạng trang tính.
Mã:
Dim PSheet như Worksheet
Bước 5: Tương tự, để tham chiếu dữ liệu chứa bảng tính hãy khai báo thêm một biến nữa là Bảng tính.
Mã:
Dim DSheet như Worksheet
Bước 6: Cuối cùng, để tìm hàng & cột được sử dụng cuối cùng, hãy xác định thêm hai biến là Long.
Mã:
Dim LR As Long Dim LC As Long
Bước 7: Bây giờ, chúng ta cần chèn một trang tính mới để tạo bảng tổng hợp. Trước đó, nếu có bất kỳ trang tính tổng hợp nào ở đó, thì chúng ta cần xóa trang tính đó.
Bước 8: Bây giờ, đặt biến đối tượng PSheet và DSheet tương ứng thành Pivot Sheet và Data Sheet.
Bước 9: Tìm hàng được sử dụng cuối cùng và cột được sử dụng cuối cùng trong biểu dữ liệu.
Bước 10: Bây giờ đặt phạm vi trục bằng cách sử dụng hàng cuối cùng và cột cuối cùng.
Điều này sẽ thiết lập phạm vi dữ liệu một cách hoàn hảo. Nó sẽ tự động chọn phạm vi dữ liệu, ngay cả khi có bất kỳ sự bổ sung hoặc xóa dữ liệu nào trong biểu dữ liệu.
Bước 11: Trước khi chúng tôi tạo bảng tổng hợp, chúng tôi cần tạo bộ đệm tổng hợp. Đặt biến bộ đệm trục xoay bằng cách sử dụng mã VBA bên dưới.
Bước 12: Bây giờ, tạo một bảng tổng hợp trống.
Bước 13: Sau khi chèn bảng tổng hợp, chúng ta cần chèn trường hàng trước. Vì vậy, tôi sẽ chèn trường hàng làm cột Quốc gia của tôi.
Lưu ý: Tải xuống sổ làm việc để hiểu các cột dữ liệu.
Bước 14: Bây giờ, một mục nữa tôi sẽ chèn vào trường hàng là mục vị trí thứ hai. Tôi sẽ chèn Sản phẩm làm mục hàng thứ hai vào trường hàng.
Bước 15: Sau khi chèn các cột vào trường hàng, chúng ta cần chèn các giá trị vào trường cột. Tôi sẽ chèn "Phân đoạn" vào trường cột.
Bước 16: Bây giờ, chúng ta cần chèn số vào trường dữ liệu. Vì vậy, hãy chèn "Bán hàng" vào trường dữ liệu.
Bước 17: Chúng ta đã làm xong phần tóm tắt bảng tổng hợp. Bây giờ chúng ta cần định dạng bảng. Để định dạng bảng tổng hợp, hãy sử dụng mã dưới đây.
Để hiển thị các mục giá trị được sắp xếp theo hàng ở dạng bảng, hãy thêm mã bên dưới ở cuối.
Ok, chúng ta đã hoàn tất nếu chúng ta chạy mã này bằng phím F5 hoặc sau đó theo cách thủ công, chúng ta sẽ nhận được bảng tổng hợp như thế này.
Như vậy, bằng cách sử dụng mã hóa VBA, chúng ta có thể tự động hóa quá trình tạo bảng tổng hợp.
Để bạn tham khảo, tôi đã đưa ra mã bên dưới.
Sub PivotTable() Dim PTable As PivotTable Dim PCache As PivotCache Dim PRange As Range Dim PSheet As Worksheet Dim DSheet As Worksheet Dim LR As Long Dim LC As Long On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets("Pivot Sheet").Delete 'This will delete the exisiting pivot table worksheet Worksheets.Add After:=ActiveSheet ' This will add new worksheet ActiveSheet.Name = "Pivot Sheet" ' This will rename the worksheet as "Pivot Sheet" On Error GoTo 0 Set PSheet = Worksheets("Pivot Sheet") Set DSheet = Worksheets("Data Sheet") 'Find Last used row and column in data sheet LR = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LC = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column 'Set the pivot table data range Set PRange = DSheet.Cells(1, 1).Resize(LR, LC) 'Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=PRange) 'Create blank pivot table Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="Sales_Report") 'Insert country to Row Filed With PSheet.PivotTables("Sales_Report").PivotFields("Country") .Orientation = xlRowField .Position = 1 End With 'Insert Product to Row Filed & position 2 With PSheet.PivotTables("Sales_Report").PivotFields("Product") .Orientation = xlRowField .Position = 2 End With 'Insert Segment to Column Filed & position 1 With PSheet.PivotTables("Sales_Report").PivotFields("Segment") .Orientation = xlColumnField .Position = 1 End With 'Insert Sales column to the data field With PSheet.PivotTables("Sales_Report").PivotFields("Sales") .Orientation = xlDataField .Position = 1 End With 'Format Pivot Table PSheet.PivotTables("Sales_Report").ShowTableStyleRowStripes = True PSheet.PivotTables("Sales_Report").TableStyle2 = "PivotStyleMedium14" 'Show in Tabular form PSheet.PivotTables("Sales_Report").RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
