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

КАК ПОСЧИТАТЬ СРЕДНЕЕ ЗНАЧЕНИЕ В EXCEL С УСЛОВИЯМИ

В программе Microsoft Excel есть несколько способов вычисления среднего значения с учетом условий. Один из наиболее распространенных методов — использование функции СРЗНАЧ(Среднее.Значение). Эта функция позволяет рассчитать среднее значение только для тех ячеек, которые соответствуют заданным условиям.

Для использования функции СРЗНАЧ(Среднее.Значение) с условиями, вы можете воспользоваться функцией СРЗНАЧСИ(Среднее.Значение.Если). Она позволяет указать диапазон ячеек, которые нужно учитывать, и условие, которому должны удовлетворять значения в этих ячейках. Например:

=СРЗНАЧСИ(A1:A10, «>5») — вычислит среднее значение только для тех ячеек в диапазоне A1:A10, которые больше 5.

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

=СРЗНАЧСИ(B1:B10, «>=10″, » — рассчитает среднее значение только для тех ячеек в диапазоне B1:B10, которые находятся в диапазоне от 10 до 20.

Также можно использовать функцию СРЗНАЧПО(Среднее.Значение.Если.По). Она позволяет задавать дополнительные условия по столбцам или строкам. Например:

=СРЗНАЧПО(C1:C10, A1:A10, «>5») — вычислит среднее значение только для тех ячеек в диапазоне C1:C10, которые соответствуют условию, заданному в столбце A1:A10 (больше 5).

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

=СРЗНАЧ.ЕСЛИСПР(D1:D10, «>5», C1:C10, «<>0″) — рассчитает среднее значение только для тех ячеек в диапазоне D1:D10, которые больше 5 и для которых значения в соответствующих ячейках в столбце C1:C10 не равны 0.

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

Выборочные вычисления суммы, среднего и количества в Excel

Excel Среднее значение группы чисел

5 Трюков Excel, о которых ты еще не знаешь!

Поиск минимального и максимального значений по условию

Как посчитать среднее значение в Excel (среднее арифметическое в Экселе) — Функция СРЗНАЧ (AVERAGE)

Функция СУММЕСЛИ и СУММЕСЛИМН в excel. Как пользоваться формулами. Менеджер Маркетплейсов / Урок 16

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

Как в экселе посчитать среднее значение

Функция ВПР в Excel. от А до Я

Как извлечь цифры из текста ➤ Формулы Excel

Михаил Захаров

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

Вам также может понравиться:

Как посчитать среднее значение с условием в Excel

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

Как найти среднее арифметическое число по условию в Excel

Ниже на рисунке представлен список медалистов зимних олимпийских игр за 1972-ой год. Допустим нам необходимо узнать средний показатель результатов призеров из Швейцарии. Название страны для условия записано в отдельной ячейке, благодаря чему можно легко менять его на другое. Формула решения:

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

В данном примере каждое значение в диапазоне … проверяется: будет или не будет оно учтено при расчете среднего показателя после выборки. Все зависит от того выполняет ли условие значение текущей ячейки согласно с логическим выражением, указанным в критерии отбора. При этом если не найдется ни одной ячейки удовлетворяющей условие выборки, тогда функция СРЗНАЧЕСЛИ возвращает ошибку #ДЕЛ/0!

Альтернативная формула для расчета среднего числа с условием

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

В программе Excel 90% задач можно решить несколькими правильными решениями.

• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

СРЗНАЧЕСЛИМН (функция СРЗНАЧЕСЛИМН)

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

Описание

Возвращает среднее значение (среднее арифметическое) всех ячеек, которые соответствуют нескольким условиям.

Синтаксис

Аргументы функции СРЗНАЧЕСЛИМН указаны ниже.

• Диапазон_усреднения: обязательный. Одна или несколько ячеек для вычисления среднего с числами или именами, массивами или ссылками, содержащими числа.
• Диапазон_условий1, диапазон_условий2, … параметр «диапазон_условий1» — обязательный, остальные диапазоны условий — нет. От 1 до 127 интервалов, в которых проверяется соответствующее условие.
• Условие1, условие2, … Параметр «условие1» является обязательным, остальные условия — нет. От 1 до 127 условий в форме числа, выражения, ссылки на ячейку или текста, определяющих ячейки, для которых будет вычисляться среднее. Например, условие может быть выражено следующим образом: 32, «32», «>32», «яблоки» или B4.

Замечания

• Если «диапазон_усреднения» является пустым или текстовым значением, то функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ/0!.
• Если ячейка в диапазоне условий пустая, функция СРЗНАЧЕСЛИМН обрабатывает ее как ячейку со значением 0.
• Ячейки в диапазоне, которые содержат значение ИСТИНА, оцениваются как 1; ячейки в диапазоне, которые содержат значение ЛОЖЬ, оцениваются как 0 (ноль).
• Каждая ячейка в аргументе «диапазон_усреднения» используется в вычислении среднего значения, только если все указанные для этой ячейки условия истинны.
• В отличие от аргументов диапазона и условия в функции СРЗНАЧЕСЛИ, в функции СРЗНАЧЕСЛИМН каждый диапазон_условий должен быть одного размера и формы с диапазоном_суммирования.
• Если ячейки в параметре «диапазон_усреднения» не могут быть преобразованы в численные значения, функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ/0!.
• Если нет ячеек, которые соответствуют условиям, функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ/0!.
• В этом аргументе можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одиночному символу; звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, то перед ними следует поставить знак тильды (~).

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

• Среднее значение — это среднее арифметическое, которое вычисляется путем сложения набора чисел с последующим делением полученной суммы на их количество. Например, средним значением для чисел 2, 3, 3, 5, 7 и 10 будет 5, которое является результатом деления их суммы, равной 30, на их количество, равное 6.
• Медиана — это число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана. Например, медианой для чисел 2, 3, 3, 5, 7 и 10 будет 4.
• Мода — это число, наиболее часто встречающееся в данном наборе чисел. Например, модой для чисел 2, 3, 3, 5, 7 и 10 будет 3.

При симметричном распределении множества чисел все три значения центральной тенденции будут совпадать. При смещенном распределении множества чисел значения могут быть разными.

Примеры

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу Enter. При необходимости измените ширину столбцов, чтобы видеть все данные.

Как рассчитать средние значения в Excel

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

Эту статью просмотрели 138,108 раз (а).

С математической точки зрения, «среднее» используется большинством людей для обозначения «центральной тенденции», которая относится к самому центру диапазона чисел. Существует три общих показателя центральной тенденции: (арифметическое) среднее, медиана и мода. В Microsoft Excel есть функции для всех трех показателей, а также возможность определять средневзвешенное значение, что полезно для определения средней цены при работе с различным количеством товаров по разным ценам.

• В большинстве случаев вы вводите числа в столбцы, поэтому для этих примеров вводите числа в ячейки с A1 по A10 на листе.
• Введите числа 2, 3, 5, 5, 7, 7, 7, 9, 16 и 19.
• Хотя в этом нет необходимости, вы можете найти сумму чисел, введя формулу «= СУММ (A1: A10)» в ячейку A11. (Не включайте кавычки; они служат для отделения формулы от остального текста.)

• Щелкните пустую ячейку, например A12, затем введите «= СРЕДНЕЕ (A1: 10)» (опять же, без кавычек) прямо в ячейке.
• Щелкните пустую ячейку, затем щелкните символ «f _x » на панели функций над рабочим листом. Выберите «СРЕДНЕЕ» из списка «Выбрать функцию:» ​​в диалоговом окне «Вставить функцию» и нажмите «ОК». Введите диапазон «A1: A10» в поле «Номер 1» диалогового окна «Аргументы функции» и нажмите «ОК».
• Введите знак равенства (=) на панели функций справа от символа функции. Выберите функцию СРЕДНЕЕ из раскрывающегося списка поля Имя слева от символа функции. Введите диапазон «A1: A10» в поле «Номер 1» диалогового окна «Аргументы функции» и нажмите «ОК».

• Если вы рассчитали сумму, как предложено, вы можете проверить это, введя «= A11 / 10» в любую пустую ячейку.
• Среднее значение считается хорошим индикатором центральной тенденции, когда отдельные значения в диапазоне выборки довольно близки друг к другу. Это не считается хорошим индикатором в выборках, где есть несколько значений, которые сильно отличаются от большинства других значений.

Введите числа, для которых вы хотите найти медиану. Мы будем использовать тот же диапазон из десяти чисел (2, 3, 5, 5, 7, 7, 7, 9, 16 и 19), который мы использовали в методе нахождения среднего значения. Введите их в ячейки от A1 до A10, если вы еще этого не сделали.

• Щелкните пустую ячейку, например A13, затем введите «= MEDIAN (A1: 10)» (опять же, без кавычек) прямо в ячейке.
• Щелкните пустую ячейку, затем щелкните символ «f _x » на панели функций над рабочим листом. Выберите «МЕДИАНА» из списка «Выбрать функцию:» ​​в диалоговом окне «Вставить функцию» и нажмите «ОК». Введите диапазон «A1: A10» в поле «Номер 1» диалогового окна «Аргументы функции» и нажмите «ОК».
• Введите знак равенства (=) на панели функций справа от символа функции. Выберите функцию МЕДИАНА из раскрывающегося списка поля Имя слева от символа функции. Введите диапазон «A1: A10» в поле «Номер 1» диалогового окна «Аргументы функции» и нажмите «ОК».

Наблюдайте за результатом в ячейке, в которую вы ввели функцию. Медиана — это точка, в которой половина чисел в выборке имеет значения выше медианного значения, а другая половина имеет значения ниже медианного значения. (В случае нашего диапазона выборки медианное значение равно 7.) Медиана может совпадать с одним из значений в диапазоне выборки, а может и не совпадать.

Введите числа, для которых вы хотите найти режим. Мы снова будем использовать тот же диапазон чисел (2, 3, 5, 5, 7, 7, 7, 9, 16 и 19), введенный в ячейки от A1 до A10.

• Для Excel 2007 и более ранних версий существует одна функция РЕЖИМ. Эта функция найдет единственный режим в диапазоне выборки чисел.
• Для Excel 2010 и более поздних версий вы можете использовать либо функцию РЕЖИМ, которая работает так же, как в более ранних версиях Excel, либо функцию РЕЖИМ.SNGL, которая использует предположительно более точный алгоритм для поиска режима. [1] Икс Источник исследования (Другая функция режима, MODE.MULT, возвращает несколько значений, если обнаруживает несколько режимов в выборке, но она предназначена для использования с массивами чисел вместо одного списка значений. [2] Икс Источник исследования )

• Щелкните пустую ячейку, например A14, затем введите «= MODE (A1: 10)» (опять же, без кавычек) прямо в ячейке. (Если вы хотите использовать функцию MODE.SNGL, введите «MODE.SNGL» вместо «MODE» в уравнении.)
• Щелкните пустую ячейку, затем щелкните символ «f _x » на панели функций над рабочим листом. Выберите «MODE» или «MODE.SNGL» из списка «Выберите функцию:» ​​в диалоговом окне «Вставить функцию» и нажмите «ОК». Введите диапазон «A1: A10» в поле «Номер 1» диалогового окна «Аргументы функции» и нажмите «ОК».
• Введите знак равенства (=) на панели функций справа от символа функции. Выберите функцию MODE или MODE.SNGL из раскрывающегося списка поля Имя слева от символа функции. Введите диапазон «A1: A10» в поле «Номер 1» диалогового окна «Аргументы функции» и нажмите «ОК».

• Если два числа появляются в списке одинаковое количество раз, функция РЕЖИМ или РЕЖИМ.SNGL сообщит значение, которое она встретит первой. Если вы измените «3» в списке сэмплов на «5», режим изменится с 7 на 5, потому что 5 встречается первой. Если, однако, вы измените список на три семерки перед тремя пятерками, режим снова будет 7.

• В этом примере мы включим метки столбцов. Введите метку «Цена за дело» в ячейку A1 и «Количество случаев» в ячейку B1.
• Первая партия была из 10 ящиков по 20 долларов за ящик. Введите «20 долларов» в ячейку A2 и «10» в ячейку B2.
• Спрос на тоник увеличился, поэтому вторая партия была отправлена ​​на 40 ящиков. Однако из-за спроса цена на тоник поднялась до 30 долларов за упаковку. Введите «30 долларов» в ячейку A3 и «40» в ячейку B3.
• Поскольку цена выросла, спрос на тоник упал, поэтому третья партия была всего на 20 ящиков. При более низком спросе цена за коробку упала до 25 долларов. Введите «25 долларов» в ячейку A4 и «20» в ячейку B4.

• СУММПРОИЗВ. Функция СУММПРОИЗВ умножает числа в каждой строке и добавляет их к произведению чисел в каждой из других строк. Вы указываете диапазон каждого столбца; поскольку значения находятся в ячейках от A2 до A4 и от B2 до B4, вы должны написать это как «= СУММПРОИЗВ (A2: A4, B2: B4)». В результате получается общая долларовая стоимость всех трех отправлений.
• СУММ. Функция СУММ складывает числа в одну строку или столбец. Поскольку мы хотим найти среднее значение цены упаковки тоника, мы просуммируем количество коробок, проданных во всех трех партиях. Если бы вы написали эту часть формулы отдельно, она бы читалась как «= СУММ (B2: B4)».

Поскольку среднее значение определяется делением суммы всех чисел на количество чисел, мы можем объединить две функции в одну формулу, записанную как «= СУММПРОИЗВ (A2: A4, B2: B4) / СУММ (B2: B4). ».

• Общая стоимость отправлений составляет 20 x 10 + 30 x 40 + 25 x 20, или 200 + 1200 + 500, или 1900 долларов.
• Общее количество проданных ящиков — 10 + 40 + 20 или 70.
• Средняя цена за футляр составляет 1900/70 = 27,14 доллара.