Как найти медиану графика

Для вычисления медианы в 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

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

Формула МЕДИАНА в Excel

Ниже приведена формула МЕДИАНА в excel.

Аргументы, используемые для формулы MEDIAN:

число_1, число_2,…, число_n: набор чисел или ссылок на ячейки, обозначающих числовые значения, для которых должна быть вычислена медиана.

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

Вывод:

МЕДИАНА вычисляет медиану заданного набора чисел. Ровно половина входных чисел имеет значения больше медианы, а половина чисел имеет значения меньше медианы. Если число входов четное, функция МЕДИАНА вычисляет среднее значение двух чисел в середине. Предположим, что в качестве входных данных задано всего шесть чисел, тогда МЕДИАНА вернет среднее из трех rd и 4 th числа. Функция МЕДИАНА сначала переставляет входные числовые значения в порядке возрастания, а затем определяет среднее значение.

Иллюстрация

Предположим, вы хотите найти медиану чисел <2, 3, 4, 5, 6>. Эти числа указаны в ячейке B3: B7.

Чтобы вычислить медианную функцию, вы можете использовать следующую формулу MEDIAN:

Формула MEDIAN вернет среднее значение, то есть 4.

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

= МЕДИАНА (2, 3, 4, 5, 6)

Эта формула MEDIAN в Excel вернет тот же результат.

Если вы используете в качестве аргумента четное число значений, например <2, 3, 4, 5, 6, 7>, формула МЕДИАНА выведет среднее из двух средних значений — 4 и 5.

что составляет 4,5.

Предположим, вы даете девять аргументов <3, 8, 4, 12, 14, 5, 1, 2, 10>, которые не расположены в порядке возрастания, MEDIAN сама переупорядочит их в порядке возрастания: <1, 2, 3, 4 , 5, 8, 10, 12, 14>, а затем верните 5 th значение, т. е. 5, вместо 14 в качестве вывода.

Вывод:

Как использовать функцию МЕДИАНА в Excel?

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

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

Пример # 1

Предположим, у вас есть данные о продажах различных продуктов вашей компании. Данные приведены в ячейке B4: B17.

Теперь вы хотите рассчитать средние продажи. Для этого вы можете использовать формулу MEDIAN в excel:

и нажмите ввод. Он вернет медианное значение.

Пример # 2

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

Чтобы рассчитать среднюю высоту, вы можете использовать формулу MEDIAN в excel:

Пример # 3

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

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

Чтобы рассчитать средние цены на август, вы можете использовать формулу MEDIAN в Excel:

и нажмите Enter. Это даст медианное значение для августа, то есть 82,42.

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

Он вернет 82,365

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

= ИНДЕКС (F4: F5; ПОИСКПОЗ (МАКС (G4: G5); G4: G5; 0))

который вернет Aug.

Пример # 4

Предположим, у вас есть оценки, полученные учащимися класса. В ячейке D4 выставлены оценки: D23.

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

Для этого вы можете использовать следующую формулу МЕДИАНЫ:

= ЕСЛИ (D4> = МЕДИАНА ($ D $ 4: $ D $ 23), «Выше среднего», «Ниже среднего»)

и нажмите Enter. Он вернет производительность для 1 ул студент.

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

Давайте подробно рассмотрим формулу MEDIAN в Excel.

МЕДИАНА ($ D $ 4: $ D $ 23) вычислит среднее значение оценок, полученных учащимися. Здесь медиана 74,4.

ЕСЛИ (D4> = МЕДИАНА ($ D $ 4: $ D $ 23), «Выше среднего», «Ниже среднего») означает, что если оценки, полученные учащимся, выше медианы, он вернет Выше среднего, в противном случае — возврат ниже среднего.

Пример # 5

Предположим, у вас есть ежемесячная зарплата отдела вашей компании. Заработная плата указана в ячейке B4: B13.

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

Для этого вы можете использовать формулу MEDIAN в excel:

= ЕСЛИ (СТАНДОТКЛОН (C4: C13)> (СРЕДНИЙ (C4: C13) / 3), МЕДИАНА (C4: C13); СРЕДНИЙ (C4: C13))

В этом случае стандартное отклонение составляет 29959, а среднее значение — 28300, что ясно показывает, что данные имеют очень высокое стандартное отклонение. Стандартное отклонение в этом случае больше одной трети среднего; таким образом, он сообщит о среднем значении, то есть 15000.