Лабораторная работа №6




НазваниеЛабораторная работа №6
Дата публикации17.10.2016
Размер9,76 Kb.
ТипЛабораторная работа
Лабораторная работа №6:

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

^

Пример. Количество произведенных изделий и затраты


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

В ячейке В21 рассчитывается коэффициент корреляции по формуле:
=КОРРЕЛ(А2:А20;В2:В20) (Результат: -0,623).
Результат появления в имеющихся данных выброса нивелирует имеющуюся положительную связь. Выброс стал следствием пожара, который произошел на заводе. Значительная часть исходных материалов была уничтожена огнём – именно это и стало причиной резкого скачка затрат на этой неделе. Выпуск продукции резко упал из-за остановки производства.

На рисунке 2 отражены те же данные, но без выброса. Как видно, корреляция, рассчитанная в ячейке В22 положительная и тесная (r=0,869). Если к имеющимся данным добавить линию тренда и выбрать в диалоговом окне Формат линии трендаПараметрыПрогноз назад на 22 единицы, получим точку пересечения тренда с осью Y. Эта точка является параметром а для линейного тренда (также его называют «отрезком» или «сдвигом») и рассчитывается в ячейке F20 по формуле:
=ОТРЕЗОК(C2:C19;B2:B19) (Результат: 2272,07).


Рисунок 1 – Резко отклоняющееся значение нарушило корреляцию. Вместо того, чтобы выявить в целом взаимосвязь роста между объёмом произведённой продукции и затратами, коэффициент корреляции, r=-0,623, указывает на наличие не прямой, а обратной взаимосвязи, при которой более высоким объёмам производства соответствуют меньшие затраты.
Параметр b (коэффициент регрессии или «наклон») характеризует крутизну наклона линии регрессии и рассчитывается в ячейке F21:
=НАКЛОН(C2:C19;B2:B19) (Результат: 51,66).
В ячейке В20 рассчитывается среднее значение количества изделий по формуле:
=СРЗНАЧ(B2:B19) (Результат: 32,5),
а в ячейке С20 - среднее значение затрат по формуле:
=СРЗНАЧ(С2:С19) (Результат: 3951,06).


Рисунок 2 – Исходная совокупность данных без выброса иллюстрирует прямую взаимосвязь между объёмом произведённой продукции и затратами для «обычных» недель (без чрезвычайных происшествий). Коэффициент корреляции, r=0,869, в этом случае имеет положительное значение и приближается к 1, что указывает на тесную связь.
В ячейке В21 рассчитывается среднее квадратическое отклонение (стандартное отклонение) количества изделий по формуле:
=СТАНДОТКЛОН(B2:B19) (Результат: 6,555),
а в ячейке С21 – среднее квадратическое отклонение (стандартное отклонение) затрат по формуле:
=СТАНДОТКЛОН(С2:С19) (Результат: 389,613) .
Если выделить диапазон ячеек F22:G22 и ввести с помощью формулы массива следующее выражение:
{=ЛИНЕЙН(C2:C19;B2:B19)} (Результат: 51,66; 2272,07),
то таким образом рассчитаем одновременно оба параметра уравнения регрессии. Формула массива вводится постепенным нажатием клавиш CtrlShiftEnter.
Стандартная ошибка (Se) является приближённым показателем величины ошибок остатков для имеющихся данных, измеряется в тех же единицах, что и Y. Она показывает величину отклонения в большую и меньшую сторону от значений линии регрессии с определенной долей вероятности. Так, если ошибки прогноза имеют нормальное распределение, то можно ожидать, что примерно 2/3 точек данных будут находиться на расстоянии не более величины Se выше или ниже линии регрессии. При 95% вероятности значения будут сосредоточены вокруг линии регрессии на расстоянии ±2Se и при вероятности близкой к 1 - ±3Se.

На рисунке 3 стандартная ошибка прогноза рассчитывается в ячейке Н1 по формуле:
=СТОШYX(B2:B19;A2:A19) (Результат: 198,582).
Тренд (столбец С) рассчитывается по формуле:
{=ТЕНДЕНЦИЯ(B2:B19;A2:A19)}.
Нижняя и верхняя границы рассчитываются как разница и сумма тренда и стандартных ошибок прогноза соответственно. Рассчитаем границы с вероятностью 2/3 (около 67%).

Нижняя граница в ячейке D2:
=C2-$H$1 (Результат: 3210).
Остальные ячейки столбца D копируем.
Верхняя граница в ячейке Е2:
=C2+$H$1 (Результат: 3607).
Остальные ячейки столбца Е копируем.

Рисунок 3 – Стандартная ошибка прогноза (Se) показывает приблизительно ошибку какой величины можно допустить, когда вместо фактического значения Y используется прогнозируемое значение Y. Можно ожидать, что в случае обычной линейной связи примерно 2/3 точек данных будут находиться между верхней и нижней границами прогноза.
Чтобы построить диаграмму, как на рисунке 3 необходимо выполнить следующие действия:

  1. выделить диапазон ячеек А1:В19;

  2. выбрать ВставкаДиаграммаТочечная и оформить в соответствии с рисунком;

  3. ДиаграммаДобавить линию трендаЛинейная;

  4. ДиаграммаИсходные данныеРядДобавить;

  5. В качестве значения Х выбрать диапазон ячеек A2:A19, значения Yдиапазон ячеек D2:D19

  6. щёлкнув на диаграмме по появившимся ниже линии тренда точкам и выбрав ^ Формат рядов данных Линия Обычная Маркер Отсутствует, получите на диаграмме нижнюю границу прогноза.

Аналогично строится верхняя граница прогноза.

На рисунке 4 показан расчёт стандартных ошибок параметров уравнения регрессии. Введем следующие формулы:


в ячейку Е2: =СТОШYX(B2:B19;A2:A19)

в ячейку Н2: =СТОШYX(B2:B19;A2:A19)

в ячейку Е3: =1/СЧЁТ(B2:B19)

в ячейку Н3: =СТАНДОТКЛОН(A2:A19)

в ячейку Е4: =(СРЗНАЧ(A2:A19))^2

в ячейку Н4: =СЧЁТ(B2:B19)-1

в ячейку Е5: =(СТАНДОТКЛОН(A2:A19))^2

в ячейку Н5: =КОРЕНЬ(H4)

в ячейку Е6: =СЧЁТ(B2:B19)-1

в ячейку Н1: =H2/(H3*H5)

в ячейку Е7: =КОРЕНЬ(E3+E4/(E5*E6))




в ячейку Е1: =E2*E7






Рисунок 4 – Для расчёта доверительных интервалов параметров уравнения регрессии используются стандартные ошибки параметров a и b. Они показывают вероятное отклонение соответствующих параметров в генеральной и выборочной совокупностях.
Расчёт доверительных интервалов осуществляется вводом следующих формул:


в ячейку Е10: =ОТРЕЗОК(B2:B19;A2:A19)

в ячейку Н10: =НАКЛОН(B2:B19;A2:A19)

в ячейку Е11: =СТЬЮДРАСПОБР(0,05;16)

в ячейку Н11: =СТЬЮДРАСПОБР(0,05;16)

в ячейку Е12: =E10-E11*E1

в ячейку Н12: =H10-H11*H1

в ячейку Е13: =E10+E11*E1

в ячейку Н13: =H10+H11*H1


Функция СТЬЮДРАСПОБР позволяет определить t-статистику Стьюдента, которая придаёт доверительным интервалам вероятностный характер. В данном случае параметр вероятность, равный 0,05, говорит о 95% вероятности, а параметр степени_свободы, равный 16, о том, что из 18 степеней свободы 2 степени свободы теряются из-за двух параметров в уравнении регрессии.

В ^ Excel для расчёта параметров уравнения регрессии в Пакете анализа существует средство Регрессия. Его можно активировать следующим образом: выбрать СервисАнализ данныхРегрессия. Появится диалоговое окно, показанное на рисунке 5.

Рисунок 5 – Средство ^ Пакета анализа «Регрессия» позволяет рассчитать большинство параметров регрессионной статистики и дисперсионного анализа
Если заполнить диалоговое окно Регрессия так, как показано на рисунке, в новом рабочем листе будет выведен перечень основных показателей регрессионной статистики и дисперсионного анализа (рисунок 6).

В ячейке В4 рассчитан множественный коэффициент корреляции (множественный R). Это же значение показано на рисунке 2 – ячейка В22.

В ячейке В5 рассчитан множественный коэффициент детерминации (R-квадрат). Это же значение можно увидеть на диаграмме рисунка 2.

В ячейке В6 рассчитан скорректированный множественный коэффициент детерминации (нормированный R-квадрат), который содержит поправку на число степеней свободы и всегда меньше множественного коэффициента детерминации (R-квадрат).

Рисунок 6 – Результаты компьютерных вычислений для данных о производственных затратах и объёме производства продукции
В ячейке В7 рассчитана стандартная ошибка прогноза. Её значение соответствует результатам, полученным на рисунках 3 (ячейка Н1) и 4 (ячейки Е2 и Н2).

В ячейке В8 содержится количество наблюдений (объём изучаемой совокупности).

В ячейках В11:В14 (df) содержатся данные о количестве степеней свободы.

Одна степень свободы в ячейке В12 говорит о непредсказуемости направления связи (прямая или обратная), число 16 в ячейке В13 представляет количество наблюдений минус число параметров уравнения регрессии.

В ячейках С11:С14 (SSсумма квадратов) рассчитаны: объяснённая (факторная) – ячейка С12, остаточная – ячейка С13, и общая – ячейка С14, дисперсии.

В ячейках D11:D13 (MSсумма квадратов на одну степень свободы) рассчитаны объяснённая (факторная) и остаточная дисперсии на 1 степень свободы.

В ячейке Е12 рассчитана F-статистика Фишера (путём деления ячейки D12 на ячейку D13), а в ячейке F12 её значимость. Полученные данные позволяют с очень высокой степенью вероятности говорить о значимости построенной регрессионной модели. Лишь в 28 случаях из 10000000 модель не значима.

В ячейках В17:В18 рассчитаны коэффициенты уравнения регрессии, они равны соответствующим значениям на рисунках 2 и 4. В ячейках С17:С18 рассчитаны стандартные ошибки, соответствующие значениям Е1 и Н1 на рисунке 4.

Если разделить коэффициент уравнения регрессии на стандартную ошибку, то будет получено значение t-статистики. Так, если В17 разделить на С17, получим ячейку D17. Значения ячеек D17:D18 превосходят табличные значения t-статистики. Это означает, что коэффициенты уравнения регрессии значимо отличаются от нуля. Р-значения в ячейках Е17:Е18 говорят об очень незначительной вероятности получения нулевого результата.

В ячейках F17:G18 содержатся доверительные интервалы коэффициентов уравнения регрессии при 95% уровне вероятности, которые совпадают с данными рисунка 4.

Похожие:

Лабораторная работа №6 iconЛабораторная работа №1 Знакомство с интегрированной средой. Компоновка,...
Целью работы является освоение инструментальных средств создания и отладки программ на языке ассемблера
Лабораторная работа №6 iconЛабораторный практикум бо, ко. Перечень Работ. Лабораторная Работа №1
Лабораторная Работа №1 -расчет для конкретного налога для предприятия бюджетной организации программа
Лабораторная работа №6 iconЛабораторная работа: «Выявление изменчивости у особей одного вида»...
Информация для учащихся ( где можно найти учебный материал для подготовки к к/р или л/р)
Лабораторная работа №6 iconЛабораторная работа 2
Лабораторная работа Решение рациональных уравнений. Решение нелинейных уравнений
Лабораторная работа №6 iconЛабораторная работа №1 Динамика движения объекта в графическом режиме....
Как выбрать вариант задания: смотрите свой номер по журналу, если № больше количества вариантов, то делите номер на количество вариантов....
Лабораторная работа №6 iconЛабораторная работа «Работа в Windows c помощью основного меню. Использование технологии ole»
Запишите размер папки, выраженный в Мб (мегабайтах) в текстовый редактор блокнот
Лабораторная работа №6 iconExcel 1 Лабораторная работа ехсе L 1 Построение таблицы. Работа с формулой
Например, выполним расчет белков для яйца. Для этого установите курсор на ячейке G9
Лабораторная работа №6 iconЛабораторная работа Работа с ресурсами в сети
В этой лабораторной работе демонстрируются простейшие способы организации работы с общими файлами в сетях, позволяющие даже неподготовленным...
Лабораторная работа №6 iconЛабораторная работа №5. Тема «Текстовый редактор Word. Работа в редакторе формул.»
Редактор формул Microsoft Equation используется для создания сложных математических формул
Лабораторная работа №6 iconЛабораторная работа №20 положение о расследовании и учете несчастных случаев на производстве

Вы можете разместить ссылку на наш сайт:
Школьные материалы


При копировании материала укажите ссылку © 2013
контакты
dopoln.ru
Главная страница