Регрессионный анализ Задание 16

Дан набор точек: (0,3) , (1,1) , (2,6) , (3,3) , (4,7). Найти коэффициенты а и b прямой линии Y=aX+b , наилучшим образом аппроксимирующей эти данные.

Решение

1. Введите координаты точек в диапазон А2:В6 .

	A	B
	X	Y
	0	3
	1	1
	2	6
	3	3
	4	7

2. Для того, чтобы найти коэффициенты а и b, выделите диапазон А7:В7, вызовите Мастер функций, категория Статистические, функция ЛИНЕЙН, в поле Известные значения Y выделите диапазон В2:В6, в поле Известные значения Х выделите диапазон А2:А6, поля Конст и Статистика оставите пустыми и нажмите комбинацию клавиш Ctrl+Shift+Enter.

3. В ячейке А7 получим значение коэффициента а, в ячейке В7 – значение коэффициента b.

4. Искомое уравнение прямой линии будет иметь вид Y=X+2.

5. Для того чтобы вывести полную статистику, выделите блок из 5 строк и 2 столбцов - D2:E6, вызовите Мастер функций, выберите Статистические, ЛИНЕЙН. Первым аргументом укажите блок В2:В6, вторым аргументом – блок А2:А6, в третьем и четвертом поле поставьте 1, нажмите Ctrl+Shift+Enter.

6. Получите таблицу, в которой в D2 записан коэффициент – а; в E2 – коэффициент b; в D3 и E3 – стандартные отклонения для этих коэффициентов; в D4 – коэффициент детерминации R2, чем ближе он к 1, тем лучше регрессионное уравнение описывает зависимость; в E4 – стандартная ошибка для оценки y; в D5 – значение F-статистики; в E5 – количество степеней свободы; в D6 – регрессионная сумма квадратов; в E6 – остаточная сумма квадратов.

Задание 17

Для заданного набора пар значений независимой переменой и функции определить наилучшее линейное приближение в виде прямой с уравнением Y=aX+b и показательное приближение в виде линии с уравнением Y=b·a^X .

Решение

3. В столбец А, начиная с ячейки А1, введите 10 произвольных значений независимой переменой.

4. В столбец В, начиная с В1, введите 10 произвольных значений функции.

5. В диапазоне С1:D1 найдите коэффициенты а и b искомой прямой, с помощью функции ЛИНЕЙН.

6. Чтобы из полученного массива извлечь значения в отдельные ячейки, используйте функцию ИНДЕКС. Для этого выделите ячейку С3, вызовите Мастер функций, категория Ссылки и массивы, функция ИНДЕКС, в поле Массив выделите С1:D1, в поле Номер строки укажите число 1, получите значение коэффициента а.

7. Для извлечения коэффициента b, выделите ячейку D3, выполните действия аналогично предыдущему пункту, только в поле Номер строки укажите число 2.

8. В диапазоне Е1:F1 найдите коэффициенты а и b показательного приближения. Для этого выделите диапазон Е1:F1,вызовите мастер функций, категория Статистические, функция ЛГРФПРИБЛ, в поле Известные значения Y выделите диапазон В1:В10, в поле Известные значения Х выделите диапазон А1:А10, поля Конст и Статистика оставите пустыми и нажмите комбинацию клавиш Ctrl+Shift+Enter.

9. В ячейки Е3 и F3 извлеките коэффициенты а и b с помощью функции ИНДЕКС.