如何在 Excel 数据透视表中按单元格公式过滤日期？
我正在尝试使用 Sql 服务器视图的数据透视表在 Excel 中制作一些报告。客户要求之一是能够根据他们选择的日期范围过滤报告，这似乎很合理。最终用户流程需要相当简单，因此我的偏好是有一个单元格用于开始日期，一个单元格用于结束日期，并允许用户 输入这些并刷新表格。
有两个问题我无法解决：
选择日期过滤器似乎没有为我提供任何方法来引用单元格作为值的来源。我当时必须把它放进去。
似乎只有当我在日期字段上分组时才可以定义日期范围。对于某些报告，这违反了规范。数据透视表的报告过滤器部分似乎只允许我按单个值而不是范围进行过滤。对于可能有数百个日期要经过的真实数据，这变得难以管理。
我尝试过的一件事是在同一工作簿的单独工作表中创建一个没有任何聚合列的中间表，然后对其进行过滤。这正确地将列隐藏在该中间表中，但对从中派生的数据透视表没有影响。
有人对我如何实现这项工作有想法吗？这似乎是一个相当明显的用例 - 关于如何使用数据透视表，我是否遗漏了什么？
I am attempting to make some reports in Excel using a pivot table of a Sql server view. One of the customer requirements is to be able to filter the reports based on a date range they select, which seems reasonable. The end user process needs to be fairly simple, so my preference is to have a cell for begin date and one for end date and allow the user to
enter these and refresh the table.
There are two problems which I haven't been able to get past:
Selecting a date filter doesn't appear to provide any way for me to reference a cell as a source for the value. I have to put it in at the time.
It seems as if it is only possible to define a date range if I am grouping on the date field. For some of the reports, this violates the specification. The Report Filter section of a pivot table only seems to allow me to filter by individual values rather than a range. This gets unmanageable on real data which might have hundreds of dates to go through.
One thing I have tried is to create an intermediate table without any aggregates columns in a separate sheet in the same workbook, and then filter that. This hid the columns correctly in that intermediate table, but had no effect on the pivot table which derived from it.
Does anyone have ideas for how I can make this work? It seems like a fairly obvious use case - is there something I am missing about how pivot tables are supposed to be used?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以做的最简单的事情就是即时更改数据透视表查询。
当源单元格发生更改（
Worksheet_Changed事件）时，将" AND WhateverDate > '1/1/1111'"附加到Me.PivotTables("您的数据透视表").PivotCache.CommandText并刷新数据透视表。The easies thing you can do is changing your pivot table query on the fly.
When the source cell is changed (
Worksheet_Changedevent), append" AND WhateverDate > '1/1/1111'"toMe.PivotTables("Your pivot").PivotCache.CommandTextand refresh the pivot.我记得几年前我通过动态 SQL 查询解决了类似的需求（即将其作为 VBA 中的字符串放在一起，并在 where 条件中使用具有范围值的单元格）。然后使用标记为“刷新”的按钮调用 VBA 宏。
但很抱歉不再有此代码，我只记得大概的想法。
I remember I solved a similar requirement some years ago by making the SQL query dynamic (i. e. putting it together as a string in VBA, and using the cells with the range values in the where condition). ant the VBA macor was then called with a button labeled "refresh".
But I am sorry do not have this code available any more, I just remember the general idea.
这已经死了一段时间了，但我想我会发布一种不同的方法。在源表中添加一个公式，该公式对于您想要的内容返回“1”，对于您不需要的内容返回“0”，然后在数据透视表中添加一个仅过滤“1”的过滤器。
This has been dead for a while, but I thought I would post a different method. add a formula to the source table that returns a '1' for thing you want, and a '0' for things you don't, and then add a filter in the pivot table that filters only the '1'.