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

Примеры решения уравнений методом итераций в 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. Получим тот же результат:

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

Напомню, что циклическая ссылка появляется, если в ячейку 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» – начальное значение для рекуррентной формулы. Изменяя его, можно выйти на все корни уравнения.

Нахождение определителя матрицы

Это одно единственное число, которое находится для квадратной матрицы. Используемая функция – МОПРЕД.

Ставим курсор в любой ячейке открытого листа. Вводим формулу: =МОПРЕД(A1:D4).

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

Вычислить значения корней сформированной системы уравнений двумя методами: обратной матрицы и методом Крамера.

Введем данные значения в ячейки А2:С4 – матрица А и ячейки D2:D4 – матрица В.

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

Решим систему методом Крамера, для этого найдем определитель матрицы. Найдем определители матриц, полученных заменой одного столбца на столбец b.

Найдем корни уравнения, для этого в ячейку В21 введем: =B16/$B$15, в ячейку В22 введем: = =B17/$B$15, в ячейку В23 введем: ==B18/$B$15.

m×n,n×m

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

Применим метод Крамера для системы из 2 уравнений и двумя искомыми величинами:

$\begin{cases} a_1x_1 + a_2x_2 = b_1 \\ a_3x_1 + a_4x_2 = b_2 \\ \end{cases}$

Отобразим её в расширенной форме для удобства:

$A = \begin{array}{cc|c} a_1 & a_2 & b_1 \\ a_3 & a_4 & b_1 \\ \end{array}$

$D = \begin{array}{|cc|} a_1 & a_2 \\ a_3 & a_4 \\ \end{array} = a_1 \cdot a_4 – a_3 \cdot a_2$

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

$D_1 = \begin{array}{|cc|} b_1 & a_2 \\ b_2 & a_4 \\ \end{array} = b_1 \cdot a_4 – b_2 \cdot a_4$

$D_2 = \begin{array}{|cc|} a_1 & b_1 \\ a_3 & b_2 \\ \end{array} = a_1 \cdot b_2 – a_3 \cdot b_1$

Теперь найдём неизвестные $x_1$ и $x_2$:

$x_1 = \frac {D_1}{D}$

$x_2 = \frac {D_2}{D}$

Пример 1

Метод Крамера для решения СЛАУ с основной матрицей 3 порядка (3 x 3) и тремя искомыми.

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

$\begin{cases} 3x_1 – 2x_2 + 4x_3 = 21 \\ 3x_1 +4x_2 + 2x_3 = 9\\ 2x_1 – x_2 — x_3 = 10 \\ \end{cases}$

Сосчитаем главный детерминант матрицы пользуясь вышеизложенным под пунктом номер 1 правилом:

$D = \begin{array}{|ccc|} 3 & -2 & 4 \\3 & 4 & -2 \\ 2 & -1 & 1 \\ \end{array} = 3 \cdot 4 \cdot (-1) + 2 \cdot (-2) \cdot 2 + 4 \cdot 3 \cdot (-1) – 4 \cdot 4 \cdot 2 – 3 \cdot (-2) \cdot (-1) — (-1) \cdot 2 \cdot 3 = — 12 – 8 -12 -32 – 6 + 6 = — 64$

А теперь три других детерминанта:

$D_1 = \begin{array}{|ccc|} 21 & 2 & 4 \\ 9 & 4 & 2 \\ 10 & 1 & 1 \\ \end{array} = 21 \cdot 4 \cdot 1 + (-2) \cdot 2 \cdot 10 + 9 \cdot (-1) \cdot 4 – 4 \cdot 4 \cdot 10 – 9 \cdot (-2) \cdot (-1) — (-1) \cdot 2 \cdot 21 = — 84 – 40 – 36 – 160 – 18 + 42 = — 296$

$D_2 = \begin{array}{|ccc|} 3 & 21 & 4 \\3 & 9 & 2 \\ 2 & 10 & 1 \\ \end{array} = 3 \cdot 9 \cdot (- 1) + 3 \cdot 10 \cdot 4 + 21 \cdot 2 \cdot 2 – 4 \cdot 9 \cdot 2 – 21 \cdot 3 \cdot (-1) – 2 \cdot 10 \cdot 3 = — 27 + 120 + 84 – 72 + 63 – 60 = 108$

$D_3 = \begin{array}{|ccc|} 3 & -2 & 21 \\ 3 & 4 & 9 \\ 2 & 1 & 10 \\ \end{array} = 3 \cdot 4 \cdot 10 + 3 \cdot (-1) \cdot 21 + (-2) \cdot 9 \cdot 2 – 21 \cdot 4 \cdot 2 — (-2) \cdot 3 \cdot 10 — (-1) \cdot 9 \cdot 3 = 120 – 63 – 36 – 168 + 60 + 27 = — 60$

Найдём искомые величины:

$x_1 = \frac{D_1} {D} = \frac{- 296}{-64} = 4 \frac{5}{8}$

$x_2 = \frac{D_1} {D} = \frac{108} {-64} = — 1 \frac {11} {16}$

$x_3 = \frac{D_1} {D} = \frac{-60} {-64} = \frac {15} {16}$

2.7. Регрессия

Для построения используются
несколько стандартных функций листа.

TREND / ТЕНДЕНЦИЯ

Строит

y=b+m₁x₁+…+m_J x_J+e

Аппроксимирует известные значения вектора откликов
known_y’s для заданных значений матрицы предикторов
known_x’s и возвращает значения y,
для заданного массива new_x’s.  

Синтаксис 

TREND(known_y’s
)

Примечания 

• Вектор
  known_y’s должен занимать один столбец,
  тогда каждый столбец матрицы массива known_x’s
  интерпретируется как отдельная переменная;

• Если
  аргумент known_x’sопущен, то предполагается, что это вектор чисел {1;2;3;…}
  такого же размера, как и known_y’s;

• Матрица
  новых значений new_x’sдолжна иметь столько же столбцов
  (переменных), как и матрица known_x’s;

• Если
  аргумент new_x’sопущен, то предполагается, что он совпадает с
  массивом known_x’s.
  Результат является вектором, в котором число строк равно
  числу строк в массиве new_x’s.

Пример 
 

Рис.34 Функция TREND

Функция TRENDявляется функцией
массива и ее ввод должен завершаться нажатием комбинации
CTRL+SHIFT+ENTER. 

LINEST /
ЛИНЕЙН

Дополняет функцию TREND и выводит некоторые
статистические значения, связанные с регрессией  

y=b+m₁x₁+…+m_J x_J+e

Синтаксис 

LINEST(known_y’s
)

Рис. 35 Таблица вывода функция LINEST

m_J, …,
m₂, m₁
и b – оценки регрессионных
коэффициентов;

s_J, …,
s₂, s₁
и s_b
– стандартные ошибки для оценок регрессионных коэффициентов;

R2 –
коэффициент детерминации;

s_y –
стандартная ошибка оценки y;

F – F-статистика;

DoF – число степеней
свободы;

SS_reg –
регрессионная сумма квадратов;

SS_res–
остаточная сумма квадратов.

Примечания 

• LINEST – это
  очень плохо сконструированная функция, очень неудобная в
  практическом применении;

• Примечания,
  представленные в описании функции полностью применимы к
  функции LINEST.

Пример 
 

Рис.36 Функция LINEST

Функция LINEST является функцией массива и ее ввод должен
завершаться нажатием комбинации CTRL+SHIFT+ENTER. 

Как посчитать процент от суммы

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

Как посчитать проценты: от числа, от суммы чисел и др. — заметка для начинающих

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

Вся суть приведена на скрине ниже: если у вас есть общая сумма, допустим в моем примере это число 3060 — ячейка F8 (т.е. это 100% прибыль, и какую то ее часть сделал «Саша», нужно найти какую. ).

По пропорции формула будет выглядеть так: =F10*G8/F8 (т.е. крест на крест: сначала перемножаем два известных числа по диагонали, а затем делим на оставшееся третье число).

В принципе, используя это правило, запутаться в процентах практически невозможно .

Пример решения задач с процентами

PS

Собственно, на этом я завершаю данную статью. Не побоюсь сказать, что освоив все, что написано выше (а приведено здесь всего лишь «пяток» формул) — Вы дальше сможете самостоятельно обучаться Excel, листать справку, смотреть, экспериментировать, и анализировать.

Скажу даже больше, все что я описал выше, покроет многие задачи, и позволит решать всё самое распространенное, над которым часто ломаешь голову (если не знаешь возможности Excel) , и даже не догадывается как быстро это можно сделать.

Формулы массива

Построение матрицы средствами Excel в большинстве случаев требует использование формулы массива. Основное их отличие – результатом становится не одно значение, а массив данных (диапазон чисел).

Порядок применения формулы массива:

1. Выделить диапазон, где должен появиться результат действия формулы.
2. Ввести формулу (как и положено, со знака «=»).
3. Нажать сочетание кнопок Ctrl + Shift + Ввод.

В строке формул отобразится формула массива в фигурных скобках.

Чтобы изменить или удалить формулу массива, нужно выделить весь диапазон и выполнить соответствующие действия. Для введения изменений применяется та же комбинация (Ctrl + Shift + Enter). Часть массива изменить невозможно.

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

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

Делается это на вкладке «Формулы» в «Параметрах Excel». Найдем корень уравнения х – х 3 + 1 = 0 (а = 1, b = 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. Получим тот же результат:

Решение СЛУ методом Гаусса в Excel:

В тексте будет предлагаться ввести в диапазон ячеек формулу вида: {=A1:B3+$C$2:$C$3} и т.п., это так-называемые «формулы массива». Microsoft Excel автоматически заключает ее в фигурные скобки ({ }). Для введения такого типа формул необходимо выделить весь диапазон, куда нужно вставить формулу, в первой ячейке ввести формулу без фигурных скобок (для примера выше – =A1:B3+$C$2:$C$3) и нажать Ctrl+Shift+Enter.
Пускай имеем систему линейных уравнений:

1.

Запишем коэффициенты системы уравнений в ячейки A1:D4 а столбец свободных членов в ячейки E1:E4. Если в ячейке
A1
находится 0, необходимо поменять строки местами так, чтоб в этой ячейке было отличное от ноля значение
. Для большей наглядности можно добавить заливку ячеек, в которых находятся свободные члены.

2.
Необходимо коэффициент при x1 во всех уравнениях кроме первого привести к 0. Для начала сделаем это для второго уравнения. Скопируем первую строку в ячейки A6:E6 без изменений, в ячейки A7:E7 необходимо ввести формулу: {=A2:E2-$A$1:$E$1*(A2/$A$1)}. Таким образом мы от второй строки отнимаем первую, умноженную на A2/$A$1, т.е. отношение первых коэффициентов второго и первого уравнения. Для удобства заполнения строк 8 и 9 ссылки на ячейки первой строки необходимо использовать абсолютные (используем символ $).

3.
Копируем введенную формулу формулу в строки 8 и 9, таким образом избавляемся от коэффициентов перед x1 во всех уравнениях кроме первого.

4.
Теперь приведем коэффициенты перед x2 в третьем и четвертом уравнении к 0. Для этого скопируем полученные 6-ю и 7-ю строки (только значения) в строки 11 и 12, а в ячейки A13:E13 введем формулу {=A8:E8-$A$7:$E$7*(B8/$B$7)}, которую затем скопируем в ячейки A14:E14. Таким образом реализуется разность строк 8 и 7, умноженных на коэффициент B8/$B$7. .

5.
Осталось привести коэффициент при x3 в четвертом уравнении к 0, для этого вновь проделаем аналогичные действия: скопируем полученные 11, 12 и 13-ю строки (только значения) в строки 16-18, а в ячейки A19:E19 введем формулу {=A14:E14-$A$13:$E$13*(C14/$C$13)}. Таким образом реализуется разность строк 14 и 13, умноженных на коэффициент C14/$C$13. Не забываем проводить перестановку строк, чтоб избавиться от 0 в знаменателе дроби
.

6.
Прямая прогонка методом Гаусса завершена. Обратную прогонку начнем с последней строки полученной матрицы. Необходимо все элементы последней строки разделить на коэффициент при x4. Для этого в строку 24 введем формулу {=A19:E19/D19}.

7.
Приведем все строки к подобному виду, для этого заполним строки 23, 22, 21 следующими формулами:

23: {=(A18:E18-A24:E24*D18)/C18} – отнимаем от третьей строки четвертую умноженную на коэффициент при x4 третьей строки.

22: {=(A17:E17-A23:E23*C17-A24:E24*D17)/B17} – от второй строки отнимаем третью и четвертую, умноженные на соответствующие коэффициенты.

21: {=(A16:E16-A22:E22*B16-A23:E23*C16-A24:E24*D16)/A16} – от первой строки отнимаем вторую, третью и четвертую, умноженные на соответствующие коэффициенты.

Результат (корни уравнения) вычислены в ячейках E21:E24.

Составитель: Салий Н.А.

Метод Крамера применяется для решения систем линейных алгебраических уравнений (СЛАУ), в которых число неизвестных переменных равно числу уравнений и определитель основной матрицы отличен от нуля. В этой статье мы разберем как по методу Крамера находятся неизвестные переменные и получим формулы. После этого перейдем к примерам и подробно опишем решение систем линейных алгебраических уравнений методом Крамера.

Навигация по странице.

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

Этот метод позволяет достаточно легко находить решения систем линейных уравнений, в которых более двух уравнений и неизвестных. По сути, этот метод является обобщением метода подстановки. Итак, как можно решить систему линейных уравнений? Рассмотрим этот способ на примере системы трёх уравнений с тремя неизвестными. 

На первом этапе решения систему уравнений необходимо привести к трапециевидной форме, которая выглядит следующим образом:

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

Применим данный метод к системе линейных уравнений с тремя переменными:

Расширенная матрица A данной системы принимает вид:

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

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

С одной стороны, можно остановиться на данном этапе, поменять вторую и третью строку местами, решить систему уравнений, соответствующую полученной расширенной матрице: 

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

Это позволит перейти к решению ещё более простой системы линейных уравнений:

Теперь реализуем обратный ход метода Гаусса: из третьего уравнения системы определим z = 3 , из второго — y = 2. Далее используем метод подстановки и определим значение x:

Итак, решение системы линейных уравнений методом Гаусса: x = 1, y = 2, z = 3.

1.9. «Растаскивание» формул

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

Поясним эту технику на нашем примере. Начнем с ввода
формулы-образца. В этом случае –  это формула, помещенная в ячейку
J3  .

Рис.21
Маркер заполнения

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

Рис.22 Растаскивание серии однотипных формул

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

Рис.23
Копирование серии однотипных формул

Отметим, что независимо от способа, ссылки получаются
правильными – на соответствующий диапазон ячеек. Это произошло потому,
что в исходной формуле мы использовали относительную адресацию в
аргументе: B3:I3.

Подробности можно прочитать

здесь. 

· Аналитическим методом

·
Графическим методом

·
Численным методом

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

Нашел с заданной точностью корень уравнения
0,25-x+sin(x)=0 на промежутке методом простой итерации.

Приложение.

1.Ручной расчет.

2.Автоматический расчет.

3.Решение уравнения 0.25-x-sin(x)=0
графическим способом.

Библиографический список.

1.
Волков Е.А. «Числовые методы».

2.
Самарский А.А. «Введение в
числовые методы».

3.
Игалеткин И.И. «Числовые методы».

Пример 3.1.

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

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

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

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

, (3.2)

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

, (3.3)

Для определения количества итераций, необходимое для достижения заданной точности 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

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

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

Варианты решений

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

Способ 1: матричный метод

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

1. Заполняем матрицу числами, которые являются коэффициентами уравнения. Данные числа должны располагаться последовательно по порядку с учетом расположения каждого корня, которому они соответствуют. Если в каком-то выражении один из корней отсутствует, то в этом случае коэффициент считается равным нулю. Если коэффициент не обозначен в уравнении, но соответствующий корень имеется, то считается, что коэффициент равен 1. Обозначаем полученную таблицу, как вектор A.
2. Отдельно записываем значения после знака «равно». Обозначаем их общим наименованием, как вектор B.

Аргумент «Массив» — это, собственно, адрес исходной таблицы.

Способ 2: подбор параметров

Второй известный способ решения системы уравнений в Экселе – это применение метода подбора параметров. Суть данного метода заключается в поиске от обратного. То есть, основываясь на известном результате, мы производим поиск неизвестного аргумента. Давайте для примера используем квадратное уравнение

1. Принимаем значение x за равное . Высчитываем соответствующее для него значение f(x), применив следующую формулу:

Этот результат также можно проверить, подставив данное значение в решаемое выражение вместо значения x.

Способ 3: метод Крамера

Теперь попробуем решить систему уравнений методом Крамера. Для примера возьмем все ту же систему, которую использовали в Способе 1:

1. Как и в первом способе, составляем матрицу A из коэффициентов уравнений и таблицу B из значений, которые стоят после знака «равно».
2. Далее делаем ещё четыре таблицы. Каждая из них является копией матрицы A, только у этих копий поочередно один столбец заменен на таблицу B. У первой таблицы – это первый столбец, у второй таблицы – второй и т.д.
3. Теперь нам нужно высчитать определители для всех этих таблиц. Система уравнений будет иметь решения только в том случае, если все определители будут иметь значение, отличное от нуля. Для расчета этого значения в Экселе опять имеется отдельная функция – МОПРЕД. Синтаксис данного оператора следующий:

Таким образом, как и у функции МОБР, единственным аргументом выступает ссылка на обрабатываемую таблицу.

Способ 4: метод Гаусса

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

1. Опять последовательно записываем коэффициенты в таблицу A, а свободные члены, расположенные после знака «равно» — в таблицу B. Но на этот раз сблизим обе таблицы, так как это понадобится нам для работы в дальнейшем. Важным условием является то, чтобы в первой ячейке матрицы A значение было отличным от нуля. В обратном случае следует переставить строки местами.
2. Копируем первую строку двух соединенных матриц в строчку ниже (для наглядности можно пропустить одну строку). В первую ячейку, которая расположена в строке ещё ниже предыдущей, вводим следующую формулу:

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

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

Второй метод

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

1. Создаете два диапазона.

На заметку! Смена знака результата говорит о том, что решение находится в промежутке между этими двумя переменными.

2. Переходите во вкладку Вставка и выбираете обычный график.

3. Выбираете данные из столбца f (x), а в качестве подписи горизонтальной оси – значения иксов.

Важно! В настройках оси поставьте положение по делениям

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

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