CC BY
115
Союнов А.С., Прокопов С.П. Построение модели множественной регрессии в агроинженерии // Электронный научно-методический журнал Омского ГАУ. - 2016. -№4 (7) октябрь - декабрь. - URL http://e-journal.omgau.ru/index.php/2016-god/7/32-statya-2016-4/492-00237. - ISSN 2413-4066
УДК 631.171:004.9(571.13)
Союнов Алексей Сергеевич
Кандидат технических наук, доцент ФГБОУВО Омский ГАУ, г. Омск as.soyunov@omgau.org
Прокопов Сергей Петрович
Старший преподаватель ФГБОУ ВО Омский ГАУ, г. Омск sp. prokopov@omgau.org
Построение модели множественной регрессии в агроинженерии
Аннотация. Статья посвящена методики анализа организации технического сервиса предприятий Омской области в программе Microsoft Excel 2010 с построением уравнения множественной регрессии оптимизируемой величины - выручка хозяйства (тыс. руб.), в зависимости от таких параметров, как количество механизаторов в хозяйстве (чел./100 га), средний возраст тракторов (лет), энерговооруженность (кВт/100 га). В результате вычислений получена регрессионная модель, которая проверена по различным критериям адекватность и значимость коэффициентов модели.
Ключевые слова: множественная регрессия, критерий Фишера, критерий Стьюдента, фактор, оптимизируемая величина
При исследовании различных процессов для установления зависимости оптимизируемой величины (откликом Y) от переменных (факторов X) часто принимается линейная регрессия. Однако она не всегда позволяет с точностью описать процесс, и поэтому принимают другие виды регрессии. Наиболее из распространенных нелинейных взаимосвязей между двумя переменными является квадратичная зависимость. Для ее анализа предназначена модель квадратичной регрессии.
Материал будет проиллюстрирован сквозным примером: прогнозирование выручки в хозяйствах Омской области, в зависимости от организации технического сервиса тракторов. Представьте себе, что вы - директор агропромышленного предприятия и стремитесь оптимизировать производственные расходы на организацию технического сервиса техники и повысить выручку. В частности на получение большой выручки будут оказывать значительное влияние количество работников - механизаторов, которые обслуживают и работают на тракторах, средний возраст тракторов, а также энерговооруженность [1]. Построив модель множественной регрессии, позволяющую наиболее точно предсказать сумму выручки, которую получить хозяйство при работе некоторого числа механизаторов, среднего возраста тракторов и его энерговооруженности [2]. Она позволит выявить наиболее активно влияющие причины на получение хозяйством выручки. Как построить наиболее подходящую модель? С чего начать?
Модель квадратичной регрессии [3]:
У = Д, + Д Хи + рг + Цъ X2; + р, XI + р5 Х31 + р6 XI + (1)
+р7 ХиХ2 + Д Х1гХзг + Д, Х2гХ3г + Ь ХцХ2,Х31 + 8г где во - сдвиг, вь в3, в5 - коэффициенты линейных эффектов, в2, в4, вб - коэффициенты квадратичных эффектов, в7, в8, в9, в10 - коэффициенты взаимодействия линейных эффектов, £\ - случайная ошибка переменной У в /-ом наблюдении.
Модель квадратичной регрессии похожа на модель множественной регрессии с тремя переменными, за исключением того, что в ней присутствуют квадраты переменных, а также их взаимодействия. Как и в модели множественной регрессии, выборочные коэффициенты регрессии Ь0.. ,Ь10 представляют собой оценки параметров генеральной совокупности в0- • в10. Таким образом, можно сформулировать следующую квадратичную модель с тремя объясняющими переменными Х1, Х2, Х3 и зависимой переменной У (уравнение квадратичной регрессии):
У = Ьо + Ь Хи+Ь2 Х2 + Ьз Х2/ + Ь4 Х2 + Ь5 Хз, + Ьб х3 +, (2)
+Ь7 Х1,Х2, + Ь8 Х1,Х3, + Ь9 Х2,Х3, + Ь10 Х1,Х2,Х3,
где коэффициент Ь0 является сдвигом, коэффициенты Ь1, Ь3, Ь5 оценивают линейные эффекты, коэффициенты Ь2, Ь4, Ьб - квадратичных эффектов, а коэффициенты Ь7, Ь8, Ь9, Ь10 -взаимодействия линейных эффектов.
Вычисление коэффициентов регрессии и предсказание отклика. На основании статистических наблюдений за хозяйствами Омской области была создана выборка, состоящая из 20 значений выручки полученной хозяйствами за год (табл. 1).
Таблица 1
Выборка по 20 хозяйствам Омской области
Количество механиков, чел./100 га Возраст тракторов, лет Энерговооруженность, кВт/100 га Выручка, тыс. руб.
Xi X2 Хэ Y
0,158524283 23,8 201,719229 759,778
0,208309223 22,7 172,3868296 666,365
0,302536653 23,5 143,5633912 898,823
0,236061151 23,1 108,2326706 1145,492
0,251748252 10,4 243,5305235 1304,716084
0,208157803 11,4 191,9231022 875,4225108
0,201570863 8,5 162,2523337 789,2194342
0,178807432 8,8 147,9731306 975,8920936
0,305784422 8,4 222,1184065 1767,688779
0,24361949 23,9 191,697498 1918,585
0,215866163 6,4 241,2450227 1780,841878
0,136857412 8,7 162,564412 893,0715935
0,235264983 24,5 86,67657256 491,419
0,187204052 25,2 149,8971717 782,568
0,128328521 28,1 44,43808582 356,165
0,495896033 27,8 143,592635 450,308
0,323624595 28,6 288,9151287 2608,123
0,178959478 25,2 434,564054 857,395
0,197869102 26,8 121,5804846 812,572
0,192364605 27,2 94,16267407 243,267
Значения коэффициентов регрессии (b0...bl0) можно вычислить с помощью Пакета анализа Excel. Предварительно нужно создать еще семь колонок с недостающими значениями X?, X22 , X32 , X1X2 , X1X3 , X2X3 и X1X2X3 (рис. 1).
XI Х2 ХЗ Х1"2 Х2"2 Х3"2 Х12 Х13 Х23 Х123 Y
0.153524233 23.8 201,719229 0,025129948 566.44 40690.64733 3.77237794 31.9773961 4800.917649 761.0620233 759,778
0,203309223 22,7 172,3863296 0,043392733 515.29 29717,21902 4,72361937 35.9097666 3913.131032 315,1517021 666,365
0,302536653 23,5 143.5633912 0.091523427 552,25 20610.44729 7.10961136 43.4331379 3373.739693 1020.679916 398.823
0,2360611S1 23,1 103,2326706 0,055724367 533.61 11714,31099 5,45301259 25,5495233 2500,174691 590,1941155 1145,492
0,251743252 10,4 243,5305235 0.063377182 108.16 59307.11589 2,61313132 61.3083335 2532.717445 637.6071339 1304.71608
0,203157303 11,4 191,9231022 0,043329671 129.95 36334,47715 2,37299896 39,9502914 2137,923365 455,4333217 375,422511
0,201570863 8.5 162.2523337 0,040630313 72.25 26325,31973 1.71335233 32.7053429 1379.144836 277.9954142 739.219434
0.173807432 3.8 147.9731306 0.031972098 77.44 21396.04737 1,5735054 26.4536955 1302.163549 232,3365204 975.892094
0,305734422 8,4 222,1134065 0,093504113 70,56 49336,53652 2,56358914 67,9203486 1865,791615 570,5309278 1767,68878
0,24361049 23.9 191.697493 0,059350456 571,21 36747,93072 5,3225053 46.7012466 4531,570201 1116.159794 1918,535
0,215866163 6.4 241,2450227 0,0465982 40,96 53199,16097 1,33154144 52.0766374 1543,963145 333.2904793 1780,34133
0,136857412 8,7 162.564412 0,018729951 75.69 26427.13304 1.19065943 22.2431447 1414,310334 193.5588585 393.071594
0,235264033 24 5 36.676572Е6 0,055349612 600,25 7512,323231 5,76399203 20,3919623 2123,576023 499,6030774 491.419
0.137204052 25.2 149.3971717 0,035045357 635,04 22469.16203 4.71754212 23,061353 3777.403727 707.1462213 732,568
0,123323521 28,1 44,43803532 0,016463209 739,61 1974,743472 3,60603144 5,70267333 1243,710212 160,2451346 356,165
0,495896033 27,3 143,592635 0,245912375 772,34 20613.34484 13.7359097 71,2070131 3991.875254 1979.555102 450.303
0,323624595 28,6 233,9151237 0,104732379 817,96 33471,9516 9.25566343 93.5000417 3262,972631 2674,101191 2608,123
0,178959478 25.2 434.564054 0.032026495 635.04 183345,917 4.50977836 77.7693565 10951,01416 1959.787733 357.395
0,197869102 26,8 121,5804846 0,039152132 718,24 14731,31423 5,30239193 24,0570213 3253,356937 644,728171 312.572
0,192364605 27,2 94.16267407 0,037004141 739.84 8366,609183 5,23231725 18.1135656 2561,224735 492.6339342 243.267
Рисунок 1 - Расширенная таблица значений X], X2,X3 Далее запускаем Пакета анализа Excel следуя по следующему пути: В ленте меню переключаемся на вкладку Данные и выбираем Анализ данных (рис. 2).
ач-
а страницы Формулы Данные Рецензировг
татьи - Microsoft Excel ABBYY FineReader И
й О в & S3
Ms Access r^ Из Интернета ¿^j Из текста
Получеь
AL
Существующие подключения
(данных_I
||гпч |Щ|под1иш>ч»
¡^Свойства
Обновить Я| Сортиров*
всет • :пенить сбя:и а +
Очистить Повторить
П0ДКЛЮЧ1
Фильтр Текст по Удал1
.^Дополнительно столбцам дубли!
Проверка данных Ф Группировать'' Анализ данных §»■ Консолидация ч" Разгруппировать' "з Поиск решения Анализ "что если" ' Щ Промежуточный итог Работа с данными__Структура_гй|_Анализ_I
Рисунок 2 — Запуск Пакета анализа Excel
Откроется окно Анализ данных (рис. 3), где выбираем инструмент анализа
Регрессия.
пункт
"Нд.^з дэинь >:
S
Инструменты анализа
Ковариация
Описательная статистика
Экспоненциальное сглаживание
.Зеухвыборочный Р-тест для дисперсии
Анализ Фурье
Гистограмма
Скользящее среднее
Генерация случайный чисел
Ранг и персентиль
Регрессия
OK
Отмена
Справка
Рисунок 3 - Окно Анализ данных для выбора инструмента анализа В открывшемся окне Регрессия (рис. 4) выбираем Входной интервал Y: значения столбца Y из таблицы (рис. 1) вместе с обозначением.
выделяем
5К52:5ВД22
ш
5A52:5JS22
] Константа - ноль %
95
Регрессия
Вводные данные Входной интервал V:
Входной интервал X: Метки
]_] Уровень надежности:
Параметры вывода в Выходной интервал: Новый рабочий лист: Новая рабочая книга Остатки
Остатки [ ] График остатков
;_.! Стандартизованные остатки График подбора
5AS25
а ¡-^-Г
ОК
Справка
Нормальная вероятность
i График нормальной вероятности
Рисунок 4 - Окно инструмента Регрессия Также выбираем Входной интервал X (рис. 4): для чего из таблицы (рис. 1) выделяем значения всех столбцов с факторами, а также их квадраты и взаимодействия.
Ставим отметку Метки, чтобы обозначения факторов и оптимизируемой величины не принимались в расчете как числовые значения. После чего задаем Параметры вывода - в нашем случае выбрана ячейка на том же листе, где находиться расчетная таблица. Если выбрать пункт Новый рабочий лист, то результат анализа будет размещен на новом листе, которому можно будет задать имя в соответствующем окошке справа, в противном случае Excel вставит стандартное название Лист N, где последующий номер листа в книге. При выборе пункта Новая рабочая книга будет создана новая рабочая книга, которую можно будет сохранить под желаемым именем.
После задание всех входных данных и выбора параметров вывода, нажимаем кнопку
ОК и на листе отобразиться Вывод итогов (рис. 5)._
А В С D Е F G Н I
25 ВЫВОД ИТОГОВ
2 6
21 Рггрйгшонштя cm am ист и на
2£ Множественный R. 0,90553454)3
29 R-KBa^pai 0,319992745
30 Нормированный R-к 0,613334634
31 Ста н^а ртн а n ош и &к 364,3623253
32 Наблюдения 20
33
34 ДИСП&РСИОННЫЙ аьалHi
35 df SS MS F Значимость F
36 Регрессии 10 5442906,461 544290,6461 4,099793 0,022505715
37 Остаток э 1194343,072 132760,3414
3£ Итого 13 6637743,533
39 40 41
Козффициенть Cm андарт ная аш и бка Г-сгп(7тислпин(7 ^-Значение Нижние95% Верхние 95% Нижние 95,096 Верхние 95,0%
У-пересечение 1907,397943 3235,241279 0,230216343 0,323071 -16335,11336 20649,3153 -16335,12 20643,31535
42 XI -2934,32571 40033,10404 -0,074533954 0,942212 -33545,19376 37577,1474 -93545,1933 37577,14735
43 Х2 -151,137S2S 361,5730046 -0,417995032 0,635747 -969,0341003 666,303445 -969,034101 666,3034453
44 ХЗ -6,33703043 44,52652791 -0,142320337 0,339962 -107,0630345 94,3339736 -107,063035 94,33397357
45 XIЛ2 -24233,4463 14290,33343 -1,696136912 0,124095 -56565,54029 3033,64644 ■56565,5403 3033,646441
46 2,210097341 4,57652673 0,432920333 0,640632 -3,142724332 12,5629206 -3,14272433 12,56292056
47 ХЗЛ2 -0,01135106 0,012124364 -0,935706063 0,350034 -0,039373275 0,01547616 -0,03337327 0,015476157
4£ Ж12 394,7213469 1633,126035 0,241697112 0,314432 -3299,66591 4039,1096 -3233,66591 4039,109604
49 Х13 77,53501301 207,3271164 0,373074599 0,717723 -352,6025319 547,672613 -332,602532 547,67 2 6179
SO Х23 0,166933243 1,593502004 0,104462333 0,919093 -3,449079511 3,73304601 -3,44907951 3,733046007
51 Х123 -1,00055025 7,707320309 -0,129309745 0,399572 -13,4363523 16,43 57513 -13,4363523 16,4357513
Рисунок 5 - Результаты регрессионного анализа, полученные с помощью Пакета анализа Excel
На основании выполненного расчета в ячейках B41...B51 (рис. 5) представлены коэффициенты уравнения регрессии b0.. ,b10, подставив их уравнение (2), оно примет вид:
Y = 1907,39-2984,02X1; -24238,44X1 -151,13X2i + 2,21X2 -6,33X3i -0,0119X3 + (3) +394,72XhX2j + 77,53XhX3i + 0,166XaXa - W^X^
где Y - предсказанная выручка/-го хозяйства, Х1;, X2i, X3i - соответственно количество механиков, возраст тракторов и энерговооруженность в i-ом хозяйстве.
Коэффициент b0, представляет собой предсказанную выручку хозяйства при нулевом значении факторов X1, X2, X3 и является сдвигом отклика, равным 1907,39. Чтобы объяснить смысл коэффициентов bi...bi0, следует обратить внимание на их значение. Если перед коэффициентом стоит знак «+» значить влияние фактора или его взаимодействия положительно сказывается на оптимизируемой величине, то есть увеличение выручки хозяйства, а знак «-» об отрицательном влияние фактора или его взаимодействия. Из уравнения регрессии (3) видно, что на снижение выручки сильное влияние оказывает фактор - количество механиков (чел./100 га).
Проверка значимости квадратичной модели. Убедившись, что квадратичная модель адекватна исходным данным, можно проверить, существует ли статистически значимая зависимость между полученной выручкой/ и факторами X. Нулевая и альтернативная гипотезы формулируются следующим образом: Н0: ß1 = ß2= ... = ßi = 0(между откликом Y и объясняющими переменным Х. нет зависимости); Н1: ß1 Ф 0 и/или ß. Ф 0 (между откликом Y и объясняющими переменными Х. есть зависимость). Нулевую гипотезу можно проверить с помощью F-критерия:
F = MR = 544290-6461 = 4,099798
MST 132760,3414
(см. рис. 5, ячейки D36, D37, Е36)
Если уровень значимости а = 0,05, критическое значение F-распределения, имеющего 10 и 9 степеней свободы, =F^P(0,95;10;9) = 3,14 (рис. 6). Поскольку F = 4,099>F^ = 3,14 и р =1-F.РАСП(Е36;10;9;ИСТИНА) = 0,0225< 0,05, нулевая гипотеза Но отклоняется. Таким образом, между выручкой хозяйства и рассматриваемыми факторами существует статистически значимая зависимость.
0 3.14
I
Область Кршическо* Облаен,
ГфИнЧ-ИЦ значение 01КЛОЧОНИЯ гипотезы ГиЛИЁШ
Рисунок 6 - ^-распределение при уровне значимости 0,05, 10 степенях свободы в числителе
и 9 - в знаменателе
Оценка квадратичного эффекта. Для оценки вклада каждой поясняющей переменной используется ¿-критерий. Среднеквадратичная ошибка каждого коэффициента регрессии и соответствующие значения ¿-статистики приведены на рисунке 5. Чтобы проверить значимость квадратичного эффекта, на примере Х\2, сформулируем следующую нулевую и альтернативную гипотезы: Но - включение квадратичного эффекта не приводит к значительному увеличению точности модели (в2 = 0), Н}- включение квадратичного эффекта значительно повышает точность модели (в2 ф 0). ¿-статистика квадратичного эффекта (в2) = -1,696 (см. рис. 5, ячейка D45). Критические значения ¿-статистики, имеющего 9 степеней свободы при уровне значимости а = 0,05: ¿кр =СТЬЮДЕНТ.0БР.2Х(0,05;9) = 2,262 или ¿L =СТЬЮДЕНТ.0БР(0,025;9) = -2,262; ¿и =СТЬЮДЕНТ.0БР(0,975;9) = +2,262 (рис. 7).
rnwiw ГИПФ1Ш runüTiiau
Рисунок 7 - t-распределение при уровне значимости а = 0,05 и степеней свободы 9
Поскольку t = |-1,696|<tKp = 2,262 и р = 0,124> 0,05, квадратичный эффект является значимым и значительно повышает точность предсказания выручки, которую сможет получить хозяйство.
Коэффициент множественной смешанной корреляции в модели множественной регрессии позволяет оценить долю вариации переменной Y, объясняемой изменениями переменных факторов. В нашем случае этот коэффициент задается формулой:
2 SSR r =-
Y12 SST '
В нашем примере SSR = 5442906,461 (рис. 5, ячейка С36), SST = 6637749,533 (ячейка С38). Таким образом, rY212 = 0,82 (рис. 5, ячейка B29). Эта величина означает, что 82%
вариации выручки, которую получит хозяйство можно объяснить квадратичной зависимостью от выбранных переменных Xi.
Этические вопросы возникают, когда модель множественной регрессии используется для предсказания величин, находящихся под управлением пользователя. Ключевым моментом в этом случае являются намерения исследователя. Возможны варианты, когда статистик преднамеренно не исключает из модели множественной регрессии коллинеарные переменные и неправомерно применяет метод наименьших квадратов даже тогда, когда не выполняются необходимые условия.
Резюме. В заметке показано, как директор сельскохозяйственного производства может применять множественный линейный анализ для увеличения выручки хозяйства. Рассмотрена квадратичная модель множественной регрессии, включающая эффекты: факторов, квадратов факторов, а также их взаимодействия.
Ссылки на источники
1. Повышение эффективности технического сервиса сельскохозяйственной техники в Западной Сибири / А.П. Соломкин, О.В. Мяло, С.П. Прокопов // Вестник ВосточноСибирского государственного университета технологий и управления. - 2015. - № 2 (53). -С. 53-60.
2. Формирование системы технического обслуживания машинно-тракторного парка в сельском хозяйстве в современных условиях / А.П. Соломкин, Н.И. Мошкин, О.В. Мяло, С.П. Прокопов // Вестник Восточно-Сибирского государственного университета технологий и управления. - 2013. - № 5 (44). - С. 54-60.
3. Методологические основы совершенствования рабочих органов почвообрабатывающих и посевных машин: монография / И.Д. Кобяков, А.В. Евченко, Е.В. Демчук, А С. Союнов. - Омск: Изд-во ФГБОУ ВПО ОмГАУ им. П.А. Столыпина, 2012. - 144 с.
Aleksey Soyunov
Candidate of Technical Sciences, Associate Professor
FSBEI HE Omsk SA U, Omsk
as.soyunov@omgau.org
Sergey Prokopov
Senior Lecturer
FSBEI HE Omsk SA U, Omsk
sp. prokopov@omgau.org
Building a Multiple Regression Model in Agricultural Engineering
Abstract. The article is devoted to methods of analysis of the organization of technical service enterprises of the Omsk region in the program Microsoft Excel 2010 with the construction of multiple regression equation optimized values - revenue management (thousand rubles), Depending on parameters such as the number of machine operators in the sector (person / 100 ha), the average age of tractor (s) installed power (kW / 100 ha). As a result of calculations obtained by the regression model, which is tested on a variety of criteria adequacy and relevance of the model coefficients.
Keywords: multiple regression, Fisher's exact test, Student's t-test, factor value of the optimized.
