Решение уравнений в табличном процессоре ms excel
Нынешнему поколению учащихся предстоит жить и работать в третьем тысячелетии, когда новые технологии на производстве и в обществе получат своё дальнейшее развитие, поэтому развитие логичного, системного, алгоритмического стиля мышления становится одним из основных направлений в обучении. Однако, при изучении отдельных тем на уроках математики в старших классах, при решении некоторых задач, громоздкие вычисления как, например, при решении уравнений методом деления отрезка пополам или методом последовательных приближений, затмевают существо математической задачи, не дают увидеть красоту, рациональность применяемого метода решения.
В данной статье я представила те задачи, решение которых с помощью MS EXCEL позволяет получить наглядное, доступное для понимания учащимися решение, показать его логику, рациональность. Попутно учащиеся получают устойчивые навыки работы с программой.
Вложение | Размер |
---|---|
statya.doc | 143.5 КБ |
prilozhenie1.ppt | 921 КБ |
prilozhenie2.ppt | 321.5 КБ |
prilozhenie3.ppt | 1.16 МБ |
Варианты решений
Любое уравнение может считаться решенным только тогда, когда будут отысканы его корни. В программе Excel существует несколько вариантов поиска корней. Давайте рассмотрим каждый из них.
Способ 2: подбор параметров
Второй известный способ решения системы уравнений в Экселе – это применение метода подбора параметров. Суть данного метода заключается в поиске от обратного. То есть, основываясь на известном результате, мы производим поиск неизвестного аргумента. Давайте для примера используем квадратное уравнение
-
Принимаем значение x за равное 0. Высчитываем соответствующее для него значение f(x), применив следующую формулу:
Этот результат также можно проверить, подставив данное значение в решаемое выражение вместо значения x.
Способ 1: матричный метод
Самый распространенный способ решения системы линейных уравнений инструментами Excel – это применение матричного метода. Он заключается в построении матрицы из коэффициентов выражений, а затем в создании обратной матрицы. Попробуем использовать данный метод для решения следующей системы уравнений:
- Заполняем матрицу числами, которые являются коэффициентами уравнения. Данные числа должны располагаться последовательно по порядку с учетом расположения каждого корня, которому они соответствуют. Если в каком-то выражении один из корней отсутствует, то в этом случае коэффициент считается равным нулю. Если коэффициент не обозначен в уравнении, но соответствующий корень имеется, то считается, что коэффициент равен 1. Обозначаем полученную таблицу, как вектор A.
Аргумент «Массив» — это, собственно, адрес исходной таблицы.
Способ 4: метод Гаусса
Решить систему уравнений можно также, применив метод Гаусса. Для примера возьмем более простую систему уравнений из трех неизвестных:
-
Опять последовательно записываем коэффициенты в таблицу A, а свободные члены, расположенные после знака «равно» — в таблицу B. Но на этот раз сблизим обе таблицы, так как это понадобится нам для работы в дальнейшем. Важным условием является то, чтобы в первой ячейке матрицы A значение было отличным от нуля. В обратном случае следует переставить строки местами.
Если вы расположили матрицы по-другому, то и адреса ячеек формулы у вас будут иметь другое значение, но вы сможете высчитать их, сопоставив с теми формулами и изображениями, которые приводятся здесь.
Как видим, в Экселе систему уравнений можно решить целым рядом способов, каждый из которых имеет собственные преимущества и недостатки. Но все эти методы можно условно разделить на две большие группы: матричные и с применением инструмента подбора параметров. В некоторых случаях не всегда матричные методы подходят для решения задачи. В частности тогда, когда определитель матрицы равен нулю. В остальных же случаях пользователь сам волен решать, какой вариант он считает более удобным для себя.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
Еще статьи по данной теме:
Способ 2 — подбор параметров.
Ничего, что у квадратного уравнения 2 корня, а вы нашли только один и тот с погрешностью?
Вообще решать квадратное уравнение подбором при наличие простой формулы не очень адекватно.
Другое дело, если уравнение «в лоб» не решается, например, какой-нибудь седьмой степени или ещё с какой функцией. Но и тут проще родственный графический метод. Строим график функции от минус бесконеч… в реальности — от минус миллиарда до плюс миллиарда с большим шагом. Смотрим, где пересечения с осью ОХ и, если требуется точнее, уменьшаем шаг на этих участках.
Графический метод позволяет решить и систему из двух нелинейных уравнений. Надо построить два графика y(x) и найти их пересечения.
Здравствуйте, Борис. Спасибо за замечание. Нашей целью было охватить все возможные способы решений уравнений в Экселе. Каждый из них имеет свои преимущества и, безусловно, недостатки. Читатель, ознакомившись с данной статьей, должен сам определить, какой способ подходит именно ему.
Можно ли решить вторым способом (подбором параметров) СИСТЕМУ уравнений?
Еще можно решить с помощью надстройки «Поиск решений»…
я задал вопрос как решить одно кубическое уравнение полное а не систему уравнений как квадратное уравнение так его легче по формуле решить а не фигнёй хитроумной заниматься а матрицы это имет значение и оно будет эффективно но меня не это интересует а уравнения вида ax^3+bx^2+cx+d=0 b и только
Как решить в excel систему двух нелинейных уравнений вида exp(x*y)
Самый простой способ, о котором нигде не написано. Всего одна функция. А именно ЛИНЕЙН. Правда, если мы, при этом, уверенны, что система имеет ровно одно решение.
Можно ли методом Крамера решить матрицу размерностью 5 на 4?
Задайте вопрос или оставьте свое мнение Отменить комментарий
Введём исходные данные для рассмотренной задачи бросания мяча, как показано на рис. 9.8. Для того чтобы формулы выглядели более привычно, дадим ячейкам Bl, В2 и В3 имена S, Н и v (их можно ввести в левом верхнем углу таблицы).
В столбце А заполним ряд значений углов от 0 o до 85 o с шагом 5 o . Для этого введём два первых значения, выделим эти ячейки и «растянем» за маркер заполнения (квадратик в правом нижнем углу выделенной части, рис. 9.9).
Рис. 9.8
Рис. 9.9
Добавим столбцы, в которых для каждого угла будут вычисляться его значение в радианах (с помощью стандартной функции RADIANS, в русской версии Excel — РАДИАНЫ), время полёта, координата у и значение функции f(а) (рис. 9.10).
Рис. 9.10
Обратите внимание, что в формулах мы используем имена ячеек S, Н и v. Это абсолютные ссылки, не меняющиеся при копировании; например, вместо имени S можно было бы написать адрес $В$1, но это было бы менее понятно. Эти формулы можно просто «растянуть» (скопировать) вниз за маркер заполнения.
Теперь построим график функции f(а). Сначала нужно выделить данные в столбцах А и Е, это можно сделать, если удерживать нажатой клавишу Ctrl. Затем строим диаграмму типа Диаграмма XY (в Excel — диаграмма Точечная) — рис. 9.11. График функции пересекает ось ОХ в двух точках, т. е. уравнение f(а) = 0 имеет два решения, одно около 35 o , второе — около 65 o .
Рис. 9.11
Теперь уточним решение, используя возможности табличного процессора, в котором реализован один из приближённых методов решения уравнений. Для этого нужно знать начальное приближение α0 — значение неизвестной величины, достаточно близкое к решению. По графику мы определили, что первый раз график пересекает ось ОХ для значения угла около 35 o , поэтому можно взять α0 = 35 o . Запишем это значение в свободную ячейку, например в Н2, и добавим недостающие формулы так, чтобы получить значение функции f(а) в ячейке L2 (рис. 9.12).
Рис. 9.12
Задача подбора параметра формулируется так: «установить в ячейке . значение . изменяя значение ячейки . ». Например, в нашем случае нужно установить в ячейке L2 значение 0, изменяя Н2. Ячейка L2 называется целевой, потому что наша цель — получить в ней определённое значение (ноль). Ячейка Н2 — это изменяемая ячейка. В главном меню выбираем пункт Сервис, Подбор параметра и вводим эти данные (рис. 9.13).
Рис. 9.13
После нажатия на кнопку ОК найденное решение уравнения будет записано в ячейку Н2.
Как же найти второе решение? Для этого нужно выбрать другое начальное приближение, например α0 = 70 o , в остальном порядок действий не меняется. Сделайте это самостоятельно.
Проверьте, что будет происходить при изменении начальной скорости до 10 м/с и до 20 м/с. Попробуйте объяснить эти результаты с точки зрения физики.
Следующая страница Вопросы и задания
Cкачать материалы урока
Введём исходные данные для рассмотренной задачи бросания мяча, как показано на рис. 9.8. Для того чтобы формулы выглядели более привычно, дадим ячейкам Bl, В2 и В3 имена S, Н и v (их можно ввести в левом верхнем углу таблицы).
В столбце А заполним ряд значений углов от 0 o до 85 o с шагом 5 o . Для этого введём два первых значения, выделим эти ячейки и «растянем» за маркер заполнения (квадратик в правом нижнем углу выделенной части, рис. 9.9).
Рис. 9.8
Рис. 9.9
Добавим столбцы, в которых для каждого угла будут вычисляться его значение в радианах (с помощью стандартной функции RADIANS, в русской версии Excel — РАДИАНЫ), время полёта, координата у и значение функции f(а) (рис. 9.10).
Рис. 9.10
Обратите внимание, что в формулах мы используем имена ячеек S, Н и v. Это абсолютные ссылки, не меняющиеся при копировании; например, вместо имени S можно было бы написать адрес $В$1, но это было бы менее понятно. Эти формулы можно просто «растянуть» (скопировать) вниз за маркер заполнения.
Теперь построим график функции f(а). Сначала нужно выделить данные в столбцах А и Е, это можно сделать, если удерживать нажатой клавишу Ctrl. Затем строим диаграмму типа Диаграмма XY (в Excel — диаграмма Точечная) — рис. 9.11. График функции пересекает ось ОХ в двух точках, т. е. уравнение f(а) = 0 имеет два решения, одно около 35 o , второе — около 65 o .
Рис. 9.11
Теперь уточним решение, используя возможности табличного процессора, в котором реализован один из приближённых методов решения уравнений. Для этого нужно знать начальное приближение α0 — значение неизвестной величины, достаточно близкое к решению. По графику мы определили, что первый раз график пересекает ось ОХ для значения угла около 35 o , поэтому можно взять α0 = 35 o . Запишем это значение в свободную ячейку, например в Н2, и добавим недостающие формулы так, чтобы получить значение функции f(а) в ячейке L2 (рис. 9.12).
Рис. 9.12
Задача подбора параметра формулируется так: «установить в ячейке . значение . изменяя значение ячейки . ». Например, в нашем случае нужно установить в ячейке L2 значение 0, изменяя Н2. Ячейка L2 называется целевой, потому что наша цель — получить в ней определённое значение (ноль). Ячейка Н2 — это изменяемая ячейка. В главном меню выбираем пункт Сервис, Подбор параметра и вводим эти данные (рис. 9.13).
Рис. 9.13
После нажатия на кнопку ОК найденное решение уравнения будет записано в ячейку Н2.
Как же найти второе решение? Для этого нужно выбрать другое начальное приближение, например α0 = 70 o , в остальном порядок действий не меняется. Сделайте это самостоятельно.
Проверьте, что будет происходить при изменении начальной скорости до 10 м/с и до 20 м/с. Попробуйте объяснить эти результаты с точки зрения физики.
Следующая страница Вопросы и задания
Cкачать материалы урока
Введём исходные данные для рассмотренной задачи бросания мяча, как показано на рис. 9.8. Для того чтобы формулы выглядели более привычно, дадим ячейкам Bl, В2 и В3 имена S, Н и v (их можно ввести в левом верхнем углу таблицы).
В столбце А заполним ряд значений углов от 0 o до 85 o с шагом 5 o . Для этого введём два первых значения, выделим эти ячейки и «растянем» за маркер заполнения (квадратик в правом нижнем углу выделенной части, рис. 9.9).
Рис. 9.8
Рис. 9.9
Добавим столбцы, в которых для каждого угла будут вычисляться его значение в радианах (с помощью стандартной функции RADIANS, в русской версии Excel — РАДИАНЫ), время полёта, координата у и значение функции f(а) (рис. 9.10).
Рис. 9.10
Обратите внимание, что в формулах мы используем имена ячеек S, Н и v. Это абсолютные ссылки, не меняющиеся при копировании; например, вместо имени S можно было бы написать адрес $В$1, но это было бы менее понятно. Эти формулы можно просто «растянуть» (скопировать) вниз за маркер заполнения.
Теперь построим график функции f(а). Сначала нужно выделить данные в столбцах А и Е, это можно сделать, если удерживать нажатой клавишу Ctrl. Затем строим диаграмму типа Диаграмма XY (в Excel — диаграмма Точечная) — рис. 9.11. График функции пересекает ось ОХ в двух точках, т. е. уравнение f(а) = 0 имеет два решения, одно около 35 o , второе — около 65 o .
Рис. 9.11
Теперь уточним решение, используя возможности табличного процессора, в котором реализован один из приближённых методов решения уравнений. Для этого нужно знать начальное приближение α0 — значение неизвестной величины, достаточно близкое к решению. По графику мы определили, что первый раз график пересекает ось ОХ для значения угла около 35 o , поэтому можно взять α0 = 35 o . Запишем это значение в свободную ячейку, например в Н2, и добавим недостающие формулы так, чтобы получить значение функции f(а) в ячейке L2 (рис. 9.12).
Рис. 9.12
Задача подбора параметра формулируется так: «установить в ячейке . значение . изменяя значение ячейки . ». Например, в нашем случае нужно установить в ячейке L2 значение 0, изменяя Н2. Ячейка L2 называется целевой, потому что наша цель — получить в ней определённое значение (ноль). Ячейка Н2 — это изменяемая ячейка. В главном меню выбираем пункт Сервис, Подбор параметра и вводим эти данные (рис. 9.13).
Рис. 9.13
После нажатия на кнопку ОК найденное решение уравнения будет записано в ячейку Н2.
Как же найти второе решение? Для этого нужно выбрать другое начальное приближение, например α0 = 70 o , в остальном порядок действий не меняется. Сделайте это самостоятельно.
Проверьте, что будет происходить при изменении начальной скорости до 10 м/с и до 20 м/с. Попробуйте объяснить эти результаты с точки зрения физики.
Следующая страница Вопросы и задания
Cкачать материалы урока
Предварительный просмотр:
Одна из наиболее актуальных проблем компьютерного обучения – проблема отбора и использования педагогически целесообразных обучающих программ.
При изучении отдельных тем и решении некоторых задач на уроках математики в старших классах громоздкие вычисления как, например, при решении уравнений методом деления отрезка пополам или методом последовательных приближений, затмевают существо математической задачи, не дают увидеть красоту, рациональность применяемого метода решения.
В данной статье я представила те задачи, решение которых с помощью MS EXCEL позволяет получить наглядное, доступное для понимания учащимися решение, показать его логику, рациональность. Попутно учащиеся получают устойчивые навыки работы с программой.
- Нахождение корней уравнения с помощью подбора параметра
Пусть известно, что в штате больницы состоит 6 санитарок, 8 медсестер, 10 врачей, 3 заведующих отделениями, главный врач, заведующий аптекой, заведующая хозяйством и заведующий больницей. Общий месячный фонд зарплаты составляет 1000 000 условных единиц. Необходимо определить, какими должны быть оклады сотрудников больницы.
Решение такой задачи можно искать методом перебора. Однако в лучшем случае на это уходит много времени. Можно предложить другой способ решения. В EXCEL он реализован как поиск значения параметра формулы, удовлетворяющего ее конкретному значению.
Построим модель решения этой задачи. За основу возьмем оклад санитарки, а остальные оклады будем вычислять, исходя из него: во столько-то раз или на столько-то больше. Говоря математическим языком, каждый оклад является линейной функцией от оклада санитарки: A i *С+В i , где С - оклад санитарки; А i и В i - коэффициенты, которые для каждой должности определяют следующим образом:
- медсестра получает в 1,5 раза больше санитарки (А 2 =1,5; В 2 =0);
- врач - в 3 раза больше санитарки (А 3 =3; В 3 =0);
- заведующий отделением - на 30 y.e. больше, чем врач (А 4 =3; B4=30);
- заведующий аптекой - в 2 раза больше санитарки (А 5 =2; В 5 =0);
- заведующий хозяйством - на 40 y.e. больше медсестры (А 6 =1,5; В 6 =40);
- заведующий больницей - на 20 y.e. больше главного врача (А 8 =4; В 8 =20);
- главный врач - в 4 раза больше санитарки (А 7 =4; В 7 =0);
Зная количество человек на каждой должности, нашу модель можно
записать как уравнение: N 1 *(A 1 *C+B 1 )+N 2 *(A 2 *C+B 2 )+. +N 8 *(A 8 *C+B 8 ) = 1000000,
где N 1 - число санитарок, N 2 - число медсестер и т.д.
В этом уравнении нам известны A 1 . A 8 , B 1 . B 8 и N 1 . N 8 , а С неизвестно. Анализ уравнения показывает, что задача вычисления заработной платы свелась к решению линейного уравнения относительно С. Предположим, что зарплата у санитарки 150,00 y.e.
Введите исходные данные в рабочий лист электронной таблицы, как показано ниже.
Умение решать системы уравнений часто может принести пользу не только в учебе, но и на практике. В то же время, далеко не каждый пользователь ПК знает, что в Экселе существует собственные варианты решений линейных уравнений. Давайте узнаем, как с применением инструментария этого табличного процессора выполнить данную задачу различными способами.
Способ 3: метод Крамера
Теперь попробуем решить систему уравнений методом Крамера. Для примера возьмем все ту же систему, которую использовали в Способе 1:
-
Как и в первом способе, составляем матрицу A из коэффициентов уравнений и таблицу B из значений, которые стоят после знака «равно».
Таким образом, как и у функции МОБР, единственным аргументом выступает ссылка на обрабатываемую таблицу.
Читайте также: