vba 是否可以基于过滤 Excel 中源表中的行来过滤数据透视表使用的数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13883092/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to filter data used by pivot table based on filtering the rows in a source table in Excel?
提问by Geoffrey Stoel
I have developed a dashboard in Excel 2007 that uses one source table in a sheet (being filled with a query on our data warehouse) and multiple pivot tables making different cross sections on this data.
我在 Excel 2007 中开发了一个仪表板，它使用工作表中的一个源表（填充了对我们数据仓库的查询）和多个数据透视表，在此数据上形成不同的横截面。
I use the GETPIVOTDATA in almost a hundred formulas to give me the right value for a specific indicator in my dashboard.
我在近一百个公式中使用了 GETPIVOTDATA 来为我的仪表板中的特定指标提供正确的值。
This all works fine. However I now have received the question to make the dashboard for 5 different segments. As you can imagine I don't want to create 5 different workbooks for this and need to maintain the dashboard logic on all of them.
这一切正常。但是，我现在收到了为 5 个不同细分市场制作仪表板的问题。正如您可以想象的那样，我不想为此创建 5 个不同的工作簿，并且需要在所有这些工作簿上维护仪表板逻辑。
So my question is the following. Is it possible to automatically (through VBA or any other means) filter the results in my source table which is the source for my pivot tables and thus for my dashboard values.
所以我的问题如下。是否可以自动（通过 VBA 或任何其他方式）过滤我的源表中的结果，这是我的数据透视表的源，因此也是我的仪表板值的源。
So schematically:
所以示意图：
DATABASE_VIEW --> SOURCE_TABLE --> 12 pivot tables --> 100 GETPIVOTDATA functions
DATABASE_VIEW --> SOURCE_TABLE --> 12 个数据透视表 --> 100 个 GETPIVOTDATA 函数
Preferably I would like to load all the segments in the source_table (one view on my database) and then filter the data in the source table, which results in filterd source_dat for my pivots. This way I can (without requerying the db) quickly change between segments in the dashboards (refreshing pivots only).
最好我想加载 source_table 中的所有段（我的数据库上的一个视图），然后过滤源表中的数据，这会为我的数据透视表生成 filterd source_dat。这样我就可以（无需重新查询数据库）在仪表板中的段之间快速更改（仅刷新枢轴）。
Data in the source table has the column: CUSTOMER_SEGMENT available to filter upon.
源表中的数据具有可用于过滤的列：CUSTOMER_SEGMENT。
Any help is appreciated.
任何帮助表示赞赏。
Geoffrey
杰弗里
回答by K_B
You can manipulate all external data connections and internal pivottables through VBA.
您可以通过 VBA 操作所有外部数据连接和内部数据透视表。
To make it a double learner for you I recommend using the Record Macro button and then changing a filter in your pivot table and also change your SQL query a bit.
为了使它成为您的双重学习者，我建议您使用“记录宏”按钮，然后更改数据透视表中的过滤器，并稍微更改您的 SQL 查询。
You will now see that in the recorded macro the related properties of that Pivottable/query are stated. Filters and SQL are simply Strings in the VBA code, thus you can alter certain bits to get different filters or "WHERE Cust_ID = " comboboxCust.Valuekind of things.
您现在将看到在录制的宏中说明了该数据透视表/查询的相关属性。过滤器和 SQL 只是 VBA 代码中的字符串，因此您可以更改某些位以获得不同的过滤器或"WHERE Cust_ID = " comboboxCust.Value类型的东西。
Doing it through VBA codes to change the filters and SQL is usually more speedy then having it all interactively related with the standard Excel tools (Functions, parameters, linked filters, ...)
通过 VBA 代码更改过滤器和 SQL 通常比将其与标准 Excel 工具（函数、参数、链接过滤器等）交互相关更快
