Как построить медиану в excel
Медиана в статистике
Центральную тенденцию данных можно рассматривать не только, как значение с нулевым суммарным отклонением (среднее арифметическое) или максимальную частоту (мода), но и как некоторую отметку (значение в совокупности), делящую ранжированные данные (отсортированные по возрастанию или убыванию) на две равные части. Половина исходных данных меньше этой отметки, а половина – больше. Это и есть медиана.
Итак, медиана в статистике – это уровень показателя, который делит набор данных на две равные половины. Значения в одной половине меньше, а в другой больше медианы. В качестве примера обратимся к набору нормально распределенных случайных чисел.
Очевидно, что при симметричном распределении середина, делящая совокупность пополам, будет находиться в самом центре – там же, где средняя арифметическая (и мода). Это, так сказать, идеальная ситуация, когда мода, медиана и средняя арифметическая совпадают и все их свойства приходятся на одну точку – максимальная частота, деление пополам, нулевая сумма отклонений – все в одном месте. Однако, жизнь не так симметрична, как нормальное распределение.
Допустим, мы имеем дело с техническими замерами отклонений от ожидаемой величины чего-нибудь (содержания элементов, расстояния, уровня, массы и т.д. и т.п.). Если все ОК, то отклонения, скорее всего, будут распределены по закону, близкому к нормальному, примерно, как на рисунке выше. Но если в процессе присутствует важный и неконтролируемый фактор, то могут появиться аномальные значения, которые в значительной мере повлияют на среднюю арифметическую, но при этом почти не затронут медиану.
Медиана выборки – это альтернатива средней арифметической, т.к. она устойчива к аномальным отклонениям (выбросам).
Математическим свойством медианы является то, что сумма абсолютных (по модулю) отклонений от медианного значения дает минимально возможное значение, если сравнивать с отклонениями от любой другой величины. Даже меньше, чем от средней арифметической, о как! Данный факт находит свое применение, например, при решении транспортных задач, когда нужно рассчитать место строительства объектов около дороги таким образом, чтобы суммарная длина рейсов до него из разных мест была минимальной (остановки, заправки, склады и т.д. и т.п.).
Формула медианы
Формула медианы в статистике для дискретных данных чем-то напоминает формулу моды. А именно тем, что формулы как таковой нет. Медианное значение выбирают из имеющихся данных и только, если это невозможно, проводят несложный расчет.
Первым делом данные ранжируют (сортируют по убыванию). Далее есть два варианта. Если количество значений нечетно, то медиана будет соответствовать центральному значению ряда, номер которого можно определить по формуле:
№Me – номер значения, соответствующего медиане,
N – количество значений в совокупности данных.
Тогда медиана обозначается, как
Это первый вариант, когда в данных есть одно центральное значение. Второй вариант наступает тогда, когда количество данных четно, то есть вместо одного есть два центральных значения. Выход прост: берется средняя арифметическая из двух центральных значений:
В интервальных данных выбрать конкретное значение не представляется возможным. Медиану рассчитывают по определенному правилу.
Для начала (после ранжирования данных) находят медианный интервал. Это такой интервал, через который проходит искомое медианное значение. Определяется с помощью накопленной доли ранжированных интервалов. Где накопленная доля впервые перевалила через 50% всех значений, там и медианный интервал.
Не знаю, кто придумал формулу медианы, но исходили явно из того предположения, что распределение данных внутри медианного интервала равномерное (т.е. 30% ширины интервала – это 30% значений, 80% ширины – 80% значений и т.д.). Отсюда, зная количество значений от начала медианного интервала до 50% всех значений совокупности (разница между половиной количества всех значений и накопленной частотой предмедианного интервала), можно найти, какую долю они занимают во всем медианном интервале. Вот эта доля аккурат переносится на ширину медианного интервала, указывая на конкретное значение, именуемое впоследствии медианой.
Обратимся к наглядной схеме.
Немного громоздко получилось, но теперь, надеюсь, все наглядно и понятно. Чтобы при расчете каждый раз не рисовать такой график, можно воспользоваться готовой формулой. Формула медианы имеет следующий вид:
где xMe — нижняя граница медианного интервала;
iMe — ширина медианного интервала;
∑f/2 — количество всех значений, деленное на 2 (два);
S(Me-1)— суммарное количество наблюдений, которое было накоплено до начала медианного интервала, т.е. накопленная частота предмедианного интервала;
fMe — число наблюдений в медианном интервале.
Как нетрудно заметить, формула медианы состоит из двух слагаемых: 1 – значение начала медианного интервала и 2 – та самая часть, которая пропорциональна недостающей накопленной доли до 50%.
Для примера рассчитаем медиану по следующим данным.
Требуется найти медианную цену, то есть ту цену, дешевле и дороже которой по половине количества товаров. Для начала произведем вспомогательные расчеты накопленной частоты, накопленной доли, общего количества товаров.
По последней колонке «Накопленная доля» определяем медианный интервал – 300-400 руб (накопленная доля впервые более 50%). Ширина интервала – 100 руб. Теперь остается подставить данные в приведенную выше формулу и рассчитать медиану.
То есть у одной половины товаров цена ниже, чем 350 руб., у другой половины – выше. Все просто. Средняя арифметическая, рассчитанная по этим же данным, равна 355 руб. Отличие не значительное, но оно есть.
Расчет медианы в Excel
Медиану для числовых данных легко найти, используя функцию Excel, которая так и называется — МЕДИАНА. Другое дело интервальные данные. Соответствующей функции в Excel нет. Поэтому нужно задействовать приведенную выше формулу. Что поделаешь? Но это не очень трагично, так как расчет медианы по интервальным данным – редкий случай. Можно и на калькуляторе разок посчитать.
Напоследок предлагаю задачку. Имеется набор данных. 15, 5, 20, 5, 10. Каково среднее значение? Четыре варианта:
Мода, медиана и среднее значение выборки – это разный способ определить центральную тенденцию в выборке.
Ниже видеоролик о том, как рассчитать медиану в Excel.
Формулы для расчета среднего по функции МЕДИАНА и МОДА в Excel
Понятие среднее значение за частую подразумевает среднее арифметическое число. То есть результат после разделения суммы чисел на их количество. Так работает функция СРЗНАЧ. Но в программе Excel можно получить еже 2 вида средних значений: МЕДИАНА и МОДА – самое популярное и часто встречаемое значение в списке данных.
Примеры как найти медиальное, популярное и арифметическое среднее
Ниже на рисунке представлен список из 20 студентов с оценками их контрольных работ. Допустим, что мы хотим проанализировать все их оценки по всем трем видам средних значений в Excel:
На основе полученных значений сделаем соответствующие выводы.
Три формулы, используемые для расчетов среднего арифметического, медиального и самого популярного значения в диапазоне исходных данных, которые изображены выше на рисунке:
В результате вычислений формул получаем 3 разных значения средний для одной и той же самой группы чисел.
Как найти среднее значение в Excel с помощью функции СРЗНАЧ
Среднее арифметическое число оценок равно 85,1 медиальное – 90,5 и наиболее часто встречаемая оценка 93 балла. Среднеарифметическое число рассчитано с помощью функции СРЗНАЧ, которая суммирует все значения в указанном диапазоне и делит их сумму на их же количество. Сравнение медианы и наиболее часто встречающегося числа со средним арифметическим позволяет получить дополнительную информацию после такого анализа данных.
Формула вычисления среднего значения в Excel по функции МЕДИАНА
Медиану в Excel вычисляют с помощью функции со соответствующим названием МЕДИАНА. Если все оценки будут отсортированы по возрастанию, функция МЕДИАНА будет возвращать значение, которое находится точно по середине списка данных. Так как общее количество оценок представляет собой парное число, данный список не содержит конкретного числового значения. В такие случаи функция возвращает среднее арифметическое число двух значений находящийся наиболее близко к центру. Как видно на рисунке после сортировки списка по убыванию наиболее близко к центру находятся сразу два числа 90 и 91:
Соответственно функция МЕДИАНА возвращает в итоговом результате своих вычислений число 90,5. Что из этого следует?
Большая разница между средним арифметическим числом и медианой означает, что показатели оценок весьма неравномерные. Например, в данному случае большая разница возникает между наибольшими и наименьшими оценками. Если же среди статистических данных находится одно аномально большое или малое значение, оно может существенно влиять на показатель среднего арифметического числа, но не на медиану!
Формула расчета среднего значения в Excel с функцией МОДА
Наиболее популярное значение находится с помощью функции с соответствующем названием МОДА. Данная функция возвращает оценку, которую получило наибольшее количество студентов. На первом рисунке на против оценок отображается их количество в списке. Как видно оценка в 93 балла появляется 4 раза в одном и том же списке – наиболее чаще других оценок. Если в наборе данных каждое значение появляется такое же количество раз (например, все значения уникальны – значит появляются в списке только 1 раз или же в столбце «Кол-во оценок» все значения одинаковы), тогда функция МОДА возвращает код ошибки недоступных данных #Н/Д! Если больше чем одно значение встречается такое же количество раз (например, если бы 91 балл также встречался 4 раза, как и 93 балла), тогда функция возвращает первый найденный результат.
Функция МОДА.ОДН возвращает массив средних значений в Excel
Начиная с версии 2010-й в Excel были введены две новые функции служащие для поиска наиболее популярных значений МОДА.НСК и МОДА.ОДН. Функция МОДА.НСК работает также, как и обычная МОДА возвращает наиболее часто встречаемое значение, если их есть несколько. А функция МОДА.ОДН возвращает целый одномерный массив значений со всеми популярными значениями.
Ниже на рисунке представлен список чисел, в котором есть сращу 2 самых популярных числа (2 и 4). Числа 2 и 4 появляются в общем списке двукратно. Так как функция МОДА.ОДН возвращает целый массив значений, следует для нее соответствующий диапазон ячеей для заполнения этими всеми значениями. На рисунке показано как приготовлены 3 ячейки, несмотря на то, что в данном случае функция МОДА.ОДН возвращает только 2 значения. Если диапазон ячеек больше чем возвращаемый массив с результатами лишние ячейки будут заполнены кодом ошибки недоступных данных #Н/Д!
Формула, которая содержит функцию МОДА.ОДН выполняется в массиве с помощью нажатия комбинации клавиш CTRL+SHIFT+Enter (а не просто Enter как обычно выполняется ввод формул). Если все сделано правильно в строке формул по краям появятся фигурные скобки (<>), которые информируют пользователя о том, что введена формула массива.
Как построить график медианы в excel
Функция МЕДИАНА в Excel для выполнения статистического анализа
Функция МЕДИАНА в Excel используется для анализа диапазона числовых значений и возвращает число, которое является серединой исследуемого множества (медианой). То есть, данная функция условно разделяет множество чисел на два подмножества, первое из которых содержит числа меньше медианы, а второе – больше. Медиана является одним из нескольких методов определения центральной тенденции исследуемого диапазона.
Примеры использования функции МЕДИАНА в Excel
Пример 1. При исследовании возрастных групп студентов использовались данные случайно выбранной группы учащихся в ВУЗе. Задача – определить срединный возраст студентов.
Формула для расчета:
То есть в группе есть студенты, возраст которых меньше 21 года и больше этого значения.
Сравнение функций МЕДИАНА и СРЗНАЧ для вычисления среднего значения
Пример 2. Во время вечернего обхода в больнице каждому больному была замерена температура тела. Продемонстрировать целесообразность использования параметра медиана вместо среднего значения для исследования ряда полученных значений.
Формула для нахождения среднего значения:
Формула для нахождения медианы:
Как видно из показателя среднего значения, в среднем температура у пациентов выше нормы, однако это не соответствует действительности. Медиана показывает, что как минимум у половины пациентов наблюдается нормальная температура тела, не превышающая показатель 36,6.
Внимание! Еще одним методом определения центральной тенденции является мода (наиболее часто встречающееся значение в исследуемом диапазоне). Чтобы определить центральную тенденцию в Excel следует использовать функцию МОДА. Обратите внимание: в данном примере значения медианы и моды совпадают:
То есть срединная величина, делящая одно множество на подмножества меньших и больших значений также является и наиболее часто встречающимся значением в множестве. Как видно, у большинства пациентов температура составляет 36,6.
Пример расчета медианы при статистическом анализе в Excel
Пример 3. В магазине работают 3 продавца. По результатам последних 10 дней необходимо определить работника, которому будет выдана премия. При выборе лучшего работника учитывается степень эффективности его работы, а не число проданных товаров.
Исходная таблица данных:
Для характеристики эффективности будем использовать сразу три показателя: среднее значение, медиана и мода. Определим их для каждого работника с использованием формул СРЗНАЧ, МЕДИАНА и МОДА соответственно:
Для определения степени разброса данных используем величину, которая является суммарным значением модуля разницы среднего значения и моды, среднего значения и медианы соответственно. То есть коэффициент x=|av-med|+|av-mod|, где:
Рассчитаем значение коэффициента x для первого продавца:
Аналогично проведем расчеты для остальных продавцов. Полученные результаты:
Определим продавца, которому будет выдана премия:
Примечание: функция НАИМЕНЬШИЙ возвращает первое минимальное значение из рассматриваемого диапазона значений коэффициента x.
Коэффициент x является некоторой количественной характеристикой стабильности работы продавцов, которую ввел экономист магазина. С его помощью удалось определить диапазон с наименьшими отклонениями значений. Этот способ демонстрирует, как можно использовать сразу три метода определения центральной тенденции для получения наиболее достоверных результатов.
Особенности использования функции МЕДИАНА в Excel
Функция имеет следующий синтаксис:
=МЕДИАНА( число1; [число2];. )
Медиана в EXCEL
Начнем с медианы для выборок (т.е. для фиксированного набора значений).
Медиана выборки
Для вычисления медианы необходимо сначала отсортировать множество чисел (значения в выборке ). Например, медианой для выборки (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.
Очевидно, что средняя зарплата (71 тыс. руб.) не отражает тот факт, что 86% сотрудников получает не более 30 тыс. руб. (т.е. 86% сотрудников получает зарплату в более, чем в 2 раза меньше средней!). В то же время медиана (15 тыс. руб.) показывает, что как минимум у половины сотрудников зарплата меньше или равна 15 тыс. руб.
Медиана непрерывного распределения
Если Функция распределения F (х) случайной величины х непрерывна, то медиана является решением уравнения F(х) =0,5.
Например, решив аналитическим способом это уравнение для Логнормального распределения lnN(μ; σ 2 ), получим, что медиана вычисляется по формуле =EXP(μ). При μ=0, медиана равна 1.
Примечание : Напомним, что интеграл от функции плотности вероятности по всей области задания случайной величины равен единице.
Поэтому, линия медианы (х=Медиана) делит площадь под графиком функции плотности вероятности на две равные части.
Примеры функции КВАРТИЛЬ в Excel для расчета медианы квартиля
Функция КВАРТИЛЬ в Excel используется для расчета квартиля диапазона числовых данных и возвращает соответствующее числовое значение.
Функция КВАРТИЛЬ.ВКЛ вычисляет на основе указанной процентили в качестве второго аргумента функции. Полностью соответствует первой функции. Последняя используется в Excel 2007 и более ранних версиях и оставлена для совместимости.
Функция КВАРТИЛЬ.ИСКЛ используется для расчета квартили диапазона числовых значений на основе известной процентили, за исключением граничных значений (минимального и максимального значения в диапазоне).
Квартили используются для распределения диапазона чисел на четыре равные части:
Функция КВАРТИЛЬ.ВКЛ может быть использована не только для определения медианы выборки (второго квартиля), а и нахождения минимального и максимального значений соответственно. При работе с большими диапазонами чисел для подобных расчетов рекомендуется использовать функции МИН и МАКС соответственно.
Существует несколько алгоритмов расчета квартилей. Все рассмотренные функции используют следующую формулу:
Для расчета индекса квантиля (x) функция КВАРТИЛЬ.ВКЛ используют формулу:
x=(n-1)p, где n – количество элементов в диапазоне.
Функция КВАРТИЛЬ.ИСКЛ использует формулу x=(n+1)p.
В Excel принято так, что первые выше указанные 2 функции используют метод N-1-интерполяцию, а третья функция – N+1-интерполяцию.
Функция МЕДИАНА
Определение и формула медианы в статистике, расчет в Excel, примеры использования, свойства, отличие от средней арифметичесчкой.
Примеры использования функции МЕДИАНА в Excel
Пример 1. При исследовании возрастных групп студентов использовались данные случайно выбранной группы учащихся в ВУЗе. Задача – определить срединный возраст студентов.
Формула для расчета:
То есть в группе есть студенты, возраст которых меньше 21 года и больше этого значения.
Описание
Возвращает медиану заданных чисел. Медиана — это число, которое является серединой множества чисел.
Медиана выборки
Для вычисления медианы необходимо сначала отсортировать множество чисел (значения в выборке ). Например, медианой для выборки (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.
Чтобы в этом убедиться – построим гистограмму для симметричной выборки, состоящую из 36 значений, и вычислим среднее и медиану (см. файл примера лист Медиана-выборка ).
Очевидно, что средняя зарплата (71 тыс. руб.) не отражает тот факт, что 86% сотрудников получает не более 30 тыс. руб. (т.е. 86% сотрудников получает зарплату в более, чем в 2 раза меньше средней!). В то же время медиана (15 тыс. руб.) показывает, что как минимум у половины сотрудников зарплата меньше или равна 15 тыс. руб.
Описание
Microsoft Excel функция МЕДИАНА возвращает среднее значение предоставленных чисел.
Функция МЕДИАНА – это встроенная в Excel функция, которая относится к категории статистических функций.
Её можно использовать как функцию рабочего листа (WS) в Excel.
Как функцию рабочего листа, функцию МЕДИАНА можно ввести как часть формулы в ячейку рабочего листа.
Первый квартиль
Значение квартиля Q1 находится в интервале 68,98 – 71,70, соответствующего частоте fQ1 = 150:4 = 37,5
Сравнение функций МЕДИАНА и СРЗНАЧ для вычисления среднего значения
Пример 2. Во время вечернего обхода в больнице каждому больному была замерена температура тела. Продемонстрировать целесообразность использования параметра медиана вместо среднего значения для исследования ряда полученных значений.
Формула для нахождения среднего значения:
Формула для нахождения медианы:
Как видно из показателя среднего значения, в среднем температура у пациентов выше нормы, однако это не соответствует действительности. Медиана показывает, что как минимум у половины пациентов наблюдается нормальная температура тела, не превышающая показатель 36,6.
Внимание! Еще одним методом определения центральной тенденции является мода (наиболее часто встречающееся значение в исследуемом диапазоне). Чтобы определить центральную тенденцию в Excel следует использовать функцию МОДА. Обратите внимание: в данном примере значения медианы и моды совпадают:
То есть срединная величина, делящая одно множество на подмножества меньших и больших значений также является и наиболее часто встречающимся значением в множестве. Как видно, у большинства пациентов температура составляет 36,6.
Медиана непрерывного распределения
Если Функция распределения F (х) случайной величины х непрерывна, то медиана является решением уравнения F(х) =0,5.
Например, решив аналитическим способом это уравнение для Логнормального распределения lnN(μ; σ 2 ), получим, что медиана вычисляется по формуле =EXP(μ). При μ=0, медиана равна 1.
Примечание : Напомним, что интеграл от функции плотности вероятности по всей области задания случайной величины равен единице.
Поэтому, линия медианы (х=Медиана) делит площадь под графиком функции плотности вероятности на две равные части.
Полезные видео
Ознакомиться с основными принципами вычисления медианы в программе Excel можно, просмотрев несколько обучающих роликов в YouTube
Оцените качество статьи. Нам важно ваше мнение:
Особенности использования функции МЕДИАНА в Excel
Функция имеет следующий синтаксис:
=МЕДИАНА( число1; [число2];…)
Скачать примеры функции МЕДИАНА для статистического анализа в Excel
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.