excelperfect 使用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')) 其原理与上文所讲解的单个工作表的版本相同。 注:本技巧整理自excelxor.com,有兴趣的朋友对照原文研读,收获更丰。
|
|
来自: hercules028 > 《excel》