Функция ЛИНЕЙН
Параметры линейной регрессии можно определить с помощью встроенной статистической функции ЛИНЕЙН. Порядок вычисления следующий:
- Ввод исходных данных;
- Выделите область пустых ячеек 5х2 (5 строк, 2 столбца) для вывода результатов регрессионной статистики или область 1х2 – для получения только оценок коэффициентов регрессии;
- Активизируйте Мастер функций – щелкните fx на панели инструментов или в главном меню выберите Вставка – Функция;
- В окне Категория выберите Статистические, в окне Функция – ЛИНЕЙН. Щелкните ОК.
- Заполните аргументы функции:
q Известные значения у – диапазон, содержащий данные результативного признака;
q Известные значения_х – диапазон, содержащий данные факторов независимого признака;
q Константа – логическое значение, которое указывает на наличие или отсутствие свободного члена в уравнении: если Константа = 1, то свободный член рассчитывается обычным способом, если Константа = 0, то свободный член равен 0;
q Статистика – логическое значение, которое указывает, выводить дополнительную информацию по регрессионному анализу ( = 1) или нет (=0);
q Нажмите комбинацию клавиш CTRL – SHIFT – ENTER. Дополнительная регрессионная статистика будет выводиться в порядке, указанном в следующей таблице:
Таблица 4.2.
| Коэффициент b | Коэффициент a |
| Среднеквадратическое отклонение b | Среднеквадратическое отклонение a |
| Индекс детерминации R2 | Среднеквадратическое отклонение остатков |
| F – статистика | Число степеней свободы остатков |
| Регрессионная сумма квадратов S(Y^ - Y^средн.)2 | Сумма квадратов остатков S(Y - Y^)2 |
Полученный результат:
| 1,7818 | -4,2727 |
| 0,2451 | 3,7578 |
| 0,8544 | 2,5710 |
| 52,833 | |
| 349,23 | 59,490 |
Если случайно щёлкнули ОК, нажмите на клавишу F2, а затем – на комбинацию клавиш CTRL – SHIFT – ENTER.
Для вычисления параметров показательной функции Y = abxв Excel применяется встроенная статистическая функция ЛГФПРИБЛ. Порядок вычислений аналогичен применению функции ЛИНЕЙН.
Как видите, полученные коэффициенты a, b и индекс детерминации R2 совпадают с результатами их оценки с помощью диаграммы. Кроме того, получены погрешности коэффициентов a, b, стандартное отклонение Y, число степеней свободы остатков (n-2 = 9), сумма квадратов остатков, регрессионная сумма квадратов = S(Y^ - Y^средн.)2 и статистика Фишера.