Упражнения по программированию по теме «Работа с надстройкой Поиск решения»

1. В файле ProdMix.xls приведена типичная модель выбора набора производимых товаров. Компании требуется принять решение о производстве различных типов рам для картин. Это решение вызвано серьезными ограничениями на доступные ресурсы и объемом продаж. Основным критерием является максимизация прибыли. Модель уже настроена соответствующим образом, но текущее решение не является оптимальным. Ячейки, отмеченные синим цветом, представляют исходные параметры, а ячейки, выделенные красным цветом, изменяются. В текстовом поле описываются используемые диапазоны. Напишите процедуру, которая настраивает и запускает надстройку Поиск решения.

2. В файле ProdShed.xls представлена модель планирования производства на протяжении нескольких периодов времени. Компания должна спланировать производство на срок в несколько месяцев для своевременного обеспечения существующего спроса на ее товары. В модели существуют ограничения на объем производства и объем складских помещений. На данный момент модель корректно настроена на период планирования в 6 месяцев. Ячейки, помеченные синим цветом, представляют исходные параметры, а красные ячейки изменяются. В текстовом поле указываются имена диапазонов, которые используются в модели. Эту модель можно легко изменить, добавив или скопировав столбцы вправо, что уменьшает или увеличивает период планирования. Предположим, что в модель внесены подобные изменения. Вашей задачей является создание процедуры, которая соответствующим образом изменяет названия диапазонов, настраивает надстройку и запускает Поиск решения (т.е. процедура должна оптимизировать модель, описанную на листе, независимо от размера периода планирования).

3. В файле FacilityLoc.xls содержится модель поиска центрального склада. Существует четыре клиента, которые отправляют товары на этот склад. Предоставлены координаты и объемы годовых поставок каждого клиента. Требуется минимизировать расстояние, которое преодолевается службами доставки за год. Ограничения отсутствуют. Ячейки, отмеченные синим цветом, представляют входные параметры, а ячейки, выделенные красным цветом, предизменяющиеся. В текстовом поле указаны имена диапазонов, которые используются в модели. Это нелинейная модель, поэтому, кроме глобального минимума, в нее может быть также включен и локальный минимум. Если локальный минимум существует, то решение с помощью средств надстройки Поиск решения будет зависеть от начального решения, которое указано в ячейках, выделенных красным цветом. Для проверки такой ситуации напишите две короткие процедуры и назначьте их отдельным кнопкам. Первая кнопка должна случайным образом генерировать разумные решения. (Воспользуйтесь функцией VBA Rnd, которая генерирует равномерно распределенные случайные числа в диапазоне от 0 до 1. Не забудьте в начале процедуры указать ключевое слово Randomize, которое позволяет получать разные последовательности случайных чисел при каждом запуске процедуры.) Вторая процедура должна запускать надстройку Поиск решения. (Настраивать параметры надстройки Поиск решения в данной процедуре не требуется, так как это можно сделать и вручную.) Несколько раз поочередно щелкните на первой и второй кнопке. Всегда ли надстройка Поиск решения находит одно и тоже решение?

4. В файле Transports . xls продемонстрирована транспортная модель, по которой товар должен доставляться с трех фабрик в четыре города. Вам нужно минимизировать стоимость доставки. Ограничениями модели выступает невозможность поставок большого количества товара, чем позволяет объем производства, и необходимость поставки того количества товара, которое покрывает спрос в городах. Правильная модель разработана на листе Модель. Необходимо запустить эту модель в пяти различных сценариях. Каждый из тех сценариев, которые показаны на листе Сценарии, имеет свое соотношение объемов спроса и производства. Напишите процедуру, которая в цикле For реализует все сценарии и выполняет следующие действия.

• Копирует данные определенного сценария в соответствующие диапазоны модели.

• Запускает надстройку Поиск решения.

• Копирует полученные результаты на лист Результаты. а листе Results показаны результаты выполнения сценария 1. Именно этот формат необходимо использовать в каждом из сценариев.

5. В файле Pricing. xls представлена модель поиска оптимальной цены на товар. Товар производится в США и продается в Германии. Компания должна вычислить цену в немецких марках, которая позволит максимизировать прибыль в долларах США. Спрос на товар является функцией от цены. Предполагается, что спрос является не постоянным. Полученная формула спроса приведена в ячейке В14 и зависит от параметров в строке 10. (Предполагается, что эти параметры известны.) Выручка в долларах равна цене умноженной на спрос. Конечно, все зависит от обменного курса, который указан в ячейке В4. Компании необходимо провести анализ чувствительностик изменениям в обменном курсе. Результат должен выводиться на лист Чувствительность (на нем уже указаны обменные курсы, для которых выполняется проверка). Выполните следующие действия.

• Введите любые данные в столбцы В, С и D на листе Чувствительность и воспользуйтесь ими для создания трех линейных диаграмм (справа от данных), которые показывают цену, спрос и прибыль в зависимости от обменного курса.

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

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

6. В файле Stocks.xlsсодержится информация о биржевых ценах акцийбольшого количества компаний за период в 5 лет. Данным каждой компании отведен отдельный рабочий лист, названный биржевым именем компании. Кроме того, в книгу включен рабочий лист S&P500, который содержит сведения об изменении цен на акции. На листе Модель указывается рыночное и биржевое изменения цен, используемые в уравненииMarket=Alpha+Beta x Stock, где Market и Stock представляют рыночную и биржевую цены соответственно, a Alpha и Beta являются параметрами, которые необходимо оценить. Оценка параметра Beta особенно важна в финансовом анализе. Она выражает меру изменения биржевой цены. Модель настроена правильно(в соответствии с данными American Express). Параметры Alpha и Beta найдены в результате минимизации суммы квадратичных ошибок, указанных в ячейке Е4. Напишите процедуру, которая выполняет следующие действия.

• В цикле ForEachпросматривает все листы, кроме Результаты, Модель иS&P500, т.е. все листы сданными отдельных компаний.

• Копирует цены с листа компании в столбец С листа Модель.

• Запускает надстройку Поиск решения.

• Сообщает результаты в новой строке листа Результаты.

После выполнения процедуры на листе Результаты должны отображаться значения параметров AlphaиBeta, а также биржевые имена каждой компании.(Замечание: процедура не должна настраивать параметры надстройки Поиск решения. Это можно сделать один раз на этапе разработки.)

7.(Данное упражнение требует применения пользовательских форм, которые описаны в следующей главе.) ФайлPlanting.xlsсодержит очень простуюмодель, которую фермеры могут использовать для оптимизации высадки культур. Входные параметры выделены синим цветом, а изменяющиеся ячейки по- j казаны красным цветом. Это упражнение предназначено для разработки приложения VBA, которое позволяет пользователю выполнить следующие задачи.

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

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

• Запустить надстройку Поиск решения относительно данных этой ячейки.

• Отобразить полученные результаты на листе Чувствительность.

Примите к сведению несколько полезных советов. В первой задаче необходимо разработать пользовательскую форму со списком входных данных, представленных описательными именами (например, Прибыль с акра пшеницы, Количество рабочих на акр пшеницы и т.д.). Пользователю должна быть предоставлена возможность выбрать один из элементов списка. Во второй задаче необходимо разработать вторую пользовательскую форму, в которой пользователю предоставляется возможность ввести минимальное значение, максимальное значение и приращение. Например, пользователь может указать, что прибыль с акра пшеницы должна изменяться в диапазоне от $150 до $350 с приращением S50. Выполните проверку вводимых данных, чтобы обеспечить передачу значений в правильном числовом формате, убедиться в том, что минимальное значение меньше максимального и приращение является положительным. В третьей задаче сохраните текущие значения выбранных входныхпараметров в переменных, выполните анализ чувствительности и восстановите текущие значения. В четвертой задаче измените надписи в ячейках А1 и A3 на листе Чувствительность в соответствии с выбранным входным параметром.

8.Надстройка Поиск решения содержит скрытые возможности, которые иногда оказываются весьма полезными. (Для получения информации об этих возможностях вам может потребоваться установить дружеские отношения с представителями службы технической поддержки компанииFrontlineSystems!)

Ниже описана одна из таких возможностей. Диапазон изменяющихся ячеек в любой модели Поиск решения получает название диапазонаSolver_adj.Это имя не отображается в списке имен диапазонов, даже если воспользоваться командой Вставка-Имя-Создать, но в файле рабочей книги оно существует. Указанное имя можно использовать следующим образом. Откройте файл PlantLoc.xls. Это достаточно большая модель, которая может использоваться для поиска оптимального расположения фабрики и складов. На данный момент модель правильно настроена, но из-за ее размера с ней сложно работать, также непросто определить, где находятся изменяющиеся ячейки. (Красная граница вокруг таких ячеек, которую мы обычно добавляем, была удалена.) Конечно, можно заглянуть в диалоговое окно Поиск решения и найти там ссылку на изменяющиеся ячейки, но этого делать не стоит. Вместо этого напишите процедуру, которая отображает окно сообщения с координатами диапазона, называющегосяsolver_adj.

9. В продолжение предыдущей задачи можно поинтересоваться, не используются ли в надстройке Поиск решения другие имена скрытых диапазонов. Еще раз откройте файл PlantLoc . xls. Как вы заметили, в столбцах АА и АВ расположены заголовки. Введите и запустите следующую процедуру. Процедура будет искать имена диапазонов, которые начинаются с Модель! Solver. Как вы видите, надстройка Поиск решения хранит достаточно большой объем информации! Какое имя диапазона присваивается ячейке с целью оптимизации? (Код, обрабатывающий ошибку, очень важен, так как некоторые встроенные имена надстройки Поиск решения не ссылаются на диапазоны. Методы вывода сообщений об ошибках рассматриваются в главе 13.)

Sub ShowSolverRangeNames ()

Dim nm As Object, Counter As Integer

Counter = 1

With Range("AA1")

For each nm In ActiveWorkbook.Names

On Error resume Next

If Left(nm.Name, 12) = "Модель!Solver" And _

Range(nm.Name).Address <> "" Then If Err = 0 Then

.Offset(Counter, 0) = nm.Name .Offset(Counter, 1) = Range(nm.Name).Address

Counter = Counter + 1

End If

End If

Next

EndWith

EndSub