Oc-windows.ru

IT Новости из мира ПК
34 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Асимметричность в excel

Применение описательной статистики в Microsoft Excel

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

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

Использование описательной статистики

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

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

  • Медиана;
  • Мода;
  • Дисперсия;
  • Среднее;
  • Стандартное отклонение;
  • Стандартная ошибка;
  • Асимметричность и др.

Рассмотрим, как работает данный инструмент на примере Excel 2010, хотя данный алгоритм применим также в Excel 2007 и в более поздних версиях данной программы.

Подключение «Пакета анализа»

Как уже было сказано выше, инструмент «Описательная статистика» входит в более широкий набор функций, который принято называть Пакет анализа. Но дело в том, что по умолчанию данная надстройка в Экселе отключена. Поэтому, если вы до сих пор её не включили, то для использования возможностей описательной статистики, придется это сделать.

  1. Переходим во вкладку «Файл». Далее производим перемещение в пункт «Параметры».

В активировавшемся окне параметров перемещаемся в подраздел «Надстройки». В самой нижней части окна находится поле «Управление». Нужно в нем переставить переключатель в позицию «Надстройки Excel», если он находится в другом положении. Вслед за этим жмем на кнопку «Перейти…».

  • Запускается окно стандартных надстроек Excel. Около наименования «Пакет анализа» ставим флажок. Затем жмем на кнопку «OK».
  • После вышеуказанных действий надстройка Пакет анализа будет активирована и станет доступной во вкладке «Данные» Эксель. Теперь мы сможем использовать на практике инструменты описательной статистики.

    Применение инструмента «Описательная статистика»

    Теперь посмотрим, как инструмент описательная статистика можно применить на практике. Для этих целей используем готовую таблицу.

      Переходим во вкладку «Данные» и выполняем щелчок по кнопке «Анализ данных», которая размещена на ленте в блоке инструментов «Анализ».

    Открывается список инструментов, представленных в Пакете анализа. Ищем наименование «Описательная статистика», выделяем его и щелкаем по кнопке «OK».

    После выполнения данных действий непосредственно запускается окно «Описательная статистика».

    В поле «Входной интервал» указываем адрес диапазона, который будет подвергаться обработке этим инструментом. Причем указываем его вместе с шапкой таблицы. Для того, чтобы внести нужные нам координаты, устанавливаем курсор в указанное поле. Затем, зажав левую кнопку мыши, выделяем на листе соответствующую табличную область. Как видим, её координаты тут же отобразятся в поле. Так как мы захватили данные вместе с шапкой, то около параметра «Метки в первой строке» следует установить флажок. Тут же выбираем тип группирования, переставив переключатель в позицию «По столбцам» или «По строкам». В нашем случае подходит вариант «По столбцам», но в других случаях, возможно, придется выставить переключатель иначе.

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

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

    Далее, если вы хотите чтобы выводилась также итоговая статистика, то нужно установить флажок около соответствующего пункта. Также можно установить уровень надежности, поставив галочку около соответствующего значения. По умолчанию он будет равен 95%, но его можно изменить, внеся другие числа в поле справа.

    Кроме этого, можно установить галочки в пунктах «K-ый наименьший» и «K-ый наибольший», установив значения в соответствующих полях. Но в нашем случае этот параметр так же, как и предыдущий, не является обязательным, поэтому флажки мы не ставим.

    После того, как все указанные данные внесены, жмем на кнопку «OK».

    После выполнения этих действий таблица с описательной статистикой выводится на отдельном листе, который был нами назван «Итоги». Как видим, данные представлены сумбурно, поэтому их следует отредактировать, расширив соответствующие колонки для более удобного просмотра.

    Читать еще:  Как строить графики в excel 2020

  • После того, как данные «причесаны» можно приступать к их непосредственному анализу. Как видим, при помощи инструмента описательной статистики были рассчитаны следующие показатели:
    • Асимметричность;
    • Интервал;
    • Минимум;
    • Стандартное отклонение;
    • Дисперсия выборки;
    • Максимум;
    • Сумма;
    • Эксцесс;
    • Среднее;
    • Стандартная ошибка;
    • Медиана;
    • Мода;
    • Счет.
  • Если какие-то из вышеуказанных данных для конкретного вида анализа не нужны, то их можно удалить, чтобы они не мешали. Далее производится анализ с учетом статистических закономерностей.

    Как видим, с помощью инструмента «Описательная статистика» можно сразу получить результат по целому ряду критериев, которые в ином случае рассчитывались с применением отдельно предназначенной для каждого расчета функцией, что заняло бы значительное время у пользователя. А так, все эти расчеты можно получить практически в один клик, использовав соответствующий инструмент — Пакета анализа.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Функция СКОС и коэффициент асимметрии распределения в Excel

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

    Расчет коэффициента асимметрии распределения чисел в Excel

    Коэффициент асимметрии показывает степень несимметричности распределения числовых данных относительно среднего значения. Может принимать следующие значения:

    1. Из диапазона отрицательных чисел – отклонение в сторону отрицательных значений (отрицательные величины преобладают).
    2. Из диапазона положительных чисел – отклонение в сторону положительных значений (преобладание положительных величин).
    3. 0 – асимметрия отсутствует (например, для последовательности 1, 2, 3, -1, -2, -3 асимметрический коэффициент равен нулю – 0).

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

    Пример 1. В таблице Excel содержатся два ряда числовых данных. Определить, какой из числовых рядов характеризуется наименьшим коэффициентом асимметрии.

    Вид таблицы данных:

    Для решения используем следующую формулу:

    С помощью функции ЕСЛИ выполняем проверку коэффициента симметрии («имеет ли второй ряд большее значение скоса?») и возвращаем соответствующее значение с пояснением.

    Проверим значения для каждого ряда по отдельности с помощью функций:

    Обе последовательности имеют отклонения в отрицательную сторону, но у ряда 1 это выражено в большей степени.

    Коэффициент асимметрии и аппроксимация нормальным распределением в Excel

    Пример 2. Имеем последовательность чисел. Необходимо проанализировать данную последовательность и сделать вывод о возможности аппроксимации нормальным распределением.

    Вид таблицы данных:

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

    Определим значения асимметрии и эксцесса следующими функциями:

    Отклонения от 0 значительны, поэтому аппроксимация невозможна. Чтобы автоматизировать подобные расчеты введем некоторые условия:

    В данном случае принято допущение о том, что максимальное допустимое отклонение модулей асимметрии и эксцесса составляет 0,1

    Правила использования функции СКОС в Excel

    Функция имеет следующую синтаксическую запись:

    =СКОС( число1; [число2];. )

    • число1 – обязательный, принимает первое значение числовой последовательности или ссылку на диапазон ячеек с числовыми данными.
    • [число2];… — второй и последующие необязательные аргументы, принимающие числовые значения второго и последующих чисел исследуемого ряда.
    1. Функция принимает в качестве аргументов числа или данные, которые могут быть преобразованы к числовым данным, а также ссылки на ячейки с числами или преобразуемыми к числам данными. Иначе СКОС будет возвращать код ошибки #ЗНАЧ!
    2. Не преобразуемые к числам значения, содержащиеся в ячейках, в расчете не учитываются. Логические ИСТИНА и ЛОЖЬ также игнорируются рассматриваемой функцией.
    3. Если исследуемый ряд значений содержит менее трех числовых значений, функция СКОС вернет код ошибки # ЧИСЛО!

    Применение описательной статистики в Microsoft Excel

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

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

    Использование описательной статистики

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

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

    • Медиана;
    • Мода;
    • Дисперсия;
    • Среднее;
    • Стандартное отклонение;
    • Стандартная ошибка;
    • Асимметричность и др.

    Рассмотрим, как работает данный инструмент на примере Excel 2010, хотя данный алгоритм применим также в Excel 2007 и в более поздних версиях данной программы.

    Читать еще:  Входной интервал в excel

    Подключение «Пакета анализа»

    Как уже было сказано выше, инструмент «Описательная статистика» входит в более широкий набор функций, который принято называть Пакет анализа. Но дело в том, что по умолчанию данная надстройка в Экселе отключена. Поэтому, если вы до сих пор её не включили, то для использования возможностей описательной статистики, придется это сделать.

    1. Переходим во вкладку «Файл». Далее производим перемещение в пункт «Параметры».

    В активировавшемся окне параметров перемещаемся в подраздел «Надстройки». В самой нижней части окна находится поле «Управление». Нужно в нем переставить переключатель в позицию «Надстройки Excel», если он находится в другом положении. Вслед за этим жмем на кнопку «Перейти…».

  • Запускается окно стандартных надстроек Excel. Около наименования «Пакет анализа» ставим флажок. Затем жмем на кнопку «OK».
  • После вышеуказанных действий надстройка Пакет анализа будет активирована и станет доступной во вкладке «Данные» Эксель. Теперь мы сможем использовать на практике инструменты описательной статистики.

    Применение инструмента «Описательная статистика»

    Теперь посмотрим, как инструмент описательная статистика можно применить на практике. Для этих целей используем готовую таблицу.

      Переходим во вкладку «Данные» и выполняем щелчок по кнопке «Анализ данных», которая размещена на ленте в блоке инструментов «Анализ».

    Открывается список инструментов, представленных в Пакете анализа. Ищем наименование «Описательная статистика», выделяем его и щелкаем по кнопке «OK».

    После выполнения данных действий непосредственно запускается окно «Описательная статистика».

    В поле «Входной интервал» указываем адрес диапазона, который будет подвергаться обработке этим инструментом. Причем указываем его вместе с шапкой таблицы. Для того, чтобы внести нужные нам координаты, устанавливаем курсор в указанное поле. Затем, зажав левую кнопку мыши, выделяем на листе соответствующую табличную область. Как видим, её координаты тут же отобразятся в поле. Так как мы захватили данные вместе с шапкой, то около параметра «Метки в первой строке» следует установить флажок. Тут же выбираем тип группирования, переставив переключатель в позицию «По столбцам» или «По строкам». В нашем случае подходит вариант «По столбцам», но в других случаях, возможно, придется выставить переключатель иначе.

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

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

    Далее, если вы хотите чтобы выводилась также итоговая статистика, то нужно установить флажок около соответствующего пункта. Также можно установить уровень надежности, поставив галочку около соответствующего значения. По умолчанию он будет равен 95%, но его можно изменить, внеся другие числа в поле справа.

    Кроме этого, можно установить галочки в пунктах «K-ый наименьший» и «K-ый наибольший», установив значения в соответствующих полях. Но в нашем случае этот параметр так же, как и предыдущий, не является обязательным, поэтому флажки мы не ставим.

    После того, как все указанные данные внесены, жмем на кнопку «OK».

    После выполнения этих действий таблица с описательной статистикой выводится на отдельном листе, который был нами назван «Итоги». Как видим, данные представлены сумбурно, поэтому их следует отредактировать, расширив соответствующие колонки для более удобного просмотра.

  • После того, как данные «причесаны» можно приступать к их непосредственному анализу. Как видим, при помощи инструмента описательной статистики были рассчитаны следующие показатели:
    • Асимметричность;
    • Интервал;
    • Минимум;
    • Стандартное отклонение;
    • Дисперсия выборки;
    • Максимум;
    • Сумма;
    • Эксцесс;
    • Среднее;
    • Стандартная ошибка;
    • Медиана;
    • Мода;
    • Счет.
  • Если какие-то из вышеуказанных данных для конкретного вида анализа не нужны, то их можно удалить, чтобы они не мешали. Далее производится анализ с учетом статистических закономерностей.

    Как видим, с помощью инструмента «Описательная статистика» можно сразу получить результат по целому ряду критериев, которые в ином случае рассчитывались с применением отдельно предназначенной для каждого расчета функцией, что заняло бы значительное время у пользователя. А так, все эти расчеты можно получить практически в один клик, использовав соответствующий инструмент — Пакета анализа.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    CFA — Симметрия и асимметрия в распределениях доходности

    Рассмотрим симметрию и ассиметрию, — важнейшие характеристики распределений, используемые при выборе оптимального портфеля и в управлении финансовыми рисками, — в рамках изучения количественных методов по программе CFA.

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

    Читать еще:  Excel сравнение двух таблиц на совпадение

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

    Если распределение доходности симметрично относительно его среднего значения, то каждая сторона распределения является зеркальным отражением другой. Таким образом, равные интервалы убытков и доходов показывают одинаковые частоты. Например, убытки от -5% до -3% происходят примерно с той же частотой, что и доходы от 3% до 5%.

    Одним из наиболее важных распределений является нормальное распределение, изображенное на рисунке ниже. Это симметричное распределение в форме колокола играет центральную роль в модели среднего отклонения (англ. ‘mean-variance model’), которая используется для выбора портфеля. Оно также широко используется в управлении финансовыми рисками.

    Нормальное распределение (англ. ‘normal distribution’) имеет следующие характеристики:

    • Его среднее значение и медиана равны.
    • Оно полностью описывается двумя параметрами — его средним значением и дисперсией.
    • Примерно 68% его наблюдений лежат между плюс и минус 1-м стандартным отклонением от среднего. 95% лежат между плюс и минус 2-мя стандартными отклонениями; и 99% лежат между плюс и минус 3 стандартными отклонениями.

    Распределение, которое не является симметричным, называется скошенным или ассиметричным.

    Распределение доходности с положительной скошенностью (положительно ассиметричное) часто приносит небольшие убытки и несколько экстремальных доходов.

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

    Свойства асимметричных распределений.

    Показанное на графике положительно скошенное распределение имеет длинный хвост с правой стороны. Отрицательно скошенное распределение имеет длинный хвост на левой стороне.

    Для положительно скошенного унимодального распределения мода меньше медианы, а медиана меньше среднего. Для унимодального распределения с отрицательной скошенностью среднее значение меньше медианы, а медиана что меньше моды.

    Инвесторов привлекает положительная ассиметричность, поскольку средняя доходность находится выше медианы. Вместе с тем, положительная ассиметричность (относительно среднего значения) означает небольшие, хотя и частые, убытки при крупных, но менее частых доходах.

    Ассиметрия или скошенность (англ. ‘skewness’ или ‘skew’) — это термин, означающий статистическую меру ассиметрии. Как и дисперсия, асимметрия вычисляется с использованием отклонения каждого наблюдения от его среднего значения.

    Асимметрия (иногда упоминаемая как относительная асимметрия) вычисляется как среднее кубическое отклонение от среднего, деленное на стандартное отклонение в кубе, что делает меру независимой от шкалы.

    Здесь мы обсуждаем обычный коэффициент асимметрии. В некоторых учебниках представлен коэффициент асимметрии Пирсона, равный 3 * (Среднее — Медиана) / Стандартное отклонение, который имеет недостаток, заключающийся в необходимости вычисления медианы.

    Симметричное распределение имеет асимметрию равную 0, положительно асимметричное распределение имеет положительную асимметрию, а отрицательно асимметричное распределение имеет отрицательную асимметрию.

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

    Чтобы сумма была положительной, убытки должны быть небольшими и вероятными, а доходы менее вероятными, но более экстремальными. Следовательно, если асимметрия положительна, средняя величина положительных отклонений больше, чем средняя величина отрицательных отклонений.

    Простой пример показывает, что симметричное распределение имеет показатель асимметрии, равный 0.

    Предположим, у нас есть следующий ряд значений: 1, 2, 3, 4, 5, 6, 7, 8 и 9. Среднее равно 5, а отклонения составляют -4, -3, -2, -1, 0, 1, 2, 3 и 4. Возведение отклонений в куб дает -64, -27, -8, -1, 0, 1, 8, 27 и 64, что в сумме равно 0.

    Числитель асимметрии (и, следовательно, сама асимметрия), таким образом, равен 0, что подтверждает наше утверждение. Ниже приведена формула для вычисления асимметрии по выборке.

    Формула выборочного коэффициента асимметрии.

    Выборочный коэффициент ассиметрии (англ. ‘sample skewness’, ‘sample relative skewness’), SK, рассчитывается по формуле:

    ( S_K = left [ right ] ^n (X_i — overline X)^3 over s^3> ) (формула 17),

    где n — количество наблюдений в выборке, а s — стандартное отклонение выборки.

    Выражение n / [(n — 1) (n — 2)] в формуле 17 корректирует смещение вниз в небольших выборках.

    Алгебраический знак коэффициента указывает направление ассиметриии (скошенности), при этом отрицательный SK указывает на отрицательно скошенное распределение, а положительный SK указывает на положительную ассиметрию. Обратите внимание, что когда n становится большим, формула сводится к среднему кубическому отклонению:

    Для выборки размером 100 или более наблюдений, с нормальным распределение, коэффициент асимметрии ± 0,5 будет считаться необычно большим.

    Таблица 27 показывает несколько сводных статистических показателей для годовой и месячной доходности S&P 500.

    Ранее мы обсуждали среднеарифметическую доходность и стандартное отклонение доходности, и а в следующей теме мы кратко обсудим эксцесс.

    Ссылка на основную публикацию
    Adblock
    detector