数据表 滚动条事件_使用滚动条更新数据透视表

数据表 滚动条事件

A couple of months ago, I shared an example with a scroll bar that selects the dates for an Excel report. There is a pivot table on a hidden sheet, and a summary report uses GetPivotData formulas to pull data from that pivot table.

几个月前，我分享了一个带有滚动条的示例，该滚动条为Excel报告选择日期 。 隐藏的工作表上有一个数据透视表，摘要报告使用GetPivotData公式从该数据透视表中提取数据。

Here is a screen shot of that summary report, with the scroll bar at the top.

这是该摘要报告的屏幕快照，滚动条在顶部。

This technique works well when there are a few static row headings, like the East and West region names, in this example. If the row headings will change every month, or if you have a larger number of categories, my technique won’t work as well.

在此示例中，当有几个静态行标题(例如东和西区域名称)时，此技术效果很好。 如果行标题每个月都会更改，或者您的类别数量更多，则我的技术也无法正常工作。

使用滚动条过滤数据 (Filter the Data With a Scroll Bar)

AlexJ has created a new version of that file, and he uses a pivot table as the report, instead of creating a static table. This technique gives you more flexibility, because you don’t have to set up the row headings manually.

AlexJ创建了该文件的新版本，他使用数据透视表作为报表，而不是创建静态表。 这种技术为您提供了更大的灵活性，因为您不必手动设置行标题。

In the screen shot below, the Central region was automatically added to the headings, because it first appears in the November data. It’s showing 3 months of data, and you can change that setting, to show more or fewer months.

在下面的屏幕截图中，中央区域自动添加到标题中，因为它首先出现在11月数据中。 它显示3个月的数据，您可以更改该设置以显示更多或更少的月份。

这个怎么运作 (How It Works)

In addition to the pivot table summary, AlexJ made a few other changes to the workbook,

除了数据透视表摘要外，AlexJ还对该工作簿进行了其他一些更改，

• It uses the first day of the month rather than the last
  它使用每月的第一天而不是最后一天
• Instead of the current date, the dates are driven by the latest date in the sales data.
  日期由销售数据中的最新日期代替，而不是当前日期。
• A new calculated field in the sales data identifies if the entry should be included in the final presentation table.
  销售数据中新计算的字段标识条目是否应包括在最终展示表中。
• Added a parameter to define how many months of data to show.
  添加了一个参数来定义要显示多少个月的数据。
• One line of VBA, to refresh the pivot table, when you click the scroll bar.
  当您单击滚动条时，一行VBA用于刷新数据透视表。

Here is the Admin sheet, where you can change the settings in the yellow cells. The grey cells have formulas that calculate the date range for the report.

这是管理表，您可以在其中更改黄色单元格中的设置。 灰色单元格具有用于计算报告日期范围的公式。

下载样本文件 (Download the Sample File)

To see AlexJ’s workbook, you can download his sample file. On my Contextures website, go to the AlexJ Sample Files page, and in the Pivot Tables section, look for PT0004 - Change Pivot Table Date Range With Scroll Bar.

要查看AlexJ的工作簿，您可以下载他的样本文件。 在我的Contextures网站上，转到AlexJ示例文件页面 ，然后在“数据透视表”部分中查找PT0004-使用滚动条更改数据透视表日期范围 。

翻译自: https://contexturesblog.com/archives/2013/02/28/update-pivot-table-with-scroll-bar/

数据表 滚动条事件