Как посчитать дециль в excel

Как посчитать дециль в excel

Необходимые характеристики должны быть рассчитаны как для исходного ряда значений каждого признака (с помощью функций MS Excel), так и для сгруппированных данных. При этом последние являются приближенными значениями искомых характеристик.

1. Характеристики центра и структуры распределения

Средняя величина — обобщающая количественная характеристика признака в статистической совокупности, отражающая типичный уровень этого признака в расчете на единицу совокупности.

Средняя величина для несгруппированных данных:

,

где xi – значение признака у i–ой единицы совокупности;

N — объем совокупности.

Среднее значение по исходным данным определяются с помощью функции СРЗНАЧ. Вызываем функцию (из категории «Статистические»):

= СРЗНАЧ(число_1;число_2…)

где число_1;число_2… – числовые аргументы, для которых вычисляется среднее (выделить для первого аргумента столбец исходных значений признака).

Средняя величина для интервально сгруппированных данных:

,

где х н j, х в j — нижняя и верхняя граница j–ого интервала;

fj – вес усреднения для j-ой группы (в качестве весов усреднения берут частоты/частости).

К структурным характеристикам ряда распределения относятся квантили распределения и мода.

Квантиль распределения(Qi) – это значение признака, занимающее определенное место в упорядоченной по данному признаку совокупности. Основными квантильными характеристиками являются:

медиана (Ме) — значение признака, приходящееся на середину упорядоченной совокупности,

квартили (Q1/4, Q2/4=Ме, Q3/4) – значения признака, делящие упорядоченную совокупность на 4 равные (по числу единиц) части,

децили (Q0,1,Q0,2,…,Q0,9) – значения признака, делящие упорядоченную совокупность на 10 равных частей.

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

Квантили распределения по исходным данным определяются с помощью функций МЕДИАНА, КВАРТИЛЬ, ПРОЦЕНТИЛЬ. Вызываем необходимую функцию (из категории «Статистические»):

= МЕДИАНА(число_1;число_2…)

где число_1;число_2… – числовые аргументы, для которых вычисляется медиана (выделить для первого аргумента столбец исходных значений признака).

= КВАРТИЛЬ(массив;часть)

где массив – это столбец исходных значений признака, для которых определяется значение квартиля;

часть – это значение, определяющее уровень квартиля: для Q1/41, для Q3/43.

= ПРОЦЕНТИЛЬ(массив;К)

где массив – это столбец исходных значений признака, для которых определяется значение К-ого процентиля (может использоваться для определения квартилей и децилей);

К – это значение, определяющее уровень процентиля: для Q0,10.1, для Q0,90.9; для Q1/40.25, для Q3/40.75 .

Результаты расчета характеристик по функциям MS Excel:

Для сгруппированых данных предварительно определяется группа, которая содержит i-ый квантиль: та группа от начала ряда, в которой сумма накопленных частот равна или превышает N·i, где i— индекс квантиля.

Квантили для интервально сгруппированных данных:

где Xqi — нижняя граница интервала, в котором находится i — ый квантиль;

— величина интервала, в котором находится i — ый квантиль;

F(-1) – сумма накопленных частот интервалов, предшествующих интервалу, в котором находится i — ый квантиль;

Nqi – частота интервала, в котором находится i — ый квантиль.

Мода (Мо) – наиболее часто встречающееся значение признака в совокупности.

Для не сгруппированных данных мода обычно не определяется. Если признак принимает ограниченное число значений и они повторяются, можно определить моду с помощью функции МОДА. Вызываем функцию (из категории «Статистические»):

= МОДА(число_1;число_2…)

где число_1;число_2… – числовые аргументы, для которых вычисляется мода (выделить для первого аргумента столбец исходных значений признака).

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

где XMo — нижняя граница интервала, в котором находится мода;

— величина модального интервала;

NMо, NMо-1, NMо+1 – частоты, соответственно, модального, предшествующего и последующего интервалов.

Расчет моды по данной формуле предполагает, что модальный, предшествующий и последующий интервалы – это интервалы одинаковой длины.

Таблица 3. Расчет характеристик центра и структуры распределения

Границы интервала Частота Накопленная частота Середина интервала Сер. инт. × Частота
нижняя верхняя
12 Мо 12 Q1/4, Q1/10
22 Ме
30 Q3/4
39 Q9/10
Итого

Расчет характеристик (см. табл. 3):

Среднее: млн. у.е./год

Медиана: млн. у.е./год

1 квартиль: млн. у.е./год

3 квартиль: млн. у.е./год

1 дециль: млн. у.е./год

9 дециль: млн. у.е./год

Мода: млн. у.е./год

2. Характеристики вариации

Для измерения рассеяния (вариации) признака применяются различные абсолютные и относительные показатели вариации.

Абсолютные показатели вариации:

Размах вариации, R — разность между максимальным и минимальным значениями признака в совокупности:

Среднее линейное отклонение, d — средняя арифметическая абсолютных значений отклонений отдельных вариант от их средней арифметической. Для не сгруппированных и сгруппированных данных, соответственно:

Читайте также:  Как попасть в настройки биос

, ,

где N – объем совокупности;

fj – частота/частость в j – ой группе.

Среднее квадратическое отклонение, s — средняя квадратическая из отклонений отдельных вариант от их средней арифметической. Для не сгруппированных и сгруппированных данных, соответственно:

, .

Дисперсия, s 2 — средний квадрат отклонений вариант от их средней величины (квадрат среднего квадратического отклонения). Может быть также вычислена, как разность среднего квадрата значения признака и квадрата среднего арифметического значения признака:

.

Абсолютные показатели вариации по исходным данным определяются с помощью функций СРОТКЛ, СТАНДОТКЛОН, ДИСП. Вызываем необходимую функцию (из категории «Статистические»):

= СРОТКЛ(число_1;число_2…)

где число_1;число_2… – числовые аргументы, для которых вычисляется среднее линейное отклонение (выделить для первого аргумента столбец исходных значений признака).

= СТАНДОТКЛОН(число_1;число_2…)

где число_1;число_2… – числовые аргументы, для которых вычисляется среднее квадратическое отклонение (выделить для первого аргумента столбец исходных значений признака).

= ДИСП(число_1;число_2…)

где число_1;число_2… – числовые аргументы, для которых вычисляется дисперсия (выделить для первого аргумента столбец исходных значений признака).

Самым распространенным относительным показателем рассеяния является коэффициент вариации. Он представляет собой выраженное в процентах отношение среднего квадратического отклонения к средней арифметической:

.

Коэффициент вариации используют как характеристику однородности совокупности. Совокупность считается качественно однородной, если коэффициент вариации не превышает 33%.

Результаты расчета характеристик по функциям MS Excel:

Расчет характеристик (см. табл. 4):

Размах вариации: млн. у.е./год

Среднее линейное отклонение: млн. у.е./год

Среднее квадратическое отклонение: млн. у.е./год

Дисперсия: (млн. у.е./год) 2

Коэффициент вариации:

Таблица 4. Расчет показателей вариации

Серед. инт. Частота (Серед. инт.-сред.) × Част. ABS((Серед. инт.-сред.) × Част.) (Серед. инт.-сред.) 2 × Част.
-1860
-550
Итого

3. Характеристики формы распределения

Для характеристики однородности совокупности используют и показатели формы распределения: коэффициент асимметрии и эксцесс.

Коэффициент асимметрии, Asпоказатель симметричности распределения. Положительная величина показателя асимметрии указывает на правостороннюю асимметрию, отрицательная – на левостороннюю, близость нулю свидетельствует о симметричном распределении.

Способы расчета коэффициента асимметрии:

1. Коэффициент асимметрии Пирсона:

.

Величина As может изменяться от –1 до +1 (для одновершинных распределений). Чем ближе по модулю As к 1, тем асимметрия существеннее.

2. Показатель, основанный на определении центрального момента третьего порядка – М3:

.

В симметричном распределении его величина равна нулю. Для оценки существенности такого коэффициента вычисляется его средняя квадратическая ошибка:

,

где N — объем совокупности.

Если çAsç/sAs меньше 2, это свидетельствует о несущественном характере асимметрии.

Коэффициент эксцесса, Exпоказатель островершинности распределения. Он рассчитывается для симметричных распределений.Эксцесс представляет собой выпад вершины эмпирического распределения вверх или вниз от вершины кривой нормального распределения. Показатель, использующий центральный момент четвертого порядка — М4:

.

Для нормального распределения эксцесс равен нулю. Положительный эксцесс означает, что распределение более островершинное чем нормальное; отрицательный эксцесс означает более плосковершинный характер распределения, чем у нормального Для оценки существенности такого коэффициента эксцесса вычисляется его средняя квадратическая ошибка:

,

где N — объем совокупности.

Если çExç/sEx меньше 2, это свидетельствует о несущественном характере эксцесса (близости распределения по характеру островершинности к нормальному).

По исходным данным характеристики формы распределения могут быть определены с помощью функций СКОС, ЭКСЦЕСС. Вызываем функцию (из категории «Статистические»):

= СКОС(число_1;число_2…)

где число_1;число_2… – числовые аргументы, для которых вычисляется асимметрия (выделить для первого аргумента столбец исходных значений признака).

= ЭКСЦЕСС(число_1;число_2…)

где число_1;число_2… – числовые аргументы, для которых вычисляется эксцесс распределения (выделить для первого аргумента столбец исходных значений признака).

Результаты расчета характеристик по функциям MS Excel:

Таблица 5. Расчет показателей формы распределения

Середина интервала Частота (Середина интервала -среднее) 3 × Частота
-44686500
-1663750
Итого

Расчет характеристик (см. табл. 5):

Асимметрия:

Так как данный ряд распределения явно несимметричен, расчет эксцесса не производится.

1. Задача по статистике – Коэффициент эластичности.

Для данного товара коэффициент эластичности k эл = -0,5.

Как изменится потребление этого товара, если цены на него возрастут на 10%?

Коэффициент эластичности – это отношение процентного изменения спроса к процентному изменению цены.

Следовательно процентное изменение спроса = k эл*процентное изменение цены=

=-0,5*10=5%. То есть потребление снизится на 5%.

Найти децильный коэффициен, моду и медианну по следующим данным.

Средний доход, руб. / (чел. мес)

Количество человек, млн. чел

Накопленная частота, млн. чел

Мода рассчитывается по формуле:

,

Где – нижняя граница модального интервала.

– модальный интервал.

– частота модального интервала.

– частота интервала, предшествующего модальному.

Читайте также:  Вид со спутника просмотр улиц

– частота интервала, следующего за модальным.

Модальный интервал определяется по наибольшей частоте. Наибольшая частота в данной задаче 31,3, что соответствует интервалу от 4000 до 6000 рублей.

=

=4000+2000*(31,3-30,6) / (( 31,3-30,6)-( 31,3-25,3))=4209 рублей.

Таким образом самый частый доход 4209 рублей.

Медианна рассчитывается по формуле:

,

Где – нижняя граница медианного интервала.

– медианный интервал.

– f частота.

– частота медианного интервала.

– накопленная частота интервала, предшествующего медианному.

Медианный интервал определяется по накопленной частоте. Суммируются f частоты до тех пор, пока очередная накопленная частота не превысит середину совокупности. В данной задаче совокупность состоит из 147,5 млн человек. Поэтому суммируем f частоты пока не превысим 147,5 /2=7 3,75 млн человек. Это произойдет в интервале от 6000 до 8000 руб, поскольку накопленная частота данного интервала равна 87.2, т.е. больше половины совокупности.

Следовательно интервал от 6000 до 8000 руб является медианным интервалом. Накопленная частота интервала, предшествующего медианному, равна 61,9.

=

=6000+2000*(0,5*147,5-61,9) /2 5,3 = 6937 руб. / мес.

Значит половина людей в совокупности имеет доход менее 6937 рублей, а половина более 6937 рублей.

Расчет децильного коэффициента

Рассчитаем дециль №1 (10% совокупности).

=0.1*1 47,5 =14. 75 млн чел.(14,75 млн чел соответствует 1-му интервалу).

Таким образом, дециль №1 входит в 1-й интервал с доходом до 4000.

Дециль рассчитывается по формуле:

,

Где – нижняя граница децильного интервала (интервала, содержащего дециль).

– децильный интервал.

– f частота.

– частота децильного интервала.

– накопленная частота интервала, предшествующего децильному.

– коэффициент дециля (для дециля №1 10%, т.е. 0,1).

Рассчитаем первый дециль

=

=0+4000*(14,75-0) / 30,6 = 1928 рублей.

Рассчитаем дециль №9 (90% совокупности).

=0. 9 *14 7,5 = 132,75 млн чел.(соответствует интервалу 16000-20000, а накопленная частота, предшествующая интервалу, содержащему дециль, равно 132,6)

Рассчитаем девятый дециль

=

=16000+4000*(132,75-132,6) /7= 16086 рублей.

Децильный коэффициент рассчитывается по формуле

.

=16086 / 1928=8,34.

Таким образом, минимальный доход 10% самого богатого населения в 8,4 раза выше, чем максимальный доход 10% самого бедного населения.

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

Вероятность события p есть отношение числа благоприятных исходов m к числу всех возможных исходов n этого события: p=m/n. Например, вероятность появления туза в наугад выбранной карте из колоды в 52 карты равна 4/52=0.0769, так как m=4, а n=52.

Если известно соответствие между появлениями (величинами) x1, x2, …, xn случайного события (переменной) X и соответствующими вероятностями их реализации p1, p2, …, pn, то говорят, что известен закон распределения случайной величины F(x). Большинство встречающихся на практике распределений вероятностей реализовано в Excel.

Распределения вероятностей имеют числовые характеристики.

Функции Excel для вычисления числовых характеристик распределения вероятностей. Они входят в группу Статистические. При вычислении функций в качестве случайных величин используйте следующие значения:

Математическое ожидание случайной величины (среднее арифметическое), характеризующее центр распределения вероятностей, вычисляется функцией СРЗНАЧ. СРЗНАЧ(A1:A7) = 9.

Дисперсия, характеризует разброс случайной величины относительно центра распределения вероятностей и вычисляется функцией ДИСПР. ДИСПР(A1:A7) = 4.857.

Среднеквадратичное отклонение есть квадратный корень из дисперсии, характеризует разброс случайной величины в единицах случайной величины и вычисляется функцией СТАНДОТКЛОНП. СТАНДОТКЛОНП(A1:A7) = 2.203893.

Квантиль случайной величины с законом распределения F(x) есть значение случайной величины x при заданной вероятности p., т.е. есть решение уравнения F(x)=p. Медиана есть квантиль с вероятностью p=0.5.

Excel, вместо квантилей содержит функции вычисления х для определенных уровней р: квартили (кварта – четверть), децили (дециль – десятая часть), персентили (персент – процент). Различают нижний квартиль с вероятностью p=0.25 и верхний квартиль с вероятностью p=0.75. Децили это квантили с вероятностью 0.1, 0.2, …, 0.9.

Функцию КВАРТИЛЬ используют, чтобы разбить данные на группы. В качестве второго аргумента указывают уровень (четверть), для которого нужно вернуть решение: 0 – минимальное значение распределения, 1 – первый, нижний квартиль, 2 – медиана, 3 – третий, верхний квартиль, 4 – максимальное значение. Например, КВАРТИЛЬ(A1:A7;3) = 10, т.е. 75% всех значений меньше 10, КВАРТИЛЬ(A1:A7;2) = 9.

Функция ПЕРСЕНТИЛЬ вычисляет квантиль указанного уровня вероятности и используется для определения порога приемлемости значений. В качестве второго аргумента указывают уровень 0.1, 0.2, …, 0.9. ПЕРСЕНТИЛЬ(A1:A7;0,9) = 11.8, т.е. 90% всех значений меньше 11.8.

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

Распределения вероятностей, реализованные в Excel.

Каждый закон распределения описывает процессы разной вероятностной природы и характеризуется специфическими параметрами:

равномерное распределениеn случайных чисел выпадает с одной и той же вероятностью p=1/n; характеризуется нижней и верхней границей; примером является появление чисел 1, 2, …, 6 при бросании игральной кости (p=1/6);

Читайте также:  Беспроводное зарядное устройство для apple watch

биномиальное распределение моделирует взаимосвязь числа успешных испытаний m и вероятностей успеха каждого испытания p при общем количестве испытаний n — функции БИНОМРАСП и КРИТБИНОМ;

нормальное (гауссово) распределение описывает процессы, в которых на результат воздействует большое число независимых случайных факторов, среди которых нет сильно выделяющихся – функции НОРМРАСП, НОРМСТРАСП, НОРМОБР, НОРМСТОБР и НОРМАЛИЗАЦИЯ;

распределение Пуассона, предсказывает число случайных событий на определенном отрезке времени или на определенном пространстве, позволяет аппроксимировать биномиальное распределение – функция ПУАССОН;

экспоненциальное (показательное) распределение, моделирует временные задержки между событиями, описывает процессы в задачах массового обслуживания и в задачах с «временем жизни» — ЭКСПРАСП;

распределение хи-квадрат, связано с нормальным, возвращает одностороннюю вероятность распределения и используется для сравнения предполагаемых и наблюдаемых значений – функция ХИ2РАСП;

распределение Стьюдента, связано с нормальным, возвращает вероятность для t-распределения Стьюдента и используется для проверки гипотез при малом объеме выборки – функция СТЬЮДРАСП;

F-распределение (Фишера), связано с нормальным и может быть использовано в F-тесте, который сравнивает степени разброса двух множеств данных – fраспобр;

гамма-распределение используется для изучения случайных величин, имеющих асимметричное распределение, в теории очередей – функция ГАММАРАСП;

а также другие распределения – функции БЕТАРАСП, ВЕЙБУЛЛ, ОТРБИНОМРАСП, ГИПЕРГЕОМЕТ, ЛОГНОРМРАСП и др.

Биномиальное распределение характеризуется числом успешных испытаний m, вероятностью успеха каждого испытания p и общим количеством испытаний n. Классическим примером использования биномиального распределения является выборочный контроль качества больших партий товара, изделий в торговле, на производстве, когда сплошная проверка невозможна. Из партии выбирают n образцов и регистрируют число бракованных m. Бракованными могут быть 1, 2, … , n образцов, но вероятности реального числа бракованных будут различными. Если контрольная вероятность брака ниже допустимой вероятности, то можно гарантировать достаточное качество всей партии.

В Excel функция БИНОМРАСП вычисляет вероятность отдельного значения распределения по заданным m, n и р, а функция КРИТБИНОМ – случайное число по заданной вероятности. Обычно функция КРИТБИНОМ используется для определения наибольшего допустимого числа брака.

В качестве примера построим график плотности вероятности биномиального распределения для n=10 (1, 2, …, 10) и p=0.2. Введите исходные данные, как показано на рисунке:

Далее в ячейку В4 введите статистическую функцию БИНОМРАСП и заполните ее параметры как показано на рисунке:

Здесь параметр Число_s есть число успешных испытаний m, Испытания – число независимых испытаний n, Вероятность_s – вероятность успеха каждого испытания p. Параметр Интегральный равен 0, если требуется получить плотность распределения (вероятность для значения m), и равен 1, если требуется получить вероятность с накоплением (вероятность того, что число успешных испытаний не меньше значения аргумента Число_s).

Формулу из В4 размножьте в ячейки В5:В13. Ниже показан результат:

В колонке В вычислены вероятности успешных испытаний m=1, 2, …, 10. Теперь по диапазону В4:В13 постройте график или гистограмму биномиальной функции плотности распределения – результат на рисунке. Поэкспериментируйте, изменяя значение вероятности в ячейке В1: 0.3, 0.4, 0.8, проследите за изменениями формы графика.

Для иллюстрации функции КРИТБИНОМ используем предыдущий пример – необходимо найти число m, для которого вероятность интегрального распределения больше или равна 0.75. Вызовите функцию КРИТБИНОМ и заполните параметры. Вы должны получить значение 3. Это означает, что при вероятности интегрального распределения >= 0.75 будет не менее трех (m>=3) успешных испытаний.

Нормальное распределение характеризуется средним арифметическим (математическим ожиданием) m и стандартным (среднеквадратичным) отклонением r. Дисперсия равна r 2 . Краткое обозначение распределения N(m,r 2 ). График нормального распределения симметричен относительно центра распределения (точки m), чем меньше r, тем больше вероятность появления случайной величины. В пределы [mr,m+r] нормально распределенная случайная величина попадает с вероятностью 0,683 в пределы [m-2r,m+2r] — с вероятностью 0,955 и т.д.

При m=0 и r=1 нормальное распределение называется стандартным или нормированным – N(0,1).

Нормальное распределение имеет очень широкий круг приложений. В качестве примера построим график плотности вероятностей нормального распределения при m=15 и r=1,5 в диапазоне [m-3r,m+3r] c шагом 0,5. Результат показан на рисунке.

Выполните следующие действия:

в ячейку А4 введите формулу =B1-3*B2, в ячейку А5 формулу =A4+B$3 и размножьте ее по ячейку А22;

в ячейку В4 введите функцию НОРМРАСП из группы Статистические – параметры заполните как на рисунке;

размножьте формулу из ячейки В4 по ячейку В22 и по диапазону В4:В22 постройте график; на 2-ом шаге мастера диаграмм в закладке Ряд введите подписи к оси х из диапазона А4:А22.

Ссылка на основную публикацию
Как поставить темную тему на яндекс браузер
Многие разработчики программ и сервисов стараются добавлять альтернативное оформление в виде темного интерфейса. Замена белого цвета удобна тем, кто много...
Как пользоваться пультом управления телевизора lg
Стандартный пульт дистанционного управления Вашего ТВ может выглядеть так: 2 ответа к вопросу “Пульт дистанционного управления (описание кнопок)” как найти...
Как пользоваться рут правами на андроиде
Kingo Android Root – это программа для быстрого получения root прав практически на любом устройстве, которое находится под управлением ОС...
Как поставить фото в телеграмме на аватарку
Как в Телеграмме поставить фото на аву (аватар) — ведь трудно недооценить ее значение, картинка в профиле не только формирует...
Adblock detector