首页 > Excel专区 > Excel教程 >

SUMIFS/COUNTIFS函数内的间接列引用变化

Excel教程 2021-12-17 21:37:07

使用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”))

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


标签: excel函数excel函数公式excel表格制作excel教程

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.