Cách cập nhật động phạm vi nguồn dữ liệu bảng Pivot trong Excel

Posted on: 2020年11月13日 2020年11月13日
Tags: excel-pivot-tables

Trong bài viết này, chúng ta sẽ tìm hiểu cách chúng ta có thể làm cho bảng tổng hợp của mình tự động thay đổi nguồn dữ liệu. Nói cách khác, chúng tôi sẽ tự động hóa quy trình thay đổi nguồn dữ liệu theo cách thủ công để bao gồm động các hàng và cột mới được thêm vào bảng nguồn và phản ánh sự thay đổi trong bảng tổng hợp ngay lập tức.

Viết mã trong bảng dữ liệu nguồn Vì chúng tôi muốn điều này hoàn toàn tự động, chúng tôi sẽ sử dụng mô-đun trang tính để viết mã thay vì mô-đun lõi. Điều này sẽ cho phép chúng tôi sử dụng //events-in-vba/the-worksheet-events-in-excel-vba.html[sheet event] `.

Nếu dữ liệu nguồn và bảng tổng hợp nằm trong các trang tính khác nhau, chúng tôi sẽ viết mã VBA để thay đổi nguồn dữ liệu bảng tổng hợp trong đối tượng trang tính có chứa dữ liệu nguồn (không chứa bảng tổng hợp). Nhấn CTRL + F11 để mở trình soạn thảo VB. Bây giờ đi tới trình khám phá dự án và tìm trang tính chứa dữ liệu nguồn. Nhấp đúp vào nó.

Một khu vực mã hóa mới sẽ mở ra. Bạn có thể không thấy bất kỳ thay đổi nào nhưng bây giờ bạn có quyền truy cập vào các sự kiện trang tính.

Bây giờ chúng tôi đã sẵn sàng để triển khai mã.

Mã nguồn để cập nhật bảng tổng hợp động với phạm vi mới Để giải thích cách hoạt động, tôi có một sổ làm việc. Sổ làm việc này chứa hai trang tính. Sheet1 chứa dữ liệu nguồn có thể thay đổi. Sheet2 chứa bảng tổng hợp phụ thuộc vào dữ liệu nguồn của sheet2.

Bây giờ tôi đã viết mã này trong vùng mã hóa của sheet1. Tôi đang sử dụng sự kiện Worksheet_Deactivate để mã này chạy để cập nhật bảng tổng hợp bất cứ khi nào chúng tôi chuyển từ bảng dữ liệu nguồn.

Private Sub Worksheet_Deactivate()

Dim pt As PivotTable

Dim pc As PivotCache

Dim source_data As Range

lstrow = Cells(Rows.Count, 1).End(xlUp).Row

lstcol = Cells(1, Columns.Count).End(xlToLeft).Column

Set source_data = Range(Cells(1, 1), Cells(lstrow, lstcol))

Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, SourceData:=source_data)

Set pt = Sheet2.PivotTables("PivotTable1")

pt.ChangePivotCache pc

End Sub

Nếu bạn có một sổ làm việc tương tự, bạn có thể sao chép trực tiếp dữ liệu này. Tôi đã giải thích mã này hoạt động bên dưới.

Bạn có thể thấy tác dụng của mã này trong gif bên dưới.

Làm cách nào để mã này tự động thay đổi dữ liệu nguồn và cập nhật bảng tổng hợp? Trước hết, chúng tôi sử dụng sự kiện worksheet_deactivate. Sự kiện này chỉ kích hoạt khi trang tính chứa mã được chuyển hoặc hủy kích hoạt. Vì vậy, đây là cách mã tự động chạy.

Bây giờ để thay đổi dữ liệu nguồn của bảng pivot, chúng tôi thay đổi dữ liệu trong bộ đệm pivot. Bảng tổng hợp được tạo bằng cách sử dụng bộ đệm tổng hợp. Bộ đệm tổng hợp chứa dữ liệu nguồn cũ cho đến khi bảng tổng hợp không được làm mới theo cách thủ công hoặc phạm vi dữ liệu nguồn được thay đổi thủ công.

Chúng tôi đã tạo các tham chiếu của tên bảng tổng hợp pt, bộ đệm ẩn có tên là pc và một dải ô có tên là source_data. Dữ liệu nguồn sẽ chứa toàn bộ dữ liệu. Để tự động lấy toàn bộ bảng dưới dạng phạm vi dữ liệu, chúng tôi xác định hàng cuối cùng và cột cuối cùng.

lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row

lstcol = Cells (1, Columns.Count) .End (xlToLeft) .Column

Sử dụng hai số này, chúng tôi xác định dữ liệu nguồn. Chúng tôi xác định rằng phạm vi dữ liệu nguồn sẽ luôn bắt đầu từ A1.

Đặt source_data = Phạm vi (Ô (1, 1), Ô (lstrow, lstcol))

Bây giờ chúng ta có dữ liệu nguồn là dữ liệu động. Chúng ta chỉ cần sử dụng nó trong bảng tổng hợp.

Chúng tôi lưu trữ dữ liệu này trong bộ đệm pivot vì chúng tôi biết bộ đệm trục lưu trữ tất cả dữ liệu.

Đặt pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data) Tiếp theo, chúng ta xác định bảng tổng hợp mà chúng ta muốn cập nhật. Vì chúng tôi muốn cập nhật PivotTable1 (tên của bảng tổng hợp. Bạn có thể kiểm tra tên của bảng tổng hợp trong tab phân tích trong khi chọn bảng tổng hợp.) Trên sheet1, chúng tôi đặt pt như hình dưới đây.

Đặt pt = Sheet2.PivotTables (“PivotTable1”)

Bây giờ chúng tôi chỉ cần sử dụng bộ đệm tổng hợp này để cập nhật bảng tổng hợp. Chúng tôi sử dụng phương thức changePivotCache của đối tượng pt.

pt.ChangePivotCache pc

Và chúng tôi đã tự động hóa bảng tổng hợp của mình. Điều này sẽ tự động cập nhật bảng tổng hợp của bạn. Nếu bạn có nhiều bảng với cùng một nguồn dữ liệu, chỉ cần sử dụng cùng một bộ đệm trong mỗi đối tượng bảng tổng hợp.

Vì vậy, các bạn, đây là cách bạn có thể thay đổi động phạm vi nguồn dữ liệu trong Excel. Tôi hy vọng tôi đã giải thích đủ. Nếu bạn có bất kỳ câu hỏi nào liên quan đến bài viết này, hãy cho tôi biết trong phần bình luận bên dưới.

Bài viết phổ biến:

Chức năng Countif là cần thiết để chuẩn bị bảng điều khiển của bạn.