Примеры применения Excel функции СРЗНАЧЕСЛИ

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

Описание и синтаксис

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

Формула принимает следующие аргументы:

• Диапазон — выделенная часть ячеек, внутри которых будет выполнен поиск значений для определения среднего арифметического.
• Условие — параметр, который определяет, какие значения учитываются при определении среднего арифметического.
• Диапазон усреднения — фактическое число ячеек, из значений которых определяют среднее арифметическое. Не является обязательным аргументом. Если не указывать при написании, автоматически применяется ранее указанный диапазон.

Отметим, что функция СРАЗНАЧЕСЛИ игнорирует ячейки, в которых записаны булевые значения «ЛОЖЬ» либо «ИСТИНА». В качестве условия в формуле также может применяться значение из определенной ячейки. Если она пустая, то функция автоматически подменяет ее числом 0.

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

Таблица пример

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

Подсчет среднего размера прибыли

В таблице-примере содержится столбец, в котором указана суммарная прибыль от продаж каждого товара. По условиям задачи, необходимо определить среднее значение для товаров, прибыль от которых превышает 40 тыс. рублей.

Естественно, можно отыскать ячейки, содержащие значение больше 40 000 вручную и выделив их, определить среднее арифметическое. Однако если таблица слишком объемная, содержит большое количество строк, это будет очень проблематично. Поэтому функция СРАЗНАЧЕСЛИ здесь более чем уместна.

1. Выделяем ячейку для записи результата вычислений. Далее вводим формулу СРЗНАЧЕСЛИ через знак равенства. Альтернативный вариант — Мастер функций. Его можно вызвать через вкладку «Формула» или нажатием комбинации SHIFT+F3.
   

   Вводим функцию СРЗНАЧЕСЛИ
2. Далее нужно указать аргументы. Сначала задаем диапазон ячеек, в которых будут находиться условия, соответствующие заданному параметру. В нашем примере это ячейки из столбца D. Далее указывается само условие — >40000. Для решения поставленной задачи указывать диапазон усреднения не требуется.
   

   Указываем аргументы
3. Указав аргументы, нажимаем OK. В результате этого в ячейке мы получим среднее арифметическое из значений суммарной прибыли выше 40 000 рублей.
   

   Результат выполнения

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

Подсчет прибыли с учетом количества проданных единиц

В данном примере немного усложним задачу. Необходимо определить среднюю прибыль за товары, однако в качестве условия будет использоваться количество их продаж. В данном случае будет задействован аргумент «Диапазон усреднения», который в предыдущем примере не указывался по ненадобности.

В данном случае функция СРАЗНАЧЕСЛИ применяется следующим образом:

1. Вводим формулу СРЗНАЧЕСЛИ. Указываем диапазон, в котором содержатся ячейки, соответствующие условию. В нашем примере учитывается число проданных единиц, то есть это клетки из столбца B. В качестве аргумента указываем «>=10». Это значит, что будут искаться значения больше или равные десяти.
   

   Прописываем аргументы
2. Теперь нужно задать необязательный аргумент — диапазон усреднения. Поиск среднего арифметического будет осуществляться в столбце «Суммарная прибыль». Поэтому указываем ссылки на данный диапазон. После этого уже жмем OK.
   

   Указываем диапазон усреднения

В результате выполнения данная формула вернет среднюю прибыль за товары, проданные 10 или более раз. В данном примере хорошо видно, что необязательный аргумент может использоваться для поиска среднего арифметического в ячейках, не соответствующих изначальному обязательному диапазону поиска.

Средняя прибыль товаров определенной категории

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

Для данного примера немного дополним таблицу, добавив к ней новые позиции, а также категории. Задача состоит в том, чтобы определить среднюю прибыль за товары, соответствующие определенной категории. Решить эту задачу можно следующим образом:

1. Выделяем ячейку для записи результата. Вручную или через Мастер функций добавляем формулу СРЗНАЧЕСЛИ. Указываем в качестве первого аргумента диапазон, в котором будет выполняться поиск по условию. В нашем случае это столбец «Категория».
   

   Вызываем функцию и вводим диапазон
2. Далее прописываем само условие для поиска. Для примера это будет категория «Для единоборств». Можно прописать ее вручную или указать в качестве ссылки на ячейку, где она записана.
   

   Указываем условие
3. Завершающий этап — указываем диапазон усреднения. Мы ищем среднюю прибыль, поэтому указываем столбец, где прописан суммарный доход. В нашем случае это колонка Е. Далее нажимаем OK.
   

   Задаем диапазон усреднения

В результате выполнения формула найдет среднюю прибыль для наименований определенной категории. Аналогичным образом можно применить функцию и для другой группы товаров. Очень удобно, если нужно найти среднее для нескольких или всех категорий, так как их можно выделить в отдельную таблицу и применить маркер автозаполнения.

Результат выполнения

Как видно, при соблюдении правил оформления, использовать представленную функцию не только просто, но и очень удобно. Она поможет предотвратить необходимость выполнения промежуточных вычислений, так как позволяет найти ячейки по заданным условиям, благодаря чему в документе будет меньше формул.

Заключение

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

Видео по теме