Поиск решения средствами MS Excel

Задание 1. Пусть задана функция y = 2 ^x + 5 cos² x – 2.

Построить график функции на отрезке [–3; 4].

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

Выполнить эти процедуры можно различными способами. Рассмотрим два из них.

1 способ. Перейдите на лист График функции.

1. Введите в ячейку А1: X; в ячейку А2 значение –3, в ячейку А26 – значение 4. Заполните область А2:А26 прогрессией с автоматическим определением шага.

2. Для оформления графика в ячейку В1 введите формулу функции (можно в общем виде: y = f (x)). Введите в ячейку В2 формулу для вычисления функции, указывая вместо аргумента адрес первой ячейки из списка аргументов: = 2^A2+5*COS(A2)^2–2. Скопируйте эту формулу на область В2:В26 маркером заполнения.

3. Сделайте текущей любую ячейку таблицы значений функции (в некоторых версиях Excel следует предварительно выделить диапазон, по которому требуется построить график). Вызовите команду меню Вставка – Диаграмма (или нажмите кнопку Мастер диаграмм на Стандартной панели). Выберите Точечная диаграмма со значениями, соединенными сглаживающими линиями. На последующих шагах Мастера диаграмм введите названия диаграммы, осей, выключите линии сетки, легенду и т. д.

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

У вас должен получиться примерно такой график.

2 способ. Рассмотрим способ с использованием именованных диапазонов. Перейдите на другой лист. Назовите его График2.

1. Повторите первый шаг 1-го способа. Выделите область А2:А26. Вызовите команду Вставка – Имя – Присвоить. В диалоговом окне вам будет предложено имя Х для диапазона А2:А26. Нажмите Добавить и закройте окно.

2. В ячейку В1 введите формулу функции. Введите в ячейку В2 формулу для вычисления функции: = 2^х+5*соs(x)^2–2. Заполните этой формулой область В2:В26.

3. Постройте график функции по алгоритму третьего шага 1-го способа.

В дальнейшем при построении графиков функций можете использовать любой из способов.

Задание 2. Решить уравнение2 ^x + 5 cos² x – 2 = 0.

Напомним, что корнем уравнения f(x) = 0называется такое значение аргумента, при котором уравнение обращается в тождество. Геометрически корень уравнения – абсцисса точки пересечения, касания или другой общей точки графика функции y = f(x) с осью Ох.

График функции позволяет отделить корни уравнения. Можно утверждать, что уравнение имеет корни, по крайней мере, на отрезках [–2,5; –1,5] и [–1,5; –0,5].

Вернитесь на лист График функции. Введите в D1: Корни. Выполните объединение ячеек D1:Е1. Для этого выделите D1:Е1 и на вкладке Выравнивание диалогового окна Формат – Ячейки включите Объединение ячеек.

Найдите корень на [–2,5; –1,5]. Для этого скопируйте формулу из любой ячейки столбца В листа График функции в ячейку Е2. При этом в качестве аргумента в формуле фигурирует ячейка D2. Нужно подобрать в ячейке D2 такое значение, чтобы в ячейке Е2 получился ноль. Воспользуйтесь процедурой Поиск решения, встроенной в Excel (следует иметь в виду, что численные методы сходятся не всегда, поэтому для некоторых функций Поиск не сможет найти решение).

Вызовите команду Главная – Параметры – Параметры - Поиск решения.

Заполните диалоговое окно Поиска.

Установить целевую: указать адрес ячейки Е2

Равной значению: 0

Изменяя ячейки: указать D2 (можно щелчком мыши по ячейке)

Щелкнуть кнопку Добавить. В появившемся диалоговом окне «Добавление ограничения» ввести условие принадлежности искомой точки рассматриваемому отрезку:

Ссылка на ячейку: D2

Во втором окошке нажать стрелку ниспадающего меню и выбрать знак ³

В окошке Ограничение ввести значение –2,5

Нажать Добавить. Аналогичным образом добавить условие D2 £ –1,5.

Нажать ОК и вернуться в окно Поиска.

Кнопкой Выполнить запустить процедуру.

Если все сделано правильно, появится сообщение: Решение найдено. Кнопкой ОК сохранить найденное решение. В ячейке D2 отобразится значение корня уравнения –2,21096. Обратите внимание, что значение функции в Е2 представлено в экспоненциальной форме (–9,73E-07 = –9,73·10^–7) и его можно принять равным нулю с достаточной точностью.

Самостоятельно найдите корень на отрезке [–1,5; –0,5]. Ответ: –0,99.

Задание 3. Для поиска корня также можно пользоваться процедурой Подбор параметра. Скопируйте формулу из любой ячейки столбца В листа График функции в ячейку Е4. При этом в качестве аргумента в формуле фигурирует ячейка D4. Нужно подобрать в ячейке D4 такое значение, чтобы в ячейке Е4 получился ноль.

Выберите команду Сервис – Подбор параметра. В диалоговом окне укажите:

Установить в ячейке: Е4

Значение: 0

Изменяя значение в ячейке: D4

Кнопкой ОК запустить процедуру.

Подбор параметра найдет ближайшее к стоящему в D4 значение корня. Так как D4 – пустая, то значение в ней равно нулю. Поэтому в D4 появится –0,99.

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

Скопируйте диапазон Е4:D4 в Е5:D5. В качестве начального приближения введите в Е5 значение –2. Выполните Подбор параметра.

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

Задание 3. Найти экстремумы функции 2 ^x + 5 cos² x – 2 = 0.

Из графика функции видно, что она многоэкстремальная. Найдем локальный экстремум на отрезке [–3; 0]. Воспользуемся процедурой Поиска решения, указывая в диалоговом окне задания условий поиск минимального значения. Должны получить y_min» –1.666 при x» –1,59377.

Найдите локальный максимум на [–1; 1]. Ответ: (0,07318; 4,0253).

Найдите локальный минимум на [0; 4]. Ответ: (1,385491; 0,782343). При этом в силу особенностей поиска локального экстремума, в качестве начального приближения этого значения корня следует выбрать ненулевое значение, например, 3.

Сделайте соответствующие пояснения на листе.

Дата добавления: 2015-07-12