Вычисление в Excel коэффициентов регрессии

Используется функция ЛИНЕЙН. Она имеет синтаксис =
ЛИНЕЙН(известные_значения_y;известные_значения_x;конст;статистика)

Известные_значения_y — множество значений y, которые уже известны для у=a+bx

Известные_значения_x— необязательное множество значений x, которые уже известны для у=a+bx

Конст — логическое значение, которое указывает, требуется ли, чтобы константа «a» была равна 0.

Если Конст имеет значение ИСТИНА или опущено, то «а» вычисляется обычным образом. Если аргумент Конст имеет значение ЛОЖЬ, то «а» полагается равным 0 и значения «b» подбираются так, чтобы выполнялось соотношение y = bx.

Статистика— логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии.

Для вычисления коэффициентов а* и b* из задания 1:

1. Создать ЭТ, табл. 4.3.3.

Таблица 4.3.3

2. Активизировать ячейку А4.

3. Выполнить команды:

Вставка Þ Функция Þ Статистические Þ ЛИНЕЙН Þ ОК.

4. Ввести:

известные_значения_y В3:К3

известные_значения_x В2:К2

После ввода этих значений в окне функции ЛИНЕЙН появится значение коэффициентов регрессии {1,06;2,07}.

Нажать клавишу ОК. В ячейке А4 значение коэффициента а (1,066).

Для того чтобы получить оба коэффициента регрессии формулу необходимо ввести как формулу массива. Перед введением формулы необходимо выделить ячейки А4:В4, а после ввода известных значений х и у – нажать клавиши CTRL+SHIFT+ENTER. Если формула не будет введена как формула массива, единственное значение будет равно 1,006.

Сохранить файл с именем Регрессия 1.

Итак, функция ЛИНЕЙН позволяет оценивать параметры уравнения регрессии, а* и b*, но для осуществления прогноза нужно самостоятельно составлять уравнение регрессии и осуществлять прогнозирование.