Как в экселе посчитать межквартильный размах
Обновлено: 04.07.2024
– Приведите примеры непрерывных случайных величин (рост дерева), дискретных случайных величин (количество учеников в классе).
– Какие статистические характеристики случайных величин мы знаем (мода, медиана, среднее выборочное значение, размах ряда).
– Какие приемы используются для наглядного представления статистических характеристик случайной величины (полигон частот, круговые и столбчатые диаграммы, гистограммы).
- Рассмотрим, применение инструментов Excel для решения статистических задач на конкретном примере.
Пример. Проведена проверка в 100 компаниях. Даны значения количества работающих в компании (чел.):
- моду
- медиану
- размах ряда
- построить полигон частот
- построить столбчатую и круговую диаграммы
- раскрыть смысловую сторону каждой характеристики
1. Занести данные в EXCEL, каждое число в отдельную ячейку.
| 23 | 25 | 24 | 25 | 30 | 24 | 30 | 26 | 28 | 26 |
| 32 | 33 | 31 | 31 | 25 | 33 | 25 | 29 | 30 | 28 |
| 23 | 30 | 29 | 24 | 33 | 30 | 30 | 28 | 26 | 25 |
| 26 | 29 | 27 | 29 | 26 | 28 | 27 | 26 | 29 | 28 |
| 29 | 30 | 27 | 30 | 28 | 32 | 28 | 26 | 30 | 26 |
| 31 | 27 | 30 | 27 | 33 | 28 | 26 | 30 | 31 | 29 |
| 27 | 30 | 30 | 29 | 27 | 26 | 28 | 31 | 29 | 28 |
| 33 | 27 | 30 | 33 | 26 | 31 | 34 | 28 | 32 | 22 |
| 29 | 30 | 27 | 29 | 34 | 29 | 32 | 29 | 29 | 30 |
| 29 | 29 | 36 | 29 | 29 | 34 | 23 | 28 | 24 | 28 |
2. Для расчета числовых характеристик используем опцию Вставка – Функция. И в появившемся окне в строке категория выберем - статистические, в списке: МОДА
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили Мо = 29 (чел) – Фирм у которых в штате 29 человек больше всего.
Используя тот же путь вычисляем медиану.
Вставка – Функция – Статистические – Медиана.
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили Ме = 29 (чел) – среднее значение сотрудников в фирме.
Размах ряда чисел – разница между наименьшим и наибольшим возможным значением случайной величины. Для вычисления размаха ряда нужно найти наибольшее и наименьшее значения нашей выборки и вычислить их разность.
Вставка – Функция – Статистические – МАКС.
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили наибольшее значение = 36.
Вставка – Функция – Статистические – МИН.
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили наименьшее значение = 22.
36 – 22 = 14 (чел) – разница между фирмой с наибольшим штатом сотрудников и фирмой с наименьшим штатом сотрудников.
Для построения диаграммы и полигона частот необходимо задать закон распределения, т.е. составить таблицу значений случайной величины и соответствующих им частот. Мы ухе знаем, что наименьшее число сотрудников в фирме = 22, а наибольшее = 36. Составим таблицу, в которой значения xi случайной величины меняются от 22 до 36 включительно шагом 1.
| xi | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 |
| ni |
Чтобы сосчитать частоту каждого значения воспользуемся
Вставка – Функция – Статистические – СЧЕТЕСЛИ.
В окне Диапазон ставим курсор и выделяем нашу выборку, а в окне Критерий ставим число 22
Нажимаем клавишу ОК, получаем значение 1, т.е. число 22 в нашей выборке встречается 1 раз и его частота =1. Аналогичным образом заполняем всю таблицу.
| xi | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 |
| ni | 1 | 3 | 4 | 5 | 11 | 9 | 13 | 18 | 16 | 6 | 4 | 6 | 3 | 0 | 1 |
Для проверки вычисляем объем выборки, сумму частот (Вставка – Функция – Математические - СУММА). Должно получиться 100 (количество всех фирм).
Чтобы построить полигон частот выделяем таблицу – Вставка – Диаграмма – Стандартные – Точечная (точечная диаграмма на которой значения соединены отрезками)
Нажимаем клавишу Далее, в Мастере диаграмм указываем название диаграммы (Полигон частот), удаляем легенду, редактируем шкалу и характеристики диаграммы для наибольшей наглядности.
Для построения столбчатой и круговой диаграмм используем тот же путь (выбирая нужный нам тип диаграммы).
Диаграмма – Стандартные – Круговая.
Диаграмма – Стандартные – Гистограмма.
4. Сегодня на уроке мы научились применять компьютерные технологии для анализа и обработки статистической информации.
В Excel функция КВАРТИЛЬ используется для разделения данных на равные доли. Также еще часто используют эту функцию для поиска отстающих показателей, то есть существенно отличающихся от остальных значений в исходных данных.
Пример расчета межквартильного диапазона для статистического анализа в Excel
Ниже на рисунке представлен другой список работников с показателями производственных браков на 1000 шт. выпущенной продукции. Допустим нам необходимо узнать, какие работники делают большое и малое количество браков, существенно выходящее за пределы допустимой нормы (отстающие и превышающие ее - так называемые выборсы от медианы), чтобы потом проанализировать их. С целью поиска аномальных отклонений от показателей нормы в данном примере будет использован метод расширенного межквартильного диапазона. Межквартильный диапазон – это просто данные лежащие в среднем диапазоне, который охватывает 50% всего объема данных (находящийся между 75% и 25%). Определение «расширенный» значит, что средний диапазон данных может быть расширен с учетом определенного коэффициента, определяющего его границы. Все значения, лежащие вне границ, воспринимаются как показатели выборсы:
$F$7;"Высоко";""))' >
Для определения значения в среднем диапазоне между 75% и 25% следует воспользоваться функцией КВАРТИЛЬ.ИСКЛ вместе с указанными аргументами 3 и 1 – соответственно. Межквартильным диапазоном является разницей между этими значениями.
В случае нерасширенного межквартильного диапазона с целью определения его нижней границы достаточно всего лишь вычитать значение диапазона от 25%. А для верхней границы, нужно добавить его до 75%. Результатом применения данного метода могло бы получиться слишком большое число для найденных показателей выбросов. Умножая межквартильный диапазон на расширяющий коэффициент (в данном примере равен 1,5) расширяются границы. Таким образом, можно выбрать только особенно экстремальные значения.
Схема вычисления межквартильного диапазона в Excel
Ниже на рисунке представленные те же данные, что и в предыдущем примере, отсортированы по столбцу с показателями количества браков на 1000 шт. готовой продукции. Также для наглядности линиями наложены границы расширенного диапазона четверти и верхние с нижними границами остальных диапазонов четверти:
Чтобы определить верхнюю границу диапазона четверти, необходимо умножить расширяющий коэффициент на диапазон четверти и добавить его результат к 75%.
Чтобы определить нижнюю границу необходимо от 25% вычитать результат, полученный после умножения диапазона на коэффициент.
Может оказаться так, что расширяющий коэффициент равен 1,5 привел к исключению значения, которое казались отстающими или были выбраны значения, которые казались нормальными. В этом нет ничего особенного. Просто увеличьте или уменьшите расширяющий коэффициент, если его текущее значение не согласуются с Вашими исходными данными.
После определения границ используйте формулу со вложенными функциями ЕСЛИ с целью проверки: является ли данное значение большим чем верхнее или ниже от нижнего граничного значения. В случае значительных отклонений показателей (выбросов) от нормы формула со вложенными функциями ЕСЛИ возвращает слово «Выше» или «Ниже», а в случае значения лежащего внутри границ формула возвращает пустую строку ("").
Быстрый пример
Как найти выбросы в ваших данных
Чтобы найти выбросы в наборе данных, мы используем следующие шаги:
Вычислите 1-й и 3-й квартили (мы немного поговорим о том, что это такое).
Оцените межквартильный размах (мы также объясним это немного ниже).
Верните верхнюю и нижнюю границы нашего диапазона данных.
Используйте эти границы для определения отдаленных точек данных.
Диапазон ячеек справа от набора данных, показанного на изображении ниже, будет использоваться для хранения этих значений.
Шаг 1. Рассчитайте квартили
Excel предоставляет функцию КВАРТИЛЬ для расчета квартилей. Для этого требуются две части информации: массив и кварта.
Примечание. В Excel 2010 Microsoft выпустила функции QUARTILE.INC и QUARTILE.EXC как усовершенствования функции QUARTILE. QUARTILE более обратно совместима при работе с несколькими версиями Excel.
Вернемся к нашему примеру таблицы.
Для вычисления 1-го квартиля мы можем использовать следующую формулу в ячейке F2.
Когда вы вводите формулу, Excel предоставляет список параметров для аргумента кварты.
Чтобы вычислить 3-й квартиль, мы можем ввести формулу, аналогичную предыдущей, в ячейку F3, но используя тройку вместо единицы.
Теперь у нас есть точки данных квартилей, отображаемые в ячейках.
Шаг второй: оцените межквартильный размах
Мы собираемся использовать простую формулу в ячейке F4, которая вычитает 1-й квартиль из 3-го квартиля:
Теперь мы можем видеть наш межквартильный размах.
Шаг третий: верните нижнюю и верхнюю границы
Мы рассчитаем нижний предел в ячейке F5, умножив значение IQR на 1,5, а затем вычтя его из точки данных Q1:
Примечание. Скобки в этой формуле не нужны, потому что часть умножения будет вычисляться перед частью вычитания, но они облегчают чтение формулы.
Чтобы вычислить верхнюю границу в ячейке F6, мы снова умножим IQR на 1,5, но на этот раз добавим его к точке данных Q3:
Шаг четвертый: выявление выбросов
Мы будем использовать Функция ИЛИ для выполнения этого логического теста и отображения значений, соответствующих этим критериям, введите следующую формулу в ячейку C2:
Затем мы скопируем это значение в наши ячейки C3-C14. Значение ИСТИНА указывает на выброс, и, как видите, в наших данных их два.
Игнорирование выбросов при вычислении среднего среднего
Использование функции КВАРТИЛЬ позволяет нам рассчитать IQR и работать с наиболее широко используемым определением выброса. Однако при вычислении среднего среднего для диапазона значений и игнорировании выбросов существует более быстрая и простая функция. Этот метод не будет определять выбросы, как раньше, но он позволит нам быть гибкими в выборе того, что мы можем считать своей частью выбросов.
Нужная нам функция называется TRIMMEAN, синтаксис для нее вы можете увидеть ниже:
В нашем примере мы ввели приведенную ниже формулу в ячейку D3, чтобы вычислить среднее значение и исключить 20% выбросов.
Здесь у вас есть две разные функции для обработки выбросов. Независимо от того, хотите ли вы идентифицировать их для каких-либо потребностей в отчетности или исключить их из вычислений, таких как средние значения, в Excel есть функция, соответствующая вашим потребностям.
Момент степени k:
Центральный момент степени k:
Среднее значение
| Кол-во посетителей | 0 | 1 | 2 | 3 | 4 | 5 | 6 |
| Количество наблюдений | 114 | 115 | 52 | 52 | 24 | 13 | 30 |
| Таблица 1. Количество посетителей в час | |||||||
Чтобы найти среднее значение всех результатов необходимо сложить всё вместе и разделить на количество результатов:
μ = (114 • 0 + 115 • 1 + 52 • 2 + 52 • 3 + 24 • 4 + 13 • 5 + 30 • 6) / 400 = 716/400 = 1.79
То же самое мы можем проделать используя формулу 2:
μ = M(X) = Σ(Xi•pi) = 0 • 0.29 + 1 • 0.29 + 2 • 0.13 + 3 • 0.13 + 4 • 0.06 + 5 • 0.03 + 6 • 0.08 = 1.79 Момент первой степени, формула (5)
Собственно, формула 2 представляет собой среднее арифметическое всех значений
Итог: в среднем, 1.79 посетителя в час
| Количество посетителей | 0 | 1 | 2 | 3 | 4 | 5 | 6 |
| Вероятность (%) | 28.5 | 28.8 | 13 | 13 | 6 | 3.3 | 7.5 |
| Таблица 2. Закон распределения количества посетителей | |||||||
Отклонение от среднего
Посмотрите на это распределение, можно предположить, что в среднем случайная величина равна 100±5, поскольку кажется, что таких значений несравнимо больше чем тех, что меньше 95 или больше 105:
График 2. График функции вероятности. Распределение ≈ 100±5
но сумма таких расстояний, а следовательно и любое производное от этого числа, будет равно нулю, поэтому в качестве меры выбрали квадрат разниц между величинами и средним значением:
σ возведена в квадрат, поскольку вместо расстояний мы взяли квадрат расстояний. σ 2 называется дисперсией. Корень из дисперсии называется средним квадратическим отклонением, или среднеквадратическим отклоненим, и его используют в качестве меры разброса:
Возвращаясь к примеру, посчитаем среднеквадратическое отклонение для графика 2:
Квантиль
График 3. Функция распределения. Медиана
График 4. Функция распределения. 4-квантиль или квартиль
График 5. Функция распределения. 0.34-квантиль
То есть, если мы говорим о дециле (10-квантиле), то это означает, что мы разбили график на 10 частей, что соответствует девяти линяям, и для каждого дециля нашли значение случайной величины.
Для дискретного распределения квантиль необходимо выбирать следующим образом: квантиль гарантирует вероятность, поэтому, если рассчитанный квантиль не совпадает с одним и значений, необходимо выбирать меньшее значение.
Построение интервалов
Двусторонний доверительный интервал
Первый квартиль
Значение квартиля Q1 находится в интервале 68,98 – 71,70, соответствующего частоте fQ1 = 150:4 = 37,5
Третий квартиль
Значение квартиля находится в интервале 68,98 – 71,70, соответствующего частоте fQ3 = (3*150):4 = 112,5
Квартили непрерывного распределения
Примечание : Подробнее о Функции распределения см. статью Функция распределения и плотность вероятности в MS EXCEL .
Если известна функция плотности вероятности p (х) , то 1-й квартиль можно найти из уравнения:
Например, решив аналитическим способом это уравнение для Логнормального распределения lnN(μ; σ 2 ), получим, что медиана (2-й квартиль ) вычисляется по формуле e μ или в MS EXCEL =EXP(μ). При μ=1, медиана равна 2,718.
Обратите внимание на точку Функции распределения , для которой F(х)=0,5 (см. картинку выше или файл примера , лист Квартиль-распределение) . Абсцисса этой точки равна 2,718. Это и есть значение 2-го квартиля ( медианы ), что естественно совпадает с ранее вычисленным значением по формуле e μ .
Примечание : Напомним, что интеграл от функции плотности вероятности по всей области задания случайной величины равен единице:
Поэтому, линии квартилей ( х=квартиль ) делят площадь под графиком функции плотности вероятности на 4 равные части.
Квартили в MS EXCEL
Чтобы вычислить в MS EXCEL квартили заданного распределения необходимо использовать соответствующую обратную функцию распределения .
При вычислении квартилей в MS EXCEL используются обратные функции распределения : НОРМ.СТ.ОБР() , ЛОГНОРМ.ОБР() , ХИ2.ОБР() , ГАММА.ОБР() и т.д. Подробнее о распределениях, представленных в MS EXCEL, можно прочитать в статье Распределения случайной величины в MS EXCEL .
Например, в MS EXCEL 1-й квартиль для логнормального распределения LnN(1;1) можно вычислить по формуле =ЛОГНОРМ.ОБР(0,25;1;1) , а 3-й квартиль для стандартного нормального распределения по формуле =НОРМ.СТ.ОБР(0,75) .
Моменты случайной величины
Моменты случайно величины описывают различные аспекты характера и формы нашего распределения.
Как видно на графике, чем выше значение пики, тем выше коэффициент эксцесса, т.е. у верхней кривой коэффициент эксцесса выше, чем у нижней.
Статистический анализ роста доли дохода в Excel за период
Пример 2. В таблице приведены данные о доходах предпринимателя за год. Доказать, что примерно 75% значений меньше, чем третий квартиль доходов.
Вид исходной таблицы:
Определим 3-й по формуле:
Определим соотношение чисел, меньше полученного числа, к общему количеству значений по формуле:
Анализ статистики случайно сгенерированных чисел в Excel
Пример 3. Имеется диапазон случайных чисел, отсортированный в порядке возрастания. Определить соотношение суммы чисел, которые меньше 1-го квартиля, к сумме чисел, которые превышают значение 1-го квартиля.
Чтобы сгенерировать случайное число в Excel воспользуемся функцией:
После генерации отсортируем случайно сгенерированные числа по возрастанию. Вид исходной таблицы данных со случайными числами:
Формула для расчета имеет следующий вид (формула массива CTRL+SHIFT+ENTER):
Функции СУММ с вложенными функциями ЕСЛИ выполняют расчет суммы только тех чисел, которые меньше и больше соответственно значения, возвращаемого функцией для исследуемого диапазона. Из полученных значений вычисляется частное. Результат расчетов:
Общая сумма чисел исследуемого диапазона, которые меньше 1-го квартиля, составляет всего 8,57% от общей суммы чисел, которые больше 1-го квартиля.
Расчет квартилей в R и SAS
Функция quantile в R использует все девять алгоритмов расчета квантилей, в соответствии с нумерацией, предложенной Hyndman and Fan в работе 1996 г. (рис. 15; если вы не знакомы с R, рекомендую начать с Алексей Шипунов. Наглядная статистика. Используем R! ). Квантиль при i-м методе расчета:
где i – номер метода, 1 ≤ i ≤ 9, (j–m)/n ≤ p < (j–m+1)/n, хj – j-ый порядковый элемент упорядоченного ряда, n – размер выборки, γ является функцией двух параметров: j = floor(np + m) и g = np + m – j, где floor – функция возвращающая наибольшее целое, но всё еще меньшее, чем аргумент функции (аналог в Excel – ОКРВНИЗ.МАТ), m – константа, определяемая типом алгоритма расчета квантиля. Если вас интересуют подробности, обратитесь к справочной системе R.
SAS предлгает 5 методов расчета квантилей.
Расчет децилей для дискретного ряда
Определяем номер дециля по формуле: ,
Если номер дециля – целое число, то значение дециля будет равно величине элемента ряда, которое обладает накопленной частотой равной номеру дециля. Например, если номер дециля равен 20, его значение будет равно значению признака с S =20 (накопленной частотой равной 20).
Если номер дециля – нецелое число, то дециль попадает между двумя наблюдениями. Значением дециля будет сумма, состоящая из значения элемента, для которого накопленная частота равна целому значению номера дециля, и указанной части (нецелая часть номера дециля) разности между значением этого элемента и значением следующего элемента.
Например, если номер дециля равна 20,25, дециль попадает между 20-м и 21-м наблюдениями, и его значение будет равно значению 20-го наблюдения плюс 1/4 разности между значением 20-го и 21-го наблюдений.
Квантили специальных видов
Часто используются Квантили специальных видов:
В качестве примера вычислим медиану (0,5-квантиль) логнормального распределения LnN(0;1) (см. файл примера лист Медиана ).
Это можно сделать с помощью формулы =ЛОГНОРМ.ОБР(0,5; 0; 1)
Квантили стандартного нормального распределения
Необходимость в вычислении квантилей стандартного нормального распределения возникает при проверке статистических гипотез и при построении доверительных интервалов.
Примечание : Про проверку статистических гипотез см. статью Проверка статистических гипотез в MS EXCEL . Про построение доверительных интервалов см. статью Доверительные интервалы в MS EXCEL .
В данных задачах часто используется специальная терминология:
-
Нижний квантиль уровняальфа ( α percentage point) файл примера лист Квантили ).
Для α=0,05, нижний 0,05-квантиль стандартного нормального распределения равен -1,645. Вычисления в MS EXCEL можно сделать по формуле:
Действительно, для α=0,05, верхний 0,05-квантиль стандартного нормального распределения равен 1,645. Т.к. функция плотности вероятности стандартного нормального распределения является четной функцией, то вычисления в MS EXCEL верхнего квантиля можно сделать по двум формулам:
Чтобы пояснить название « верхний» квантиль , построим график плотности вероятности и функцию вероятности стандартного нормального распределения для α=0,05.
Выделенная площадь на рисунке соответствует вероятности, что случайная величина примет значение больше верхнего 0,05-квантиля , т.е. больше значения 1,645. Эта вероятность равна 0,05.
Невыделенная площадь на рисунке соответствует вероятности, что случайная величина примет значение между нижним квантилем уровня α /2 и верхним квантилем уровня α /2, т.е. будет между значениями -1,960 и 1,960 при α=0,05. Эта вероятность равна в нашем случае 1-(0,05/2+0,05/2)=0,95. Если Z 0 попадает в одну из выделенных областей, то нулевая гипотеза отклоняется.
Другими словами, двусторонние α-квантили задают интервал, в который рассматриваемая случайная величина попадает с заданной вероятностью α.
Квантили распределения Стьюдента
Аналогичным образом квантили вычисляются и для распределения Стьюдента . Например, вычислять верхний α/2- квантиль распределения Стьюдента с n -1 степенью свободы требуется, если проводится проверка двухсторонней гипотезы о среднем значении распределения при неизвестной дисперсии ( см. эту статью ).
Для верхних квантилей распределения Стьюдента часто используется запись t α/2,n-1 . Если такая запись встретилась в статье про проверку гипотез или про построение доверительного интервала , то это именно верхний квантиль .
Примечание : Функция плотности вероятности распределения Стьюдента , как и стандартного нормального распределения , является четной функцией.
.2X означает 2 хвоста, т.е. двусторонний квантиль .
Квантили распределения ХИ-квадрат
Вычислять квантили распределения ХИ-квадрат с n -1 степенью свободы требуется, если проводится проверка гипотезы о дисперсии нормального распределения (см. статью Проверка статистических гипотез в MS EXCEL о дисперсии нормального распределения ).
При проверке таких гипотез также используются верхние квантили. Например, при двухсторонней гипотезе требуется вычислить 2 верхних квантиля распределения ХИ 2 : χ 2 α/2,n-1 и χ 2 1- α/2,n-1 . Почему требуется вычислить два квантиля , не один, как при проверке гипотез о среднем , где используется стандартное нормальное распределение или t-распределение ?
Дело в том, что в отличие от стандартного нормального распределения и распределения Стьюдента , плотность распределения ХИ 2 не является четной (симметричной относительно оси х). У него все квантили больше 0, поэтому верхний альфа-квантиль не равен нижнему (1-альфа)-квантилю или по-другому: верхний альфа-квантиль не равен нижнему альфа-квантилю со знаком минус.
Результат равен 20,48. .ПХ означает правый хвост распределения, т.е. тот который расположен вверху на графике функции распределения .
Чтобы вычислить верхний (1-0,05/2)- квантиль при том же числе степеней свободы , т.е. χ 2 1-0,05/2,n-1 и необходимо записать формулу =ХИ2.ОБР.ПХ(1-0,05/2; 10) или =ХИ2.ОБР(0,05/2; 10)
Читайте также: