如何在间接函数中使用单元格引用

Question

我有几个单元格从多个工作表中计算单个单元格的总和，在本例中，"Y3“来自每周的每个工作表，其中n是从1到10之间的整数：

=SUMPRODUCT(COUNTIF(INDIRECT("'Week"&{1,2,3,4,5,6,7,8,9,10}&"'!Y3"),"W"))

我希望我的工作簿中有一个单元格，其中包含: A1：

1,2,3,4,5,6,7,9,10

这样，当我添加要计数的工作表时，我只能更新一个单元格。我试过几件事，但似乎没有什么效果。

Answer 1

你的公式：=SUMPRODUCT(COUNTIF(INDIRECT("'Week"&{1,2,3,4,5,6,7,8,9,10}&"'!Y3"),"W"))

这个公式只适用于10张有名称为Week1、Week2、Week3 3…的表.Week10

但是，如果你有少于10张，例如Week1，Week2，Week3 3….Week9，您的公式将失败并返回"#REF!"错误。

公式的解决方法是使用IFERROR()进行包装，并变成：

=SUMPRODUCT(IFERROR(COUNTIF(INDIRECT("'Week"&{1,2,3,4,5,6,7,8,9,10}&"'!Y3"),"W"),0))

但是，如果您希望在A1中执行动态的公式，请输入：1,2,3,4,5,6,7,9,10

但是1,2,3,4,5,6,7,9,10是一个文本字符串，您需要将它转换为一个数组。使用FILTERXML()可以完成自2013年以来可用的工作

这是一个数组公式，您需要通过按CTRL+SHIFT+ENTER来确认，而不是只输入：

=SUM(IFERROR(COUNTIF(INDIRECT("'Week"&FILTERXML("<a><b>"&SUBSTITUTE(A1,",","</b><b>")&"</b></a>","//b")&"'!Y3"),"W"),0))

编辑1：

和,

如果没有Excel 2013，则可以使用这个更长的数组(CSE)公式

=SUM(IFERROR(COUNTIF(INDIRECT("'Week"&TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",",))+1))*99,99))&"'!Y3"),"W"),0))

在那之后,

您可以在不更改公式内容的情况下调整A1编号。