(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

Решение задач на оптимизацию с помощью MS Excel

Алексей Шмуйлович

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

Скачать детально разобранный пример решения оптимизационной задачи в Excel с использованием настройки Поиск решения

Модели всех задач на оптимизацию состоят из следующих элементов:

1. Переменные - неизвестные величины, которые нужно найти при решении задачи.

2. Целевая функция - величина, которая зависит от переменных и является целью, ключевым показателем эффективности или оптимальности модели.

3. Ограничения - условия, которым должны удовлетворять переменные.

Поиск решения такой модели рассмотрим на примере такого вопроса:

Издательский дом "Геоцентр-Медиа" издаст два журнала: "Автомеханик" и "Инструмент", которые печатаются в трех типографиях: "Алмаз-Пресс", "Карелия-Принт" и "Hansaprint" (Финляндия), где общее количество часов, отведенное для печати и производительность печати одной тысячи экземпляров ограничены и представлены в следующей таблице:

Спрос на журнал "Автомеханик" составляет 12 тысяч экземпляров, а на журнал "Инструмент" -не более 7,5 тысячи в месяц.
Определите оптимальное количество издаваемых журналов, которое обеспечит максимально выручку от продажи.

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

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

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

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

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

Еще одно важное ограничение, о котором обычно забывают - переменные должны быть неотрицательными. 

Попытаемся представить модель в Excel.

Переменные, то есть объем тиража, находятся в ячейках B10:C12. Целевая функция - в ячейке D13. Обратите внимание, целевая функция построена формулой, ссылаясь на ячейки с переменными и исходные данные (стоимость единицы тиража).

Также формулами подсчитывается фактическое время печати тиража в каждой из типографий (ячейки E3:E5).

Все готово, приступаем решению задачи с помощью надстройки.

Включается она через меню Сервис - Поиск решений. Если такого пункта меню нет, войдите в меню Сервис - Надстройки и отметьте галочкой соответствующую надстройку. Может понадобиться установочный комплект Office.

Перед Вами появится следующий диалог:

Здесь указываем адрес целевой ячейки, отмечаем, что ее нужно привести к максимальному значению, изменяя ячейки $B$10:$C$12. Диапазоны можно указывать мышью - станьте в нужное поле диалога и выделите на листе нужные ячейки. Адрес автоматически попадет в диалог.

Добавляем ограничения. После нажатия кнопки Добавить появляется диалог:

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

Для Алмаз-Пресс ограничение будет таким E3 ≤ D3. В ячейке E3 должна быть формула суммы продолжительности печати тиража первого и вторго журналов в этой типографии, полученной перемножением тиража на норму времени.

Думаю, понятно, как ввести в диалог описанное ограничение.

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

Ограничения неотрицательности можно также задать с помощью этого диалога - для каждой ячейки с объемом тиража установить ограничение ≥0.

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

Здесь достаточно отметить галочку Неотрицательные значения.

Все модель готова к расчету:

Нажимаем Выполнить.

Через пару секунд Вы будете иметь оптимальное решение.

Теперь выберите Сохранить решение и нажмите Ok.

Можете проверить решение, пробуя подставлять другие значения тиража, перераспределяя тираж между типографиями. Вряд ли Вам удастся улучшить результат.

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

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

Ссылки по теме

Файлы для загрузки


 Распечатать »
 Правила публикации »
  Обсудить материал в конференции Microsoft » [2]
Обсудить материал в конференции Дизайн, графика, обработка изображений »
Написать редактору 
 Рекомендовать » Дата публикации: 26.06.2007 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
• Антивирус • Защита рабочих станций, клиентов терминальных и виртуальных серверов, клиентов встроенных систем. Dr.Web Desktop Security Suite предоставляет всестороннюю, комплексную защиту корпоративных сетей от вирусов, троянов и других web-угроз... Купить Антивирус Dr.Web Desktop Security Suite для рабочих станций
Программное обеспечение, службы, поддержка — Платформы MSDN предоставляет ИТ-организациям и разработчикам Java доступ к широкому спектру ресурсов, которые помогут организациям добиться успеха без увеличения затрат на Visual Studio. Microsoft MSDN Platforms All Lng License/Software Assurance Pack OPEN 1 License No Level Qualified
Электронный ключ Microsoft Office Mac Home Business 2011. Язык интерфейса - Английский. Только для установки и использования на одном компьютере Mac. Лицензию нельзя перенести на другой компьютер Mac. Операционная система: Mac OS X версии 10.5.8 и... Купить Microsoft Office Mac Home Business 1 PK 2011 English, полная версия, электронный ключ
Коробочная версия Microsoft Visual Studio Professional 2012, Русская, лицензия для одного разработчика. Microsoft Visual Studio Professional 2012 Russian 1 License Russia Only DVD
Электронная лицензия Microsoft Office Multi Language Pack 2013. Для коммерческого использования. Microsoft Office Standard Mac 2011 включает в себя несколько существенных обновлений, в том числе ленточный интерфейс, который сегодня является стандартным... Купить Microsoft Office Mac Standard 2011 Russian OPEN 1 License No Level
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
CubeX Duo - имеет 2 печатающие головки, в отличие от CubeX, что позволяет одновременно печатать двумя цветами, но при этом снизилась область построения (230 × 265 × 240 мм). CubeX Duo
Воспользуйтесь новыми стандартами в трехмерной печати! Четвертое поколение принтеров от компании MakerBot является лучшим настольным 3D принтером, предлагаемым в настоящее время на рынке.  С разрешающей способностью в 100 микрон и объемом печати 6700... MakerBot Replicator 2
Новый PICASO 3D Designer 1.2. PICASO 3D Designer (Красный)
С 3D принтером PICASO 3D Designer вы сможете создать свою собственную уникальную реальность... PICASO 3D Designer
Такой же белый, как падающий снег или свежо пастеризованное молоко или свежевыкрашенный забор. Этот цвет ярче, чем натуральный. С этим цветом вы сможете достигнуть нового уровня яркости моделей. Катушка ABS-пластика Myriwell 1.75 мм 1кг., белая
 
Другие предложения...
 
Книжный магазин   WWW.ITSHOP.RU
Книга позволит быстро и эффективно освоить редактирование фотографий с помощью Adobe Photoshop CS4. Рассматривается решение самых востребованных и распространенных задач коррекции, ретуши и монтажа фотографий, создания панорамных снимков, исправления... Photoshop CS4 - это просто. Экспресс-методы обработки фотографий (+ DVD)
В книге показано, как вопросы архитектурного моделирования решаются с помощью пакета 3ds Max. Рассматривается создание геометрий, в том числе декора, особенности моделирования и представления ландшафтов, проблемы расчета инсоляции зданий, детальное... Архитектурное моделирование в 3ds Max + Видеокурс (+ CD-ROM)
В учебном пособии рассматриваются основы объектно-ориентированного подхода и простейшие примеры создания приложений с графическим интерфейсом (GUI) в среде визуального программирования Visual C++; приведен обзор наиболее часто используемых... Языки и методы программирования. Создание простых GUI-приложений с помощью Visual С++
Данная книга является наилучшим руководством для программистов, желающих научится создавать приложения для мобильной платформы Android. Она представляет собой практический курс по написанию программного обеспечения на базе Android 4 SDK. Все... Android 4. Программирование приложений для планшетных компьютеров и смартфонов
Книга посвящена разработке Web-приложений для платформы Google App Engine и на основе фреймворка Google Web Toolkit на языке программирования Java и с использованием среды разработки Eclipse. Рассмотрено создание проектов и запуск GWT-приложений и... Google App Engine Java и Google Web Toolkit: разработка Web-приложений
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Adobe Photoshop: алхимия дизайна
Delphi - проблемы и решения
eManual - электронные книги и техническая документация
Новости мира 3D-ускорителей
 
Рассылки Maillist.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Adobe Photoshop - уроки для каждого
Delphi - проблемы и решения
eManual - электронные книги и техническая документация
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
Пишу программы на заказ профессионально (1199)
Пишу программы на заказ на языках Pascal (численные методы, списки, деревья, прерывания) под...
 
Программы Delphi на заказ (131)
Пишу программы в среде Delphi на заказ http://bddelphi.ucoz.ru/
 
AutoCAD помощь чайнику (157)
Здравствуйте, Я только начал осваивать AutoCAD. Слышал такое определение как лимиты чертежа,...
 
70-671 экзмен на русском языке. (356)
Уже в третий раз пытался сдать экзамен MSP 70-671 на русском языке и все без результатно,...
 
автокад для чайников (42)
Добрый день! Возник вопрос по чертежам для инженерной графики на 1 курсе МГСУ. Все чертят...
 
 
 



    
rambler's top100 Rambler's Top100