Excel для математиков

Параметры, управляющие работой Решателя

Рассмотрим возможности управления работой Решателя, задаваемые в окне Параметры (Options):

• Максимальное время (MaxTime) - ограничивает время, отведенное на процесс поиска решения. По умолчанию задано 100 секунд, что обычно достаточно для задач небольшой размерности, имеющих около 10 ограничений. Для задач большой размерности придется это значение увеличивать.
• Предельное число итераций (Iterations) - еще один способ ограничения времени поиска путем задания максимального числа итераций. По умолчанию задано 100, но это число можно увеличивать до 32767. Чаще всего, если решение не получено за 100 итераций, надежд получить его при увеличении этого значения мало. Лучше попытаться изменить начальное приближение и запустить процесс поиска заново.
• Относительная погрешность (Precision) - задает точность выполнения ограничений. Иногда проще изменить ограничение, отодвинув границу, чем пытаться выполнить ограничения с высокой точностью.
• Сходимость (Convergence) - задается десятичной дробью, меньшей единицы, позволяя остановить процесс поиска при сходимости решения к неподвижной точке, когда относительные изменения в течение последних 5 итераций не превышают заданную дробь.
• Линейная модель (Assume Linear Model) - этот флажок следует включать, когда целевая функция и ограничения - линейные функции. Эта дополнительная информация позволяет Решателю упростить процесс поиска решения.
• Неотрицательные значения (Assume Non-Negative) - этим флажком можно задать ограничения на переменные, что позволит искать решения в положительной области значений, не задавая специальных ограничений на их нижнюю границу.
• Показывать результаты итераций (Show Iteration Results) - флажок, позволяющий включить пошаговый процесс поиска, показывая на экране результаты каждой итерации. В сложных ситуациях, когда Решатель не находит решения автоматически, рекомендуется включать этот флажок, так как иногда можно найти точку, от которой процесс поиска уклонился в сторону.
• Автоматическое масштабирование (Use Automating Scaling) - флажок автоматического изменения масштаба следует включать, когда масштаб значений входных переменных и целевой функции и ограничений отличается, возможно, на порядки. Например, переменные задаются в штуках, а целевая функция, задающая суммарную стоимость, измеряется в миллионах рублей.
• Относительная погрешность (Tolerance) - задается в процентах. Указанное значение имеет смысл только для задач с целочисленными ограничениями. Решатель в таких задачах вначале находит оптимальное не целочисленное решение, а потом пытается найти ближайшую целочисленную точку, решение в которой отличалось бы от оптимального не более чем на указанное данным параметром количество процентов. Если такая точка найдена, Решатель сообщает об успехе. При большом допуске (по умолчанию 5%) может быть потеряно лучшее целочисленное решение, правда, отличающееся от найденного Решателем в пределах допуска. Для целочисленных задач допуск имеет смысл уменьшить, что я и сделал при решении транспортной задачи. Хочу еще раз обратить внимание на эту особенность решения задач целочисленного программирования. Если значение параметра Tolerance задать большим, то Решатель может остановиться раньше времени, не найдя лучшего целочисленного решения. Если же его взять малым, то наилучшее целочисленное решение будет отличаться от оптимального нецелочисленного решения на величину большую, чем ту, которая задается параметром Tolerance. В этом случае формально решение заканчивается неуспехом, поскольку найденное решение не удовлетворяет всем требованиям. Конечно, параметр Tolerance играет служебную роль, и "умный" Решатель, найдя наилучшее целочисленное решение, должен был бы уведомлять, что решение найдено, но ограничение по Tolerance не выполнено. Этого, однако, не происходит. Мы еще столкнемся с этой ситуацией при рассмотрении следующей задачи.
• Сохранить модель (Save Model) - командная кнопка; позволяет открыть диалоговое окно, где можно указать имя сохраняемой модели. Имеет смысл использовать эту возможность, когда на рабочем листе несколько моделей, так как единственная модель запоминается автоматически.
• Загрузить модель (Load Model) - позволяет загрузить одну из сохраненных моделей.
• Есть еще несколько более специальных параметров, которыми можно управлять, варьируя процедурами, применяемыми в процессе поиска. К ним следует прибегать в тяжелых ситуациях, когда решение найти не удается.

Задача о краске

Мне нравится рассказывать об этой задаче, которая принадлежит В. Очкову, и взята "из жизни" в период середины 90-х годов, когда бартер играл основную роль при расчетах. С тех пор ситуация в экономике существенно изменилась в лучшую сторону, хотя до нормы еще далеко. При описании содержательной постановки я хочу сохранить весь внешний "антураж". Заметьте, цены в те годы примерно на 4 порядка отличались от нынешних. Суть задачи такова. Коллектив программистов под руководством В. Очкова написал программу и продал ее за 14 миллионов лакокрасочному предприятию. "Живых" денег предприятие не платило, но могло в пределах указанной суммы расплатиться краской - своей продукцией. Институт, в котором работал В. Очков, нуждался в краске и готов был оплатить привезенную краску. Краска выпускалась в двух видах тары - больших и малых банках (барабанах), емкость которых составляла 55 и 15 литров, а стоимость пустых барабанов составляла - 30 и 24 тысячи рублей. Литр краски стоит 14 600 рублей. Спрашивается, сколько больших и сколько малых барабанов следует взять, если учесть, что институт заинтересован получить как можно больше краски, а создатель программы скорее заинтересован в том, чтобы как можно меньше денег оставить "лакокрасочникам". Типичная оптимизационная задача!

Для решения этой задачи (кстати, это задача с целочисленными переменными) автор использовал Решатель. Благо Excel уже тогда можно было найти повсюду, в том числе и в бухгалтерии лакокрасочного завода. В поставленной Решателю задаче требовалось максимизировать объем получаемой краски при естественном ограничении на сумму имеющихся денег и ограничений целочисленности на искомые переменные - число больших и малых барабанов. Используя установки параметров, принятые по умолчанию, найденный Решателем "оптимальный" ответ гласил, следует взять 2 малых и 16 больших барабанов с краской. Это давало 910 литров краски, но оставляло предприятию 186000 рублей. Возможно, остаток денег был большой, но В. Очков не поверил Решателю и стал искать другой ответ. И нашел лучшее решение. Оно таково: следует взять 15 больших и 6 малых барабанов. При этом краски будет больше (915 л), а денег, заработанных автором, у завода останется значительно меньше (47 000 руб.). Надеюсь, Вы понимаете, что второе решение было получено за счет разумного управления параметрами Решателя. Я напомню, что когда решается задача с целочисленными ограничениями на переменные, вначале ищется решение без учета таких ограничений. Здесь такое оптимальное решение можно найти самому, поскольку понятно, что максимизации объема можно добиться, закупая краску в больших банках. Наша сумма денег позволяет взять примерно 16,8 больших барабанов, т. е. около 924 л. Но брать неполные банки нельзя и, следовательно, нужно искать целочисленное значение. В окрестности найденного оптимального решения Решатель ищет ближайшую целочисленную точку. Значение целевой функции в ней не должно отличаться более чем на величину, заданную параметром Tolerance. Напомним, по умолчанию это значение равно 5%, что в данном примере немало: 46,2 л. Первое решение, выданное Решателем, вполне укладывается в допустимые рамки. Если уменьшить значение параметра Tolerance до 1%, Решатель найдет другое, действительно оптимальное целочисленное решение.

История с "оптимальными" решениями имеет продолжение. Рассмотрим еще одно решение с другой целевой функцией, в котором минимизировалась сумма денег, оставляемая заводу. В этом решении следует взять 37 малых и только 6 больших барабанов. Тогда заводу останется только 11 000 руб., так что краска будет закуплена почти на все заработанные автором деньги. Правда, краски будет меньше, - всего 885 литров. Но! Когда это решение было показано тем, кому предназначалась краска, они также признали его наилучшим. С большими барабанами больше возни, и краски на переливах из большого барабана теряется больше. Если ввести дополнительные ограничения на потери, то нормальное решение 37 и 6 может быть получено и при максимизации объема закупаемой краски. Обратите внимание, как, решая, по сути, одну и ту же оптимизационную задачу, можно найти принципиально разные решения. Многое зависит от удачной постановки, выбора критериев, ограничений. Немаловажное значение имеет и умение управлять параметрами алгоритмов оптимизации.

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

Рис. 2.24. Две постановки и два решения задачи о красках

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

увеличить изображение
Рис. 2.25. Минимизация остатка денег