Как найти медианное значение в 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:

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

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

Примечания 2:

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

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

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

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

Итак, медиана представляет собой число, которое выражает середину множества определенного ряда чисел. Таким образом, половина чисел указанного ряда будет больше значения вычисленной медианы, а вторая половина числового ряда – меньше. Для того чтобы определить медиану, нужно отсортировать числовые значения выборки. В итоге медианой выборки цифр 2, 2, 3, 5, 6, 7, 8 будет 5. В данном случае выборка состоит из семи значений – три цифры больше полученной медианы и три – меньше.

Важно! Если выборка состоит четного количества целых цифровых значений, то медиана будет представлена в виде десятичной дроби. Так, медиана выборки ряда 2, 2, 3, 5, 6, 7, 8, 10 составляет 5,5.

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

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

Обратите внимание! Медиана – это не то же самое, что среднее значение. Средний показатель выборки чисел 2, 2, 3, 5, 6, 7, 300 соответствует значению 46,42857, медиана при этом все еще равна 5, так как соотношение чисел больше 5 и меньше не изменилось.

Теперь рассмотрим расчет медианы в случае непрерывного распределения. Показатель медианы – это результат решения функции распределения случайных непрерывных числовых величин. Если показатель функции распределения (т.е. функция плотности) известен, то значение медианы можно вычислить по такой формуле:

При решении данного уравнения аналитическим методом при логнормальном распределении LnN (µ; σ; 2), значение медианы модно вычислить благодаря формуле =EXP (µ). При условии, что µ=0, медиана будет равна 1. В программе Excel результат медианы для логнормального распределения, представленная формулой LnN (0; 1) вычисляется через функцию =ЛОГНОРМ.ОБР (0,5; 0; 1).

Применяя функцию МЕДИАНА в Эксель, можно вычислить значение середины числового множества. Синтаксическое выражение представляет собой формулу =МЕДИАНА (число 1; [число 2]…). Аргументы «Число 1», «Число 2» и так далее до «Число 255». Первое значение – это обязательное значение, последующие – необязательные, но именно они помогают вычислить медиану.

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

Среднее значение ряда чисел

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

1. Медиана – среднее значение числового ряда. При этом половина чисел больше показателя медианы, а вторая половина – меньше. Например, медиана числового ряда 2, 2, 3, 5, 6, 7, 9 равняется 5.
2. Среднее значение – это среднеарифметический показатель, который вычисляется суммированием всех чисел определенного ряда и деления этой суммы на количество показателей, то есть 2+2+3+5+6+7+9=34/7. В данном случае среднее значение соответствует показателю 4,857143.
3. Показатель Режим – это выделение числа, которое чаще других встречается в числовом ряде. В представленном ряду чисел 2, 2, 3, 5, 6, 7, 9 этот показатель соответствует цифре 2.

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

Вычисление значений непрерывного ряда

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

1. Следует активировать ячейку справа или ниже от ряда или строки, в котором представлены значения.
2. На «Главной» вкладке выбираем параметр «Автосумма», который можно найти в группе инструментов «Редактирование».
3. Также данную функцию можно обнаружить во вкладке «Формулы».
4. Рядом с кнопкой «Автосумма», которая обозначается значком Σ, есть стрелочка с встроенным меню. Выбираем показатель «Среднее». Данное значение также можно получить, выполнив функцию СРЗНАЧ.

Среднее взвешенное значение

Здесь случае придется использовать несколько функций – СУММ и СУММПРОИЗВ. Попробуем рассчитать среднюю стоимость единицы товара при наличии трех товаров и количества проведенных продаж. Синтаксис формулы выглядит таким образом: =СУММПРОИЗВ (R[-3]C : R[-3]C[1] : R[-1]C[1]) / СУММ (R[-3]C[1] : R[-1]C[1]). После выполнения данной функции получим среднее значение стоимости одной единицы товара 184,5238095

Вычисление без учета нулевых значений

Чтобы в процессе вычислений не учитывались нулевые значения, необходимо воспользоваться двумя функциями – ЕСЛИ и СРЗНАЧ. Рассмотрим пример. В представленном числовом ряду 4, 6, 8, 0, 5, 8 необходимо найти среднее значение, но с условием исключения нулевого значения. Функция будет иметь такой синтаксис: =СРЗНАЧЕСЛИ (А2:А7; “<>0”). В итоге результат функции без нуля будет равен 6,2.

Полезные видео

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