Sau khi bạn thay đổi dữ liệu nguồn cho bảng tổng hợp, một số mục cũ có thể vẫn xuất hiện trong trình đơn thả xuống mục bảng tổng hợp. Hướng dẫn dưới đây sẽ giúp bạn xóa các mục cũ, bằng các bước thủ công hoặc macro
Các mục cũ vẫn còn trong Pivot Field Drop Downs
Dữ liệu trong nguồn bảng tổng hợp có thể thay đổi và các mục trước đây trong nguồn không còn được bao gồm. Ví dụ, một công ty tái cấu trúc và đi xuống từ 3 khu vực - Đông, Trung và Tây - chỉ còn 2 khu vực - Đông và Tây
Ngay cả sau khi bạn làm mới bảng tổng hợp, tên của vùng cũ có thể tiếp tục xuất hiện trong danh sách thả xuống, cùng với các tên mới. Trong ảnh chụp màn hình bên dưới, khu vực miền Trung vẫn xuất hiện trong danh sách các khu vực
Hướng dẫn bảng tổng hợp này cho biết cách bạn có thể xóa các mục cũ theo cách thủ công hoặc bằng macro
Video. Ngăn các mục cũ trong Pivot Table
Video ngắn này cho biết cách thay đổi cài đặt trong bảng tổng hợp, để các mục cũ không xuất hiện trong danh sách thả xuống. Hướng dẫn bằng văn bản ở bên dưới video
Dòng thời gian video
- 0. 00 Mục cũ - Giới thiệu
- 1. 37 Thay đổi cài đặt giữ lại các mục
- 2. 57 Thay đổi cài đặt mục giữ lại mặc định
Thay đổi cài đặt giữ lại các mục
Để ngăn các mục cũ được giữ lại trong bảng tổng hợp, bạn có thể thay đổi cài đặt tùy chọn
- Nhấp chuột phải vào bất kỳ ô nào trong bảng tổng hợp
- Nhấp vào tùy chọn PivotTable
- Trong hộp thoại Tùy chọn PivotTable, bấm vào tab Dữ liệu
- Trong phần Giữ lại các mục, chọn Không có từ danh sách thả xuống
- Nhấp vào OK, sau đó làm mới bảng tổng hợp
Ghi chú. Cài đặt này khả dụng trong Tùy chọn PivotTable trong Excel 2007 trở lên. Đối với Excel 2003, cài đặt có thể được thay đổi bằng macro
Thay đổi cài đặt mặc định giữ lại các mục
Để ngăn không cho các mục cũ được giữ lại trong bảng tổng hợp mới, bạn có thể thay đổi cài đặt mặc định của bảng tổng hợp, trong Excel 2019 hoặc Excel for Office 365. Sau đó, khi bạn tạo các bảng tổng hợp mới, chúng sẽ tự động có các cài đặt mà bạn muốn
GHI CHÚ. Có, thay đổi cài đặt mặc định cho bạn
Làm theo các bước sau để thay đổi cài đặt mặc định
- Ở đầu Excel, bấm vào tab Tệp
- Trong cửa sổ Tùy chọn Excel, trong danh sách Danh mục ở bên trái, bấm vào Dữ liệu
- Trong phần Tùy chọn dữ liệu, nhấp vào nút Chỉnh sửa bố cục mặc định
- Trong hộp thoại Chỉnh sửa Bố cục Mặc định, ở dưới cùng bên phải, bấm vào nút Tùy chọn PivotTable
- Trong hộp thoại Tùy chọn PivotTable, bấm vào tab Dữ liệu
- Trong phần Giữ lại các mục, chọn Không có từ danh sách thả xuống
- Bấm OK, ba lần, để đóng tất cả các cửa sổ
Xóa các mục cũ theo cách thủ công
Thay vì thay đổi cài đặt tùy chọn, bạn có thể xóa các mục cũ khỏi danh sách thả xuống theo cách thủ công
- Nếu bạn đã tạo bất kỳ nhóm nào bao gồm các mục cũ theo cách thủ công, hãy hủy nhóm các mục đó
- Xóa trường tổng hợp khỏi bảng tổng hợp
- Nhấp chuột phải vào bảng tổng hợp và nhấp vào lệnh Làm mới
- Thêm trường tổng hợp trở lại bảng tổng hợp
Macro để xóa các mục cũ
Macro sau sẽ thay đổi các thuộc tính của bảng tổng hợp để ngăn các mục bị thiếu xuất hiện hoặc xóa các mục đã xuất hiện
Macro này thay đổi cài đặt MissingItemsLimit cho tất cả các bảng tổng hợp trong sổ làm việc đang hoạt động
Sub DeleteMissingItemsAllPTs[] 'For NEW versions of Excel 'developed by contextures.com 'to prevent unused items in ' non-OLAP PivotTables ' changes MissingItemsLimit setting ' for all PTs in active workbook Dim pt As PivotTable Dim wb As Workbook Dim ws As Worksheet Dim pc As PivotCache Set wb = ActiveWorkbook 'change the settings For Each ws In wb.Worksheets For Each pt In ws.PivotTables pt.PivotCache.MissingItemsLimit _ = xlMissingItemsNone Next pt Next ws 'refresh all the pivot caches For Each pc In wb.PivotCaches On Error Resume Next pc.Refresh Next pc End Sub
Macro để xóa các mục cũ -- Excel 2000
Trong Excel 2000 và Excel 97, hãy chạy đoạn mã sau để xóa các mục cũ khỏi danh sách thả xuống.
Sub DeleteOldItemsWB[] 'For OLD versions of Excel ' XL97 and XL2000 'to prevent unused items in ' non-OLAP PivotTables ' changes MissingItemsLimit setting ' for all PTs in active workbook ' based on MSKB [202232] Dim ws As Worksheet Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem On Error Resume Next For Each ws In ActiveWorkbook.Worksheets For Each pt In ws.PivotTables pt.RefreshTable pt.ManualUpdate = True For Each pf In pt.VisibleFields If pf.Name "Data" Then For Each pi In pf.PivotItems If pi.RecordCount = 0 And _ Not pi.IsCalculated Then pi.Delete End If Next pi End If Next pf pt.ManualUpdate = False 'pt.RefreshTable 'optional - might hang Excel 'if 2 or more pivot tables on one sheet Next pt Next ws End Sub
Macro cho Cài đặt Mặc định
Để ngăn không cho các mục cũ được giữ lại trong bảng tổng hợp mới, bạn có thể sử dụng các macro này để thay đổi cài đặt mặc định của bảng tổng hợp, trong Excel 2019 hoặc Excel for Office 365
Cài đặt mặc định - Không có
Macro này thay đổi cài đặt thành Không có, vì vậy các mục cũ sẽ không xuất hiện trong bảng tổng hợp mới
Sub DefaultMissingItemsNone[] 'For Excel 365 or Excel 2019 'Default setting =None 'developed by contextures.com 'change default setting for ' MissingItemsLimit setting ' for all new PTs Application _ .DefaultPivotTableLayoutOptions _ .xlMissingItemsNone = 0 End Sub
Cài đặt mặc định - Tự động
Macro này thay đổi cài đặt thành Tự động, vì vậy các mục cũ sẽ xuất hiện trong bảng tổng hợp mới
Sub DefaultMissingItemsAuto[] 'For Excel 365 or Excel 2019 'Default setting =Automatic 'developed by contextures.com 'change default setting for ' MissingItemsLimit setting ' for all new PTs Application _ .DefaultPivotTableLayoutOptions _ .xlMissingItemsNone = -1 End Sub
Lấy tệp mẫu
Để xem dữ liệu mẫu và bảng tổng hợp được sử dụng trong các ví dụ này, hãy tải xuống sổ làm việc Mục cũ của Bảng tổng hợp. Tệp nén có định dạng xlsm và chứa các macro từ trang này. Để kiểm tra macro, hãy đảm bảo bật macro, nếu được nhắc, khi bạn giải nén và mở sổ làm việc