Excel слау поиск решения

Обновлено: 05.07.2024

Цель:изучить технологию численного решения уравнений средствами MS EXCEL.

Теоретическое введение

Пример 4.1. Нахождение корней полиномов при помощи табулирования и сервисной функции Подбор параметра.

Известно, что если функция, определенная в интервале [a,b], имеет значения F(а) и F(b) с разными знаками, то в интервале [a,b] есть, по крайней мере, один корень. Построить алгоритм нахождения корней уравнений с заданной точностью EPS следующим образом.

1. Определить начальный интервал [A, B], где находятся корни.

модули всех корней x_k , k = 1…n расположены в круговом кольце

Таким образом, положительные корни лежат в интервале [A, B], а отрицательные корни - в интервале [-B, -A].

2. Табулируя полином в найденных начальных интервалах (например, с шагом (В-А)/10), составить таблицу .

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

4. (меню Сервис). Относительная погрешность вычислений и предельное число итераций задаются на вкладке Вычисления диалогового окна Параметры (Сервис з Параметры з Вычисления) (рис. 4.1).

5. Открыть диалоговое окно Подбор параметра(рис. 4.2). В поле Установить в ячейке ввести адрес ячейки, где вычисляется значение полинома. В поле Значение ввести 0 (т.е. искомое значение полинома). В поле Изменяя значение ячейки ввести адрес ячейки, отведенной для переменной х, где должно находиться начальное приближение к корню полинома.

Рис. 4.1. Диалоговое окно Параметры

Рис. 4.2. Диалоговое окно Подбор параметра

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

В появившемся диалоговом окне отметить Значения. После этого ячейка готова к использованию в поле Изменяя значение ячейки диалогового окна Подбор параметра.

6. После подбора параметра (ОК) х получит значение корня. Процесс повторяется для всех найденных начальных приближений в диапазоне, определяемом формулой (4.1).

Рис. 4.3. Диалоговое окно Специальная вставка

Пример 4.2.Нахождение корней нелинейных уравнений с помощью метода итераций.

Пусть дано уравнение f(x)=0. Для нахождения его корней методом итераций уравнение представляют в виде x=F(x) и записывают итерационную схему

с помощью которой строится итерационный процесс уточнения корней, начиная с начального значения x₀, которое выбирается самостоятельно. Достаточное условие сходимости процесса: в окрестности корня | F / (x)| <1.

Порядок действий в Excel может быть следующий.

1. Представить данное уравнение в виде x=F(x).

2. Создать таблицу с заголовками столбцов Номер шага, Очередное приближение к корню, Проверка на точность.

3. В первую ячейку первой строки таблицы занести значение 0, во вторую – начальное приближение.

4. В следующие строки занести, соответственно, номер очередного шага, итерационную формулу, вычисляющую правую часть итерационной схемы, и условную формулу, позволяющую помещать в ячейку текст «Стоп» или «Дальше» в зависимости от выполнения заданной точности решения (см. п. 5).

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

Если процесс расходится (получающиеся приближения удаляются друг от друга) или сходится очень медленно, то необходимо сменить вид представления x=F(x).

В этом может оказать помощь другой итерационный метод решения нелинейных уравнений – метод Ньютона. Его итерационная схема имеет вид

Сравнивая (4.2) и (4.3), замечаем, что в качестве функции F(x_k) можно взять правую часть из формулы (4.3). В большинстве случаев метод Ньютона сходится быстрее.

Пример 4.3. Нахождение корней нелинейных уравнений методом бисекции.

Если метод итераций сходится не всегда, то метод бисекции (или метод деления отрезка пополам, или метод дихотомии) – безусловно сходящийся метод нахождения корней нелинейного уравнения F(x)=0, лишь бы был известен отрезок, на котором расположен корень уравнения.

Пусть непрерывная функция F(x) меняет знак на концах отрезка [a,b], т.е. F(a)ЧF(b) < 0. Назовем такой отрезок отрезком локализации корня: на нем есть, по крайней мере, один корень. Найдем координату середины этого отрезка c=(a+b)/2 и рассмотрим два получившихся отрезка [a,c] и [c,b]. Если F(a)ЧF(с) < 0, то корень находится на отрезке [a,c], в противном случае – на отрезке [с,b]. Процесс деления все новых и новых отрезков локализации корня продолжаем до тех пор, пока длина отрезка не станет меньше заданной величины точности решения EPS.

Пример 4.4. Решение систем линейных алгебраических уравнений.

В Excel имеются специальные функции для работы с матрицами (Вставка функции пМатематические):

МОБР вычисление обратной матрицы А -1 ;

МОПРЕД вычисление определителя матрицы D;

МУМНОЖ нахождение произведения двух матриц.

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

или в матричном виде

где А = ij>– матрица коэффициентов при неизвестных;

В = ij> – вектор-столбец правых частей уравнений;

Х = ij> – вектор-столбец неизвестных.

Способ 1 (метод обратной матрицы).

Решение имеет вид Х = А –1 ЧВ, где А –1 – матрица, обратная по отношению к матрице А.

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

Примечание. При работе с матрицами перед вводом формулы необходимо выделить область на рабочем листе, куда будет выведен результат вычисления, а после задания исходных данных в поле функции выйти не как обычно, нажатием клавиши Enter или кнопки ОК, а нажатием клавиш Ctrl + Shift + Enter.

Способ 2 (правило Крамера). Если определитель D, составленный из коэффициентов при неизвестных, отличен от нуля, то решение имеет вид

Здесь D_j – дополнительный определитель, полученный из главного определителя системы D путем замены его j-го столбца вектором-столбцом В.

С помощью функции МОПРЕД находятся главный и дополнительные определители, и по формулам (4.5) вычисляются корни СЛАУ.

Способ 3 (метод исключений Гаусса). Этот метод основан на приведении матрицы системы к треугольному виду, что достигается последовательным исключением неизвестных из уравнений системы.

Предположим, что в (4.4) a₁₁ ¹ 0. Разделим первое уравнение системы на a₁₁ (этот коэффициент называется ведущим или главным элементом), получим:

Затем из каждого из остальных уравнений вычитается первое уравнение, умноженное на соответствующий коэффициент a_i1 (i=2,3,¼, n).

Эти n–1 уравнений принимают вид:

Далее аналогичную процедуру выполняют с этой системой, оставляя в покое первое уравнение. Только теперь делят на другой ведущий элемент a₂₂ (1) ¹0.

В результате исключения неизвестных приходим к СЛАУ с верхней треугольной матрицей с единицами на главной диагонали:

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

Прямой ход метода Гаусса завершен.

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

В этом списке x_n уже определено из последнего уравнения системы (4.6), а общая формула обратного хода имеет вид:

Проиллюстрируем этот алгоритм на примере решения системы из трех уравнений.

1.Располагаем на листе Excel матрицу коэффициентов и столбец правых частей (т.н. расширенная матрица 3´4), например, в ячейках А4:D6 (рис. 4.4).

2.Выделяем диапазон ячеек А8:D8 и вводим формулу:

Фигурные скобки появляются автоматически при вводе формулы комбинацией клавиш Shift+Ctrl+Enter, как признак того, что идет работа не с отдельными ячейками, а с массивами.

3.Выделяем диапазон ячеек А9:D9, вводим формулу:

и копируем эту формулу в диапазон ячеек А10:D10. В ячейках А9 и А10 появились нули.

4.В ячейки А12:D12 копируем значения первой строки расширенной матрицы А8:D8, в ячейки А13:D13 – формулу .

При этом второй элемент главной диагонали матрицы коэффициентов становится равным единице.

Рис.4.4 – Результаты решения СЛАУ

5)В ячейки А16:D17 копируем значения первых двух строк расширенной матрицы (А12:D13), а в ячейки А18:D18 – формулу: .

Прямой ход метода Гаусса завершен: получилась верхняя треугольная матрица с диагональными элементами, равными 1.

Задание 4.5. Решение систем нелинейных уравнений.

С помощью сервисной программы Поиск решения (Сервис | Поиск решения) в Excel можно решать системы нелинейных уравнений.

В общем случае система нелинейных уравнений имеет вид:

Составим новую функцию F(x₁, х₂, . х_n), представляющую собой сумму квадратов правых частей уравнений:

Очевидно, переменные x₁, х₂, . х_n , являющиеся решением системы (4.7), с необходимостью и достаточностью являются также решением уравнения:

На листе Excel отводим ячейки для неизвестных данной системы уравнений, например с А1 по А5 (если пять переменных), и вводим туда начальные приближения. В ячейку В2 вводим формулу, вычисляющую функцию (4.8).

Открываем диалоговое окно Поиск решения (рис. 4.5). В поле Установить целевую ячейку вводим В2, в группе Равной устанавливаем переключатель в положение Значению и в поле ввода задаем 0. В поле Изменяя ячейки вводим диапазон ячеек А1:А5.

Рис.4.5. Диалоговое окно Поиск решения при решении нелинейного уравнения

После нажатия на кнопку Выполнить будет найдено решение, которое поместится в ячейки А1:А5. В ячейке В2 будет вычислено значение левой части уравнения (4.9) с относительной погрешностью, задаваемой в диалоговом окне Параметры поиска решения.

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

Примечание 2. Система уравнений может иметь несколько корней, поэтому необходим ее анализ и с этой стороны. Задавая разные начальные приближения, можно получить разные решения системы.

4.2. задания для практической работы

Задание 4.1. Найти корни полиномов при помощи табулирования и сервисной функции Подбор параметра.

Данные для решения взять из таблицы 4.1. Корни найти с точностью EPS=0,00001.

Таблица 4.1 Полиномы

Вариант	Уравнение	Вариант	Уравнение
x 4 +6x 3 +11x 2 –2x–28=0	x 4 +3x 3 +8x 2 –5=0
x 4 +5x 3 +9x 2 +5x–1=0	x 4 –6x 3 +11x 2 +2x–28=0
x 4 +3x 3 +3x 2 –2=0	x 4 –5x 3 +9x 2 –5x–1=0
x 4 +x 3 –7x 2 +8x–6=0	x 4 –3x 3 +3x 2 –2=0
x 4 –10x 3 +16x+5=0	x 4 –x 3 –7x 2 –8x–6=0
x 4 –3x 3 –4x 2 –x–3=0	x 4 –10x 2 –16x+5=0
x 4 +4x 3 +4x 2 +4x–1=0	x 4 +3x 3 +4x 2 +x–3=0
x 4 +6x 3 +13x 2 +10x+1=0	x 4 –4x 3 –4x 2 –4x–1=0
x 4 +x 3 –4x 2 +16x–8=0	x 4 +2x 3 +3x 2 +2x–2=0
x 4 –x 3 –4x 2 –11x–3=0	x 4 –6x 3 +13x 2 –10x+1=0
x 4 –6x 3 –12x–8=0	x 4 –3x 2 +4x–3=0
x 4 +4x 3 +4x 2 –4=0	x 4 –6x 2 +12x–8=0
x 4 +x 3 +2x+1=0	x 4 –4x 3 +4x 2 –4=0
x 4 +2x 3 +x 2 +2x+1=0	x 4 –x 3 –2x+1=0
x 4 +3x 2 –4x–1=0	x 4 –2x 3 +x 2 –2x+1=0

Задание 4.2.Найти корни нелинейных уравнений с помощью метода итераций.

Данные для решения взять из таблицы 4.2. Точность решения EPS=0,0001.

После получения решения построить график, иллюстрирующий процесс сходимости: по оси абсцисс отложить номер шага, по оси ординат – очередное приближение к корню.

Задание 4.3. Найти корни нелинейных уравнений методом бисекции.

Таблица 4.2 Нелинейные уравнения

Вариант	Уравнение	Вариант	Уравнение
ln(x)+(x+1) 3 =0	x–sin(x)=0,25
x2 x =1	tg(0,58x+0,1)=x 2
x–cos(x)=0	3x–cos(x)–1=0
3x+cos(x)+1=0
x+ln(x)=0,5	x+lg(x)=0,5
2–x=ln(x)	x 2 +4sin(x)=0
ctg(1,05x)–x 2 =0
(2–x)exp(x)=0,5	xlg(x)–1,2=0
2,2x–2 x =0
x 2 +4sin(x)=0	2x–lg(x)–7=0
2x–lg(x)=7
5x–8ln(x)=8
sin(x-0,5)–x+0,8=0	cos(x+0,3)=x 2
x 2 cos(2x)=–1

При использовании Excel достаточно внести в некоторые ячейки, лежащие в одной строке, формулы, осуществляющие:

- вычисление значений левой и правой границы отрезков локализации;

- нахождение середины отрезка;

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

- проверку на точность решения (аналогично предыдущему заданию).

Затем формулы копируются вниз по столбцам до тех пор, пока не будет найден корень с заданной степенью точности EPS=0,0001.

Данные для решения взять из таблицы 4.2, то есть решить одно и то же уравнение двумя способами.

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

Проверить найденное решение умножением матрицы коэффициентов на вектор-столбец решения.

Таблица 4.3 Системы линейных алгебраических уравнений

Задание 5. Решить систему нелинейных уравнений, взяв данные из таблицы 1.4. Проверить найденное решение.

Таблица 1.4. Системы нелинейных уравнений

Вариант

Система нелинейных уравнений

Вариант

Система нелинейных уравнений

4.3.Контрольные вопросы

1. Опишите технологию нахождения корней полиномов при помощи табулирования и сервисной функции Подбор параметра.

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

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

4. Укажите итерационную схему метода Ньютона.

5. Укажите специальные функции Excel для работы с матрицами.

6. Опишите технологию решения систем линейных алгебраических уравнений.

7. С помощью, какой сервисной программы Excel осуществляется решение систем нелинейных уравнений?

Рассмотрим использование метода "Поиск решения. " на исходных данных представленных на рис. 1.

Для использования метода "Поиск решения. " необходимо свести задачу решения СЛАУ к задаче оптимизации. Введем целевую функцию вида

где b_i - i-й элемент вектора свободных членов СЛАУ;

a_i,j - i, j-й элемент матрицы коэффициентов СЛАУ;

x_j - j-й элемент вектора решения СЛАУ;

n - количество уравнений в СЛАУ.

Ограничений на вектор решения X накладывать не будем.

Тогда математически задачу поиска вектора решения СЛАУ X можно записать

Подобная задача (5) легко решается использованием метода "Поиск решения. " MS Excel (см. рис. 3.2) следующим образом:

Ш обнуляем ячейки (B24:B27), в которых будем формировать вектор решения СЛАУ X;

для ячейки B29 в строке формул запишем

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

Рис. 3.2. Решение СЛАУ, используя метод "Поиск решения. " (пункт главного меню "Сервис") MS Excel

Ш в пункте главного меню MS Excel "Сервис" выбираем подпункт "Поиск решения. " (см. рис. 3.3).

При открытии окна "Поиск решения" напротив метки "Установить целевую ячейку:" будет отражен адрес активной ячейки (ячейки, в которой был установлен курсор при открытии окна). В ячейке $B$29 (B29) должна быть записана формула вычисления правой части целевой функции (4). Также в окне "Поиск решения" ниже метки "Изменяя ячейки:" необходимо задать адрес вектора решения СЛАУ X ($B$24:$B$27) (B24:B27). Адреса целевой ячейки и вектора решения СЛАУ можно формировать в режиме конструктора. Для этого необходимо поместить курсор в ячейку формирования соответствующего адреса и на листе MS Excel выделить ячейку или массив ячеек;

Ш нажать кнопку "Выполнить". После чего появится окно "Результаты поиска решения" и в ячейках (B24:B27) сформируется вектор решения СЛАУ X.

Рис. 3.3 Окно “Поиск решения…”

Лист MS Excel, представленный на рис. 3.2 позволяет получить вектор решения для любой СЛАУ, состоящей из трех уравнений. Описанная технология решения СЛАУ легко позволяет решить задачу любой размерности (для любого количества уравнений в СЛАУ).

Решение СЛАУ методом Крамера

СЛАУ из n уравнений задается матрицей коэффициентов СЛАУ A и вектором свободных членов СЛАУ B.

где a_i,j - i, j-й элемент матрицы коэффициентов СЛАУ;

b_i - i-й элемент вектора свободных членов СЛАУ.

Суть метода Крамера в следующем: сначала вычисляется определитель матрицы коэффициентов СЛАУ

за тем вычисляются еще n определителей

т.е. определитель вычисляется для матрицы, полученной из матрицы коэффициентов СЛАУ путем замены j-го столбца матрицы коэффициентов СЛАУ вектором свободных членов СЛАУ.

Тогда элементы вектора решения СЛАУ x_j, j = 1, …, n определяются по формуле

В MS Excel существует формула =МОПРЕД(левый_верхний_элемент_исходной_матрицы: правый_нижний_элемент_исходной_матрицы) для вычисления значений определителей квадратных матриц.

Решение СЛАУ методом Крамера (методом определителей) представлено на рис. 3.4.

Рис. 3.4. Решение СЛАУ методом Крамера

Строки с 1 по 22 на рис. 3.4 не показаны, потому что они полностью совпадают с соответствующими строками рис. 3.1, 3.2.

Необходимо сформировать матрицы для вычисления определителей , ₁, ₂, ₃ в ячейках (B24:E27), (B29:E32), (B34:E37), (B39:E42), (B44:E47), соответственно. Алгоритм формирования матриц для вычисления определителей представлен в табл. 2.

Одной из самых интересных функций в программе Microsoft Excel является Поиск решения. Вместе с тем, следует отметить, что данный инструмент нельзя отнести к самым популярным среди пользователей в данном приложении. А зря. Ведь эта функция, используя исходные данные, путем перебора, находит наиболее оптимальное решение из всех имеющихся. Давайте выясним, как использовать функцию Поиск решения в программе Microsoft Excel.

Включение функции

Можно долго искать на ленте, где находится Поиск решения, но так и не найти данный инструмент. Просто, для активации данной функции, нужно её включить в настройках программы.

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

В окне параметров кликаем по пункту «Надстройки». После перехода, в нижней части окна, напротив параметра «Управление» выбираем значение «Надстройки Excel», и кликаем по кнопке «Перейти».

Открывается окно с надстройками. Ставим галочку напротив наименования нужной нам надстройки – «Поиск решения». Жмем на кнопку «OK».

После этого, кнопка для запуска функции Поиска решений появится на ленте Excel во вкладке «Данные».

Подготовка таблицы

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

Коэффициент, который применяется для расчета суммы премии, нам предстоит вычислить с помощью функции Поиска решений. Ячейка, в которой он располагается, называется искомой.

Запуск инструмента Поиск решения

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

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

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

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

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

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

После этого, открывается окно добавления ограничения. В поле «Ссылка на ячейки» прописываем адрес ячеек, относительно которых вводится ограничение. В нашем случае, это искомая ячейка с коэффициентом. Далее проставляем нужный знак: «меньше или равно», «больше или равно», «равно», «целое число», «бинарное», и т.д. В нашем случае, мы выберем знак «больше или равно», чтобы сделать коэффициент положительным числом. Соответственно, в поле «Ограничение» указываем число 0. Если мы хотим настроить ещё одно ограничение, то жмем на кнопку «Добавить». В обратном случае, жмем на кнопку «OK», чтобы сохранить введенные ограничения.

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

Дополнительные настройки можно задать, кликнув по кнопке «Параметры».

Здесь можно установить точность ограничения и пределы решения. Когда нужные данные введены, жмите на кнопку «OK». Но, для нашего случая, изменять эти параметры не нужно.

После того, как все настройки установлены, жмем на кнопку «Найти решение».

Далее, программа Эксель в ячейках выполняет необходимые расчеты. Одновременно с выдачей результатов, открывается окно, в котором вы можете либо сохранить найденное решение, либо восстановить исходные значения, переставив переключатель в соответствующую позицию. Независимо от выбранного варианта, установив галочку «Вернутся в диалоговое окно параметров», вы можете опять перейти к настройкам поиска решения. После того, как выставлены галочки и переключатели, жмем на кнопку «OK».

Если по какой-либо причине результаты поиска решений вас не удовлетворяют, или при их подсчете программа выдаёт ошибку, то, в таком случае, возвращаемся, описанным выше способом, в диалоговое окно параметров. Пересматриваем все введенные данные, так как возможно где-то была допущена ошибка. В случае, если ошибка найдена не была, то переходим к параметру «Выберите метод решения». Тут предоставляется возможность выбора одного из трех способов расчета: «Поиск решения нелинейных задач методом ОПГ», «Поиск решения линейных задач симплекс-методом», и «Эволюционный поиск решения». По умолчанию, используется первый метод. Пробуем решить поставленную задачу, выбрав любой другой метод. В случае неудачи, повторяем попытку, с использованием последнего метода. Алгоритм действий всё тот же, который мы описывали выше.

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

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Систему линейных алгебраических уравнений можно также решить, используя надстройку «Поиск решения». При использовании данной надстройки строится последовательность приближений , i=0,1,…n.

Назовем вектором невязок следующий вектор:

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

В качестве примера рассмотрим СЛАУ (3.27).

Последовательность действий:

1. Оформим таблицу, как показано на рис.3.4. Введем коэффициенты системы (матрицу А) в ячейки А3:С5.

Рис.3.4. Решение СЛАУ с помощью надстройки «Поиск решения»

2. В ячейках А8:С8 будет сформировано решение системы (х₁, х₂, х₃). Первоначально они остаются пустыми, т.е. равными нулю. В дальнейшем будем их называть изменяемыми ячейками.. Однако для контроля правильности вводимых далее формул, удобно ввести в эти ячейки какие-либо значения, например, единицы. Эти значения можно рассматривать как нулевое приближение решения системы, = (1, 1, 1).

3. В столбец D введем выражения для вычисления левых частей исходной системы. Для этого в ячейкуD3 введем и затем скопируем вниз до конца таблицы формулу:

D3=СУММПРОИЗВ (A3:C3;$A$8:$C$8).

Используемая функция СУММПРОИЗВ принадлежит категории Математические.

4. В столбец Е запишем значения правых частей системы (матрицу В).

5. В столбец F введем невязки в соответствии с формулой (3.29), т.е. введем формулу F3=D3-E3 и скопируем ее вниз до конца таблицы.

6. Будет не лишним проверить правильность вычислений для случая = (1, 1, 1).

7. Выберем команду Данные\Анализ\Поиск решения.

Рис. 3.5. Окно надстройки «Поиск решения»

В окне Поиск решения (рис.3.5) в поле Изменяемые ячейки укажем блок $А$8:$С$8, а в поле Ограничения – $F$3:$F$5=0. Далее щелкнем по кнопке Добавить и введем эти ограничения. И затем - кнопка Выполнить

Полученное решение систем (3.28) х₁=1; х₂=–1 х₃=2 записано в ячейках А8:С8, рис.3.4.

Реализация метода Якоби средствами приложения MS Excel

В качестве примера рассмотрим систему уравнений (3.19), решение которой методом Якоби получено выше (пример 3.2)

Приведем эту систему к нормальному виду:

Последовательность действий

1. Оформим таблицу, как показано на рис.3.6.:

• Матрицы и (3.15)введем в ячейки В6:Е8.

• Значение e–в Н5.

• Номер итерации k сформируем в столбце А таблицы с помощью автозаполнения.

• В качестве нулевого приближения выберем вектор

= (0, 0, 0) и введем его в ячейки В11:D11.

2. Используя выражения (3.29), в ячейки В12:D12 запишем формулы для вычисления первого приближения:

Эти формулы можно записать иначе, используя функцию Excel СУММПРОИЗВ.

В ячейку Е12 введем формулу: E12=ABS(B11-B12) и скопируем ее вправо, в ячейки F12:G12.

Рис.3.6. Схема решения СЛАУ методом Якоби

3. В ячейку Н12 введем формулу для вычисления M (k) , используя выражение (3.18): Н12 = МАКС(E12:G12). Функция МАКС находится в категории статистические.

4. Выделим ячейки В12:Н12 и скопируем их вниз до конца таблицы. Таким образом, получим k приближений решения СЛАУ.

5. Определим приближенное решение системы и количество итераций, необходимое для достижения заданной точности e.

Для этого оценим степень близости двух соседних итераций по формуле (3.18). Воспользуемся Условным форматированиемв ячейках столбца.

Результат такого форматирования виден на рис.3.6. Ячейки столбца Н, значения которых удовлетворяют условию (3.18), т.е. меньше e=0,1, тонированы.

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

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

Приведенные графики (рис.3.7) подтверждают сходимость итерационного процесса.

Рис. 3.7. Иллюстрация сходящегося итерационного процесса

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

Реализация метода прогонки средствами приложения Excel

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

Векторы:

Последовательность действий

1. Оформим таблицу, как показано на рис.3.8. Исходные данные расширенной матрицы системы (3.30), т.е. вектора введем в ячейки B5:E10.

2. Про гоночные коэффициенты U₀=0 и V₀=0 введем в ячейки G4 и H4 соответственно.

3. Вычислим прогоночные коэффициенты L_i, U_i, V_i. Для этого в ячейках F5, G5, H5 вычислим L₁, U₁, V₁. по формуле (3.8). Для этого введем формулы:

F5 = B5*G4+C5; G5=-D5/F5, H5 = (E5-B5*H4)/F5, и затем скопируем их вниз.

Рис.3.8. Расчетная схема метода «прогонки»

4. В ячейке I10 вычислим x₆ по формуле (3.10)

5. По формуле (3.7) вычислим все остальные неизвестные x₅ x₄, x₃, x₂, x₁. Для этого в ячейке I9 вычислим x₅ по формуле (3.6): I9=G9*I10+H9 . А далее копируем эту формуле вверх.

Контрольные вопросы

1. Система линейных алгебраических уравнений (СЛАУ). Что является решением СЛАУ. Когда существует единственное решение СЛАУ.

2. Общая характеристика прямых (точных) методов решения СЛАУ. Методы Гаусса и прогонки.

3. Общая характеристика итерационных методов решения СЛАУ. Методы Якоби (простых итераций) и Гаусса-Зейделя.

4. Условия сходимости итерационных процессов.

5. Что понимают под терминами обусловленности задач и вычислений, корректности задачи решения СЛАУ.

Глава 4.

Численное интегрирование

При решении достаточно большого круга технических задач приходится сталкиваться с необходимостью вычисления определенного интеграла:

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

Если непрерывная на отрезке [a, b] функция y = f(x) имеет на этом отрезке первообразную F(x), т.е. F ’ (x) = f(x) , то интеграл (4.1) может быть вычислен по формуле Ньютона – Лейбница:

Однако, только для узкого класса функций y=f(x) первообразная F(x) может быть выражена в элементарных функциях. Кроме того, функция y=f(x) может задаваться графически или таблично. В этих случаях применяют различные формулы для приближенного вычисления интегралов.

Такие формулы называют квадратурными формулами или формулами численного интегрирования.

Формулы численного интегрирования хорошо иллюстрируются графически. Известно [1, 12], что значение определенного интеграла (4.1) пропорционально площади криволинейной трапеции, образованной подынтегральной функцией y=f(x), прямыми х=а и х=b, осью ОХ (рис.4.1).

Задачу вычисления определенного интеграла (4.1) заменяем задачей вычисления площади этой криволинейной трапеции. Однако задача нахождения площади криволинейной не является простой.

Отсюда идея численного интегрирования [3, 6] будет заключатся в замене криволинейной трапеции фигурой, площадь которой вычисляется достаточно просто.

y=f(x)

xi+1

xn=b

xо=a

Рис.4.1. Геометрическая интерпретация численного интегрирования

Для этого отрезок интегрирования [a, b] разобьем на n равных элементарных отрезков [x_i ,x_i₊₁] (i=0, 1, 2, …. n-1), с шагом h=(b-a)/n. При этом криволинейная трапеция разобьется на n элементарных криволинейных трапеций с основаниями равными h (рис.4.1).

Каждая элементарная криволинейная трапеция заменяется фигурой, площадь которой вычисляется довольно просто. Обозначим эту площадь S_i . Сумма всех этих площадей называется интегральной суммой и вычисляется по формуле

Тогда приближенная формула вычисления определенного интеграла (4.1) имеет вид

Точность вычисления по формуле (4.4) зависит от шага h, т.е. от числа разбиений n. С увеличением n интегральная сумма приближается к точному значению интеграла

Это хорошо проиллюстрировано на рис.4.2.

б_n

Точное значение интеграла

Рис.4.2. Зависимость точности вычисления интеграла

от числа разбиений

В математике доказывается теорема: если функция y=f(x) непрерывна на [a, b], то предел интегральной суммы б_n существует и не зависит от способа разбиения отрезка [a,b] на элементарные отрезки.

Формулу (4.4) можно использовать, если известна степень точности такого приближения. Существуют различные формулы для оценки погрешности выражения (4.4), но, как правило, они достаточно сложны. Будем проводить оценку точности приближения (4.4) методом половинного шага.

Читайте также: