Решение уравнений и оптимизационных задач

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

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

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

Рис.28. Заполнение ячеек рабочего листа перед вызовом режима

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

Процесс решения с помощью данного метода распадается на два этапа:

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

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

Рассмотрим теперь применение режима Подбор параметра на ряде конкретных примеров.

Пример 1. Найти решение уравнения

З х²-3 у²=5.

Результат создания влияющих и целевых ячеек показан на рис. 8.5.31, а ввод параметров в окно диалога, появляющегося при выполнении команды меню Сервис > Подбор параметра.. – на рис. 29.

Рис. 29. Ввод значений в окно Подбор параметра

Наконец, на рис.30 показан результат выполнения процедуры подбора параметра.

Рис. 30. Результирующее окно режима Подбора параметра

По видимому в результирующем окне (рис.30) текущему значению можно судить о степени точности найденного результата. Если нажать кнопку ОК, то содержимое влияющей ячейки (в нашем случае это ячейка А1) будет заменено на решение уравнения. Дополнительно отметим, что поскольку рассматриваемое уравнение З х²-3 у²=5 зависит от двух переменных и имеет бесконечное множество решений, то какие числа мы получим в ячейке А1, непосредственно зависит от их исходного содержания (так называемого начального приближения).

Рис.31. Моделирование потока платежей

Рис.32. Расчет накопленной к концу года суммы по предполагаемой ставке

Пример 2. Определить, при какой ежемесячной процентной ставке можно за год накопить 5 тыс. р., внося каждый месяц платеж на 10% больше предыдущего, начав с первого платежа 100 р.

С помощью одной лишь финансовой функций эту задачу решить нельзя, в ней слишком много неизвестных. Для начала необходимо смоделировать реальный поток платежей, затем найти накопленную к концу года сумму, нарастив каждый платеж по предполагаемой ставке (так как в задаче дана ежемесячная ставка) на соответствующее число процентных периодов (первый платеж на 12 месяцев вперед, второй – на 11 и т.п., рис. 8.5.35), и только после этого с помощью Подбора параметра найти истинное значение процентной ставки. Ответ: 12,70%. (рис.33)

Рис.33. Подбор параметра

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

Применение надстройки Поиск решения

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

Пусть перед некоторым инвестором стоит проблема принятия решения о вложении имеющегося у него капитала. Набор характеристик потенциальных объектов для инвестирования, имеющих условные имена от А до F, задается в табл.6.

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

1. Суммарный объем капитала, который должен быть вложен, составляет $100000.

2. Доля средств, вложенная в один объект, не может превышать четверти от всего объема.

Таблица 6.