UMIFS/COUNTIFS函数中的间接列引用更改

每个使用Excel的人都知道，当包含相对列引用的公式被复制到其他列时，这些引用会相应地更新。例如，公式：

=SUMIFS(C:C，A:美元A，“X”，B:美元B，“X”)

当您拖放到右侧时，它将变成：

=SUMIFS(D:D，$A:$A，“X”，$B:$B，“X”)

=SUMIFS(E:E，A:美元A，“X”，B:美元B，“X”)

等等。

因此，我们有一个相对简单的方法，可以从连续的列中获得条件和。

但是，如果我们想间接引用添加的单元格区域，应该怎么做呢？例如，如果我们使用上面的公式版本，但引用的工作表是动态的：

=SUMIFS(间接(" ' " $A$1 " '！C:C ")、间接(" ' " $A$1 " '！A:A ")、" X "、间接(" ' " $A$1 " '！B:B”)，“Y”)

其中A1包含要引用的工作表的名称(例如，“Sheet1”)。

当我们将此公式向右拖动时，间接引用的单元格区域不会改变。当然，这是完全可以预测的，因为那些“单元格区域”根本不是真正的单元格区域，而只是伪装成单元格区域的文本字符串。只有将它们传递给间接函数，它们才能被解释为实际的单元格区域。

现在的问题是：我们如何修改第一个公式，以便在将其复制到右侧后，我们可以依次获得以下等价公式：

=SUMIFS(间接(" ' " $A$1 " '！D:D)，间接(“”$A$1“！A:A ")、" X "、间接(" ' " $A$1 " '！B:B”)，“Y”)

=SUMIFS(间接(" ' " $A$1 " '！E:E ")、间接(" ' " $A$1 " '！A:A ")、" X "、间接(" ' " $A$1 " '！B:B”)，“Y”)

等等。

您可以使用INDEX函数来求解：

=SUMIFS(INDEX(INDEX)(“'”$ A $ 1 '”！A:XFD ")，COLUMNS($A:C))，'间接(' " $A$1 " '！A:A ")、" X "、间接(" ' " $A$1 " '！B:B”)，“Y”)

请注意，当省略INDEX函数的参数row_num时，将返回整个列引用，参数column_num也是如此。

如果A1中的值是“Sheet2”，则：

INDEX(间接(“”$A$1“！A:XFD ")、COLUMNS($A:C))

转换为：

=INDEX(Sheet2！A:XFD，3)

那就是：

Sheet2！C:C

向右复制，公式变为：

=SUMIFS(INDEX(INDEX)(“'”$ A $ 1 '”！A:XFD ")，COLUMNS($A:D))，'间接(' " $A$1 " '！A:A ")、" X "、间接(" ' " $A$1 " '！B:B”)，“Y”)

转换为：

=SUMIFS(INDEX(Sheet2！A:XFD，COLUMNS($A:D))，'间接(' " $A$1 " '！A:A ")、" X "、间接(" ' " $A$1 " '！B:B”)，“Y”)

转换为：

=SUMIFS(INDEX(Sheet2！A:XFD，4)，Sheet2！A:A，“X”，第2页！B:B，“Y”)

转换为：

=SUMIFS(Sheet2！D:D，Sheet2！A:A，“X”，第2页！B:B，“Y”)

……

您也可以使用偏移功能：

=SUMIFS(OFFSET(INDIRECT)(' " $ A $ 1 " '！A:A ")、COLUMNS($A:B))、INDIRECT(' " $ A $ 1 " '！A:A ")、" X "、间接(" ' " $A$1 " '！B:B”)，“Y”)

其中：

间接(“”$A$1“！A:A”)

转换为：

Sheet2！A:A

偏移列数等于：

COLUMNS(a : b)

也就是说，在传递到OFFSET函数之后，我们得到：

Sheet2！C:C

但是，如果间接引用不是一个工作表，而是多个工作表，怎么办？例如公式：

=SUMPRODUCT(SUMIFS(间接(“‘Sheets’”)！C:C)，间接(“‘Sheets’”)！A:A”)、“X”、间接(“‘Sheets’”)！B:B ")、" Y "))

其中“图纸”是定义的名称，参考位置是：

={“第1页”、“第2页”、“第3页”、“第4页”}

和以前一样，当我们想要向右拖动时，公式会更改为：

=SUMPRODUCT(SUMIFS(间接(“‘Sheets’”)！D:D)，间接(“‘Sheets’”)！A:A”)、“X”、间接(“‘Sheets’”)！B:B ")、" Y "))

=SUMPRODUCT(SUMIFS(间接(“‘Sheets’”)！E:E)，间接(“‘Sheets’”)！A:A”)、“X”、间接(“‘Sheets’”)！B:B ")、" Y "))

等等。

您仍然可以使用偏移功能：

=SUMPRODUCT(SUMIFS(OFFSET(好奇)(“‘Sheets’”)”！A:A”)、COLUMNS(a : b))、间接(“‘床单’)！A:A”)、X、间接(“‘床单’)！B:B”)、“Y”))

其原理与上文所解的单个工作表的版本相同。