Как сделать дисперсионный анализ в excel

ДИСПЕРСИОННЫЙ АНАЛИЗ В MS EXCEL

Лабораторная работа 4

Определение основных статистических характеристик и дисперсионный анализ в MS Excel

Теоретические сведения

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

Данный раздел математики имеет дело с массовыми явлениями и тесно связан с теорией вероятностей, так как базируется на ее математическом аппарате.

Цель статистического исследования – исследование соотношений между статистическим данными (описательная статистика) и использование результатов данных исследований для прогнозирования и принятия решений (аналитическая статистика).

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

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

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

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

Возможности MS Excel для анализа данных

В мастере функций Excel имеется ряд специальных функций, предназначенных для вычисления выборочных характеристик. Функция СРЗНАЧ вычисляет среднее арифметическое из нескольких массивов (аргументов) чисел. Аргументы число1, число2, . — это от 1 до 30 массивов для которых вычисляется среднее.

Функция МЕДИАНА позволяет получать медиану заданной выборки.

Функция МОДА вычисляет наиболее часто встречающееся значение в выборке.

Функция ДИСП позволяет оценить дисперсию по выборочным данным.

Функция СТАНДОТКЛОН вычисляет стандартное отклонение.

Задание 1. Определение основных статистических характеристик

Пример 1. Провести статистический анализ методом описательной статистики доходов населения в регионе 1 и регионе 2.

Рисунок 1. Определение основных статистических характеристик

1. Найти среднее значение, медиану, моду, стандартное отклонение результатов бега на дистанцию 100 м у группы студентов (с): 12,8; 13,2; 13,0; 12,9; 13,5; 13,1.

2. Определите основные статистические характеристики для данных измерений роста групп студенток: 164, 160, 157, 166, 162, 160, 161, 159, 160, 163, 170, 171.

3. Найти наиболее популярный туристический маршрут из четырех реализуемых фирмой, если за неделю последовательно были реализованы следующие маршруты: 1, 3, 3, 2, 1, 1, 4, 4, 2, 4, 1, 3, 2, 4, 1, 4, 4, 3, 1, 2, 3, 4, 1, 1, 3.

ДИСПЕРСИОННЫЙ АНАЛИЗ В MS EXCEL

1. Создать файл с исходными данными.

2. Запустить “Пакет анализа”.

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

В меню Сервис выберите команду Анализ данных. Если такая команда отсутствует в меню Сервис, то необходимо установить в Microsoft Excel пакет анализа данных.

Установка производится следующим образом. В меню Сервис выберите команду Надстройки. Если в списке надстроек нет пакета анализа данных, то нажмите кнопку “Обзор” и задайте диск, каталог и имя файла для надстройки “Пакет анализа”, или запустите программу установки Microsoft Excel. Установите флажок “Пакет анализа” (надстройки, установленные в Microsoft Excel, остаются доступными, пока не будут удалены).

Читать еще:  Как сделать из коробки машину

Выберите необходимую строку в списке “Инструменты анализа”.

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

3. Провести однофакторный дисперсионный анализ.

В меню Сервис выбираем команду Анализ данных.

В списке инструментов статистического анализа выбираем Однофакторный дисперсионный анализ (Рисунок 9).

Рисунок 9 – Выбор инструмента анализа

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

Рисунок 10 – Диалоговое окно однофакторного дисперсионного анализа

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

Группирование. Установите переключатель в положение “по столбцам” или “по строкам” в зависимости от расположения данных во входном диапазоне.

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

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

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

Новая книга. Установите переключатель, чтобы открыть новую книгу и вставить результаты анализа в ячейку А1 на первом листе в этой книге.

В результате обработки данных получили следующее:

Рисунок 11 – Результаты однофакторного дисперсионного анализа

“Счет” – число повторностей. “Сумма” – сумма значений показателя по строкам. “Дисперсия” – частная дисперсия показателя.

– Таблица ANOVA представляет результаты дисперсионного анализа однофакторного комплекса, в котором первая колонка “Источник вариации” содержит наименование дисперсий. Графа “SS” – это сумма квадратов отклонений, “df” – степень свободы, графа “MS” – средний квадрат, “F” – критерий фактического F – распределения. “P – значение” – вероятность того, что дисперсия, воспроизводимая уравнением, равна дисперсии остатков. Определяет вероятность того, что полученная количественная определенность взаимосвязи между факторами и результатом может считаться случайной. “F – критическое” – это значение F – теоретического, которое впоследствии сравнивается с F – фактическим.

4. Рассчитать эмпирическое корреляционное отношение и коэффициент детерминации. Сформулировать выводы.

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

1. Что изучает математическая статистика?

2. Чем отличается генеральная и выборочная совокупности?

Читать еще:  Ив роше сделать заказ по интернету

3. Что такое выборочная медиана?

4. Что такое выборочная мода?

5. Что называют дисперсией выборки? Что такое стандартное

6. Какие основные функции пакета MS Excel применяются для определения основных статистических характеристик?

Дисперсионный анализ вMs Excel

В программе MS Excel для статистического анализа данных имеется надстройка “Пакет анализа“, которая позволяет проводить дисперсионный анализ следующих видов:

однофакторный дисперсионный анализ,

двухфакторный дисперсионный анализ без повторений,

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

Однофакторный дисперсионный анализ.

Условием применения такого анализа является повторность данных.

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

Согласно таблице исследуемый фактор имеет 3 уровня: доменный, мартеновский и прокатный цеха. В каждой группе имеется только по 4 наблюдения – 1,2,3,4 заводы, которые рассматриваются как повторности наблюдений.

Рисунок 100. Исходные данные однофакторного комплекса

Для решения задачи в MS Excel:

1.Сформируйте таблицу с исходными данными ().

2.Выберите из меню .

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

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

входной интервал ($B$4:$D$7),

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

альфа (уровень значимости =0,05).

5.Для параметров вывода установите переключатель в положение “Выходной интервал” и укажите клетку с координатой (Е1).

6.После завершения настройки параметров нажмите кнопку [OK].

Диалоговое окно с заполненными исходными параметрами должно выглядеть следующим образом

Рисунок 101. Окно исходных параметров однофакторного дисперсионного комплекса

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

Рисунок 102. Результаты анализа однофакторного комплекса

Во второй части результатов MS Excel использует следующие обозначения:

SS – сумма квадратов,

df – степени свободы,

MS – средний квадрат (дисперсия),

F – F-статистика Фишера (фактическое значение),

p-значение – общая значимость результатов дисперсионного анализа данных, расположенных по столбцам;

F-критическое – критическое значение F-статистики (Фишера) при заданном ранее p=0,05.

Таким образом, сумма квадратов, обусловленная влиянием исследуемого фактора (межгрупповая сумма), равна 756,56 Остаточная сумма квадратов (внутригрупповая) равна 457,08. Соответствующие дисперсии межгрупповая (для исследуемого фактора) – 378,28. , остаточная, внутригрупповая – 55,79.

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

Есть основания отвергать нулевую гипотезу об отсутствии влияния рассмотренного фактора (условия и характер работы в разных цехах) на заболеваемость рабочих гастритом: т.к. выполняется неравенство FFкр., при котором величина значимости Р = 0,012. Для отрицания нулевой гипотезы она должна быть не более 0,05.

Выполнение дисперсионного анализа в Excel

Рассмотрим дисперсионный анализ на следующем примере: за месяц известны данные о выработке рабочего за время работы в первую и во вторую смены.

Таблица 2 – Исходные данные

Выработка рабочего, нормо-час

12,1; 11,1; 12,6; 12,9; 11,6; 13,1; 12,6; 12,4; 11,6; 17,3; 12,9; 11,6; 12,4

9,9; 11,4; 13,4; 10,4; 12,9; 12,6; 13,9; 13,4; 12,4; 9,9; 10,2; 11,2; 9,7

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

Читать еще:  Как сделать домик для черепахи

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

Таблица 3 – Промежуточные расчеты для проведения дисперсионного анализа

Средняя выработка, нормо-часы

Число смен в месяце

Сумма квадратов отклонений вариантов от групповой средней

Квадраты отклонений групповых средних от общей средней

Используя данные таблицы, рассчитаем и .

Число степеней свободы для расчета внутригрупповой дисперсии равно () 24 (26-2), а для расчета межгрупповой дисперсии число степеней свободы равно – 1 (2-1).

Рассчитаем значение критерия Фишера по следующей формуле:

В соответствии с числом степеней свободы для расчета внутригрупповой и межгрупповой дисперсий (24 и 1) в таблице F-распределения для б=5% находим Fтабл = 4.26.

При этом выдвигается две гипотезы. Нулевая гипотеза гласит о том, что различия выработки рабочего в первую и вторую смены несущественны. Альтернативная гипотеза: существуют существенные различия в значении выработки рабочего в первую и во вторую смены.

Так как расчетное значение критерия Фишера значительно меньше табличного значения критерия Фишера, то гипотеза о несущественности различия выработки рабочего в первую и вторую смены не опровергается, т.е. сменность не оказывает влияния на уровень выработки рабочего.

Для того, чтобы провести дисперсионный анализ в Excel, необходимо активировать команду «Анализ данных». Для этого проходится следующий путь: Сервис -> Надстройки -> Пакет анализа. После этого в меню «Сервис» появляется команда «Анализ данных» и выбирается команда «Однофакторный дисперсионный анализ».

Далее необходимо заполнить окно «Однофакторный дисперсионный анализ»:

«Входной интервал» – вводится ссылка на диапазон, содержащий анализируемые данные. Ссылка должна состоять не менее чем из двух смежных диапазонов данных, данные в которых расположены по строкам или столбцам.

«Группирование» – установите переключатель в положение. По столбцам или По строкам в зависимости от расположения данных во входном диапазоне.

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

«Альфа» – введите уровень значимости, необходимый для оценки критических параметров F-статистики. Уровень альфа связан с вероятностью возникновения ошибки типа I (опровержение верной гипотезы).

«Выходной диапазон» – введите ссылку на левую верхнюю ячейку выходного диапазона. Размеры выходной области будут рассчитаны автоматически, и соответствующее сообщение появится на экране в том случае, если выходной диапазон занимает место существующих данных или его размеры превышают размеры листа.

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

«Новая книга» – установите переключатель, чтобы открыть новую книгу и вставить результаты анализа в ячейку A1 на первом листе в этой книге.

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

Рисунок 2 – Пример заполнения окна «Однофакторный дисперсионный анализ»

Результаты расчетов однофакторного дисперсионного анализа представлены на рисунке 3.

Источники:

http://lektsii.org/10-4087.html

http://studfile.net/preview/2073870/page:60/

http://studbooks.net/2240913/matematika_himiya_fizika/vypolnenie_dispersionnogo_analiza_excel

Ссылка на основную публикацию
Статьи на тему: