HomeTin học Văn phòngExcelTìm hiểu cách...

Tìm hiểu cách tự động tạo bảng Pivot trong Excel bằng VBA


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 thêm  Cách trừ ngày tháng trong Excel

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:

Bảng tổng hợp với các tính toán cơ bản cho dữ liệu Siêu cửa hàng mẫu trong MS Excel

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.

Một vòng tròn trong sổ làm việc excel với hộp thoại đang mở

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ènmô-đ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ó.

Mô-đun VBA trong trình chỉnh sửa mã trong MS Excel

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ể.
Giao diện cửa sổ soạn thảo code Excel VBA

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
Mã để xác định phạm vi dữ liệu trong Excel VBA

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.
Mã VBA hiển thị cách tạo bộ đệm ẩn và bảng 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.

Mã VBA để thêm bộ lọc, hàng, cột và giá trị trong trục Excel

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.



Zik.vn – Biên dịch & Biên soạn Lại

spot_img

Create a website from scratch

Just drag and drop elements in a page to get started with Newspaper Theme.

Buy Now ⟶

Bài viết liên quang

DMCA.com Protection Status