在图表或系列公式中使用间接函数? [英] Using the indirect function in a chart or series formula?

问题描述

我试图引用图表中已定义的命名范围，该部分工作正常.但是，我也试图通过用户输入来更改范围.

I am attempting to reference a defined named range within a chart, that part works fine. However, I am also attempting to have that range change via user input.

例如:

在下拉菜单中，我有:
苹果
葡萄
橘子

In a dropdown I have:
Apples
Grapes
Oranges

用户可以从中选择一个.这三个都被命名为范围.

Of which the user can select one. All three are named ranges.

然后我有一张这样的图表:

Then I have a chart like this one:

图表参考这样的命名范围:

The chart references the named range like so:

SERIES(Base!$AQ$1,Test.xlsx!Dates,Test.xlsx!Apples,1)

但是，当我使用间接功能将本论坛的Apple部分指向我的下拉菜单时，它不起作用:

However, when I use the Indirect function to point the Apples portion of this forumla to my dropdown, it does not work:

SERIES(Base!$AQ$1,Test.xlsx!Dates,INDIRECT("Test.xlsx!"&'BaseSheet'!$C$10),1)

C10是下拉单元格，而BaseSheet是这两个单元格都存在的工作表.名称的引用并不重要，但这是工作簿的另一部分.

C10 being the dropdown cell, and BaseSheet being the sheet where both these cells exist. The reference for th name doesn't really matter, but it's another part of the workbook.

推荐答案

您不能将Indirect()函数放入图表系列函数中.

You can't put an Indirect() function into the chart series function.

相反，您需要在名称管理器中使用命名公式创建范围引用，然后使用语法将其插入图表中

Instead, you need to create the range reference with a named formula in the Name Manager, then plug it into the chart using the syntax

='sheet name'!Range_Name

或者具有间接功能

=INDIRECT("Test.xlsx!"&'BaseSheet'!$C$10)

这篇关于在图表或系列公式中使用间接函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！