Ый способ

1. На листе Excel выделить массив свободных ячеек из пяти строк и двух столбцов.

2. Вызвать функцию ЛИНЕЙН.

3.Указать для функции следующие аргументы: Изв_знач_y - столбец значений показателя Урожайность, ц/га; Изв_знач_x - столбец значений показателя Качество земли, балл; Константа –1, Стат– 1 (позволяет вычислить показатели, используемые для проверки адекватности модели. Если Стат– 0, то такие показатели вычисляться не будут.

4. Нажать комбинацию клавиш Ctrl-Shift-Enter.

В выделенные ячейки выводятся коэффициенты модели, а также показатели, позволяющие проверить модель на адекватность (таблица 2).

a₁, a₀ – коэффициенты модели;

S_e1 S_e0 – стандартные ошибки коэффициентов. Чем точнее модель, тем меньше эти величины.

R² – коэффициент детерминации. Чем он больше, тем точнее модель.

F – статистика для проверки значимости модели.

n-k-1 – число степеней свободы (n-объем выборки, k- количество входных переменных; в данном примере n=20, к=1)

Q_R – сумма квадратов, обусловленная регрессией;

Q_e – сумма квадратов ошибок.

5. Для проверки адекватности модели найти квантиль распределения Фишера F_f. с помощью функции FРАСПОБР. Для этого в любой свободной ячейке ввести функцию FРАСПОБР со следующими аргументами: Вероятность – 0,05, Степени_свободы _1–1, Степени_свободы _2–18. Если F> F_f, то модель адекватна исходным данным

6. Проверить адекватность построенной модели, используя расчетный уровень значимости (P). Ввести функцию FРАСП со следующими аргументами: X – значение статистики F, Степени_свободы_1 –1, Степени_свободы_2 – 18. Если расчетный уровень значимости P<α =0,05, то модель адекватна исходным данным.

Й способ.

Определение коэффициентов модели с получением показателей для проверки ее адекватности и значимости коэффициентов.

1. Выбрать команду Сервис/Анализ данных/Регрессия. В диалоговом окне установить: Входной интервал Y – значения показателя Урожайность, ц/га, Входной интервал X – значения показателя Качество земли, балл.

2. Установить флажок Метки. В области Параметры вывода выбрать переключатель Выходной интервал и указать ячейку, с которой будет начинаться вывод результатов. Для получения результатов нажать кнопку ОК.