Решение линейных уравнений в excel

Обновлено: 07.01.2025

Технология решения систем линейных уравнений для случая, когда m=n,
(m-количество уравнений, n – количество неизвестных) рассмотрена в другой статье. Для решения подобных уравнений можно применить метод обратной матрицы. Однако, в общем случае m может быть не всегда равно n. Возможны три случая: m<n, m= n и m>n.

При m<n, если система является совместной, то она не определена и имеет бесконечное множество решений.

Если m>n и система совместна, то матрица системы А имеет по крайней мере m-n линейно независимых строк. В этом случае решение может быть получено отбором n любых линейно независимых уравнений и применением формулы X=A-1 х B , т.е. метод обратной матрицы.

Однако, при решении задачи в электронной таблице удобнее применить более общий подход - метод наименьших квадратов.

Его суть состоит в том, что обе части уравнения нужно умножить на транспонированную матрицу системы Ат : АтАХ=АтВ.

Затем обе части уравнения нужно умножить на (АтА)-1 . Если эта матрица существует, то система определена. С учетом того, что (АтА)-1АтА=Е , получаем решение системы в виде Х=(АтА)-1 АтВ (1).

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

Как можно увидеть, здесь m > n. Воспользуемся для решения формулой 1. Как можно увидеть, здесь m > n. Воспользуемся для решения формулой 1.

Как можно увидеть, здесь m > n.
Воспользуемся для решения формулой 1.

1. Введем значения элементов матрицы системы А в диапазон ячеек рабочего листа, например А2:B4 .

2. Введем значения элементов вектора В в диапазон ячеек рабочего листа, например D2:D4 (см рис.).

3. Транспонируем исходную матрицу, для чего выделим диапазон ячеек размерностью 3 х 2, например А6:C7 , введем в него формулу:

= ТРАНСП(А2:В4) и нажмем комбинацию клавиш Ctrl + Shift + Enter - в выделенном диапазоне будет вычислен результат транспонирования.

4. Вычислим произведение АтВ , для чего выделим диапазон из двух ячеек ( Е6:Е7 ) и введем в него формулу = МУМНОЖ(А6:C7;D2:D4) .

5. Вычислим произведение А тА , для чего выделим диапазон ( А9:В10 ) и введем в него формулу = МУМНОЖ(A6:C7;А2:В4) .

6. Выделим диапазон ( D9:E10 ), введем в него формулу = МОБР(А9:B10) для вычисления обратной матрицы (А тА)-1

7. Для вычисления итогового результата -решения системы уравнений выделим диапазон ( В12:В13 ) и введем в него формулу для умножения матриц (АтА)-1 АТА: =МУМНОЖ(D9:E10;A9:B10) .

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

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

Рассмотрим эту технологию более подробно на том же примере. Формула, которая дает решение системы Х=(АтА)-1 (АтВ) содержит две группы (заключенные в скобки), которые должны быть перемножены с помощью функции МУМНОЖ(параметр_1;Параметр_2) . Параметр_1 в нашем случае сам является вычисляемым выражением (АтА)-1 , параметр_2 также вычисляется (АтВ) . При вводе формул, представляющих сложные выражения целесообразно придерживаться технологии, которая предлагается далее. Для решения задачи выполним действия:

1. Выделим диапазон, в котором будет вычисляться результат, и, используя мастер функций, введем в него функцию МУМНОЖ ( значок fx слева от строки формул ) , переведем курсор в поле массив2 диалогового окна “ Аргументы функции ” после чего щелкнем на кнопке fx, расположенной в левой части строки формул – окно “ Аргументы функции ” закроется, а в строке формул появится выражение =МУМНОЖ(;) (символ ; разделяет друг от друга аргументы функции).

2. Первый аргумент в нашем случае является обратной матрицей результата произведения матриц. Переведем щелчком курсор в поле первого аргумента и, используя список “ Функции ” (рис. ниже), включим функцию МОБР и закроем окно “ Аргументы функции ” щелкнув на кнопке fx в левой части строки формул. При этом курсор остается в строке формул в поле аргумента функции МОБР .

3. Аргумент функции МОБР в рассматриваемой задаче является произведением матриц. Используя список “ Функции ” включим функцию МУМНОЖ . Переведем курсор в поле массив2 диалогового окна “ Аргументы функции ” и укажем адрес массива, содержащего элементы матрицы А . Затем переведем курсор в поле переведем курсор в поле массив2 диалогового окна и используя список “ Функции ”, включим функцию ТРАНСП и в качестве ее аргумента укажем адрес массива, содержащего элементы матрицы А . Щелкнем на кнопке fx – окно “ Аргументы функции ” закроется.

4. В строке формул установим курсор в область второго аргумента первой функции МУМНОЖ . Используя список “ Функции ” включим функцию МУМНОЖ . В поскольку второй аргумент не нужно вычислять, первую очередь укажем его - в поле массив2 введем адрес диапазона, в котором содержатся элементы вектора В.

5. Переключим курсор в поле массив2 диалогового окна “ Аргументы функции ”, используя список “ Функции ” включим функцию ТРАНСП и в поле массив этой функции укажем адрес диапазона, в котором содержатся значения элементов матрицы А .

Запись формулы для решения системы уравнений методом наименьших квадратов завершена. Она имеет окончательный вид: =МУМНОЖ(МОБР(МУМНОЖ(ТРАНСП(A2:B4);A2:B4));МУМНОЖ(ТРАНСП(A2:B4);D2:D4))

6. Нажмем комбинацию клавиш <Ctrl> + <Shift> + <Enter> - в ячейках выделенного диапазона будет результат решения системы.

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

Количество переменных в системе уравнений должно быть равно количеству уравнений. Предыдущий пример использует два уравнения с двумя переменными. Три уравнения требуются для того, чтобы найти значения трех переменных (х,у и z). Общие действия по решению систем уравнений следующие (рис. 128.1).

Рис. 128.1. Использование формулы для решения системы из двух уравнений

1. Выразите уравнения в стандартной форме. Если это необходимо, используйте основы алгебры и перепишите уравнение так, чтобы все переменные отображались по левую сторону от знака равенства. Следующие два уравнения идентичны, но второе приведено в стандартном виде:
   3x - 8 = -4y
   3x + 4y = 8 .
2. Разместите коэффициенты в диапазоне ячеек размером n x n, где n представляет собой количество уравнений. На рис. 128.1 коэффициенты находятся в диапазоне I2:J3 .
3. Разместите константы (числа с правой стороны от знака равенства) в вертикальном диапазоне ячеек. На рис. 128.1 константы находятся в диапазоне L2:L3 .
4. Используйте массив формул для расчета обратной матрицы коэффициентов. На рис. 128.1 следующая формула массива введена в диапазон I6:J7 (не забудьте нажать Ctrl+Shift+Enter, чтобы ввести формулу массива): =МОБР(I2:J3) .
5. Используйте формулу массива для умножения обратной матрицы коэффициентов на матрицу констант. На рис. 128.1 следующая формула массива введена в диапазон J10:JJ11 , который содержит решение ( x = 7,5 и у = -3,625 ): =МУМНОЖ(I6:J7;L2:L3) . На рис. 128.2 показан лист, настроенный для решения системы из трех уравнений.

Рис. 128.2. В Excel можно решить систему из трех уравнений, применив нужные формулы

Требуется на отрезке [-1; 4] построить график функции f(x). Параметры a = 5 и b = 2 необходимо задать в отдельных ячейках.

Решение (1 ряд данных)

Чтобы построить график функции в MS EXCEL можно использовать диаграмму типа График или Точечная.

СОВЕТ : О построении диаграмм см. статью Основы построения диаграмм в MS EXCEL . О различии диаграмм Точечная и График см. статью График vs Точечная диаграмма в MS EXCEL .

Создадим таблицу с исходными данными для x от -1 до 4, включая граничные значения (см. файл примера, лист Ряд1 ):

Шаг по х выберем равным 0,2, чтобы график содержал более 20 точек.

Чтобы построить диаграмму типа Точечная:

• выделите любую ячейку таблицы;
• во вкладке Вставка в группе Диаграммы выберите диаграмму Точечная с прямыми отрезками и маркерами .

Чтобы построить диаграмму типа График:

• выделите любую столбец f(x) вместе с заголовком;
• во вкладке Вставка в группе Диаграммы выберите диаграмму График маркерами .

У обеих диаграмм один общий недостаток - обе части графика соединены линией (в диапазоне х от 1 до 1,2). Из этого можно сделать ошибочный вывод, что, например, для х=1,1 значение функции равно около -15. Это, конечно же, не так. Кроме того, обе части графика одного цвета, что не удобно. Поэтому, построим график используя 2 ряда данных .

Решение (2 ряда данных)

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

У такой диаграммы имеется недостаток - в диапазоне х от 1 до 1,2 на диаграмме теперь нет вообще значений. Чтобы избежать этого недостатка - построим диаграмму типа Точечная с 3-мя рядами данных.

Решение (3 ряда данных)

Для построения графика используем 2 таблицы с данными для каждого уравнения, см. файл примера, лист График .

Первое значение второго графика возьмем чуть больше 1, например, 1,00001, чтобы как можно ближе приблизиться к значению, в котором происходит разрыв двух графиков. Также для точки со значением х=1 построим на диаграмме одну точку (ряд №3), чтобы показать, что для этого х значение второго уравнения не вычисляется (хотя фактически вычисляется).

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

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

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

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

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

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

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

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

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

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

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

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

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

  
• Как открыть файл ai в фотошопе
  
• Ошибка в программе на языке программиста кроссворд
  
• Невозможно открыть окно outlook недействительный xml формат
  
• Как сделать программу которая выключает компьютер
  
• Как запустить биос на материнской плате asus p5ql