Метод простой итерации в excel пример. Решение системы уравнений методом Крамера в Excel. С использованием режима «Поиск решений»

Напомню, что циклическая ссылка появляется, если в ячейку Excel введена формула, содержащая ссылку на саму эту ячейку (напрямую или через цепочку других ссылок). Например (рис. 1), в ячейке С2 находится формула, ссылающаяся на саму ячейку С2.

Но!.. Не всегда циклическая ссылка является бедствием. Циклическую ссылку можно использовать для решения уравнений итерационным способом. Для начала нужно позволить Excel`ю вести вычисления, даже при наличии циклической ссылки. В обычном режиме Excel, обнаружив циклическую ссылку, выдаст сообщение об ошибке, и потребует ее устранения. В обычном режиме Excel не может провести вычисления, так как циклическая ссылка порождает бесконечный цикл вычислений. Можно, либо устранить циклическую ссылку, либо допустить вычисления по формуле с циклической ссылкой, но ограничив число повторений цикла. Для реализации второй возможности щелкните на кнопке «Office» (в левом верхнем углу), а затем на «Параметры Excel» (рис. 2).

Скачать заметку в формате , примеры в формате

Рис. 2. Параметры Excel

В открывшемся окне «Параметры Excel» перейдите на вкладку Формулы и отметьте «Включить итеративные вычисления» (рис. 3). Помните, что эта опция включается для приложения Excel в целом (а не для одного файла), и будет действовать, пока вы ее не отключите.

Рис. 3. Включить итеративные вычисления

На этой же вкладе, можно выбрать, как будут вестись вычисления: автоматически или вручную. При автоматическом вычислении Excel сразу рассчитает конечный результат, при вычислениях, вручную, можно будет наблюдать результат каждой итерации (простым нажатием F9 запуская каждый новый цикл вычисления).

Решим уравнение третьей степени: х 3 – 4х 2 – 4х + 5 = 0 (рис. 4). Для решения этого уравнения (и любого другого уравнения совершенно произвольного вида) понадобится всего одна ячейка Excel.

Рис. 4. График функции f(x)

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

(1) x = x – f(x)/f’(x), где

х – переменная;

f(x) – функция, задающая уравнение, корни которого мы ищем; f(x) = х 3 – 4х 2 – 4х + 5

f’(x) – производная нашей функции f(x); f’(x) = 3х 2 – 8х – 4; производные основных элементарных функций можно посмотреть .

Если вы заинтересовались, откуда взялась формула (1), можете почитать, например, .

Итоговая рекуррентная формула имеет вид:

(2) х = x – (х 3 – 4х 2 – 4х + 5)/(3х 2 – 8х – 4)

Выберем любую ячейку на листе Excel (рис. 5; в нашем примере это ячейка G19), присвоим ей имя х , и введем в нее формулу:

(3) =x-(x^3-4*x^2-4x+5)/(3*x^2-8*x-4)

Можно вместо х использовать адрес ячейки… но согласитесь, что имя х , смотрится привлекательнее; следующую формулу я ввел в ячейку G20:

(4) =G20-(G20^3-4*G20^2-4*G20+5)/(3*G20^2-8*G20-4)

Рис. 5. Рекуррентная формула: (а) для поименованной ячейки; (б) для обычного адреса ячейки

Как только мы введем формулу и нажмем Enter, в ячейке сразу же появится ответ – значение 0,77. Это значение соответствует одному из корней уравнения, а именно второму (см. график функции f(x) на рис. 4). Поскольку начальное приближение не задавалось, итерационный вычислительный процесс начинался со значения, по умолчанию хранимого в ячейке х и равного нулю. Как же получить остальные корни уравнения?

Для изменения стартового значения, с которого рекуррентная формула начинает свои итерации, предлагается использовать функцию ЕСЛИ:

(5) =ЕСЛИ(x=0;-5;x-(x^3-4*x^2-4*x+5)/(3*x^2-8*x-4))

Здесь значение «-5» – начальное значение для рекуррентной формулы. Изменяя его, можно выйти на все корни уравнения.

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

Рассмотрим на примерах некоторые варианты решений.

Решение уравнений методом подбора параметров Excel

Инструмент «Подбор параметра» применяется в ситуации, когда известен результат, но неизвестны аргументы. Excel подбирает значения до тех пор, пока вычисление не даст нужный итог.

Путь к команде: «Данные» - «Работа с данными» - «Анализ «что-если»» - «Подбор параметра».

Рассмотрим на примере решение квадратного уравнения х 2 + 3х + 2 = 0. Порядок нахождения корня средствами Excel:

Для подбора параметра программа использует циклический процесс. Чтобы изменить число итераций и погрешность, нужно зайти в параметры Excel. На вкладке «Формулы» установить предельное количество итераций, относительную погрешность. Поставить галочку «включить итеративные вычисления».



Как решить систему уравнений матричным методом в Excel

Дана система уравнений:

Получены корни уравнений.

Решение системы уравнений методом Крамера в Excel

Возьмем систему уравнений из предыдущего примера:

Для их решения методом Крамера вычислим определители матриц, полученных заменой одного столбца в матрице А на столбец-матрицу В.

Для расчета определителей используем функцию МОПРЕД. Аргумент – диапазон с соответствующей матрицей.

Рассчитаем также определитель матрицы А (массив – диапазон матрицы А).

Определитель системы больше 0 – решение можно найти по формуле Крамера (D x / |A|).

Для расчета Х 1: =U2/$U$1, где U2 – D1. Для расчета Х 2: =U3/$U$1. И т.д. Получим корни уравнений:

Решение систем уравнений методом Гаусса в Excel

Для примера возьмем простейшую систему уравнений:

3а + 2в – 5с = -1
2а – в – 3с = 13
а + 2в – с = 9

Коэффициенты запишем в матрицу А. Свободные члены – в матрицу В.

Для наглядности свободные члены выделим заливкой. Если в первой ячейке матрицы А оказался 0, нужно поменять местами строки, чтобы здесь оказалось отличное от 0 значение.

Примеры решения уравнений методом итераций в Excel

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

Делается это на вкладке «Формулы» в «Параметрах Excel». Найдем корень уравнения х – х 3 + 1 = 0 (а = 1, b = 2) методом итерации с применением циклических ссылок. Формула:

Х n+1 = X n – F (X n) / M, n = 0, 1, 2, … .

M – максимальное значение производной по модулю. Чтобы найти М, произведем вычисления:

f’ (1) = -2 * f’ (2) = -11.

Полученное значение меньше 0. Поэтому функция будет с противоположным знаком: f (х) = -х + х 3 – 1. М = 11.

В ячейку А3 введем значение: а = 1. Точность – три знака после запятой. Для расчета текущего значения х в соседнюю ячейку (В3) введем формулу: =ЕСЛИ(B3=0;A3;B3-(-B3+СТЕПЕНЬ(B3;3)-1/11)).

В ячейке С3 проконтролируем значение f (x): с помощью формулы =B3-СТЕПЕНЬ(B3;3)+1.

Корень уравнения – 1,179. Введем в ячейку А3 значение 2. Получим тот же результат:

Корень на заданном промежутке один.

Пример 3.1. Найти решение системы линейных алгебраических уравнений (3.1) методом Якоби.

Итерационные методы можно использовать для заданной системы, т.к. выполняется условие «преобладания диагональных коэффициентов», что обеспечивает сходимость этих методов.

Расчетная схема метода Якоби приведена на рис (3.1).

Приведите систему(3.1). к нормальному виду:

, (3.2)

или в матричной форме

, (3.3)

Рис.3.1.

Для определения количества итераций, необходимое для достижения заданной точности e, и приближенного решения системы полезно в столбце Н установить Условный формат . Результат такого форматирования виден на рис.3.1. Ячейки столбца Н, значения которых удовлетворяют условию (3.4) тонированы.

(3.4)

Анализируя результаты, принимаем за приближенное решение исходной системы с заданной точностью e=0,1 четвертую итерацию,

т.е. х 1 =10216; х 2 = 2,0225, х 3 = 0,9912

Изменяя значение e в ячейке Н5 можно получить новое приближенное решение исходной системы с новой точностью.

Проанализируйте сходимость итерационного процесса, построив графики изменения каждой компоненты решения СЛАУ в зависимости от номера итерации.

Для этого выделите блок ячеек А10:D20 и, используя Мастер диаграмм , постройте графики, отражающие сходимость итерационного процесса, рис.3.2.

Аналогично решается система линейных алгебраических уравнений методом Зейделя.

Лабораторная работа №4

Тема. Численные методы решения линейных обыкновенных дифференциальных уравнений с краевыми условиями. Метод конечных разностей

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

Проанализировать полученные результаты. Варианты заданий приведены в приложении 4.

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

1. Постройте вручную конечноразностную аппроксимацию краевой задачи (конечноразностную СЛАУ) с шагом h , заданным вариантом.

2. Используя метод конечных разностей, сформируйте в Excel систему линейных алгебраических конечно-разностных уравнений для шага h разбивки отрезка . Запишите эту СЛАУ на рабочем листе книги Excel . Расчетная схема приведена на рис.4.1.

3. Полученную СЛАУ решите методом прогонки.

4. Проверьте правильность решения СЛАУ с помощью надстройки Excel Поиск решения .

5. Уменьшите шаг сетки в 2 раза и еще раз решите задачу. Результаты представьте в графическом виде.

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

Решение краевой задачи с использованием электронных таблиц Microsoft Excel.

Пример 4.1. Методом конечных разностей найти решение краевой задачи , y(1)=1, y ’ (2)=0,5 на отрезке xÎ с шагом h=0,2 и с шагом h=0,1. Сравнить полученные результаты и сделать вывод о необходимости продолжения или о прекращении счета.

Расчетная схема для шага h=0,2 приведена на рис.4.1.

Полученное решение (сеточную функцию) Y {1.000, 1.245, 1.474, 1.673, 1.829, 1.930}, Х {1; 1,2; 1,4; 1,6; 1,8;2} в столбце L и B можно принять за первую итерацию (первое приближение) исходной задачи.

Для нахождения второй итерации сделайте сетку вдвое гуще (n=10, шаг h=0,1) и повторите приведенный выше алгоритм.

Это можно проделать на том же или на другом листе книги Excel . Решение (второе приближение) приведено на рис.4.2.

Сравните полученные приближенные решения. Для наглядности можно построить графики этих двух приближений (двух сеточных функций), рис.4.3.

Порядок построения графиков приближенных решений краевой задачи

1. Постройте график решения задачи для разностной сетки с шагом h=0,2 (n=5).

2. Активизируйте уже построенный график и выберите команду меню Диаграмма\Добавить данные

3. В окне Новые данные укажите данные x i , y i для разностной сетки с шагом h/2 (n=10).

4. В окне Специальная вставка установите флажки в полях:

Ø новые ряды,

Как видно из приведенных данных, два приближенных решения краевой задачи (две сеточные функции) отличаются друг от друга не более, чем на 5%. Поэтому за приближенное решение исходной задачи принимаем вторую итерацию, т.е.

Y {1, 1.124, 1.246, 1.364, 1.478, 1.584, 1.683, 1.772, 1.849, 1.914, 1.964}

Лабораторная работа №5

Нахождение корней уравнений

Графический способ нахождения корней заключается в построении графика функции f(x) на отрезке . Точка пересечения графика функции с осью абсцисс дает приближенное значение корня уравнения.

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

При нахождении корней расчетным путем для непрерывных функций f(x) руководствуются следующими соображениями:

– если на концах отрезка функция имеет разные знаки, то между точками a и b на оси абсцисс имеется нечетное число корней;

– если же функция имеет одинаковые знаки на концах интервала, то между a и b имеется четное число корней или их совсем нет;

– если на концах отрезка функция имеет разные знаки и либо первая производная, либо вторая производная не меняют знаки на этом отрезке, то уравнение имеет единственный корень на отрезке .

Найдем все действительные корни уравнения x 5 –4x–2=0 на отрезке [–2,2]. Создадим электронную таблицу.

Таблица 1

В таблице 2 получены результаты расчета.

Таблица 2

Аналогично находится решение на интервалах [-2,-1], [-1,0].

Уточнение корней уравнения

С использованием режима «Поиск решений»

Для данного выше уравнения следует уточнить с погрешностью Е=0,001 все корни уравнения x 5 –4x–2=0.

Для уточнения корней на интервале [-2,-1] составим электронную таблицу.

Таблица 3

Запускаем режим «Поиск решения» в меню «Сервис». Выполняем команды режима. Режим показа отобразит найденные корни. Аналогично уточняем корни на других интервалах.

Уточнение корней уравнений

С использованием режима «Итерации»

Метод простых итераций имеет два режима «Вручную» и «Автоматически». Для запуска режима «Итерации» в меню «Сервис» открывают вкладку «Параметры». Далее следуют командам режима. На вкладке «Вычисления» можно выбрать режим автоматический или ручной.

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

Решение систем уравнений в Excel проводится методом обратных матриц. Решить систему уравнений:

Создадим электронную таблицу.

Таблица 4

	A	B	C	D	E
	Решение системы уравнений.
		Ax=b
	Исходная матрица А		Правая часть b
	-8
			-3
			-2		-2
	Обратная матрица (1/А)		Вектор решения x=(1/A)/b
	=МОБР(А6:С8)	=МОБР(А6:С8)	=МОБР(А6:С8)		=МУМНОЖ(А11:С13;Е6:Е8)
	=МОБР(А6:С8)	=МОБР(А6:С8)	=МОБР(А6:С8)		=МУМНОЖ(А11:С13;Е6:Е8)
	=МОБР(А6:С8)	=МОБР(А6:С8)	=МОБР(А6:С8)		=МУМНОЖ(А11:С13;Е6:Е8)

Функция МОБР возвращает массив значений, который вставляется сразу в целый столбец ячеек.

В таблице 5 представлены результаты расчета.

Таблица 5

	A	B	C	D	E
	Решение системы уравнений.
		Ax=b
	Исходная матрица А		Правая часть b
	-8
			-3
			-2		-2
	Обратная матрица (1/А)		Вектор решения x=(1/A)/b
	-0,149	0,054	-0,230
	0,054	0,162	-0,189
	-0,122	0,135	-0,824

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

1. Турчак Л.И. Основы численных методов: Учеб. пособие для вузов/ ред. В.В. Щенников.–М.: Наука, 1987.–320с.

2. Банди Б. Методы оптимизации. Вводный курс.–М.: Радио и связь, 1988.–128с.

3. Евсеев А.М., Николаева Л.С. Математическое моделирование химических равновесий.–М.: Изд-во Моск. ун-та, 1988.–192с.

4. Безденежных А.А. Инженерные методы составления уравнений скоростей реакций и расчета кинетических констант.–Л.: Химия, 1973.–256с.

5. Степанова Н.Ф., Ерлыкина М.Е., Филиппов Г.Г. Методы линейной алгебры в физической химии.–М.: Изд-во Моск. ун-та, 1976.–359с.

6. Бахвалов Н.С. и др. Численные методы в задачах и упражнениях: Учеб. пособие для вузов/ Бахвалов Н.С., Лапин А.В., Чижонков Е.В. - М.: Высш. шк., 2000.-190с. - (Высшая математика/ Садовничий В.А.)

7. Применение вычислительной математики в химической и физической кинетике, под ред. Л.С. Полак, М.: Наука, 1969, 279 стр.

8. Алгоритмизация расчетов в химической технологии Б.А. Жидков, А.Г. Бондарь

9. Вычислительные методы для инженеров-химиков. Х.Розенброк, С.Стори

10. Орвис В.Д. Excel для ученых, инженеров и студентов. – Киев: Юниор, 1999.

11. Ю.Ю. Тарасевич Численные методы на Mathcade – Астраханский гос.пед.ун-т: Астрахань, 2000.

Дана система n алгебраических уравнений сn неизвестными:

Эту систему можно записать в матричном виде:
,

;;.

где A - квадратная матрица коэффициентов,X - вектор-столбец неизвестных,B - вектор-столбец свободных членов.

Численные методы решения систем линейных уравнений делятся на прямые и итерационные. Первые используют конечные соотношения для вычисления неизвестных. Пример - метод Гаусса. Вторые основаны на последовательных приближениях. Примеры - метод простой итерации и метод Зейделя.

1. Метод Гаусса

Метод основан на приведении матрицы системы к треугольному виду. Это достигается последовательным исключением неизвестных из уравнений системы. Сначала с помощью первого уравнения исключается x 1 из всех последующих уравнений. Затем с помощью второго уравнения исключаетсяx 2 из последующих и т.д. Этот процесс называется прямым ходом метода Гаусса и продолжается до тех пор, пока в левой части последнегоn -го уравнения не останется лишь один член с неизвестнымx n . В результате прямого хода система принимает вид:

(2)

Обратный ход метода Гаусса состоит в последовательном вычислении искомых неизвестных, начиная с x n и кончаяx 1 .

1. Метод простой итерации и метод Зейделя

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

.

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

(3)

где k - номер итерации, - заданная точность.

Недостатком итерационных методов является жесткое условие сходимости. Для сходимости метода необходимо и достаточно, чтобы в матрице A абсолютные значения всех диагональных элементов были больше суммы модулей всех остальных элементов в соответствующей строке:

(4)

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

(5)

Отличие метода Зейделя от метода простой итерации заключается в том, что при вычислении очередного приближения вектора неизвестных используются уже уточненные значения на этом же шаге итерации. Это обеспечивает более быструю сходимость метода Зейделя. Приведенная система уравнений имеет вид:

(6)

3.4. Реализация в пакете Excel

В качестве примера рассмотрим систему уравнений:

Данная система удовлетворяет условию сходимости и может быть решена как прямыми, так и итерационными методами. Последовательность действий (рис.7):

Оформить заголовок в строке 1 «Численные методы решения систем линейных уравнений».

В области D3:H6 ввести исходные данные, как показано на рисунке.

Ввести в ячейку F8 текст заголовка «Метод Гаусса» (выравнивание по центру).

Скопировать исходные данные E4:H6 в областьB10:E12. Это - исходные данные для прямого хода метода Гаусса. Обозначим соответствующие строкиA1,A2 иA3.

Подготовить место для первого прохода, обозначив в области G10:G12 названия строкB1,B2 иB3.

Ввести в ячейку H10 формулу «=B10/$B$10». Скопировать эту формулу на ячейкиI10:K10. Это - нормировка на коэффициентa 11 .

Ввести в ячейку H11 формулу «=B11-H10*$B$11». Скопировать эту формулу на ячейкиI11:K11.

Ввести в ячейку H12 формулу «=B12-H10*$B$12». Скопировать эту формулу на ячейкиI12:K12.

Подготовить место для второго прохода, обозначив в области A14:A16 названия строкC1,C2 иC3.

Ввести в ячейку B14 формулу «=H10». Скопировать эту формулу на ячейкиC14:E14.

Ввести в ячейку B15 формулу «=H11/$I$11». Скопировать эту формулу на ячейкиC15:E15.

12. Ввести в ячейку В16 формулу «=Н12-В15*$I$12». Скопировать эту форму­лу на ячейки С16:Е16.

13. Подготовить место для третьего прохода, обозначив в области G14:G16 на­звания строк D1, D2 и D3.

14. Ввести в ячейку H14 формулу «=В14». Скопировать эту формулу на ячейки I14:К14.

15. Ввести в ячейку H15 формулу «=В15». Скопировать эту формулу на ячейки I15:К15.

16. Ввести в ячейку Н16 формулу «=B16/$D$16». Скопировать эту формулу на ячейки I16:К16.

17. Подготовить место для обратного хода метода Гаусса, введя в ячейки В18, E18 и H18 соответствующие тексты «х3=», «х2=» и «х1=».

18. Ввести в ячейку С18 формулу «=К16». Получим значение переменной х 3.

19. Ввести в ячейку F18 формулу «=К15-J15*К16». Получим значение перемен­нойх 2.

20.Ввести в ячейку I18 формулу «=K10-I10*F18-J10*C18». Получим значение переменнойх 1.

21. Ввести в ячейку F21 текст заголовка «Метод простой итерации» (выравни­вание по центру).

22. Ввести в ячейку J21 текст «е=» (выравнивание по правому краю).

23. Ввести в ячейку К21 значение точности е (0,0001).

24. Обозначить в области А23:А25 названия переменных.

25. В области В23:В25 задать начальные значения переменных (нули).

26. Ввести в ячейку С23 формулу «=($H$4-$F$4*B24-$G$4*B25)/$E$4». Полу­чим значение переменной х 1 на первой итерации.

27. Ввести в ячейку С24 формулу «=($H$5-$E$5*B23-$G$5*B25)/$F$5». Полу­чим значение переменной х 2 на первой итерации.

28. Ввести в ячейку С25 формулу «=($H$6-$E$6*B23-$F$6*B24)/$G$6». Полу­чим значение переменной х 3 на первой итерации.

29. Ввести в ячейку С26 формулу «=ЕСЛИ(АВS(С23-В23)>$К$21;" "; ЕСЛИ(АВS(С24-В24)>$К$21;" ";ЕСЛИ(АВS(С25-В25)>$К$21;" "; ""корни")))». Это - проверка на достижение заданной точности (при этом печата­ется сообщение «корни»).

30. Выделить диапазон С23:С26 и скопировать его до столбца К, используя при­ем протаскивания. При появлении в строке 26 сообщения «корни» соответст­вующий столбец будет содержать приближенные значения переменных х 1,x 2, x 3, которые являются решением системы уравнений с заданной точно­стью.

31. В области А27:К42 построить диаграмму, показывающую процесс прибли­жения значений переменных х 1,х 2,x 3 к решению системы. Диаграмма стро­ится в режиме «График», где по оси абсцисс откладывается номер итерации.

32. Ввести в ячейку F43 текст заголовка «Метод Зейделя» (выравнивание по центру).

33. Ввести в ячейку J43 текст «е=» (выравнивание по правому краю).

34. Ввести в ячейку К43 значение точности е(0,0001).

35. Обозначить в области А45:А47 названия переменных.

36. В области В45:В47 задать начальные значения переменных (нули).

37.Ввести в ячейку С45 формулу «=($H$4-$F$4*B46-$G$4*B47)/$E$4». Полу­чим значение переменной х 1 на первой итерации.

38.Ввести в ячейку С46 формулу «=($H$5-$E$5*C45-$G$5*B47)/$F$5». Полу­чим значение переменной х 2 на первой итерации.

39. Ввести в ячейку С47 формулу «=($H$6-$E$6*C45-$F$6*C46)/$G$6». Полу­чим значение переменной x 3 ,на первой итерации.

40. Ввести в ячейку С48 формулу «=ЕСЛИ(АВ5(С45-В45)>$К$43;" "; ЕСЛИ(АВS(С46-В46)>$К$43;" ";ЕСЛИ{АВS(С47-В47)>$К$43;" ";"кор­ни")))».

41. Выделить диапазон С45:С48 и скопировать его до столбца К, используя при­ем протаскивания. При появлении в строке 26 сообщения «корни» соответст­вующий столбец будет содержать приближенные значения переменных х 1,х 2,x 3, которые являются решением системы уравнений с заданной точно­стью. Видно, что метод Зейделя сходится быстрее, чем метод простой итера­ции, то есть заданная точность здесь достигается за меньшее число итераций.

42. В области А49:К62 построить диаграмму, показывающую процесс прибли­жения значений переменных х1, х2, x3 к решению системы. Диаграмма стро­ится в режиме «График», где по оси абсцисс откладывается номер итерации.