Автор: Пользователь скрыл имя, 05 Декабря 2010 в 11:23, лекция
Надстройка MS Excel "Поиск решений" позволяет решать широкий круг задач на оптимизацию. Думаю, многие посетители сайта изучали в институте линейное программирование или исследования операций. "Поиск решений" в Excel позволяет в считанные секунды находить оптимальные решения достаточно сложных моделей, кстати не только линейных, без знания алгоритмов и длительных рутинных итерраций.
| Решение задач на оптимизацию с помощью MS Excel |
| Написал Алексей Шмуйлович |
| 29.05.2007 |
| Надстройка
MS Excel "Поиск решений"
позволяет решать широкий
круг задач на оптимизацию.
Думаю, многие посетители
сайта изучали в институте
линейное программирование
или исследования операций. "Поиск
решений" в Excel позволяет
в считанные секунды
находить оптимальные
решения достаточно
сложных моделей, кстати
не только линейных,
без знания алгоритмов
и длительных рутинных
итерраций.
Оптимизационные
модели широко используются в экономике
и технике. Среди них задачи подбора
сбалансированного рациона Модели всех задач на оптимизацию состоят из следующих элементов: 1. Переменные
- неизвестные величины, которые
нужно найти при решении 2. Целевая функция - величина, которая зависит от переменных и является целью, ключевым показателем эффективности или оптимальности модели. 3. Ограничения
- условия, которым должны Поиск решения такой модели рассмотрим на примере вопроса, поступившего на форум. Итак, сам вопрос: Издательский
дом «Геоцентр-Медиа»
издает два журнала:
«Автомеханик» и «Инструмент»,
которые печатаются
в трех типографиях:
«Алмаз-Пресс», «Карелия-Принт»
и «Hansaprint» (Финляндия),
где общее количество
часов, отведенное для
печати и производительность
печати одной тысячи
экземпляров ограничены
и представлены в следующей
таблице: Давайте разберемся, что здесь является переменными, что целевой функцией, что ограничениями. Найти нам необходимо оптимальное количество издаваемых журналов каждого вида. А издавать их можно в трех типографиях на разных условиях. Вот и получается, что нам необходимо определить размер тиража каждого журнала напечатанного в каждой типографии. Это и будут наши переменные. По какому принципу их подбирать, что считать эффективным, что нет. Перед нами поставлена задача получить максимальную выручку. Таким образом, цель - максимальная выручка. Теперь ограничения. В условиях сказано, что каждая типография может выделить на наш тираж только определенное время. Длительность печати тысячи единиц тиража каждого журнала каждой типографией известна. Таким образом, произведение объема тиража на длительность печати тысячи единиц для каждой типографии не может быть больше заданного количества времени. Еще одно важное ограничение, о котором обычно забывают - переменные должны быть неотрицательными. Попытаемся представить модель в Excel.
Переменные, то есть объем тиража, находятся в ячейках B10:C12. Целевая функция - в ячейке D13. Обратите внимание, целевая функция построена формулой, ссылаясь на ячейки с переменными и исходные данные (стоимость единицы тиража). Также формулами подсчитывается фактическое время печати тиража в каждой из типографий (ячейки E3:E5). Все готово, приступаем решению задачи с помощью надстройки. Включается она через меню Сервис - Поиск решений. Если такого пункта меню нет, войдите в меню Сервис - Надстройки и отметьте галочкой соответствующую надстройку. Может понадобиться установочный комплект Office. Перед Вами появится следующий диалог:
Здесь указываем адрес целевой ячейки, отмечаем, что ее нужно привести к максимальному значению, изменяя ячейки $B$10:$C$12. Диапазоны можно указывать мышью - станьте в нужное поле диалога и выделите на листе нужные ячейки. Адрес автоматически попадет в диалог. Добавляем ограничения. После нажатия кнопки Добавить появляется диалог:
Вспоминаем. У нас фактическое время печати тиража в каждой типографии не может превышать заданного лимита. Для Алмаз-Пресс ограничение будет таким E3 <= D3. В ячейке E3 должна быть формула суммы продолжительности печати тиража первого и вторго журналов в этой типографии, полученной перемножением тиража на норму времени. Думаю, понятно, как ввести в диалог описанное ограничение. Если нажать Ок, ограничение будет добавлено, а диалог закроется. Чтобы несколько раз не открывать диалог, сделана кнопка Добавить. Ограничение сохраняется, а диалог очищается для добавления следующего ограничения. Аналогично добавляем ограничения для оставшихся типографий. Ограничения неотрицательности можно также задать с помощью этого диалога - для каждой ячейки с объемом тиража установить ограничение >=0. Но учитывая,
что такие ограничения Здесь достаточно отметить галочку Неотрицательные значения. Все модель готова к расчету:
Нажимаем Выполнить. Через пару секунд Вы будете иметь оптимальное решение. Теперь выберите Сохранить решение и нажмите Ok. Можете проверить решение, пробуя подставлять другие значения тиража, перераспределяя тираж между типографиями. Вряд ли Вам удастся улучшить результат. Конечно, результат не стопроцентный. Бывают слишком сложные модели, модели совсем не имеющие решений (модели с несходимыми ограничениями). Кроме того, если Вы обратили внимание, в параметрах модели можно задать максимальное время решения, число итерраций, точность и другие установки. Но это для профессионалов. Что радует, все настройки модели (целевая ячейка, область переменных, ограничения, параметры) сохраняются в книге и при изменении исходных данных их не нужно вводить заново. Достаточно открыть надстройку и запустить повторный поиск решения. |
Поиск параметра.
Специальная функция Goal Seek (Подбор параметра) позволяет определить параметр (аргумент) функции если известно ее значение. При подборе параметра значение влияющей ячейки (параметра) изменяется до тех пор, пока формула, зависящая от этой ячейки, не возвратит заданное значение.
Рассмотрим процедуру поиска параметра на простом примере: решим уравнение 10 * x - 10 / x = 15. Здесь параметр (аргумент) - x. Пусть это будет ячейка A3. Введем в эту ячейку любое число, лежащее в области определения функции (в нашем примере это число не может быть равно нулю). Это значение будет использовано в качестве начального. Пусть это будет 3. Введем формулу =10*A3-10/A3, по которой должно быть получено требуемое значение, в какую-либо ячейку, например, B3. Теперь можно запустить функцию поиска параметра, выбрав команду Goal Seek (Подбор параметра) в меню Tools (Сервис). Введите параметры поиска:
По окончании работы функции на экране появится окно, в котором будут отображены результаты поиска. Найденный параметр появится в ячейке, которая была для него зарезервирована. Обратите внимание на тот факт, что в нашем примере уравнение имеет два решения, а параметр подобран только один - это происходит потому, что параметр изменяется только до тех пор, пока требуемое значение не будет возвращено. Первый найденный таким образом аргумент и возвращается к нам в качестве результата поиска. Если в качестве начального значения в нашем примере указать -3, тогда будет найдено второе решение уравнения: -0,5.
Достаточно сложно правильно определить наиболее подходящее начальное значение. Чаще мы можем сделать какие-либо предположения об искомом параметре, например, параметр должен быть целым (тогда мы получим первое решение нашего уравнения) или неположительным (второе решение).
Задачу поиска параметра при налагаемых граничных условиях поможет решить специальная надстройка Microsoft Excel Solver (Поиск решения).
Поиcк решения.
Надстройка Microsoft Excel Solver (Поиск решения) не устанавливается автоматически при обычной установке:
Процедура поиска
решения позволяет найти
В диалоговом окне Solver (Поиск решения) так же, как и в диалоговом окне Goal Seek (Подбор параметра), необходимо указать целевую ячейку, ее значение и ячейки, которые следует изменять для достижения цели. Для решения задач оптимизации целевую ячейку следует указать равной максимальному или минимальному значению.
Если Вы щелкните на кнопке Guess (Предположить), Excel сам попытается найти все ячейки, влиящие на формулу.
Вы можете добавить граничные условия, кликнув на клавише Add (Добавить).
Кликнув на кнопке Options (Параметры), можно изменить условия поиска решения: максимальное время поиска решения, количество итераций, точность решения, допуск на отклонение от оптимального решения, метод экстраполяции (линейная или квадратичная), алгоритм оптимизации и т.д.
Вернемся к предыдущему примеру: для того, чтобы получить второе (неположительное) решение, достаточно добавить граничное условие A3<=-0.01. Так же как и при подборе параметра, на экране появится окно, в котором будет отображен отчет о результатах поиска требуемого решения. Само решение будет показано в предназначенных для него ячейках (в ячейке A3 отобразится значение -0.50).
Надстройка Microsoft Excel Solver (Поиск решения) позволяет, также, решать системы уравнений или неравенств. Рассмотрим простой пример: попробуем решить систему уравнений
x
+ y = 2
x - y = 0
Результаты поиска отобразятся в предназначенных для решения ячейках (A1:A2), отчет о результатах появится на экране.
Как Вы уже знаете, формулы в Microsoft
Excel позволяют определить значение
функции по ее аргументам. Однако может
возникнуть ситуация, когда значение
функции известно, а аргумент требуется
найти (т.е. решить уравнение). Для решения
подобных проблем предназначена специальная
функция Goal
Seek (Подбор параметра).
Поиск параметра.
Специальная функция Goal Seek (Подбор параметра) позволяет определить параметр (аргумент) функции если известно ее значение. При подборе параметра значение влияющей ячейки (параметра) изменяется до тех пор, пока формула, зависящая от этой ячейки, не возвратит заданное значение.
Рассмотрим процедуру поиска параметра на простом примере: решим уравнение 10 * x - 10 / x = 15. Здесь параметр (аргумент) - x. Пусть это будет ячейка A3. Введем в эту ячейку любое число, лежащее в области определения функции (в нашем примере это число не может быть равно нулю). Это значение будет использовано в качестве начального. Пусть это будет 3. Введем формулу =10*A3-10/A3, по которой должно быть получено требуемое значение, в какую-либо ячейку, например, B3. Теперь можно запустить функцию поиска параметра, выбрав команду Goal Seek (Подбор параметра) в меню Tools (Сервис). Введите параметры поиска:
Информация о работе Решение задач на оптимизацию с помощью MS EXEL