Решение системы уравнений в Excel
Возможно вы слышали о нобелевском лауреате, психологе и исследователе по имени Дэниель Канеман. Канеман занимался наукой, которую называют термином «поведенческая экономика», т.е. изучал реакции, поведение и суждения людей в типовых жизненных (и экономических) ситуациях и условиях неопределенности. В его книге, которая называется «Думай медленно — решай быстро» (очень рекомендую, кстати) в качестве одного из примеров когнитивных искажений — несознательной автоматической реакции — приводится следующая задача: Бейсбольная бита и мяч стоят вместе 1 доллар 10 центов.
Бита дороже мяча на 1 доллар.
Сколько стоит мяч? Подозреваю, что вашей первой рефлекторной мыслью, скорее всего, будет «10 центов!» 🙂 Но весьма скоро, я уверен, вы сообразите, что на самом деле всё не так примитивно и для получения ответа нужно решить простую систему уравнений (здесь b — это бита, а m — это мяч): 
Конечно можно «тряхнуть стариной» и решить всё вручную на бумажке через подстановку переменных — как-то так: 
Но, во-первых, на практике уравнения могут быть сложнее и переменных может оказаться сильно больше двух и, во-вторых, у нас с вами есть Microsoft Excel — универсальный мега-инструмент, величайшее изобретение человечества. Так что давайте-ка лучше разберём как решить нашу задачу с его помощью.
Способ 1. Матричные функции МУМНОЖ и МОБР
Само собой, изобретать велосипед тут не надо — прогрессивное человечество в лице математиков давным-давно придумало кучу способов для решения подобных задач. В частности, если уравнения в нашей системе линейные (т.е. не используют степени, логарифмы, тригонометрические функции типа sin, cos и т.д.), то можно использовать метод Крамера. Сначала записываем числовые коэффициенты, стоящие перед нашими переменными в виде матрицы (в нашем случае — размером 2х2, в общем случае — может быть и больше). Затем находим для неё так называемую обратную матрицу , т.е. матрицу, при умножении которой на исходную матрицу коэффициентов получается единица. В Excel это легко сделать с помощью стандартной математической функции МОБР (MINVERSE) :
Здесь важно отметить, что если у вас свежая версия Excel 2021 или Excel 365, то достаточно ввести эту функцию обычным образом в первую ячейку (G7) — сразу получится динамический массив с обратной матрицей 2х2. Если же у вас более старая версия Excel, то эту функцию нужно обязательно вводить как формулу массива, а именно:
- Выделить диапазон для результатов — G7:H8
- Ввести функцию =МОБР(B7:C8) в строку формул
- Нажать на клавиатуре сочетание клавиш Ctrl + Shift + Enter
Замечательное свойство обратной матрицы состоит в том, что если умножить её на значения правых частей наших уравнений (свободные члены), то мы получим значения переменных, при которых левые и правые части уравнений будут равны, т.е. решения нашей задачи. Выполнить такое матричное умножение можно с помощью ещё одной стандартной экселевской функции МУМНОЖ (MMULT) :
Если у вас старая версия Excel, то не забудьте также ввести её в режиме формулы массива, т.е. сначала выделить диапазон K7:K8, а после ввода функции нажать сочетание клавиш Ctrl + Shift + Enter .
Само собой, уравнений и переменных может быть больше, да и посчитать всё можно сразу в одной формуле, вложив используемые функции одна в другую:
Не так уж и сложно, правда? Однако надо понимать, что этот метод подходит только для решения систем линейных уравнений. Если у вас в уравнениях используются функции посложнее четырех базовых математических действий, то зачастую проще будет пойти другим путем — через подбор.
Способ 2. Подбор надстройкой «Поиск решения» (Solver)
Принципиально другой способ решения подобных задач — это итерационные методы, т.е. последовательный подбор значений переменных, чтобы после подстановки их в наши уравнения мы получили верные равенства. Само собой, подбор имеется ввиду не тупой и долгий (брутфорс), а умный и быстрый, благо математики, опять же, давным-давно придумали кучу различных методов для решения таких задач буквально за несколько итераций.
В Microsoft Excel некоторые из этих методов реализованы в стандартной надстройке Поиск решения (Solver) . Её можно подключить через Файл — Параметры — Надстройки — Перейти (File — Options — Add-ins — Go to) или на вкладке Разработчик — Надстройки (Developer — Add-ins) .
Давайте рассмотрим её использование на следующей задаче. Предположим, что нам с вами нужно решить вот такую систему из двух нелинейных уравнений:
Подготавливаем основу для оптимизации в Excel:
- В жёлтых ячейках C9:C10 лежат текущие значения наших переменных, которые и будут подбираться в процессе оптимизации. В качестве стартовых можно взять любые значения, например, нули или единицы — роли не играет. Для удобства, кстати, этим ячейкам можно дать имена, назвав их именами переменных x и y, — для этого выделите диапазон C9:C10 и выберите команду Формулы — Создать из выделенного — Слева (Formulas — Create from selection — Left column) .
- В зелёных ячейках E9:E10 введены наши уравнения с использованием либо прямых ссылок на жёлтые ячейки переменных, либо созданных имён (так нагляднее). В результате мы видим, чему равны наши уравнения при текущих значениях переменных.
- В синих ячейках F9:F10 введены значения правых частей наших уравнений, к которым мы должны стремиться.
Теперь запускаем нашу надстройку на вкладке Данные — Поиск решения (Data — Solver) и вводим в появившемся диалоговом окне следующие параметры:
- Оптимизировать целевую функцию (Set target cell) — любая из двух наших зелёных ячеек с уравнениями, например E9.
- Изменяя ячейки переменных (By changing cells) — жёлтые ячейки с текущими значениями переменных, которыми мы «играем».
- Добавляем ограничение с помощью кнопки Добавить (Add) и задаём равенство левой и правой части наших уравнений, т.е. зелёного и голубого диапазонов.
- В качестве метода решения выбираем Поиск решения нелинейных задач методом ОПГ, т.к. уравнения у нас нелинейные. Для линейных можно смело выбирать симплекс-метод.
После нажатия на кнопку Найти решение (Solve) через пару мгновений (или не пару — это зависит от сложности задачи) мы должны увидеть окно с результатами. Если решение найдено, то в жёлтых ячейках отобразятся подобранные значения наших переменных:
Обратите внимание, что поскольку мы здесь используем итерационные, а не аналитические методы, то зеленые ячейки не совсем равны голубым, т.е. найденное решение не абсолютно точно. На практике, конечно же, такой точности вполне достаточно для большинства задач, и если необходимо, её можно настроить, вернувшись в окно Поиск решения и нажав кнопку Параметры (Options) .
Графический способ решения уравнений в среде Microsoft Excel 2007
Тип урока: Обобщение, закрепление пройденного материала и объяснение нового.
Цели и задачи урока:
- повторение изученных графиков функций;
- повторение и закрепление графического способа решения уравнений;
- закрепление навыков записи и копирования формул, построения графиков функций в электронных таблицах Excel 2007;
- формирование и первичное закрепление знаний о решении уравнений с использованием возможностей электронных таблиц Excel 2007;
- формирование мышления, направленного на выбор оптимального решения;
- формирование информационной культуры школьников.
Оборудование: персональные компьютеры, мультимедиапроектор, проекционный экран.
Материалы к уроку: презентация Power Point на компьютере учителя (Приложение 1).
Организационный момент.
Слайд 1 из Приложения1 ( далее ссылки на слайды идут без указания Приложения1).
Объявление темы урока.
1. Устная работа (актуализация знаний).
Слайд 2 — Соотнесите перечисленные ниже функции с графиками на чертеже (Рис. 1):
у = 6 — х; у = 2х + 3; у = (х + 3) 2 ; у = -(х — 4) 2 ; .
Слайд 3 Графический способ решения уравнений вида f(x)=0.
Корнями уравнения f(x)=0 являются значения х1, х2, … точек пересечения графика функции y=f(x) с осью абсцисс (Рис. 2).
Найдите корни уравнения х 2 -2х-3=0, используя графический способ решения уравнений (Рис.3).
Слайд 5 Графический способ решения уравнений вида f (x)=g (x).
Корнями уравнения f(x)=g(x) являются значения х1, х2, … точек пересечения графиков функций y=f(x) и у=g(x). (Рис. 4):
Слайд 6 Найдите корни уравнения , используя графический способ решения уравнений (Рис. 5).
2. Объяснение нового материала. Практическая работа.
Решение уравнений графическим способом требует больших временных затрат на построение графиков функций и в большинстве случаев дает грубо приближенные решения. При использовании электронных таблиц, в данном случае – Microsoft Excel 2007, существенно экономится время на построение графиков функций, и появляются дополнительные возможности нахождения корней уравнения с заданной точностью (метод Подбор параметра).
I. Графический способ решения уравнений вида f(x)=0 в Excel.
Дальнейшая работа выполняется учителем в Excel одновременно с учениками с подробными (при необходимости) инструкциями и выводом результатов на проекционный экран. Слайды Приложения 1 используются для формулировки задач и подведения промежуточных итогов.
Пример1: Используя средства построения диаграмм в Excel, решить графическим способом уравнение —х 2 +5х-4=0.
Для этого: построить график функции у=-х 2 +5х-4 на промежутке [ 0; 5 ] с шагом 0,25; \найти значения х точек пересечения графика функции с осью абсцисс.
Выполнение задания можно разбить на этапы:
1 этап: Представление функции в табличной форме (рис. 6):
- в ячейку А1 ввести текст Х, в ячейку A2 — Y;
- в ячейку В1 ввести число 0, в ячейку С1 – число 0,25;
- выделить ячейки В1:С1, подвести указатель мыши к маркеру выделения, и в тот момент, когда указатель мыши примет форму черного крестика, протянуть маркер выделения вправо до ячейки V1 (Рис. 7).
- в ячейку B2 ввести формулу =-(B1^2)+5*B1-4;
При вводе формулы можно вводить адрес ячейки с клавиатуры (не забыть переключиться на латиницу), а можно просто щелкнуть мышью на ячейке с нужным адресом.
После ввода формулы в ячейке окажется результат вычисления по формуле, а в поле ввода строки формул — сама формула (Рис. 8):
- скопировать содержимое ячейки B2 в ячейки C2:V2 за маркер выделения. Весь ряд выделенных ячеек заполнится содержимым первой ячейки. При этом ссылки на ячейки в формулах изменятся относительно смещения самой формулы.
2 этап: Построение диаграммы типа График.
- выделить диапазон ячеек B2:V2;
- на вкладке Вставка|Диаграммы|График выбрать вид График;
- на вкладке Конструктор|Выбрать данные (Рис. 9) в открывшемся окне «Выбор источника данных» щелкнуть по кнопке Изменить в поле Подписи горизонтальной оси — откроется окно «Подписи оси». Выделить в таблице диапазон ячеек B1:V1 (значения переменной х). В обоих окнах щелкнуть по кнопкам ОК;
- на вкладке Макет|Оси|Основная горизонтальная ось|Дополнительные параметры основной горизонтальной оси выбрать:
Интервал между делениями: 4;
Интервал между подписями: Единица измерения интервала: 4;
Положение оси: по делениям;
Выбрать ширину и цвет линии (Вкладки Тип линии и Цвет линии);
- самостоятельно изменить ширину и цвет линии для вертикальной оси;
- на вкладке Макет|Сетка|Вертикальные линии сетки по основной оси выбрать Основные линии сетки.
Примерный результат работы приведен на рис. 10:
3 этап: Определение корней уравнения.
График функции у=-х 2 +5х-4 пересекает ось абсцисс в двух точках и, следовательно, уравнение -х 2 +5х-4=0 имеет два корня: х1=1; х2=4.
II. Графический способ решения уравнений вида f(x)=g(x) в Excel.
Пример 2: Решить графическим способом уравнение .
Для этого: в одной системе координат построить графики функций у1= и у2=1-х на промежутке [ -1; 4 ] с шагом 0,25; найти значение х точки пересечения графиков функций.
1 этап: Представление функций в табличной форме (рис. 1):
Рис. 11. 2 этап: Построение диаграммы типа График.
Примерный результат работы приведен на Рис. 12:
3 этап: Определение корней уравнения.
Графики функций у1= и у2=1-х пересекаются в одной точке (0;1) и, следовательно, уравнение имеет один корень – абсцисса этой точки: х=0.
III. Метод Подбор параметра.
Графический способ решения уравнений красив, но далеко не всегда точки пересечения могут быть такими «хорошими», как в специально подобранных примерах 1 и 2.
Возможности электронных таблиц позволяют находить приближенные значения коней уравнения с заданной точностью. Для этого используется метод Подбор параметра.
Пример 3: Разберем метод Подбор параметра на примере решения уравнения —х 2 +5х-3=0.
1 этап: Построение диаграммы типа График для приближенного определения корней уравнения.
Построить график функции у=—х 2 +5х-3, отредактировав полученные в Примере 1 формулы.
- выполнить двойной щелчок по ячейке B2, внести необходимые изменения;
- с помощью маркера выделения скопировать формулу во все ячейки диапазона C2:V2.
Все изменения сразу отобразятся на графике.
Примерный результат работы приведен на Рис. 13:
2 этап: Определение приближенных значений корней уравнения.
График функции у=-х 2 +5х-3 пересекает ось абсцисс в двух точках и, следовательно, уравнение -х 2 +5х-4=0 имеет два корня.
По графику приближенно можно определить, что х1≈0,7; х2≈4,3.
3 этап: Поиск приближенного решения уравнения с заданной точностью методом Подбор параметра.
1) Начать с поиска более точного значения меньшего корня.
По графику видно, что ближайший аргумент к точке пересечения графика с осью абсцисс равен 0,75. В таблице значений функции этот аргумент размещается в ячейке E1.
- Выделить ячейку Е2;
- перейти на вкладку Данные|Анализ «что-если»|Подбор параметра…;
В открывшемся диалоговом окне Подбор параметра (Рис. 14) в поле Значение ввести требуемое значение функции: 0.
В поле Изменяя значение ячейки: ввести $E$1 (щелкнув по ячейке E1).
Щелкнуть по кнопке ОК.
- В окне Результат подбора (Рис. 15) выводится информация о величине подбираемого и подобранного значения функции:
- В ячейке E1 выводится подобранное значение аргумента 0,6972 с требуемой точностью (0,0001).
Установить точность можно путем установки в ячейках таблицы точности представления чисел – числа знаков после запятой (Формат ячеек|Число|Числовой).
Итак, первый корень уравнения определен с заданной точностью: х1≈0,6972.
2) Самостоятельно найти значение большего корня с той же точностью. (х2≈4,3029).
IV. Метод Подбор параметра для решения уравнений вида f(x)=g(x).
При использовании метода Подбор параметров для решения уравнений вида f(x)=g(x) вводят вспомогательную функцию y(x)=f(x)-g(x) и находят с требуемой точностью значения х точек пересечения графика функции y(x) с осью абсцисс.
3. Закрепление изученного материала. Самостоятельная работа.
Задание: Используя метода Подбор параметров, найти корни уравнения с точностью до 0,001.
- ввести функцию у= и построить ее график на промежутке [ -1; 4 ] с шагом 0,25 (Рис. 16):
Рис. 16.
- найти приближенное значение х точки пересечения графика функции с осью абсцисс (х≈1,4);
- найти приближенное решение уравнения с точностью до 0,001 методом Подбор параметра (х≈1,438).
4. Итог урока.
Слайд 12 Проверка результатов самостоятельной работы.
Слайд 13 Повторение графического способа решения уравнения вида f(x)=0.
Слайд 14 Повторение графического способа решения уравнения вида f(x)=g(x).
5. Домашнее задание.
Используя средства построения диаграмм в Excel и метод Подбор параметра, определите корни уравнения х 2 -5х+2=0 с точностью до 0,01.
Решение системы уравнений графическим методом средствами MS Excel
Оборудование урока: компьютеры, мультимедиа проектор.
Программное обеспечение: Windows XP, пакет программ MS Office 2003.
Тема нашего урока тесно связана с математикой разделы “Графики функций” и “Решение систем уравнений”. Поэтому нам понадобятся ранее полученные навыки. Но мы постараемся упростить нашу задачу с помощью применения современных вычислительных средств.
Запишите в тетради тему урока и укажите дату.
Назовите мне кого из класса сегодня нет.
Давайте вспомним, что такое уравнение, и как его можно решить графически.
Назовите, пожалуйста, что в математике называют уравнением, решением уравнения и системой уравнений.
(Учащиеся приводят определения)
Уравнение – это математическое выражение, содержащее неизвестную величину (переменную) и 0 с правой стороны от знака =.
Система уравнений – несколько связанных уравнений, имеющих одинаковые обозначения неизвестных величин (переменных).
Решением уравнения – называют такое значение неизвестной величины, при подстановке которого левая часть выражения принимает значение 0. И мы получаем верное равенство.
Но, с другой стороны, подобное выражение можно представить как функцию с зависимой и независимой величинами. Если мы слева от знака = поставим Y, а справа заданное выражение. Y – зависимая величина, Х – независимая величина. В этом случае Решением уравнения является точка пересечения графика функции с осью ОХ.
Для решения уравнения графическим методом необходимо рассчитать значения функции в ключевых точках с координатой Х (Х меняется в диапазоне допустимых значений), нанести эти точки на систему координат, построить график функции и определить координаты точки пересечения графика с осью ОХ.
Это достаточно сложная задача. Нужно достаточно много вычислений и аккуратное построение графика функции. Также мы заранее не можем сказать, из какого диапазона чисел необходимо брать значения Х.
Но эту задачу может взять на себя ЭВМ.
Мы воспользуемся возможностями программы MS Excel.
Основная часть
Давайте разобьемся на 2 группы. Сильные ученики, которые уже хорошо владеют средствами MS Excel, попытаются самостоятельно разработать таблицу. А остальные ребята будут вместе со мной последовательно выполнять действия.
Сильные ученики пересаживаются за дальние компьютеры и самостоятельно разрабатывают таблицу для решения системы уравнений. Они должны получить примерно такую картинку на экране.
С остальными мы работаем в режиме “Делай как Я”. Я демонстрирую действия на экране проектора и комментирую, вы стараетесь выполнять эти действия у себя на ЭВМ.
И так. Мы запустили программу MS Excel.
Мы хотим разработать таблицу для решения системы уравнений:
Нам необходимо задать диапазон изменения величины Х и рассчитать соответствующее значение Y.
Сформируем начальные данные.
В ячейку A1 запишем – нач Х =. В ячейку D1 запишем – шаг Х =. В ячейках B1, E1 их соответствующие значения – (-2,5) и 0,15.
В ячейках C4, F4 запишем общий вид наших уравнений. В строке 5 сформируем заголовки будущих таблиц значений заданных функций.
Теперь в столбиках B, E мы должны сформировать значения для величины Х. А в столбиках C, E значения величин Y. У нас должна получиться вот такая картинка. Столбики со значением величины X мы должны сформировать так, чтобы было удобно менять начальное его значение и шаг X, которые мы создали в заголовке.
Формулы, которые нам нужно ввести приведены на рисунке.
Заметьте, что большинство формул повторяются, и их можно ввести методом копирования.
Заполните, пожалуйста, в каждой таблице 20-25 строчек.
Символ $ в формуле обозначает, что данный адрес ячейки является абсолютным и он не будет изменяться при копировании формулы.
Проверьте, чтобы ваши расчётные данные совпадали с рисунком 2.
Нам осталось красиво оформить таблицы. Для этого нужно указать, какие границы отображать в ячейках расположения расчётных таблиц. Выделите их указателем мышки и задайте режим “Все границы”.
Теперь нам необходимо построить графики заданных функций. Для этого воспользуемся инструментом “Диаграммы”.
Выберем тип диаграммы Точечная-Сглаженная и на следующем экране укажем необходимые нам диапазоны данных, как указано на рисунке. Незабудем указать название для каждого графика. Легенду расположим снизу. А саму диаграмму “На текущем листе”, поместив её справа от расчётных таблиц.
Если вы всё сделали правильно, то у вас на экране должна получиться вот такая картинка.
У кого не получилось, давайте вместе разберёмся в ошибках и добъёмся требуемого результата.
Теперь изменяя значения в ячейках B1, D1 можно смещать графики функций вдоль оси ОХ и изменять их масштаб.
Мы видим, что одно из решений нашей системы уравнений равно -1,5.
Изменяя начальное значение Х, найдите на графике второе решение системы уравнений.
Сколько у вас получилось?
Великолепно. У нас получилось. Мы легко решили такую сложную систему уравнений.
Но можно немного изменить нашу таблицу и усовершенствовать для решения множества подобных систем уравнений или для исследования графиков заданных функций.
Для этого нужно внести изменения в таблицу и расчётные формулы.
Можно сделать следующим образом, как показано на рисунке. Формулы в ячейках показаны на следующем рисунке.
Самостоятельно внесите все необходимые изменения.
Попробуйте изменять коофициенты A, B, C, D и посмотрите, как меняется форма и положение графиков соответствующих формул.
Заключительный этап урока
Ребята, как вы думаете, что удобней самостоятельно строить график функции на бумаге или поручить эту задачу ЭВМ?
А что легче для вас?
Конечно же, на данном этапе вам удобней самостоятельно на бумаге построить график функции. Но в конце урока мы получили универсальную таблицу, которая позволяет решать множество подобных заданий.
Мы ещё раз убедились, что компьютер это мощный инструмент, который позволяет не только приятно проводить время за играми, но и решать серьёзные задачи.
Надеюсь, что вам понравилось сегодняшняя работа. И вы Довольны достигнутыми результатами.
Практическая работа «Графический метод решения уравнений в Excel»
Построим таблицу значений функции. Заполним столбец x значениями от -10 до 10. Значения y будем вычислять по формуле: =10*SIN(A2)-2*A2*A2+5 (формула для ячейки B2).
Построив график, найдем точки пересечения графика с осью OX. Это и есть приближенное решение.
Приближенное решение уравнения: -0.5 и 2.5.
Просмотр содержимого документа
«Практическая работа «Графический метод решения уравнений в Excel»»
Графический метод решения уравнений.
Найти графическим методом корень уравнения 10sin(x)-2x 2 +5=0.
Построим таблицу значений функции. Заполним столбец x значениями от -10 до 10. Значения y будем вычислять по формуле: =10*SIN(A2)-2*A2*A2+5 (формула для ячейки B2).
Построив график, найдем точки пересечения графика с осью OX. Это и есть приближенное решение.
Приближенное решение уравнения: -0.5 и 2.5.
Исследование физических моделей
Рассмотрим процесс решения задачи на конкретном примере: Тело брошено с некоторой высоты с начальной скоростью, направленной под углом к горизонту. Определить угол, при котором дальность полета будет максимальной.
Содержательная постановка задачи. В процессе тренировок теннисистов используются автоматы по бросанию мячика в определенное место площадки. Необходимо задать автомату необходимую скорость и угол бросания мячика для попадания в мишень определенного размера, находящуюся на известном расстоянии.
1) Описательная модель. Сначала построим качественную описательную модель процесса движения тела с использованием физических объектов, понятий и законов, то есть в данном случае идеализированную модель движения объекта. Из условия задачи можно сформулировать следующие основные предположения:
- тело мало по сравнению с Землей, поэтому его можно считать материальной точкой;
- изменение высоты тела не велико, поэтому ускорение свободного падения считать постоянной величиной g = 9,8 м/с 2 и движение по оси OY можно считать равноускоренным;
- скорость движения мала, поэтому сопротивлением воздуха можно пренебречь.
2) Формальная модель. Из курса физики известно, что описанное выше движение является равноускоренным. Координаты тела в любой момент времени можно найти по формулам: Для формализации модели используем известные из курса физики формулы равномерного и равноускоренного движения. При заданных начальной скорости и и угле бросания а значения координат дальности полета х и высоты у от времени можно описать следующими формулами: 
или 
или 
3) Компьютерная модель. Преобразуем формальную модель в компьютерную с использованием электронных таблиц. Выделим ячейки для ввода начальных данных: нач. скорость, нач. высота, угол. Построим таблицу для вычисления координат x и y. Координата x: =$B$1*COS($B$3*3,14/180)*A6 . Координата y: =$B$2+$B$1*SIN($B$3*3,14/180)*A6-9,8*A6*A6/2. Визуализируем модель построив график движения тела (зависимость y от x). 4) Исследуем модель и определим искомый угол. 5) Проанализируем полученные результаты. 