Цель: По опытным данным построить уравнение множественной нелинейной регрессии и определить его характеристики.
Рассмотрим случай, когда нелинейное уравнение с помощью преобразования данных можно привести к линейному виду. В связи с этим, данная лабораторная работа будет аналогична предыдущей.
Рассмотрим следующий пример.
ПРИМЕР. Предприятие выпускает продукцию, количество которой за месяц Y (тыс. шт.) зависит от затрат материальных ресурсов (т.), трудозатрат (тыс. час.) и энергозатрат (млн. кВт). При расширении производства наблюдалась следующая эмпирическая зависимость между выпуском Y и затратами ресурсов .
Х1 | ||||||||||||||
Х2 | ||||||||||||||
Х3 | ||||||||||||||
Y | 45,0 | 50,3 | 54,1 | 55,1 | 60,8 | 65,6 | 68,8 | 66,6 | 73,2 | 81,9 | 91,8 | 86,1 | 83,1 | 93,1 |
Из теории производственных функций известно, что зависимость результирующего признака (функции откликов) от факторов имеет вид . Вводим исходные данные вместе с подписями в ячейки А1-О4. Чтобы привести уравнение к линейному виду нужно прологарифмировать уравнение . Вводим вместо исходных данных их логарифмы. Для этого в ячейки А5-А8 вводим подписи «Ln X1», «Ln X2», «LnX3», «LnY». Ставим курсор в ячейку В5 и вводим функцию LN (категория математические) с аргументом «Число» В1, которое отобразится в строке формул в виде «=LN(В1)», затем переносим формулу на все данные, автозаполняя ячейки В5-О8. После этого исследуем матрицу парных коэффициентов корреляции. Для построения матрицы вызываем меню «Сервис/Анализ данных» и выбираем пункт «Корреляция». В появившемся окне в поле «Входной интервал» задаем ссылку на преобразованные данные – А5-О8. Указываем группирование «По строкам». Ставим флажок в «Метки в первом столбце» (так как в ссылках на таблицу указаны подписи строк). В области «Параметры вывода» ставим флажок напротив «Выходной интервал» и напротив в поле даем ссылку на какую-либо ячейку, откуда будет осуществляться вывод данных, например А10 и нажимаем «ОК». Для общей оценки мультиколлинеарности факторов и адекватности регрессионной модели рассчитаем определители матриц . Сформируем полную матрицу парных коэффициентов корреляции. В С11 задаем формулу «=В12», в D11 ссылку «=B13», в D12 – «=С13», в Е11 – «=В14», в Е12 – «=С14», в Е13 – «D14». Далее, для вычисления определителей в ячейку А16 вводим заголовок « =» и в В16 ставим курсор и задаем функцию «МОПРЕД» (категория «Математические»), в которой аргумент «Массив» является ссылкой на ячейки B11:E114. В ячейку А17 вводим заголовок « =» и в В17 ставим курсор и задаем функцию «МОПРЕД» с аргументом «Массив» - ссылкой на B11:D13. Результат - 0,163303, он близок к нулю, что говорит о достаточно сильной общей мультикорреляции факторов между собой. Найдем теперь коэффициент множественной корреляции. В А18 вводим «Rх=», а в В18 формулу «=КОРЕНЬ(1-В16/В17)». Результат 0,993187 говорит о достаточно высокой связи между фактором и функцией отклика.
Проведем теперь отбор факторов. Видно, что первый фактор сильно связан и со вторым и с третьим, поэтому его выводим их регрессионной модели. Одновременно видно, что влияние второго и третьего фактора на функцию Y достаточно сильно, поэтому, принимаем к рассмотрению регрессионную модель . В строках с номерами 20-22 копируем значимые факторы. Для этого в А20-А22 вводим подписи «Ln X2, LnX3, LnY», а в В20 вводим функцию «=LN(B2)» и автозаполняем ее В20-О22. В ячейку А24 вводим заголовок «Линейная» и в соседнюю В24 вводим функцию, определяющую параметры линейной регрессии «=ЛИНЕЙН». Аргументы функции: «Изв_знач_у» - B22:O22, «Изв_знач_х» - B20:O21, «Константа» = 1, «Стат» = 1. Далее обводим 5 строк и три столбца В24-D28 (т.к. находим параметры функции Y и двух факторов ), и нажимаем F2 и Ctrl+Shift+Enter. Первая строка результата – значения параметров преобразованного регрессионного уравнения. Чтобы получить данные исходного уравнения, вводим в G10, G11 и G12 подписи «», а в соседние ячейки Н10, Н11 и Н12 формулы “=C24”, “=B24” и “=EXP(D24)”. В результате уравнение регрессии есть . Вторая строка – стандартные ошибки коэффициентов. Все они меньше самих коэффициентов, это значит, что коэффициенты значимы. Коэффициент детерминации равен 0,892. Видно, что F-критерий регрессионной модели равен 45,359. Проверим модель на адекватность. Вычислим критическое значение статистики. Вводим в ячейку Н24 подпись «F-критическое» а в I24 вводим функцию FРАСПОБР, имеющую аргументы: «Вероятность» – 0,05 (т.к. ), «Степени_свободы_1» - 2 (число независимых переменных равно двум - ). Аргументом «Степени_свободы_2» служит число, показанное в ячейке С27 (в данном примере – 11). Видно, что F-статистика больше ее критического значения, поэтому модель адекватна.
Задание на самостоятельную работу
Развивающееся предприятие располагает статистикой о зависимости количества выпуска товара zi. (тыс. ед. в месяц). от месячной трудоемкости (тыс. час. в месяц), и от количества сырья, затраченного на выпуск (млн. ед. ресурса в месяц). Необходимо найти приближение производственной функции Кобба-Дугласа , найти характеристики качества уравнения, произвести отбор качества, с доверительной вероятностью р= 0,95 проверить модель на адекватность.
Значения факторов хi и уi (одинаковое для всех вариантов) | |||||||||||||||||||||||||||||||||||||
хi | |||||||||||||||||||||||||||||||||||||
уi | |||||||||||||||||||||||||||||||||||||
Вар. | Значения фактора zi (по вариантам) | ||||||||||||||||||||||||||||||||||||
1. | 1,8 | 2,0 | 2,4 | 2,7 | 2,3 | 2,7 | 3,0 | 3,5 | 2,5 | 3,5 | 3,9 | 4,5 | 2,7 | 3,8 | 4,8 | 3,2 | 4,1 | 4,9 | |||||||||||||||||||
2. | 1,8 | 2,3 | 2,7 | 3,1 | 2,6 | 3,3 | 3,6 | 3,8 | 3,1 | 3,9 | 4,5 | 4,8 | 3,3 | 4,4 | 5,6 | 3,5 | 4,8 | 5,3 | |||||||||||||||||||
3. | 3,7 | 4,1 | 4,6 | 5,2 | 5,1 | 5,7 | 6,6 | 7,0 | 6,5 | 7,7 | 8,3 | 8,9 | 7,1 | 9,0 | 7,8 | 9,6 | |||||||||||||||||||||
4. | 3,5 | 4,0 | 4,2 | 4,5 | 5,1 | 5,6 | 5,7 | 6,1 | 6,7 | 7,3 | 7,7 | 8,0 | 7,3 | 8,6 | 9,6 | 8,3 | 9,6 | ||||||||||||||||||||
5. | 2,1 | 2,7 | 3,3 | 3,6 | 2,9 | 3,6 | 4,1 | 4,7 | 3,9 | 4,7 | 5,3 | 5,7 | 3,8 | 5,1 | 6,4 | 4,1 | 5,2 | 6,0 | |||||||||||||||||||
6. | 1,6 | 1,8 | 2,1 | 2,5 | 2,5 | 2,5 | 3,0 | 3,0 | 3,0 | 3,6 | 3,6 | 3,9 | 3,3 | 4,0 | 4,5 | 3,6 | 4,4 | 5,0 | |||||||||||||||||||
7. | 2,7 | 3,5 | 4,4 | 4,7 | 3,7 | 4,9 | 5,3 | 6,1 | 4,8 | 5,8 | 6,8 | 7,4 | 4,8 | 6,7 | 8,3 | 5,1 | 6,8 | 8,2 | |||||||||||||||||||
8. | 1,5 | 1,9 | 2,3 | 2,6 | 2,2 | 2,5 | 2,8 | 3,4 | 2,7 | 3,4 | 3,8 | 4,3 | 2,8 | 3,7 | 4,5 | 2,9 | 4,0 | 4,4 | |||||||||||||||||||
9. | 4,1 | 5,2 | 5,8 | 6,5 | 5,8 | 6,8 | 7,8 | 8,4 | 7,2 | 8,8 | 9,8 | 7,2 | 9,9 | 7,9 | |||||||||||||||||||||||
10. | 4,0 | 4,9 | 5,4 | 6,1 | 5,6 | 6,6 | 7,3 | 8,0 | 7,2 | 8,5 | 9,4 | 7,4 | 9,6 | 8,1 | |||||||||||||||||||||||
11. | 1,7 | 1,8 | 2,2 | 2,2 | 2,4 | 2,8 | 3,0 | 3,2 | 3,4 | 3,7 | 3,9 | 4,0 | 3,9 | 4,4 | 4,8 | 4,1 | 5,0 | 5,3 | |||||||||||||||||||
12. | 3,8 | 4,5 | 4,9 | 5,6 | 5,3 | 6,1 | 6,8 | 7,3 | 6,9 | 8,1 | 8,6 | 9,4 | 7,3 | 9,1 | 8,1 | 9,9 | |||||||||||||||||||||
13. | 2,9 | 3,3 | 3,7 | 3,9 | 4,2 | 4,7 | 5,1 | 5,3 | 5,4 | 6,2 | 6,5 | 6,9 | 5,9 | 7,2 | 8,2 | 6,9 | 7,9 | 8,6 | |||||||||||||||||||
14. | 2,1 | 2,4 | 2,8 | 3,2 | 2,8 | 3,3 | 3,8 | 4,0 | 3,3 | 4,2 | 4,7 | 5,0 | 3,3 | 4,9 | 5,7 | 3,8 | 4,8 | 5,7 | |||||||||||||||||||
15. | 2,6 | 3,1 | 3,7 | 4,3 | 3,4 | 4,1 | 4,7 | 5,2 | 4,3 | 5,2 | 5,9 | 6,6 | 4,2 | 5,9 | 7,4 | 5,0 | 6,2 | 7,3 | |||||||||||||||||||