Й способ (графический). Построение линии тренда.
Лабораторная работа №2. Обработка и оценка результатов регрессионного анализа исследовательских данных.
Цель работы.
Научиться использовать возможности MS Excel для проведения регрессионного анализа исследовательских данных.
Краткое изложение основных теоретических и методических аспектов работы.
Регрессионный анализ.
Цель регрессионного анализа – определить количественные связи между зависимыми случайными величинами. Одна из этих величин полагается зависимой и называется откликом, другие – независимые, называются факторами. Для установления степени зависимости междуоткликом и факторами используются вычисляемые величины ковариации и коэффициент корреляции. Если коэффициент корреляции по абсолютной величине близок к единице, то для построения зависимости используется линейная модель. Для других случаев используются более сложные нелинейные модели (например, полиномиальные и экспоненциальные). В данной работе изучим линейную модель.
Уравнение линейной регрессии имеет вид:
Y = a1X1 + a2X2 + …+ akXk, (1)
где а1, а2, …, аk – параметры, подлежащие определению методом наименьших квадратов (МНК).
Обычно находят первые два параметра, которые принято обозначать a и b. В этом случае уравнение линейной регрессии имеет вид Y = a * X + b.
Коэффициенты a и b вычисляются следующим образом (формулы 2-3):
|
|
|
(2)
(3)
где i – номер измерения,
xi и yi – значения переменных при i-том измерении,
n – число измерений при моделировании системы.
В среде MS Excel для нахождения модели регрессии (т.е., фактически коэффициентов a и b) можно использовать несколько способов:
– использовать встроенную функцию ЛИНЕЙН;
– графический способ – построение линии тренда на диаграмме с показом уравнения регрессии;
– инструмент Регрессия из Пакета анализа;
– использовать встроенную функцию СУММКВРАЗН и инструмент Поиск решения;
– использовать встроенные функции НАКЛОН (вычисляет коэффициент a) и ОТРЕЗОК (вычисляет коэффициент b).
Контрольные вопросы.
1. В чем цель регрессионного анализа?
2. Опишите уравнение линейной регрессии.
3. Какими способами можно найти модель регрессии в MS Excel? Коротко опишите эти способы.
Порядок выполнения задания.
1. Перед выполнением задания изучить теоретическую часть практикума и ответить на контрольные вопросы.
2. Сохранить новую книгу MS Excel.
3. В книге MS Excel выполнить задание со следующими условиями.
|
|
|
Таблица 1 – Исходные данные
| Номер месторождения | Себестоимость добычи 1 т нефти (по отношению к себестоимости по месторождению №1, доли единицы) | Глубина залегания продуктивного горизонта, км | ||
| Балдрян Артём | ||||
| 1 | 1,00 | 2,4 | ||
| 2 | 1,10 | 2,8 | ||
| 3 | 1,07 | 2,7 | ||
| 4 | 1,02 | 2,5 | ||
| 5 | 1,09 | 2,8 | ||
| 6 | 1,06 | 2,6 | ||
| 7 | 1,07 | 2,7 | ||
| 8 | 1,04 | 2,5 | ||
| 9 | 1,09 | 2,8 | ||
| 10 | 1,10 | 2,9 | ||
| Гадзалов Вадим | ||||
| 1 | 1,01 | 2,5 | ||
| 2 | 1,11 | 2,91 | ||
| 3 | 1,08 | 2,81 | ||
| 4 | 1,03 | 2,6 | ||
| 5 | 1,1 | 2,91 | ||
| 6 | 1,07 | 2,7 | ||
| 7 | 1,08 | 2,81 | ||
| 8 | 1,05 | 2,6 | ||
| 9 | 1,1 | 2,91 | ||
| 10 | 1,11 | 3,02 | ||
| Дармини Анас | ||||
| 1 | 1,02 | 2,52 | ||
| 2 | 1,12 | 2,94 | ||
| 3 | 1,09 | 2,84 | ||
| 4 | 1,04 | 2,63 | ||
| 5 | 1,11 | 2,94 | ||
| 6 | 1,08 | 2,73 | ||
| 7 | 1,09 | 2,84 | ||
| 8 | 1,06 | 2,63 | ||
| 9 | 1,11 | 2,94 | ||
| 10 | 1,12 | 3,05 | ||
| Козинцев Владимир | ||||
| 1 | 1,03 | 2,54 | ||
| 2 | 1,13 | 2,97 | ||
| 3 | 1,1 | 2,86 | ||
| 4 | 1,05 | 2,65 | ||
| 5 | 1,12 | 2,97 | ||
| 6 | 1,09 | 2,76 | ||
| 7 | 1,1 | 2,86 | ||
| 8 | 1,07 | 2,65 | ||
| 9 | 1,12 | 2,97 | ||
| 10 | 1,13 | 3,07 | ||
| Мирзаев Руслан | ||||
| 1 | 1,04 | 2,57 | ||
| 2 | 1,14 | 3 | ||
| 3 | 1,11 | 2,89 | ||
| 4 | 1,06 | 2,68 | ||
| 5 | 1,13 | 3 | ||
| 6 | 1,1 | 2,78 | ||
| 7 | 1,11 | 2,89 | ||
| 8 | 1,08 | 2,68 | ||
| 9 | 1,13 | 3 | ||
| 10 | 1,14 | 3,1 | ||
| Раджабов Магомед
| ||||
| 1 | 1,05 | 2,59 | ||
| 2 | 1,16 | 3,02 | ||
| 3 | 1,12 | 2,92 | ||
| 4 | 1,07 | 2,7 | ||
| 5 | 1,14 | 3,02 | ||
| 6 | 1,11 | 2,81 | ||
| 7 | 1,12 | 2,92 | ||
| 8 | 1,09 | 2,7 | ||
| 9 | 1,14 | 3,02 | ||
| 10 | 1,16 | 3,13 | ||
| Сафронов Станислав | ||||
| 1 | 1,06 | 2,62 | ||
| 2 | 1,17 | 3,05 | ||
| 3 | 1,13 | 2,94 | ||
| 4 | 1,08 | 2,73 | ||
| 5 | 1,16 | 3,05 | ||
| 6 | 1,12 | 2,83 | ||
| 7 | 1,13 | 2,94 | ||
| 8 | 1,1 | 2,73 | ||
| 9 | 1,16 | 3,05 | ||
| 10 | 1,17 | 3,16 | ||
| Татусь Олег | ||||
| 1 | 1,07 | 2,64 | ||
| 2 | 1,18 | 3,08 | ||
| 3 | 1,14 | 2,97 | ||
| 4 | 1,09 | 2,75 | ||
| 5 | 1,17 | 3,08 | ||
| 6 | 1,13 | 2,86 | ||
| 7 | 1,14 | 2,97 | ||
| 8 | 1,11 | 2,75 | ||
| 9 | 1,17 | 3,08 | ||
| 10 | 1,18 | 3,19 | ||
| Терикмурзаев Рустам | ||||
| 1 | 1,08 | 2,66 | ||
| 2 | 1,19 | 3,11 | ||
| 3 | 1,16 | 3 | ||
| 4 | 1,1 | 2,78 | ||
| 5 | 1,18 | 3,11 | ||
| 6 | 1,14 | 2,89 | ||
| 7 | 1,16 | 3 | ||
| 8 | 1,12 | 2,78 | ||
| 9 | 1,18 | 3,11 | ||
| 10 | 1,19 | 3,22 | ||
| Чараханов Ислам | ||||
| 1 | 1,09 | 2,69 | ||
| 2 | 1,2 | 3,14 | ||
| 3 | 1,17 | 3,02 | ||
| 4 | 1,11 | 2,8 | ||
| 5 | 1,19 | 3,14 | ||
| 6 | 1,16 | 2,91 | ||
| 7 | 1,17 | 3,02 | ||
| 8 | 1,13 | 2,8 | ||
| 9 | 1,19 | 3,14 | ||
| 10 | 1,2 | 3,25 | ||
|
|
|
Рассмотреть первые два из перечисленных способов нахождения модели регрессии. Все действия (в том числе форматирование таблиц) необходимо выполнять, опираясь на образец.
Пример выполнения задания.
Рассмотрим на примере первые два из перечисленных способов нахождения модели регрессии.
Таблица 2 – Исходные данные
| Год | Производительность труда буровой бригады, м/бригаду вгод | Средний стажработы рабочихв бригаде, годы |
| 2008 | 10 650 | 2,5 |
| 2009 | 11 200 | 3,5 |
| 2010 | 12 050 | 4,0 |
| 2011 | 13 500 | 5,0 |
| 2012 | 14 900 | 5,5 |
| 2013 | 13 100 | 5,0 |
| 2014 | 14 200 | 5,5 |
| 2015 | 16 300 | 6,5 |
| 2016 | 17 500 | 7,5 |
| 2017 | 18 200 | 8,0 |
Й способ. Функция ЛИНЕЙН.
В первом способе для получения коэффициентов а и b линейного уравнения регрессии Y = a * X + b, описывающего зависимость между исследуемыми показателями, воспользуемся статистической функцией ЛИНЕЙН. Для этого выделите две ячейки D16:E16 и выполните вставку функции ЛИНЕЙН с аргументами согласно рисунку1.
Рисунок 1 – Аргументы функции ЛИНЕЙН
Здесь «Известные_значения_y» – диапазон значений «Средний стаж работы рабочих в бригаде», «Известные_значения_x» – диапазон значений «Производительность труда буровой бригады». Нажмите комбинацию клавиш SHIFT+CTRL+ENTER.
Получаем следующие значения коэффициентов регрессии – a = 0,001 (ячейка D16), b = -4,044 (ячейка E16). В ячейку D17 введем уравнение Y = 0,001 * X - 4,044, чтобы продемонстрировать уравнение регрессии:
й способ (графический). Построение линии тренда.
1. Для получения уравнения регрессии построим корреляционное поле переменных X (Производительность труда буровой бригады) и Y (Средний стаж работы рабочих в бригаде).
2. Выделим диапазон ячеек В2:С11, запустим мастер диаграмм и выберем тип диаграммы – Точечная (в Excel 2007 выберем на панели инструментов «Вставка» кнопку «Точечная» и выберем подтип «Точечная с маркерами», после этого диаграмма будет создана и помещена на текущий лист, после чего ее можно будет дооформить). Задаем для диаграммы имя – «Корреляционное поле», название оси Х – «Производительность труда буровой бригады, м/бригаду в год», оси Y – «Средний стаж работы рабочих в бригаде, годы» (в Excel 2007 данные действия выполняются на вкладке «Макет» после выделения диаграммы – команды «Название диаграммы» и «Названия осей»). На последнем шаге мастера указываем место расположения – текущий лист.
3. Добавим линию тренда на точечный график (рисунок 2). Для этого необходимо выделить диаграмму и выполнить команду меню «Диаграмма/Добавить линию тренда» (в Excel 2007 на вкладке «Макет» выберите команду «Анализ» и далее «Линия тренда» и «Линейное приближение»), либо выполнить данную команду из контекстного меню «Добавить линию тренда…», щелкнув по любой точке графика правой кнопкой мыши. Линия тренда – графическое представление направления изменения ряда данных.
4. Выбираем тип тренда «Линейный», который используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением: Y = a * X + b, где a – угол наклона (в радианах) и b – координата пересечения оси абсцисс (оси Y).
Рисунок 2 – Диаграмма с линией и уравнением тренда
5. На вкладке Параметры устанавливаем флажки «Показать уравнение на диаграмме» и «Поместить на диаграмму величину достоверности аппроксимации R2». Щелкаем по кнопке ОК. Далее можно отформатировать эти уравнения, выделив их и в контекстном меню выбрав «Формат подписи линии тренда». R2 – это число от 0 до 1, которое отражает близость линии тренда к фактическим данным. Линия тренда наиболее соответствует действительности, когда значение близко к 1.
6. Сравниваем уравнение регрессии, полученное графическим методом, с уравнением, рассчитанным с помощью функции ЛИНЕЙН. Как видим, эти уравнения одинаковые.
Дата добавления: 2018-08-06; просмотров: 353; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!