Полиномиальное приближение

Рассмотрим квадратичную модель, в которой функция регрессии представляет собой полином второй степени. Уравнение регрессии квадратичной модели имеет следующий вид.

В качестве независимых переменных в уравнении используются переменные x и x ².

1. Откройте программу Excel. Щелкните на кнопке Сохранить на панели инструментов Стандартная. В появившемся диалоговом окне откройте папку Статистика и задайте имя файлу Нелинейная регрессия.xls.

2. Откройте файл Двумерные данные.xls. Выделите Лист1 и выполните команду Правка→Переместить/скопировать лист… В диалоговом окне из списка в книгу: выберите файл Нелинейная регрессия.xls, в списке перед листом: выберите Лист1, установите флажок Создавать копию и щелкните на кнопке ОК.

3. В файле Нелинейная регрессия.xls удалите Лист1, а имя Лист1(2) замените на имя Лист1. Закройте файл Двумерные данные.xls.

4. На диаграмме рассеяния щелкните правой кнопкой мыши на любой точке данных (Ряд1) и в контекстном меню выберите команду Добавить линию тренда… Появится диалоговое окно (см. рисунок ниже).

5. В диалоговом окне на вкладке Тип щелкните по пиктограмме Полиномиальная. Параметр Степень: должен соответствовать числу 2.

6. Откройте вкладку Параметры и в области Название аппроксимирующей (сглаженной) кривой выберите опцию автоматическое:. Убедитесь, что опция пересечение кривой с осью Y в точке: не отмечена. Включите опции показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R^2). Щелкните на кнопке ОК.

7. Выделите текст с уравнением регрессии и значением R ², щелкните у его границы и расположите под заголовком диаграммы.

Результат приближения квадратичной функцией немного лучше, чем при линейном приближении, т.к. коэффициент детерминации R ², равный 68%, получился больше 66%. Для более точного анализа квадратичной модели получим дополнительные характеристики регрессии, используя инструмент анализа Регрессия.

8. Скопируйте данные с Листа1 (диапазон A1:B16) на Лист2 в такой же диапазон.

9. Выделите столбец B и из контекстного меню выберите команду Добавить ячейки.

10. В ячейку B1 введите метку Площадь^2. Увеличьте ширину столбца B, дважды щелкнув на правой границе его заголовка.

11. Выделите ячейку B2 и введите в нее формулу =A2^2.

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

13. Выберите команду: Сервис®Анализ данных®Регрессия. В диалоговом окне Регрессия установите параметры как указано ниже на рисунке. Щелкните на кнопке ОК.

14. Выделите диапазон столбцов E:M и увеличьте ширину столбцов, дважды щелкнув по правой границе в строке заголовков столбцов.

15. Удалите часть результатов, относящихся к дисперсионному анализу. Для этого выделите диапазон E10:M14 и выберите из контекстного меню команду Удалить… В диалоговом окне установите опцию ячейки, со сдвигом вверх. Щелкните на кнопке ОК.