что такое медиана в экселе
Медиана в статистике
Центральную тенденцию данных можно рассматривать не только, как значение с нулевым суммарным отклонением (среднее арифметическое) или максимальную частоту (мода), но и как некоторую отметку (значение в совокупности), делящую ранжированные данные (отсортированные по возрастанию или убыванию) на две равные части. Половина исходных данных меньше этой отметки, а половина – больше. Это и есть медиана.
Итак, медиана в статистике – это уровень показателя, который делит набор данных на две равные половины. Значения в одной половине меньше, а в другой больше медианы. В качестве примера обратимся к набору нормально распределенных случайных чисел.
Очевидно, что при симметричном распределении середина, делящая совокупность пополам, будет находиться в самом центре – там же, где средняя арифметическая (и мода). Это, так сказать, идеальная ситуация, когда мода, медиана и средняя арифметическая совпадают и все их свойства приходятся на одну точку – максимальная частота, деление пополам, нулевая сумма отклонений – все в одном месте. Однако, жизнь не так симметрична, как нормальное распределение.
Допустим, мы имеем дело с техническими замерами отклонений от ожидаемой величины чего-нибудь (содержания элементов, расстояния, уровня, массы и т.д. и т.п.). Если все ОК, то отклонения, скорее всего, будут распределены по закону, близкому к нормальному, примерно, как на рисунке выше. Но если в процессе присутствует важный и неконтролируемый фактор, то могут появиться аномальные значения, которые в значительной мере повлияют на среднюю арифметическую, но при этом почти не затронут медиану.
Медиана выборки – это альтернатива средней арифметической, т.к. она устойчива к аномальным отклонениям (выбросам).
Математическим свойством медианы является то, что сумма абсолютных (по модулю) отклонений от медианного значения дает минимально возможное значение, если сравнивать с отклонениями от любой другой величины. Даже меньше, чем от средней арифметической, о как! Данный факт находит свое применение, например, при решении транспортных задач, когда нужно рассчитать место строительства объектов около дороги таким образом, чтобы суммарная длина рейсов до него из разных мест была минимальной (остановки, заправки, склады и т.д. и т.п.).
Формула медианы
Формула медианы в статистике для дискретных данных чем-то напоминает формулу моды. А именно тем, что формулы как таковой нет. Медианное значение выбирают из имеющихся данных и только, если это невозможно, проводят несложный расчет.
Первым делом данные ранжируют (сортируют по убыванию). Далее есть два варианта. Если количество значений нечетно, то медиана будет соответствовать центральному значению ряда, номер которого можно определить по формуле:
№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
history 23 октября 2016 г.
Начнем с медианы для выборок (т.е. для фиксированного набора значений).
Медиана выборки
Для вычисления медианы необходимо сначала отсортировать множество чисел (значения в выборке ). Например, медианой для выборки (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
Медиана представляет собой значение, отделяющее верхнюю половину от нижней половины выборки данных. В Microsoft Excel есть функция, позволяющая легко вычислить медианное значение. Посмотрим, как это сделать расчет медианы в Excel.
Расчет медианы в Excel
Медианную функцию в Excel можно отнести к категории статистических функций. Его можно ввести как часть формулы в ячейку рабочего листа. Синтаксис функции МЕДИАНА следующий:
Где число1, число2,… — числовые значения, для которых вы хотите вычислить медиану. Это могут быть числа, именованные диапазоны или ссылки на ячейки, содержащие числа. Number1 является обязательным, последующие числа необязательны.
Чтобы использовать функцию МЕДИАНА в качестве функции рабочего листа в Microsoft Excel:
1]Введите значения в ячейки
Предположим, вы хотите найти медиану чисел в ячейках. D2: D8. Просто введите значение данных, открыв пустой лист Excel и выполнив следующие действия:
Сделайте один столбец «Средние ежемесячные расходы» и соседний столбец «Сумма».
Введите описание в столбец 1 и соответствующее ему значение или сумму в столбце 2.
2]Используйте формулу для вычисления значения
Теперь, чтобы рассчитать средний клик внутри любой ячейки и использовать такую простую формулу:
Вам не нужно беспокоиться о ячейках с текстом, поскольку пустые ячейки, а также ячейки, содержащие текст и логические значения, игнорируются.
Примечание: Ячейки с нулевыми значениями (0) включаются в вычисления.
В самых последних версиях Microsoft Excel функция МЕДИАНА принимает до 255 аргументов.
МЕДИАНА Функция Excel
Медианная функция в Excel
Функция медианы в Excel дает медиану любого заданного набора чисел и относится к категории статистических функций. Медиана любого заданного числа — это число в середине набора. МЕДИАНА измеряет центральную тенденцию, которая является местоположением центра группы чисел в статистическом распределении.
Формула МЕДИАНА в Excel
Ниже приведена формула МЕДИАНА в excel.
Аргументы, используемые для формулы MEDIAN:
число_1, число_2,…, число_n: набор чисел или ссылок на ячейки, обозначающих числовые значения, для которых должна быть вычислена медиана.
Необходимо указать хотя бы один номер. Последующие числа указывать необязательно. В медианной функции можно указать максимум 255 чисел. Входными данными могут быть числа, имена, массивы или ссылки на ячейки, содержащие числа. Любые логические значения и текстовые представления чисел, введенные непосредственно в качестве входных данных, также учитываются функцией Median.
Вывод:
МЕДИАНА вычисляет медиану заданного набора чисел. Ровно половина входных чисел имеет значения больше медианы, а половина чисел имеет значения меньше медианы. Если число входов четное, функция МЕДИАНА вычисляет среднее значение двух чисел в середине. Предположим, что в качестве входных данных задано всего шесть чисел, тогда МЕДИАНА вернет среднее из трех rd и 4 th числа. Функция МЕДИАНА сначала переставляет входные числовые значения в порядке возрастания, а затем определяет среднее значение.
Иллюстрация
Предположим, вы хотите найти медиану чисел <2, 3, 4, 5, 6>. Эти числа указаны в ячейке B3: B7.
Чтобы вычислить медианную функцию, вы можете использовать следующую формулу MEDIAN:
Формула MEDIAN вернет среднее значение, то есть 4.
Вместо ссылок на ячейки вы можете напрямую указать входные значения как:
Эта формула MEDIAN в Excel вернет тот же результат.
Если вы используете в качестве аргумента четное число значений, например <2, 3, 4, 5, 6, 7>, формула МЕДИАНА выведет среднее из двух средних значений — 4 и 5.
Вывод:
Как использовать функцию МЕДИАНА в Excel?
Функция МЕДИАНА Excel очень проста и удобна в использовании. Давайте разберемся с работой функции МЕДИАНА на некоторых примерах.
Функция «Медиана» в Excel дает меру центральной тенденции или среднего значения и наиболее подходит, когда данные искажены или содержат исключительно высокие или низкие значения. МЕДИАНА — наиболее подходящая мера для данных, классифицированных по порядковой шкале. Функция МЕДИАНА в Excel может использоваться для определения медианы продаж, доходов или расходов.
Пример # 1
Предположим, у вас есть данные о продажах различных продуктов вашей компании. Данные приведены в ячейке B4: B17.
Теперь вы хотите рассчитать средние продажи. Для этого вы можете использовать формулу MEDIAN в excel:
и нажмите ввод. Он вернет медианное значение.
Пример # 2
Предположим, в ячейке C4: C15 указан рост 12 учеников. Вы хотите рассчитать средний рост учеников.
Чтобы рассчитать среднюю высоту, вы можете использовать формулу MEDIAN в excel:
Пример # 3
Предположим, у вас есть цены на бензин в разных городах страны за два разных месяца, как показано ниже.
Теперь вы хотите рассчитать средние цены на бензин для каждого месяца, а затем сравнить цены на основе их медианных значений.
Чтобы рассчитать средние цены на август, вы можете использовать формулу MEDIAN в Excel:
и нажмите Enter. Это даст медианное значение для августа, то есть 82,42.
Точно так же вы можете найти медиану для сентября, используя формулу MEDIAN в excel:
Теперь, чтобы узнать, в каком месяце было большее медианное значение, вы можете использовать индекс:
= ИНДЕКС (F4: F5; ПОИСКПОЗ (МАКС (G4: G5); G4: G5; 0))
который вернет Aug.
Пример # 4
Предположим, у вас есть оценки, полученные учащимися класса. В ячейке D4 выставлены оценки: D23.
Теперь вы хотите сравнить оценки с полученными средними оценками. Если полученные оценки выше среднего, учащийся будет считаться выше среднего, в противном случае учащийся будет считаться ниже среднего.
Для этого вы можете использовать следующую формулу МЕДИАНЫ:
и нажмите Enter. Он вернет производительность для 1 ул студент.
Теперь вы можете просто перетащить его к остальным ученикам, чтобы увидеть результаты каждого из учеников.
Давайте подробно рассмотрим формулу MEDIAN в Excel.
Пример # 5
Предположим, у вас есть ежемесячная зарплата отдела вашей компании. Заработная плата указана в ячейке B4: B13.
Теперь вам интересно вычислить центральную тенденцию данной зарплаты. Вы решили, что если стандартное отклонение больше одной трети среднего, вы рассчитаете медиану; в противном случае вы рассчитаете среднее значение.
Для этого вы можете использовать формулу MEDIAN в excel:
= ЕСЛИ (СТАНДОТКЛОН (C4: C13)> (СРЕДНИЙ (C4: C13) / 3), МЕДИАНА (C4: C13); СРЕДНИЙ (C4: C13))
В этом случае стандартное отклонение составляет 29959, а среднее значение — 28300, что ясно показывает, что данные имеют очень высокое стандартное отклонение. Стандартное отклонение в этом случае больше одной трети среднего; таким образом, он сообщит о среднем значении, то есть 15000.
Как в 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». Первое значение – это обязательное значение, последующие – необязательные, но именно они помогают вычислить медиану.
Следует отметить! В качестве аргументов могут использоваться не только числовые значение, но и ссылки, имена, массивы. Если ссылка или массив частично представлены в виде текста или в выборке есть пустые ячейки, то эти значения не учитываются в процессе вычислений. Использование текстового формата в аргументах приводят к ошибкам в работе функции.
Среднее значение ряда чисел
Рассмотрим вариант расчета средних значений определенного ряда чисел. Например, это может быть средняя температура воздуха в конкретный день года, выполнение поставленных задач сотрудниками и так далее. Для выполнения данного вычисления потребуются три важных параметра:
Указанные параметры при симметричном распределении числового ряда своих значений не меняют, а при асимметричном распределении они могут меняться.
Вычисление значений непрерывного ряда
При определении среднего значения непрерывного числового ряда необходимо осуществить определенный порядок действия:
Среднее взвешенное значение
Здесь случае придется использовать несколько функций – СУММ и СУММПРОИЗВ. Попробуем рассчитать среднюю стоимость единицы товара при наличии трех товаров и количества проведенных продаж. Синтаксис формулы выглядит таким образом: =СУММПРОИЗВ (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