Применение описательной статистики в Microsoft Excel - TurboComputer.ru
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд (пока оценок нет)
Загрузка...

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Обзор встроенных средств Excel для статистического анализа данных

    Основными средствами анализа статистических данных в Excel являются статистические процедуры надстройки Пакет анализа (Analysis ToolРак) и статистические функции библиотеки встроенных функций. Основные сведения обо всех этих средствах имеются в электронной справочной системе Excel.

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

    Статистические процедуры Пакета анализа

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

    В Пакет анализа входят следующие статистические процедуры:

    1. генерация случайных чисел (Random number generation);
    2. выборка (Sampling);
    3. гистограмма (Histogram);
    4. описательная статистика (Descriptive statistics);
    5. ранги персентиль (Rank and percentile);
    6. двухвыборочный z-тест для средних (z-Test: Two Sample for Means);
    7. двухвыборочный t-тест для средних с одинаковыми дисперсиями (t-Test: Two-Sample Assuming Equal Variances);
    8. двухвыборочный t-тест для средних с различными дисперсиями (t-Test: Two-Sample Assuming Unequal Variances);
    9. парный двухвыборочный t-тест для средних (t-Test: Paired Two Sample for Means);
    10. двухвыборочный F-тест да я дисперсий (F-Test: Two Sample for Variances);
    11. коварнация (Covariance);
    12. корреляция (Correlation);
    13. рецессия (Regression);
    14. однофакторный дисперсионный анализ (ANOVA: Single Factor);
    15. двухфакторный дисперсионный анализ без повторений (ANOVA: Two Factor Without Replication);
    16. двухфакторный дисперсионный анализ с повторениями (ANOVA: Two Factor With Replication);
    17. скользящее среднее (Moving Average);
    18. экспоненциальное сглаживание (Exponential Smoothing);
    19. анализ Фурье (Fourier Analysis).

    Для доступа к процедурам Пакета анализа необходимо в меню Сервис (Tools) щелкнуть указателем мыши на строке Анализ данных (Data Analysis). Откроется диалоговое окно с соответствующим названием, в котором перечислены процедуры статистического анализа данных (рис. 1).

    Рис.1. Диалоговое окно Анализ данных

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

    Рис.2. Диалоговое окно процедуры Описательная статистика

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

    К числу общих для большинства процедур элементов управления относятся:

    • поле ввода Входной интервал (Input Range). В это поле вводится ссылка на диапазон, содержащий статистические данные, подлежащие обработке. Входной диапазон может быть столбцом пли группой столбцов (строкой или группой строк);
    • переключатель Группирование (Grouped By). В том случае, когда входной диапазон представляет собой столбец или группу столбцов, переключатель устанавливается в положение по столбцам (Columns). Если же входной диапазон представляет собой строку или группу строк, то переключатель устанавливается в положение по строкам (Rows). Более точным названием этого переключателя было бы название Расположение;
    • флажок Метки (Labels in First Row). Флажок устанавливается в тех случаях, когда первая строка (первый столбец) входного диапазона содержит заголовки. Если такие заголовки отсутствуют, флажок Метки не устанавливают. При этом Excel автоматически создает и выводит на экран стандартные названия для данных выходного диапазона (Столбец1, Столбец2,… или Строка 1. Строка2,…);
    • переключатели Выходной интервал/Новый рабочий лист/Новая книга (Output Range/New Worksheet/New Workbook). Эти переключатели определяют место вывода таблицы, содержащей результаты реализации статистической процедуры. В группе может быть выбран только одни переключатель.
    Читайте также:  Функция ВПР в программе Microsoft Excel

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

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

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

    Эффективным и очень удобным в использовании средством парного регрессионного анализа и анализа временных рядов является процедура Добавить линию тренда (Add Trendline), входящая в комплекс графических средств Excel.

    Статистические функции библиотеки встроенных функций Excel

    Табличный процессор Excel имеет библиотеку встроенных функции рабочего листа (Worksheet function). Одним из разделов этой библиотеки является раздел Статистические функции. В этот раздел входят 83 функции, предназначенные для решения некоторых наиболее востребованных задач теории вероятностей и математической статистики.

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

    Когда в качестве какого-либо аргумента встроенной статистической функции введен текст, функция выдает сообщение об ошибке #ЗНАЧ! (#VALUE!). Если в качестве аргумента, который по определению должен быть целым числом, введено число не целое, Excel использует в качестве аргумента целую часть этот числа. Никакие сообщения об этом «несанкционированном округлении» на экран не выводятся.

    Описательная статистика на базе пакета анализа данных Excel

    Краткая характеристика встроенного пакета анализа данных

    Предполагается, что студент обладает навыками работы в операционной системе Microsoft Windows и Microsoft Excel. Общий вид экрана при работе в Microsoft Excel представлен на рис. 2.2.

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

    Обращение к средствам анализа данных. Средства, которые включены в пакет анализа данных, доступны через команду Анализ данных меню Сервис. Если этой команды нет в меню, необходимо загрузить надстройку Пакет анализа. Для этого надо щелкнуть по кнопке Офис, далее – по

    2. Вариационные ряды и их статистические характеристики

    кнопке Параметры Excel, выбрать Надстройки, в нижней части окна, в поле Управление, выбрать Надстройки Excel, щелкнуть по кнопке Перейти, поставить галочку напротив Пакета анализа (рис. 2.3, я). На вкладке Данные в группе Анализ появится команда Анализ данных (рис. 2.3, б).

    Рис. 2.2. Общий вид экрана при работе в Microsoft Excel

    Рис. 2.3. Диалоговое окно анализа данных

    Дисперсионный анализ. Существует несколько видов дисперсионного анализа. Требуемый вариант выбирается с учетом числа факторов и имеющихся выборок из генеральной совокупности. Однофакторный дисперсионный анализ используется для проверки гипотезы о сходстве средних значений двух или более выборок, принадлежащих одной и той же генеральной совокупности. Этот метод распространяется также на тесты для двух средних (к которым относится, например, /-критерий). Двухфакторный дисперсионный анализ с повторениями представляет собой более сложный вариант однофакторного анализа с несколькими выборками для каждой группы данных. Двухфакторный дисперсионный анализ без повторения представляет собой двухфакторный анализ дисперсии, не включающий более одной выборки на группу. Используется для проверки гипотезы о том, что средние значения двух или нескольких выборок одинаковы (выборки принадлежат одной и той же генеральной совокупности). Этот метод распространяется также на тесты для двух средних, такие как /-критерий.

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

    Ковариационный анализ. Ковариация является мерой связи между двумя диапазонами данных. Используется для вычисления среднего произведения отклонений точек данных от относительных средних. Ковариационный анализ дает возможность установить, ассоциированы ли наборы данных по величине, т. е., большие значения из одного набора данных связаны с большими значениями другого набора (положительная ковариация) или, наоборот, малые значения одного набора связаны с большими значениями другого (отрицательная ковариация), или данные двух диапазонов никак не связаны (ковариация близка к нулю). Вычисления ковариации для отдельной пары данных производятся с помощью статистической функции КОВАР.

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

    Экспоненциальное сглаживание. Применяется для предсказания значения на основе прогноза для предыдущего периода, скорректированного с учетом погрешностей в этом прогнозе. При анализе используется константа сглаживания а, по величине которой определяется степень влияния на прогнозы погрешностей в предыдущем прогнозе. Для константы сглаживания наиболее подходящими являются значения от 0,2 до 0,3. Эти значения показывают, что ошибка текущего прогноза установлена на уровне от 20 до 30 % ошибки предыдущего прогноза. Более высокие значения константы ускоряют отклик, но могут привести к непредсказуемым выбросам. Низкие значения константы могут привести к большим промежуткам между предсказанными значениями.

    Двухвыборочный F-mecm для дисперсии. Двухвыборочный F-тест применяется для сравнения дисперсий двух генеральных совокупностей. Например, F-тест можно использовать для выявления различия в дисперсиях временных характеристик, вычисленных по двум выборкам.

    Анализ Фурье. Предназначается для решения задач в линейных системах и анализа периодических данных на основе метода быстрого преобразования Фурье (БПФ). Эта процедура поддерживает также обратные преобразования, при этом инвертирование преобразованных данных возвращает исходные данные.

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

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

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

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

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

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

    Читайте также:  Применение функции ПСТР в Microsoft Excel

    Т-тест. Этот вид анализа используется для проверки средних для различных типов генеральных совокупностей. Двухвыборочный ?-тест Стьюдента служит для проверки гипотезы о равенстве средних для двух выборок. Эта форма ^-теста предполагает совпадение дисперсий генеральных совокупностей и обычно называется гомоскедастическим ^-тестом. Двухвыборочный Етест Стьюдента используется для проверки гипотезы о равенстве средних для двух выборок данных из разных генеральных совокупностей. Эта форма Етеста предполагает несовпадение дисперсий генеральных совокупностей и обычно называется гетероскедастическим ^-тестом. Если тестируется одна и та же генеральная совокупность, используйте парный тест. Парный двухвыборочный ?-тест Стьюдента используется для проверки гипотезы о различии средних для двух выборок данных. В нем не предполагается равенство дисперсий генеральных совокупностей, из которых выбраны данные. Парный тест используется, когда имеется естественная парность наблюдений в выборках, например, когда генеральная совокупность тестируется дважды – до и после эксперимента. Одним из результатов теста является совокупная дисперсия (совокупная мера распределения данных вокруг среднего значения.

    Z-mecm. Двухвыборочный z-тест для средних с известными дисперсиями. Используется для проверки гипотезы о различии между средними двух генеральных совокупностей. Например, этот тест может использоваться для определения различия между характеристиками двух моделей теодолитов.

    Пример 2.3. На полиметаллическом руднике из 4 забоев взяты 70 проб из каждого забоя. Результаты химических анализов на содержание металла по пробам представлены в табл. 2.7.

    Требуется представить данные опробования в виде упорядоченного и интервального вариационных рядов.

    Применение пакета программы «EXCEL» в статистическом анализе данных

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

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

    Предусмотрены средства представления числовых данных в виде диаграммы, создания, сортировки и фильтрации списков, статического анализа данных и решения оптимизационных задач.

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

    В процессе анализа данных, как правило, присутствуют следующие основные этапы:

    • 1. Ввод данных
    • 2. Преобразование данных
    • 3. Визуализация данных
    • 4. Статистический анализ
    • 5. Представление результатов

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

    Инструменты анализа программы

    1. Дисперсионный анализ

    Пакет анализа включает в себя три средства дисперсионного анализа. Выбор конкретного инструмента определяется числом факторов и числом выборок в исследуемой совокупности данных. [5]

    2. Корреляционный и ковариационный анализ

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

    X, Y – множества значений случайных величин размерности m;

    M(X) – математическое ожидание случайной величины Х;

    M(Y) – математическое ожидание случайной величины Y.

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

    Ковариация зависит от единиц измерения исследуемых величин, что ограничивает ее применение на практике. Более удобным для использования в анализе является производный от нее показатель – коэффициент корреляции R, вычисляемый по формуле:

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

    Определение количественных характеристик для оценки тесноты взаимосвязи между случайными величинами в ППП EXCEL может быть осуществлено двумя способами:

    • · с помощью статистических функций КОВАР и КОРРЕЛ ;
    • · с помощью специальных инструментов статистического анализа.

    Если число исследуемых переменных больше 2, более удобным является использование инструментов анализа.

    • 3. Корреляция
    • 1. Выберите в главном меню тему “Сервис” пункт “Анализ данных”. Результатом выполнения этих действий будет появление диалогового окна “Анализ данных”, содержащего список инструментов анализа.
    • 2. Выберите из списка “Инструменты анализа” пункт “Корреляция” и нажмите кнопку “ОК” (рис.1). Результатом будет появление окна диалога инструмента “Корреляция”.
    • 3. . Заполните поля диалогового окна, как показано на рис. 2 и нажмите кнопку “ОК”.

    Вид полученной ЭТ после выполнения элементарных операций форматирования приведен на рис. 3.

    Рисунок 1 Список инструментов анализа (выбор пункта «Корреляция”)

    Рисунок 2. Заполнение окна диалога инструмента “Корреляция”

    Рисунок 3 Результаты корреляционного анализа

    Результаты корреляционного анализа представлены в ЭТ в виде квадратной матрицы, заполненной только наполовину, поскольку значение коэффициента корреляции между двумя случайными величинами не зависит от порядка их обработки. Нетрудно заметить, что эта матрица симметрична относительно главной диагонали, элементы которой равны 1, так как каждая переменная коррелирует сама с собой.

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

    Для проверки гипотезы о нормальном распределении случайной величины применяются специальные статистические критерии: Колмогорова-Смирнова, . В целом ППП EXCEL позволяет быстро и эффективно осуществить расчет требуемого критерия и провести статистическую оценку гипотез.

    Однако в простейшем случае для этих целей можно использовать такие характеристики распределения, как асимметрия и эксцесс. Для вычисления коэффициента асимметрии и эксцесса в EXCEL реализованы специальные статистические функции – СКОС () и ЭКСЦЕСС(). [8]

    4. Описательная статистика

    Инструмент “Описательная статистика” автоматически вычисляет наиболее широко используемые в практическом анализе характеристики распределений. При этом значения могут быть определены сразу для нескольких исследуемых переменных.

    Определим параметры описательной статистики. Для этого необходимо выполнить следующие шаги.

    • 1. Выберите в главном меню тему “Сервис” пункт “Анализ данных”. Результатом выполнения этих действий будет появление диалогового окна “Анализ данных”, содержащего список инструментов анализа.
    • 2. Выберите из списка “Инструменты анализа” пункт “Описательная статистика” и нажмите кнопку “ОК”. Результатом будет появление окна диалога инструмента “Описательная статистика”.
    • 3. Заполните поля диалогового окна, как показано на рис. 4 и нажмите кнопку “ОК”.

    Результатом выполнения указанных действий будет формирование отдельного листа, содержащего вычисленные характеристики описательной статистики для исследуемых переменных. Выполнив операции форматирования, можно привести полученную ЭТ к более наглядному виду (рис.5).

    Рисунок 4 . Заполнение полей диалогового окна “Описательная статистика”

    Рисунок 5. Описательная статистика для исследуемых переменных

    Вторая строка ЭТ содержит значения стандартных ошибок для средних величин распределений. Другими словами среднее или ожидаемое значение случайной величины М (Е) определено с погрешностью . [5]

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

    Мода – наиболее вероятное значение случайной величины (наиболее часто встречающееся значение в интервале данных). Для симметричных распределений мода равна математическому ожиданию. Иногда мода может отсутствовать. В данном случае ППП EXCEL вернул сообщение об ошибке. Таким образом, вычисление моды не представляется возможным.

    Эксцесс характеризует остроконечность (положительное значение) или пологость (отрицательное значение) распределения по сравнению с нормальной кривой. Теоретически, эксцесс нормального распределения должен быть равен 0. Однако на практике для генеральных совокупностей больших объемов его малыми значениями можно пренебречь. [22]

    Асимметричность (коэффициент асимметрии или скоса – s) характеризует смещение распределения относительно математического ожидания. При положительном значении коэффициента распределение скошено вправо, т.е. его более длинная часть лежит правее центра (математического ожидания) и обратно. Для нормального распределения коэффициент асимметрии равен 0. На практике, его малыми значениями можно пренебречь.

    Для вычисления коэффициента асимметрии используется статистическая функция СКОС (). Формула для проверки значимости показателя эксцесса задается аналогичным образом. Числителем этой формулы будет функция ЭКСЦЕСС (), а знаменателем соотношение, реализованное средствами ППП EXCEL.

    Оставшиеся показатели описательной статистики представляют меньший интерес. Величина “Интервал” определяется как разность между максимальным и минимальным значением случайной величины (численного ряда). Параметры “Счет” и “Сумма” представляют собой число значений в заданном интервале и их сумму соответственно. [8]

    Читайте также:  Способы абсолютной адресации в Microsoft Excel

    Последняя характеристика “Уровень надежности” показывает величину доверительного интервала для математического ожидания согласно заданному уровню надежности или доверия. По умолчанию уровень надежности принят равным 95%.

    5. Анализ данных

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

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

    ь Экспоненциальное сглаживание

    ь Анализ Фурье

    ь Двухвыборочный F-тест для дисперсий

    ь Гистограмма

    ь Скользящее среднее

    ь Проведение t-теста

    ь Двухвыборочный t-тест с одинаковыми дисперсиями

    ь Двухвыборочный t-тест с разными дисперсиями

    ь Парный двухвыборочный t-тест для средних

    ь Генерация случайных чисел

    ь Ранг и персентиль

    ь Двухвыборочный z-тест для средних

    6. Графический анализ данных

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

    Статистическая диаграмма- это особый способ наглядного представления и изложения с помощью геометрических знаков и других графических средств статистической информации с целью её обобщения и анализа. Основным и наиболее важным свойством статистических диаграмм является их наглядность. Непосредственная наглядность статистических диаграмм делает их более выразительными и наглядными. [22]

    При анализе статистических данных диаграммы могут использоваться для решения таких задач:

    • · Отображать распределение единиц статистической совокупности по значениям или разновидностям исследуемого признака;
    • · Характеризовать развитие изучаемых явлений во времени, их общую тенденцию развития, сезонность колебаний, абсолютную и относительную скорость их развития и изменения;
    • · Сравнивать размеры различных явлений, их разных частей, а также тенденцию их развития и изменения во времени и пространстве;
    • · Выявлять структуру изучаемых явлений и её изменения, т.е. структурные сдвиги;
    • · Устанавливать взаимозависимость между явлениями или их признаками, а также степень тесноты существующей между ними связи;
    • · Отображать степень распространения изучаемых явлений по той или иной территории и интенсивности этого распространения.

    Вычисление показателей описательной статистики в ms Excel

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

    Рассмотрим пример. В ходе текущего наблюдения за показателями заболеваемости с временной утратой трудоспособности обнаружилось, что на одном из филиалов предприятия была отмечена тенденция роста длительность временной нетрудоспособности. С целью оперативной проверки статистической значимости этой тенденции была методом случайного отбора была сделана выборка из 15 больничных листов с указанного филиала и 15 больничных листов, для контроля, из совокупности больничных листков работников другого филиала. При этом, в этих выборках возрастно-половой и профессиональный болевших был практически одинаков. Полученные исходные данные были занесены в MS Excel в два столбика: столбик А и столбик В. С помощью показателей описательной статистики необходимо провести сравнительный анализ этих данных.

    Порядок решения задачи в MS Excel:

    1.Введите в ячейки A1:A15 и В1:В15 исходные данные.

    2.Выполните команду из меню .

    Примечание: Если данная команда отсутствует, то необходимо с помощью команды из меню открыть окно диалога “Надстройки” и в нем установить флажок для компоненты “Пакет анализа“. После нажатия кнопки [OK] меню будет дополнено командой . Если появится сообщение, что выбранная надстройка не может быть найдена, то необходимо выполнить более полную инсталляцию пакета MS Excel на вашем компьютере.

    3.Выберите в появившемся диалоговом окне метод “Описательная статистика” и нажмите кнопку [OK].

    Рисунок 98. Окно «Описательная статистика».

    4.В окне “Описательная статистика” установите следующие параметры:

    Входной диапазон ($A$1:$В$15),

    Группирование (по столбцам),

    Метки (входной диапазон не содержит метки, то есть названий строк и столбцов),

    Уровень надежности =0,95,

    Выходной диапазон. Для выходного диапазона выберите ссылку на ячейку D1, расположенную в левом верхнем углу выходного диапазона. Полные размеры выходной области будут определены автоматически.

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

    5.После завершения настройки параметров нажмите кнопку [OK]. Результаты анализа принимают вид (Рисунок 99):

    Рисунок 99. Результаты вычисления показателей описательной статистики.

    Из полученных данных следует, что с вероятностью 0,95 действительная (генеральная) средняя продолжительность одного случая ЗВУТ в первой совокупности (Столбец 1) находится в интервале 17,3 2,54 дня. Во второй совокупности (Столбец 2) в интервале 12,71,47 дней. Как свидетельствуют эти результаты, а так же показатели дисперсии, стандартного отклонения и интервала данных, в столбце 1 заметно выше разброс значений, чем в столбце 2. При этом увеличение границ разброса произошло за счет увеличения максимального значения: 24 дня в столбце 1 по сравнению с 17 днями в столбце 2.

    Анализ статистических данных в MS Excel. Инструменты анализа: описательная статистика, корреляция.

    В состав электронных таблиц Microsoft Excel входит так называемый пакет анализа – набор инструментов, предназначенный для решения сложных статистических задач. Данный пакет производит анализ статистических данных с помощью макрофункций и позволяет, выполнив одно действие, получить на выходе большое количество результатов. В пакете анализа, имеющемся в Excel, среди прочих инструментов анализа имеется разделы «Описательная статистика» и «Корреляция».

    Инструмент «Описательная статистика» позволяет нам получить значительный перечень рассчитанных статистических характеристик для большого количества числовых рядов. С помощью инструмента «Корреляция» мы получаем корреляционную матрицу, содержащую все возможные парные коэффициенты корреляции. Для k рядов будет получено k (k – 1)/2 коэффициентов корреляции.

    Пакет анализа вызывается с помощью пункта меню Сервис – Анализ данных… Если этот пункт меню отсутствует, значит, пакет анализа не установлен. Для его установки надо вызвать пункт меню Сервис – Надстройки… и включить надстройку «Пакет анализа», ОК (см. рисунок 1).

    Рисунок 1. Диалоговое окно включения/выключения надстроек

    После включения надстройки «Пакет анализа» будет доступен пункт меню Сервис – Анализ данных… При его выборе появляется следующее диалоговое окно (рисунок 2).

    Рисунок 2. Диалоговое окно выбора инструмента для анализа данных

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

    Рисунок 3. Диалоговое окно инструмента «Описательная статистика»

    Рассмотрим работу инструмента анализа «Описательная статистика» на следующем примере. В процессе обследования группы школьников (n = 21) измерялись следующие показатели: рост, масса тела, динамометрия правой и левой руки, жизненная емкость легких, проба Штанге и проба Генчи. Результаты были занесены в таблицу (рисунок 4).

    Для получения статистических характеристик воспользуемся пакетом анализа, инструментом «Описательная статистика». В поле «Входной интервал» занесем диапазон ячеек В1:Н22. Так как выделенный входной интервал содержит заголовки столбцов, включаем флажок «Метки в первой строке». Для удобства работы в качестве места выхода результата выбираем «Новый рабочий лист». В качестве выводимых данных отметим флажками «Итоговая статистика» и «Уровень надежности: 95 %». Последний флажок позволит вывести параметры доверительного интервала с доверительной вероятностью 0,95. Полученный результат после небольшого форматирования будет выглядеть так, как показано на рисунке 5.

    Рисунок 4. Результаты обследования группы школьников

    Рисунок 5. Результат работы инструмента «Описательная статистика»

    После выбора инструмента «Корреляция» и нажатия ОК в диалоговом окне «Анализ данных» (рисунки 2, 6) появится еще одно диалоговое окно (рисунок 7), требующее ввода входных данных и места вывода результатов. Здесь достаточно в поле «Входной интервал» ввести диапазон ячеек, содержащих исходные данные. Можно указать диапазон с заголовками столбцов, в этом случае потребуется включить флажок «Метки в первой строке». Для указания выходного интервала достаточо указать только левую верхнюю ячейку диапазона. Результаты вычисления автоматически займут требуемое количество строк и столбцов в таблице.

    Рисунок 6. Диалоговое окно выбора инструмента для анализа данных

    Рисунок 7. Диалоговое окно инструмента «Корреляция»

    Рассмотрим работу инструмента анализа «Корреляция» на примере, представленном на рисунке 4.

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

    Рисунок 8. Корреляционная матрица

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

    Дата добавления: 2018-05-12 ; просмотров: 467 ;

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