Функция СРЗНАЧЕСЛИ
Возвращает среднее арифметическое всех ячеек в диапазоне, которые удовлетворяют заданному условию. Функция СРЗНАЧЕСЛИ суммирует все результаты, которые проходят логическую проверку и делит сумму на количество выбранных величин.
AVERAGEIF(Range; Criterion [; Average_Range ])
Диапазон – обязательный аргумент. Массив, имя именованного диапазона или заголовок столбца или строка, содержащие числа для усреднения, либо числа или текст для условия.
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".
Диапазон усреднения — необязательный аргумент. Диапазон значения для расчёта среднего значения.
Если Диапазон усреднения не указан, Диапазон используется как для вычисления среднего значения и поиска в соответствии с условием. Если Диапазон усреднения указан, Диапазон используется только для проверки условия, тогда как Диапазон усреднения используется для расчёта среднего значения.
Если ячейка в диапазоне значений для вычисления среднего значения пуста или содержит текст, то функция СРЗНАЧЕСЛИ игнорирует эту ячейку.
Если же весь диапазон пуст, содержит только текст или все значения диапазона не соответствуют условию (или любых комбинаций этих условий), функция возвращает ошибку #ДЕЛ/0!.
Рассмотрите следующую таблицу
|
A |
B |
C |
|
|---|---|---|---|
|
1 |
Имя товара |
Sales |
Revenue |
|
2 |
карандаш |
20 |
65 |
|
3 |
ручка |
35 |
85 |
|
4 |
тетрадь |
20 |
190 |
|
5 |
книга |
17 |
180 |
|
6 |
пенал |
нет |
нет |
Во всех примерах внизу диапазоны вычисления содержат строку №6, которая игнорируется, поскольку содержит текст.
Простое использование
=СРЗНАЧЕСЛИ(B2:B6; "<35")
Вычисляет среднее значение для значений диапазона B2:B6, которые меньше 35. Возвращает 19, потому что вторая строка не участвует в вычислении.
=СРЗНАЧЕСЛИ(B2:B6; "<"&МАКС(B2:B6))
Вычисляет среднее значение для значений того же диапазона, которые меньше максимального значения этого диапазона. Возвращает 19, потому что наибольшее значение (вторая строка) не участвует в вычислении.
=СРЗНАЧЕСЛИ(B2:B6; ">"&НАИМЕНЬШИЙ(B2:B6; 1))
Вычисляет среднее значение для значений того же диапазона, которые больше первого наименьшего значения этого диапазона. Возвращает 25, потому что первое наименьшее значение (четвертая строка) не участвует в вычислении.
Использование Диапазона усреднения
=СРЗНАЧЕСЛИ(B2:B6; "<35"; C2:C6)
Функция определяет значения ниже 35 в диапазоне B2:B6, и рассчитывает среднее значение соответствующих значений из диапазона C2:C6. Возвращает 145, потому что вторая строка не участвует в вычислении.
=СРЗНАЧЕСЛИ(B2:B6; ">"&МИН(B2:B6); C2:C6)
Функция определяет значения из диапазона B2:B6 больше наименьшего значения в диапазоне B2:B6, и рассчитывает среднее значение из соответствующих значений диапазона C2:C6. Возвращает 113,3, потому что четвёртая строка (в которой находится наименьшее значение диапазона B2:B6) не участвует в вычислении.
=СРЗНАЧЕСЛИ(B2:B6; "<"&НАИБОЛЬШИЙ(B2:B6; 2); C2:C6)
Функция определяет значения из диапазона B2:B6, которые меньше второго наибольшего значения в диапазоне B2:B6, и рассчитывает среднее значение из соответствующих значений диапазона C2:C6. Возвращает 180, потому что в вычислении участвует только четвёртая строка.
Использование регулярных выражений
=СРЗНАЧЕСЛИ(A1:A50)
Функция определяет значения из диапазона A2:A6, которые содержат только слово «pen», и рассчитывает среднее значение из соответствующих значений диапазона B2:B6. Возвращает 35, потому что только вторая строка участвует в вычислении. Поиск осуществляется по диапазону A2:A6, но значения возвращаются из диапазона B2:B6.
=СРЗНАЧЕСЛИ(A1:A50)
Функция определяет значения из диапазона A2:A6, которые начинаются со строки «pen» и завершаются на любое количество других символов, и рассчитывает среднее значение из соответствующих значений диапазона B2:B6. Возвращает 27,5, потому что на момент проверки слово «pencil» также соответствовало условиям, и обе строки — первая и вторая, участвуют в вычислениях.
=СРЗНАЧЕСЛИ(A1:A50)
Функция определяет значения из диапазона A2:A6, которые содержат слово «book», а начинаются и завершаются любым количеством других символов, и рассчитывает среднее значение из соответствующих значений диапазона B2:B6. Возвращает 18,5, потому что только третья и четвёртая строка участвует в вычислениях.
Ссылка на ячейку с условием
Для более удобного изменения условия функции можно указать его в отдельной ячейке и использовать ссылку на него в качестве условия функции СРЗНАЧЕСЛИ.
=СРЗНАЧЕСЛИ(A2:A6; ".*"&E2&".*"; B2:B6)
Функция определяет значения из диапазона A2:A6, которые содержат комбинацию символов, указанную в ячейке E2, а начинаются и заканчиваются на любое количество других символов, и рассчитывает среднее значение из соответствующих значений диапазона B2:B6. Если E2 = книга, функция возвращает 18,5.
=СРЗНАЧЕСЛИ(B2:B6; "<"&E2; C2:C6)
Функция определяет значения из диапазона B2:B6, которые меньше значения, указанного в ячейке E2, и рассчитывает среднее значение из соответствующих значений диапазона C2:C6. Если E2 = 35, функция возвращает 145.
Эта функция является частью формата Open Document для офисных приложений (OpenDocument) v1.2. (ISO/IEC 26300:2-2015)