Информатика / Учебные_материалы / Excel / Excel-решение_уравнений

71

РХТУ им. Д.И. Менделеева Методическое пособие по изучению Excel 5.0

Решение задач численными методами

с помощью Excel.

Нахождение корней функции вида f(x)=0

Изучение возможностей Excel для автоматизации расчета и оптимизации поиска решения.

Что необходимо знать в Excel для решения этой задачи:

 Приемы оптимального заполнения таблиц данными

(автозаполнение, копирование формул);

 Построение диаграммы с помощью Мастера диаграмм;

 Некоторые функции стандартной библиотеки функций EXCEL;

 Команда Копирование  Специальная вставка;

 Команда Поиск решения;

 Команда Подбор параметра.

Постановка задачи:

Найти все корни уравнения y=x²Cos2x+1 в диапазоне [0÷2].

Метод решения:

Поиск корней проведем в два этапа.

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

2-ой этап – уточнение корней. Берем первый подинтервал, в котором есть единственный корень, и ищем корень тремя различными способами (Приложение 2, стр. 2):

 простая итерация, выполняемая вручную;

 команда Поиск решения;

 команда Подбор параметра.

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

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

Выполнение задания

 Выполним первый этап – отделение корней

Ввести исходные данные для решения задачи С6  0; D6  =2ПИ(); F6  =ПИ()12

 Заполнить таблицу для табулирования заданной функции

 Колонка "№ п.п." – автозаполнение.

 Колонка "Х":

Ячейка В9 0

В ячейку В10 скопировать значение из ячейки F6, выполнив

команды Копировать, Специальная вставка…, следующим

образом:

выделить ячейку F6  вызвать контекстное меню (правая кнопка

мыши  команда Копировать.

Выделить ячейку назначения В10  вызвать контекстное меню 

команда Специальная вставка…  выбрать  Значения  Ok.

Будет скопировано значение, а не формула!

Выделить ячейки В9, В10  выполнить команду автозаполнение

(протащить маркер вниз).

 Колонка "Y":

Ячейка С9  =В9^2COS(2D9)+1;

Скопировать формулу вниз на всю колонку.

 Колонка "Поиск" диапазона с единственным корнем:

Ячейка D10  =ЕСЛИ(ЗНАК(С10)?ЗНАК(С9); "Корень"; "-"),

скопировать вниз на всю колонку.

Диапазоны, содержащие корни отделены (локализованы)!

 Построить линейный график функции. Для этого:

Выделить колонки "Х" и "Y" (диапазон В8:С33)  кнопка Мастер функций  указать место для диаграммы 

Шаг 1 – согласиться с предложенным диапазоном, нажав кнопку Далее;

Шаг 2 – тип диаграммы  График, Далее;

Шаг 3 – вид графика  4, Далее;

Шаг 4 – считать  столб. метками оси Х, Далее;

Шаг 5 – добавить легенду  Нет, Готово.

Этап отделения корней завершен!

 Приступаем к этапу уточнения корней. Ищем корень в первом найденном поддиапазоне (вариант а).

Ячейка G41  0,00001 – точность вычисления;

Ячейки K41:L41 

 выделить первый найденный диапазон В13:В14, команда Копировать;

 выделить ячейку назначения К41, команда Специальная вставка…,

 Транспонировать  Ok.

 выберем шаг табулирования для первого прохода  ячейка В45 – 0,05.

 Заполним первую таблицу.

Столбец "Х":

Ячейка А48  значение начала диапазона скопировать из ячейки К41;

 Ячейка А49  вычислить следующее значение Х по формуле

=А48+В45;

Заменить в ячейке А49 формулу значением  команды Копировать,

Специальная вставка,  Значения, Ok;

 Выделить ячейки А48:А49 и заполнить всю колонку, протащив

мышкой маркер заполнения.

Столбец "Y":

 Ячейка В48  скопировать формулу из любой ячейки колонки "Y"

первой таблицы. Команды Копировать, Специальная вставка…,

 Формулы, Ok; Проверьте формулу в строке формул – произошла

автоматическая настройка ссылок!

 Скопируйте формулу вниз.

Столбец "Поиск":

 Ячейка С48  ввести формулу для автоматического поиска корня с заданной точностью

=ЕСЛИ(FBS(B48)=$G$41;"Корень";"-")

 Скопировать формулу вниз.

 Столбец "Поиск" заполнен "-" – корень с заданной точностью не найден.

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

Первый корень равен Х₁ = 1,8332

 Рассмотрим вычислительные возможности Excel по нахождению решений.

Команда Поиск решения… (вариант б)

 В ячейки С67 и F67 скопируем значение начала диапазона поиска

первого корня – из ячейки К41.

 В ячейки С68 и F68 скопируем формулу. Команды Копировать,

Специальная вставка.

 Выделить целевую ячейку F68.

 Меню Сервис  команда Поиск решения…

 В этом окне диалога мы должны

указать цель вычислений;

указать изменяемые ячейки;

указать ограничения.

Цель  Установить целевую ячейку F68

Равной  Значению 0,00001

Изменяя ячейки  щелкнуть мышью по ячейке F67

Ограничения  окно ограничения, кнопка Добавить… - появится новое

диалоговое окно:

Ввести в это окно последовательно следующие ограничения:

F67=B65  значение корня в пределах диапазона, Добавить.

F67=C67  значение корня в пределах диапазона, Ok,

вернулись в предыдущее окно.

 Щелкнуть по кнопке Выполнить. Команда Поиск решения находит оптимальное решение с заданной точностью для заданной целевой ячейки при всех заданных ограничениях и выводит следующее окно диалога:

Установите переключатель  Сохранить найденное решение и нажмите кнопку Ok.

Первый корень равен Х₁ = 1,8332

Сравните найденное значение корня с предыдущим вариантом.

 Рассмотрим вычислительные возможности Excel по нахождению решений с помощью команды Подбор параметра… (вариант в)

 В ячейки К67 и N67 скопируем значение начала диапазона поиска

первого корня – из ячейки К41.

 В ячейки К68 и N68 скопируем формулу. Команды Копировать,

Специальная вставка.

 Выделить целевую ячейку N68.

 Меню Сервис  команда Подбор параметра…

На экране появится диалоговое окно:

 Целевая ячейка N68  вычисляемое значение Y.

 Значение 0,00001  Y сделать равным этому значению.

 Изменяя значение ячейки N67  найти значение Х, для которого

Y=0,00001.

 Щелкнуть по кнопке Ok.

Решение найдено.

Первый корень равен Х₁ = 1,8332

Результат совпадает с предыдущими вариантами. Нажать кнопку Ok.

 Выбрать наиболее рациональный для данной задачи способ уточнения корней и найти остальные корни уравнения. Сохранить результат на диске.