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

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

ПРИМЕЧАНИЕ

Инструмент является надстройкой. Если в свое время выполнялась полная установка Excel, команда находится  в  меню Сервис. В противном случае нужно  в  меню  Сервис  выбрать  команду  Надстройки  и  в  списке  доступных  надстроек установить  флажок  Поиск  решения.  Если  же  в  списке  нет  варианта  Поиск  решения, придется запустить программу установки Excel еще раз. Этот процесс описан в приложении А «Установка Microsoft Excel».

Рассмотрим задачу, ответ  в которой можно найти с помощью команды . Представьте, что вы разрабатываете  план рекламной кампании. Общий бюджет на выпуск печатной рекламной продукции  составляет $12 000 000, при этом крайне желательно, чтобы число  потенциальных читателей этой рекламы не падало ниже 800 миллионов,  а вся кампания  развернулась на  страницах  шести  печатных  изданий,  назовем их  для примера Изд1, Изд2, ИздЗ и т. д. Каждое из этих  изданий имеет свой круг читателей и различную  стоимость  размещения  рекламных  материалов на  своих страницах.  Ваша задача состоит в  том,  чтобы оптимизировать затраты, то есть в  пределах выделенной суммы достичь максимального эффекта при соблюдении определенных дополнительных условий:

? в каждом издании должно быть напечатано не менее 6 объявлений;

? нельзя тратить более трети всей суммы на одно издание;

? общая стоимость размещения рекламы в ИздЗ и Изд4 не должна превышать $7 500 000.

На рис. 18.23 показан один из возможных вариантов решения.

Рис. 18.23. Для решения задачи с несколькими параметрами лучше использовать инструмент

СМОТРИ ТАКЖЕ

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

Поставленную задачу  можно  попытаться  решить  вручную путем  подбора  значений  в ячейках D2:D7, не забывая при этом о наложенных  ограничениях и следя за конечной суммой расходов в ячейке Е8. В конце концов, наверное, вам*и удастся оптимизировать затраты, но поверьте,  силами программного решателя это будет сделано во  много раз быстрее,  ведь при расчетах применяется не просто перебор значений  параметров, а ряд специально приспособленных алгоритмов.

Рис. 18.24. Диалоговое окно

Чтобы начать работу с инструментом , выберите эту  команду в  меню Сервис. На экране появится диалоговое окно , продемонстрированное на рис.  18.24.  Необходимо  заполнить   все поля  этого  окна,  то  есть  указать  конечный результат   (цель,   в   данном   случае   —   минимизацию   затрат),   изменяемые   ячейки (количество объявлений в  каждом издании) и ограничения (условия,  которые должны соблюдаться при расчетах; они выделены рамкой в нижней части листа на рис. 18.23).

Указание ожидаемого результата

В поле Установить целевую ячейку (Set Target Cell) задается цель поиска решения. В рассматриваемом примере нашей задачей является минимизация общих затрат (значения в ячейке  Е8),  поэтому  в   указанное   поле  следует  ввести ссылку   на  эту  ячейку,  а переключатель Равной (Equal To) установить в  положение  Минимальному значению (Min).

СОВЕТ –

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

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

СМОТРИ ТАКЖЕ  

Для  получения  дополнительных  сведений  о  флажке  Показывать  результаты  итераций обращайтесь к разделу «Просмотр промежуточных результатов» данной главы.

Задание переменных

На  следующем  шаге  работы  с  инструментом  Поиск  решений   нужно  определить изменяемые ячейки, то есть те, в которых значения могут изменяться. В нашем примере это ячейки D2:D7. Можно либо ввести ссылку на диапазон в поле Изменяя ячейки, либо щелкнуть на кнопке Предположить (Guess), и программа сама предложит изменяемые ячейки, исходя из заданной целевой ячейки модели.

Задание ограничений

Последний шаг — добавление в модель ограничений — не является необходимым. Когда он опущен, поиск решения выполняется безо всяких условий. Если же требуется уточнить критерии поиска, щелкните на кнопке Добавить (Add) в диалоговом

окне и заполните поля диалогового окна Добавление  ограничения (Add Constraint).  Рисунок  18.25  демонстрирует,  как  следует  вводить ограничение  на общие расходы на рекламу (значение в ячейке Е8) — они не должны превышать общий бюджет проекта (значение в ячейке G11).

Рис. 18.25. Чтобы ввести ограничения в этом окне, щелкните на кнопке Добавить в диалоговом окне

На рис. 18.26 показано диалоговое окно после заполнения всех его полей и   наложения   ограничений.   Обратите   внимание,  что   ограничения   перечислены   в алфавитной последовательности, которая может и не совпадать с порядком их ввода.

Рис. 18.26. Диалоговое окно Поиск решений, подготовленное к решению задачи

Заметьте  также,  что  два ограничения  слева от  оператора  сравнения  включают в  себя ссылки на диапазоны. Выражение $D$2:$D$7>=$G$15  подразумевает, что значение в любой ячейке диапазона D2:D7 обязано быть больше 6 или равно этому числу, а условие

$F$2:$F$7<=$G$14 указывает, что значение в любой ячейке диапазона F2:F9 не должно превышать 33,3%. Каждое из двух выражений является краткой записью шести отдельных ограничений. При таком способе записи ограничений в правой   части выражения должна стоять либо ссылка на одиночную ячейку, либо ссылка на диапазон той же размерности, что и в левой  части выражения, либо константа.

После заполнения всех параметров диалогового окна  нажмите кнопку

Выполнить  (Solve).  В  нашем  примере  с  рекламной  кампанией  программа  находит

оптимальное решение при фиксированной конечной цели и введенных ограничениях и в результате выводит на экран диалоговое окно,  представленное на рис. 18.27. Значения, полученные в  ячейках на  рабочем  листе, как раз и показывают наилучший вариант. Их можно  или   оставить  на  листе,  установив   переключатель  в   положение   Сохранить найденное решение (Keep Solver Solution) и  нажав  кнопку  ОК,  или  восстановить исходные   значения.   Для   второго  случая   установите  переключатель   в    положение Восстановить  исходные  значения  (Restore  Original  Values)  и  нажмите  OK  (или нажмите   кнопку  Отмена  (Cancel)).  Также  есть  возможность сохранить   найденное решение как сценарий.

Рис. 18.27. Когда ответ найден, на экране появляется диалоговое окно Результаты поиска решения

Ограничение на количество разрядов в ответе

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

Чтобы программа находила только целые значения определенных переменных, нажмите кнопку  Добавить  (Add)  в   диалоговом окне   Поиск  решений.  В  диалоговом окне Добавление   ограничения   введите  ссылку   на   диапазон   D2:D7,   откройте   раскрывающийся список в средней части этого диалогового окна и выберите пункт Цел (Int), как показано на рис. 18.28. В поле Ограничение (Constraint) появится  слово «целое» (integer). Нажмите кнопку ОК, чтобы вернуться к  основному диалоговому окну .

Рис. 18.28. Чтобы ограничить решение множеством целых чисел, выберите во втором раскрывающемся списке диалогового окна пункт Цел

При  поиске  целочисленного  решения  Excel  не  просто  округляет   числа  в   меньшую сторону, а выполняет их оптимизацию с учетом  ограничения. Так, при размещении 53 рекламных объявлений в Изд4 появляется возможность купить дополнительные места на рекламу в  Изд5,  что при очень маленьком увеличении  бюджета позволяет расширить аудиторию потенциальных читателей на целых 2 млн человек.

СОВЕТ

Установка  целочисленных  ограничений  обычно  ведет  к  усложнению  задачи  в  геометрической прогрессии, а вследствие этого и к увеличению времени поиска оптимального ответа. Пример, приведенный выше, достаточно прост, и ответ находится быстро, но для сложных моделей    поиск   решения   среди   целочисленных   значений   может   оказаться    очень ресурсоемким процессом. Поэтому старайтесь обращаться к данному ограничению только в действительно  критических  случаях.  В  частности,   целочисленные  решения  являются обязательными  для  задач,  в  которых  переменные  в  состоянии  принимать  только  два значения, например 1 и 0 («Да» и «Нет»). Тогда в диалоговом окне Изменение ограничения (Change Constraint) в раскрывающемся списке в средней части окна нужно выбрать вариант

«двоич» (bin).

Сохранение модели поиска решения

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

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

1.   Откройте диалоговое окно , выбрав   в  меню Сервис  одноименную команду.

2.   Щелкните на кнопке Параметры (Options), чтобы вывести на экран диалоговое окно Параметры  поиска  решения  (Solver  Options),  показанное  на  рис.  18.29.  Затем нажмите  кнопку  Сохранить  модель  (Save  Model).  Программа  попросит  указать ячейку или  диапазон рабочего листа, в  которые будут записаны параметры  поиска решения.

3.   Задайте пустую ячейку, щелкнув  на ней или введя  ссылку, и  нажмите  кнопку ОК. Сохраняемые параметры появятся на листе, начиная с указанной ячейки, и займут при этом необходимое количество соседних ячеек. (Поэтому предварительно также следует убедиться, что ячейки ниже заданной не содержат данных.)

4.  Чтобы воспользоваться сохраненными параметрами, нажмите кнопку Загрузить модель (Load Model) в  диалоговом окне Параметры поиска  решения и задайте диапазон, в который была записана схема поиска решения.

Рис. 18.29. Кнопки Сохранить модель и Загрузить модель позволяют сохранить,

а затем вновь загрузить параметры поиска решения

Для   того   чтобы   упростить   поиск   сохраненных   моделей,   присваивайте названия содержащим их диапазонам. Тогда для загрузки нужной модели поиска достаточно будет просто  ввести   имя  диапазона.   Процедура  создания  имен  для  диапазонов   и  ячеек рассматривается в разделе «Присвоение имен ячейкам и диапазонам» главы 12.

Сохранение результатов в виде сценария

Существует еще один способ записи параметров поиска решения — сохранение их в виде именованных сценариев. Как вы могли заметить, в диалоговом окне Результаты поиска решения,  представленном на  рис.  18.27,  есть  кнопка  Сохранить  сценарий  (Save Scenario).  Щелкните   на  этой  кнопке,  чтобы  назначить  имя  сценария  для  текущих значений в изменяемых ячейках. Записанный таким образом сценарий является отличной базой   для   проведения  дальнейшего   анализа   «что-если»,   основанного  на   подборе переменных.

СМОТРИ ТАКЖЕ

Подробнее о сценариях рассказывается в разделе «Диспетчер сценариев» данной главы.

Другие параметры поиска решения

Диалоговое окно Параметры поиска решения, изображенное на рис. 18.29, содержит ряд требующих некоторых пояснений элементов управления:

? Поля Максимальное время (Max Time) и Предельное число итераций (Iterations) позволяют ограничить  время решения  временем  работы  программы и  количеством итераций соответственно. Как  только  одно из этих условий выполнится, вычисления

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

? Поле Относительная погрешность (Precision) отвечает за  точность, то есть за то, насколько найденное решение должно быть  близко к заданному целевому значению при соблюдении всех ограничений. Чем ближе значение относительной погрешности к 1, тем  точность ниже. Однако если установить значение погрешности  меньше, чем 0,000001 (по умолчанию), это может привести к существенному увеличению времени вычисления.

? Параметр  Допустимое отклонение (Tolerance) используется  только  при  решении задач   с   целочисленными   значениями   и   определяет   в    процентном   отношении допустимую в решении ошибку.

? Если вы не знакомы с методами линейной оптимизации, то  переключатели Оценки (Estimates), Разности (Derivatives) и Метод поиска (Search) лучше оставить в  тех положениях,    в     которых    они    установлены    по    умолчанию.    Чтобы    получить дополнительную  информацию об этих параметрах, обратитесь к справочной системе Excel.

Линейная оптимизация

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

Флажок Линейная модель (Assume Linear Model) следует  устанавливать только для задач   линеаризации.   Например,   модели,   в   которых   присутствуют  исключительно операции вычитания или сложения, линейны по своей природе. Но в реальных ситуациях вы будете  иметь дело в  основном с нелинейными описаниями. В них входят  операции умножения    или    деления,    а    также    непропорциональные    функции,    такие    как экспоненциальные   и   логарифмические,   или    специализированные  функции   Excel, например ПЛТ.

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

СОВЕТ  

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

Просмотр промежуточных результатов

Если вы хотите видеть результаты подбора параметров не только по  факту завершения работы решателя, но и для других, промежуточных,  стадий, установите в  диалоговом окне Параметры поиска решения флажок Показывать результаты итераций (Show Iteration Results). Теперь после каждой итерации на экране будет появляться диалоговое окно Текущее состояние поиска решения (Show Trial Solution),  предлагающее вам выбор: сохранить  эти  параметры  в   виде сценария,  прекратить  поиск  решения  или продолжить итеративный процесс.

ПРИМЕЧАНИЕ

Следует учитывать, что значения, отображаемые в диалоговом окне  Текущее состояние поиска  решения,  могут  не  удовлетворять  всем  заданным  ограничениям  и  не  являться оптимальными.

Создание отчетов

После выполнения поиска оптимального решения можно.организовать  все  полученные данные  в  виде любого  из  трех  типов отчетов:  Результаты (Answer), Устойчивость (Sensitivity)  и  Пределы  (Limits).  Для  этого  требуется  выделить нужные  названия в диалоговом  окне  Результаты поиска решения и  нажать  в  нем  кнопку  ОК.  (Чтобы выделить  несколько   отчетов,  удерживайте  нажатой   клавишу   Ctrl.)   Каждый   отчет сохраняется на отдельном листе текущей книги.

Отчет по устойчивости

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

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

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

Отчет по результатам

Отчет  по  результатам  включает в  себя  целевую ячейку,  список  изменяемых  ячеек  и ограничений.  Дополнительно  в    нем   отображается   такая  информация,  как  статус (состояние)  и  строгость  каждого  из  ограничений.  Ограничение  может  находиться  в одном  из  трех  состояний:  Связанное  (Binding),  Несвязанное  (Not  Binding)  или Невыполненное (Not Satisfied). Строгость выражается в разности между фактическим значением в  ячейке ограничения при получении решения и числом, заданным в правой  части  выражения   ограничения.  Связанное  ограничение  (строгое,  сильное,  жесткое) требует совпадения значений (расхождение равно 0). Несвязанное  ограничение (мягкое,

слабое) — соблюденное с ненулевым значением разницы.

ПРИМЕЧАНИЕ

Если в диалоговом окне Параметры поиска решения установлен флажок Линейная модель,

доступным окажется только отчет по результатам.

Отчёт по пределам

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

Если решение не найдено

Инструмент , безусловно, мощное средство, но, к сожалению, не панацея, и  с  его  помощью  можно  решить  далеко  не   всякую задачу  по  оптимизации.  Если оптимальный  ответ  не  найден,   в   диалоговом  окне  Результаты  поиска  решения появляется извинение следующего вида:

? Поиск решения не может найти подходящего решения. Это означает, что решение не найдено, так как не удалось удовлетворить всем ограничениям. К подобной ситуации приводит   неаккуратная    постановка   задачи    —    она    возникает,   если    условия противоречат/друг другу или, по существу, несовместимы (например, когда требуется, чтобы рекламная кампания охватила 800 млн читателей при расходах в $1 000 000). В некоторых случаях данное сообщение выводится в ситуации, когда начальные значения в  изменяемых ячейках слишком нереалистичны. Если вы считаете, что  ограничения внутренне непротиворечивы и задача разрешима, попробуйте задать другие начальные значения в изменяемых ячейках и повторить процедуру поиска.

? Поиск остановлен (достигнуто максимальное число итераций).  Чтобы  не тратить зря время на решение потенциально неразрешимой  задачи, после достижения заданного количества итераций поиск приостанавливается, и на экране появляется это сообщение. Здесь можно щелкнуть на кнопке Продолжить (Continue) или на кнопке Стоп (Stop). В  первом случае  вычисления продолжаются  и  прерываются,  только  если  найдено оптимальное  решение,  исчерпано  отведенное   время  или  по  каким-либо  другим объективным  причинам.   Если   решение   требует   большего   количества  итераций, измените  значение  в  поле Предельное число итераций (Iterations) в  диалоговом окне Параметры поиска решения.

? Поиск  остановлен (истекло  заданное  на  поиск  время). Это  сообщение  аналогично предыдущему.   Вычисления   останавливаются    по   истечении   установленного  по умолчанию времени. Время поиска решения можно увеличить в поле Максимальное время (Max Time) диалогового окна Параметры поиска решения.

Источник: Эффективная работа: Microsoft Office Excel 2003 / М. Додж, К. Стинсон — СПб.: Питер, 2005. — 1088 с: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий