vba 在没有数据的情况下将过滤器保留在数据透视表中

Keeping filters in a pivot table in absence of data

提问by Francisco Corrêa

Imagine I have a quality control in a factory retrieving the state of each product StateId 1 - Good 2 - Acceptable 3 - Subproduct 4 - waste

想象一下，我在一家工厂有一个质量控制，检索每个产品的状态 StateId 1 - 良好 2 - 可接受 3 - 子产品 4 - 浪费

My table 'StateIdHistory' gives me the state of each ProductId at each time. I am creating a pivot table in which I want to filter out the StateId=3 in a specific datarange. so I filter my report selecting "3".

我的表 'StateIdHistory' 每次都给我每个 ProductId 的状态。我正在创建一个数据透视表，我想在其中过滤掉特定数据范围中的 StateId=3。所以我过滤我的报告选择“3”。

Imagine I want to use this same model to other database in which there isn't any StateId=3. My data model will be destroyed. Can I somehow fix the filter to be equal to 3 in spite of having or not data? VBA? If 3 still does not exist it would retrieve me 0 or blank cells...

想象一下，我想对其他没有 StateId=3 的数据库使用相同的模型。我的数据模型将被破坏。不管有没有数据，我能否以某种方式将过滤器修复为等于 3？VBA？如果 3 仍然不存在，它将检索我 0 或空白单元格...

Thanks in advance!!

提前致谢！！

回答by psymann

If I understand correctly, you have four options for StateId:

如果我理解正确，StateId 有四个选项：

1 - Good
2 - Acceptable
3 - Subproduct
4 - waste

1 - 良好
2 - 可接受
3 - 副产品
4 - 浪费

And then you've made a pivot table, and put StateId into the Report Filter section, and filtered it on only "3 - Subproduct"

然后您制作了一个数据透视表，并将 StateId 放入“报告过滤器”部分，并仅对“3 - 子产品”进行过滤

So at the top of your pivot table in the filtering section, it shows:

因此，在筛选部分中数据透视表的顶部，它显示：

| StateId | 3 - Subproduct |

| 状态 ID | 3 - 子产品 |

If you refresh your pivot table, it will keep filtering on 3-Subproduct as you've asked it to... ...until your data has no entries for 3-subproduct. Then, instead of keeping filtering on it, it automatically changes to:

如果您刷新数据透视表，它将按照您的要求继续过滤 3-Subproduct ......直到您的数据没有 3-subproduct 的条目。然后，它不会继续对其进行过滤，而是自动更改为：

| StateId | (All) |

| 状态 ID | (全部) |

What you wanted (I think), was for it to stay filtering on 3-Subproduct, and just show nothing in the pivot table. But what it ever-so-helpfully does instead is reset the filter to (All) because there are no 3-Subproduct entries to show. Which then means later on, if you add some 3-Subproduct entries, and refresh the pivot table again, it staysfiltering on (All), and doesn't remember you actually wanted 3-Subproduct.

您想要的（我认为）是让它保持对 3-Subproduct 的过滤，并且只在数据透视表中不显示任何内容。但它非常有帮助的做法是将过滤器重置为 (All)，因为没有要显示的 3-Subproduct 条目。这意味着稍后，如果您添加一些 3-Subproduct 条目，并再次刷新数据透视表，它会保持对 (All) 的过滤，并且不记得您实际上想要 3-Subproduct。

If that's your problem then it's the same as the one I've had, and I've finally realised today that the answer is actually very simple:

如果那是您的问题，那么它与我遇到的问题相同，我今天终于意识到答案实际上非常简单：

• Right-click on the filter option and go to Field Settings
• Choose Layout & Print tab
• Tick the box called Show Items with no data

• 右键单击过滤器选项并转到字段设置
• 选择布局和打印选项卡
• 勾选名为 Show Items with no data 的框

Then it remembers you've picked 3-subproduct even when there's no data for 3-subproduct in there, and just returns a blank pivot table instead of reverting to (All).

然后它记住你选择了 3-subproduct，即使那里没有 3-subproduct 的数据，并且只返回一个空白的数据透视表而不是恢复到 (All)。

回答by Steven Edwards

One quick comment to add to the excellent answer by PsyMann above; you must have the PivotTable set to retain data for deleted items or the option to "Show items with no data" will be greyed out.

添加到上面 PsyMann 的优秀答案的一个快速评论；您必须将数据透视表设置为保留已删除项目的数据，否则“显示没有数据的项目”选项将变灰。

Do this by right-clicking on the table, picking PivotTable Options, Data, and ensuring that "Retain items deleted from the data source" is set to either "Automatic" or "Maximum"

通过右键单击表格，选择数据透视表选项，数据，并确保“保留从数据源中删除的项目”设置为“自动”或“最大”来执行此操作

The frustrating thing is that you can't make this field dependent so this then means that all fields will retain obsolete data, and these obsolete items will now show up in Data Slicers etc.

令人沮丧的是，您不能使该字段依赖，因此这意味着所有字段都将保留过时的数据，而这些过时的项目现在将显示在数据切片器等中。

回答by Jacob

The answer is to create a separate table for the state dimension:

答案是为状态维度创建一个单独的表：

This should be connected to your StateIDHistory and the state in your filter should then be from the state table. not only will this show all options regardless of whether they have data but it's also good practice for more complicated models.

这应该连接到您的 StateIDHistory，然后您的过滤器中的状态应该来自状态表。这不仅会显示所有选项，无论它们是否有数据，而且对于更复杂的模型也是一种很好的做法。

回答by Uttam

I faced the same issue in a exception report I sent.. it worked fine where filter returned some data, where there was no data the filter got removed and it showed all data.

我在发送的异常报告中遇到了同样的问题..它在过滤器返回一些数据的情况下工作正常，在没有数据的情况下过滤器被删除并显示所有数据。

I moved the filter column from Pivot Filterssection to Rowssection and then used the Label Filteroption for the column to specify the filter and then I made the filter column hidden. This worked correctly.

我将过滤器列从 Pivot Filters部分移到Rows部分，然后使用列的标签过滤器选项指定过滤器，然后隐藏过滤器列。这工作正常。

There are limitations to this approach as you cannot select multiple values to filter, Label Filter has limited options. My filter was based on a yes/no type column I had added in base data to identify exception records so this approach worked fine for me.

这种方法存在局限性，因为您不能选择多个值进行过滤，标签过滤器的选项有限。我的过滤器基于我在基础数据中添加的 yes/no 类型列来识别异常记录，因此这种方法对我来说效果很好。