通过单元格的值动态设置Excel图表数据系列范围 [英] Set Excel chart data series range dynamically by a cell's value

问题描述

我在Excel中有一个简单的图表，其中绘制了涉及第三个数据的2个数据系列.

I have a simple chart in Excel where I plot 2 data series referring to a 3rd one.

我要做的是根据单元格中的数字更改第一个数据系列之一的范围.例如，如果数字为1并且数据系列在A列中，则图表的范围应为A1:A100.现在，如果我将数字更改为10，则图表范围应为A10:A100.

What I want to do is to change the range of one of the first data series based on a number in a cell. For example if the number is 1 and the data series is in column A the range of the chart should be A1:A100. Now if I change the number to 10 the chart range should be A10:A100.

这似乎并不难，但我无法弄清楚.我一直在搜索StackOverflow和网络很长时间，但找不到任何能解决我问题的东西.

This doesn't seem hard but I cant figure it out. I've been searching StackOverflow and the web for a long time but I couldn't find anything that seems to solve my problem.

在此先感谢所有可以提供帮助的人！

Thanks in advance to everyone who can help!

推荐答案

您可以使用OFFSET函数确定要使用的范围.

You can use OFFSET function to determine the range to work with.

1)变量开始:

如果E2保留该数字，并且您在工作表1中，则可以使用以下命令进行定义:

If E2 held the number, and you are in sheet 1, you could define with:

=OFFSET(Sheet1!$A$1,Sheet1!$E$2-1,0,100-Sheet1!$E$2+1,1)

1. 如果E2为1，则范围为A1:A100
2. 如果E2为10，则范围为A10:A100，等等.

---

2)变量开始和结束:

您可以扩展它，并为终点设置一个变量-而不是硬编码100，您可以使用单元格F2指定终点，并将公式更改为:

You could extend this and have a variable for the end point - rather than hard code 100, you could use cell F2 to specify the end point, and change the formula to:

=OFFSET(Sheet1!$A$1,Sheet1!$E$2-1,0,Sheet1!$F$2-Sheet1!$E$2+1,1)

示例，带有开始和结束(请注意，开始到结束是最终范围的height参数.如果要使用一列，将width参数设置为1):

Example, with start and end (note that start to end is the height argument of the final range. As you want a single column, the width argument is set at 1):

3)感兴趣的变量开始，结束和列(即，不仅是A列):

然后您可以通过指定offset columns参数来进一步扩展，以从使用A列开始转移.假设我们使用G2来指定向右移动多少列，我们得到:

You could then further extend by specifying the offset columns arguments to shift from working with column A. Let's say we use G2 to specify how many columns to move to the right, we get:

=OFFSET(Sheet1!$A$1,Sheet1!$E$2-1,Sheet1!$G$2,Sheet1!$F$2-Sheet1!$E$2+1,1)

哪个是:

详细了解OFFSET:

在编辑栏中键入=OFFSET()，然后按 f _x 符号以展开参数:

Type =OFFSET() into the formula bar and press the f_x symbol to expand the arguments:

您将看到它以锚点(参考)单元格开头，例如A1，然后是行，列，高度，宽度参数.从A1说出您要移动多少行，要移动的列以及最终范围应该是什么高度和宽度.

You will see it start with an anchor (reference) cell e.g. A1 and then rows, columns, height, width arguments. Saying from A1 how many rows do you want to move, columns to move, and what height and width should the final range be.

其他信息:

1. https ://support.microsoft.com/en-gb/help/324991/how-to-use-the-offset-function-in-excel
2. http://www.contextures.com/exceloffsetfunction.html

1. https://support.microsoft.com/en-gb/help/324991/how-to-use-the-offset-function-in-excel
2. http://www.contextures.com/exceloffsetfunction.html

这篇关于通过单元格的值动态设置Excel图表数据系列范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！