Порядок построения уравнения регрессии

Порядок построения аппроксимирующей функции с использованием приложения Excel рассмотрим на примере построения линейной регрессии y=P₁(x) = a+bx.

Будем находить параметры a, b , которые, согласно МНК, должны минимизировать сумму квадратов отклонений
. При этомминимизацию суммы будем реализовывать с помощью надстройки Excel «Поиск решения».

1. Оформим таблицу, как показано на рис.3.3. В ячейки А9:В15 введем результаты эксперимента (результаты эксперимента взять в приложении 3 в соответствии с вариантом). Эти данные являются исходными.

2. Подготовим ячейки В3:С3, в которых будут получены коэффициенты уравнения регрессии а, в.Для контроля проводимых ниже расчетов примите их равными единице, т.е. В3 =1, С3 =1.С «точки зрения» надстройки это изменяемые ячейки, а принятые значения а=1, в=1 можно считать начальным приближением.

3. В столбцеС (Прямая) вычислите значения УР в экспериментальных точках:

· C9 = $B$3+$C$3*A9

· Скопируйте формулу вниз до конца таблицы

Рис.3.3. Расчетная схема для определения коэффициентов УР

4. В столбце F сформируйте квадраты отклонений по формуле

· F9 =(B9-C9)^2

· Скопируйте формулу вниз

5. В ячейке F16 вычислите сумму квадратов отклонений для всех точек: F16=СУММ(F9:F15)

Нашей задачей (согласно методу наименьших квадратов) является минимизация этой суммы путем изменения значений коэффициентов a и b уравнения (т.е. ячеек В3 и С3). В исходном состоянии они пусты или имеют какие-либо значения (см. пункт2).

6. Минимизацию суммы квадратов отклонений будем производить с помощью надстройки Поиск решения. Для этого выполните команду: меню Данные\Анализ|Поиск решения.

В появившемся окне «Поиск решения» сделайте установки в соответствии с (рис.3.4):

• Установить целевую ячейку – F16

• Равнойминимальному значению

• Изменяя ячейки – В3:С3

• Кнопка выполнить.

Рис.3.4. Окно Поиск решения. Определение коэффициентов

Результаты, полученные в изменяемых ячейках В3 и С3 соответствуют коэффициентам уравнения линейной регрессии y=1,108+0,566x

7. Аналогичным образом вычислите коэффициенты для УР 2-ого и 3-его порядков.

8. Вычислите средние квадратичные отклонения для каждого приближения по формуле (3.4):

• F17 = корень(F16/7) (7 – количество экспериментальных точек)

• Скопируйте формулу вправо

9. Сделайте обоснованный вывод о «наилучшем» приближении.

10. Постройте диаграммы всех аппроксимирующих функций, нанесите множество экспериментальных точек (рис.3.5).

Рис.3.5. Аппроксимирующие полиномы и экспериментальные данные

11. Геометрический смысл степени точности аппроксимации проиллюстрируйте в соответствии с рис.3.6 для полученного вами наилучшего приближения.

Геометрический смысл степени точности аппроксимации иллюстрирует рис.3.6. Для этого строим график УР y=j(х) , наносим экспериментальные точки M_i (x_i, y_i), i=1,2,..,n. Добавляем еще два графика y=j(х)+ε и y=j(х)-ε. Если до 95% экспериментальных точек попадают в полосу шириной 2ε, т.е. отстоят от графика УР не далее чем на ε, то можно говорить о приближении с точностью ε.

Рис.3.6. Геометрический смысл степени точности аппроксимации

Проверьте правильность ваших расчетов, используя надстройку «Линия тренда».

Контрольные вопросы к индивидуальному заданию №3

1. Понятие аппроксимации (приближения). Когда возникают задачи аппроксимации.

2. Среднеквадратичное приближение. Суть метода наименьших квадратов (МНК).

3. Среднее квадратичное отклонение. Выбор «наилучшего» приближения.

4. Геометрический смысл точности аппроксимации исследуемого процесса.

Приложение 3

Варианты индивидуального задания 3