Excel图表 - 动态设置系列结束 [英] Excel charts - setting series end dynamically

问题描述

我有一个包含大量图表的电子表格和一张包含大量数据的表格.

I've got a spreadsheet with plenty of graphs in it and one sheet with loads of data feeding those graphs.

我已经使用

=Sheet1!$C5:$C$3000

这基本上只是在图表上绘制 C5 到 C3000 中的值.

This basically just plots the values in C5 to C3000 on a graph.

虽然我只想查看数据的一个子集，例如，我可能只想查看前 1000 行.目前要做到这一点，我必须修改每个图表中的公式，这需要时间.

Regularly though I just want to look at a subset of the data i.e. I might just want to look at the first 1000 rows for example. Currently to do this I have to modify the formula in each of my graphs which takes time.

你知道有什么方法可以简化这个吗?理想情况下，如果我可以在单张纸上有一个单元格，它会从行号中读取并绘制从 C5 到 C行号"的所有图表.

Would you know a way to simplify this? Ideally if I could just have a cell on single sheet that it reads in the row number from and plots all the graphs from C5 to C 'row number' would be best.

任何帮助将不胜感激.

推荐答案

好的，我不得不做更多的研究，这里是如何使它工作，完全在电子表格中(没有 VBA):

OK, I had to do a little more research, here's how to make it work, completely within the spreadsheet (without VBA):

使用 A1 作为所需范围的结尾，并且图表与数据在同一张纸上:

Using A1 as the end of your desired range, and the chart being on the same sheet as the data:

将数据的第一个单元格 (C5) 命名为命名范围，例如 TESTRANGE.
按照以下公式创建命名范围 MYDATA:

Name the first cell of the data (C5) as a named range, say TESTRANGE.
Created a named range MYDATA as the following formula:

=OFFSET(TESTRANGE, 0, 0, Sheet1!$A$1, 1)

=OFFSET(TESTRANGE, 0, 0, Sheet1!$A$1, 1)

现在，转到图表源数据对话框的系列选项卡，并将您的 VALUES 语句更改为:

Now, go to the SERIES tab of the chart SOURCE DATA dialog, and change your VALUES statement to:

=Sheet1!MYDATA

=Sheet1!MYDATA

现在每次更改 A1 单元格值时，它都会更改图表.

Now everytime you change the A1 cell value, it'll change the chart.

感谢 Robert Mearns 指出我之前回答中的缺陷.

Thanks to Robert Mearns for catching the flaws in my previous answer.

这篇关于Excel图表 - 动态设置系列结束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！