AVERAGEIF 函数
返回满足给定条件的区域中所有单元格的算术平均值。AVERAGEIF 函数求与逻辑测试匹配的所有结果之和, 然后除以选中值的数量.
This function is available since LibreOffice 4.0.
AVERAGEIF(范围; 条件 [; 平均范围 ])
范围 – 必填参数。数组、命名区域的名称或列/行的标签,包含用于求平均值但数字或构成条件的数字或文本。
Criterion: A criterion is a single cell Reference, Number or Text. It is used in comparisons with cell contents.
A reference to an empty cell is interpreted as the numeric value 0.
A matching expression can be:
-
A number or logical value. A matching cell content equals the number or logical value.
-
A value beginning with a comparator (<, <=, =, >, >=, <>).
For =, if the value is empty it matches empty cells.
For <>, if the value is empty it matches non-empty cells.
For <>, if the value is not empty it matches any cell content except the value, including empty cells.
Note: "=0" does not match empty cells.
For = and <>, if the value is not empty and can not be interpreted as a number type or one of its subtypes and the property Search criteria = and <> must apply to whole cells is checked, comparison is against the entire cell contents, if unchecked, comparison is against any subpart of the field that matches the criteria. For = and <>, if the value is not empty and can not be interpreted as a Number type or one of its subtypes applies.
-
Other Text value. If the property Search criteria = and <> must apply to whole cells is true, the comparison is against the entire cell contents, if false, comparison is against any subpart of the field that matches the criteria. The expression can contain text, numbers, regular expressions or wildcards (if enabled in calculation options).
The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every regular expression metacharacter or operator with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in .
When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".
平均范围 – 可选。用于计算平均值的值范围。
If the Average_Range is not specified, Range is used for both, the calculation of the mean and the search according to the condition. If Average_Range is specified, the Range is used only for the condition test, while Average_Range is used for the average calculation.
如果用于计算平均值的值范围内的单元格为空或包含文本, 则函数 AVERAGEIF 将忽略该单元格。
如果整个范围为空、仅包含文本或范围内的所有值均不满足条件 (或这些条件的任何组合), 则该函数将返回 #DIV/0! 错误。
考虑下表
|
A
|
B
|
C
|
1
|
产品名称
|
Sales
|
Revenue
|
2
|
铅笔
|
20
|
65
|
3
|
钢笔
|
35
|
85
|
4
|
笔记本
|
20
|
190
|
5
|
图书
|
17
|
180
|
6
|
pencil-case
|
not
|
not
|
在下面的所有示例中, 计算范围都包含行 #6, 该行将被忽略, 因为它包含文本。
简单用法
「=AVERAGEIF(B2:B6;"<35")」
计算 B2:B6 范围小于 35 的数值的平均值。返回 19, 因为第二行不参与计算。
「=AVERAGEIF(B2:B6;"<"&MAX(B2:B6))」
计算范围内小于此范围最大值的所有值的平均值。返回 19, 因为最大的值 (第二行) 不参与计算。
「=AVERAGE(A1:A50)」
计算范围内大于此范围第一个最小值的所有值的平均值。返回 25, 因为第一个最小值 (第四行) 不参与计算。
使用「平均范围」
「=AVERAGEIF(B2:B6;"<35";C2:C6)」
该函数在 B2:B6 范围内搜索哪些值小于 35, 并计算 C2:C6 范围内相应值的平均值。返回 145, 因为第二行不参与计算。
「=AVERAGE(A1:A50)」
该函数在 B2:B6 范围内搜索哪些值大于 B2:B6 范围中的最小值, 并计算 C2:C6 范围内的相应值的平均值。返回 113.3, 因为第四行 (B2:B6 范围内的最小值位于该行) 不参与计算。
「=AVERAGEIF(B2:B6;"<"&LARGE(B2:B6;2);C2:C6)」
该函数在 B2:B6 范围内搜索哪些值小于 B2:B6 范围中第二大的值, 并计算 C2:C6 范围内相应值的平均值。返回 180, 因为只有第四行参与计算。
使用正则表达式
「=AVERAGE(A1:A50)」
该函数搜索 A2:A6 范围内的仅包含单词「pen」的单元格, 并计算 B2:B6 范围内相应值的平均值。返回 35, 因为只有第二行参与计算。搜索在 A2:A6 范围内执行, 但值从 B2:B6 范围返回。
「=AVERAGE(A1:A50)」
该函数搜索 A2:A6 范围中以「pen」开头, 以任意数量其他字符结尾的单元格, 并计算 B2:B6 范围内相应值的平均值。返回 27.5, 因为现在「pencil」也满足条件, 第一行和第二行都参与计算。
「=AVERAGE(A1:A50)」
该函数搜索 A2:A6 范围中包含「book」、以任意数量其他字符开始和结束的单元格, 并计算 B2:B6 范围内相应值的平均值。返回 18.5, 因为只有第三行和第四行参与计算。
引用单元格作为条件
如果您需要轻松地更改一个条件, 可以考虑在单独的单元格中指定条件, 并在 AVERAGEIF 函数的条件中使用对该单元格的引用。
「=AVERAGE(A1:A50)」
该函数搜索 A2:A6 范围中包含 E2 中指定的字符、以任意数量其他字符开始和结束的单元格, 并计算 B2:B6 范围内相应值的平均值。如果 E2 = book, 则该函数返回 18.5。
「=AVERAGEIF(B2:B6;"<"&E2;C2:C6)」
该函数搜索 B2:B6 中小于 E2 指定值的单元格, 并计算 C2:C6 范围中相应值的平均值。如果 E2 = 35, 则该函数返回 145。
此功能是「用于 Office 应用程序的开放文档格式 (OpenDocument)」标准 1.2 版本的一部分。(ISO/IEC 26300:2-2015)