Excel Average函数的使用方法,包含与If、Int、Left、Find、Year、Month组合求隔行、指定条件、年月和季度

亮术网 2019-09-10 本网原创

在 Excel 中,Average函数用于求数值的平均值,它至少要有一个数值最多只能有 255 个数值;如果求文本型或逻辑值的平均值,需要用 AverageA函数。

Average函数可以与一些函数组合求平均值,如 Average + If 组合实现隔行(或指定条件)求平均值,Average + If + Int + Left + Find 组合实现求季度平均值,Average + Year 组合实现求年平均值,Average + Month 组合实现求月平均值。

 

一、Excel Average函数的语法

1、表达式:AVERAGE(Number1, [Number2], ...)

中文表达式:AVERAGE(数字1, [数字2], ...)

 

2、说明:

A、Number 可以是数字、数组、包含数字的名称和单元格区域,至少要有一个 Number、最多只能有 255 个 Number。

B、如果 Number 为能转为数值的文本(如 "1"),Average函数会自动把它转换为数值;如果 Number 为不能转为数值的文本(如 "一"或 "A"),Average函数将返回值错误 #VALUE!。如果引用单元格中有文本(单元格格式为文本);它们将被忽略。如果数组有文本,能转为数值将计入求平均值,不能转为数值的将被忽略。如果 Number 中有错误值,则返回错误。

C、如果引用的单元格中有 0 或空值的单元格,0 将被计入求平均值,而空值不被计入求平均值。

D、如果引用单元格中有逻辑值 True 或 False,它们将被忽略;如果  Number 为数组且包含逻辑值,则 True 被转为 1,False 被转为 0。

E、如果要计算引用单元格中的逻辑值和单元格格式为文本的数值,需要使用 AverageA 函数。

 

 

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

(一)引用单元格区域有数字为文本的实例

1、双击 B1 单元格,输入公式 =AVERAGE(A1:A3),按回车,返回求平均值结果 7;操作过程步骤,如图1所示:

Excel Average函数的使用方法及实例

图1

2、A1:A3 中共有三个数值,求平均值结果为 7,说明 A1 中的 10 未被计入求平均值,A1 左上角有一个绿色小三角形,说明 A1 的单元格格式为文本,而Average函数不把格式为文本的数字计入求平均值。

 

(二)数组中有文本的实例

1、双击 A1 单元格,把公式 =AVERAGE(4,"6",8) 复制到 A1,按回车,返回求平均值结果 6;双击 A1,把 6 改为“一”,按回车,返回值错误 #VALUE!;操作过程步骤,如图2所示:

Excel Average函数的使用方法,包含与If、Int、Left、Find、Year、Month组合求隔行、指定条件、年月和季度平均值

图2

2、公式 =AVERAGE(4,"6",8) 返回 6,说明文本 "6" 计入了求平均值;而把 "6" 改为 "一" 后,返回错误,说明参数中有不能转为数值的文本,Average函数返回错误。

 

(三)引用单元格有 0 和错误值的实例

1、双击 B1 单元格,把公式 =AVERAGE(A1:A4) 复制到 B1,按回车,返回值错误 #VALUE!;选中 A1,把内容删除,B1 的值变为除数为0 错误 #DIV/0!;再把 A2 中的内容删除,B1 中的值变为 0;操作过程步骤,如图3所示:

Excel Average函数引用单元格有 0 和错误值的实例

图3

2、从上面的操作可知,当引用单元格中有错误值时,Average函数都返回错误;引用单元格中的空单元格被忽略,而 0 被计入求平均值。

 

(四)引用单元格或数组中有逻辑值 True 或 False 的实例

1、双击 A3 单元格,输入公式 =AVERAGE(A1:A2),按回车,返回除数为 0 错误 #DIV/0!;双击 A4,把公式 =AVERAGE(TRUE,FALSE) 复制到 A4,按回车,返回求平均值结果 0.5;操作过程步骤,如图4所示:

Excel Average函数引用单元或数组中有逻辑值 True 或 False 的实例

图4

2、公式 =AVERAGE(A1:A2) 与 =AVERAGE(TRUE,FALSE) 都是对 True 和 False 求平均值,但前者返回错误后者能返回正确的值,说明逻辑值在单元格时,Average函数把它们忽略,而作为Average函数的参数时会被计入求平均值。

 

 

三、Excel Average函数的扩展使用实例

(一)Average + If函数组合实现隔行求平均值(或按指定条件求平均值)

1、假如要分别求“女装和男装”销量的平均值。双击 B12 单元格,把公式 =AVERAGE(IF(C$2:C$9=A12,D$2:D$9,"")) 复制到 B12,按 Ctrl + Shift + 回车,返回求平均值结果 705;把鼠标移到 B12 右下角的单元格填充柄上,鼠标变为加号后,按住左键,往下拖,则求出所有男装的平均销量;操作过程步骤,如图5所示:

Excel Average + If函数组合实现隔行求平均值(或按指定条件求平均值)

图5

 

2、公式 =AVERAGE(IF(C$2:C$9=A12,D$2:D$9,"")) 说明:

A、C$2 是对列相对引用、对行绝对引用,往下拖时,C2 不会变为 C3、C4 等;C$9、D$2、D$9 与 C$2 是一个意思;而 A12 对列和行都是相对引用,A12 会变 A13;凡是在列或行前加 $ 都表示绝对引用。

B、C$2:C$9 以数组形式返回 C2 至 C9 中的所有值,则 If 的条件 C$2:C$9=A12 变为 {"女装";"男装";"女装";"男装";"女装";"男装";"女装";"男装"}="女装",接着,从数组中取出每一个元素与“女装”比较,如果相等返回 True,否则返回 False;例如:第一次取出 "女装",它与 "女装" 相等,因此返回 True;第二次取出 "男装",它不等于 "女装",因此返回 False;其它的以此类推,最后返回 {TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}。

C、则 IF(C$2:C$9=A12,D$2:D$9,) 变为 IF({TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE},D$2:D$9,),接着从 If 的条件数组中取出每一个元素,如果为 TRUE,返回 D$2:D$9 对应的元素,否则返回 "";例如:第一次取出 TRUE,返回 D2;第二次取出 FALSE,返回 "";最后返回 {892;"";638;"";528;"";762;""}。

D、则公式变为 =AVERAGE({892;"";638;"";528;"";762;""}),最后对数组中的元素求平均值,最后返回 705。

提示:除可用 Average + If函数实现外,也可以用AverageIf函数实现,即 =AVERAGEIF(C2:C9,A12,D2:D9)。

 

(二)Average + If + Int + Left + Find函数组合实现求季度平均值(每个季度不一定有三个月)

1、假如要求每个季度的平均销量。双击 E2 单元格,把公式 =AVERAGE(IF((INT(LEFT(A$2:A$10,FIND("月",A$2:A$10)-1))>(3*D2-3))*(INT(LEFT(A$2:A$10,FIND("月",A$2:A$10)-1))<=v),B$2:B$10,"")) 复制到 E2,按 Ctrl + Shift + 回车,返回求平均值结果 553.67;同样用往下拖的方法求出其它季度平均销量,操作过程步骤,如图6所示:

Excel Average + If + Int + Left + Find函数组合实现求季度平均值(每个季度不一定有三个月)

图6

 

2、公式 =AVERAGE(IF((INT(LEFT(A$2:A$10,FIND("月",A$2:A$10)-1))>(3*D2-3))*(INT(LEFT(A$2:A$10,FIND("月",A$2:A$10)-1))<=3*D2),B$2:B$10,"")) 说明:

A、A$2:A$10 以数组形式返回 A2:A10 中的月份,即 {"1月";"2月";"3月";"4月";"5月";"6月";"7月";"8月";"10月"}。

B、FIND("月",A$2:A$10) 用于依次在 A2:A10 中查找“月”的位置,目的是截取出“月份”中的数字;例如:执行时,第一次从 A2:A10取出 A2,返回“月”的位置为 2;第二次取出 A3,返回“月”的位置也为 2,其它的以此类推,最后返回 {2;2;2;2;2;2;2;2;3};则 FIND("月",A$2:A$10)-1 变为 {2;2;2;2;2;2;2;2;3}-1,接着用数组中每个元素减 1,则数组变为 {1;1;1;1;1;1;1;1;2}。

C、则 LEFT(A$2:A$10,FIND("月",A$2:A$10)-1) 变为 LEFT({"1月";"2月";"3月";"4月";"5月";"6月";"7月";"8月";"10月"},{1;1;1;1;1;1;1;1;2}),接着,从第一个数组中取出“1月”,再从第二个数组中取出与“1月”对应的位置 1,然后把“1月”从左边截 1 位,因此返回 1,其它的以此类推,最后返回 {"1";"2";"3";"4";"5";"6";"7";"8";"10"}。

D、则 INT(LEFT(A$2:A$10,FIND("月",A$2:A$10)-1)) 变为 INT({"1";"2";"3";"4";"5";"6";"7";"8";"10"}),进一步计算把数组中的每个元素取整,最后返回 {1;2;3;4;5;6;7;8;10}。

E、D2 为 1,把它代入 3*D2-3 得 0,再把它代入 3*D2 得 3;公式变为 =AVERAGE(IF({1;2;3;4;5;6;7;8;10}>0)*({1;2;3;4;5;6;7;8;10}<=3),B$2:B$10,"")),接着,从 If 的第一个条件数组中取出每个元素 与 0 比较,如果大于 0,返回 True,否则返回 False,最后返回 {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE};然后再从 If 的第二个条件数组中取出每一个元素与 3 比较,如果小于等于 3,返回 True,否则返回 False,最后返回 {TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}。

F、则公式变为 =AVERAGE(IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}*{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},B$2:B$10,"")),然后把两个数组对应元素相乘,相乘时,True 被转为 1,False 被转为 0。

G、则公式变为 =AVERAGE(IF({1;1;1;0;0;0;0;0;0},B$2:B$10,"")),接着,从 If 的条件数组中取出每一个元素,如果为 True 返回B2:B10 中对应的元素,否则返回 "";例如:第一次取出条件数组第一个元素 1,由于为 True,所以返回 567;第二次取出第二个元素 1,返回 571;其它的以此类推,最后返回 {567;571;523;"";"";"";"";"";""}。

H、则公式变为 =AVERAGE({567;571;523;"";"";"";"";"";""}),最后对数组中的元素求平均值,结果为 553.67。

 

(三)求年和月平均值

(1)Average + If + Year函数组合实现求年平均值

1、假如有一个2018年的销量表,要求年平均销量。双击 E2 单元格,把公式 =AVERAGE(IF(YEAR(A$2:A$362)=D2,B$2:B$362,"")) 复制到E2,按 Ctrl + Shift + 回车,返回求平均值结果 768.64;操作过程步骤,如图7所示:

Excel Average + If + Year函数组合实现求年平均值

图7

 

2、公式 =AVERAGE(IF(YEAR(A$2:A$362)=D2,B$2:B$362,"")) 说明:

A、YEAR(A$2:A$362) 以数组形式返回 A2:A362 中的年份,即 {2018;2018;……;2018}。

B、则 YEAR(A$2:A$362)=D2 变为 {2018;2018;……;2018}=D2,接着从数组中取出每个 2018 与 D2(即 2018)比较,由于它们都相等,所以全返回 True,即 {TRUE;TRUE;……;TRUE}。

C、则公式变 =AVERAGE(IF(TRUE;TRUE;……;TRUE},B$2:B$362,"")),接着,从条件数组中取出每一个元素,如果为真,返回 B2:B362 中对应的值,否则返回 "";由于条件数组中全为真,因此以数组形式返回 B2:B362 中的所有值,即 {975;697;……;841}。

D、则公式变为 =AVERAGE({975;697;……;841}),最后对数组中的值求平均值,结果为 768.64。

提示:如果表格中同时有不同年份的数据,也可以用上面的公式,要求那年的平均值,只需把具体年份写到 D2。

 

(2)Average + If + Month函数组合实现求月平均值

1、假如要求 1 到 12 月的平均销量。双击 D4 单元格,把公式 =AVERAGE(IF(MONTH(A$2:A$362)=D4,B$2:B$362,"")) 复制到 D4,按回车,返回 1 月求平均值结果 788.71;用往下拖的方法求出剩余月份的平均销量;操作过程步骤,如图8所示:

Excel Average + If + Month函数组合实现求月平均值

图8

2、公式 =AVERAGE(IF(MONTH(A$2:A$362)=D4,B$2:B$362,"")) 说明:

公式与求年平均值公式一样,所不同的是 MONTH(A$2:A$362) 用于取 A2 到 A362 中日期的月份;例如:第一次取 A2 中 2018/1/1 的月份 1。