Дисперсионный анализ в 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 применяются для определения основных статистических характеристик?