SUMIFS/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(INDIRECT(“‘”&$A$1&”‘!C:C”),INDIRECT(“‘”&$A$1&”‘!A:A”),”X”,INDIRECT(“‘”&$A$1&”‘!B:B”),”Y”)

其中，A1包括要援用的工作表名称（例如“Sheet1”）。

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

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

=SUMIFS(INDIRECT(“‘”&$A$1&”‘!D:D”),INDIRECT(“‘”&$A$1&”‘!A:A”),”X”,INDIRECT(“‘”&$A$1&”‘!B:B”),”Y”)

=SUMIFS(INDIRECT(“‘”&$A$1&”‘!E:E”),INDIRECT(“‘”&$A$1&”‘!A:A”),”X”,INDIRECT(“‘”&$A$1&”‘!B:B”),”Y”)

等等。

可使用INDEX函数来解决：

=SUMIFS(INDEX(INDIRECT(“‘”&$A$1&”‘!A:XFD”),,COLUMNS($A:C)),INDIRECT(“‘”&$A$1&”‘!A:A”),”X”,INDIRECT(“‘”&$A$1&”‘!B:B”),”Y”)

注意，当省略INDEX函数的参数row_num时，会返回整列援用，对参数column_num也是如此。

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

INDEX(INDIRECT(“‘”&$A$1&”‘!A:XFD”),,COLUMNS($A:C))

转换为：

=INDEX(Sheet2!A:XFD,,3)

即：

Sheet2!C:C

向右复制，公式成为：

=SUMIFS(INDEX(INDIRECT(“‘”&$A$1&”‘!A:XFD”),,COLUMNS($A:D)),INDIRECT(“‘”&$A$1&”‘!A:A”),”X”,INDIRECT(“‘”&$A$1&”‘!B:B”),”Y”)

转换为：

=SUMIFS(INDEX(Sheet2!A:XFD,,COLUMNS($A:D)),INDIRECT(“‘”&$A$1&”‘!A:A”),”X”,INDIRECT(“‘”&$A$1&”‘!B:B”),”Y”)

转换为：

=SUMIFS(INDEX(Sheet2!A:XFD,,4),Sheet2!A:A,”X”,Sheet2!B:B,”Y”)

转换为：

=SUMIFS(Sheet2!D:D,Sheet2!A:A,”X”,Sheet2!B:B,”Y”)

……

也能够使用OFFSET函数：

=SUMIFS(OFFSET(INDIRECT(“‘”&$A$1&”‘!A:A”),,COLUMNS($A:B)),INDIRECT(“‘”&$A$1&”‘!A:A”),”X”,INDIRECT(“‘”&$A$1&”‘!B:B”),”Y”)

其中：

INDIRECT(“‘”&$A$1&”‘!A:A”)

转换为：

Sheet2!A:A

而偏移的列数等于：

COLUMNS($A:B)

即2，因而传递到OFFSET函数后得到：

Sheet2!C:C

但是，如果间接援用的不是一个工作表，而是多个工作表，如何处理？例如公式：

=SUMPRODUCT(SUMIFS(INDIRECT(“‘”&Sheets&”‘!C:C”),INDIRECT(“‘”&Sheets&”‘!A:A”),”X”,INDIRECT(“‘”&Sheets&”‘!B:B”),”Y”))

其中，“Sheets”是定义内涵的名称，援用位置为：

={“Sheet1″,”Sheet2″,”Sheet3″,”Sheet4”}

像前面一样，我们希望向右拖拉时，公式变化为：

=SUMPRODUCT(SUMIFS(INDIRECT(“‘”&Sheets&”‘!D:D”),INDIRECT(“‘”&Sheets&”‘!A:A”),”X”,INDIRECT(“‘”&Sheets&”‘!B:B”),”Y”))

=SUMPRODUCT(SUMIFS(INDIRECT(“‘”&Sheets&”‘!E:E”),INDIRECT(“‘”&Sheets&”‘!A:A”),”X”,INDIRECT(“‘”&Sheets&”‘!B:B”),”Y”))

等等。

依然可使用OFFSET函数：

=SUMPRODUCT(SUMIFS(OFFSET(INDIRECT(“‘”&Sheets&”‘!A:A”),,COLUMNS($A:B)),INDIRECT(“‘”&Sheets&”‘!A:A”),”X”,INDIRECT(“‘”&Sheets&”‘!B:B”),”Y”))

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