Сводные таблицы в Excel

1. Удаление отсутствующих элементов в выпадающих меню

Problems: Old Items Remain in Pivot Field Dropdowns

The data in the pivot table source may change, and items that were previously in the source are no longer included. For example, some sales reps may leave the company, and the names of their replacements appear in the source table.

Even after you refresh the pivot table, the names of the old sales reps will appear, along with the new names. In the list at right, Cartier has replace Gill, but Gill still appears in the list.

Solution 1: Manually Clear Old Items
To manually clear the old items from the list:

1. If you manually created any groups that include the old items, ungroup those items.
2. Drag the pivot field out of the pivot table.
3. On the Pivot toolbar, click the Refresh button.
4. Drag the pivot field back to the pivot table.

Solution 2: Change the Retain Items Setting in Excel 2007
To prevent old items from being retained in an Excel 2007 pivot table, you can change an option setting:

1. Right-click a cell in the pivot table
2. Click on PivotTable options
3. Click on the Data tab
4. In the Retain Items section, select None from the drop down list.
5. Click OK, then refresh the pivot table.

Solution 3: Programmatically Clear Old Items -- Excel 2002 or later

In Excel 2002, and later versions, you can programmatically change the pivot table properties, to prevent missing items from appearing, or clear items that have appeared.

Sub DeleteMissingItems2002All()
'prevents unused items in non-OLAP PivotTables
'pivot table tutorial by contextures.com
Dim pt As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache
'change the settings
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next pt
Next ws


'refresh all the pivot caches
For Each pc In ActiveWorkbook.PivotCaches
On Error Resume Next
pc.Refresh
Next pc


End Sub

Материал из книги "Excel 2007 PivotTables Recipes", взято отсюда