Тема: Решение задач описательной статистики средствами ms ex
| Вид материала | Решение |
- Решение задач описательной статистики средствами ms excel содержание, 164.81kb.
- I. Решение логических задач средствами алгебры логики 22 >II. Решение логических задач, 486.64kb.
- Конспект открытого урока по теме: "Решение логических задач средствами алгебры логики", 93.45kb.
- Предполагает комплекс мероприятий, нацеленных на выбор и эффективное использование, 280.88kb.
- Решение математических и экономических задач средствами matlab, 11.27kb.
- Решение задач математической статистики по теме «Проверка статистических гипотез», 728.89kb.
- Решение линейных уравнений Цель урока, 126.51kb.
- Урок. Тема: «Повторение и обобщение свойств азота и его соединений. Решение задач, 59.14kb.
- Элективный курс «Решение задач» Тема: «Молекулярно-кинетическая теория идеального газа», 63.54kb.
- Календарно-тематическое планирование элективного курса " методы решения физических, 107.87kb.
Решение задач описательной статистики средствами MS Excel
Выполнил:
Тема: Решение задач описательной статистики средствами MS Excel
ЗАДАНИЕ 1.
Даны результаты наблюдения за уровнем шумов приемника:
1,9 2,5 3,4 4,1 5,8 6,6 7,2 8,0 9,2 0,1 2,4 3,5 4,8
5,6 6,3 7,3 8,1 1,4 3,0 4,7 5,2 6,3 7,0 4,8 5,9 6,1
3,4 4,3 5,2 6,1 7,2 4,8 5,3 6,4 4,9 5,1 6,2 5,0 5,7
Выполнить расчет описательной статистики, сформировать таблицу частот, построить гистограмму распределения, полиномиальный тренд второго порядка с указанием аппроксимирующего уравнения и коэффициента достоверности аппроксимации. Выдвинуть предположение о модели распределения и проверить гипотезу по критерию хи-квадрат.
ОПИСАНИЕ МЕТОДИКИ ИССЛЕДОВАНИЯ
Анализ данных проведен в рамках описательной статистики с использованием средств Вставка функций и Мастер диаграмм MS Excel. Расчеты указанных в статистической таблице параметров описательной статистики, выполнены при помощи средства
Вставка функции. Применяемые расчетные формулы показаны на Рис.1:Рисунок 1.
Формулы для расчета характеристик описательной статистики.
-
СРЕДНЕЕ
СРЗНАЧ(A2:C14)
СРЕДНЕКВАДРАТИЧНОЕ ОТКЛОНЕНИЕ
СТАНДОТКЛОН(A2:C14)
ДИСПЕРСИЯ
ДИСПА(A2:C14)
МЕДИАНА
МЕДИАНА(A2:C14)
МОДА
МОДА(A2:C14)
АССИМЕТРИЯ
СКОС(A2:C14)
ЭКСЦЕСС
ЭКСЦЕСС(A2:C14)
НАИМЕНЬШЕЕ
МИН(A2:C14)
НАИБОЛЬШЕЕ
МАКС(A2:C14)
РАЗМАХ
G10-G9
КВАРТИЛЬ 1
КВАРТИЛЬ(A2:C14;1)
КВАРТИЛЬ 3
КВАРТИЛЬ(A2:C14;3)
КОЛ-ВО ВЫБОРОК
39
Далее была сформирована таблица частот исследуемой величины, выполнена группировка данных и расчеты с помощью средства
Вставить функцию: - выполнен расчет минимального числа интервалов по формуле ОКРУГЛ(5*LOG10(G14);0).
- вставлена формула для расчета ширины интервала группирования методом непосредственного ввода: G11/K2.
- аналогично введены формулы для вычисления правых границ интервалов: J7+$K$3.
- вставлена формула для расчета частот с применением функции массивов: выделяем диапазон ячеек K7:K14; выбираем функцию ЧАСТОТА из категории Статистические; выделяем мышью диапазон ячеек исходных данных А1:С14; выделяем мышью диапазон ячеек интервалов J7:J14 (клавиши Ctrl+Shift+Enter для фиксации функции массива).
Далее была построена гистограмма для исследуемой величины с применением мастера диаграмм: выделили диапазон ячеек с таблицей частот J6:L14. Используем инструмент Мастер диаграмм
/ на вкладке Нестандартные в поле Тип выбираем вариант График/Гистограмма 2. Проведена аппроксимация и сглаживание построенной в задании гистограммы при помощи построения линий полиномиального тренда второго порядка.
Полиномиальный тренд для ряда Частоты добавлен на гистограмму следующим образом: выделяем Гистограмму / щелчок правой кнопкой мыши по одному из столбиков ряда Частоты / Добавить линию тренда контекстного меню / в окне Линия тренда на вкладке Тип выбираем образец Полиномиальная /. Далее на вкладке Параметры устанавливаем флажки показывать уравнение на диаграмме, поместить на диаграмму величину достоверности аппроксимации.
Далее проведен анализ данных в рамках задачи проверки гипотезы о распределении при помощи критерия согласия хи-квадрат с использованием средств Вставка функций и Анализ данных MS Excel. Используя построенную таблицу частот и рассчитанные среднее и среднеквадратичное, а также стандартные встроенные функции, сформируем таблицу для расчета статистики хи-квадрат. В столбцах скорректированных теоретических и эмпирических частот выполнено объединение тех карманов, где значение теоретических частот менее 5. Это условие правильного применения критерия. Расчетная формула для числа степеней свободы распределения хи-квадрат определяется разностью числа карманов (с учетом их объединения) и числа 3.
РЕЗУЛЬТАТЫ ИССЛЕДОВАНИЯ И ИХ ИНТЕРПРЕТАЦИЯ.
Тема: Проверка гипотез в MS Excel. Параметрические и непараметрические методы.
ЗАДАНИЕ 2.
Пробы из чистого железа, полученного двумя различными методами, имели следующие точки плавления:
| 1-й метод | 1493 | 1519 | 1518 | 1512 | 1512 | 1514 | 1489 | 1508 | 1508 | 1494 |
| 2-й метод | 1509 | 1494 | 1512 | 1483 | 1507 | 1491 | | | | |
Проверить нулевую гипотезу, согласно которой оба метода дают железо, имеющую одну и ту же точку плавления.
ОПИСАНИЕ МЕТОДИКИ ИССЛЕДОВАНИЯ.
Для решения поставленной задачи, сначала был проведен анализ данных в рамках проверки гипотезы о принадлежности двух дисперсий одной генеральной совокупности (следовательно, их равенстве) по критерию Фишера с использованием средства Анализ данных MS Excel. Для этого была использована процедура проверки гипотезы: Сервис / Анализ данных / Двухвыборочный тест для дисперсии. В одноименном окне были указаны диапазоны ячеек для 1 и 2 выборок (B2:K2 и B3:G3). Введен уровень значимости 0,05 в поле Альфа. Указана верхняя левая ячейка размещения результатов в поле Выходной интервал (A6). Далее полученные результаты были проанализированы исходя из того, что по условиям критерия нулевая гипотеза отвергается, если значение F статистики Фишера больше верхнего критического или меньше нижнего.
Далее, с учетом полученных результатов для дисперсии был проведен анализ данных в рамках задачи проверки гипотезы о равенстве средних при неравных дисперсиях и объемах выборок по критерию Стъюдента с использованием средства Анализ данных MS Excel. Для этого была использована процедура проверки гипотезы: Сервис / Анализ данных / Двухвыборочный t-тест с различными дисперсиями. В одноименном окне были указаны диапазоны ячеек для 1 и 2 выборок (B2:K2 и B3:G3). Введен уровень значимости 0,05 в поле Альфа. Указана верхняя левая ячейка размещения результатов в поле Выходной интервал (A21). Далее полученные результаты были проанализированы исходя из того, что по условиям критерия нулевая гипотеза отвергается, если значение t-статистики Стъюдента по абсолютной величине больше верхней точки распределения или критического значения.
РЕЗУЛЬТАТЫ ИССЛЕДОВАНИЯ И ИХ ИНТЕРПРЕТАЦИЯ.
ВЫВОД. Окончательно можно сказать о том, что оба метода дают железо, имеющую одну и ту же точку плавления.
Тема: Регрессионный анализ в MS Exсel.
ЗАДАНИЕ 3.
Проверить гипотезу о наличии статистически значимой связи между затратами на рекламу и объемом реализации продукции. Исходные данные приведены в таблице:
| Объем реализации | 126 | 137 | 148 | 191 | 274 | 370 | 432 | 445 | 367 | 367 | 321 | 307 | 331 |
| Затраты на рекламу | 4 | 4,8 | 3,8 | 8,7 | 8,2 | 9,7 | 14,7 | 18,7 | 19,8 | 10,6 | 8,6 | 6,5 | 12,6 |
Сформировать линейную регрессионную модель и оценить ее адекватность.
ОПИСАНИЕ МЕТОДИКИ ИССЛЕДОВАНИЯ.
Для решения задачи сначала был проведен линейный регрессионный анализ: Сервис / Анализ данных / Регрессия / в одноименном окне указываем входные диапазоны B2:B12 для X и A2:A12 для Y, устанавливаем флажок метки / устанавливаем все флажки в разделах Остатки и Нормальная вероятность, указываем начальную ячейку выходного диапазона A17. Далее анализируем полученные результаты: в бланке расчета модели указаны значения оценок свободного члена (столбец Коэффициенты, строка Y-пересечение) и коэффициента пропорциональности (столбец Коэффициенты, строка X). Оцениваем информативность построенной линейной модели посредством анализа коэффициента множественной корреляции Множественный R в таблице Регрессионная статистика бланка результатов. Его значимость оценивается по критерию Фишера. Далее находим расчетное значение статистики, приведенное в таблице Дисперсионный анализ в столбце F строки Регрессия. Находим критическое значение по расчетной формуле FРАПОБР(0,05; 1;11). Если критическое значение меньше расчетного, коэффициент множественной корреляции считается значимым. Модель считается информативной, если расчетная статистика не менее, чем в 10 раз больше табличного значения критерия Фишера. Далее оцениваем качественно адекватность построенной модели посредством анализа сравнительного графика кривой модели и исходной кривой (График подбора) и поведения остатков (разности между предсказанными моделью значениями у и входными значениями из выборки).
РЕЗУЛЬТАТЫ ИССЛЕДОВАНИЯ И ИХ ИНТЕРПРЕТАЦИЯ.
ВЫВОДЫ.
- Уравнение для линейной регрессионной модели данной задачи имеет вид:
- Коэффициент множественной корреляции является значимым, так как расчетное значение статистики
больше критического значения 
. Данную модель нельзя считать информативной, потому что расчетная статистика только в 3 раза больше табличного значения критерия Фишера.
- Считаю, что построенная модель не является адекватной, что видно посредством анализа о графика кривой модели и исходной кривой (График подбора) и поведения остатков (разности между предсказанными моделью значениями у и входными значениями из выборки). Визуально степень отклонения модели от реального графика довольно значительна. Также сравнивая график остатков и график нормального распределения видно, что остатки не принадлежат к нормальному закону.