Параболическая регрессия.

Если для нелинейной модели не удается никакими преобразованиями привести уравнение регрессии к линейному виду, то часто используют полиномиальною регрессию, когда данные приближаются многочленом степени к, уравнение которого имеет вид y(x)=a_Q + а_{х +а₂х +... + а_кх . Частным видом полиномиальной регрессии является параболическая регрессия, уравнение которой есть у(х) = ах ~ +Ьх +с .

Для нахождения неизвестных параметров уравнения нужно решить систему уравнений:

а • 2>,4 +6 • 2>.3 + с • Xх-2 = Ех-2^’

(3.4.4)

«•Zx2+^Zx- +с'п^у>-

Коэффициент нелинейной корреляции для такой модели вычисляется по

формуле

I. жы

У 1<Т-у,г

ПРИМЕР 3.4.3. Исследуется зависимость полученной киоском прибыли (тыс. р. в день) от зарплаты продавца X (тыс. р. в месяц). Предполагается, что зависимость носит параболический характер. Построить эту зависимость. Данные приведены в табл. 3.4.4

Таблица 3.4.4

РЕШЕНИЕ. Вводим эти данные в электронную таблицу вместе с подписями в ячейки А1-М2. Строим график. Для этого обводим данные Y (ячейки В2-М2), вызываем мастер диаграмм, выбираем тип диаграммы «График», вид диаграммы — график с точками (второй сверху левый), нажимаем «Далее», переходим на закладку «Ряд» и в поле «Подписи оси X» делаем ссылку на В2-М2, нажимаем «Готово».

Видно, что график имеет экстремум, и хорошо будет описываться параболой, ветви которой направлены вниз (рис. 3.4.5).

Рис. 3.4.5

Рассчитаем суммы. Для этого в ячейку АЗ вводим подпись «Х^Л2», а в ВЗ вводим формулу «=В1*В1» и автозаполнением переносим ее на всю строку ВЗ-МЗ. В ячейку А4 вводим подпись «Х^Л3», а в В4 формулу «=В1*ВЗ» и автозаполнением переносим ее на всю строку В4-М4. В ячейку А5 вводим «Х^Л4», а в В5 формулу «=В4*В1» , автозаполняем строку. В ячейку А6 вводим «Х*У», а в В8 формулу «=В2*В1» , автозаполняем строку. В ячейку А7 вводим «Х^Л2*У», а в В9 формулу «=ВЗ*В2» , автозаполняем строку. Теперь считаем суммы. Выделяем другим цветом столбец N, щелкнув по заголовку и выбрав цвет. В ячейку N1 помещаем курсор и щелкнув по кнопке автосуммы со значком Е, вычисляем сумму первой строки. Автозаполнением переносим формулу на ячейки N1-N7.

Решаем теперь систему уравнений. Для этого введем основную матрицу системы. В ячейку А9 введем подпись «А=«, а в ячейки матрицы В 13-Е 16 введем суммы, стоящие в левой части системы уравнений (1), то есть ссылки, согласно табл. 3.4.5

Таблица 3.4.5

Число 12 в последней ячейке — это число точек, взятых из наблюдений.

Вводим теперь матрицу-столбец с коэффициентами правых частей системы (3.4.4). Для этого в ячейку Е9 вводим подпись «В=«, а в ячейки F9, F10 и Fl 1 вводим ссылки на ячейки «=N7», «=N6» и «=N2». Решаем систему матричным методом. Из высшей математики известно, что решение равно А~^]В. Находим обратную матрицу А '. Для этого в ячейку G9 вводим подпись «А обр.» и, поставив курсор в Н9, задаем формулу МОБР (категория «Математические»). В качестве аргумента «Массив» даем ссылку на ячейки B9:D11. Результатом также должна быть матрица размером 3x3. Для ее получения обводим ячейки H9-J11 мышью, выделяя их, и нажимаем F2 и Ctrl+Shift+Enter. Результат — матрица А^-1 . Найдем теперь произведение этой матрицы на столбец В (ячейки F9-F11). Вводим в ячейку А12 подпись «Коэффициенты» и в В13 задаем функцию МУМНОЖ (категория — «Математические»). Аргументами функции «Массив 1» служит ссылка на матрицу А^-1 (ячейки H9-J11), а в поле «Массив 2» даем ссылку на столбец В (ячейки F9-F11). Далее выделяем В13-В15 и нажимаем F2 и Ctrl+Shift+Enter. Получившийся массив — коэффициенты уравнения регрессии а,Ь,С. В результате получаем уравнение регрессии вида

у = -0,166%² + 4,207%-11,119 .

Построим графики исходных данных и полученных на основании уравнения регрессии. Для этого в ячейку А8 вводим подпись «Регрессия» и в В8 вводим формулу «=$В$13*ВЗ+$В$14*В1+$В$15». Автозаполнением переносим формулу в ячейки В 8-М 8. Для построения графика выделяем ячейки Bl 1-М11 и, удерживая клавишу Ctrl, выделяем также ячейки В2-М2. Вызываем мастер диаграмм, выбираем тип диаграммы «График», вид диаграммы — график с точками (второй сверху левый), нажимаем «Далее», переходим на закладку «Ряд» и в поле «Подписи оси X» делаем ссылку на В2-М2, нажимаем «Готово». Видно, что кривые почти совпадают (рис. 3.4.6).

? Эмпирические данные Линия регрессии

Рис. 3.4.6

Из проведенного анализа видно, что оптимальная зарплата продавца должна составлять около 12—13 тыс. р. (максимум уравнения регрессии). Уравнение также можно использовать в прогнозах.