Как посчитать эксцесс в excel
В библиотеке табличного процессора есть ряд специальных функций для вычисления выборочных характеристик, которые можно по назначению разбить на три группы:
• функции, характеризующие центр распределения;
• функции, характеризующие рассевание;
• функции, позволяющие оценить форму эмпирического распределения.
Первую группу функций составляют:
• функция СРЗНАЧ вычисляет среднее арифметическое из одного или нескольких массивов чисел;
• функция СРГАРМ позволяет получить среднее гармоническое множества данных. Среднее гармоническое – это величина, обратная к среднему арифметическому обратных величин;
• функция СРГЕОМ вычисляет среднее геометрическое значений массива положительных чисел. Эту функцию можно использовать для вычисления средних показателей динамического ряда;
• функция МЕДИАНА позволяет получить медиану заданной выборки. Медиана – это элемент выборки, число элементов со значениями больше которого и меньше которого равно. Например, МЕДИАНА (5; 6; 8; 5; 9; 10; 8; 9) равна 8;
• функция МОДА вычисляет количество наиболее часто встречающихся значений в выборке (наиболее вероятная величина).
Вторую группу функций составляют:
• функция ДИСП позволяет оценить дисперсию по выборочным данным – степень разброса элементов выборки относительно среднего значения;
• функция СТАНДОТКЛОН вычисляет стандартное отклонение – характеризует степень разброса элементов выборки относительно среднего значения;
• функция ПЕРСЕНТИЛЬ позволяет вычислить квантили заданной выборки.
В третью группу функций входят:
• функция ЭКССЦЕСС – вычисляет оценку эксцесса по выборочным данным – степень выраженности хвостов распределения, т.е. частоты появления удаленных от среднего значения;
• функция СКОС позволяет оценить асимметрию выборочного распределения - величину, характеризующую несимметричность распределения элементов выборки относительно среднего значения.
Применение некоторых из перечисленных функций рассмотрим на примере.
Пример 1. В таблице, приведенной ниже, приведены сведения о ежемесячной реализации продукции за периоды до начала и после начала рекламной компании.
Функция ЭКСЦЕСС предназначена для анализа значений диапазона данных и возвращает значение коэффициента эксцесса.
Примеры использования функции ЭКСЦЕСС в Excel
Эксцесс является мерой остроты пика в распределении случайных величин. Для любого распределения величин можно рассчитать значение средней величины. В данном контексте коэффициент эксцесса показывает, находятся ли большинство значений распределения в непосредственной близости к средней величине, либо же они распределены отдаленно от нее.
Для нормального распределения величин значение эксцесса равно 0 (нулю). Если значение эксцесса принадлежит к диапазону положительных чисел, анализируемое распределение величин является относительно остроконечным. В противном случае (отрицательный коэффициент) рассматриваемое распределение является относительно сглаженным.
Анализ соотношения цены и спроса по коэффициенту ЭКСЦЕСС в Excel
Пример 1. Проверить теорию о том, что с уменьшением цен на бензин объемы покупок увеличиваются. В таблице представлены сведения о цене и объемах потребления топлива для одной заправочной станции по дням.
Вид таблицы данных:
Для расчета эксцесса используем данные из столбца C. Вид формулы:
Небольшой отрицательный эксцесс свидетельствует о том, что фактический рост объемов покупок оказался ниже ожидаемого, меньше 14%.
Для наглядного примера выполним визуализацию исходных данных с помощью линейного графика:
Сложность использования функции ЭКСЦЕСС заключается в правильной трактовке полученных значений. Для формулировки более достоверных выводов в отношении исследуемых данных рассчитывают другие статистические показатели.
Анализ распределения чисел и статистических выбросов в Excel
Пример 2. На основе данных об успеваемости студентов определить, есть ли в группе студенты, сильно отстающие от процесса обучения.
Исходная таблица данных:
Для расчетов используем формулу:
- B3:B13 – диапазон исследуемых значений (средних баллов студентов).
В результате получаем следующее значение:
Положительный эксцесс свидетельствует о том, что линия на графике распределения более резкая, нежели у кривой нормального закона распределения, с такой же дисперсией:
Положительный эксцесс свидетельствует о неравномерности распределения данных, а его довольно большая величина характеризует о имеющемся «скачке», то есть число студентов с низкой успеваемостью небольшое (в данном случае – только один студент).
Функция СКОС в Excel предназначена для определения коэффициента асимметрии для последовательности числовых данных и возвращает соответствующее числовое значение.
Расчет коэффициента асимметрии распределения чисел в Excel
Коэффициент асимметрии показывает степень несимметричности распределения числовых данных относительно среднего значения. Может принимать следующие значения:
- Из диапазона отрицательных чисел – отклонение в сторону отрицательных значений (отрицательные величины преобладают).
- Из диапазона положительных чисел – отклонение в сторону положительных значений (преобладание положительных величин).
- 0 – асимметрия отсутствует (например, для последовательности 1, 2, 3, -1, -2, -3 асимметрический коэффициент равен нулю – 0).
Для определения коэффициента асимметрии используется уравнение:
Пример 1. В таблице Excel содержатся два ряда числовых данных. Определить, какой из числовых рядов характеризуется наименьшим коэффициентом асимметрии.
Вид таблицы данных:
Для решения используем следующую формулу:
С помощью функции ЕСЛИ выполняем проверку коэффициента симметрии («имеет ли второй ряд большее значение скоса?») и возвращаем соответствующее значение с пояснением.
Проверим значения для каждого ряда по отдельности с помощью функций:
Обе последовательности имеют отклонения в отрицательную сторону, но у ряда 1 это выражено в большей степени.
Коэффициент асимметрии и аппроксимация нормальным распределением в Excel
Пример 2. Имеем последовательность чисел. Необходимо проанализировать данную последовательность и сделать вывод о возможности аппроксимации нормальным распределением.
Вид таблицы данных:
Для проверки нормального распределения величины применяют довольно сложные статистические критерии. Однако, в простейшем случае можно определить две величины (коэффициент асимметрии и эксцесс), чтобы сделать определенные выводы. Если они близки к нулю, аппроксимация нормальным распределением допустима.
Определим значения асимметрии и эксцесса следующими функциями:
Отклонения от 0 значительны, поэтому аппроксимация невозможна. Чтобы автоматизировать подобные расчеты введем некоторые условия:
В данном случае принято допущение о том, что максимальное допустимое отклонение модулей асимметрии и эксцесса составляет 0,1
. (3.24)
Нетрудно проверить, что , , где — выборочная средняя.
Выборочный центральный момент k-го порядка определяется формулой
. (3.25)
Из определения (3.25) следует
, , , , (3.26)
где s 2 — выборочная дисперсия.
Выборочный коэффициент асимметрии определяется как отношение
(3.27)
Для симметричных распределений коэффициент асимметрии равен нулю. Знак асимметрии показывает скос распределения относительно его среднего.
Положительная асимметрия указывает на то, что справа от среднего сосредоточено больше элементов выборки, чем слева от среднего значения.
Отрицательная асимметрия указывает на отклонение распределения в сторону отрицательных значений.
В программе Excel для вычисления асимметрии предназначена функция
Массив — диапазон ячеек с выборочными данными, для которых вычисляется асимметрия.
Значение функции СКОС() в программе Excel вычисляется по формуле
, (3.28)
которая дает несмещенную состоятельную оценку асимметрии генеральной совокупности.
Выборочным эксцессом распределения называется величина
(3.29)
Для нормального распределения эксцесс равен нулю.
Эксцесс характеризует относительную остроконечность или сглаженность распределения по сравнению с нормальным распределением. Положительный эксцесс обозначает относительно остроконечное распределение. Отрицательный эксцесс обозначает относительно сглаженное распределение.
В программе Excel эксцесс вычисляет функция
Массив — Диапазон ячеек, содержащий ряд.
Значение функции ЭКСЦЕСС() в программе Excel вычисляется по следующей формуле
. (3.30)
Формула (3.30) дает несмещенную состоятельную оценку эксцесса генеральной совокупности.
Пример 3.11. Вычислить асимметрию и эксцесс для данного вариационного ряда (табл.3.5).
xi | 2,4 | 2,8 | 3,2 | 3,6 | 4,0 | 4,4 | 4,8 | 5,2 | 5,6 |
ni |
Решение.Введите исходные данные в диапазоне А1:В10, как показано на рис.3.11.
В ячейку В11 введите формулу =СУММ(B2:B10) для посчета суммы частот.
Для вычисления среднего значения в ячейку D2 введите формулу
Для вычисления среднеквадратического отклонения в ячейку D3 введите формулу
и нажмите комбинацию клавиш Ctrl + Shift + Enter, так как в параметрах функции СУММКВ(A2:A10*B2:B10-D2) используются операции с массивами.
Выражение A2:A10*B2:B10-D2 представляет операции с массивами, оно означает массив из девяти ячеек, значения который соответственно равны значениям выражений А2*В2-D2, А3*В3-D2, …, А10*В10-D2.
Для вычисления асимметрии в D5 введите формулу
и нажмите комбинацию клавиш Ctrl + Shift + Enter.
Для вычисления эксцесса в D8 введите формулу
и нажмите комбинацию клавиш Ctrl + Shift + Enter.
Выводы. Асимметрия вариационного ряда положительна, это значит, что большая часть вариантов находится справа от среднего.
Положительный эксцесс указывает на то, что полигон частот по сравнению с плотностью вероятностей нормального распределения имеет более остроконечную или вытянутую вверх форму.
Для контроля правильности расчетных формул можно сравнить вычисляемые значения со значениями функций СКОС и ЭКСЦЕСС. Однако эти функции не применимы к вариационному ряду, их можно применить к несгруппированной выборке.
Замените в столбце В2:В10 числа на единицы, в столбце А2:А10 введите любые числа, а в ячейках D6 и D9 введите формулы =СКОС(А2:А10) и =ЭКСЦЕСС(А2:А10).
Получим результат, показанный на рис.3.12. В ячейках D5, D6 должны быть одинаковые значения, также как и в ячейках D8, D9.
Замечание. Для сгруппированной выборки при вычислении асимметрии и эксцесса нам пришлось вводить громоздкие формулы, так как функции СКОС и ЭКСЦЕСС применимы только к несгруппированной выборке.
Как построить свою речь (словесное оформление): При подготовке публичного выступления перед оратором возникает вопрос, как лучше словесно оформить свою.
Как вы ведете себя при стрессе?: Вы можете самостоятельно управлять стрессом! Каждый из нас имеет право и возможность уменьшить его воздействие на нас.
Почему человек чувствует себя несчастным?: Для начала определим, что такое несчастье. Несчастьем мы будем считать психологическое состояние.
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).
В этой статье описаны синтаксис формулы и использование функции СКОС в Microsoft Excel.
Описание
Возвращает асимметрию распределения. Асимметрия характеризует степень несимметричности распределения относительно его среднего. Положительная асимметрия указывает на отклонение распределения в сторону положительных значений. Отрицательная асимметрия указывает на отклонение распределения в сторону отрицательных значений.
Синтаксис
Аргументы функции СКОС описаны ниже.
Число1; число2. Аргумент "число1" является обязательным, последующие числа необязательные. От 1 до 255 аргументов, для которых вычисляется асимметрия. Вместо аргументов, разделенных точкой с запятой, можно использовать один массив или ссылку на массив.
Замечания
Аргументы могут быть либо числами, либо содержащими числа именами, массивами или ссылками.
Учитываются логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов.
Если аргумент, который является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, то такие значения пропускаются; однако ячейки, которые содержат нулевые значения, учитываются.
Аргументы, которые представляют собой значения ошибок или текст, не преобразуемый в числа, приводят к возникновению ошибки.
Уравнение для асимметрии имеет следующий вид:
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Эксцесс (коэффициент эксцесса) случайной величины (и дискретной, и непрерывной) Ex(X) — величина, характеризующая степень островершинности или плосковершинности распределения, т.е. степень так называемого «выпада». Сидоренко Е.В. Методы математической обработки в психологии / Е.В. Сидоренко; [отв. ред. к.ф.-м.н. А.Б. Алексеев].?Санкт-Петербург: Речь, 2014.?349 с.
Пусть задана случайная величина , такая что .
Коэффициент эксцесса распределения случайной величины определяется формулой:
— четвёртый центральный момент случайной величины ;
— дисперсия или второй центральный момент случайной величины ;
Нормальное распределение имеет нулевой эксцесс, .
Если хвосты распределения «легче», а пик острее, чем у нормального распределения, то .
Если хвосты распределения «тяжелее», а пик более «приплюснутый», чем у нормального распределения, то .
Область возможных значений эксцесса .
Рис. 3 Распределение плотности вероятности с различными коэффициентами эксцесса
В MS Excel расчет эксцесса и коэффициента асимметрии реализован с помощью функций ЭКСЦЕСС И СКОС Информационные технологии в науке и образовании: Учебное пособие / Е.Л. Федотова, А.А.Федотов. — М.: ИД ФОРУМ: ИНФРА-М, 2011. — 336 с.
СКОС — Возвращает асимметрию распределения. Асимметрия характеризует степень несимметричности распределения относительно его среднего. Положительная асимметрия указывает на отклонение распределения в сторону положительных значений. Отрицательная асимметрия указывает на отклонение распределения в сторону отрицательных значений.
Число 1, число2, . — это от 1 до 30 аргументов, для которых вычисляется асимметричность. Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой.
Аргументы должны быть числами или именами, массивами или ссылками, содержащими числа.
Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако, ячейки, которые содержат нулевые значения учитываются.
Уравнение для асимметрии определяется следующим образом:
где — стандартное отклонение выборки.
ЭКСЦЕСС — Возвращает эксцесс множества данных. Эксцесс характеризует относительную остроконечность или сглаженность распределения по сравнению с нормальным распределением. Положительный эксцесс обозначает относительно остроконечное распределение. Отрицательный эксцесс обозначает относительно сглаженное распределение.
Число1, число2, . — это от 1 до 30 аргументов, для которых вычисляется эксцесс. Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой.
Аргументы должны быть числами или именами, массивами или ссылками, содержащими числа.
Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако, ячейки, которые содержат нулевые значения учитываются.
Эксцесс определяется следующим образом:
где — стандартное отклонение выборки.
Глава 3. Методика графического представления (гистограмма, полигон, кумулята) результатов психологических наблюдений с использованием электронных таблиц MS Excel
1) Заполнить таблицу.
Рис. 3 Заполнение таблицы исходными данными
- 2) Выделить Диапазон, в нашем случае (А1:B7), на панели быстрого доступа найти кнопку Мастер диаграмм или Вставка —> Диаграмма Симоновича С. В. Информатика. Базовый курс : учебное пособие для студ. высш. техн. учеб.заведений / Под ред. С. В. Симоновича .? Издание 2-е .? Санкт-Петербург [и др.] : Питер, 2014.? 640 с.
- 3) Определить тип диаграммы(гистограмма, кумулята, полигон), в нашем случае —> Гистограмма
Рис. 4 Определение типа диаграммы
Пример 1. Построить эмпирическое распределение веса курсантов в килограммах для следующей выборки: 64, 57, 63, 62, 58, 61, 63, 70, 60, 61, 65, 62, 62, 40, 64, 61, 59, 59, 63, 61.
- 1. В ячейку А1 введите слово Наблюдения, а в диапазон А2:А21 — значения веса курсантов.
- 2. В ячейку В1 введите названия интервалов Вес, кг. В диапазон В2:В8 введите граничные значения интервалов (40, 45, 50, 55, 60, 65, 70).
- 3. Введите заголовки создаваемой таблицы: в ячейки С1 — Абсолютныечастоты, в ячейки D1 — Относительныечастоты, в ячейки E1 — Накопленныечастоты. Информатика. Базовый курс, СПб: Питер, 2011, (Учебник для ВУЗов), под ред. Симновича С.В.-640 с.
- 4. С помощью функции Частота заполните столбец абсолютных частот, для этого выделите блок ячеек С2:С8. С панели инструментов Стандартная вызовите Мастер функций (кнопка fx). В появившемся диалоговом окне выберите категорию Статистические и функцию ЧАСТОТА, после чего нажмите кнопку ОК. Указателем мыши в рабочее поле Массив_данных введите диапазон данных наблюдений (А2:А8). В рабочее поле Двоичный_массив мышью введите диапазон интервалов (В2:В8). Слева на клавиатуре последовательно нажмите комбинацию клавиш Ctrl+Shift+Enter. В столбце C должен появиться массив абсолютных частот.
- 5. В ячейке C9 найдите общее количество наблюдений. Активизируйте ячейку С9, на панели инструментов Стандартная нажмите кнопку Автосумма. Убедитесь, что диапазон суммирования указан правильно и нажмите клавишу Enter.
- 6. Заполните столбец относительных частот. В ячейку введите формулу для вычисления относительной частоты: =C2/$C$9. Нажмите клавишу Enter. Протягиванием (за правый нижний угол при нажатой левой кнопке мыши) скопируйте введенную формулу в диапазон и получите массив относительных частот.
- 7. Заполните столбец накопленных частот. В ячейку D2 скопируйте значение относительной частоты из ячейки E2. В ячейку D3 введите формулу: =E2+D3. Нажмите клавишу Enter. Протягиванием (за правый нижний угол при нажатой левой кнопке мыши) скопируйте введенную формулу в диапазон D3:D8. Получим массив накопленных частот.
Рис. 5. Результат вычислений из примера 1
8. Постройте диаграмму относительных и накопленных частот. Щелчком указателя мыши по кнопке на панели инструментов вызовите Мастер диаграмм. В появившемся диалоговом окне выберите закладку Нестандартные и тип диаграммы График/гистограмма.
Рис. 6 Диаграмма относительных и накопленных частот из примера 1
Рис. 7 Гистограмма коэффициентов асиметрии и эксцесса
Рис. 8 Полигон коэффициентов асиметрии и эксцесса
Рис. 9 Кумулята коэффициентов асиметрии и эксцесса
Функция СКОС в Excel предназначена для определения коэффициента асимметрии для последовательности числовых данных и возвращает соответствующее числовое значение.
Расчет коэффициента асимметрии распределения чисел в Excel
Коэффициент асимметрии показывает степень несимметричности распределения числовых данных относительно среднего значения. Может принимать следующие значения:
- Из диапазона отрицательных чисел – отклонение в сторону отрицательных значений (отрицательные величины преобладают).
- Из диапазона положительных чисел – отклонение в сторону положительных значений (преобладание положительных величин).
- 0 – асимметрия отсутствует (например, для последовательности 1, 2, 3, -1, -2, -3 асимметрический коэффициент равен нулю – 0).
Для определения коэффициента асимметрии используется уравнение:
Пример 1. В таблице Excel содержатся два ряда числовых данных. Определить, какой из числовых рядов характеризуется наименьшим коэффициентом асимметрии.
Вид таблицы данных:
Для решения используем следующую формулу:
С помощью функции ЕСЛИ выполняем проверку коэффициента симметрии («имеет ли второй ряд большее значение скоса?») и возвращаем соответствующее значение с пояснением.
Проверим значения для каждого ряда по отдельности с помощью функций:
Обе последовательности имеют отклонения в отрицательную сторону, но у ряда 1 это выражено в большей степени.
Коэффициент асимметрии и аппроксимация нормальным распределением в Excel
Пример 2. Имеем последовательность чисел. Необходимо проанализировать данную последовательность и сделать вывод о возможности аппроксимации нормальным распределением.
Вид таблицы данных:
Для проверки нормального распределения величины применяют довольно сложные статистические критерии. Однако, в простейшем случае можно определить две величины (коэффициент асимметрии и эксцесс), чтобы сделать определенные выводы. Если они близки к нулю, аппроксимация нормальным распределением допустима.
Определим значения асимметрии и эксцесса следующими функциями:
Отклонения от 0 значительны, поэтому аппроксимация невозможна. Чтобы автоматизировать подобные расчеты введем некоторые условия:
В данном случае принято допущение о том, что максимальное допустимое отклонение модулей асимметрии и эксцесса составляет 0,1
Читайте также: