Линейная множественная регрессия

Цель: По опытным данным построить уравнение множественной линейной регрессии и определить его характеристики.

ПРИМЕР. Некоторая организация занимается торговлей компьютерами. Она определила, что на количество продаж основное влияние оказывают следующие факторы: цена товара , затраты на рекламу и число конкурирующих организаций в регионе . Результаты наблюдений приведены в таблице

Построим линейное уравнение множественной регрессии. Для этого предварительно исследуем матрицу парных коэффициентов корреляции. Вводим исходные данные вместе с подписями в ячейки А1-О4. Для построения матрицы парной корреляции вызываем меню «Сервис/Анализ данных» (если пункт меню отсутствует, то вызываем «Сервис/Надстройки» и ставим галочку напротив строки «Пакет анализа»). Выбираем пункт «Корреляция». В появившемся окне в поле «Входной интервал» задаем ссылку на таблицу – А1-О4. Указываем группирование «По строкам». Ставим флажок в «Метки в первом столбце» (так как в ссылках на таблицу указаны подписи строк). В области «Параметры вывода» ставим флажок напротив «Выходной интервал» и напротив в поле даем ссылку на какую-либо ячейку, откуда будет осуществляться вывод данных, например А7. Нажимая «ОК», получаем нижнюю половину матрицы парной корреляции. Для общей оценки мультиколлинеарности факторов и адекватности регрессионной модели рассчитаем определители матриц . Сформируем полную матрицу парных коэффициентов корреляции. В С8 задаем формулу «=В9», в D8 ссылку «=B10», в D9 – «=С10», в Е8 – «=В11», в Е9 – «=С11», в Е10 – «D11». Далее, для вычисления определителей в ячейку А13 вводим заголовок « =» и в В13 ставим курсор и задаем функцию «МОПРЕД» (категория «Математические»), в которой аргумент «Массив» является ссылкой на ячейки B8:E11. В ячейку А14 вводим заголовок « =» и в В14 ставим курсор и задаем функцию «МОПРЕД» с аргументом «Массив» - ссылкой на B8:D10. Результат - 0,427104 (ближе к 0, чем к 1), что говорит о достаточно высокой общей мультикорреляции. Найдем теперь коэффициент множественной корреляции. В А15 вводим «Rх=», а в В15 формулу «=КОРЕНЬ(1-В13/В14)». Результат 0,883788 говорит о достаточно высокой связи между фактором и функцией отклика.

Проведем теперь отбор факторов. Рассмотрим матрицу коэффициентов парной корреляции. Видно, что . Факторы можно считать коллинеарными (интеркоррелированными), если их парный коэффициент по модулю больше 0,7. В нашем случае таких пар факторов нет. Все факторы можно считать независимыми друг от друга и использовать в регрессионном уравнении. Определим теперь влияние каждого фактора на функцию отклика Y. Для этого рассмотрим коэффициенты парной корреляции . Видно, что третий коэффициент -0,45 намного меньше по модулю, чем примерная граница 0,7, поэтому влияние третьего фактора Х₃ на результат мало и его можно отбросить из рассмотрения.

Копируем теперь на А18-О20 функцию отклика и значимые факторы (1, 2 и 4 строки, соответственно А1-О2 копируем в А18-О19, а А4-О4 – в А20-О20). В ячейку А22 вводим заголовок «Линейная» и в соседнюю В22 вводим функцию, определяющую параметры линейной регрессии «=ЛИНЕЙН» (категория «Статистические»). Аргументы функции: «Изв_знач_у» - B20:O20 (значения функции), «Изв_знач_х» - B18:O19 (значения двух значимых аргументов), «Константа» – 1 (расчет свободного члена), «Стат» - 1 (вывод дополнительных характеристик регрессии). Результат вычислений функции ЛИНЕЙН в случае функции нескольких переменных имеет вид:

Коэффициент аk	Коэффициент ak- 1	…	Коэффициент	Коэффици-ент
Стандартная ошибка	Стандартная ошибка	…	Стандартная ошибка	Стандартная ошибка
Коэффициент детерминации	Среднеквадратическое отклонение у	…	нет данных	нет данных
F – статистика	Степень свободы п - k -1	…	нет данных	нет данных
Регрессионная сум-ма квадратов	Остаточная сумма квадратов	…	нет данных	нет данных

В нашем случае имеем два фактора , поэтому обводим 5 строк и три столбца В22-D26 и нажимаем F2 и Ctrl+Shift+Enter.

Первая строка результата – значения параметров регрессионного уравнения - числа . Следовательно, уравнение регрессии есть . Вторая строка – стандартные ошибки коэффициентов. Все они меньше самих коэффициентов, это значит, что коэффициенты значимы. В ячейках D24-D26 стоят значения «#Н/Д» (нет данных), как и должно быть в соответствии с таблицей.

Коэффициент детерминации равен 0,7783. Видно, что F-критерий регрессионной модели равен 19,3097. Проверим модель на адекватность. Вычислим критическое значение статистики. Вводим в ячейку Н22 подпись «F-критическое» а в I22 вводим функцию FРАСПОБР, имеющую аргументы: «Вероятность» – уровень значимости, если он не задан в условии (как в нашем случае), то обычно его принимаем 0,05. Аргумент «Степени_свободы_1» - число независимых переменных (у нас их две - ). Аргументом «Степени_свободы_2» служит число, показанное в ячейке С25 (в данном примере – 11). Видно, что F-статистика больше ее критического значения, поэтому модель адекватна.

Задание на самостоятельную работу

Задание 1. Построить двухфакторную модель по данным

Задание 2. Отобрать факторы и построить регрессионную модель для данных (для всех вариантов).

проверить значимость модели на уровне 0,01.