Численные методы расчетов в Exel

Интерполяция функции с равноотстоящими узлами.
Анализ и прогнозирование в EXCEL.
I. Написать выражение для интерполяционного полинома Ньютона .
II. Составить программу для вычисления значения функции в заданных точках
x1 ; x2 ; x3 ; x4 :
1) при помощи полинома Ньютона для реализации ее в EXCEL ;
2) при помощи функций, осуществляющих прогноз вычислений
(ТЕНДЕНЦИЯ и ПРЕДСКАЗАНИЕ) .
Функция задана таблицей с равноотстоящими узлами :
x 0.15 0.20 0.25 0.30 0.35 0.40 0.45 0.50 0.55 0.60
y 0.860 0.819 0.779 0.741 0.705 0.670 0.638 0.606 0.577 0.549
Значения x1 = 0.149 x2 = 0.240 x3 = 0.430 x4 = 0.560
Основные понятия.
Цель работы: научиться пользоваться программой EXCEL для получения аналитической зависимости по экспериментальным данным и изучение режимов экстраполяции данных в EXCEL .
Задача интерполяции сводится к требованию точного совпадения в узловых
точках функции и ее приближения, где число определяемых параметров аппроксимирующей зависимости равно числу точек. При выборе данного критерия задача сводится к построению интерполяционных многочленов (полиномов) .
По определению интерполяция — это отыскание промежуточных значений величины по некоторым известным ее значениям. Само слово интерполяция происходит от латинского “interpolation” , что в переводе значит “изменение, переделка” .
Экстраполяция — это процедура аналогичная интерполяции, но при условии, что x лежит вне интервала (x0 , xn ) . Происходит от “экстра…” и латинского “polio” , что значит “приглаживаю, изменяю” .
Аппроксимация — это замена одних математических объектов (например, чисел или
функций) другими, более простыми и в том или ином смысле близкими к исходным(например, кривых линий близкими к ним ломаными). Слово происходит от латинского“approximo” , что значит “приближаюсь” .
Графически задача интерполяции заключается в том, чтобы построить такую интерполирующую функцию , которая бы проходила через все узлы интерполяции. Чаще всего в качестве интерполирующей функции F (x) используются многочлены Pn (x). Задача состоит в том, чтобы подобрать многочлен Pn (x), обеспечивающий требуемую интерполяцию е .
Наиболее успешно для интерполяции используется полином Ньютона , для записи которого в случае интерполяции функции с равноотстоящими узлами используются конечные разности .
Термин “полином” имеет то же значение, что и слово “многочлен” и происходит от “поли…” — часть сложных слов, указывающая на множество, всесторонний охват или разнообразный состав чего-либо (от греческого “polys” – многий, многочисленный, обширный) и латинского “nomen” , т.е. имя .
Конечной разностью первого порядка называется разность:
Дyi = yi + 1 - yi , i = 0,1, .... , n – 1
Аналогично определяются конечные разности второго и более высоких порядков.
Интерполяционный полином Ньютона.
Интерполяционный многочлен Ньютона для равноотстоящих узлов записывается в виде:
Pn (x) = y0 + (x-x0 ) · Дy0 /1!h + (x-x0 )(x-x1 ) · ДІy0 /2!hІ+....+ ( x - x 0 )( x - x 1 )…..( x - xn -1 ) · Д n y 0 / n ! hn
Решение.
Выполнение задания I.
Напишем выражение для интерполяционного полинома Ньютона для экспериментальных данных, приведенных в вышеуказанной таблице. Конечные разности указаны в “Приложение 2” . Из таблицы видно, что значения x являются равноотстоящими узлами, так как возрастают равномерно с шагом h = 0,05 . Степень полинома определяется числом (порядком) конечных разностей ( в данном случае их девять ).
Pn (x ) = P9 (x)= y0 + (x-x0 ) Дy0 / 1!h + (x-x0 ) (x-x1 ) ДІy0 /2!h2 +..
..+ (x-x0 ) (x-x1 ) (x-x2 ) (x-x3 ) (x-x4 ) (x-x5 ) (x-x6 ) (x-x7 ) (x-x8 ) (x-x9 ) Д 9 y0 / 9! h9 =
0,860 + (x- 0,15) (-0,041) / 1! · 0,05 + (x- 0,15) (x- 0,20) · 0,001 / 2! · 0,05 2 +
(x- 0,15) (x- 0,20) (x- 0,25) · 0,001 / 3! · 0,05 3 +(x- 0,15) (x- 0,20) (x- 0,25) (x- 0,30) · (-0,001) / 4! · 0,05 4 +
(x- 0,15) (x- 0,20) (x- 0,25) (x- 0,30)(x- 0,35) · 0 / 5! · 0,05 5 +
(x- 0,15) (x- 0,20) (x- 0,25) (x- 0,30)(x- 0.35)(x- 0,40) · 0,004 / 6! · 0,05 6 +
(x- 0,15) (x- 0,20) (x- 0,25) (x- 0,30) (x- 0,35) (x- 0,40) (x- 0,45) ·(-0,016) / 7! 0,05+
(x- 0,15) (x- 0,20) (x- 0,25) (x- 0,30) (x- 0,35) (x- 0,40) (x- 0,45) ( x- 0,50) · 0,047 / 8! · 0,05 8 +
( x - 0,15) ( x - 0,20) ( x - 0,25) ( x - 0,30) ( x - 0,35) ( x - 0,40) ( x - 0,45) ( x - 0,50) ( x - 0,55) · (-0,119) / 9! · 0,05 9 .
Выполнение задания II.
1)Составление программы для вычисления значений функции в заданных точках при помощи полинома Ньютона.
Шаг первый:
Подготовка исходных данных электронной таблицы в EXCEL :
а ) Введем текстовые и числовые константы (ячейки A1 : N4).
б ) Введем номера по порядку в ячейки A5 : A14.
в ) Введем исходные данные в ячейки B5 : C14.
Таким образом подготовлена таблица для выполнения работы.
Шаг второй:
Ввод формул:
а ) Ввод формул для вычисления конечных разностей первого порядка :
а.1) в ячейку D5 введем формулу для вычисления Дy0 = y1 – y0 , которая примет вид: =C6–C5 ;
a.2) копируем эту формулу в ячейки D6 : D13. В результате в ячейке D6
получаем формулу =C7-C6 (т.е.Дy1 =y2 - y1 = 0,779 – 0,819 = -0,040 ),в ячейке D7
получаем формулу =C8-C7 (т.е. Дy2 = y3 – y2 = 0,741 – 0,779= -0,038 ) и т.д. до ячейки D13, где
получаем формулу
=C14-C13 (т.е. Дy8 = y9 – y8 = 0,549 – 0,577= -0,028 )
б) Ввод формул для вычисления конечных разностей второго порядка :
б.1) в ячейку E5 копируем формулу из ячейки D5. В ячейке E5 появится формула
=D6-D5 (т.е. ДІy0 = Дy1 - Дy0 = -0,040 - ( -0,041) = 0,001 ). Копируем эту формулу в ячейки E6 : E12.
В ячейке E12 получаем формулу =D13 - D1 (т.е. ДІy7 = Дy8 - Дy7 = - 0,028 - ( -0,029) = 0,001 ).
в) Ввод формул для вычисления конечных разностей вплоть до девятого порядка :
для вычисления всех конечных разностей необходимо ввести только одну формулу(в ячейке D5), все
остальные будут получены копированием, т.е. из ячейки E5 копируем формулу в ячейку F5, из F5 в G5 и т.д.
Отображение в режиме формул см. в “Приложении 1”.
Отображение в режиме значений см. в “Приложении 2” .
Шаг третий:
Ввод формул:
а) Ввод формул для вычисления промежуточных коэффициентов:
а.1) для вычисления первого промежуточного коэффициента (x-x0 /1!h) в ячейку M5 введем формулу
=($N$2 - B5) / (A5 + 1) / $F$2 . В ячейке N2 находится текущее значение x . При копировании адрес этой ячейки изменять нельзя, поэтому мы используем абсолютный адрес (значок $). В ячейке F2 находится шаг интерполяции , адрес этой ячейки тоже абсолютный (значок $).
а.2) для вычисления второго промежуточного коэффициента
(x-x0 ) (x- x1 )/2!h І = (x-x0 )/1·h · (x-x1 )/ 2·h = a · b,
гдеa коэффициент в ячейке M5, a = (x-x0 )/1h,
b коэффициент, на который нужно умножить M5, b = (x-x1 ) / 2h,
вводим в M6 формулу: =M5*($N$2 – B6) / (A6 + 1) / $F$2 .
а.3) после ввода данных в M5 и M6, для вычисления остальных промежуточных коэффициентов
копируем формулу из M6 в остальные 7 нижестоящие ячейки. Вячейке M7 мы увидим формулу:
=M6*($N$2 – B7) / (A7 + 1) / $F$2 , в ячейке M8 мыувидим формулу: =M7*($N$2 – B8) / (A8 + 1) / $F$2 и
т.д.
Шаг четвертый:
Ввод формул:
а) Ввод формул для вычисления полинома Ньютона:
а.1) для вычисления первого полинома Ньютона , который равен (x-x0 ) · Дy0 / 1!h = (x-x0 ) / 1h ·Дy0 , содержимое ячейки M5 надо умножить на содержимое ячейки D5, где хранятся конечные разности первого порядка . Вводим в ячейку N5 формулу =M5*D$5 . Знак $ перед номером строки необходим, т.к. в полиноме Ньютона находятся только конечные разности с индексом ноль, т.е. все конечные разности берутся только из строки с номером 5;
а.2) для ввода остальных членов полинома Ньютона копируем формулу из N5 в остальные 8 нижестоящих ячеек (включительно по N13). Получаем в N6 формулу =M6*E$5 , в N7 формулу =M7*F$5 , в N8 формулу =M8*G$5 и т.д. до ячейки N13.
Шаг пятый :
Ввод формул:
а) Ввод формул для вычисления суммы коэффициентов полинома Ньютона:
а.1) объединим ячейки A16 : M16, затем в объединенные ячейки введем комментарий
"Сумма коэффициентов полинома”;
а.2) в ячейку N16 вводим формулу =СУММ(N5:N13) . Теперь в N16 будет сумма всех членов полинома Ньютона, кроме y0 . При x = 0,149 в ячейке N16 получается число 0,001.
Шаг шестой:
Ввод формул:
а) Ввод формул для вычисления значения полинома:
а.1) объединим ячейки A18 : M18, затем в объединенные ячейки введем комментарий "Значение полинома" ;
а.2) в ячейку N18 вводим формулу =N16+C5 . В ячейке N18 появится число 0,861 , которое и есть значение полинома, вычисленное в точке x = 0,149
Шаг седьмой:
Вычисление сумм коэффициентов полинома и значений полинома
при x = 0,240 ; x = 0,430 ; x = 0,560.
а) в ячейку N2 вводим 0,240 . Результат:
в ячейке N16 — (-0,073); в ячейке N18 — (0.787);
б) в ячейку N2 вводим 0,430 . Результат:
в ячейке N16 — (-0,209); в ячейке N18 — (0,651);
в) в ячейку N2 вводим 0.560 . Результат:
в ячейке N16 — (-0,287); в ячейке N18 — (0,573).
Шаг восьмой:
Для удобства полученные данные занесем в нашу таблицу.
Таблицы прилагаются. Режим формул — “Приложение 1”. Режим значений — “Приложение 2.
2)Составление программы для вычисления значений функции в заданных точках при помощи функций, осуществляющих прогноз вычислений (ТЕНДЕНЦИЯ и ПРЕДСКАЗАНИЕ).
Экстраполяция (прогнозирование) с помощью функции аппроксимации кривой.
Табличный процессор EXCEL предоставляет возможность аппроксимации с использованием “функций аппроксимации кривой”
Пусть в узлах x0 , x1 , …, x n известны значения f(x0 ), f(x1 ), … ,f(x n ). Необходимо осуществить экстраполяцию (прогнозирование), т.е. вычислить значения f(x n+1 ), f(x n+2 ), … .
В категории Статистические функции EXCEL для этого используются две функции: ТЕНДЕНЦИЯ и ПРЕДСКАЗАНИЕ , осуществляющие линейную аппроксимацию кривой для данных массивов
x (x0 , x1 , … , x n ) и y (y0 ,y1 , … , y n ) методом наименьших квадратов.
Функция ТЕНДЕНЦИЯ имеет структуру:
ТЕНДЕНЦИЯ (y массив, x массив, x список)
y массив , x массив — даны из условия.
x список -- это те значения x , для которых требуется сосчитать значения функции f(x).
Функция ПРЕДСКАЗАНИЕ имеет структуру:
ПРЕДСКАЗАНИЕ ( x ; y массив; x массив)
После аппроксимации эта функция возвращает только одно прогнозируемое значение y (для одного из заданных значений аргументов.
Работа с функцией ТЕНДЕНЦИЯ.
Шаг первый:
Создадим электронную таблицу в EXCEL , используя исходные данные.
Шаг второй:
Для того, чтобы поместить результат в список итоговых ячеек C6:F6, выделим эти ячейки.
Шаг третий:
Далее необходимо щелкнуть по пиктограмме Мастер функций .
Шаг четвертый:
а) В первом окне выберем категорию Статистические , функцию ТЕНДЕНЦИЯ,
затем щелкнем по OK.
б) В окне “Известные значения y” введем адрес блока ячеек C3:L3.
в) В окне “Известные значения x” введем адрес блока ячеек C2:L2.
г) В окне “Новые значения x” укажем адрес блока ячеек C5:F5.
Шаг пятый:
Для подтверждения этой функции одновременно нажмем клавиши SHIFT / CTRL и ENTER. В ячейках C6:F6 мы увидим прогноз.
В режиме формул:в ячейке C6 — =ТЕНДЕНЦИЯ(C3:L3;C2:L2;C5)
в ячейке D6 — =ТЕНДЕНЦИЯ(C3:L3;C2:L2;D5)
в ячейке E6 — =ТЕНДЕНЦИЯ(C3:L3;C2:L2;E5)
в ячейке F6 — =ТЕНДЕНЦИЯ(C3:L3;C2:L2;F5)
В режиме значений: в ячейке C6 — 0,8610
в ячейке D6 — 0,7951
в ячейке E6 — 0,6576
в ячейке F6 — 0,5635
Таблицы прилагаются.
Режим формул — “Приложение 3” . Режим значений “Приложение 4”.
Работа с функцией ПРЕДСКАЗАНИЕ.
Шаг первый:
Создадим электронную таблицу в EXCEL , используя исходные данные.
Шаг второй:
Для размещения результата активизируем ячейку С6.
Шаг третий:
а) При помощи Мастера функций вызовем функцию ПРЕДСКАЗАНИЕ,
категория Статистические.
б) В окне “x” укажем адрес ячейки C6.
в) В окне “Известные значения y” укажем адрес блока ячеек C3:L3.
г) В окне “Известные значения x” укажем адрес блока ячеек C2:L2.
Шаг четвертый:
Для подтверждения этой функции щелкнем по OK . В ячейке C6 появится результат. Для появления результата в остальных ячейках, проделаем все то же самое, поочередно активизируя ячейки D6, E6, F6.
В результате мы увидим:
В режиме формул:
в ячейке C6 — =ПРЕДСКАЗ(C5;C3:L3;C2:L2)
в ячейке D6 — =ПРЕДСКАЗ(D5;C3:L3;C2:L2)
в ячейке E6 — =ПРЕДСКАЗ(E5;C3:L3;C2:L2)
в ячейке F6 — =ПРЕДСКАЗ(F5;C3:L3;C2:L2)
В режиме значений: в ячейке C6 — 0,8506
в ячейке D6 — 0,7877
в ячейке E6 — 0,6564
в ячейке F6 — 0,5665
Таблицы прилагаются. Режим формул — “Приложение 5”. Режим значений — “Приложение 6”.
Итоговая сравнительная таблица.
Для сравнения значений функции в точках:
x 1 = 0,149;
x 2 = 0,240;
x 3 = 0,430;
x 4 = 0,560;
полученных при помощи трех разных способов:
1 полинома Ньютона,
2 функции ТЕНДЕНЦИЯ,
3 функции ПРЕДСКАЗАНИЕ;
создадим сравнительную таблицу,
x
Значение полинома
Ньютона
Прогнозирование значения функции при помощи функций:
ТЕНДЕНЦИЯ ПРЕДСКАЗАНИЕ
0,149 0,861 0,86 * 0,861 0,86 * 0,8506 0,85 *
0,240 0,787 0,79 * 0,795 0,80 * 0,7877 0,79 *
0,430 0,651 0,65 * 0,658 0,66 * 0,6564 0,66 *
0,560 0,573 0,57 * 0,564 0,56 * 0,5665 0,57 *
* Результаты вычислений округлены до двух знаков после запятой.
Вывод: значение функции в заданных четырех точках мы получили тремя разными способами. Для наглядности все полученные данные мы свели в итоговую сравнительную таблицу. Видно, что результаты получились не совсем одинаковые. Но однако в целом, отклонения в значениях в пределах 0,01 , что вполне допустимо для наших данных. Для того, чтобы получить более точные значения функции в определенной точке, необходимо, чтобы исходные данные были представлены более широким спектром узлов.
Задача 2.
Решение систем уравнений в EXCEL.
Решить заданную систему уравнений:
1) методом обратной матрицы;
2) методом простых итераций.
0,1 x1 + 4,6 x2 + 7,8 x3 = 9,8
2,8 x1 + 6,1 x2 + 2,8 x3 = 6,7
4,5 x1 + 5,7 x2 + 1,2 x3 = 5,8
Цель работы: научиться решать в EXCEL системы конечных уравнений методом обратной матрицы и простых итераций.

Внимание, отключите Adblock

Вы посетили наш сайт со включенным блокировщиком рекламы!
Ссылка для скачивания станет доступной сразу после отключения Adblock!

Скачать полную версию