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(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”))

其原理與上文所解的單個工作表的版本相同。