Ый способ.
1. На листе Excel выделить массив свободных ячеек из пяти строк и двух столбцов.
2. Вызвать функцию ЛИНЕЙН.
3.Указать для функции следующие аргументы: Изв_знач_y- столбец значений показателя Урожайность, ц/га; Изв_знач_x- столбец значений показателя Качество земли, балл; Константа –1, Стат– 1 (позволяет вычислить показатели, используемые для проверки адекватности модели. Если Стат– 0, то такие показатели вычисляться не будут.
4. Нажать комбинацию клавиш Ctrl-Shift-Enter.
В выделенные ячейки выводятся коэффициенты модели, а также показатели, позволяющие проверить модель на адекватность (таблица 2).
| Таблица 2 | |
| a1 | a0 |
| Se1 | Se0 |
| R2 | Se |
| F | n-k-1 |
| QR | Qe |
a1, a0 – коэффициенты модели;
Se1 Se0 – стандартные ошибки коэффициентов. Чем точнее модель, тем меньше эти величины.
R2 – коэффициент детерминации. Чем он больше, тем точнее модель.
F – статистика для проверки значимости модели.
n-k-1– число степеней свободы (n-объем выборки, k- количество входных переменных; в данном примере n=20, к=1)
QR – сумма квадратов, обусловленная регрессией;
Qe – сумма квадратов ошибок.
5. Для проверки адекватности модели найти квантиль распределения Фишера Ff. с помощью функции FРАСПОБР. Для этого в любой свободной ячейке ввести функцию FРАСПОБР со следующими аргументами: Вероятность – 0,05, Степени_свободы_1–1, Степени_свободы_2–18. Если F> Ff, то модель адекватна исходным данным
6. Проверить адекватность построенной модели, используя расчетный уровень значимости (P). Ввести функцию FРАСП со следующими аргументами: X– значение статистики F, Степени_свободы_1–1, Степени_свободы_2– 18. Если расчетный уровень значимости P<α =0,05, то модель адекватна исходным данным.
Й способ.
Определение коэффициентов модели с получением показателей для проверки ее адекватности и значимости коэффициентов.
1. Выбрать команду Сервис/Анализ данных/Регрессия. В диалоговом окне установить: Входной интервал Y – значения показателя Урожайность, ц/га,Входной интервал X – значения показателя Качество земли, балл.
2.Установить флажок Метки. В области Параметры вывода выбрать переключатель Выходной интервал и указать ячейку, с которой будет начинаться вывод результатов. Для получения результатов нажать кнопку ОК.