Как считать медиану в excel

Медиана в EXCEL

Для вычисления медианы в MS EXCEL существует специальная функция МЕДИАНА() . В этой статье дадим определение медианы и научимся вычислять ее для выборки и для заданного закона распределения случайной величины.

Начнем с медианы для выборок (т.е. для фиксированного набора значений).

Медиана выборки

Медиана (median) – это число, которое является серединой множества чисел: половина чисел множества больше, чем медиана , а половина чисел меньше, чем медиана .

Для вычисления медианы необходимо сначала отсортировать множество чисел (значения в выборке ). Например, медианой для выборки (2; 3; 3; 4 ; 5; 7; 10) будет 4. Т.к. всего в выборке 7 значений, три из них меньше, чем 4 (т.е. 2; 3; 3), а три значения больше (т.е. 5; 7; 10).

Если множество содержит четное количество чисел, то вычисляется среднее для двух чисел, находящихся в середине множества. Например, медианой для выборки (2; 3; 3 ; 6 ; 7; 10) будет 4,5, т.к. (3+6)/2=4,5.

Для определения медианы в MS EXCEL существует одноименная функция МЕДИАНА() , английский вариант MEDIAN().

Медиана не обязательно совпадает со средним значением (mean, average) в выборке . Совпадение имеет место только в том случае, если значения в выборке распределены симметрично относительно среднего . Например, для выборки (1; 2; 3 ; 4 ; 5; 6) медиана и среднее равны 3,5.

Чтобы в этом убедиться — построим гистограмму для симметричной выборки, состоящую из 36 значений, и вычислим среднее и медиану (см. файл примера лист Медиана-выборка ).

В чем же ценность медианы ? Почему ее используют зачастую наравне со средним значением ?

Оба параметра используются для определения «центральной тенденции» выборки . Для выборки с несимметричным распределением, медиана будет отличаться от среднего . Например, для (1; 2; 3 ; 4 ; 5; 600) медиана равна 3,5, а вот среднее равно 103,5 (смещено в сторону б о льшего значения).

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

Очевидно, что средняя зарплата (71 тыс. руб.) не отражает тот факт, что 86% сотрудников получает не более 30 тыс. руб. (т.е. 86% сотрудников получает зарплату в более, чем в 2 раза меньше средней!). В то же время медиана (15 тыс. руб.) показывает, что как минимум у половины сотрудников зарплата меньше или равна 15 тыс. руб.

Примечание : Так как медиана является 50-й процентилью и 2-й квартилью , ее также можно вычислить с помощью формул =ПРОЦЕНТИЛЬ.ВКЛ( Выборка;0,5 ) и =КВАРТИЛЬ.ВКЛ( Выборка;2 ) , где Выборка – это ссылка на диапазон, содержащий значения выборки.

Если выборка содержит нечетное количество чисел, то для вычисления медианы можно также воспользоваться формулой: НАИБОЛЬШИЙ(Выборка;СЧЁТ(Выборка)/2) .

Медиана непрерывного распределения

Если Функция распределения F (х) случайной величины х непрерывна, то медиана является решением уравнения F(х) =0,5.

Примечание : подробнее о Функции распределения см. статью Функция распределения и плотность вероятности в MS EXCEL .

Если известна Функция распределения F(х) или функция плотности вероятности p (х) , то медиану можно найти из уравнения:

Например, решив аналитическим способом это уравнение для Логнормального распределения lnN(μ; σ 2 ), получим, что медиана вычисляется по формуле =EXP(μ). При μ=0, медиана равна 1.

Обратите внимание на точку Функции распределения , для которой F (х)=0,5 (см. картинку выше) . Абсцисса этой точкиравна1. Это и есть значение медианы, что естественно совпадает с ранее вычисленным значением по формуле em.

В MS EXCEL медиану для логнормального распределения LnN(0;1) можно вычислить по формуле =ЛОГНОРМ.ОБР(0,5;0;1) .

Примечание : Напомним, что интеграл от функции плотности вероятности по всей области задания случайной величины равен единице.

Поэтому, линия медианы (х=Медиана) делит площадь под графиком функции плотности вероятности на две равные части.

Примечание : В статье о распределениях MS EXCEL приведены ссылки на распределения для которых в MS EXCEL существуют специальные функции ( нормальное распределение , гамма-распределение , Экспоненциальное и др.). Используя эти функции можно вычислить медиану соответствующего распределения.

Функция МЕДИАНА в Excel для выполнения статистического анализа

Функция МЕДИАНА в Excel используется для анализа диапазона числовых значений и возвращает число, которое является серединой исследуемого множества (медианой). То есть, данная функция условно разделяет множество чисел на два подмножества, первое из которых содержит числа меньше медианы, а второе – больше. Медиана является одним из нескольких методов определения центральной тенденции исследуемого диапазона.

Примеры использования функции МЕДИАНА в Excel

Пример 1. При исследовании возрастных групп студентов использовались данные случайно выбранной группы учащихся в ВУЗе. Задача – определить срединный возраст студентов.

Формула для расчета:

• B3:B15 – диапазон исследуемых возрастов.

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

Сравнение функций МЕДИАНА и СРЗНАЧ для вычисления среднего значения

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

Формула для нахождения среднего значения:

Формула для нахождения медианы:

Как видно из показателя среднего значения, в среднем температура у пациентов выше нормы, однако это не соответствует действительности. Медиана показывает, что как минимум у половины пациентов наблюдается нормальная температура тела, не превышающая показатель 36,6.

Внимание! Еще одним методом определения центральной тенденции является мода (наиболее часто встречающееся значение в исследуемом диапазоне). Чтобы определить центральную тенденцию в Excel следует использовать функцию МОДА. Обратите внимание: в данном примере значения медианы и моды совпадают:

То есть срединная величина, делящая одно множество на подмножества меньших и больших значений также является и наиболее часто встречающимся значением в множестве. Как видно, у большинства пациентов температура составляет 36,6.

Пример расчета медианы при статистическом анализе в Excel

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

Исходная таблица данных:

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

Для определения степени разброса данных используем величину, которая является суммарным значением модуля разницы среднего значения и моды, среднего значения и медианы соответственно. То есть коэффициент x=|av-med|+|av-mod|, где:

• av – среднее значение;
• med – медиана;
• mod – мода.

Рассчитаем значение коэффициента x для первого продавца:

Аналогично проведем расчеты для остальных продавцов. Полученные результаты:

Определим продавца, которому будет выдана премия:

Примечание: функция НАИМЕНЬШИЙ возвращает первое минимальное значение из рассматриваемого диапазона значений коэффициента x.

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

Особенности использования функции МЕДИАНА в Excel

Функция имеет следующий синтаксис:

=МЕДИАНА( число1; [число2];. )

• число1 – обязательный аргумент, характеризующий первое числовое значение, содержащееся в исследуемом диапазоне;
• [число2] – необязательный второй (и последующие аргументы, всего до 255 аргументов), характеризующий второе и последующие значения исследуемого диапазона.

1. При расчетах удобнее передавать сразу весь диапазон исследуемых значений вместо последовательного ввода аргументов.
2. В качестве аргументов принимаются данные числового типа, имена, содержащие числа, данные ссылочного типа и массивы (например, =МЕДИАНА()).
3. При расчете медианы учитываются ячейки, содержащие пустые значения или логические ИСТИНА, ЛОЖЬ, которые будут интерпретированы как числовые значения 1 и 0 соответственно. Например, результат выполнения функции с логическими значениями в аргументах (ИСТИНА;ЛОЖЬ) эквивалентен результату выполнения с аргументами (1;0) и равен 0,5.
4. Если один или несколько аргументов функции принимают текстовые значения, которые не могут быть преобразованы в числовые, или содержат коды ошибок, результатом выполнения функции будет код ошибки #ЗНАЧ!.
5. Для определения медианы выборки могут быть использованы другие функции Excel: ПРОЦЕНТИЛЬ.ВКЛ, КВАРТИЛЬ.ВКЛ, НАИБОЛЬШИЙ Примеры использования:

• =ПРОЦЕНТИЛЬ.ВКЛ(A1:A10;0,5), поскольку по определению медиана – 50-я процентиль.
• =КВАРТИЛЬ.ВКЛ(A1:A10;2), так как медиана – 2-я квартиль.
• =НАИБОЛЬШИЙ(A1:A9;СЧЁТ(A1:A9)/2), но только если количество чисел в диапазоне является нечетным числом.

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

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

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

Как найти среднее значение, медиану и моду в Excel (с примерами)

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

=AVERAGE( A1:A10 ) =MEDIAN( A1:A10 ) =MODE.MULT( A1:A10 ) 

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

В следующих примерах показано, как использовать эти формулы на практике со следующим набором данных:

Пример: нахождение среднего значения в Excel

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

На следующем снимке экрана показано, как рассчитать среднее значение набора данных в Excel:

Среднее значение получается 19,11 .

Пример: поиск медианы в Excel

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

На следующем снимке экрана показано, как рассчитать медиану набора данных в Excel:

Медиана получается 20 .

Пример: поиск режима в Excel

Мода представляет значение, которое чаще всего встречается в наборе данных. Обратите внимание, что набор данных может не иметь режима, иметь один режим или несколько режимов.

На следующем снимке экрана показано, как рассчитать режим(ы) набора данных в Excel:

Моды оказываются 7 и 25.Каждое из этих значений встречается в наборе данных дважды, что встречается чаще, чем любое другое значение.

Примечание. Если вместо этого вы используете функцию =MODE() , она вернет только первый режим. Для этого набора данных будет возвращено только значение 7. По этой причине всегда рекомендуется использовать функцию =MODE.MULT() , если в наборе данных окажется более одной моды.

Excel: как рассчитать медиану в сводной таблице

Часто вам может понадобиться вычислить медианное значение в сводной таблице Excel.

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

В следующем пошаговом примере показано, как это сделать.

Шаг 1: введите данные

Во-первых, давайте введем следующие данные, которые показывают очки, набранные баскетболистами в разных командах:

Шаг 2: Рассчитайте медианное значение по группе

Затем мы можем использовать следующую формулу для расчета среднего значения очков для каждой команды:

=MEDIAN(IF( $A$2:$B$13 = A2 , $B$2:$B$13 )) 

На следующем снимке экрана показано, как использовать эту формулу на практике:

Шаг 3: Создайте сводную таблицу

Чтобы создать сводную таблицу, щелкните вкладку « Вставка » на верхней ленте, а затем щелкните значок «Сводная таблица»:

В появившемся новом окне выберите A1: C13 в качестве диапазона и поместите сводную таблицу в ячейку E1 существующего рабочего листа:

После того, как вы нажмете OK , в правой части экрана появится новая панель полей сводной таблицы .

Перетащите поле « Команда» в поле « Строки », затем перетащите поля « Точки » и « Средние точки » в поле « Значения »:

Затем щелкните стрелку раскрывающегося списка « Сумма медианных точек» и выберите « Настройки поля значений »:

В появившемся новом окне измените пользовательское имя на Median Pts , а затем нажмите « Среднее » в качестве итогового значения:

Как только вы нажмете OK , среднее значение очков для каждой команды будет добавлено в сводную таблицу:

Сводная таблица теперь содержит следующую информацию:

• Каждое уникальное название команды
• Сумма очков, набранных каждой командой
• Среднее количество очков, набранных каждой командой

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel: