更多文章,可关注微信公众号:excelwork
我们在使用Excel的时候,经常会遇到条件判别的问题,并将判别后的结果进行计算。而在Excel工具中,提供了几个常用的函数,如countif、countifs、sumif、sumifs、averageif、averageifs等,语法基本相同,下面举几个例子我们来看下函数用法。
以averageif函数为例,语法如下:
AVERAGEIF(range, criteria, [average_range])
即averageif(要计算的单元格,应用的条件,要计算的实际单元格),其中,当“要计算实际单元格”为空时,则计算“要计算的单元格”。接下来,我们通过实际例子来理解此类函数用法。
如上数据所示:
a. 想计算quantity大于1的平均销售金额amount:
=AVERAGEIF(D:D,">1",C:C)
结果为:62.85714
b. 当实际计算区域省略时,则计算大于1的平均销售数量quantity:
=AVERAGEIF(D:D,">1")
结果为:5
c. 如果多加一个条件,计算重庆销售数量大于1的平均销售金额,这时,averageif就无法满足要求,多条件判别,我们需要使用averageifsha函数:
基本语法如下:
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)看官网介绍说是127个条件。
由上述公式我们可以很容易看到,averageifs函数与averageif函数最大的区别在于:criteria_range增加,即可多条件判别。另外,averageifs函数中average_range前置替代了averageif函数中range参数,回到我们的例子上来:
=AVERAGEIFS(D:D,D:D,">1",B:B,"重庆")
结果为:7
可以看到,=AVERAGEIFS(D:D,D:D,">1",必须将实际要计算的单元格放在第一个位置,而不能像averageif函数一样AVERAGEIFS(D:D,">1",B:B,"重庆",D:D),这么写是不对的,系统会认为你最后的D:D条件没写完。
d. 既然说到*ifs系列函数,那不得不说说通配符的使用。
可以使用问号(?)以及星号(*),区别在于问号匹配单个字符,星号匹配系列字符
计算北京平均quantity的话:
=AVERAGEIFS(D:D,B:B,"北京*")
结果为:2.333333
e. 最后还是强调下函数实际能发挥的作用,如果是文本是会跳过计算,比如:
=AVERAGEIFS(D:D,B:B,"北京*")
结果为:5
再举个AVERAGEA函数的例子,进一步理解,如果空值按0算,可使用AVERAGEA函数,如果不需要,则使用average系列除AVERAGEA这种明确注明参数特性的函数,
-
参数可以是下列形式:数值;包含数值的名称、数组或引用;数字的文本表示;或者引用中的逻辑值,例如 TRUE 和 FALSE。
-
包含文本的数组或引用参数将作为 0(零)计算。空文本 ("") 计算为 0(零)。
=AVERAGEA(D2:D9)
结果为:3.625
如果正常计算,是跳过写着空白文字的单元格,个数少了这两个,再去取均值。