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

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

Trong bài viết trước, chúng ta đã tìm hiểu cách bạn có thể thay đổi và cập nhật động các bảng tổng hợp riêng lẻ với nguồn dữ liệu thu nhỏ hoặc mở rộng.

Trong bài viết này, chúng ta sẽ tìm hiểu cách có thể làm cho tất cả các bảng tổng hợp trong sổ làm việc tự động thay đổi nguồn dữ liệu. Nói cách khác, thay vì thay đổi một bảng tổng hợp tại một thời điểm, chúng tôi sẽ cố gắng thay đổi nguồn dữ liệu của tất cả các bảng tổng hợp trong sổ làm việc để 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.

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 động tất cả bảng tổng hợp trong sổ làm việc 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 ba trang tính. Sheet1 chứa dữ liệu nguồn có thể thay đổi. Sheet2 và Sheet3 chứa các 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 source_data As Range

'Determining last row and column number

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

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

'Setting the new range

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

'Code to loop through each sheet and pivot table

For Each ws In ThisWorkbook.Worksheets

For Each pt In ws.PivotTables



pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create( _

SourceType:=xlDatabase, _

SourceData:=source_data)



Next pt

Next ws

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ể sửa đổi nó theo nhu cầu của mình.

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.

Để 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. Bạn có thể xác định tham chiếu ô đầu của riêng bạn.

Đặ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.

Vì chúng ta không biết sổ làm việc sẽ chứa bao nhiêu bảng tổng hợp tại một thời điểm, chúng ta sẽ lặp qua từng trang tính và các bảng tổng hợp của mỗi trang tính. Vì vậy, không có bảng tổng hợp nào còn lại. Đối với điều này, chúng tôi sử dụng các vòng lặp for lồng nhau.

Đối với mỗi ws Trong ThisWorkbook.Worksheets

Đối với mỗi pt Trong ws.PivotTables

pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create (_

SourceType: = xlDatabase, _

SourceData: = source_data)

Tiếp theo pt

Vòng lặp đầu tiên lặp lại qua mỗi trang tính. Vòng lặp thứ hai lặp qua mỗi bảng tổng hợp trong một trang tính. Bảng tổng hợp được gán cho biến pt. * Chúng tôi sử dụng phương thức ChangePivotCache của đối tượng pt. Chúng tôi tự động tạo một bộ đệm xoay vòng bằng Phương pháp ThisWorkbook.PivotCaches.Create. Phương thức này nhận hai biến SourceType và SourceData. Là loại nguồn, chúng tôi khai báo xlDatabase và như SourceData, chúng tôi chuyển phạm vi dữ liệu nguồn_data mà chúng tôi đã tính toán trước đó.

Và đó là nó. Chúng tôi có bảng tổng hợp của chúng tôi tự động. Điều này sẽ tự động cập nhật tất cả các bảng tổng hợp trong sổ làm việc.

Vâng thưa các bạn, đây là cách bạn có thể thay đổi động các phạm vi nguồn dữ liệu của tất cả các bảng tổng hợp trong sổ làm việc 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.