If you add a chart to a dashboard, it’s helpful to let people choose what they’d like to see in the chart. In this example, you’ll choose a date range, and select other criteria, and the interactive Excel chart automatically adjusts to show total values for the selected data.

如果您将图表添加到仪表板，让人们选择想要在图表中看到的内容会很有帮助。 在此示例中，您将选择一个日期范围，然后选择其他条件，交互式Excel图表将自动调整以显示所选数据的总值。

Interactive Excel图表–设置数据范围 (Interactive Excel Chart – Set Data Range)

In an earlier tutorial, I showed how to use check boxes to show or hide chart data for specific regions. That chart always showed 6 months of data, but you could show East, Central or West regions.

在较早的教程中，我展示了如何使用复选框显示或隐藏特定区域的图表数据 。 该图表始终显示6个月的数据，但您可以显示东部，中部或西部地区。

That example was based on a technique that I learned from Jon Peltier, whose Excel charting tools can make your life easier, if you build charts too.

该示例基于我从乔恩·Perl捷(Jon Peltier)那里学到的技术，他的Excel图表工具可以使您的生活更加轻松，即使您也可以构建图表。

If a series isn’t selected, its data shows #N/A, and that doesn’t appear as a line in the chart.

如果未选择系列，则其数据显示为＃N / A，并且不会在图表中显示为一条线。

新的交互式Excel图表 (A New Interactive Excel Chart)

My new interactive chart has check boxes too, and uses that #N/A technique, so you can show the Quantity or Price or both series. It also lets you select dates and criteria, and creates totals from the source data, based on your selections.

我的新交互式图表也具有复选框，并使用＃N / A技术，因此您可以显示“数量”或“价格”或两个序列。 它还允许您选择日期和条件，并根据您的选择从源数据创建总计。

The chart is described briefly below, and all the details are on the Excel Interactive Totals Chart page on my website.

下面简要介绍了该图表，所有详细信息都在我网站的Excel Interactive Totals Chart页面上。

Interactive Excel图表–动态日期范围 (Interactive Excel Chart – Dynamic Date Range)

Instead of using a set range of cells for the chart data, the new chart uses a dynamic date range. Enter a start date and an end date, and formulas show that list of months.

新图表不使用图表数据的单元格范围，而是使用动态日期范围。 输入开始日期和结束日期，公式将显示该月份列表。

An OFFSET formula is used to define that ChartData range, so it automatically expands or contracts when you change the start or end date.

OFFSET公式用于定义ChartData范围，因此当您更改开始日期或结束日期时，它会自动扩展或收缩。

=OFFSET(ChartStart,1,0,COUNT(ChartDatesALL),3)

= OFFSET(ChartStart，1,0，COUNT(ChartDatesALL)，3)

其他图表条件 (Other Chart Criteria)

There are drop down lists above the chart, where you can select a Region and Category.

图表上方有下拉列表 ，您可以在其中选择地区和类别。

In the table that has the sales data, additional columns were added, to calculate Quantity and Priced, for the rows that match the selected criteria.

在具有销售数据的表中，添加了其他列，以计算与所选条件匹配的行的“数量”和“价格”。

计算图表总计 (Calculate the Chart Totals)

On the Chart Data sheet, where the selected date range is listed, there are formulas that total the calculated Quantity and Price. For example, this formula is in cell D4, to get the total quantity for the first month in the date range.

在图表数据表上，列出了选定的日期范围，其中有一些公式将计算出的数量和价格合计。 例如，此公式位于单元格D4中，以获取日期范围内第一个月的总量。

=IF($C4="","", IF(D$2=FALSE,NA(), SUMIFS(Sales_Data[CalcQty], Sales_Data[YrMth],C4)))

= IF($ C4 =“”，“”，IF(D $ 2 = FALSE，NA()，SUMIFS(Sales_Data [CalcQty]，Sales_Data [YrMth]，C4)))

In cell E4, a similar formula get the total for calculated price, and shows #N/A, if the Price check box is not checked.

在单元格E4中，如果未选中“价格”复选框，则类似的公式将获得计算价格的总和，并显示＃N / A。

制作动态图表 (Make a Dynamic Chart)

A Clustered Column chart was created first, using the static range C3:E7. Then the chart data range was changed to the dynamic named range, Chart Data.

首先使用静态范围C3：E7创建聚簇柱形图。 然后，将图表数据范围更改为动态命名范围“图表数据”。

After that, the series and the date labels were changed, to use their dynamic ranges, instead of static ranges. All the details are on the Excel Interactive Totals Chart page on my website.

此后，更改了系列和日期标签，以使用其动态范围而不是静态范围。 所有详细信息都在我网站的Excel Interactive Totals Chart页面上。

添加图表标题 (Add Chart Titles)

If you plan to print the chart on its own, you can add a chart title that shows the selected date range, and other criteria. In this example, there is a formula in cell A1, and the chart title is linked to that cell. Text boxes are linked to the two criteria cells, to show those selections.

如果计划单独打印图表，则可以添加显示所选日期范围和其他条件的图表标题。 在此示例中，单元格A1中有一个公式，并且图表标题链接到该单元格。 文本框链接到两个条件单元格，以显示这些选择。

获取交互式Excel图表工作簿 (Get the Interactive Excel Chart Workbook)

All the details for building this chart are on the Excel Interactive Totals Chart page on my website. On that page, you can download a file that has the data only, so you can build from scratch. Or, download the completed workbook, to see how the interactive Excel chart works.

构建此图表的所有详细信息都在我网站的Excel Interactive Totals Chart页面上。 在该页面上，您可以下载仅包含数据的文件，因此可以从头开始构建。 或者，下载完整的工作簿，以查看交互式Excel图表如何工作。