Как посчитать медиану в excel
Формула МЕДИАНА в Excel
Ниже приведена формула МЕДИАНА в excel.
Аргументы, используемые для формулы MEDIAN:
число_1, число_2,…, число_n: набор чисел или ссылок на ячейки, обозначающих числовые значения, для которых должна быть вычислена медиана.
Вывод:
МЕДИАНА вычисляет медиану заданного набора чисел. Ровно половина входных чисел имеет значения больше медианы, а половина чисел имеет значения меньше медианы. Если число входов четное, функция МЕДИАНА вычисляет среднее значение двух чисел в середине. Предположим, что в качестве входных данных задано всего шесть чисел, тогда МЕДИАНА вернет среднее из трех rd и 4 th числа. Функция МЕДИАНА сначала переставляет входные числовые значения в порядке возрастания, а затем определяет среднее значение.
Иллюстрация
Предположим, вы хотите найти медиану чисел . Эти числа указаны в ячейке B3: B7.
Чтобы вычислить медианную функцию, вы можете использовать следующую формулу MEDIAN:
Формула MEDIAN вернет среднее значение, то есть 4.
Вместо ссылок на ячейки вы можете напрямую указать входные значения как:
= МЕДИАНА (2, 3, 4, 5, 6)
Эта формула MEDIAN в Excel вернет тот же результат.
что составляет 4,5.
Предположим, вы даете девять аргументов , которые не расположены в порядке возрастания, MEDIAN сама переупорядочит их в порядке возрастания: , а затем верните 5 th значение, т. е. 5, вместо 14 в качестве вывода.
Вывод:
Как использовать функцию МЕДИАНА в Excel?
Функция МЕДИАНА Excel очень проста и удобна в использовании. Давайте разберемся с работой функции МЕДИАНА на некоторых примерах.
Предположим, у вас есть данные о продажах различных продуктов вашей компании. Данные приведены в ячейке B4: B17.
Теперь вы хотите рассчитать средние продажи. Для этого вы можете использовать формулу MEDIAN в excel:
и нажмите ввод. Он вернет медианное значение.
Предположим, в ячейке C4: C15 указан рост 12 учеников. Вы хотите рассчитать средний рост учеников.
Чтобы рассчитать среднюю высоту, вы можете использовать формулу MEDIAN в excel:
Предположим, у вас есть цены на бензин в разных городах страны за два разных месяца, как показано ниже.
Теперь вы хотите рассчитать средние цены на бензин для каждого месяца, а затем сравнить цены на основе их медианных значений.
Чтобы рассчитать средние цены на август, вы можете использовать формулу MEDIAN в Excel:
и нажмите Enter. Это даст медианное значение для августа, то есть 82,42.
Точно так же вы можете найти медиану для сентября, используя формулу MEDIAN в excel:
Он вернет 82,365
Теперь, чтобы узнать, в каком месяце было большее медианное значение, вы можете использовать индекс:
= ИНДЕКС (F4: F5; ПОИСКПОЗ (МАКС (G4: G5); G4: G5; 0))
который вернет Aug.
Предположим, у вас есть оценки, полученные учащимися класса. В ячейке D4 выставлены оценки: D23.
Теперь вы хотите сравнить оценки с полученными средними оценками. Если полученные оценки выше среднего, учащийся будет считаться выше среднего, в противном случае учащийся будет считаться ниже среднего.
Для этого вы можете использовать следующую формулу МЕДИАНЫ:
= ЕСЛИ (D4> = МЕДИАНА ($ D $ 4: $ D $ 23), «Выше среднего», «Ниже среднего»)
и нажмите Enter. Он вернет производительность для 1 ул студент.
Теперь вы можете просто перетащить его к остальным ученикам, чтобы увидеть результаты каждого из учеников.
Давайте подробно рассмотрим формулу MEDIAN в Excel.
МЕДИАНА ($ D $ 4: $ D $ 23) вычислит среднее значение оценок, полученных учащимися. Здесь медиана 74,4.
Предположим, у вас есть ежемесячная зарплата отдела вашей компании. Заработная плата указана в ячейке B4: B13.
Теперь вам интересно вычислить центральную тенденцию данной зарплаты. Вы решили, что если стандартное отклонение больше одной трети среднего, вы рассчитаете медиану; в противном случае вы рассчитаете среднее значение.
Для этого вы можете использовать формулу MEDIAN в excel:
= ЕСЛИ (СТАНДОТКЛОН (C4: C13)> (СРЕДНИЙ (C4: C13) / 3), МЕДИАНА (C4: C13); СРЕДНИЙ (C4: C13))
Функция КВАРТИЛЬ в Excel используется для расчета квартиля диапазона числовых данных и возвращает соответствующее числовое значение.
Функция КВАРТИЛЬ.ВКЛ вычисляет на основе указанной процентили в качестве второго аргумента функции. Полностью соответствует первой функции. Последняя используется в Excel 2007 и более ранних версиях и оставлена для совместимости.
Функция КВАРТИЛЬ.ИСКЛ используется для расчета квартили диапазона числовых значений на основе известной процентили, за исключением граничных значений (минимального и максимального значения в диапазоне).
Квартили используются для распределения диапазона чисел на четыре равные части:
- Первый квартиль является числом из диапазона исследуемых значений, которое делит данный диапазон на две части так, что около 25% данного диапазона являются числами, которые меньше первого квартиля, а остальные (75%) – больше. Рассматриваемые функции могут возвращать результат интерполяции двух соседних значений из диапазона.
- Второй квартиль эквивалентен медиане выборки (исследуемого числового диапазона), то есть числовому значению, которое делит диапазон на две части: 50% чисел меньше медианы, остальные 50% чисел больше медианы. Так, запись =КВАРТИЛЬ.ВКЛ(A1:A10;2) возвращает значение, эквивалентное результату вычисления функции =МЕДИАНА(A1:A10), при условии, что ячейки из диапазона A1:A10 содержат числовые значения.
- Третий квартиль – числовое значение, делящее диапазон на две части, в первой из которой содержатся 75% чисел диапазона, которые меньше полученного значения, а во второй (25%) – больше.
Функция КВАРТИЛЬ.ВКЛ может быть использована не только для определения медианы выборки (второго квартиля), а и нахождения минимального и максимального значений соответственно. При работе с большими диапазонами чисел для подобных расчетов рекомендуется использовать функции МИН и МАКС соответственно.
Существует несколько алгоритмов расчета квартилей. Все рассмотренные функции используют следующую формулу:
- Qp – p-й квантиль (является частным случаем квантиля);
- x – индекс квантиля;
- i – индекс элемента из выборки;
- A1,A2…Ai – элементы выборки, отсортированной по возрастанию значений.
Для расчета индекса квантиля (x) функция КВАРТИЛЬ.ВКЛ используют формулу:
x=(n-1)p, где n – количество элементов в диапазоне.
Функция КВАРТИЛЬ.ИСКЛ использует формулу x=(n+1)p.
В Excel принято так, что первые выше указанные 2 функции используют метод N-1-интерполяцию, а третья функция – N+1-интерполяцию.
Примеры использования функций КВАРТИЛЬ в Excel
Пример 1. В столбце таблицы содержится числовая последовательность. Определить число, которое делит последовательность на 2 части, 25% первой – числа меньше полученного значения, а 75% - больше. Использовать N+1-интерполяцию.
Вид таблицы данных:
Для определения 1-го квартиля используем функцию:
- A2:A15 – диапазон ячеек с исследуемыми числами;
- 1 – номер вычисляемого квартиля.
Проверим утверждение о том, что второй квартиль соответствует медиане выборке. Определим 2-й по формуле:
Полученные значения совпадают:
В результате расчетов мы получили первый, второй квартили и медиану для исходного диапазона чисел.
Статистический анализ роста доли дохода в Excel за период
Пример 2. В таблице приведены данные о доходах предпринимателя за год. Доказать, что примерно 75% значений меньше, чем третий квартиль доходов.
Вид исходной таблицы:
Определим 3-й по формуле:
Определим соотношение чисел, меньше полученного числа, к общему количеству значений по формуле:
Анализ статистики случайно сгенерированных чисел в Excel
Пример 3. Имеется диапазон случайных чисел, отсортированный в порядке возрастания. Определить соотношение суммы чисел, которые меньше 1-го квартиля, к сумме чисел, которые превышают значение 1-го квартиля.
Чтобы сгенерировать случайное число в Excel воспользуемся функцией:
После генерации отсортируем случайно сгенерированные числа по возрастанию. Вид исходной таблицы данных со случайными числами:
Формула для расчета имеет следующий вид (формула массива CTRL+SHIFT+ENTER):
Функции СУММ с вложенными функциями ЕСЛИ выполняют расчет суммы только тех чисел, которые меньше и больше соответственно значения, возвращаемого функцией для исследуемого диапазона. Из полученных значений вычисляется частное. Результат расчетов:
Общая сумма чисел исследуемого диапазона, которые меньше 1-го квартиля, составляет всего 8,57% от общей суммы чисел, которые больше 1-го квартиля.
В этой статье описаны синтаксис формулы и использование функции МЕДИАНА в Microsoft Excel.
Описание
Возвращает медиану заданных чисел. Медиана — это число, которое является серединой множества чисел.
Синтаксис
Аргументы функции МЕДИАНА описаны ниже.
Число1, число2. Аргумент "число1" является обязательным, последующие числа необязательные. От 1 до 255 чисел, для которых требуется определить медиану.
Замечания
Если в наборе имеется ряду чисел, медиана вычисляет среднее значение двух чисел в середине. См. вторую формулу в примере.
Аргументы могут быть либо числами, либо содержащими числа именами, массивами или ссылками.
Учитываются логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов.
Если аргумент, который является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, то такие значения пропускаются; однако ячейки, которые содержат нулевые значения, учитываются.
Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, приводят в возникновению ошибок.
Примечание: Функция МЕДИАНА измеряет центральную тенденцию, которая является центром множества чисел в статистическом распределении. Существует три наиболее распространенных способа определения центральной тенденции:
Среднее значение — это среднее арифметическое, которое вычисляется путем сложения набора чисел с последующим делением полученной суммы на их количество. Например, средним значением для чисел 2, 3, 3, 5, 7 и 10 будет 5, которое является результатом деления их суммы, равной 30, на их количество, равное 6.
Медиана — это число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана. Например, медианой для чисел 2, 3, 3, 5, 7 и 10 будет 4.
Мода — это число, наиболее часто встречающееся в данном наборе чисел. Например, модой для чисел 2, 3, 3, 5, 7 и 10 будет 3.
При симметричном распределении множества чисел все три значения центральной тенденции будут совпадать. При смещенном распределении множества чисел значения могут быть разными.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Для вычисления медианы в 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 существуют специальные функции ( нормальное распределение , гамма-распределение , Экспоненциальное и др.). Используя эти функции можно вычислить медиану соответствующего распределения.
Читайте также: