Вычисление среднего, медианы и режима в Excel

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

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

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

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

Например, чтобы вычислить среднее значение чисел {1, 2, 2, 3, 4, 6}, вы складываете их, а затем делите сумму на 6, что дает 3: (1+2+2+3+4 +6)/6=3.

В Microsoft Excel среднее значение можно вычислить с помощью одной из следующих функций:

СРЗНАЧ- возвращает среднее число.
СРЗНАЧ – возвращает среднее значение ячеек с любыми данными (числами, булевыми и текстовыми значениями).
СРЗНАЧЕСЛИ – находит среднее число по одному критерию.
AVERAGEIFS — находит среднее число на основе нескольких критериев.

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

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

=СРЕДНЕЕ(C2:C8)

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

=СРЗНАЧЕСЛИ(A2:A8, “Банан”, C2:C8)

Чтобы вычислить среднее значение на основе 2 условий, например, среднего значения продаж «Банан» со статусом «Доставлено», используйте СРЗНАЧЕСЛИМН:

=СРЗНАЧЕСЛИ(C2:C8,A2:A8, “Банан”, B2:B8, “Доставлено”)

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

Медиана – это среднее значение в группе чисел, расположенных в порядке возрастания или убывания, т.е. половина чисел больше медианы, а половина чисел меньше медианы. Например, медиана набора данных {1, 2, 2, 3, 4, 6, 9} равна 3.

Это прекрасно работает, когда в группе нечетное количество значений. Но что, если у вас есть четное количество значений? В этом случае медиана представляет собой среднее арифметическое (среднее) двух средних значений. Например, медиана {1, 2, 2, 3, 4, 6} равна 2,5. Чтобы вычислить его, вы берете 3-е и 4-е значения в наборе данных и усредняете их, чтобы получить медиану 2,5.

В Microsoft Excel медиана вычисляется с помощью функции МЕДИАНА. Например, чтобы получить медиану всех сумм в нашем отчете о продажах, используйте следующую формулу:

=МЕДИАНА(C2:C8)

Чтобы сделать пример более наглядным, я отсортировал числа в столбце C в порядке возрастания (хотя на самом деле это не требуется для работы формулы Excel Median):

В отличие от среднего, Microsoft Excel не предоставляет какой-либо специальной функции для вычисления медианы с одним или несколькими условиями. Однако вы можете «эмулировать» функциональные возможности МЕДИАНИФ и МЕДИАНИФМ, используя комбинацию двух или более функций, как показано в следующих примерах:

Как рассчитать режим в Excel

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

Например, мода набора значений {1, 2, 2, 3, 4, 6} равна 2. В Microsoft Excel моду можно вычислить с помощью одноименной функции, функции МОДА. Для нашего примера набора данных формула выглядит следующим образом:

=РЕЖИМ(C2:C8)

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

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

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

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

Например, общепризнано, что медиана лучше, чем среднее значение для расчета типичной заработной платы. Почему? Лучше всего это понять на примере. Пожалуйста, ознакомьтесь с примерами заработной платы для обычных должностей:

Электрик – $20/час
Медсестра – $26/час
Полицейский – 47 долларов в час.
Менеджер по продажам – $54/час
Инженер-технолог – $63/час

Теперь посчитаем среднее (среднее): сложим приведенные выше числа и разделим на 5: (20+26+47+54+63)/5=42. Таким образом, средняя заработная плата составляет 42 доллара в час. Средняя заработная плата составляет 47 долларов в час, и ее получает полицейский (1/2 зарплаты ниже, а 1/2 выше). Что ж, в данном конкретном случае среднее значение и медиана дают одинаковые числа.

Но давайте посмотрим, что произойдет, если мы расширим список зарплат, включив в него знаменитость, которая зарабатывает, скажем, около 30 миллионов долларов в год, что составляет примерно 14 500 долларов в час. Теперь средняя заработная плата составляет 2451,67 доллара в час, зарплату, которую никто не зарабатывает! Напротив, медиана существенно не изменилась из-за этого одного выброса, она составляет 50,50 долларов в час.