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.

我绘制了每个图表上的数据使用

I've plotted the data on each graph using

=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)

现在，转到图表SOURCE DATA对话框的$ SERIES选项卡
，将您的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屋！