Как подсчитать среднее арифметическое значение в excel

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

Стандартный способ вычисления

Самый простой и известный способ найти среднее арифметическое набора чисел — это воспользоваться специальной кнопкой на ленте Microsoft Excel. Выделяем диапазон чисел, расположенных в столбце или в строке документа. Находясь во вкладке «Главная», жмем на кнопку «Автосумма», которая расположена на ленте в блоке инструментов «Редактирование». Из выпадающее списка выбираем пункт «Среднее».

Стандартный способ вычисления

Стандартный способ вычисления

После этого, с помощью функции «СРЗНАЧ», производится расчет. В ячейку под выделенным столбцом, или справа от выделенной строки, выводится средняя арифметическая данного набора чисел.

Стандартный способ вычисления

Стандартный способ вычисления

Этот способ хорош простотой и удобством. Но, у него имеются и существенные недостатки. С помощью этого способа можно произвести подсчет среднего значения только тех чисел, которые располагаются в ряд в одном столбце, или в одной строке. А вот, с массивом ячеек, или с разрозненными ячейками на листе, с помощью этого способа работать нельзя.

Стандартный способ вычисления

Например, если выделить два столбца, и вышеописанным способом вычислить среднее арифметическое, то ответ будет дан для каждого столбца в отдельности, а не для всего массива ячеек.

Стандартный способ вычисления

Стандартный способ вычисления

Стандартный способ вычисления

Расчет среднего арифметического

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

  • открываете утилиту. В верхней строке набираете ряд нужных цифр;
  • под первой цифрой ставите курсор. В верхней строке программы выбираете вкладку «Редактирование», затем кнопку «Сумма». В выпавшем окне выбираете значение «Среднее»;
Расчет среднего арифметического
  • после того, как кликните в том пункте на котором стоит курсор, появится формула;
  • останется только выделить диапазон и кликнуть по кнопке «Ввод». А в ячейке теперь отобразится результат из взятых данных выше.
Читайте также:  Ошибка 924 в Play Маркет на Android — как исправить

Возможности функции

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

Возможности функции

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

При помощи этой функции можно посчитать среднее число без учета нулевых значений, если правильно задать условие. В нашем случае, чтобы ноль не учитывать, ставите во втором блоке больше 0.

Возможности функции

Таким же образом можно работать и с текстовыми ячейками, при этом условие может быть записано в отдельном месте, а в формуле будет ссылка на него. Если текст пишете сразу, то не забывайте про кавычки.

Важно! При работе с текстом, обязательным является третий аргумент – диапазон усреднения.

Возможности функции

Рассмотрим более сложный пример.

Есть перечень товаров с определенной стоимостью и количеством проданных единиц за установленный промежуток времени. Как посчитать средневзвешенную цену всего проданного товара? Для этого используете 2 функции: СУММПРОИЗВ с двумя массивами цены и количества и СУММ с количеством. Поделив одно на другое, получаете среднее значение цены, то есть математические законы сохраняются.

Возможности функции

Как видите, среднее значение в excel это полезная функция, при помощи которой можно работать с числами и текстом по заданным условиям. Синтаксис формулы прост и будет понятен даже для неопытных пользователей редактором.

Пример использования поиска решений

Теперь перейдем к самой функции.

1) Чтобы включить «Поиск решений», выполните следующие шаги:

  • нажмите «Параметры Excel», а затем выберите категорию «Надстройки»;
  • в поле «Управление» выберите значение «Надстройки Excel» и нажмите кнопку «Перейти»;
  • в поле «Доступные надстройки» установите флажок рядом с пунктом «Поиск решения» и нажмите кнопку ОК.
Читайте также:  7 проверенных причин почему в ноутбуке не работает интернет

2) Теперь упорядочим данные в виде таблицы, отражающей связи между ячейками. Советуем использовать цветовые обозначения: на примере красным выделена целевая функция, бежевым — ограничения, а желтым — изменяемые ячейки.

Не забудьте ввести формулы. Стоимость заказа рассчитывается как «Оплата труда за 1 изделие» умножить на «Число заготовок, передаваемых в работу». Для того, чтобы узнать «Время на выполнение заказа», нужно «Число заготовок, передаваемых в работу» разделить на «Производительность».

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

4) Заполните параметры «Поиска решений» и нажмите «Найти решение».

Совокупная стоимость 1000 изделий рассчитывается как сумма стоимостей количества изделий от каждого работника. Данная ячейка (Е13) — это целевая функция. D9:D12 — изменяемые ячейки. «Поиск решений» определяет их оптимальные значения, чтобы целевая функция достигла минимума при заданных ограничениях.

В нашем примере следующие ограничения:

  • общее количество изделий 1000 штук ($D$13 = $D$3);
  • число заготовок, передаваемых в работу — целое и больше нуля либо равно нулю ($D$9:$D$12 = целое, $D$9:$D$12 > = 0);
  • количество дней меньше либо равно 30 ($F$9:$F$12 < = $D$6, либо как в примере в ячейке F13 задать функцию МАКС(F9:F12) и поставить ограничение $F$13 < = $D$6).

5) В конце проверьте полученные данные на соответствие заданному целевому значению. Если что-то не сходится — нужно пересмотреть исходные данные, введенные формулы и ограничения.

Эта функция поможет оптимизировать работу и быстро найти нужный результат. На онлайн-курсе «Excel для карьеры и поиска работы» от Changellenge >> ToolKit мы делимся только теми функциями, которые действительно пригодятся на практике. Присоединяйтесь и начните работать эффективно!

Читайте также:  Ratatype (Все10) — Бесплатный клавиатурный тренажер онлайн

Узнать о курсе

Hard Skills Туториал

Множественные условия

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

В этом случае придется записать более сложную формулу массива :

= НАИМЕНЬШИЙ(ЕСЛИ(($A$6:$A$16=E6)*($B$6:$B$16=F6);$C$6:$C$16;””);1)

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

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