Cách tự động cập nhật Pivot Table bằng VBA trong Microsoft Excel
Trong bài này, chúng ta sẽ tìm hiểu Cách Tự động Cập nhật Bảng Pivot bằng VBA trong Microsoft Excel 2010.
Tại sao chúng ta cần cập nhật biểu đồ bảng tổng hợp?
Như chúng ta đã biết, bất cứ khi nào chúng ta thực hiện thay đổi trong dữ liệu nguồn của bảng tổng hợp, thì dữ liệu đó không phản ánh ngay lập tức trong bảng tổng hợp. Chúng tôi cần làm mới bảng tổng hợp để xem các thay đổi. Và nếu bạn gửi một tệp cập nhật mà không làm mới bảng tổng hợp, bạn có thể cảm thấy bối rối.
Vì vậy, trong bài viết này, chúng ta sẽ tìm hiểu cách tự động làm mới bảng tổng hợp bằng VBA.
Đây là cú pháp đơn giản để tự động làm mới bảng tổng hợp trong sổ làm việc.
'Code in Source Data Sheet Object
Private Sub Worksheet_Deactivate()
sheetname_of_pivot_table.PivotTables("pivot_table_name").PivotCache.Refresh
End Sub
Pivot Caches là gì? Mỗi bảng tổng hợp đều lưu trữ dữ liệu trong bộ đệm tổng hợp. Đây là lý do tại sao pivot có thể hiển thị dữ liệu trước đó. Khi chúng tôi làm mới bảng tổng hợp, nó sẽ cập nhật bộ đệm với dữ liệu nguồn mới để phản ánh những thay đổi trên bảng tổng hợp.
Mã ở đâu để tự động làm mới bảng Pivot? Nếu dữ liệu nguồn và bảng tổng hợp của bạn nằm trong các trang tính khác nhau, thì mã VBA sẽ nằm trong bảng dữ liệu nguồn.
Ở đây chúng ta sẽ sử dụng Sự kiện Worksheet_SelectionChange. Điều này sẽ làm cho mã chạy bất cứ khi nào chúng ta chuyển từ trang dữ liệu nguồn sang trang tính khác.
Tôi sẽ giải thích sau tại sao tôi sử dụng sự kiện này.
Ở đây, tôi có dữ liệu nguồn trong sheet2 và bảng tổng hợp trong sheet1.
Mở VBE bằng phím CTRL + F11. Trong trình khám phá dự án, bạn có thể thấy ba đối tượng, Sheet1, Sheet2 và Workbook.
Vì Sheet2 chứa dữ liệu nguồn, hãy nhấp đúp vào đối tượng sheet2.
Bây giờ bạn có thể thấy hai trình đơn thả xuống ở đầu vùng mã. Từ menu thả xuống đầu tiên, hãy chọn trang tính. Và từ menu thả xuống thứ hai, hãy chọn Hủy kích hoạt. Thao tác này sẽ chèn một tên phụ rỗng Worksheet_Deactivate. Mã của chúng tôi sẽ được viết trong phụ này. Bất kỳ dòng nào được viết trong phụ này, sẽ được thực thi ngay khi người dùng chuyển từ trang này sang bất kỳ trang nào khác.
Trên sheet1, tôi có hai bảng tổng hợp. Tôi chỉ muốn làm mới một bảng tổng hợp. Để làm được điều đó, tôi cần biết tên của bảng tổng hợp. Để biết tên của bất kỳ bảng tổng hợp nào, hãy chọn bất kỳ ô nào trong bảng tổng hợp đó, hãy chuyển đến tab phân tích bảng tổng hợp. Ở phía bên trái, bạn sẽ thấy tên của bảng tổng hợp. Bạn cũng có thể thay đổi tên của bảng tổng hợp tại đây.
Bây giờ chúng ta biết tên của bảng tổng hợp, chúng ta có thể viết một dòng đơn giản để làm mới bảng tổng hợp.
Private Sub Worksheet_Deactivate()
Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
End Sub
Và nó được thực hiện.
Bây giờ bất cứ khi nào bạn chuyển từ dữ liệu nguồn, mã vba này sẽ chạy để làm mới bảng tổng hợp1. Như bạn có thể thấy trong gif bên dưới.
Làm thế nào để Làm mới Tất cả Bảng Tổng hợp trong Sổ làm việc? Trong ví dụ trên, chúng tôi chỉ muốn làm mới một bảng tổng hợp cụ thể. Nhưng nếu bạn muốn làm mới tất cả các bảng tổng hợp trong sổ làm việc, bạn chỉ cần thực hiện các thay đổi nhỏ đối với mã của mình.
Private Sub Worksheet_Deactivate()
'Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
For Each pc In ThisWorkbook.PivotCaches
pc.Refresh
Next pc
End Sub
Tại sao sử dụng Worksheet_Deactivate Event?
Nếu bạn muốn làm mới bảng tổng hợp ngay khi có bất kỳ thay đổi nào trong dữ liệu nguồn, bạn nên sử dụng sự kiện Worksheet_Change. Nhưng tôi không khuyến khích nó. Nó sẽ làm cho sổ làm việc của bạn chạy mã mỗi khi bạn thực hiện bất kỳ thay đổi nào trong trang tính. Bạn có thể phải thực hiện hàng trăm thay đổi trước khi muốn xem kết quả. Nhưng excel sẽ làm mới bảng tổng hợp mỗi lần thay đổi. Điều này sẽ dẫn đến lãng phí thời gian xử lý và nguồn lực. Vì vậy, nếu bạn có bảng tổng hợp và dữ liệu trong các trang tính khác nhau, tốt hơn nên sử dụng Sự kiện hủy kích hoạt trang tính. Nó cho phép bạn hoàn thiện công việc của mình. Khi bạn chuyển sang trang tính bảng tổng hợp để xem các thay đổi, nó sẽ sửa đổi các thay đổi.
Private Sub Worksheet_Change(ByVal Target As Range)
Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
End Sub
Làm thế nào để Làm mới Mọi thứ trong Sổ làm việc Khi Một Thay đổi được Thực hiện trong Dữ liệu Nguồn? Nếu bạn muốn làm mới mọi thứ trên sổ làm việc (biểu đồ, bảng tổng hợp, công thức, v.v.), bạn có thể sử dụng lệnh ThisWorkbook.RefreshAll.
Private Sub Worksheet_Change(ByVal Target As Range) ThisWorkbook.RefreshAll End Sub
Hy vọng bài viết này về Cách tự động cập nhật Pivot Table bằng VBA trong Microsoft Excel là giải thích. Tìm thêm các bài viết về cách tính giá trị và các công thức Excel liên quan tại đây. Nếu bạn thích blog của chúng tôi, hãy chia sẻ nó với bạn bè của bạn trên Facebook. Và bạn cũng có thể theo dõi chúng tôi trên Twitter và Facebook. Chúng tôi rất muốn nghe ý kiến của bạn, hãy cho chúng tôi biết cách chúng tôi có thể cải thiện, bổ sung hoặc đổi mới công việc của mình và làm cho nó tốt hơn cho bạn. Viết thư cho chúng tôi tại [email protected].