49436 (Численные методы расчетов в Exel), страница 2

Ввод формул:

а) Ввод формул для вычисления полинома Ньютона:

а.1) для вычисления первого полинома Ньютона, который равен (x-x₀) · Дy₀ / 1!h = (x-x₀) / 1h ·Дy₀, содержимое ячейки 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 будет сумма всех членов полинома Ньютона, кроме y₀. При 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 предоставляет возможность аппроксимации с использованием “функций аппроксимации кривой”

Пусть в узлах x₀ , x₁, …, x _n известны значения f(x₀), f(x₁), … ,f(x _n). Необходимо осуществить экстраполяцию (прогнозирование), т.е. вычислить значения f(x _n+1), f(x _n+2), … .

В категории Статистические функции EXCEL для этого используются две функции: ТЕНДЕНЦИЯ и ПРЕДСКАЗАНИЕ, осуществляющие линейную аппроксимацию кривой для данных массивов

x (x₀ , x₁ , … , x _n) и y (y₀ ,y₁ , … , 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 ₁ = 0,149;

x ₂ = 0,240;

x ₃ = 0,430;

x ₄ = 0,560;

полученных при помощи трех разных способов:

1. полинома Ньютона,

2. функции ТЕНДЕНЦИЯ,

1. функции ПРЕДСКАЗАНИЕ;

создадим сравнительную таблицу,

*Результаты вычислений округлены до двух знаков после запятой.

Вывод: значение функции в заданных четырех точках мы получили тремя разными способами. Для наглядности все полученные данные мы свели в итоговую сравнительную таблицу. Видно, что результаты получились не совсем одинаковые. Но однако в целом, отклонения в значениях в пределах 0,01 , что вполне допустимо для наших данных. Для того, чтобы получить более точные значения функции в определенной точке, необходимо, чтобы исходные данные были представлены более широким спектром узлов.

Задача 2.

Решение систем уравнений в EXCEL.

Решить заданную систему уравнений:

1) методом обратной матрицы;

2) методом простых итераций.

0,1 x₁ + 4,6 x₂ + 7,8 x₃ = 9,8

2,8 x₁ + 6,1 x₂ + 2,8 x₃ = 6,7

4,5 x₁ + 5,7 x₂ + 1,2 x₃ = 5,8

Цель работы: научиться решать в EXCEL системы конечных уравнений методом обратной матрицы и простых итераций.

Основные понятия.

Уравнение — это математическая запись задачи о разыскании значений аргументов, при которых значения данных функций равны. Аргументы, от которых зависят функции, называются неизвестными, а значения неизвестных, при которых значения функций равны, называются решениями (корнями).

Матрица — это прямоугольная таблица каких-либо элементов a_ik (чисел, математических выражений), состоящая из m строк и n столбцов. Если m = n , то матрица называется квадратной.

Детерминант (определитель) — это число detA, которое можно сопоставить квадратной матрице А.

Минором некоторого элемента а_ij определителя n-го порядка называется определитель n первого порядка, полученный из исходного путем вычеркивания строки и столбца, на пересечении которых находится выбранный элемент.

Алгебраическим дополнением элемента а_ij определителя называется его минор, взятый со знаком “+”, если сумма “ i+j” четное число, и со знаком “-“ , если эта сумма нечетная.

Итерация — это повторное применение каких-либо математических операций. Происходит от латинского “iteratio” ,что в переводе значит “повторение”.

Решение.

1). Математический расчет решения системы уравнений методом обратной матрицы.

Дана система трех линейных уравнений с тремя неизвестными.

а). Рассмотрим матрицы:

— матрица системы (составлена из коэффициентов при неизвестных):

0,1 4,6 7,8

А = 2,8 6,1 2,8

4,5 5,7 1,2

— матрица неизвестных:

x₁

X = x₂

x₃

— матрица свободных членов:

9,8

B = 6,7

5,8

б). Найдем детерминант (определитель) матрицы А.