Поиск решения

Общие рекомендации по разработке структур электронных таблиц

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

Необходимым условием корректной структуры ЭТ, используемой для решения оптимизационной задачи, является наличие изменяе­мых ячеек и целевой ячейки.

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

При оформлении оптимизационной задачи в структуре электрон­ных таблиц рекомендуется использовать во всех текстовых ячейках, содержащих названия столбцов и/или строк, определение размерно­сти содержимого ячейки. Например «т» (тонна), или «час», или «долл.» и т. п. Использование размерностей способствует выявлению грубых ошибок, связанных, например, с умножением «столов на сту­лья» и получением в результате «тонн в минуту».

Просмотрите разработанную структуру ЭТ с использованием гра­фа зависимостей: все стрелки должны быть направлены от изменяе­мых ячеек и ячеек исходных данных через зависимые ячейки в сторо-

ну целевой. Хорошо структурированная таблица характеризуется на-глядным графом зависимостей.

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

Старайтесь использовать в ЭТ числа по возможности одного по­рядка или близких порядков (например, 10 и 100). Это упрощает про­цесс поиска решения и позволяет избежать многих вычислительных ошибок. Например, в рассмотренной выше задаче о красках оптовая ■на тонны краски выражается в тысячах долларов. Однако в таблице И< пользуются значения 3 (тыс. долл.) и 2 (тыс. долл.), а не 3000 долл. И 2000 долл. Это сделано специально в стиле этой рекомендации.

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

Использование ЭТ для решения оптимизационной задачи будет Солее наглядным, если вы будете использовать именование ячеек. До-стоинства именования ячеек наглядно проявляются при работе с про­граммой поиска решения, при анализе отчетов по результатам моде­лирования и при построении сводных таблиц по результатам исследо­ваний системы.

Например, мы хотим назвать ячейку В23, в которой сохраняется сугочный объем производства краски для наружных работ, именем Краска_Н, ячейку В24 с аналогичным содержимым — именем Крас-ка_В, а целевую ячейку с общим доходом — именем Общий_доход. Для этого мы последовательно выделяем каждую из этих ячеек в таб­лице и обращаемся к меню Вставка, раздел Имя, оператор Присвоить, который открывает окно именования. В это окно вводится соответст-вующее имя, и в дальнейшем во всех отчетах поименованная ячейка будет идентифицироваться присвоенным ей именем.

Стиль оформления ограничений

Хорошим стилем оформления задания на поиск решения являет­ся использование ограничений, левые и правые части которых состо­ит только из имени (адреса) одной ячейки или массива ячеек. При этом

20

Часть 1. Поиск решений на электронных таблицах

Поиск решения

21

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

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

Ниже приводятся некоторые рекомендации по оформлению огра­ничений на оптимальное решение задачи.

Старайтесь избегать избыточных ограничений. Тривиальный при­мер таких ограничений: В23<=16, В23<=20. Избыточные ограничения всегда «мешают» процессу поиска и в некоторых случаях могут приве­сти к зацикливанию вычислений.

Использование ограничений в форме равенства всегда «сужает» полигон для поиска решения. Такие ограничения в общем случае ока­зываются слишком «жесткими» для реальных задач, и (по возможно­сти) следует отдавать предпочтение более «мягким» неравенствам.

Противоречивые ограничения делают процесс поиска бессмыслен­ным. Тривиальный пример таких ограничений: В24 <= СЗО; В24 >= СЗО+2. Такие ограничения всегда связаны с отсутствием ре­шения задачи. Основная проблема, связанная с противоречивыми ограничениями, заключается в том, что для сложных задач с большим числом ограничений весьма трудно выявить противоречия между от­дельными ограничениями.

Проблема начальных значений

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

и пленяемых ячеек и «почувствовать» тенденции приближения к опти­муму. Запомните, чем ближе начальные значения к точке оптимума,

тем легче и быстрее его удается найти. К сожалению, эта рекоменда­ция может быть использована для решения сравнительно простых задач.^|

В этом отношении может может оказаться полезным специальное

средство системы EXCEL — Подбор параметра (меню Сервис).

В поле Установить в ячейке указывается адрес (имя) ячейки, со­держащей формулу (в нашем случае это целевая ячейка Е24), которая устанавливает зависимость от изменяемой ячейки (в нашем случае ЭТО В23). Подбор параметра позволяет подобрать такое значение из­меняемой ячейки, при котором целевая получит установленное нами значение (в этом примере 16).

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

Результат работы программы подбора параметра занесется в ячей­ку, указанную в поле Изменяя значение ячейки (в нашем примере В23).

Управление процессом поиска решения

Нажатие кнопки Выполнить окна Поиск решения приводит к запу­ску процесса поиска решения задачи оптимизации. При невозможно­сти запустить поиск решения система может вывести следующие со­общения:

Недостаточно памяти для решения задачи. Это означает, что ЕXCEL не смог выделить память для поиска решения. Закройте не­нужные файлы или приложения и попытайтесь запустить программу снова.

Часть 1. Поиск решений на электронных таблицах

В другом сеансе EXCEL используется SOLVER. Запущено несколь­ко сеансов работы с EXCEL, в одном из которых используется SOLVER — программа поиска решения. Попробуйте завершить или закрыть окна, связанные с этими сеансами.

Управление процессом поиска решения связано с использовани­ем диалогового окна Параметры поиска решения, которое открывается при нажатии кнопки Параметры окна Поиск решения.

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

Поиск решения

Группа параметров, определяющих время процесса поиска

К этой группе относятся четыре параметра: максимальное время, число итераций, точность и допустимое отклонение.

К настройке этих параметров целесообразно обратиться при полу­чении следующих сообщений о неудачном окончании процесса поис-1 ка решения:

• поиск решения не может улучшить текущее решение. Все огра­
ничения выполнены;

• остановка при исчерпании лимита времени;

• остановка при выполнении максимального числа итераций.