Главная страница Случайная страница
КАТЕГОРИИ:
АвтомобилиАстрономияБиологияГеографияДом и садДругие языкиДругоеИнформатикаИсторияКультураЛитератураЛогикаМатематикаМедицинаМеталлургияМеханикаОбразованиеОхрана трудаПедагогикаПолитикаПравоПсихологияРелигияРиторикаСоциологияСпортСтроительствоТехнологияТуризмФизикаФилософияФинансыХимияЧерчениеЭкологияЭкономикаЭлектроника
Решение задач аппроксимации с помощью электронных таблиц Excel
|
|
5.4.1. Построение уравнений регрессии методом наименьших квадратов с использованием надстройки «Поиск решения»
Имеются результаты (табл.5.2) некоторой серии экспериментов, в которой выявлена некоторая зависимость параметров x и y.
Таблица 5.2
| x | |||||||
| y | 3, 5 | 5.5 |
В таких случаях ставится задача о выявлении некоторой аналитической зависимости между величинами x и y, полученными в процессе эксперимента.
В качестве аппроксимирующих функций рассмотрим полиномы разной степени:
y=Pm(x)=a 0 +a 1 x+a 2 x2+…+amxm (5.29)
Поиск коэффициентов такого уравнения осуществляется с помощью МНК. При этом минимизацию суммы квадратов отклонений можно реализовать с помощью надстройки MS Excel «Поиск решения».
Рассмотрим 3 вида уравнений регрессий:
1. Прямая Р1(х) = a+bx
2. Парабола Р2(х) = a+bx+cx2
3. КубПарабола Р3(х) = a+bx+cx2+dx3
Оформим таблицу, как показано на рис.5.5.
Значения xi и yi из табл. 5.2 введем в массив ячеек A9: B15.
В столбцах Квадраты отклонений (F, G, H) будем записывать квадраты отклонений между экспериментальными значениями уi и полиномами первой Р 1(х), второй Р 2(х) и третьей Р 3(х) степени соответственно для x = x i, i =1, 2,.., n.
В общем виде эти выражения имеют вид: [ уi – Рm (хi)]2.
Рассмотрим случай линейной регрессии, т.е. Р1(х)=a+bx.
Последовательность действий
Для контроля проводимых ниже расчетов принимаем а =1, в =1 (ячейки B3, C3). С точки зрения надстройки «Поиск решения» эти значения можно считать начальным приближением, а ячейки B3, C3 - изменяемыми ячейками.
1. Итак, введем В3=1, С3=1 (рис. 5.5).
2. В столбце Прямая (столбец С)вычислим значения УР в экспериментальных точках: C9=$B$3+$C$3*A9. Копируем эту формулу вниз до конца таблицы.
3. В столбце F сформируем квадраты отклонений,
, т.е. введем формулу: F9=(B9-C9)^2 и скопируем ее вниз до конца таблицы
4. В ячейке F16 вычислим сумму квадратов отклонений для всех точек: F16=СУММ(F9: F15)
5. Нашей задачей является минимизация этой суммы путем изменения значений коэффициентов уравнения a и b (ячеек В3 и С3). В исходном состоянии они пусты или имеют какие-либо значения (смотри пункт 1).
6. Для поиска оптимальных значений коэффициентов выполним команду:
Данные\Поиск решения
и в появившемся окне «Поиск решения» сделаем следующие установки:
· целевая ячейка – F16,
· изменяемые ячейки – В3: С3,
· поставим флажок в поле минимальному значению,
· нажмем кнопку выполнить
Результаты, полученные в ячейках В3, С3, соответствуют коэффициентам линейной регрессии вида:
y= 8, 828+0, 770 x.
Рис.5.5. Схема построения уравнения регрессии
7. Среднеквадратичное отклонение вычислим по формуле (5.19) в ячейке F17: F17=КОРЕНЬ(А16/7)
Аналогичным образом получим уравнения регрессии второго и третьего порядков:
у =1, 187+0, 559 х +0, 021 х 2 .
у = –2, 779+4, 642 х –0, 965 х 2 +0, 066 x 3.
Точно так же можно сформировать уравнение регрессии любого порядка.
Графическое отображение результатов вычисления приведено на рис.5.6 для полученных выше уравнений регрессии.
Рис.5.6. Графическое отображение аппроксимирующих полиномов и экспериментальных данных
Таким образом, в этом примере увеличение степени аппроксимирующего полинома снижает погрешность приближения. Однако это не всегда так. Самая высокая степень такого уравнения на единицу меньше числа экспериментальных точек. В рассмотренном примере теоретически возможен полином шестой степени. Но на практике не следует стремиться к полному устранению погрешности, поскольку и сами экспериментальные данные не являются точными и с увеличением степени полинома возрастают погрешности округления.
5.4.2. Построение линейной эмпирической формулы с использованием встроенных функций ЛИНЕЙН и ТЕНДЕНЦИЯ
Для построения линейной эмпирической формулы в приложении Excel предусмотрены встроенные функции ЛИНЕЙН и ТЕНДЕНЦИЯ из категории Статистические.
ЛИНЕЙН (< известное Y >; < известное X >) – вычисляет коэффициенты линеного уравнения регрессии для множества значений независимой переменной Х и зависимой переменной Y Результаты выводятся в две смежные ячейки – сначала коэффициент при х, затем свободный член.
Поскольку X и Y являются массивами, то функция вводится как функция обработки массивов:
• выделяются две смежные ячейки для результатов;
• вводится функция;
• одновременно нажимаются клавиши Ctrl+Shift+Enter.
ТЕНДЕНЦИЯ (< известное Y >; < известное X >; < новое Х >) – вычисляет ожидаемое новое значение у для нового х, если известны некоторые опытные значения Х и Y.
Ввод этой функции аналогичен вводу функции ЛИНЕЙН.
1. Пример. Для исходных данных, заданных табл. 5.1, найдем коэффициенты линейной эмпирической формулы y=a+bx, используя функцию ЛИНЕЙН.
Последовательность действий
Таблица исходных данных приведена рис.5.7.
Выделим ячейки E4: F4 и введем формулу: =ЛИНЕЙН($C$6: $С$15; $В$6: $В$15), используя Мастер функции.
Результаты в ячейках E4 и F4 можно интерпретировать как коэффициенты линейного уравнения y= 1, 8+0, 64 x.
Рис. 5.7. Определение коэффициентов линейной регрессии
с помощью функции ЛИНЕЙН
2. Пример. Для тех же исходных данных, приведенных на рис.5.7, вычислим ожидаемое новое значение у для нового значения х, используя функцию ТЕНДЕНЦИЯ.
Последовательность действий
Вычисления производятся в предположении, что х и у зависят линейно.
1. Новые значения х запишем в ячейках E8: E15.
2. Результаты вычислений новых значений у будем записывать в ячейки F8: F15.
3. Выделим ячейку F8 и с помощью Мастера функций введем формулу:
=ТЕНДЕНЦИЯ($C$6: $C$15; $B$6: $B$15; F8).
4. Одновременно нажмем клавиши Ctrl+Shift+Enter.
5. Скопируем формулу вниз для всех новых значений х.
Контрольные вопросы
1. Понятие численного эксперимента, пример такого эксперимента.
2. Что понимается под термином аппроксимации (приближения). Когда возникают задачи аппроксимации.
3. Как ставится задача интерполирования функций.
4. Среднеквадратичное приближение. Суть метода наименьших квадратов (МНК).
5. Среднее квадратичное отклонение. Выбор «наилучшего» приближения.
6. Геометрический смысл точности аппроксимации исследуемого процесса. Метод выравнивания
7. Аппроксимация с помощью эмпирической формулы с двумя параметрами.