excel - 如何在 INDIRECT 函数中使用单元格引用

Question

我有几个单元格计算来自多个工作表的单个单元格的总数，在这种情况下，来自 Week 中的每个工作表的“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….Week10 的工作表时才有效

但是，如果您的纸张少于 10 张，例如第 1 周、第 2 周、第 3 周……第 9 周，您的公式将失败并返回 "#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()可以完成自 Excel 2013 以来可用的工作

这是一个数组公式，您需要按 CTRL+SHIFT+ENTER 而不是 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不改变公式内容的数字