ЛАБОРАТОРНАЯ РАБОТА N 6
ИСПОЛЬЗОВАНИЕ ПАКЕТА АНАЛИЗА В EXCEL
Цель работы: Изучить средства статистического анализа данных, входящих в Пакет анализа MICROSOFT EXCEL.
ОСНОВНЫЕ СВЕДЕНИЯ
Пакет анализа – это надстройка, предназначенная для статистического анализа данных. В пакет анализа входят следующие программы:
* Однофакторный дисперсионный анализ.
* Двухфакторный дисперсионный анализ с повторением.
* Двухфакторный дисперсионный анализ без повторений.
* Корреляция.
* Ковариация.
* Описательная статистика.
* Экспоненциальное сглаживание.
* Двухвыборочный F-тест для дисперсии.
* Анализ Фурье.
* Гистограмма.
* Скользящее среднее.
* Генерация случайных чисел.
* Ранг и персентиль.
* Регрессия.
* Выборка.
* Парный двухвыборочный t-тест для средних.
* Двухвыборочный t-тест с одинаковыми дисперсиями.
* Двухвыборочный t-тест с разными дисперсиями.
* Двухвыборочный z-тест для средних.
Установка пакета анализа необходимо выполнить команду Сервис\надстройки. Если пакет анализа установлен, в меню Сервис появляется команда Анализ данных. Для загрузки необходимой для расчета программы следует выполнить команду Сервис/ Анализ данных идалее выбрать программу из списка.
Описательная статистика.
Рассмотрим пример применения пакета анализа для сравнения двух групп -экспериментальной и контрольной. Из числа членов генеральной совокупности в случайном порядке сформированы две группы учащихся. В ходе эксперимента получены следующие баллы по учебному предмету: Результаты эксперимента (введены в электронную таблицу )
| А | В | |
| экспериментальная группа | контрольная группа | |
Рис. 1
Для получения статистических характеристик экспериментальной и контрольной групп можно использовать статистические функции, входящие в список функций Excel, или программу Описательная статистика, включенную в Пакет анализа.
 |
На рисунке 2 приведено диалоговое окно программы Описательная статистика, в которое введены параметры для рассматриваемого примера.
Рис. 2
Входной и выходной интервалы можно указать с помощью мыши.
Результаты работы программы после обработки данных программой Описательная статистика приведены на рисунке 3.
| экспериментальная группа | контрольная группа | ||
| Среднее | 13,63 | Среднее | 9,44 |
| Стандартная ошибка | 0,742 | Стандартная ошибка | 0,689 |
| Медиана | Медиана | ||
| Мода | Мода | ||
| Стандартное отклонение | 2,460 | Стандартное отклонение | 2,068 |
| Дисперсия выборки | 6,054 | Дисперсия выборки | 4,278 |
| Эксцесс | 0,427 | Эксцесс | 0,129 |
| Асимметричность | -0,1757 | Асимметричность | -0,1013 |
| Интервал | Интервал | ||
| Минимум | Минимум | ||
| Максимум | Максимум | ||
| Сумма | Сумма | ||
| Счет | Счет | ||
| Уровень надежности(95,0%) | 1,653 | Уровень надежности(95,0%) | 1,589 |
Рис. 3
Проверка статистических гипотез.
Метод статистических гипотез широко применяется в научной деятельности для оценки результатов эксперимента. Как правило, при проведении научного исследования приходится выполнять сравнительные оценки генеральных параметров по различиям, наблюдаемым между выборками. О преимуществе той или иной из сравниваемых групп обычно судят по разности между средними долями и другими выборочными показателями.
При проведении статистической обработки результатов эксперимента выдвигается нулевая гипотеза (Н0), которая сводится к тому, что разница между генеральными параметрами сравниваемых групп равна нулю и что различия, наблюдаемые между выборочными характеристиками, носят случайный характер. В другой, альтернативной гипотезе делается предположение о преимуществе нового метода.
Альтернативные гипотезы принимаются только тогда, когда опровергается нулевая гипотеза. Это возможно в тех случаях, когда различия в средних выборочных настолько значимы (статистически достоверны), что риск ошибки отвергнуть нулевую гипотезу и принять альтернативную не превышает одного из трех принятых уровней значимости статистического вывода:
* первый уровень – 5% (допускается риск ошибки вывода в пяти случаях их ста таких же экспериментов при строго случайном отборе в выборочную совокупность);
* второй уровень – 1% (допускается риск ошибиться только в одном случае из ста;
* третий уровень – 0,1% (допускается риск ошибиться только в одном случае из тысячи).
 |
Существует два вида статистических критериев - параметрические и непараметрические. Параметрические критерии служат для проверки гипотез о параметрах совокупностей, распределяемых по нормальному закону. Рассмотрим пример применения одного из параметрических критериев - t-критерия Стьюдента для сравнительной оценки средних величин, рассчитанных для контрольной и экспериментальной групп в рассматриваемом примере. Для выполнения расчета вызовем программ у Двухвыборочный t-тест с разными дисперсиями, входящую в Пакет анализа и введем данные в диалоговое окно. Для ввода исходного диапазона блок данных можно выделить непосредственно в таблице. Уровень значимости задается в поле Альфа вводом значения с клавиатуры, в поле Выходной интервал указывается адрес верхней левой ячейки области размещения выходных таблиц. Выходные таблицы могут быть размещены на том же рабочем листе, на другом листе или в другом файле.
Рис. 4
Результаты обработки данных приведены на рисунке 5.
| Двухвыборочный t-тест с различными дисперсиями | ||
| экспериментальная группа | контрольная группа | |
| Среднее | 13,63 | 9,44 |
| Дисперсия | 6,054 | 4,278 |
| Наблюдения | ||
| Гипотетическая разность средних | ||
| df | ||
| t-статистика | 4,029 | |
| t критическое двухстороннее | 2,101 |
Рис.5
Сравнение полученной t-статистики (4,029) с t табличным (2,100) свидетельствует о том, что есть основание принять альтернативную гипотезу о том, что учащиеся экспериментальной группы показывают более высокий уровень знаний.
В случаях, когда необходимо оценить эффективность проведенной экспериментальной работы в одной группе, используется парный двухвыборочный t-тест для средних.