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

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

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

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

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

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

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

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

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

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

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

З х²-3 у²=5.

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

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

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

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

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

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

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

по предполагаемой ставке.

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

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

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

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

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

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

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

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

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

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

Таблица 8.5.6.

Параметры условных активов (для учебного примера)

3. Более половины всех средств должны быть вложены в долгосрочные активы (допустим, на рассматриваемый момент к таковым относятся активы со сроком погашения после 2004 года).

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

Дадим теперь описание экономико-математической модели для данной ситуации.

В рассматриваемом примере в качестве переменных выступают объемы средств, вложенных в активы той или иной фирмы. Обозначим их как x_a, x_b, x_с, x_d, x_e, x_f. Тогда суммарная прибыль от размещенных активов, которую получит инвестор, может быть представлена в виде

P = 0,055 x_a+0,06 x_b+0,08 x_c+0,075 x_d+0,055 x_e+0,07 x_f.

На следующем этапе моделирования мы должны формально описать перечисленные выше ограничения 1 – 4 на структуру портфеля.

1. Ограничение на суммарный объем активов:

x_a+ x_b + x_c + x_d + x_e + x_f = 100000.

2. Ограничение на размер доли каждого актива:

x_a ≤ 25000, x_b ≤ 25000, x_c ≤ 25000

x_d ≤ 25000, x_e ≤ 25000, x_f ≤ 25000.

3. Ограничение, связанное с необходимостью вкладывать половину средств в долгосрочные активы:

x_b + x_c ≥ 50000.

4. Ограничение на долю ненадежных активов:

x_c + x_d ≤ 33333.

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

x_a ≥ 0, x_b ≥ 0, x_c ≥ 0, x_d ≥ 0, x_e ≥ 0, x_f ≥ 0.

Перечисленные условия образуют математическую модель поведения инвестора. В рамках этой модели может быть поставлена задача поиска таких значений переменных x_a, x_b, x_c, x_d, x_e, x_f, при которых достигается наибольшее значение прибыли и одновременно выполняются ограничения на структуру портфеля активов.

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

1. На выбранном рабочем листе задать ячейки, которые будут предназначены для сохранения переменных решаемой задачи (x_a, x_b, x_c, x_d, x_e, x_f), как это сделано на рис. 8.5.37, где переменная x_a содержится в ячейке А2, x_b – в В2 и т.д.

Заметим, что хорошим стилем работы является использование смежных ячеек для хранения имён переменных (на рис. 8.5.37 для этого служат ячейки с Al по F1). Очевидно, что ни в коем случае не следует путать ячейки с переменными с ячейками со смысловыми именами.

Рис. 8.5.37. Заполнение листа Excel перед вызовом Поиска решения

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

=0,055 * А2 + 0,06 * B2 + 0,08 * С2 + 0,075 * D2 + 0,055 * Е2 + 0,07 * F2.

3. Заполнить ячейки для формул «сложных» ограничений (типа x_b + x_c < 50000. и т. п.). На рис. 8.5.37 для этого использованы ячейки A4 – А18, а ячейки D4 – D18 содержат числовые значения ограничений. На рис. 8.5.37 формулы в ячейках А4 – А18 показаны только в качестве иллюстрации. Они будут видны только в строке команд. В столбце С для иллюстрации показаны соответствующие знаки ограничений.

Интерфейс надстройки Поиск решения устроен таким образом, что для учета условия вида x_b + x_c ≤ 50000 нужно в некоторую вспомогательную ячейку ввести формулу с левой частью неравенства и уже для нее задать ограничение ≤ 50000.

4. Выполнить команду меню Сервис > Поиск решения....

Рис. 8.5.38. Вид листа перед входом в надстройку Поиск решения

Рис. 8.5.39. Заполнение диалогового окна надстройки Поиск решения

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

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

Рис. 8.5.40. Заполнение диалогового окна Параметры поиска решения

7. Нажать клавишу Параметры и установить в диалоговом окне Линейная модель, Неотрицательные значения и ОК (рис. 8.5.40).

Рис. 8.5.41. Вид рабочего листа с найденным решением

8. Нажать кнопку Выполнить, после чего будет осуществлена процедура поиска решения, по результатам которой выводится сообщение о найденном решении (или о невозможности его обнаружить). Как видно из рисунка 8.5.41, полученные результаты можно сохранить (кнопка ОК), изменив, таким образом, содержимое ячеек с переменными, можно от них отказаться (не сохранять), наконец, можно сформировать отчет с более подробной информацией о том, как проходил процесс поиска решения. Таким образом, мы получили, что при оптимальном распределении прибыль инвестора составит $6791,7.

Пример расчетов с использованием Таблиц подстановки

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

Рис. 8.5.42. Ввод расчетной формулы и выделение диапазона

для таблицы подстановки

Необходимо проделать следующие операции:

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

2. Задать в «матричной» форме, как это показано на рис. 8.5.42, целевую формулу, зависящую от ячеек, определенных на этапе 1 (для нашего примера – это формула = А1*А2 в ячейке В4), а также серии значений, предназначенных для подстановки вместо переменных. Серии значений должны располагаться в левой колонке (В) и верхней строке (4). При определении таблицы необходимо соблюдать очевидное правило – ячейки с переменными не должны попасть в ее внутреннюю область.

3. Выделить область таблицы, как это показано на рис. 8.5.42.

4. Выполнить команду меню Данные > Таблица подстановки....

Рис. 8.5.43. Задание соответствия между переменными и сериями

значений в столбцах и строках Таблицы подстановки

Рис. 8.5.44. Результат заполнения Таблицы подстановки

5. Заполнить параметры в появившемся диалоговом окне (рис. 8.5.43). Первое значение – Подставлять значения по столбцам в – задает адрес ячейки с той переменной, вместо которой в целевую формулу будут подставляться значения из крайней верхней строки таблицы подстановки. В нашем случае вместо переменной из ячейки А1 последовательно будут подставлены в формулу значения из интервала C4:L4. Аналогично, второе значение – Подставлять значения по строкам в – задает адрес ячейки той переменной, вместо которой в целевую формулу будут подставляться значения из крайнего левого столбца таблицы подстановки. В нашем примере вместо переменной из ячейки А2 в формулу будут подставлены значения из интервала В5:В14.

6. Нажать кнопку ОК.

Результат заполнения таблицы подстановки показан на рис. 8.5.44.

8.6. Использование систем интегрированных программных пакетов