seychellesartprojects.org

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 của chúng tôi, mặc dù việc 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 trong 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

Bạn có thể tải xuống Mẫu bảng VBA Pivot này tại đây - Mẫu bảng VBA Pivot

Để 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 đến 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 tôi cần tạo bộ đệm tổng hợp để xác định nguồn dữ liệu.

Trong bảng tổng hợp trang tính thông thường mà không gặp rắc rối, 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 a 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 bảng tính cho bảng pivot khai báo biến dưới dạng trang tính.

Mã:

 Dim PSheet dưới dạng Worksheet

Bước 5:  Tương tự để tham chiếu dữ liệu có chứa bảng tính khai báo thêm một biến 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, 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 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 đã hoàn thành 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ã bên dưới.

Lưu ý: Để có nhiều kiểu bảng khác nhau, hãy ghi macro chúng và lấy kiểu bảng.

Để 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 đoạn mã dưới đây ở 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 theo cách thủ công sau đó, chúng ta sẽ nhận được bảng tổng hợp như thế này.

Như thế nà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 Trang tính "). Xóa 'Thao tác này sẽ xóa trang tính bảng tổng hợp hiện ra Worksheets. Thêm Sau: = ActiveSheet' Thao tác này sẽ thêm trang tính mới ActiveSheet.Name =" Trang tính Pivot "'Thao tác này sẽ đổi tên trang tính thành" Trang tính tổng hợp "Khi Lỗi GoTo 0 Đặt PSheet = Worksheets ("Pivot Sheet") Đặt DSheet = Worksheets ("Data Sheet") 'Tìm hàng và cột được sử dụng gần đây nhất trong bảng dữ liệu LR = DSheet.Cells (Rows.Count, 1) .End (xlUp) .Row LC = DSheet.Cells (1, Columns.Count) .End (xlToLeft) .Column 'Đặt phạm vi dữ liệu bảng tổng hợp Đặt PRange = DSheet.Cells (1, 1) .Resize (LR, LC) 'Đặt pivot cahe Đặt PCache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = PRange)' Tạo bảng tổng hợp trống Đặt PTable = PCache.CreatePivotTable (TableDestination: = PSheet. Cells (1, 1), TableName: = "Sales_Report") 'Chèn quốc gia vào hàng Filed With PSheet.PivotTables ("Sales_Report"). PivotFields ("Country") .Orientation = xlRowField .Position = 1 End With' Chèn Sản phẩm vào 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 ("Phân đoạn") .Orientation = xlColumnField .Position = 1 End With 'Chèn cột Bán hàng vào trường dữ liệu Với PSheet.PivotTables ("Sales_Report"). PivotFields ("Sales") .Orientation = xlDataField .Position = 1 End With 'Định dạng Pivot Table PSheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True PSheet.PivotTables ("Sales_Report2"). "PivotStyleMedium14" 'Hiển thị ở dạng bảng PSheet.PivotTables ("Sales_Report"). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub) .RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub) .RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub