Решение. 1 Ввести массив Хi диапазон А2:А6.

1 Ввести массив Х_i диапазон А2: А6.

2 Ввести массив Y_i диапазон В2: В6.

На рисунке 3.1. приведен фрагмент листа Excel решения задачи из примера 3.1.

Рисунок 3.1 - Использование функций НАКЛОН и ОТРЕЗОК для вычисления

коэффициентов регрессионной прямой

При решении некоторых задач для прогнозирования дальнейших значений откликов можно не вычислять коэффициенты регрессионного уравнения, поскольку они нужны для оценки откликов для старых и новых значений факторов. (Старые значения — те, на основе которых вычислялось уравнение регрессии.) Для этого служат две функции: ТЕНДЕНЦИЯ и ПРЕДСКАЗ.

Функция ТЕНДЕНЦИЯ (известные_значения_y; известные_значения_x; новые_значения_x; конст) возвращает значения в соответствии с линейным трендом. Аппроксимирует прямой линией (по методу наименьших квадратов) массивы известные_значения_y и известные_значения_x. Возвращает значения y, в соответствии с этой прямой для заданного массива новые_значения_x.

Функция ПРЕДСКАЗ(x; известные_значения_y; известные_значения_x) вычисляет или предсказывает будущее значение по существующим значениям. Предсказываемое значение - это y-значение, соответствующее заданному x-значению.

Пример 3.2.

В таблице 3.1 приведены две соответствующие величины: процент углерода в металле и производительность печи (т/ч).

Построить диаграмму зависимости производительности печи от процента углерода. На диаграмме получить линию тренда и уравнение регрессии.

Используя функции НАКЛОН и ОТРЕЗОК, определить угловой и свободный коэффициенты уравнения регрессии двумя способами.

Тремя способами дать приблизительную оценку производительности печи при проценте углерода в металле равном 0, 97.

Таблица 3.1 - Зависимость производительности печи от процента углерода в металле.