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)