Пример выполнения работы

Построить уравнение регрессии между выходной толщиной h₁ и входной толщиной h₀ холоднокатаной полосы. Значения толщин x = h₀ и y = h₁ даны в таблице 2.

Регрессионный анализ в Excel невероятно прост. Как только данные представлены в графическом виде, регрессия выполняется с помощью нескольких щелчков мыши, поэтому регрессия с использованием прямой часто применяется, несмотря на то, что зависимость между переменными не линейная, а более сложная.

Сформулируем практическое правило: всегда следует строить диаграмму, на которой представлена кривая регрессии и данные, чтобы можно было визуально оценить степень совпадения. В том случае, когда регрессия проводится с помощью линии тренда, кривая регрессии автоматически добавляется на диаграмму с соответствующими данными.

Таблица 2

x_i ·10^-2	71	78	84	92	87	85	86	91	93	93	90	85	81	80	78	84	82	85	82	80
y_i ·10^-2	46	50	54	54	52	50	49	53	57	59	56	53	50	48	50	54	55	55	54	54

1. Подготовьте начальный рабочий лист с исходными данными (рис. 32), постройте точечную диаграмму.

2. При построении на диаграмме линии тренда Excel автоматически находит значения коэффициентов a и b, а также квадрат коэффициента корреляции (достоверность аппроксимации) R2.

3. Уравнение линии тренда и значение R2 по умолчанию на диаграмме не отображаются. Чтобы отобразить эту информацию, следует в нижней части диалога Параметры линии тренда поставить флажки в параметрах:

þ показывать уравнение на диаграмме;

þ поместить на диаграмме величину достоверности аппроксимации (R2).

Рис. 32. Исходные данные и точечная диаграмма

4. Щелкните правой кнопкой мыши на любом из маркеров данных и вконтекстном меню выберите команду Добавить линию тренда... (рис. 33).

Рис. 33. Контекстное меню

5. В диалоговом окне Параметры линии тренда выберите тип диаграммы Линейная (рис. 34).

6. Отобразите уравнение кривой регрессии на диаграмме, и величину достоверности аппроксимации (R²).

Рис. 34. Параметры линии тренда

7. Как видно из диаграммы (рис. 35), уравнение регрессии имеет вид

y = 0,4108· х + 18 с достоверностью аппроксимации R² = 0,5223.

Рис. 35. Линейная модель

8. Рассчитаем среднюю ошибку аппроксимации, предварительно сформировав в столбце С массив значений . Для этого введите следующие формулы:

в ячейку С2: = 0,4108*А2 + 18;

в ячейку D2: =ABS((B2 – D2)/B2 (рис. 36).

Рис. 36. Расчёт модельных значений и относительной ошибки

9. В ячейку D25 введите формулу: =1/20*D22*100 (рис. 37).

Получаем: R² = 0,5223, = 3,53%. Построенное уравнение регрессии считается удовлетворительным, поскольку значение не превышает 10-12%.

Рис. 37. Расчёт средней ошибки аппроксимации для линейной модели

10. Рассчитайте F _факт и F _табл. Для этого в ячейки D27 и D28 введитеследующие формулы:

D27: = КОРРЕЛ(A2:A21;B2:D21)^2/(1 -КОРРЕЛ(A2:A21;B2:D21)^2)*18;

D28: = FРАСПОБР(0,05;1;18). Получаем F _факт=19,6809, F _табл=4,413873.

Так как F _ma_6л< F _факт, то H₀ - гипотеза о случайной природе оцениваемых характеристик отклоняется и признается их статистическая значимость и надежность (рис. 37).

Поскольку идеальной аппроксимации соответствует величина R² = 1, можно сделать вывод, что прямая линия не описывает зависимости идеально. Нужно попытаться подобрать другую кривую регрессии, обладающую некоторой кривизной.