Структура таблицы для реализации метода простых итераций

Строки 1-5 содержат текстовые заголовки и числовые значения. В частности, диапазон A2:D4содержит значения коэффициентов системы. Эти ячейки заполняются вручную.

Также вручную в ячейки A6:C7 заносятся формулы. Они соответствуют формулам (2.6, 2.7). Затем ячейки A7:C7 выделяются и копируются вниз на несколько строк. Окончание итерационного процесса определяется визуально по совпадению значений двух последовательных приближений (в соседних строках таблицы).

Аналогичным образом можно реализовать и метод Зейделя. Структура таблицы показана в таблице 9; она незначительно отличается от таблицы 8 метода простых итераций (в ячейках В7, С7):

Таблица 9.

Структура таблицы для реализации метода Зейделя

Окончание процесса последовательных приближений определяется визуально по совпадению значений неизвестных в двух последовательных строках таблицы. В результате можно видеть, что при использовании метода Зейделя решение находится за меньшее число приближений (таблица содержит меньше строк).

Процесс сходимости методов последовательных приближений можно проиллюстрировать графиком. Для этого выделяются диапазоны значений неизвестных с заголовками (от ячеек А5:С5 вниз до конца таблицы) и строится диаграмма типа "График". Диаграмма показывает изменение значений неизвестных от приближения к приближению. В случае сходимости можно видеть стремление значений неизвестных к постоянным пределам, выражающим решение системы.

2. Решение с использованием матричных функций

Система линейных алгебраических уравнений в общем виде может быть представлена

, (2.14)

где A – матрица коэффициентов, x - вектор-столбец неизвестных, b – вектор-столбец правых частей. Например, для системы (2.10) такое представление имеет вид

. (2.15)

Умножив обе части уравнения (2.14) слева на матрицу, обратную по отношению к матрице А,получим

(2.16)

где А^-1 – матрица, обратная по отношению к матрице А. Поскольку произведение матриц, обратных по отношению друг к другу, дает единичную матрицу (у которой по диагонали стоят единицы, а остальные элементы равны 0), а произведение единичной матрицы на вектор-столбец равно этому вектор-столбцу, получаем из (2.16):

. (2.17)

- то есть, вектор решения системы.

Для работы с матрицами в EXCEL предусмотрены специальные матричные функции. Их список содержится в окне Мастера функций в категории "Математические". В частности, функция МОБР(…) позволяет вычислить обратную матрицу, функция МУМНОЖ(…) – произвести перемножение матриц. Для применения матричных функций необходимо в качестве аргументов подставлять диапазоны, содержащие соответствующие матрицы. Результатом вычисления также является диапазон. Необходимо, чтобы число строк и столбцов в соответствующих диапазонах позволяло произвести нужное действие. Так, при вычислении обратной матрицы получается матрица такого же размера, как и исходная, причем обе матрицы должны быть квадратными. При перемножении матриц число столбцов первой матрицы должно равняться числу строк второй, в результате получается матрица с числом строк таким же, как у первой матрицы и числом столбцов таким же, как у второй.

Ниже описан ход решения системы (2.15).

1. В диапазон А1:Е1 (рис. 14) вставляется текстовый заголовок Исходные данные (с выравниванием текста по центру диапазона). Столбцы А:Е выделяются и для них устанавливается числовой формат с указанием нужного числа цифр после запятой (4 – 5 цифр).

2. Диапазон А2:С4 заполняется значениями элементов матрицы системы, диапазон Е2:Е4 – значениями правых частей.

3. В диапазон А5:С5 вставляется заголовок Обратная матрица, в ячейку Е5 – Решение.

4. Выделяется диапазон А6:С8. Щелчком мыши по строке формул в нее вставляется курсор редактирования и набирается формула =МОБР(А2:С4). Ссылку на диапазон можно быстро создать, выделив этот диапазон мышью в процессе набора формулы. При наборе формулы с клавиатуры, без применения Мастера функций, после окончания набора следует нажать комбинацию клавиш <Ctrl>+<Shift>+<Enter> и формула автоматически вставляется во все ячейки выделенного диапазона, в которых появляются вычисленные значения элементов обратной матрицы. Порядок нажатия комбинации клавиш таков: нажимают первую, затем, не отпуская ее, нажимают вторую, затем, удерживая нажатыми эти клавиши, нажимают третью (не пытайтесь нажимать клавиши одновременно!).

При использовании Мастера функций после заполнения окна вставки аргумента следует, вместо щелчка по виртуальной клавише [OK], нажать вышеупомянутую комбинацию клавиш на клавиатуре. Если щелкнуть [OK], формула первоначально вставляется только в первую ячейку выделенного диапазона. Для ее распространения на весь диапазон следует заново выделить этот диапазон, затем щелкнуть мышью по строке формул для появления курсора редактирования и нажать <Ctrl>+<Shift>+<Enter>.

5. Выделяется диапазон Е6:Е8 и в него вставляется формула =МУМНОЖ(А6:С8,Е2:Е4 одним из описанных в п.4 способов. В результате столбец оказывается заполнен вычисленными значениями искомых неизвестных.

Рис. 14. Структура таблицы при решении

СЛАУ с помощью матричного умножения

Вид решения показан в таблице 10.

Таблица 10.

Результат решения СЛАУ с помощью матричного умножения

3. Применение модуля "Поиск решения".

"Поиск решения" – модуль EXCEL, применяемый для решения задач с несколькими переменными, поиска одного из нескольких корней алгебраических уравнений и вообще, для решения задач, в которых на искомое решение наложены дополнительные условия в виде уравнений или неравенств.

Модуль "Поиск решения" запускается командой меню: "Сервис" – "Поиск решения". После этого на экране появляется диалоговое окно, с помощью которого осуществляется решение задачи. Структуру и принцип работы с этим окном разберем на примере решения системы (2.10).

Пусть исходные коэффициенты системы вместе с заголовком заполняют диапазон А1:Е4(рис.15). Затем в ячейку А5 вставляется заголовок Решение, а ячейкам А6:А8 присваиваются имена Z_1, Z_2, Z_3. В качестве первого приближения в эти ячейки можно занести, например, значения правых частей уравнений. Ячейки Е6:Е8 заполняются формулами для вычисления левых частей уравнений системы. Для этого формула вносится в ячейку Е6 и затем копируется в две другие ячейки.

Рис. 15. Структура таблицы для применения модуля "Поиск решения"

После этого запускается модуль "Поиск решения". Диалоговое окно настройки модуля показано на рис. 16.

Одна из вспомогательных ячеек, в данном случае Е6, выбирается в качестве целевой.Компьютеру дается задание: подобрать значения неизвестных так, чтобы значение этой ячейки стало равным значению правой части первого уравнения системы. Для этого переключатель устанавливается на позицию "Значению" и в соответствующее окно ввода с клавиатуры заносится численноезначение правой части (в данном случае нельзя пользоваться ссылкой на ячейку, содержащую это значение). Два других уравнения образуют набор дополнительных условий, накладываемых на значения переменных. Эти условия заносятся в окно ввода "Ограничения" с помощью диалогового окна (рис. 17), появляющегося после нажатия виртуальной клавиши [Добавить].

Рис. 16. Окно настройки модуля "Поиск решения"

Рис.17. Окно добавления ограничений модуля "Поиск решения"

Это окно содержит три внутренних окна ввода. В окно "Ссылка на ячейку" вставляется ссылка на ячейку, содержащую формулу вычисления левой части второго уравнения, в окно "Ограничение" – ссылка на ячейку, содержащую значение правой части этого уравнения (при этом можно использовать способ быстрого создания ссылок). Второе окно ввода содержит один из знаков математических отношений (равенства или неравенства) и значок раскрывающегося списка. Раскрыв список щелчком, можно с помощью мыши выбрать нужный знак – в данном случае знак равенства. Затем следует щелкнуть [Добавить], если надо добавить следующее ограничение, либо [OK], если вставка ограничений закончена. Таким же образом в окно "Ограничения" вставляется второе условие (учитывающее третье уравнение системы). В окно ввода "Изменяя ячейки" (рис. 16) вставляются ссылки на ячейки, предназначенные для размещения искомого решения. После выполнения этих подготовительных действий щелчком нажимается виртуальная клавиша [Выполнить].

Рис. 18. Окно сообщения о результатах поиска решения

Принцип работы модуля "Поиск решения" ясен из описанной структуры окна. Компьютер подбирает значения неизвестных так, чтобы одновременно соблюдалось и основное требование (выполнения первого уравнения), и условия (выполнение второго и третьего уравнений). В случае, если ему это удается, в возникающем диалоговом окне появляется сообщение: "Решение найдено" (рис. 18). Имеющийся в окне переключатель позволяет либо занести в таблицу полученное решение, либо отменить его и восстановить ранее внесенные в ячейки значения.

Замечание:

Создавая таблицу для применения модуля "Поиск решения", можно обойтись без присваивания имен ячейкам А6:А8(рис.15) . В этом случае в формулах для ячеек Е6:Е8 необходимо использовать абсолютные ссылки на ячейки диапазона А6:А8.

4. Порядок выполнения работы.

1. На листе 1 заполнить массив исходных коэффициентов СЛАУ, выбранных согласно индивидуальному варианту задания. Провести анализ выполнения условий сходимости для методов последовательных приближений. Если необходимо, произвести соответствующие преобразования системы. Заполнить массив коэффициентов преобразованной системы.

2. Используя специальную вставку, дважды скопировать на лист 2 массив значений коэффициентов преобразованной системы. Вставленные массивы должны располагаться на одном горизонтальном уровне (т.е., в одних и тех же строках). Задать для ячеек таблицы числовой формат с 5 знаками после запятой.

3. Найти решение системы с помощью методов простых итераций и Зейделя, аналогично таблицам 8,9. Сравнить количество приближений в обоих методах, необходимое для достижения заданной точности решения. Для одного из методов (любого) построить график. отображающий изменение значений приближений в ходе итерационного процесса.

4. Используя специальную вставку, скопировать на лист 3 массив значений коэффициентов исходной системы. Найти решение с помощью матричного перемножения.

5. Используя специальную вставку, скопировать на лист 4 массив значений коэффициентов исходной системы. Найти решение с помощью модуля "Поиск решения".

6. Сделать выводы по результатам работы. В выводах сравнить использованные методы и приемы решения СЛАУ по следующим признакам:

а) возможность реализации с помощью простейших вычислительных средств (карандаш, бумага, калькулятор);

б) простота реализации в электронных таблицах EXCEL;

в) необходимость выполнения предварительных действий перед началом процесса решения (преобразование уравнений, проверка условий сходимости);

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

Определить метод, наиболее пригодный для практической работы в следующих условиях:

а) в отсутствие компьютера (с использованием простейших вычислительных средств);

б) единичное решение СЛАУ с применением электронных таблиц;

в) применение в составе сложной математической модели для решения промежуточных задач.

5. Контрольные вопросы и задания.

1. Как преобразуются уравнения СЛАУ для применения методов последовательных приближений?

2. В чем заключаются условия сходимости методов последовательных приближений?

3. Для СЛАУ, предложенной преподавателем, произвести проверку условий сходимости методов последовательных приближений.

4. В чем состоит различие между методом простых итераций и методом Зейделя?

5. Вспомните правило перемножения матриц и запишите формулу, определяющую значения элементов матрицы-результата. Как должны соотноситься количества строк и столбцов матриц при их перемножении? Сколько строк и столбцов будет иметь матрица, полученная в результате перемножения?

6. Чему равен результат перемножения единичной матрицы и вектора?

7. Какой раздел меню сдержит команду запуска модуля "Поиск решения"?

8. В чем заключается разница между модулями "Подбор параметра" и "Поиск решения"?

6. Варианты заданий для индивидуального выполнения.

Таблица 11.

Окончание таблицы 11.

2.4. Полиномиальное интерполирование.

Цель работы: Знакомство с понятием интерполирования, практическое освоение приемов интерполирования с помощью электронных таблиц.

Интерполированием называется построение функции, которая в отдельных точках (узлах), лежащих в пределах некоторого отрезка числовой оси, принимает заданные значения. Практическое применение интерполирования связано с т.н. "уплотнением таблиц". Если какая-либо таблица содержит значения некоторой функции, свойства какого-то вещества и т.п., то часто возникает необходимость определить значение этой же величины в точке, не попавшей в таблицу, но лежащей где-то между имеющимися табличными значениями. В этом случае в качестве приближенного значения искомой величины используют значение построенной интерполирующей функции

Существуют различные способы осуществления интерполирования. Чаще всего в качестве интерполирующей функции используется полином, степень которого на единицу меньше числа имеющихся узлов. Определение коэффициентов интерполирующего полинома может быть проведено разными способами (интерполирующий полином в форме Лагранжа, Стирлинга, Ньютона и др.), хотя все они дают в конечном итоге один и тот же результат. В лабораторной работе рассматривается непосредственное определение коэффициентов интерполирующего полинома.

1. Постановка задачи.

Даны N значений некоей функции y(x) в точках x₀, x₁, x₂, … x_N. Далее будем обозначать их: y_i = y(x_i), i=0, 1, … N. Требуется построить полином

, (2.18)

принимающий в любой из точек x_i значение y_i.

2. Решение.

Пользуясь свойством полинома P(x_i),запишем N+1 равенств для узлов интерполирования

(2.19)

Они образуют систему из N+1 линейных алгебраических уравнений для определения N+1 неизвестных коэффициентов полинома P(x). Решив систему одним из способов, разобранных в лабораторной работе 2.3, можно построить искомый интерполирующий полином.

На рис. 19 приведены таблица и график, иллюстрирующие построение интерполирующего полинома 3 степени, отображающего вид функции y=sin x на отрезке [0, p]. Для размещения координат x_i, y_i четырех характерных точек, выбранных в качестве узлов интерполирования, использован диапазон D3:E6. Система уравнений решена с использованием матричных функций EXCEL, решение (коэффициенты полинома) находится в диапазоне K8:K11. Для получения значений полинома в ячейку С2 заносится формула:

=$K$8+$K$9*A2+$K$10*A2^2+$K$11*A2^3

- и копируется вниз в диапазон С2:С12.

Рис. 19. Интерполирование полиномом 3 степени

На рис. 20 приводится график, показывающий сравнение погрешностей интерполирования (под погрешностью интерполирования понимается разница между исходной функцией и построенным полиномом) при использовании интерполяционных полиномов 3 и 4 степени. Погрешности обозначены соответственно d3 и d4.

Рис. 20. Погрешности интерполирования функции sin x

Видно, что применение полинома более высокой степени позволяет существенно уменьшить погрешность интерполирования.

3. Порядок выполнения работы.

1. Заполнить нужные ячейки текстовыми заголовками по образцу, представленному на рис. 19.

2. Используя столбцы А и В, составить таблицу значений функции y(x)согласно индивидуальному варианту из таблицы 12. Таблица должна содержать 11 значений (т.е., предусматривать 10 шагов изменения аргумента).

3. Построить график протабулированной функции (диаграмма типа "точечная", вид – линия с маркерами точек).

4. Выбрать, основываясь на виде графика, 4 характерных точки из числа имеющихся в таблице (ими являются начало и конец отрезка табулирования, а также точки перегибов и т.п.).

5. Используя специальную вставку, скопировать по очереди значения x_i, y_i, соответствующие выбранным точкам, в диапазон D2:E5.

6. Заполнить диапазон G3:J6 таблиц значениями элементов матрицы коэффициентов СЛАУ (19) для определения коэффициентов полинома

и столбец K3:K6 значениями y_i, стоящими в правых частях уравнений.

Заполнение матрицы коэффициентов проводится в следующем порядке. В ячейку G3 с клавиатуры заносится значение 1 и копируется в ячейки G4:G6. В ячейку H3 вносится формула =D3 и копируется вниз в ячейки H4:H6. Таким образом заполняются два первых столбца матрицы коэффициентов.

Затем в ячейку I3 вносится формула =H3*$H3. В ячейке, таким образом, вычисляется значение x₁². Затем эта формула копируется вниз в ячейки I4:I6 и затем весь диапазон-столбец I3:I6 копируется вправо, в столбецJ.

Поскольку ссылка на второй сомножитель в формуле ячейки I3 является смешанной, это не мешает преобразованию ссылок при копировании формулы вниз, поэтому в каждой строке матрицы используется соответствующее этой строке значение x_i. При копировании вправоналичие знака $ препятствует преобразованию смешанной ссылки. Поэтому в ячейках столбца J происходит умножение значений из соседних слева ячеек (первый множитель формулы – относительная ссылка на левую соседнюю ячейку) на ячейки соответствующих строк столбца Н (то есть, на x_i). В результате, при любом размере матрицы, в ее столбцах, начиная с четвертого, последовательно вычисляются значения x_i³, x_i⁴, и т.д.

7. Решить систему уравнений и определить коэффициенты полинома. Для решения следует использовать любой способ, в котором при изменении исходных данных происходит автоматический пересчет таблицы. Из способов, рассмотренных в работе 2.3, таким является применение прямого перемножения матриц.

8. В столбце С протабулировать полином P(x) при тех же значениях x_i, при которых табулировалась заданная функция.

9. Добавить на график функции y(x) график полученного полинома (см. п. 1.7).

10. Сравнить графики исходной функции и полинома. Если результаты интерполяции неудовлетворительны (графики сильно различаются), попробовать поменять выбранные узлы интерполирования, копируя через буфер обмена новые значения в ячейки диапазона D3:E6 взамен старых. При правильно построенной таблице исходными влияющими ячейками являются только ячейки этого диапазона и пересчет остальной части таблицы (а также соответствующее перестроение графика) происходит автоматически.

11. С помощью буфера обмена скопировать таблицу значений исходной функции на лист 2. На листе 2 выполнить построение интерполирующего полинома 4 степени. Таблицу для расчета интерполирующего полинома создать самостоятельно по аналогии с расчетом полинома 3 степени.

12. С помощью буфера обмена скопировать на лист 3 значения x_i, P₃(x_i), P₄(x_i)_. Составить таблицу значений погрешностей интерполирования

d₃(x) = P₃(x) – y(x)

(2.20)

d₄(x) = P₄(x) – y(x).

13. Построить график погрешностей. Сделать вывод о пригодности полинома 3 или 4 степени для интерполирования заданной функции.

4. Контрольные вопросы и задания.

1. Что понимается под интерполированием? Для чего оно применяется?

2. Какова степень интерполирующего полинома, построенного по N узлам?

3. Как меняется погрешность интерполирования при увеличении степени интерполирующего полинома?

4. Продолжите на листе 2 таблицу значений заданной функции дальше примерно на такой же отрезок числовой оси. Рассчитайте для новых значений интерполирующий полином 4 степени. Постройте графики функции и полинома. Проанализируйте графики и ответьте на вопрос: можно ли использовать интерполирующую функцию за пределами того отрезка числовой оси, на котором она была построена?

5. Варианты заданий для индивидуального выполнения.

Таблица 12.

Окончание таблицы 12

2.5. Приближенное вычисление определенных интегралов

Цель работы: знакомство с методами приближенного вычисления определенных интегралов вида:

(2.21)

и освоение приемов вычисления интегралов с помощью электронных таблиц.

Прежде. чем приступить к работе, следует вспомнить ряд сведений из курса высшей математики: определение понятия "определенный интеграл", геометрический смысл определенного интеграла.

Для приближенного вычисления отрезок [a,b] разбивается на малые отрезки (шаги) hравной величины. Количество шагов для рассматриваемых в работе методов: для метода трапеций безразлично, для метода Симпсона-Ньютона оно должно быть четным числом. Точки, являющиеся границами отрезков, называются узлами и обозначаются

(2.22)

где N - число шагов. Для каждого узла вычисляется значение функции

1. Метод трапеций.

Расчетная формула метода трапеций получается следующим образом. На каждом шаге интегрируемая функция заменяется на линейную, проходящую через узловые точки (проводится ее интерполирование полиномом 1 степени, т.е., линейной функцией). Затем на каждом шаге эта линейная функция интегрируется, и получившиеся в результате для каждого шага выражения складываются. Итоговая формула имеет вид:

(2.23)

Таким образом, вычисление интеграла выполняется в следующей последовательности:

1. Табулируется функция f(x).

2. Вычисляется сумма значений функции в узлах.

3. От вычисленной суммы отнимается полусумма значений функции в первом и последнем узлах (т.е., на концах отрезка).

4. Результат умножается на величину шага.

Для быстрого вычисления суммы значений функции во всех узлах (т.е., суммы значений, размещенных в столбце таблицы), можно воспользоваться кнопкой [S] ([Автосумма]) на стандартной панели инструментов. Для этого выделяется столбец суммируемых значений и одна дополнительная пустая ячейка снизу, затем нажимается кнопка [Автосумма]. При этом в пустую ячейку заносится значение вычисленной суммы значений ячеек столбца. Выделив эту ячейку, можно видеть в строке формул, что это значение функции СУММ(…),аргументом которой является выделенный первоначально диапазон за исключением ячейки с результатом. Затем в соседних ячейках размещаются формулы для производства вычислений, соответствующих этапам 3 и 4 вышеописанной последовательности действий.

Можно не использовать автосуммирование, а прямо внести в какую-нибудь подходящую ячейку конечную формулу вычисления интеграла. Ниже в качестве примера приведена структура таблицы приближенного вычисления интеграла

. (2.24)

Содержание ячеек показано в таблице 13, вид итоговой таблицы – на рис. 21.

В ячейках А1:В1 размещены текстовые заголовки столбцов таблицы. В ячейке В15 для контроля помещено точное значение указанного интеграла, вычисленное с помощью соответствующей формулы. Если отформатировать ячейки столбца В, выбрав для них числовой формат с 5 знаками после запятой, в ячейке В13 окажется приближенное значение интеграла, равное в данном случае 0,63265, а в ячейке В15 будет точное значение 0,63212 (рис. 21). Таким образом, расчет по формуле трапеций позволил вычислить значение интеграла с точностью до 2 знаков после запятой (при округлении до 3 знаков приближенное значение получится равным 0,633, тогда как точное - 0,632).

Таблица 13.