excelperfect

使用Excel朋友都知道，將包含相對列引用的公式復(fù)制到其他列時,，這些引用也會相應(yīng)地更新,。例如,，公式：

=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')

等等,。

因此,，我們有一個相對簡單的方法，可以從連續(xù)的列中獲得條件和,。

但是,，如果我們希望增加的單元格區(qū)域是間接引用的，那該怎么辦,？例如,，如果我們使用上述公式版本，但所引用的工作表是動態(tài)的：

=SUMIFS(INDIRECT('''&$A$1&''!C:C'),INDIRECT('''&$A$1&''!A:A'),'X',INDIRECT('''&$A$1&''!B:B'),'Y')

其中,，A1包含要引用的工作表名稱（例如“Sheet1”）,。

當(dāng)我們向右拖動此公式時，間接引用的單元格區(qū)域不會改變,。當(dāng)然,，這是完全可以預(yù)料的，因?yàn)槟切皢卧駞^(qū)域”根本不是真正的單元格區(qū)域,，而只是偽裝成單元格區(qū)域的文本字符串,，只有通過將它們傳遞給INDIRECT函數(shù)才能將其解釋為實(shí)際的單元格區(qū)域。

現(xiàn)在的問題是：我們?nèi)绾涡薷牡谝粋€公式,，以便將其向右復(fù)制后,，依次獲得以下等價的公式：

=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函數(shù)來解決：

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

注意,，當(dāng)省略INDEX函數(shù)的參數(shù)row_num時,，會返回整列引用，對于參數(shù)column_num也是如此,。

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

INDEX(INDIRECT('''&$A$1&''!A:XFD'),,COLUMNS($A:C))

轉(zhuǎn)換為：

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

即：

Sheet2!C:C

向右復(fù)制，公式成為：

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

轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

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

……

也可以使用OFFSET函數(shù)：

=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')

轉(zhuǎn)換為：

Sheet2!A:A

而偏移的列數(shù)等于：

COLUMNS($A:B)

即2,，于是傳遞到OFFSET函數(shù)后得到：

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函數(shù)：

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

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

注：本技巧整理自excelxor.com,，有興趣的朋友對照原文研讀，收獲更豐,。