使用 INDIRECT 的动态图表范围:该函数无效(尽管范围突出显示) [英] Dynamic chart range using INDIRECT: That function is not valid (despite range highlighted)
问题描述
我正在尝试使用 INDIRECT
函数创建一个范围动态构建的图表.Excel 确实识别我使用 INDIRECT
创建的范围,因为它突出显示了工作表上的相应范围:
但是,在保存图表时,我收到一条错误消息,指出该函数无效:
有谁知道问题是什么/如何创建从特定起点到特定终点的动态图表范围?
PS:,介绍了如何在图表中使用动态范围.
I'm trying to create a chart with a range built dynamically using the INDIRECT
function. Excel does recognize the range I am creating using INDIRECT
as it highlights the corresponding range on the sheet:
However when saving the chart, I get an error message saying the function is not valid:
Does anybody know what the problem is / how to create a dynamic chart range from a specific start to specific end point?
PS: You can download the above spreadsheet here. The formula I was using:
=INDIRECT("sheet!"&E2&":"&E3)
Mine is similar to Sean's excellent answer, but allows a start and end day. First create two named ranges that use Index/Match formulas to pick the begin and end days based on E2 and 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))
You can then click the series in the chart and modify the formula to:
=SERIES(Sheet1!$B$1,Sheet1!rngDay,Sheet1!rngValue,1)
Here's a nice Chandoo post on how to use dynamic ranges in charts.
这篇关于使用 INDIRECT 的动态图表范围:该函数无效(尽管范围突出显示)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!