UMIFSCOUNTIFS函数内的间接列引用变化
作者&投稿：哈聪 （若有异议请与网页底部的电邮联系）
=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”))
其原理与上文所解的单个工作表的版本相同。
夕馥蕲蛇： SUMIF:函数对符合指定条件的单元格求和. SUM(IF):函数用于计算单个或多个参数之和. COUNT和COUNTIF两者联用,一般用于计算优生率、合格率等.
东港区15899236067： countif函数的使用方法 - ？
夕馥蕲蛇： OUNTIF函数可以统计单元格区域中满足给定条件的单元格的个数,该函数的语法结构为COUNTIF(rage,criteria),其参数range表示需要统计其中满足条件的单元格数目的单元格区域;criteria表示指定的统计条件,其形式可以为数字、表达式、单元格引用或文本.在运用COUNTIF函数时要注意,当参数criteria为表达式或文本时,必须用引号引起来,否则将提示出错.
东港区15899236067： 请教EXCEL中的SUMIF函数公式怎么应用 - ？
夕馥蕲蛇： SUMIF( 条件区域, 条件, 求和区域) 如果你需要计算A列中产品名称为＂毛巾＂, B列的数量总和 可以使用下面这个公式 =SUMIF(A:A,＂毛巾＂,B:B)
东港区15899236067： Excel countif怎么求指定日期范围内的数量 - ？
夕馥蕲蛇： 输入: =COUNTIFS(A:A,＂>=2017-1-1＂,A:A,＂<=2017-1-15＂)COUNTIF只能对单一条件进行计数. 对于指定日期内数量求和,COUNTIF函数是无法满足的.用万能多条件求和(计数也可以)函数SUMPRODUCT可以完成.举例说明. M2公式:=SUMPRODUCT(($A$2:$A$11>=$J$2)*($A$2:$A$11<=$K$2)*($C$2:$C$11=M$1)*($D$2:$D$11)) 满足3个条件:1、开始日期;2、结束日期;3、业务员=＂A”对销售额求和.
东港区15899236067： excel函数IF的用法 - ？
夕馥蕲蛇： IF 请参阅 执行真假值判断,根据逻辑计算的真假值,返回不同结果. 可以使用函数 IF 对数值和公式进行条件检测. 语法 IF(logical_test,value_if_true,value_if_false) Logical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达式.例如,A10=...
东港区15899236067： excel中公式COUNTIF怎么用 - ？
夕馥蕲蛇： =countif(A1:A10,1) 这个的含义就是统计在 A1--A10这个范围内 统计值为1 的单元格个数
东港区15899236067： countif函数的使用 - ？
夕馥蕲蛇： 应该再加一列,比如叫做“统计标准”,那么表的列为:A B C D E 编号 姓名 实发工资 补贴 统计标准 A-01 地方 1025 45.3 =IF(AND(D2>70,C2<2000),＂√＂,＂＂)按照上面的办法,用函数计算E列的所有数值,最后才对E列进行COUNTIF,得出你的结果,公式为=COUNTIF(E2:E1000,＂√＂)类似的办法,VLOOCKUP、SUMIF等函数,如果有多个条件,都需要增加一个复合列作为控制,否则无法实现.
东港区15899236067： 关于一组Excel中的求合(SUMIF函数) - ？
夕馥蕲蛇： 在C列输 =if(b2=＂预算外＂,if(a2>10,a2,0),0) 然后计算C列的和即可.
东港区15899236067： 在excel里函数里的名次与四舍五入如何输入? - ？
夕馥蕲蛇： 在Excel提供的“数学与三角函数”中提供了函数:ROUND(number,num_digits),它的功能就是根据指定的位数将数字四舍五入.这个函数有两个参数,分别是number和num_digits,其中number就是将要进行四舍五入的数字,num_digits则是希望得到数字的小数点后的位数.
东港区15899236067： excel中统计个数的函数怎么用 - ？
夕馥蕲蛇： 若是分开统计 ,用countif, =countif(a1:m1,＂√＂),五角星以此类推. 若是一起统计 =sumproduct((isnumber(find({＂√＂,＂★＂},a1:m1)))*1)
