如何使用单元格值来定义图表数据的范围？(How to use cell values to define the range of chart data?)

如何使用单元格值来定义图表数据的范围？(How to use cell values to define the range of chart data?)

我有一组500个单元格，我使用以下范围定义创建了一个图表：

=Sheet1!$A$1:$A$500

我有2个单元格B1和B2 ，我想在图表中定义开始和结束值（1-500内），以仅显示我的数据的一个子集（假设从20到200）。

如何引用单元格B1和B2来取代我的数据范围定义中的$1和$500 ？

I have a set of 500 cells from which I've created a chart using the following range definition:

=Sheet1!$A$1:$A$500

I have 2 cells B1 and B2 in which I would like to define start and end values (inside 1-500) for the chart to show only a subset of my data (let's say from 20 to 200).

How can I reference the cells B1 and B2 to replace $1 and $500 in my data range definition?

最满意答案

一般来说，您应该使用计算命名区间的动态图表，并借助定义图表系列的SERIES函数中使用的OFFSET和COUNTA 。

这里有很多解释，但这里是我自己创建的员工培训示例文件： https ： //www.dropbox.com/s/5enub45nn4y36gz/DynamicCharts4Types.xlsx

它包括4种不同类型的应用动态，如显示最后的X点，从X到Y的显示点等。要查看魔术，请转到功能区Formulas > Name Manager并检查如何定义不同的范围。

In general, you should use dynamic charts with calculated named ranges with help of OFFSET and COUNTA used in SERIES function that defines chart series.

It's pretty much to explain here, but here is my own sample file created for staff training: https://www.dropbox.com/s/5enub45nn4y36gz/DynamicCharts4Types.xlsx

It includes 4 different types of applied dynamics, such as show last X points, show points from X to Y, etc. To see the magic go to Ribbon Formulas > Name Manager and check how different ranges are defined.