Функция СРЗНАЧЕСЛИМН среднее значение в Excel по условию

Функция СРЗНАЧЕСЛИМН в Excel используется для расчета среднего арифметического числовых значений из указанного диапазона ячеек с учетом одного или нескольких условий, которые можно задать в качестве дополнительных аргументов функции.

Данная функция упрощает процедуру выборки данных из таблицы для последующего расчета среднего арифметического с учетом определенных критериев.

Как сделать выборку средних значений по условию в Excel

Примеры использования функции СРЗНАЧЕСЛИМН в Excel.

Пример 1. Студенты, изучавшие некоторый предмет, на протяжении семестра зарабатывали баллы (до 100 баллов). При этом те, кто набрал 49 и менее баллов считается не сдавшим предмет. Определить средний балл для студентов, которые набрали 50 и более баллов (сдали).

Вид таблицы данных:

Пример 1.

Для определения искомого значения используем формулу:

=СРЗНАЧЕСЛИМН(B3:B17;C3:C17;"сдал")

Описание аргументов:

  • B3:B17 – диапазон усреднения (оценки всех студентов);
  • C3:C17 – диапазон, на основе данных которого будет проверяться условие;
  • «сдал» - условие (проверка каждой ячейки на соответствие хранящихся в ней данных заданной текстовой строке).

В результате получим:

СРЗНАЧЕСЛИМН.

В результате была сделана выборка средних значений только для студентов, сдавших семестр.



Расчет среднего чека для выбранного товара по условию в Excel

Пример 2. Перед продавцами магазина стоят следующие задачи по продажам:

  • количество товаров в чеке – не менее 3 единиц;
  • средняя цена в чеке – 40 у. е.;
  • обязательная продажа продукции фирмы Adidas.

Определить, соответствуют ли данные по продажам за день указанным условиям для каждого продавца.

Вид таблицы данных:

Пример 2.

Для расчетов используем следующую формулу:

Описание аргументов:

  • B3:B13 – диапазон ячеек с ценами в чеке;
  • A3:A12 – с номерами продавцов;
  • C15 – ячейка с номером продавца как критерий выбора;
  • C3:C12 – с количеством единиц товаров в чеке;
  • ">="&C17 – критерий выбора (не менее числа, указанного в ячейка C17, в данном примере – не менее 3 единиц);
  • D3:D12 – с названиями брендов;
  • "*"&C18&"*" – критерий выбора по названию бренда (так как наименования перечислены через запятую, для нахождения подстроки в строке используем символы «*» для указания любого числа любых символов в начале и в конце исследуемой строки).

Результат вычислений для первого продавца:

Результат 1.

Для второго продавца:

Результат №2.

Как видно, оба продавца справились с поставленной задачей.

Правила работы с функцией СРЗНАЧЕСЛИМН в Excel

Функция СРЗНАЧЕСЛИМН предназначена для расчета среднего значения с учетом нескольких условий. Она имеет следующую синтаксическую запись:

=СРЗНАЧЕСЛИМН(диапазон_усреднения;диапазон_условий1;условие1;[диапазон_условий2;условие2];…)

Описание аргументов:

  • диапазон_усреднения – обязательный для заполнения, принимает ссылку на одну ячейку или диапазон, которые содержат числа или данные другого типа, которые могут быть преобразованы в числовые. Среднее арифметическое будет рассчитано на основе данных из данного диапазона с учетом соответствующих критериев.
  • диапазон_условий1 – обязательный для заполнения, принимает ссылку на одну ячейку или диапазон, которые содержат данные, на основе которых будет выполнен отбор числовых данных (первый аргумент) для расчета среднего арифметического. Последующие аргументы (диапазон_условий2…) являются необязательными для заполнения и имеют тот же смысл (для более тонкой настройки отбора данных).
  • условие1 – обязательный для заполнения, принимает числовые, текстовые, ссылочные данные, имена и логические выражения, используемые в качестве критерия для отбора данных. Последующие аргументы (условие2…) необязательны для заполнения, имеют тот же смысл. Примеры:
  1. 2 – выбрать значения из диапазона, которые равны числу 2.
  2. «2» - то же самое (текст будет преобразован в число).
  3. «>5» - рассчитать среднее арифметическое для чисел из диапазона, значения которых больше 5.
  4. «аккумулятор» - рассчитать среднее арифметическое для числовых данных, сопоставимых с категорией аккумулятор.
  5. A2 – использовать для проверки данные, хранящиеся в ячейке A2 (может хранить числа или данные, преобразуемые в число, а также выражения).

Примечания:

  1. Пустые ячейки, переданные в качестве аргументов условиеX преобразуются в значения 0.
  2. Ошибка #ДЕЛ/0! возникает в случае, если диапазон_усреднения содержит нечисловые данные, которые не могут быть преобразованы в числа.
  3. Логические данные (ЛОЖЬ, ИСТИНА) автоматические преобразуются в соответствующие числовые значение 0 и 1.
  4. Размеры и формы диапазонов условий и усреднения должны совпадать.
  5. Результат работы рассматриваемой функции будет являться кодом ошибки #ДЕЛ/0!, если в результате проверки условий не были найдены данные, которые им соответствуют.
  6. В логическом выражении, используемом в качестве аргумента для указания критериев поиска данных (условиеX) можно использовать подстановочные знаки («*» - любое количество любых символов, «?» - любой одиночный символ).

en ru