Решение систем уравнений в ms excel сообщение

Обновлено: 18.05.2024

Матрица как прямоугольная таблица, составленная из чисел. Знакомство с методами решения систем линейных уравнений в приложении Microsoft Excel. Особенности решения систем уравнений методом Крамера и методом Гаусса. Характеристика программы Excel.

Рубрика	Программирование, компьютеры и кибернетика
Вид	курсовая работа
Язык	русский
Дата добавления	07.09.2015
Размер файла	181,7 K

Студенты, аспиранты, молодые ученые, использующие базу знаний в своей учебе и работе, будут вам очень благодарны.

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

в приложении Microsoft Excel

Введение

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

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

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

Проблема численного решения линейных уравнений интересует математиков уже несколько столетий. Первые математические результаты появились в XVIII веке. В 1750 году Г. Крамер (1704-1752) опубликовал свои труды по детерминантам квадратных матриц и предложил алгоритм нахождения обратной матрицы, известный, как правило, Крамера. Гаусс в 1809 году опубликовал работу, посвященную движению небесных тел, в которой был изложен метод для решения линейных систем, известный как метод исключения.

В 40-х годах XX века с появлением компьютеров сильно возрос интерес к численным методам. Тогда же началось активное исследование существующих методов для их реализации на ЭВМ и предпринимались активные попытки увеличить их точность.

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

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

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

Объект исследования - процесс решения систем уравнений с помощью различных численных методов (Метода Крамера, Метода Гаусса), посредством приложения MS Excel.

Предмет исследования - численные методы решения систем уравнений.

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

Для достижения цели поставлены следующие задачи:

1. изучить литературу по данной теме;

2. ознакомиться с численными методами решения систем уравнений - Методом Крамера, методом Гаусса;

3. создать компьютерные модели решения системы линейных уравнений разными способами в MS Excel;

4. сравнить имеющиеся численные методы решения систем линейных уравнений, выявить их достоинства и недостатки.

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

Гипотеза: все системы уравнений можно решить с помощью численных методов с той или иной степенью точности.

В данной работе будут рассмотрены численные способы в электронных таблицах Excel.

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

1. Численные методы решение систем линейных уравнений

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

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

Линейные системы двух уравнений с двумя неизвестными

Линейной системой двух уравнений с двумя неизвестными называется система вида

Решением системы уравнения с двумя неизвестными x и y называется такая пара (х0;у0), которая является решением каждого уравнения системы.

Решить систему уравнений - это значит найти все её решения или установить, что их нет. Из школьного курса алгебры нам известно три способа решения уравнений:

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

1.2 Матричное представление системы линейных уравнений. Определитель матрицы

Матрица - прямоугольная таблица, составленная из чисел.

Пусть дана квадратная матрица 2 порядка:

Определителем (или детерминантом) 2 порядка, соответствующим данной матрице, называется число

Определитель (или детерминант) 3 порядка, соответствующим матрице называется число

Пример1: Найти определители матриц и

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

Пусть дана система 3х линейных уравнений с 3мя неизвестными

Систему (1) можно записать в матрично-векторной форме

где А - матрица коэффициентов

В - расширенная матрица

Х - искомый компонентный вектор;

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

Пусть дана система линейных уравнений с двумя неизвестными:

Рассмотрим решение систем линейных уравнений с двумя и тремя неизвестными по формулам Крамера. Теорема 1. Если главный определитель системы отличен от нуля, то система имеет решение, притом единственное. Решение системы определяется формулами:

где x1, x2 - корни системы уравнений,

- главный определитель системы, x1, х2 - вспомогательные определители.

Главный определитель системы определяется:

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

Пусть дана система линейных уравнений с тремя неизвестными:

Теорема 2. Если главный определитель системы отличен от нуля, то система имеет решение, притом единственное. Решение системы определяется формулами:

где x1, x2, x3 - корни системы уравнений,

- главный определитель системы,

x1, x2, x3 - вспомогательные определители.

Главный определитель системы определяется:

1. Составить табличку (матрицу) коэффициентов при неизвестных и вычислить основной определитель .

2. Найти - дополнительный определитель x, получаемый из заменой первого столбца на столбец свободных членов.

3. Найти - дополнительный определитель y, получаемый из заменой второго столбца на столбец свободных членов.

4. Найти - дополнительный определитель z, получаемый из заменой третьего столбца на столбец свободных членов. Если основной определитель системы не равен нулю, то выполняют пункт 5.

5. Найти значение переменной x по формуле x / .

6. Найти значение переменной у по формуле y / .

7. Найти значение переменной z по формуле z / .

8. Записать ответ: х=…; у=…, z=… .

Решить систему уравнений по формулам Крамера:

Пример 2. Решить систему уравнений по формулам Крамера.

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

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

Метод последовательного исключения неизвестных Гаусса является одним из наиболее универсальных и эффективных методов решения линейных систем. (Карл Фридрих Гаусс (1777-1855) - немецкий математик и физик, работы которого оказали большое влияние на дальнейшее развитие высшей алгебры, геометрии, теории чисел, теории электричества и магнетизма.) Этот метод известен в различных вариантах уже более 2000 лет. Он относится к числу прямых методов.

Процесс решения по методу Гаусса состоит из двух этапов, называемых прямым и обратным ходом. На первом этапе система (3) приводится к треугольному виду; на втором (обратный ход) идет последовательное определение неизвестных из указанной треугольной системы.

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

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

Пример 3. Решить систему уравнений по методу Гаусса.

1. Уравнение (1) разделим на a11, т.е. на 2, получим уравнение

Умножим полученное уравнение (4) на a21, т.е. на 1, получим уравнение (5).

2. Вычтем из уравнений (2) и (3) уравнение (5), получим уравнения (6) и (7).

3. Уравнение (6) разделим на a22 , т.е. на 3/2, получим уравнение (8).

4. Умножим уравнение (8) на a32 , т.е. на 1/2, получим уравнение (9).

5. Вычтем из уравнения (7) уравнение (9) получим уравнение (10).

6. Итак, прямой ход закончен, начинаем обратный ход. Подставим (10) в уравнение (8), получим x2=2 (11)

7. Подставим (11) и (10) в уравнение (5), получим: x1=1 (12)

Ответ: x1=1; x2=2; x3=3

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

2.1 Среда разработки модели Microsoft Excel

линейный уравнение microsoft

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

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

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

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

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

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

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

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

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

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

f(x)

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

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

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

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

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

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

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

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

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

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

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

Тип урока: урок закрепления изученного материала и объяснения нового.

ХОД УРОКА

I. Организационная часть.

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

Ответ: “Знание – сила!”

Молодцы! А знаете, кому принадлежит это выражение? (Если нет, то один ученик ищет ответ в Интернете. Остальные отвечают на вопросы: Для чего предназначена программа Excel? (Программа Excel предназначена для хранения и обработки данных, представленных в табличном виде) Что собой представляет документ в Excel? (Каждый документ в Excel представляет собой набор таблиц – рабочую книгу, которая состоит из одного или многих рабочих листов) Какая функция используется для подсчета суммы чисел? (Функция СУММ). Как определить адрес ячейки? (Excel вводит номера ячеек автоматически. Адрес ячейки составляется как объединение номеров столбца и строки без пробела между ними)

Выражение английского философа Френсиса Бэкона “Знание – сила!” и будет эпиграфом к нашему уроку. ("Нравственные и политические очерки", 1597).

II. Повторение пройденного материала.

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

Хорошо, все справились и каждому поставим соответствующие оценки в журнал. А давайте устроим путешествие в математику и вспомним, что мы понимаем под понятием: “Решить систему уравнений”? (Найти такие значения х и у, которые будут удовлетворять и первое уравнение и второе). Какие способы существуют для решения систем уравнений (метод подстановки, метод сложения и графический способ). Сегодня мы с вами научимся решать системы уравнений, используя возможности электронных таблиц.

III. Объяснение нового.

А. Решим систему графическим способом. Преобразуем данную систему . Для решения воспользуемся диаграммой, на которой отобразим графики обеих функций. Заполняем столбец А: заполняем ячейки А2:А22 числами от -5 до 5 с шагом 0,5. (в ячейку А2 заносим число -5, в ячейку А3 – число -4,5, выделяем ячейки А2 и А3, установим курсор мыши на правый нижний угол рамки (указатель примет форму черного крестика) и растягиваем рамку вниз, пока последнее значение не станет равным 5). При заполнении столбца В в ячейку В2 заносим формулу =А2*А2, которую затем копируем до ячейки В22. (протянем формулу за правый нижний угол). При заполнении столбца С в ячейку С2 заносим формулу =1-2*А2, копируем ее до ячейки С22. Выделим блок с данными, с помощью Мастера диаграмм выберем тип диаграммы Точечная и построим графики функций. Координаты точек пересечения графиков – решения системы.

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

Запишем алгоритм решения систем уравнений графическим способом:

1. Преобразовать систему уравнений, если это необходимо.

2. Задать начальные значения для Х.

3. Найти значение первой функции при заданных Х.

4. Найти значение второй функции при тех же Х.

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

6. Решение системы - точка пересечения графиков функций.

7. Для нахождения координат точек пересечения с заданной точностью построить новый график на том отрезке, где находится решение, с шагом, равным значению точности.

Б. Решить систему уравнений . Занесем в электронную таблицу исходные данные и расчетные формулы следующим образом:.

Для решения системы уравнений воспользуемся надстройкой Поиск решения, которая запускается через Сервис (-Надстройки) и заполним диалоговое окно следующим образом:

При нажатии на кнопку Выполнить происходит решение системы уравнений и в ячейках B3 и B4 высвечивается результат.

Запишем примерный алгоритм решения системы уравнений, используя Поиск решения

1. Преобразовать систему уравнений, если это необходимо

2. Записать исходные данные (в ячейку А1 ввести текст “Решите уравнение”, в ячейку В1 записать первое уравнение, в ячейку В2 второе уравнение, в ячейку А3 ввести текст “Х=”, в ячейку А4 “Y=”, в ячейку А5 “уравнение 1”, в ячейку А6 “уравнение 2”. В ячейке B3 хотим получить значение Х, в ячейке В4 – значение Y, их оставляем пустыми.

3. В ячейку В5 переписать уравнение 1, используя правило записи арифметических выражений, следующим образом: в левой части вместо Х указывать ячейку В3, вместо Y ячейку В4, правую часть отбросить. Таким же образом переписать левую часть второго уравнения в ячейку В6.

4. Выбрать команду Сервис – Поиск решения.

5. Установить целевую ячейку - ту ячейку, в которой содержится формула, например, В5 и задать значение, равное значению правой части первого уравнения

6. В поле “изменяя ячейки” указать ячейки, в которых хотим увидеть ответ (В3 и В4)

7. Вести ограничение $B$6 = -3. Для этого щелкнуть на кнопке Добавить и в полученном окне установить реквизиты следующим образом: в поле Ссылка на ячейку указать ячейку, в которой записана левая часть другого уравнения, в другом поле выбрать знак “=”, в третьем ввести число, равное значению правой части. Закрыть окно Добавить ограничение, щелкнув кнопкой ОК

Microsoft Office Excel может здорово помогать студентам и магистрантам в решении различных задач из высшей математики. Не многие пользователи знают, что базовые математические методы поиска неизвестных значений в системе уравнений реализованы в редакторе. Сегодня рассмотрим, как происходит решение уравнений в excel.

Первый метод

1. Зададимся простым квадратичным уравнением и найдем решение при х=0.

2. Переходите к инструменту и заполняете все необходимые поля

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

4. Подставив полученное значение в исходное уравнение можно проверить правильность решения.

Второй метод

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

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

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

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

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

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

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

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

Третий метод

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

1. Записываете произвольную систему уравнений.

2. Отдельно выписываете аргументы при неизвестных в каждую ячейку. Если нет какого-то из иксов – ставите ноль. Аналогично поступаете с цифрами после знака равно.

3. Выделяете в свободной зоне диапазон ячеек равный размеру матрицы. В строке формул пишете МОБР и выбираете массив аргументов. Чтобы функция сработала корректно нажимаете одновременно Ctrl+Shift+Enter.

4. Теперь находите решение при помощи функции МУМНОЖ. Также предварительно выделяете диапазон размером с матрицу результатов и нажимаете уже известное сочетание клавиш.

Четвертый метод

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

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

1. Зададимся произвольной системой уравнений и выпишем все коэффициенты в отдельный массив.

2. Копируете первую строку в другое место, а ниже записываете формулу следующего вида: =C67:F67-$C$66:$F$66*(C67/$C$66).

Поскольку работа идет с массивами, нажимайте Ctrl+Shift+Enter, вместо Enter.

3. Маркером автозаполнения копируете формулу в нижнюю строку.

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

5. Повторяете операцию для третьей строки, используя формулу

=C73:F73-$C$72:$F$72*(D73/$D$72). На этом прямая последовательность решения закончена.

6. Теперь необходимо пройти систему в обратном порядке. Используйте формулу для третьей строчки следующего вида =(C78:F78)/E78

7. Для следующей строки используйте формулу =(C77:F77-C84:F84*E77)/D77

8. В конце записываете вот такое выражение =(C76:F76-C83:F83*D76-C84:F84*E76)/C76

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

Метод Гаусса является одним из самых трудоемких среди прочих вариантов, однако позволяет пошагово просмотреть процесс поиска неизвестных.

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

Решим систему из 3-х линейных алгебраических уравнений с помощью обратной матрицы (матричным методом).

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

Систему n линейных алгебраических уравнений с n неизвестными можно решать матричным методом только тогда, когда определитель основной матрицы системы отличен от нуля (в противном случае мы имеем линейно зависимые уравнения и соответственно решение систем не единственное). В нашем случае определитель =12.

Для этого выделите ячейки A18:C20 , а в Строке формул введите =МОБР(A11:C13) , затем нажмите CTRL+SHIFT+ENTER .

Решение системы уравнений получим умножением обратной матрицы и столбца свободных членов. Перемножить матрицы можно с помощью формулы массива =МУМНОЖ() .

Для этого выделите ячейки F18:F20 , а в Строке формул введите =МУМНОЖ(A18:C20;F11:F13) , затем нажмите CTRL+SHIFT+ENTER .

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