Как решить нелинейное уравнение в excel

Как решить нелинейное уравнение в excel

Цели работы

1. Научиться использовать инструмент Подбор параметра для решения нелинейных уравнений вида f (x) = 0.

2. Освоить программный инструмент Поиск решения для решения систем нелинейных уравнений в среде Excel .

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

Задание 1

Найти все корни уравнения .

Порядок выполнения работы

1. Дать рабочему листу название «Уравнение».

2. Настроить текущий документ Еxcel на вычисления с заданной точностью, открыв Настройку панели быстрого доступаи выбравДругие команды.На вкладке Формулы задать предельное число итераций, равное 10000, и относительную погрешность, равную 0,000001.

3. Найти решение нелинейного уравнения в два этапа.

Этап – ЛОКАЛИЗАЦИЯ КОРНЕЙ

4. Создать таблицу значений функции f (x) для х Î [‑2,5; 2,5], шаг изменения 0,1.

5. Построить график функции f (x) (тип – График).

6. Основываясь на данных таблицы и графика, выделить интервалы, на которых функция меняет знак (существует корень).

Этап – УТОЧНЕНИЕ КОРНЕЙ

7. Задать начальные приближения к корням – точки из отрезков локализации корней, например: ‑2,2; 0 и 1,5. Ввести эти значения в ячейки F16, F17 и F18 соответственно.

8. В ячейку G16 ввести формулу =(F16^3-4*F16+1)/(ABS(F16)+1) и с помощью маркера заполнения размножить эту формулу в ячейки G17 и G18.

9. На вкладке ДанныевыбратьАнализ «что-если»,открыть диалоговое окноПодбор параметра и заполнить его поля: в поле Установить в ячейке ввести G16(в этом поле дана ссылка на ячейку, в которую введена формула, вычисляющая значение левой части уравнения); в поле Значениеввести значение 0 (здесь указывается правая часть уравнения); в поле Изменяя значение ячейки ввести F16 (в этом поле дана ссылка на ячейку, отведенную под переменную). После нажатия кнопки OK средство подбора параметра находит приближенное значение корня с заданной точностью.

10. Повторить указанные в п. 9 действия для ячеек G17, F17 и G18, F18 соответственно. Ячейки F16:F18 содержат значения корней уравнения, а ячейки G16:G18 –значения функции в этих точках, близкие к нулю.

11. Оформить созданный лист заголовками и комментариями, как в образце (рис. 13).

K

Рис. 13. Лист «Уравнение»

Задание 2

Найти решение системы нелинейных уравнений на интервале [– 1,7; 1,7] с помощью Excel, используя инструментПоиск решения.

Порядок выполнения работы

1. Дать рабочему листу название «Система».

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

3. Столбец А(аргумент x) заполнить с помощью арифметической прогрессии на интервале [– 1,7; 1,7] с шагом 0,1.

4. Столбцы B, C, D озаглавить как y1, у2, у3 и заполнить формулами, соответствующими полуокружностям и прямой, используя относительную адресацию и маркер заполнения.

5. Построить графики функций системы уравнений для диапазона А2:D36в одной системе координат и определить интервалы локализации корней.

6. Отредактировать диаграмму, согласно образцу (рис. 15).

7. На втором этапе– УТОЧНЕНИЕ КОРНЕЙ – найти корни системы уравнений с помощью инструмента Поиск решения.

8. Для нахождения первого корня в ячейку F25 ввести начальное приближение для первого корня по х. В ячейку G25 – начальное приближение для первого корня по у. В ячейку H25 ввести формулу = (F25^2+G25^2–3)^2+(2*F25+3*G25–1)^2

9. Чтобы получить уточненное значение первого корня, открыть диалоговое окно Поиск решения вкладки Данные. В поле Изменяя ячейки ввести диапазон ячеек F25:G25. В группе Равной установить переключатель в положение Значению, в поле ввода которого ввести . Убедитесь, что в диалоговом окне Параметры поиска решения снят флажок Линейная модель. Затем нажать кнопку Выполнить(рис. 14).

Читайте также:  Как повернуть листок в ворде

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

Рис. 14. Инструмент «Поиск решения»

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

12. Проверить пару значений (1,5; 0). Область притяжения какого корня вы получаете?

13. Оформить созданный документ заголовками и комментариями (рис. 15).

Задание 3

Найти экстремумы функции на интервале [‑ 2; 2] с шагом 0,1. Точность поиска e = 10 -6

Порядок выполнения работы:

1. Дать рабочему листу название «Экстремум».

2. Задав значения аргумента (столбец А) и рассчитав значения функции (столбец В), построить график функции и определить интервалы локализации экстремумов.

3. На этапе– УТОЧНЕНИЕ ЭКСТРЕМУМОВ –найти уточненные значения координат экстремумов и значения функции в этих точках с помощью инструмента Поиск решения.

Рис. 15. Лист «Система»

4. Для нахождения первого экстремума (максимума) в ячейку F17 ввести начальное приближение, в ячейку G17 ввести формулу = F17^3 – F17^2 + 4.

5. Чтобы получить уточненное значение максимума, открыть диалоговое окно Поиск решения вкладки Данные.В поле Установить целевую ячейку ввести адрес ячейки, содержащей формулу – G17. В группе Равной установить переключатель в положение Максимальному значению.В поле Изменяя ячейки ввести адрес ячейки F17. Затем нажать кнопку Выполнить.

6. Получено приближенное значение координаты экстремума и максимальное значение функции в ячейках F17 и G17.

7. Аналогично находят второй экстремум. Но при настройке диалогового окна Поиск решенияв группе Равной установить переключатель в положение Минимальному значению.

8. Оформить созданный документ заголовками и комментариями (рис. 16).

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

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

Практическая работа № 17.

Тема: Решение линейных и нелинейных уравнений с помощью MS Excel.

Цель: научиться решать линейные и нелинейные уравнения различными способами.

Теоретические сведения и задания:

Графический метод решения уравнения.

Известно, что графическим решением уравнения f(x)=0 является точка пересечения графика функции f(x) с осью абсцисс, т.е. такое значение x, при котором функция обращается в ноль.

Разберем графический метод решения уравнения на примере: пусть необходимо решить уравнение x 3 — 0,01x 2 — 0,7044x + 0,139104 = 0.

На листе 1 проведем табулирование нашей функции на интервале от -1 до 1 с шагом 0,2, для этого построим таблицу значений. Затем по таблице построим точечную диаграмму. Результаты вычислений приведены на рисунке, где в ячейку В2 была введена формула: = A2^3 — 0,01*A2^2 — 0,7044*A2 + 0,139104. На графике видно, что функция три раза пересекает ось Оx, а так как полином третьей степени имеет не более трех вещественных корней, то графическое решение поставленной задачи найдено. Иначе говоря, была проведена локализация корней, т.е. определены интервалы, на которых находятся корни данного полинома: [-1,-0.8], [0.2,0.4] и [0.6,0.8] (можно получить более точное решение если выбрать шаг 0,1).

Читайте также:  Как создать аккаунт в mozilla firefox

Лист 1 переименовать в Задание1 и сохранить работу в своей папке с именем Фамилия пр17.xls

Решение уравнения с помощью инструмента «Подбор параметра».

Перейти на лист 2.

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

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

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

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

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

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

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

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

Лист 2 переименовать в Задание2.

Решение уравнения с помощью инструмента «Поиск решения».

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

Перейти на лист 3.

Рассмотрим, как воспользоваться Поиском решения на примере того же квадратного уравнения.

Окно диалога Поиск решения

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

1. в поле Установить целевую ячейку ввести адрес ячейки, содержащей формулу для вычисления значений оптимизируемой функции, в нашем примере целевая ячейка — это С4, а формула в ней имеет вид: = C3^2 — 5*C3 + 6;

2. для максимизации значения целевой ячейки, установить переключатель максимальному значению, для минимизации используется переключатель минимальному значению, в нашем случае устанавливаем переключатель в положение значению и вводим значение ;

3. в поле Изменяя ячейки ввести адреса изменяемых ячеек, т.е. аргументов целевой функции (С3), разделяя их знаком ";" (или щелкая мышью при нажатой клавише Сtrl на соответствующих ячейках), для автоматического поиска всех влияющих на решение ячеек используется кнопка Предположить;

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

Читайте также:  Как разобрать музыкальный центр самсунг

5. для запуска процесса поиска решения нажать кнопку Выполнить.

Результаты поиска

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

Решение нелинейных уравнений и систем»

Цель работы: Изучение возможностей пакета Ms Excel 2007 при решении нелинейных уравнений и систем. Приобретение навыков решения нелинейных уравнений и систем средствами пакета.

Задание1. Найти корни полинома x 3 — 0,01x 2 — 0,7044x + 0,139104 = 0.

Для начала решим уравнение графически. Известно, что графическим решением уравнения f(x)=0 является точка пересечения графика функции f(x) с осью абсцисс, т.е. такое значение x, при котором функция обращается в ноль.

Проведем табулирование нашего полинома на интервале от -1 до 1 с шагом 0,2. Результаты вычислений приведены на ри., где в ячейку В2 была введена формула: = A2^3 — 0,01*A2^2 — 0,7044*A2 + 0,139104. На графике видно, что функция три раза пересекает ось Оx, а так как полином третьей степени имеется не более трех вещественных корней, то графическое решение поставленной задачи найдено. Иначе говоря, была проведена локализация корней, т.е. определены интервалы, на которых находятся корни данного полинома: [-1,-0.8], [0.2,0.4] и [0.6,0.8].

Теперь можно найти корни полинома методом последовательных приближений с помощью команды Данные→Работа с данными→Анализ «Что-Если» →Подбор параметра.

После ввода начальных приближений и значений функции можно обратиться к команде Данные→Работа с данными→Анализ «Что-Если» →Подбор параметра и заполнить диалоговое окно следующим образом.

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

После нажатия кнопки ОК появится диалоговое окно Результат подбора параметра с сообщением об успешном завершении поиска решения, приближенное значение корня будет помещено в ячейку А14.

Два оставшихся корня находим аналогично. Результаты вычислений будут помещены в ячейки А15 и А16.

Задание 2. Решить уравнение e x — (2x — 1) 2 = 0.

Проведем локализацию корней нелинейного уравнения.

Для этого представим его в виде f(x) = g(x) , т.е. e x = (2x — 1) 2 или f(x) = e x , g(x) = (2x — 1) 2 , и решим графически.

Графическим решением уравнения f(x) = g(x) будет точка пересечения линий f(x) и g(x).

Построим графики f(x) и g(x). Для этого в диапазон А3:А18 введем значения аргумента. В ячейку В3 введем формулу для вычисления значений функции f(x): = EXP(A3), а в С3 для вычисления g(x): = (2*A3-1)^2.

Результаты вычислений и построение графиков f(x) и g(x):

На графике видно, что линии f(x) и g(x) пересекаются дважды, т.е. данное уравнение имеет два решения. Одно из них тривиальное и может быть вычислено точно:

Для второго можно определить интервал изоляции корня: 1,5 Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

    #

Ссылка на основную публикацию
Как поставить старую версию скайпа
Программа Skype, как и любой другой активно развивающийся софт, постоянно обновляется. Однако не всегда новые версии выглядят и работают лучше...
Как повернуть диаграмму на 90 градусов
Научимся вращать (поворачивать) график функции относительно начала координат. Для примера используем график функции y=x*sin(10*x). Координаты (x'; y') в результате поворота...
Как повернуть купольную камеру видеонаблюдения
Страница 12 5 Регулировка положения видеокамеры Положение купольной видеокамеры можно регулировать по двум осям. Следя за изображением на мониторе, отрегулируйте...
Как поставить фотографию на контакт в андроид
На любом смартфоне реализована возможность установки изображения на телефонный контакт. Оно будет отображаться при поступлении входящих звонков от этого контакта...
Adblock detector