Excel AverageIfs函数的使用方法,包括六大实例

在 Excel 中,如果要多条件求平均值,可以用AverageIfs函数,它最多可以有 127 个条件,每个条件对应一个区域,即可以组合 127 个条件范围/条件对,并且一个条件范围即同列可以组合多个条件。AverageIfs函数与AverageIf函数的区别除能组合多个条件外,还表现在AverageIfs函数要求求平均值范围与条件范围必须一致,而AverageIf函数则不要求一致。以下是Excel用AverageIfs函数多条件求平均值的具体实例,含同列双条件等六个实例,实例操作所用版本均为 Excel 2016。

一、AverageIfs函数语法

1、表达式:AVERAGEIFS(Average_Range, Criteria_Range1, Criteria1, [Criteria_Range2, Criteria2], …)

中文表达式:AVERAGEIFS(求平均值范围, 条件范围1, 条件1, [条件范围2, 条件2], …)

2、说明:

A、条件范围1与条件1组成一个条件范围/条件对,最多可以有 127 个条件范围/条件对。

B、条件可以是单纯的文字,例如“白色”;也可以用大小于和等于号,例如 ">=100" 或 ">="&100。另外,条件还可以使用通配符问号(?)和星号(*),问号表示一个字符,星号表示一个或多个字符,如果要查找问号或星号,需要在它们前面加转义字符 ~,例如 ~*。

C、如果选定的单元格中有逻辑值 True 或 False 都将被忽视。

D、如果 Average_Range 为空值、文本值或无法转换为数字的其它内容,将返回分母为 0 错误(即 #DIV/0! 错误)。

E、如果条件中包含空单元格,将被视为 0 值;如果选定的区域没有满足条件的单元格,将返回 #DIV/0!错误。

F、AverageIfs函数条件范围必须与求平均值范围一致,这点与AverageIf函数允许条件范围与求平均值范围不一致不同。

二、AverageIfs函数的使用方法及实例

(一)只有一个条件范围/条件对的实例

1、假如要求价格大于等于 90 的服装销量的平均值。选中 E2 单元格,把公式 =AVERAGEIFS(D2:D10,C2:C10,">40") 复制到 E2,按回车,返回求平均值结果 624.25;操作过程步骤,如图1所示:

Excel AverageIfs函数的使用方法,包括六大实例图1

2、说明:公式 =AVERAGEIFS(D2:D10,C2:C10,">40") 中求平均值范围为 D2:D10,条件范围为 C2:C10,条件为 ">40"。

(二)逻辑值 True 或 False 都被忽视的实例

1、选中 B1 单元格,把公式 =AVERAGEIFS(A1:A4,B1:B4,">=30") 复制到 B1,按回车,返回 14;双击 B1 单元格,把公式改为 =AVERAGEIFS(B1:B4,A1:A4,">=0"),按回车,返回 46;操作过程步骤,如图2所示:

Excel AverageIfs函数的使用方法,包括六大实例图2

2、说明:

A、A1 至 A4 中分别有一个逻辑值 True 和 False,当 A1:A4 为求平均值范围时,返回结果为 14,而条件是 B1:B4 中的数值必须大于等于 30,符合条件的有 A2、A3 和 A4,如果 True 被转为 1,结果应该为 (14 + 1) / 2 = 7.5,由此可知逻辑值 True 和 False 都没有计入求平均值。

B、当把 B1:B4 改为求平均值范围时,返回结果为 31.5,而条件是 A1:A4 中的数值必须大于等于 0,假设 True 被转为 1、False 被转为 0,则 B1 至 B4 都符合条件,结果应该为 (17 + 53 + 46 + 81) / 4 = 49.25,而 (17 + 46) / 2 = 31.5,说明逻辑值 True 和 False 也都没有被计入求平均值。

(三)选定单元格为空值、文本值或无法转换为数字的其它内容的实例

1、把公式 =AVERAGEIFS(B1:B5,A1:A5,">=0") 复制到 B1 单元格,按回车,返回 #DIV/0! 错误,选中 B2,输入 5,选中 B3,仍然返回 #DIV/0! 错误,输入 6,选中 B4,返回 6;操作过程步骤,如图3所示:

Excel AverageIfs函数的使用方法,包括六大实例图3

2、说明:

A、公式中 B1:B5 为求平均值范围,条件为 A1 至 A5 中的数值大于等于 0,假设 A2 中的空值被转为 0,则 B1 至 B5 都满足条件,假设 B1 至 B5 中的所有空值也都被转为 0,应该有这样的算式 0 / 5 = 0,返回结果应该 0,但返回 #DIV/0! 错误,说明空单元格只是被视为 0,并没被转为数值型 0,从这个公式 =AVERAGEIFS(B1:B5,A1:A5,">="&A2) 可以得到进一步认证,如图4所示:

Excel AverageIfs函数的使用方法,包括六大实例图4

B、选中公式 =AVERAGEIFS(B1:B5,A1:A5,">="&A2) 所在的单元格 C2,按住 Alt,按一次 M,按一次 V,打开“公式求值”窗口,单击“求值”,A2 被转换为 0,如图5所示:

Excel AverageIfs函数的使用方法,包括六大实例图5

C、再点一次求值,0 不见了,如图6所示:

Excel AverageIfs函数的使用方法,包括六大实例

D、当在 B2 中输入 5 时,仍然返回 #DIV/0! 错误,也说明空单元格(A2)只被视为 0 而没有被转为数值 0。当在 B3 中输入 6 时,立即返回 6,B3 对应的单元格为 A3,A3 满足大于等于 0 的条件,因此有 6 / 1 = 6。

(四)两个条件范围/条件对的实例(条件中同时使用通配符问号 ? 和星号 * )

1、假如求服装表中“编号”以 WS 开头、“产品名称”由四个字组成的服装销量的平均值。把公式 =AVERAGEIFS(F2:F12,A2:A12,"WS*",B2:B12,"????") 复制到 G2 单元格,按回车,返回结果 667.5,操作过程步骤,如图7所示:

Excel AverageIfs函数的使用方法,包括六大实例图7

2、公式说明:公式 =AVERAGEIFS(F2:F12,A2:A12,"WS*",B2:B12,"????") 第一个条件范围/条件对为 A2:A12,"WS*",表示在 A2 至 A12 中,选出所有以 WS 开头的服装;第二个条件范围/条件对为 B2:B12,"????",表示选出 B2 至 B12 中所有由四个字组成的服装。

(五)同一区域既为求平均值范围又为条件范围的实例

1、假如要求分类为“衬衫”、销售地区为“广州”且销量大于等于 500 的服装销量的平均值。把公式 =AVERAGEIFS(F2:F12,C2:C12,"衬衫",D2:D12,"广州",F2:F12,">=500") 复制到 G2 单元格,按回车,返回 563;操作过程步骤,如图8所示:

Excel AverageIfs函数的使用方法,包括六大实例8

2、公式中 F2:F12 既是求平均值范围又是第三个条件的条件范围,说明同一区域既可为求平均值范围又可为条件范围。

(六)同列多个条件求平均值

1、假如要求销售地区为“广州”、价格大于等于 80 同时小于等于 90 的服装销量的平均值。把公式 =AVERAGEIFS(F2:F12,D2:D12,"广州",E2:E12,">=80",E2:E12,"<=90") 复制到 G2 单元格,按回车,返回 625,操作过程步骤,如图9所示:

Excel AverageIfs函数的使用方法,包括六大实例图10

2、公式中 E2:E12 有两个条件,即 ">=80" 和 "<=90",这种情况 AverageIfs函数能返回正确的结果;但同列是两个纯文字的条件,则会返回 #DIV/0! 错误;例如求销售地区为“广州和深圳”的服装销量的平均值,把公式 =AVERAGEIFS(F2:F12,D2:D12,"广州",D2:D12,"深圳") 复制到 G2 单元格,按回车,返回 #DIV/0! 错误,演示如图10所示:

Excel AverageIfs函数的使用方法,包括六大实例图11