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

23.09.2019

Если в диапазоне нет пустых ячеек и только числа, без текста и т.д., то формула среднего значения будет вычисляться, как мы и привыкли в обиходе. Разделить на сумму весов можно в этой же ячейке, дописав формулу вручную, либо в соседней. В нашем случае, цифра 18,9 указывает на то, что среднему значению (32,8 у.е. в неделю) просто нельзя доверять. Найдем среднее всех ячеек, значения которых соответствуют определенному условию.

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

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

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

Но сейчас на дворе 21-й (двадцать первый) век и вручную считают достаточно редко, что, к сожалению, не в лучшую сторону отражается на умственных способностях граждан. Даже калькуляторы не в моде (в том числе программируемые и инженерные), тем паче счеты и логарифмические линейки.

Решил пока уделить больше внимания теоретическим вопросам анализа данных, чтобы, описывая расчеты, например, в Excel, можно было бы сослаться на базовые знания о статистике. Средняя арифметическая величина – один из наиболее часто используемых статистических показателей.

Расчет средней арифметической в Excel

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

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

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

Есть и стандартный для всех функций способ вызова. Нужно нажать на кнопку fx в начале строки, где прописываются функции (формулы) и тем самым вызвать Мастер функций. Снова жмем на «Ввод» или «Ок» Результат расчета отразиться в ячейке с формулой.

Среднее квадратическое отклонение: формула в Excel

Как нетрудно догадаться, формула СРЗНАЧ умеет считать только среднюю арифметическую простую, то есть все складывает и делит на количество слагаемых (за вычетом количества пустых ячеек).

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

Вообще, одни и те же задачи в Экселе можно решать разными способами, что делает табличный процессор очень гибким и практичным. Для этого есть готовая формула СРЗНАЧЕСЛИ. Есть и такая возможность – функция ПРОМЕЖУТОЧНЫЕ ИТОГИ. В параметре выбора формулы следует поставить 1 (а не 9, как в случае с суммированием).

Однако то, что описано выше встречается в 90% случаев и вполне достаточно для успешного применения. Среднее арифметическое в excel. Таблицы Excel, как нельзя лучше подходят для всяких вычислений. Мы даже не задумываемся, какой мощный инструмент находится на наших компьютерах, а значит, и не используем его в полную силу. Многие родители думают, что компьютер – это просто дорогая игрушка.

Как найти среднее арифметическое чисел?

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

Имеется таблица, состоящая их двух столбцов: из столбца с повторяющимися текстовыми значениями и столбца с числами. Создадим таблицу состоящую только из строк с уникальными текстовыми значениями. По числовому столбцу произведем вычисление среднего.

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

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

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

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

Excel превратил вычисление среднего арифметического нескольких ячеек в очень простую задачу – просто используйте функцию СРЗНАЧ (AVERAGE). Но что делать, если некоторые значения имеют больший вес, чем другие? Например, на многих курсах тесты имеют больший вес, чем задания. Для таких случаев необходимо рассчитывать среднее взвешенное .

В Excel нет функции для расчёта средневзвешенного значения, зато есть функция, которая сделает за Вас большую часть работы: СУММПРОИЗВ (SUMPRODUCT). И даже, если Вы никогда не использовали эту функцию раньше, то к концу этой статьи будете работать с ней как профи. Метод, который мы используем, работает в любой версии Excel, а также в других электронных таблицах, таких как Google Sheets.

Подготавливаем таблицу

Если Вы собираетесь вычислять среднее взвешенное, Вам потребуется минимум два столбца. Первый столбец (в нашем примере – столбец B) содержит оценки для каждого задания или теста. Второй столбец (столбец C) содержит веса. Больший вес означает большее влияние задания или теста на итоговую оценку.

Чтобы понять, что такое вес, Вы можете представить его, как процент от итоговой оценки. На самом деле это не так, поскольку в таком случае веса в сумме должны составлять 100%. Формула, которую мы разберем в этом уроке, будет подсчитывать все правильно и не зависеть от суммы, в которую складываются веса.

Вводим формулу

Теперь, когда наша таблица готова, мы добавляем формулу в ячейку B10 (подойдёт любая пустая ячейка). Как и с любой другой формулой в Excel, начинаем со знака равенства (=).

Первая часть нашей формулы – это функция СУММПРОИЗВ (SUMPRODUCT). Аргументы должны быть заключены в скобки, поэтому открываем их:

СУММПРОИЗВ(
=SUMPRODUCT(

Далее, добавляем аргументы функции. СУММПРОИЗВ (SUMPRODUCT) может иметь несколько аргументов, но обычно используют два. В нашем примере, первым аргументом будет диапазон ячеек B2:B9 , который содержит оценки.

СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9

Вторым аргументом будет диапазон ячеек C2:C9 , в котором содержатся веса. Между этими аргументами должен стоять разделитель точка с запятой (запятая). Когда все будет готово, закрываем скобки:

СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)

Теперь добавим вторую часть нашей формулы, которая поделит результат вычисляемый функцией СУММПРОИЗВ (SUMPRODUCT) на сумму весов. Позже мы обсудим, почему это важно.

Чтобы выполнить операцию деления, продолжаем уже введённую формулу символом / (прямой слеш), а далее записываем функцию СУММ (SUM):

СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(

Для функции SUM (СУММ) мы укажем только один аргумент – диапазон ячеек C2:C9 . Не забудьте после ввода аргумента закрыть скобки:

СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)

Готово! После нажатия клавиши Enter , Excel рассчитает среднее взвешенное значение. В нашем примере итоговый результат будет равен 83,6 .

Как это работает

Давайте разберем каждую часть формулы, начиная с функции СУММПРОИЗВ (SUMPRODUCT), чтобы понять, как она работает. Функция СУММПРОИЗВ (SUMPRODUCT) вычисляет произведение оценки каждого задания на его вес, а затем суммирует все полученные произведения. Другими словами, функция находит сумму произведений (sum of the products), отсюда она и получила своё название. Итак, для Задания 1 умножаем 85 на 5, а для Теста умножаем 83 на 25.

Если Вас удивляет, зачем перемножать значения в первой части, представьте, что чем больше вес у задания, тем большее число раз мы должны учитывать оценку за него. Например, Задание 2 посчитано 5 раз, а Итоговый экзамен – 45 раз. Вот почему Итоговый экзамен имеет большее влияние на итоговую оценку.

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

Если бы Вы могли заглянуть под капот функции СУММПРОИЗВ (SUMPRODUCT), то увидели, что на самом деле она считает вот что:

=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)

К счастью, нам не нужно писать такую длинную формулу, поскольку СУММПРОИЗВ (SUMPRODUCT) делает всё это автоматически.

Сама по себе функция СУММПРОИЗВ (SUMPRODUCT) возвращает нам огромное число – 10450 . В этот момент включается вторая часть формулы: /СУММ(C2:C9) или /SUM(C2:C9) , которая возвращает результат в нормальный диапазон оценок, давая ответ 83,6 .

Вторая часть формулы очень важна, т.к. позволяет автоматически корректировать вычисления. Помните, что веса не обязаны складываться в сумму 100%? Все это благодаря второй части формулы. Например, если мы увеличиваем одно или несколько значений весов, вторая часть формулы просто выполнит деление на большее значение, вновь приводя к правильному ответу. Или же мы можем сделать веса намного меньше, например, указать такие значения как 0,5 , 2,5 , 3 или 4,5 , и формула по-прежнему будет работать правильно. Здорово, правда?

Программа Ecxel является многообразной, поэтому есть несколько вариантов, которые позволят вам найти средние значение:

Первый вариант. Вы просто суммируете все ячейки и делите на их количество;

Второй вариант. Воспользоваться специальной командой, напишете в требуемой ячейки формулу =СРЗНАЧ(а тут укажите диапазон ячеек);

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

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

Начнм сначала и по порядку. Что значит среднее значение?

Среднее значение - это значение, которое является средним арифметическим значением, т.е. вычисляется сложением набора чисел с последующим делением всей суммы чисел на их количество. Например, для чисел 2, 3, 6, 7, 2 будет 4 (сумму чисел 20 делим на их количество 5)

В таблице Excel лично мне, проще всего было пользоваться формулой =СРЗНАЧ. Чтобы рассчитать среднее значение, необходимо ввести данные в таблицу, под столбцом данных написать функцию =СРЗНАЧ(), а в скобках указываем диапазон чисел в ячейках, выделив столбец с данными. После этого нажимаем ВВОД, либо просто кликаем левой кнопкой мышки на любой ячейке. Результат отобразится в ячейке под столбцом. С виду описано непонятно, но по факту - минутное дело.

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

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

Это очень просто, если данные уже внесены в ячейки. Если вас интересует просто число, достаточно выделить нужный диапазон /диапазоны, и внизу справа в строке состояния появится значение суммы этих чисел, их среднее арифметическое и их количество.

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

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

Проще простого. Для того, чтобы найти среднее значение в excel, понадобится всего лишь 3 ячейки. В первую мы запишем одно число, во вторую - другое. А в третьей ячейке мы забьем формулу, которая нам выдаст среднее значение между этими двумя числами из первой и второй ячейки. Если ячейка 1 называется А1, ячейка 2 называется B1, то в ячейке с формулой нужно записать так:

Такой формулой вычисляется среднее арифметическое двух чисел.

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

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

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

обводите значения, которые будут участвовать в расчте,нажимаете вкладку Формулы, там увидите слева есть Автосумма и рядом с ней треугольник, направленный вниз. щлкаете на этот треугольник и выбираете Среднее. Вуаля, готово) внизу столбика увидите среднее значение:)

Самым распространенным видом средней является средняя арифметическая.

Средняя арифметическая простая

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

Простая средняя арифметическая — Равна отношению суммы индивидуальных значений признака к количеству признаков в совокупности

Пример 1. Бригада из 6 рабочих получает в месяц 3 3,2 3,3 3,5 3,8 3,1 тыс.руб.

Найти среднюю заработную плату
Решение: (3 + 3,2 + 3,3 +3,5 + 3,8 + 3,1) / 6 = 3,32 тыс. руб.

Средняя арифметическая взвешенная

Если объем совокупности данных большой и представляет собой ряд распределения, то исчисляется взвешенная среднеарифметическая величина. Так определяют средневзвешенную цену за единицу продукции: общую стоимость продукции (сумму произведений ее количества на цену единицы продукции) делят на суммарное количество продукции.

Представим это в виде следующей формулы:

Взвешенная средняя арифметическая — равна отношению (суммы произведений значения признака к частоте повторения данного признака) к (сумме частот всех признаков).Используется, когда варианты исследуемой совокупности встречаются неодинаковое количество раз.

Пример 2. Найти среднюю заработную плату рабочих цеха за месяц

Средняя заработная плата может быть получена путем деления общей суммы заработной платы на общее число рабочих:

Ответ: 3,35 тыс.руб.

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

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

Средние, вычисляемые из интервальных рядов являются приближенными.

Пример 3 . Определить средний возраст студентов вечернего отделения.

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

При расчете средних в качестве весов могут использоваться не только абсолютные, но и относительные величины (частость):

Средняя арифметическая обладает целым рядом свойств, которые более полно раскрывают ее сущность и упрощают расчет:

1. Произведение средней на сумму частот всегда равно сумме произведений вариант на частоты, т.е.

2.Средняя арифметическая суммы варьирующих величин равна сумме средних арифметических этих величин:

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

4.Сумма квадратов отклонений вариантов от средней меньше, чем сумма квадратов отклонений от любой другой произвольной величины , т.е.

Лучше всего подходит в качестве программы для различных вычислений. Как правило, Excel поставляется вместе с «офисным» пакетом программ MS Office, который установлен почти на каждом компьютере. Но мало кто знает, насколько мощным функционалом обладает эта программа. Изучив основы Эксель, его можно применять практически в любой сфере деятельности. Эта программа очень пригодится школьникам для решения задач по математике, физике, химии, экономике и пр. Например, в Excel можно достаточно быстро и просто найти среднее значение нужных чисел.

Видео по расчету среднего значения

Как найти среднее значение в Excel?

Итак, как обычно рассчитывается среднее арифметическое? Для этого и разделить на их общее количество. Для решения очень простых задач этого достаточно, но во всех остальных случаях такой вариант не подойдет. Дело в том, что в реальной ситуации числа всегда меняются, количество этих чисел тоже. К примеру, у пользователя есть таблица, где указаны оценки студентов. И нужно найти средний балл каждого студента. Понятно, что у каждого из них будут разные оценки, а количество предметов на разных специальностях и на разных курсах тоже будет разным. Было бы очень глупо (и нерационально) все это отслеживать и считать вручную. Да и делать это не понадобится, поскольку в Excel есть специальная функция, которая поможет найти среднее значение любых чисел. Даже если они будут изменяться время от времени, программа будет автоматически пересчитывать новые значения.

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

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

Если теперь, например, изменить какое-то значение по одному из предметов (или вовсе его удалить и оставить поле пустым), то Эксель сразу же пересчитает формулу и выдаст новый результат.

Альтернативные способы расчета среднего значения

Еще один способ найти среднее значение в Excel — с помощью строки формул.

Она находится чуть ниже панели меню и чуть выше от первой строки рабочего листа Эксель. Именно здесь отображаются . Например, если нажать на ячейку, где уже посчитано среднее значение, то в строке формул можно увидеть примерно следующее: =СРЗНАЧ(B1:B6). А чуть левее находится кнопка «fx», нажав на которую, можно открыть знакомое уже окно для выбора нужной функции.

Также можно прописывать любые формулы и вручную. Для этого нужно в любой выбранной ячейке поставить знак «=», прописать вручную формулу (СРЗНАЧ), открыть скобку, выбрать нужный диапазон ячеек и закрыть скобку. Результат тут же будет отображен.

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