III. Применение Excel для решения математических задач

Решения "что-если" для подбора параметра.

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

Для изучения возможностей подбора параметров выполните действия следующего примера.

Задача 1: найти значение переменной , при котором функцияпринимает значение 10.

Выделите Лист2 открытой книги. На этом листе мы будем использовать ячейку А1 для отображения искомого результата задачи, а ячейку В1 для помещения в нее исследуемой формулы. Введите в ячейку В1 формулу =А1^2. Используйте команду Данные(Работа с данными) Анализ"что-если" Подбор параметра. В открывшемся окне в поле в поле Установить в ячейке введите ссылку на ячейку, содержащую формулу В1, в поле Значение введите желаемое значение формулы 10, а в поле Изменяя значение ячейки – ссылку на ячейку результата А1 и нажмите ОК. На экране появится окно, в котором будут отображены результаты подбора, а в ячейке А1 – результат решения задачи.

Используя данный пример и ячейки А2 – для результата и В2 – для формулы, решите самостоятельно следующую задачу.

Задача 2: найти значение переменной , при котором функцияпринимает значение минус одна вторая.

Исследование математических функций на экстремум.

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

Оставаясь на активном Листе2, активируйте надстройку Поиск решения. Для этого в окне Параметров Excel выберите вкладку Надстройки, затем на правой панели в списке Управление выберите значение Надстройки Excel и нажмите кнопку Перейти. В открывшемся окне установите флажок Поиск решения и нажмите ОК.

Для изучения возможностей поиска решения выполните действия следующего примера.

Задача 3: найти значение переменной , при котором функцияпринимает минимальное значение.

Введите в ячейку В5 формулу =(А5+1)^2. Используйте команду Данные(Анализ) Поиск решения. В открывшемся окне: в поле Установить целевую ячейку укажите ссылку на ячейку с формулой В5; в поле Равной отметьте пункт минимальному значению; в поле изменяя ячейки укажите ссылку на ячейку размещения результата поиска А5. Нажмите кнопку Выполнить и пронаблюдайте появление окна отчета о решении. При этом в ячейке А5 отобразится результат решения задачи, а в ячейке В5 – достигнутое минимальное значение функции.

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

Задача 4: найти значение переменной , при котором функцияпринимает максимальное значение.

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

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

Рассмотрим следующий пример.

Задача 5: определить значения переменных и, удовлетворяющих системе линейных уравнений

Прейдите на Лист3 открытой книги. Для решения задачи мы задействуем ячейки А1 и В1 – для результирующих значений переменных и, соответственно; ячейкуС1 – для формулы первого уравнения системы; ячейку С2 – для записи правой части второго уравнения. Введите в ячейку С1 формулу, соответствующую первому уравнению задачи =2*А1+В1. В ячейку С2 запишите правую часть второго уравнения, т.е. число 1. Далее используем команду Данные(Анализ) Поиск решения. Устанавливаем целевую ячейку С1, в поле Равной отмечаем пункт Значению и вводим правую часть первого уравнения – число 5. В поле изменяя ячейки помещаем ссылки А1:В1 (обратите внимание, что при задании этих ссылок щелчком по соответствующим ячейкам, в заполняемом поле записываются абсолютные ссылки в виде $A$1:$B$1). Теперь необходимо ввести второе уравнение системы в виде ограничения на решение. Для этого нажимаем кнопку Добавить, размещенную возле поля Ограничения. В открывшемся окне указываем ссылку на ячейку, содержащую правую часть второго уравнения С2, в поле логических операторов выбираем знак равенства =, а в поле ограничение вводим формулу, соответствующую второму уравнению системы, для чего щелкаем по ячейке А1, набираем на клавиатуре знак +, и щелкаем по ячейке В1. После этого в окне ввода ограничения нажимаем ОК, и используем кнопку Выполнить в окне поиска решения. В ячейках А1 и В1 получаем результат решения в виде числовых значений искомых переменных и.

Используя в качестве образца решение задачи 5 и произвольную область ячеек на Листе3, решите самостоятельно следующую задачу

Задача 6: определить значения переменных ,и, удовлетворяющих системе линейных уравнений:

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