Ход работы. Исследовалась зависимость прибыли от затрат на модернизацию оборудования по 10 однотипным предприятиям: приведенных в значениях Y X

Исследовалась зависимость прибыли от затрат на модернизацию оборудования по 10 однотипным предприятиям: приведенных в значениях Y X

Была создана таблица исходных данных

 

 

Заполнили таблицу формулами

Таким образом, уравнение регрессии имеет вид

 

 

Коэффициент показывает, что если затраты на модернизацию оборудования увеличить, то прибыль предприятия в среднем не увеличится. Значение коэффициента можно интерпретировать как значение функции т.е. производятся затраты на модернизацию. Таким образом, этот коэффициент показывает, если есть затраты на модернизацию оборудования, то прибыль предприятия в среднем будет иметь значение в столбце

Далее рассчитываются коэффициенты регрессии, используя встроенные функции MS Excel. Для этого сформируем в диапазоне A18:B20 таблицу для вычислений.

Для диапазона A20:B20 мы должны установить формат ячеек

 

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

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

В окне Формат линии тренда на вкладке Параметры линии тренда выбираем тип линии Линейная. Включим опцию Показывать уравнение на диаграмме. В результате на диаграмме появиться вид теоретической кривой — тренда и ее уравнение.

Рассчитываем коэффициент корреляции «вручную» и с использованием встроенных функций MS Excel. Для этого мы сформируем в диапазоне C20:D22 таблицу для вычислений:

Для диапазона C22:D22 установим формат ячеек Числовой с отображением трех знаков после запятой. Значения рассчитанные «вручную» и с помощью встроенных функций, совпадают.

Коэффициент корреляции показывает, что связь между величинами является прямой и весьма высокой. 0,994r

Далее вычисляем коэффициент детерминации. Для этого в диапазоне G1:J16 сформируем структуру таблицы для вычислений.

Коэффициент детерминации у =0.994. Это означает, что для данной выборки значение получаемой прибыли (зависимой переменной) на 96.8% определяется затратами на модернизацию). Остальные 3.2% — влияние случайных факторов.

Для диапазонов G2:J11 и H12:H16 установим формат ячеек Числовой с отображением трех знаков после запятой.

Для проверки соотношения в ячейку H18 введите формулу =C22^2. Значения R^2и r^2 совпадают.

 

Итоговая таблица с вычислениями имеет вид:

 

 

Вывод: В ходе лабораторной работы научились находить коэффициент корреляции и детерминации; находить коэффициенты регрессии, строить уравнение регрессии.Таким образом, были получены новые знания в MS Excel.