49436 (Численные методы расчетов в Exel), страница 2
Описание файла
Документ из архива "Численные методы расчетов в Exel", который расположен в категории "". Всё это находится в предмете "информатика" из 1 семестр, которые можно найти в файловом архиве . Не смотря на прямую связь этого архива с , его также можно найти и в других разделах. Архив можно найти в разделе "контрольные работы и аттестации", в предмете "информатика, программирование" в общих файлах.
Онлайн просмотр документа "49436"
Текст 2 страницы из документа "49436"
Ввод формул:
а) Ввод формул для вычисления полинома Ньютона:
а.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;
полученных при помощи трех разных способов:
-
полинома Ньютона,
-
функции ТЕНДЕНЦИЯ,
-
функции ПРЕДСКАЗАНИЕ;
создадим сравнительную таблицу,
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 системы конечных уравнений методом обратной матрицы и простых итераций.
Основные понятия.
Уравнение — это математическая запись задачи о разыскании значений аргументов, при которых значения данных функций равны. Аргументы, от которых зависят функции, называются неизвестными, а значения неизвестных, при которых значения функций равны, называются решениями (корнями).
Матрица — это прямоугольная таблица каких-либо элементов aik (чисел, математических выражений), состоящая из 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
— матрица неизвестных:
x1
X = x2
x3
— матрица свободных членов:
9,8
B = 6,7
5,8
б). Найдем детерминант (определитель) матрицы А.