Метод Гаусса в Excel

Обновлено: 06.07.2024

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

для получения одного уравнения с одним неизвестным, из которого y = 6. Теперь, используя значение y, вы можете вычислить x.

Чтобы распространить эту процедуру на систему n уравнений с n неизвестными, требуется систематическая работа. Процесс решения эквивалентен преобразованию приведенной выше матрицы nxn в треугольную матрицу, такую как верхняя треугольная матрица d]\X\ + d\2X2 + «13*3 + •■■ + Q\nXn = b\

Строка 2 нормализована:

Термины x2 исключаются из столбца 2 строк 3 и 4:

Строка 3 нормализована, и из столбца 3 строки 4 удалены термины x3:

Строка 4 нормализована:

Как видите, матрица коэффициентов теперь представляет собой верхнюю треугольную матрицу с диагональными элементами, равными единице. Результаты получаются последовательной подстановкой, начиная с последней строки. Последняя строка соответствует jc4 = 154, третья строка соответствует Xz — 0,272727x4 = 107, откуда x3 = 149, и так далее. Результаты х\, х2, х3 и х4 равны 106, 52, 49, 54 соответственно. Вы можете увидеть этапы вычисления исключения Гаусса, используя демонстрационную программу, представленную на компакт-диске (папка «Глава 09 Одновременные уравнения», рабочая тетрадь «Одновременные линейные уравнения», лист «Демонстрация исключения Гаусса»).

Метод исключения Гаусса также можно выполнить с помощью пользовательской функции VBA GaussElim. Код VBA показан на рис. 9-4.

Синтаксис функции: GaussE\\rr\(coeff_matrix,const_vector). Функция возвращает вектор результатов; поскольку функция является функцией массива, необходимо выбрать диапазон ячеек соответствующего размера и нажать CTRL+SHIFT+ENTER (Windows), COMMAND+RETURN или CTRL+SHIFT+RETURN (Macintosh).

Функция GaussElim(coeff_matrix, const_vector)

Затемнить AugMatrix() как Double, ResultVector() как Double Затемнить NormFactor как Double

Dim temp As Double, term As Double, ElimFactor As Double Dim I As Integer, J As Integer, K As Integer Dim C As Integer, R As Integer Dim N As Integer

ReDim AugMatrix(N, N + 1), ResultVector(N)

'Создать расширенную матрицу с размерами N x (N+1) For I = 1 To N For J = 1 To N

AugMatrix(l, J) = coeff_matrix(l, J) Next J, I For J = 1 To N

'Нормализуйте каждую строку, начиная со столбца K вправо. 'Если коэффициент нормализации равен нулю, поменять строки местами. J) AugMatrix(K + 1, J) = temp Next J

Коэффициент нормы = AugMatrix(K, K) End If

AugMatrix(K, C) = AugMatrix(K, C) / NormFactor Next C

'Исключить For R = K + 1 To N ElimFactor = AugMatrix(R, K) For C = K To N + 1

AugMatrix(R, C) = AugMatrix(R, C) - AugMatrix(K, C) * ElimFactor Next C Next R

'Рассчитать и вернуть коэффициенты.

'Выбранный диапазон может быть горизонтальным или вертикальным.

Для C = N To K + 1 Step -1 term = term + AugMatrix(K, C) * ResultVector(C) Next C

ResultVector(K) = AugMatrix(K, N + 1) - терм Next K

Если Range(Application.Caller.Address).Rows.Count > 1, Then GaussElim = Application.Transpose(ResultVector) Else

GaussElim = ResultVector End If

Завершить функцию

Рисунок 9-4. Код VBA для пользовательской функции исключения Гаусса (папка «Глава 09 Одновременные уравнения», рабочая тетрадь «Одновременные уравнения II», модуль «GaussianElimFunction»)

Вычисление происходит в основном так, как описано в примере. Во-первых, элементы рабочей матрицы AugMatrix заполняются путем считывания значений из аргументов coeff_matrix и const_vector. Затем в цикле каждая строка нормируется путем деления на соответствующий диагональный элемент, и для следующих строк выполняется исключение Гаусса. Когда все строки заполнены, вычисляются результаты, начиная с последней строки верхней диагональной матрицы.

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

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

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

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

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

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

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

Установите максимальное количество итераций и относительную ошибку на вкладке "Формулы". Установите флажок, чтобы включить итерационные вычисления.

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

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

Вводим значения элементов в ячейки Excel в виде таблицы.
Найдем обратную матрицу. Выделите диапазон B6:E9, куда в дальнейшем будут помещены элементы матрицы (ориентируемся на количество строк и столбцов в исходной матрице). Откройте список функций (fx). В категории «Математика и триггер» мы находим функцию MINVERS. Аргумент представляет собой массив ячеек с элементами исходной матрицы.
Нажмите "ОК", и в левом верхнем углу диапазона появится значение. Последовательно нажмите кнопку F2 и Ctrl+Shift+Enter.
Умножаем обратную матрицу Ax -1x на матрицу B (только в таком порядке умножения!). Выделяем диапазон H1:H4, где впоследствии появятся элементы получившейся матрицы (ориентируемся на количество строк и столбцов матрицы B). Откройте диалоговое окно математической функции МУМНОЖ. Первый диапазон — это обратная матрица. Второй — матрица B.
Закройте окно с аргументами функции, нажав OK. Последовательно нажимайте кнопку F2 и комбинацию Ctrl+Shift+Enter.

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

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

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

Вычислим определители матриц, полученных заменой одного столбца в матрице A на столбец матрицы B. И будем решать методом Крамера.

Используйте функцию MDETERM для вычисления определителей. Аргумент представляет собой диапазон с соответствующей матрицей.

Мы также вычисляем определитель матрицы A (массив представляет собой диапазон матрицы A).

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

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

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

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

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

Запишем коэффициенты в матрицу A. И запишем постоянный член в матрицу B.

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

Мы присваиваем 0 всем коэффициентам матрицы A, кроме первого уравнения. Копируем значения первой строки двух матриц в ячейки B6:E6. В ячейку B7 вводим формулу: Затем выбираем диапазон B7:E7. Нажмите F2 и нажмите Ctrl + Shift + Enter. Мы вычли из второй строки первую, которая умножается на отношение первых элементов второго и первого уравнения.
Скопируйте введенную формулу в строку 7. Таким образом, мы избавились от коэффициентов перед A. Было сохранено только первое уравнение.
Приводим к 0 все коэффициенты при B в третьем и четвертом уравнениях. Копируем строки 5 и 6 (только значения). Затем переносим их ниже в строки 9 и 10. Эти данные должны остаться без изменений. Затем вводим формулу массива в ячейку A11
Прямая прогонка проводилась по методу Гаусса. В обратном порядке начинаем прогон с последней строки получившейся матрицы. Все элементы этой строки нужно разделить на коэффициент С. Введите в строку формулу массива:
В строке 15: из второй строки вычитаем третью, умноженную на коэффициент C из второй строки В строке 14: из первой строки вычитаем вторую и третью, умноженные на соответствующие коэффициенты В последней столбец новой матрицы, получаем корни уравнения.

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

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

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

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

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

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

Вводим значение в ячейку A1: a=1. Точность - три десятичных знака. Затем вводим формулу для вычисления текущего значения x в соседней ячейке (B1):

Мы контролируем значение f(x) в ячейке C1 по формуле:

Корень уравнения равен 1. Мы вводим значение 2 в ячейку A1. Получаем тот же результат. В заданном интервале есть только один корень.

Функция GAUSS относится к категории статистических функций Excel. Функции Список наиболее важных функций Excel для финансовых аналитиков. Эта шпаргалка охватывает сотни функций, которые важно знать аналитику Excel. Он вернет вероятность того, что член стандартной нормальной популяции будет находиться между средним значением и заданным числом стандартных отклонений от среднего. Другими словами, GAUSS дает нам способ решить конкретный случай, когда диапазон идет от среднего значения до точки выше среднего.

Как финансовый аналитик Финансовый аналитик Описание работы Приведенное ниже описание работы финансового аналитика дает типичный пример всех навыков, образования и опыта, необходимых для найма на работу аналитика в банке, учреждении или корпорации. Выполняйте финансовое прогнозирование, отчетность и отслеживайте операционные показатели, анализируйте финансовые данные, создавайте финансовые модели. Функция ГАУСС полезна для понимания рынков, цен и вероятностей. Например, если мы хотим инвестировать 5000 долларов в акции и облигации с годовой доходностью 15% и 6% соответственно, мы можем использовать эту функцию, чтобы узнать, как распределить наш инвестиционный капитал, чтобы получить желаемую прибыль.

GAUSS появился в MS Excel 2013 и недоступен в более ранних версиях. Эта функция способна эффективно обрабатывать большие наборы данных и предоставляет инструменты для простого управления данными, что делает ее подходящей для проведения анализа с использованием высокочастотных данных в реальном времени.

Формула Гаусса

=ГАУСС(z)

В формуле используется только один аргумент:

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

Как использовать функцию ГАУССА в Excel?

Чтобы понять, как использовать функцию ГАУССА, рассмотрим пример:

Пример

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

Мы получаем следующий результат:

Что следует помнить о функции GAUSS

Дополнительные ресурсы

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

Функции Excel для финансов Excel для финансов В этом руководстве по Excel для финансов представлены 10 основных формул и функций, которые необходимо знать, чтобы стать отличным финансовым аналитиком в Excel.
Усовершенствованные формулы Excel, которые необходимо знать Усовершенствованные формулы Excel, которые необходимо знать Эти расширенные формулы Excel крайне важны для понимания и выведут ваши навыки финансового анализа на новый уровень. Загрузите нашу бесплатную электронную книгу Excel!
Сочетания клавиш Excel для ПК и Mac Ярлыки Excel для ПК Mac Сочетания клавиш Excel — список наиболее важных и распространенных сочетаний клавиш MS Excel для пользователей ПК и Mac, специалистов в области финансов и бухгалтерского учета. Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, работу с данными, редактирование формул и ячеек и другие сочетания клавиш.

Бесплатное руководство по Excel

Чтобы овладеть искусством работы с Excel, ознакомьтесь с БЕСПЛАТНЫМ ускоренным курсом CFI по Excel. Основы Excel — формулы для финансов Вы ищете ускоренный курс по Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. , который научит вас, как стать опытным пользователем Excel. Изучите самые важные формулы, функции и сочетания клавиш, чтобы уверенно проводить финансовый анализ.

Запустите бесплатный курс CFI по Excel прямо сейчас Основы Excel — формулы для финансов Вы ищете ускоренный курс Excel? Пройдите бесплатное обучение Excel, чтобы начать карьеру в сфере корпоративных финансов и инвестиционно-банковских услуг, от Института корпоративных финансов.

В методе Гаусса-Жордана используется та же расширенная матрица [A|C], что и в методе исключения Гаусса. В методе исключения Гаусса исключались только матричные элементы ниже опорной строки; в методе Гаусса-Жордана элементы как выше, так и ниже опорной строки исключаются, в результате чего получается матрица единичных коэффициентов:

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

Пользовательская функция GaussJordanl VBA, показанная на рис. 9-5, включает частичный поворот. На компакт-диске, прилагаемом к этой книге, имеются две версии: первая версия, GaussJordanl, имеет синтаксис GaussJordanl(coeff_matrix, const_vector, valuejndex). Аргумент value_index указывает элемент возвращаемого вектора результатов. Вторая версия, GaussJordan2, имеет синтаксис GaussJordan2(coe/f_/nafr/x, const_vector) и возвращает вектор результатов. Вы должны выбрать диапазон ячеек подходящего размера и нажать CTRL+SHIFT+ВВОД (Windows), COMMAND+RETURN или CTRL+SHIFT+RETURN (Macintosh).

'Решение систем линейных уравнений методом исключения Гаусса-Жордана' +++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++

Function GaussJordanl (coeff_matrix, const_vector, valuejndex) ' Эта версия возвращает один элемент вектора решения, ' указанный valuejndex.

Dim X() как Double, AugMatrix() как Double, PivotRow() как Integer

Затемнить PivotLogical() как логическое значение

Размерьте I как целое число, J как целое число

Размерьте R как целое число, C как целое число, P как целое число

Размер N как целое число

Dim TempMax As Double, factor As Double N = coeff_matrix.Rows.Count

ReDim X(N), AugMatrix(N, N + 1), PivotRow(N), PivotLogical(N)

'Создать расширенную матрицу (A|B) с размерами N x (N+1) For I = 1 To N For J = 1 To N

AugMatrix(l, J) = coeff_matrix(l, J) Next J, I For J = 1 To N

'Инициализировать сводные элементы для каждой строки

'Выполнить исключение по столбцам. Для C = 1 до N

'Найти максимальное значение в столбце TempMax = 0 For R = 1 To N

Если Abs(AugMatrix(R, C)) P, то коэффициент = AugMatrix(J, C) / AugMatrix(P, C) For R = C + 1 To N + 1

AugMatrix(J, R) = AugMatrix(J, R) - коэффициент * AugMatrix(P, R) Next R End If Next J Next C

'Вычислить вектор решения и вернуть указанный элемент. Для C = 1 To N P = PivotRow(C)

X(C) = AugMatrix(P, N + 1) / AugMatrix(P, C) Следующий C

GaussJordanl = X(valuejndex) Конечная функция

Рисунок 9-5. Код VBA для пользовательской функции Gauss-Jordan (папка «Chapter 09 Simultaneous Equations», рабочая тетрадь «Simult Eqns II», модуль «GaussJordanFunction»)

Рисунки 9-6 и 9-7 иллюстрируют использование функций GaussElim и GaussJordan для решения систем одновременных уравнений, в данном случае спектрофотометрического определения концентраций смеси n компонентов путем измерения поглощения на n различных длинах волн, как описано в начале этой главы. Поглощение шестикомпонентной смеси измеряли при шести длинах волн; на рис. 9-3 значения оптической плотности образца представлены в столбце H, а известные молярные оптические способности шести компонентов — в B5:G10.

молярная абсорбция видов 1-6

Рисунок 9-6. Таблица данных для использования с функциями GaussElim или GaussJordan (папка «Глава 09 Одновременные уравнения», рабочая тетрадь «Одновременные уравнения II», лист «Исключение Fns»)

На рис. 9-7 показаны результаты, возвращаемые функциями GaussElim и GaussJordan2. Вектор результатов представляет собой вектор концентраций шести компонентов в смеси. Цифры процентной ошибки в столбцах L и N — это ошибки между известными концентрациями и концентрациями, возвращаемыми функциями.

По мере того, как количество одновременных уравнений становится больше, ошибки могут резко возрасти. В этой системе уравнений значения переменных с первой по пятую могут быть получены с хорошей точностью, поскольку каждая из них имеет максимум там, где другие виды не сильно поглощают. Концентрация шестого вида подвержена значительной ошибке. А если на последнем рисунке (рис. 9-8) измерения оптической плотности изменить случайным образом всего на ±1, ошибки значительно увеличатся.

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