Функция СРЗНАЧЕСЛИ
Возвращает среднее арифметическое всех ячеек в диапазоне, которые удовлетворяют заданному условию. Функция СРЗНАЧЕСЛИ суммирует все результаты, которые проходят логическую проверку и делит сумму на количество выбранных величин.
=СРЗНАЧЕСЛИ(Диапазон; Условие [; Диапазон усреднения ])
Диапазон – обязательный аргумент. Массив, имя именованного диапазона или заголовок столбца или строка, содержащие числа для усреднения, либо числа или текст для условия.
Поиск поддерживает подстановочные знаки и регулярные выражения. При включении регулярных выражений можно ввести, например, «all.*» для поиска первого вхождения «all», за которым следуют любые символы. Если необходимо выполнить поиск текста, который сам является регулярным выражением, то перед каждым метасимволом или оператором регулярного выражения нужно вставлять символ «\», либо заключать текст в \Q...\E. Включить или выключить автоматическое применение подстановочных знаков или регулярных выражений можно в .
При использовании функций, один или несколько аргументов которых являются строками поиска с регулярными выражениями, сначала выполняется попытка преобразовать строку поиска в число. Например, «.0» будет преобразовано в число 0.0 и так далее. В случае удачного поиска, сопоставление будет производиться не по регулярному выражению, а по числовому совпадению. Однако при переключении на локаль, в которой точка не является десятичным разделителем, преобразование в регулярное выражение начинает работать. Для принудительного использования регулярного выражения вместо числового, используйте выражения, которые нельзя ошибочно принять за число, например «.[0]», «.0» или «(?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)
