Как посчитать уравнение в excel

Рассмотрим задачу нахождения корня уравнения методом Ньютона с использованием циклических ссылок. Возьмем для примера квадратное уравнение: х 2 — 5х + 6=0, графическое представление которого приведено на рис. 8. Найти корень этого (и любого другого) уравнения можно, используя всего одну ячейку Excel.

Выберем произвольную ячейку, присвоим ей новое имя, скажем — Х, и введем в нее рекуррентную формулу, задающую вычисления по методу Ньютона:

• В ячейку Хнач (В4) заносим начальное приближение — 5.
• В ячейку Хтекущ (С4) записываем формулу:
  =ЕСЛИ(Хтекущ=0;Хнач; Хтекущ-(Хтекущ^2-5*Хтекущ+6)/(2*Хтекущ-5)).
• В ячейку D4 помещаем формулу, задающую вычисление значения функции в точке Хтекущ, что позволит следить за процессом решения.
• Заметьте, что на первом шаге вычислений в ячейку Хтекущ будет помещено начальное значение, а затем уже начнется счет по формуле на последующих шагах.
• Чтобы сменить начальное приближение, недостаточно изменить содержимое ячейки Хнач и запустить процесс вычислений. В этом случае вычисления будут продолжены, начиная с последнего вычисленного
  

  Рис. 9. Определение начальных установок

2.2. Подбор параметра

Возьмем в качестве примера все то же квадратное уравнение х 2 -5х+6=0. Для нахождения корней уравнения выполним следующие действия:

В ячейку С3 (рис. 10) введем формулу для вычисления значения функции,

Рис. 10. Окно диалога Подбор параметра

Вернемся к примеру. Опять возникает вопрос: как получить второй корень? Как и в предыдущем случае необходимо задать начальное приближение. Это можно сделать следующим образом (рис. 11,а):

• В ячейку Х (С2) вводим начальное приближение.
• В ячейку Х_i (С3) вводим формулу для вычисления очередного приближения к корню, т.е.
  =X-(X^2-5*X+6)/(2*X-5).
• В ячейку С4 поместим формулу, задающую вычисление значения функции, стоящей в левой части исходного уравнения, в точке Х_i.
• После этого выбираем команду Подбор параметра, где в качестве изменяемой ячейки принимаем ячейку С2. Результат вычислений изображен на рис. 11,б (в ячейке С2 — конечное значение, а в ячейке С3 — предыдущее).

Однако все это можно сделать и несколько проще. Для того чтобы найти второй корень, достаточно в качестве начального приближения (рис. 10) в ячейку C2 поместить константу 5 и после этого запустить процесс Подбор параметра.

2.3. Поиск решения

Команда Подбор параметра является удобной для решения задач поиска определенного целевого значения, зависящего от одного неизвестного параметра. Для более сложных задач следует использовать команду Поиск решения (Решатель), доступ к которой реализован через пункт меню Сервис/Поиск решения.

Задачи, которые можно решать с помощью Поиска решения, в общей постановке формулируются так:

• найти максимум целевой функции F(х₁, х₂, … , х_n);
• найти минимум целевой функции F(х₁, х₂, … , х_n);
• добиться того, чтобы целевая функция F(х₁, х₂, … , х_n) имела фиксированное значение: F(х₁, х₂, … , х_n) = a.

Функции G(х₁, х₂, … , х_n) называются ограничениями. Их можно задать как в виде равенств, так и неравенств. На регулируемые ячейки можно наложить дополнительные ограничения: неотрицательности и/или целочисленности, тогда искомое решение ищется в области положительных и/или целых чисел.

Выше для нахождения корней квадратного уравнения был применен метод Ньютона (п. 1.4) с использованием циклических ссылок (п. 2.1) и средство Подбор параметра (п. 2.2). Рассмотрим, как воспользоваться Поиском решения на примере того же квадратного уравнения.

После открытия диалога Поиск решения (рис. 12) необходимо выполнить следующие действия:

1. в поле Установить целевую ячейку ввести адрес ячейки, содержащей формулу для вычисления значений оптимизируемой функции, в нашем примере целевая ячейка — это С4, а формула в ней имеет вид: = C3^2 — 5*C3 + 6;
2. для максимизации значения целевой ячейки, установить переключатель максимальному значению в положение 8 , для минимизации используется переключатель минимальному значению, в нашем случае устанавливаем переключатель в положение значению и вводим значение 0;
3. в поле Изменяя ячейки ввести адреса изменяемых ячеек, т.е. аргументов целевой функции (С3), разделяя их знаком «;» (или щелкая мышью при нажатой клавише Сtrl на соответствующих ячейках), для автоматического поиска всех влияющих на решение ячеек используется кнопка Предположить;
4. в поле Ограничения с помощью кнопки Добавить ввести все ограничения, которым должен отвечать результат поиска: для нашего примера ограничений задавать не нужно;
5. для запуска процесса поиска решения нажать кнопку Выполнить.

Опции, управляющие работой Поиска решения, задаваемые в окне Параметры (окно появляется, если нажать на кнопку Параметры окна Поиск решения), следующие (рис. 14):

Сохранить модель поиска решения можно следующими способами:

1. при сохранении книги Excel после поиска решения все значения, введенные в окнах диалога Поиск решения, сохраняются вместе с данными рабочего листа. С каждым рабочим листом в рабочей книге можно сохранить один набор значений параметров Поиска решения;
2. если в пределах одного рабочего листа Excel необходимо рассмотреть несколько моделей оптимизации (например найти максимум и минимум одной функции, или максимальные значения нескольких функций), то удобнее сохранить эти модели, используя кнопку Параметры/Сохранить модель окна Поиск решения. Диапазон для сохраняемой модели содержит информацию о целевой ячейке, об изменяемых ячейках, о каждом из ограничений и все значения диалога Параметры. Выбор модели для решения конкретной оптимизационной задачи осуществляется с помощью кнопки Параметры/Загрузить модель диалога Поиск решения;
3. еще один способ сохранения параметров поиска — сохранение их в виде именованных сценариев. Для этого необходимо нажать на кнопку Сохранить сценарий диалогового окна Результаты поиска решений.

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

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

Исправляем ошибки: Нашли опечатку? Выделите ее мышкой и нажмите Ctrl+Enter

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

Так как правая часть диффура не подходит под табличный формат, то не получится подбирать частное решение по правой части как делали это в предыдущем примере. Воспользуется методом Лагранжа или как его еще называют вариация произвольной постоянной. Для начала найдем общее решение однородного уравнения $y»-2y’+y=0.$

Применения операционного исчисления VMath

Заполняем матрицу числами, которые являются коэффициентами уравнения. Данные числа должны располагаться последовательно по порядку с учетом расположения каждого корня, которому они соответствуют. Если в каком-то выражении один из корней отсутствует, то в этом случае коэффициент считается равным нулю. Если коэффициент не обозначен в уравнении, но соответствующий корень имеется, то считается, что коэффициент равен 1. Обозначаем полученную таблицу, как вектор A.

Для решения этой задачи необходимо записать выражения (формулы) для вычисления значений функций, стоящих слева в уравнениях системы (12). Отведем для примера под эти формулы интервал C7:C9. В ячейкуC7 введем формулу=A3*$B$7+B3*$B$8+C3*$B$9-D3 и скопируем ее в оставшиесяC8 иC9. В них появятся соответственно=A4*$B$7+B4*$B$8+C4*$B$9-D4 и=A5*$B$7+B5*$B$8+C5*$B$9-D5.

• В ячейку Хнач (В4) заносим начальное приближение — 5.
• В ячейку Хтекущ (С4) записываем формулу:
  =ЕСЛИ(Хтекущ=0;Хнач; Хтекущ-(Хтекущ^2-5*Хтекущ+6)/(2*Хтекущ-5)).
• В ячейку D4 помещаем формулу, задающую вычисление значения функции в точке Хтекущ, что позволит следить за процессом решения.
• Заметьте, что на первом шаге вычислений в ячейку Хтекущ будет помещено начальное значение, а затем уже начнется счет по формуле на последующих шагах.
• Чтобы сменить начальное приближение, недостаточно изменить содержимое ячейки Хнач и запустить процесс вычислений. В этом случае вычисления будут продолжены, начиная с последнего вычисленного
  

  Рис. 9. Определение начальных установок

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

Рассмотрим задачу нахождения корня уравнения методом Ньютона с использованием циклических ссылок. Возьмем для примера квадратное уравнение: х2 — 5х + 6=0, графическое представление которого приведено на рис. 8. Найти корень этого (и любого другого) уравнения можно, используя всего одну ячейку Excel.

Выберем произвольную ячейку, присвоим ей новое имя, скажем — Х, и введем в нее рекуррентную формулу, задающую вычисления по методу Ньютона:

В ячейку Хтекущ (С4) записываем формулу:
=ЕСЛИ(Хтекущ=0;Хнач; Хтекущ-(Хтекущ^2-5*Хтекущ+6)/(2*Хтекущ-5)).

В ячейку D4 помещаем формулу, задающую вычисление значения функции в точке Хтекущ, что позволит следить за процессом решения.

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

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

значения. Чтобы обнулить значение, хранящееся в ячейке Хтекущ, нужно заново записать туда формулу. Для этого достаточно для редактирования выбрать ячейку, содержащую формулу, дважды щелкнув мышью на ней (при этом содержимое ячейки отобразится в строке формул). Щелчок по кнопке (нажатие клавиши) Enter запустит вычисления с новым начальным приближением.

Возьмем в качестве примера все то же квадратное уравнение х2-5х+6=0. Для нахождения корней уравнения выполним следующие действия:

В ячейку С3 (рис. 10) введем формулу для вычисления значения функции,

стоящей в уравнении слева от знака равенства. В качестве аргумента используем ссылку на ячейку С2, т.е. =С2^2-5*C2+6.

В окне диалога Подбор параметра (рис. 10) в поле Установить в ячейке введем ссылку на ячейку с формулой, в поле Значение — ожидаемый результат, в поле Изменяя значения ячейки — ссылку на ячейку, в которой будет храниться значение подбираемого параметра (содержимое этой ячейки не может быть формулой).

После нажатия на кнопку Ok Excel выведет окно диалога Результат подбора параметра. Если подобранное значение необходимо сохранить, то нажмите на Оk, и результат будет сохранен в ячейке, заданной ранее в поле Изменяя значения ячейки. Для восстановления значения, которое было в ячейке С2 до использования команды Подбор параметра, нажмите кнопку Отмена.

Вернемся к примеру. Опять возникает вопрос: как получить второй корень? Как и в предыдущем случае необходимо задать начальное приближение. Это можно сделать следующим образом (рис. 11,а):

В ячейку Хi (С3) вводим формулу для вычисления очередного приближения к корню, т.е. =X-(X^2-5*X+6)/(2*X-5).

В ячейку С4 поместим формулу, задающую вычисление значения функции, стоящей в левой части исходного уравнения, в точке Хi.

После этого выбираем команду Подбор параметра, где в качестве изменяемой ячейки принимаем ячейку С2. Результат вычислений изображен на рис. 11,б (в ячейке С2 — конечное значение, а в ячейке С3 — предыдущее).

Однако все это можно сделать и несколько проще. Для того чтобы найти второй корень, достаточно в качестве начального приближения (рис. 10) в ячейку C2 поместить константу 5 и после этого запустить процесс Подбор параметра.

Команда Подбор параметра является удобной для решения задач поиска определенного целевого значения, зависящего от одного неизвестного параметра. Для более сложных задач следует использовать команду Поиск решения (Решатель), доступ к которой реализован через пункт меню Сервис/Поиск решения.

Задачи, которые можно решать с помощью Поиска решения, в общей постановке формулируются так:

Найти:
х1, х2, … , хn
такие, что:
F(х1, х2, … , хn) >
при ограничениях:
G(х1, х2, … , хn) > Value;

добиться того, чтобы целевая функция F(х1, х2, … , хn) имела фиксированное значение: F(х1, х2, … , хn) = a.

Функции G(х1, х2, … , хn) называются ограничениями. Их можно задать как в виде равенств, так и неравенств. На регулируемые ячейки можно наложить дополнительные ограничения: неотрицательности и/или целочисленности, тогда искомое решение ищется в области положительных и/или целых чисел.

Выше для нахождения корней квадратного уравнения был применен метод Ньютона с использованием циклических ссылок (п. 1) и средство Подбор параметра (п. 2).

Приближённое отделение корней можно провести и графически. Для этого уравнение (1) заменяют равносильным ему уравнением р(х) = ф(х), где функции р(х) и ф(х] более простые, чем функция f(x). Тогда, построив графики функций у = р(х) и у = ф(х), искомые корни получим, как абсциссы точек пересечения этих графиков

Решение уравнений средствами Excel — Студопедия

Если не получается решить свою задачу, то присылайте её к нам. Мы предоставим подробное решение. Вы сможете ознакомиться с ходом вычисления и почерпнуть информацию. Это поможет своевременно получить зачёт у преподавателя!

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

​«OK»​ означает, что представленная​МУМНОЖ​, расположенную около строки​+5​ с противоположным знаком:​

​ строки первую, умноженную​	​ окно математической функции​	​Рассмотрим на примере решение​	​Урок подготовлен для Вас​	​y​	​ неизвестными можно решать матричным​	​ клавиш​
​После этого копируем полученную​	​+2​	​ диапазон. После этого​	​ и таблицу​	​.​
​ система уравнений решена​	​. Данный оператор имеет​	​ формул.​	​x3​	​ f (х) =​	​ на отношение первых​	​ МУМНОЖ. Первый диапазон​

​ квадратного уравнения х2​ командой сайта office-guru.ru​,​​ методом только тогда,​​Ctrl+Shift+Enter​

​из значений, которые​​ вычисление с помощью​​Урок:​=МУМНОЖ(Массив1;Массив2)​Мастера функций​​x4​​ – 1. М​​ первого уравнения.​​ Второй – матрица​ 2 = 0.​Перевела: Ольга Гелих​12​

​ матрицы системы отличен​​Теперь смотрим на числа,​​ ниже.​x3​​. Данная функция выводит​​ стоят после знака​​ подбора параметра. Об​​Обратная матрица в Excel​​Выделяем диапазон, в нашем​​. Переходим в категорию​=213​​ = 11.​​Копируем введенную формулу на​

​Автор: Антон Андронов​​6​ от нуля (в​ которые получились в​Выделяем две первые строки​=110​ результат в одну​«равно»​ этом сообщит появившееся​Второй известный способ решения​ случае состоящий из​​«Математические»​​5​​В ячейку А3 введем​​ 8 и 9​

​Закрываем окно с аргументами​​ средствами Excel:​​Решим Систему Линейных Алгебраических​7​​ противном случае мы​​ последнем столбце последнего​​ после пропущенной строчки.​​7​​ ячейку, а не​​.​​ информационное окно. В​​ системы уравнений в​ четырех ячеек. Далее​​. В представившемся списке​​x1​

​Далее делаем ещё четыре​ нем следует нажать​
​ Экселе – это​
​ опять запускаем​

Команда Подбор параметра является удобной для решения задач поиска определенного целевого значения, зависящего от одного неизвестного параметра. Для более сложных задач следует использовать команду Поиск решения (Решатель), доступ к которой реализован через пункт меню Сервис/Поиск решения.

Как посчитать уравнение в excel

​ нулю. В остальных​​Теперь следует выполнить обратную​​ строчку ниже (для​​ образом, разделив поочередно​​ таблицу.​x4​​«Работа с данными»​​ вышеуказанные действия проведены,​

​ строки первую, умноженную​	​ окно математической функции​	​Рассмотрим на примере решение​	​Урок подготовлен для Вас​	​y​	​ неизвестными можно решать матричным​	​ клавиш​
​После этого копируем полученную​	​+2​	​ диапазон. После этого​	​ и таблицу​	​.​
​ система уравнений решена​	​. Данный оператор имеет​	​ формул.​	​x3​	​ f (х) =​	​ на отношение первых​	​ МУМНОЖ. Первый диапазон​

Решение уравнений методом хорд в excel

Аналитическое решение некоторых уравнений, содержащих, например тригонометрические функции может быть получено лишь для единичных частных случаев. Так, например, нет способа решить аналитически даже такое простое уравнение, как cos x=x

Численные методы позволяют найти приближенное значение корня с любой заданной точностью.

Приближённое нахождение обычно состоит из двух этапов:

1) отделение корней, т.е. установление возможно точных промежутков [a,b], в которых содержится только один корень уравнения;

2) уточнение приближённых корней, т.е. доведение их до заданной степени точности.

Мы будем рассматривать решения уравнений вида f(x)=0. Функция f(x) определена и непрерывна на отрезке [а.Ь]. Значение х 0 называется корнем уравнения если f(х 0 )=0

Для отделения корней будем исходить из следующих положений:

• Если f(a)* f(b] a, b существует, по крайней мере, один корень
• Если функция y = f(x) непрерывна на отрезке [a, b], и f(a)*f(b) и f ‘(x) на интервале (a, b) сохраняет знак, то внутри отрезка [а, b] существует единственный корень уравнения

Приближённое отделение корней можно провести и графически. Для этого уравнение (1) заменяют равносильным ему уравнением р(х) = ф(х), где функции р(х) и ф(х] более простые, чем функция f(x). Тогда, построив графики функций у = р(х) и у = ф(х), искомые корни получим, как абсциссы точек пересечения этих графиков

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

ПРИМЕР : Определим графически корень уравнения . Пусть f1(х) = х , a и построим графики этих функций. (График). Корень находится на интервале от 1 до 2. Здесь же уточним значение корня с точностью 0,001(на доске шапка таблицы)

Точки графика функции на концах интервала соединяются хордой. Точка пересечения хорды и оси Ох (х*) и используется в качестве пробной. Далее рассуждаем так же, как и в предыдущем методе: если f(x a ) и f(х*) одного знака на интервале , нижняя граница переносится в точку х*; в противном случае – переносим верхнюю границу. Далее проводим новую хорду и т.д.

Осталось только уточнить, как найти х*. По сути, задача сводится к следующей: через 2 точки с неизвестными координатами (х 1 , у 1 ) и (х 2 , у 2 ) проведена прямая; найти точку пересечения этой прямой и оси Ох.

В точке пересечения этой прямой и оси Ох у=0, а х=х*, то есть

, откуда

процесс вычисления приближённых значений продолжается до тех пор, пока для двух последовательных приближений корня х„ и х п _1 не будет выполняться условие abs(xn-x n-1 ) е – заданная точность

Алгоритм различается только в пункте вычисления серединной точки- пересечения хорды с осью абсцисс и условия останова (разность между двумя соседними точками пересечения)

Уравнения для самостоятельного решения: (отрезок в excel ищем самостоятельно)

Метод хорд [7] заключается в замене кривой y = f(x) отрезком прямой, проходящей через точки (a, f(a)) и (b, f(b)) (см. рис. 2.6). Абсцисса точки пересечения прямой с осью OX принимается за очередное приближение.

Чтобы получить расчетную формулу метода хорд, запишем уравнение прямой, проходящей через точки (a, f(a)) и (b, f(b)) и, приравнивая y нулю, найдем x:

3) Если f(x_k)= 0 (корень найден), то переходим к 5).

Замечание.Действия третьего пункта аналогичны действиям метода половинного деления. Однако в методе хорд на каждом шаге может сдвигаться один и тот же конец отрезка (правый или левый), если график функции в окрестности корня выпуклый вверх (рис. 2.6, a)) или вогнутый вниз (рис. 2.6, b)). Поэтому в критерии сходимости используется разность соседних приближений.

Пример 2.6. Применим метод хорд к уравнению sin 5x + x 2 – 1 = 0 и отрезку [0,2; 0,3] для определения корня с точностью до ε = 0,001.

Решение. Проведем расчеты в программе Excel:

1) В ячейки A1:H1 запишем заголовки столбцов как в табл. 2.6;

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

Приведем программу, которая реализует метод хорд на языке C++:

Как видим, результат совпадает с предыдущими расчетами.

Дата добавления: 2015-04-25 ; Просмотров: 3757 ; Нарушение авторских прав? ;

Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет

Тема урока: «Решение нелинейных уравнений в MS Excel».

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

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

Вид урока: сдвоенный, продолжительность – 1,5 часа.

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

• Компьютеры с OS MS Windows;
• Программа Microsoft Excel;
• Программа Turbo Pascal;
• Презентация по теме, выполненная в программе Power Point;
• Карточки с заданиями для самостоятельной работы.

В данном уроке особое внимание уделено визуальному представлению информации – в ходе урока с помощью проектора демонстрируются слайды, подготовленные в пакете презентационной графики Microsoft Power Point.

II. Актуализация знаний, умений и навыков учащихся – повторение материала прошлого урока по теме «Решение нелинейных уравнений методом половинного деления»

Учащиеся повторяют указанный метод с помощью слайдов, подготовленных в пакете презентационной графики Microsoft Power Point метод половинного деления

• Всегда ли существуют формулы для «точного» решения уравнений?
• Сформулируйте основное условие существования корня на заданном отрезке.
• Запишите уравнение, позволяющее определить координаты середины отрезка.
• Почему алгоритм решения этой задачи можно назвать циклическим?
• Какое действие в алгоритме повторяется?
• Определите условие, при котором действие алгоритма должно остановиться.

III. Изобразите блок-схему алгоритма. блок-схема

IV. Практическое задание с использованием программы на языке Turbo Pascal (Учащимся разрешено использовать программу, составленную на предыдущем уроке. Было решено уравнение y = x 3 – cos(x)) метод половинного деления TP

Найти решение уравнения y = x 3 – cos(x) на отрезке [–1;1], при = 0,0001

Найти решение уравнения y = x 3 – cos(x) на отрезке [–1;1], определить на каком шаге циклического алгоритма будет получено решение.

V. Изучение нового материала «Решение нелинейных уравнений методом хорд»

VI. Объяснить алгоритм решения уравнения f(x)=0 на отрезке [а;в] методом хорд с помощью слайдов, подготовленных в пакете презентационной графики Microsoft Power Point. метод хорд

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

Решение уравнений методом хорд в excel

Далее в полученном уравнении разделяем переменные $t$ и $x$ по разные стороны знака равенства. Для этого выносим за скобку $t’x$ $t’x(2+t)=-t.$ Делим на $t$ обе части уравнения $t’x\frac=-1.$ Представляем производную $t’ = \frac$ и переносим $dx$ и $x$ в правую часть равенства $\fracdt = -\frac.$