Метод гаусса в excel

Обновлено: 04.07.2024

Рассмотрим достаточные условия сходимости итерационной последовательности n>.
Практически, для применения метода итерации систему линейных уравнений удобно "погрузить" в одну из трёх следующих метрик:
(3.4)
Для того, чтобы отображение F, заданное в метрическом пространстве соотношениями (3.2), было сжимающим, достаточно выполнение одного из следующих условий:
а) в пространстве с метрикой ρ₁: , т. е. максимальная из сумм модулей коэффициентов в правой части системы (3.2), взятых по строкам, должна быть меньше единицы.
б) в пространстве с метрикой ρ₂: , т. е. максимальная из сумм модулей коэффициентов в правой части системы (3.2), взятых по столбцам, должна быть меньше единицы.
в) в пространстве с метрикой ρ₃: , т. е. сумма квадратов при неизвестных в правой части системы (3.2) должна быть меньше единицы

Пример . Вычислить два приближения методом простой итерации. Оценить погрешность второго приближения. В качестве начального приближения выбрать x 0 =(0; 0; 0).

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

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

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

Метод итераций для системы уравнений в Excel

На листе Excel организуется таблица в три зоны: первая зона состоит из одного столбца (номер итерации), вторая зона определяет переменные x , третья зона под вычисления точности epsilon .
Во второй зоне по итерационной схеме организуется расчет переменных x (в примере для случая трех переменных):
Итерация №1: =$F$2/$B$2-C6*$C$2/$B$2-D6*$D$2/$B$2;=$F$3/$C$3-B6*$B$3/$C$3-D6*$D$3/$C$3;=$F$4/$D$4-B6*$B$4/$D$4-C6*$C$4/$D$4
Итерация №2: =$F$2/$B$2-C7*$C$2/$B$2-D7*$D$2/$B$2;=$F$3/$C$3-B7*$B$3/$C$3-D7*$D$3/$C$3;=$F$4/$D$4-B7*$B$4/$D$4-C7*$C$4/$D$4

Функция ГАУСС, подлежащая применению в версиях Excel начиная от 2013 года или новее. Она позволяет вычислить такую вероятность, с которой элемент стандартной нормальной совокупности будет находиться в интервале между средними и стандартными отклонениями от среднего.

Примеры использования функции ГАУСС в Excel

Синтаксис рассматриваемой функции не представляет из себя ничего сложного, ведь функции ГАУСС присущ всего один обязательный аргумент – Z – возвращающий число.

Важно отметить, что существует определенная связь между функцией ГАУСС и такой статистической функцией, как стандартное нормальное распределение, иначе говоря – НОРМ.СТ.РАСП.

Итак, всегда функция НОРМ.СТ.РАСП (0; Истина) делает возврат 0,5, тогда как ГАУСС (z) имеет в результате значение меньше на 0,5, чем результат функции НОРМ.СТ.РАСП. На рисунке, расположенном ниже, приведен пример использования данных статистических функций для возвращения числа 1,5.

Для наглядности продемонстрируем зависимость между значениями функций графическим способом. Для этого – сформируем таблицу с выборкой чисел, например на интервале от -5 до 5 с шагом 0,5, а затем по имеющимся данным построим график:

На графике четко прослеживается пропорциональная корреляция результатов вычислений функций ГАУСС и НОРМ.СТ.РАСП.

Решение системы вероятности методом ГАУССА в Excel

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

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

Существует 36 возможных комбинаций. При этом, вероятность того, что при бросании двух костей выпадет 2 очка равна 1/36, а 7 очков – 1/6. Отобразим перечень возможных значений бросания двух игральных костей в таблице, приведя при этом все вероятности к общему знаменателю.

Однако, такой ряд данных не дает возможности для выявления полного распределения, поэтому следует отобразить данные об отдельных вероятностях в рассчитанную по функции распределения. Так необходимо, все вероятности просуммировать последовательно (1+2+3+4+5+6+5+4+3+2+1).

Теперь определяем коэффициент вероятности разделив по отдельности последовательную сумму вероятностей на максимально возможное количество комбинаций 36.

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

Необходимо преобразовать диапазон ячеек D2:D13 в числовой формат данных, иначе при обращении на них функции ГАУСС будет иметь место ошибка.

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

Далее, протянем формулу вниз по столбцу, и получим ряд вероятностей с использованием функции ГАУСС.

Для более наглядной визуализации, построим график вероятности:

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

Теперь в качестве примера нормального распределения с помощью функции ГАУСС решим задачу о вероятностном соотношении результатов стрельбы по мишени.

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

Затем, выберем только уникальные результаты, для этого используем хитрую формулу:

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

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

Далее применим функцию ГАУСС к значениям ячеек с частотой встречаемости. Отразим результаты вычислений на графике:

На графике красной линией определено нормальное распределение кривой Гаусса.

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

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

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

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

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

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

Введем в ячейку В2 формулу для нахождения значения функции. В качестве аргумента применим ссылку на ячейку В1.
Открываем меню инструмента «Подбор параметра». В графе «Установить в ячейку» - ссылка на ячейку В2, где находится формула. В поле «Значение» вводим 0. Это то значение, которое нужно получить. В графе «Изменяя значение ячейки» - В1. Здесь должен отобразиться отобранный параметр.
После нажатия ОК отобразится результат подбора. Если нужно его сохранить, вновь нажимаем ОК. В противном случае – «Отмена».

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

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

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

Значения элементов введем в ячейки Excel в виде таблицы.
Найдем обратную матрицу. Выделим диапазон, куда впоследствии будут помещены элементы матрицы (ориентируемся на количество строк и столбцов в исходной матрице). Открываем список функций (fx). В категории «Математические» находим МОБР. Аргумент – массив ячеек с элементами исходной матрицы.
Нажимаем ОК – в левом верхнем углу диапазона появляется значение. Последовательно жмем кнопку F2 и сочетание клавиш Ctrl + Shift + Enter.
Умножим обратную матрицу Ах -1х на матрицу В (именно в таком порядке следования множителей!). Выделяем диапазон, где впоследствии появятся элементы результирующей матрицы (ориентируемся на число строк и столбцов матрицы В). Открываем диалоговое окно математической функции МУМНОЖ. Первый диапазон – обратная матрица. Второй – матрица В.
Закрываем окно с аргументами функции нажатием кнопки ОК. Последовательно нажимаем кнопку F2 и комбинацию Ctrl + Shift + Enter.

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

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

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

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

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

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

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

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

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

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

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

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

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

Приведем все коэффициенты при а к 0. Кроме первого уравнения. Скопируем значения в первой строке двух матриц в ячейки В6:Е6. В ячейку В7 введем формулу: =B3:Е3-$B$2:$Е$2*(B3/$B$2). Выделим диапазон В7:Е7. Нажмем F2 и сочетание клавиш Ctrl + Shift + Enter. Мы отняли от второй строки первую, умноженную на отношение первых элементов второго и первого уравнения.
Копируем введенную формулу на 8 и 9 строки. Так мы избавились от коэффициентов перед а. Сохранили только первое уравнение.
Приведем к 0 коэффициенты перед в в третьем и четвертом уравнении. Копируем строки 6 и 7 (только значения). Переносим их ниже, в строки 10 и 11. Эти данные должны остаться неизменными. В ячейку В12 вводим формулу массива.
Прямую прогонку по методу Гаусса сделали. В обратном порядке начнем прогонять с последней строки полученной матрицы. Все элементы данной строки нужно разделить на коэффициент при с. Введем в строку формулу массива: .
В строке 15: отнимем от второй строки третью, умноженную на коэффициент при с второй строки (). В строке 14: от первой строки отнимаем вторую и третью, умноженные на соответствующие коэффициенты (). В последнем столбце новой матрицы получаем корни уравнения.

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

Программированию нельзя научить, можно только научится

Главная » Уроки по Численным методам » Урок 15. Решение СЛУ методом Крамера и методом Гаусса.

Метод Крамера

(СЛУ)
- определитель системы
Если определитель СЛУ отличен от нуля, тогда решение системы определяется однозначно по формулам Крамера:
, , ()
где:

	Для этого в столбец, где стоит переменная х, а значит в первый столбец, вместо коэффициентов при х, ставим свободные коэффициенты, которые в системе уравнений стоят в правых частях уравнений
	Для этого в столбец, где стоит переменная y (2 столбец), вместо коэффициентов при y, ставим свободные коэффициенты, которые в системе уравнений стоят в правых частях уравнений
	Для этого в столбец, где стоит переменная z, а значит втретий столбец, вместо коэффициентов при z, ставим свободные коэффициенты, которые в системе уравнений стоят в правых частях уравнений

Задание 1. Решить СЛУ с помощью формул Крамера в Excel

Ход решения

1. Запишем уравнение в матричном виде:

2. Введите матрицу А и В в Excel.

3. Найдите определитель матрицы А. Он должен получится равным 30.

4. Определитель системы отличен от нуля, следовательно - решение однозначно определяется по формулам Крамера.

5. Заполните значения dX, dY, dZ на листе Excel (см.рис.ниже).

6. Для вычисления значений dX, dY, dZ в ячейки F8, F12, F16 необходимо ввести функцию, вычисляющую определитель dX, dY, dZ соответственно.

7. Для вычисления значения X в ячейку I8 необходимо ввести формулу =F8/B5 (по формуле Крамера dX/|A|).

8. Самостоятельно введите формулы для вычисления Y и Z.

Задание 2: самостоятельно найти решение СЛУ методом Крамера:

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

Метод Гаусса

Процесс решения по методу Гаусса состоит из двух этапов.

1. Прямой ход: система приводится к ступенчатому (в частности, треугольному) виду.

Для того чтобы решить систему уравнений выписывают расширенную матрицу этой системы

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

2. Обратный ход: идет последовательное определение неизвестных из этой ступенчатой системы.

Пример. Установить совместность и решить систему

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

.

Имеем Ранги матрицы системы и ее расширенной матрицы совпали с числом неизвестных. Согласно теореме Кронекера-Капелли система уравнений совместна и решение ее единственно.
Обратный ход: Выпишем систему уравнений, расширенную матрицу которой мы получили в результате преобразований:

Итак, имеем .
Далее, подставляя в третье уравнение, найдем .
Подставляя и во второе уравнение, получим .
Подставляя в первое уравнение найденные получим .
Таким образом, имеем решение системы .

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

В тексте будет предлагаться ввести в диапазон ячеек формулу вида: и т.п., это так-называемые «формулы массива». 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 во всех уравнениях кроме первого.

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 введем формулу .

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

23: – отнимаем от третьей строки четвертую умноженную на коэффициент при x4 третьей строки.

22: – от второй строки отнимаем третью и четвертую, умноженные на соответствующие коэффициенты.

21: – от первой строки отнимаем вторую, третью и четвертую, умноженные на соответствующие коэффициенты.

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

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

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

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

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

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

6. Прямая прогонка методом Гаусса завершена. Обратную прогонку начнем с последней строки полученной матрицы. Необходимо все элементы последней строки разделить на коэффициент при x₄. Для этого в строку 24 введем формулу . (скриншот)

UPDATE от 25 апреля 2012 г. Выкладываю xls-файл с решением линейных уравнений методом Гаусса в Microsoft Excel: Показать ссылку

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