Решение. 1 В ячейки А2:М4 ввести исходные данные (рисунок 3.5).

1 В ячейки А2: М4 ввести исходные данные (рисунок 3.5).

2 Выделить диапазон А3: М4 и построить точечную диаграмму зависимости производительности печи от процента углерода.

3 Для получения линии тренда в контекстном меню одной из точек диаграммы выбрать опцию «Добавить линию тренда…» (рисунок 3.2).

Рисунок 3.2 - Вызов контекстного меню для получения линии тренда

4 В диалоговом окне «Линия тренда» на вкладке «Тип» выбрать опцию «Линейная» (рисунок 3.3).

Рисунок 3.3 - Выбор типа линии тренда

5 Для получения уравнения регрессии на диаграмме в диалоговом окне «Линия тренда» на вкладке «Параметры» (рисунок 3.4) поставить флажок «Показывать уравнение на диаграмме».

6 Нажать кнопку ОК.

7 Определить угловой коэффициент с помощью функции НАКЛОН. Для этого:

· выделить ячейку J8;

· в мастере функций выбрать функцию НАКЛОН;

· заполнить аргументы функции так, как показано на рисунке 3.5;

· завершить ввод формулы нажатием на клавишу ОК.

Рисунок 3.4 - Определение параметров линии тренда

Рисунок 3.5 - Пример использования функций регрессионного анализа
(линейная регрессия)

8 Определить линейный коэффициент с помощью функции ОТРЕЗОК. Для этого:

· выделить ячейку J13;

· в мастере функций выбрать функцию ОТРЕЗОК;

· заполнить аргументы функции так, как показано на рисунке 3.5;

· завершить ввод формулы нажатием на клавишу ОК.

9 в ячейку В20 ввести значение процента углерода в металле, для которого надо произвести оценку производительности печи.

10 произвести оценку производительности различными способами.

· 1 способ. С помощью полученных коэффициентов регрессии. Для такой оценки в ячейку F22 надо ввести формулу =J8*B20+J13. Эта формула получена в соответствии с линейной зависимостью.

· 2 способ. С помощью функции ТЕНДЕНЦИЯ. Для оценки в ячейке F23 с помощью мастера функций надо ввести формулу =ТЕНДЕНЦИЯ(B4: M4; B3: M3; B20; 1).

· 3 способ. С помощью функции ПРЕДСКАЗ. Для оценки в ячейке F24 с помощью мастера функций надо ввести формулу =ПРЕДСКАЗ(B20; B4: M4; B3: M3).

Полный вариант решения задачи приведен на рисунке 3.5.

Задача регрессии включена в " Пакет анализа". Если выбрать в меню " Сервис/ Анализ данных/ Регрессия", то можно получить при решении задачи регрессионного анализа намного больше статистической информации.