我正在尝试使用INDIRECT
函数创建一个动态生成的图表范围。Excel可以识别我使用INDIRECT
创建的范围,因为它会在工作表上突出显示相应的范围:
但是当保存图表时,我收到一个错误消息,说该函数无效:
有人知道问题所在/如何从特定的起点到特定的终点创建动态图表范围吗?
附注:您可以在此处下载上述电子表格。我使用的公式:
=INDIRECT("sheet!"&E2&":"&E3)
我正在尝试使用INDIRECT
函数创建一个动态生成的图表范围。Excel可以识别我使用INDIRECT
创建的范围,因为它会在工作表上突出显示相应的范围:
但是当保存图表时,我收到一个错误消息,说该函数无效:
有人知道问题所在/如何从特定的起点到特定的终点创建动态图表范围吗?
附注:您可以在此处下载上述电子表格。我使用的公式:
=INDIRECT("sheet!"&E2&":"&E3)
您尝试的方法是行不通的。图表数据范围必须具有固定地址。
有一种解决方法,那就是使用命名区域。
在单元格中输入您要在数据中使用的行数(例如E1)。
因此,使用您的示例,我将“Number of Rows”放在D1中,将6放在E1中
在名称管理器中,定义数据和标题的名称。
我使用了xrange和yrange,并将它们定义为:
xrange: =OFFSET(Sheet1!$A$2,0,0,Sheet1!$E$1)
yrange: =OFFSET(Sheet1!$B$2,0,0,Sheet1!$E$1)
现在,到达您的图表 - 您需要知道工作簿的名称(一旦设置,Excel的跟踪更改功能将确保引用保持正确,无论任何重命名)
将“Chart data range”留空
对于“Legend Entries(Series)”,像往常一样输入标题,然后输入您为数据定义的名称(请注意,使用命名区域需要工作簿名称)
对于“Horizontal(Category)Axis Labels”,输入您为标签定义的名称
现在,通过更改E1中的数字,您将看到图表发生变化:
$E$1
单元格中行数),但起始点不是。我改编了Doug Glancy
的示例:假设我在单元格$E$2
中定义了开始日期,然后yrange
变为=OFFSET(INDIRECT("Sheet1!$A$"&MATCH(Sheet1!$E$2,Sheet1!$A:$A,0)),0,0,Sheet1!$E$1)
。 - Max=OFFSET(Sheet1!$A$1,LinesToSkip,0,LinesOfData)
——Offset函数需要在起始位置之后提供2-4个参数:第1和第2个参数表示从起始位置移动的行数和列数,第3和第4个参数表示要返回的行数和列数(默认为1)。 - SeanC='My Data.xlsx'!x_axis
。 - SeanC我的方法与Sean的优秀答案类似,但允许指定开始和结束日期。首先创建两个命名区域,使用Index/Match公式基于E2和E3选择开始和结束日期:
rngDay
=INDEX(Sheet1!$A:$A,MATCH(Sheet1!$E$2,Sheet1!$A:$A,0)):INDEX(Sheet1!$A:$A,MATCH(Sheet1!$E$3,Sheet1!$A:$A,0))
rngValue
=INDEX(Sheet1!$B:$B,MATCH(Sheet1!$E$2,Sheet1!$A:$A,0)):INDEX(Sheet1!$B:$B,MATCH(Sheet1!$E$3,Sheet1!$A:$A,0))
您可以单击图表中的系列,并修改公式为:
=SERIES(Sheet1!$B$1,Sheet1!rngDay,Sheet1!rngValue,1)
这是一篇关于如何在图表中使用动态范围的Chandoo文章。
这是另一篇与位和谷歌相关的答案。
如果您仍然想引用起始单元格和结束单元格,则需要为您的Day Range和Values Range添加单独的公式。以下是公式,屏幕截图显示了使用的公式。
Day Range:
="Sheet1!"&$F$2&":"&ADDRESS(ROW(INDIRECT($F$3)),COLUMN(INDIRECT($F$2)))
Values Range:
="Sheet1!"&ADDRESS(ROW(INDIRECT($F$2)),COLUMN(INDIRECT($F$3)))&":"&$F$3
然后添加两个引用这些单元格INDIRECT
值的范围
按Ctrl + F3,点击New,并添加一个名为“chart_days”的新范围,引用=INDIRECT(Sheet1!$F$4)
;以及一个名为“chart_values”的新范围,引用=INDIRECT(Sheet1!$F$5)
最后,在您的图表中,添加一个系列,引用=nameOfYourWorkbook!chart_values
并编辑类别,引用=nameOfYourWorkbook!chart_days
Jaycal
,只是有点遗憾的是你没有将F4/F5
公式放入你的答案中,我们无法从截图中复制/粘贴 :( - Max我使用OFFSET
来创建一个定义名称公式,以便我可以定义所有数据的范围,从而使我拥有起始日期和结束日期(或任何数据集的开始和结束位置)。
对于简单的图表,我将名称CategoryLabels定义如下:
= OFFSET($A$5; (InicitialMonth-1); 0; LastMonth - (InitialMonth-1))
并且DataCars如下:
= OFFSET($B$5; (InicitialMonth-1); 0; LastMonth - (InitialMonth-1))
您需要定义与所需包含的系列数量相同的名称,按照相同的过程进行。在这个简单的例子中,我只包括了汽车销售。
初始月份和最后一个月份是范围名称,用于表示将包括图表的哪些月份(从起始月到结束月)中使用的单个单元格。
请记住,正如Sean Cheshire和其他人所解释的那样,要使用图表值的名称,必须包括电子表格的名称。
当折线图的范围是一个命名变量,并且该变量通过单元格对INDIRECT()引用到一个范围时,该变量必须至少有两个由逗号分隔的INDIRECT()。
使用间接函数的命名公式在图表中无法正常工作。它可以在其他形式中正常工作,因为您所需的动态源将被突出显示,但是当您在图表中使用它时,它将不会被计算。希望微软能够解决这个问题。
INDIRECT
:( 如果你尝试使用=INDIRECT("Sheet!$A$3:$B$7")
,这也会抛出一个错误。等待看看是否有其他人有解决方法! - JerryINDIRECT
来创建可以以这种方式使用的单元格“范围”。 - MartinINDIRECT
或其他函数并不重要,只要我能像示例中那样定义范围的起始和结束即可。 - Max