(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
Электронный ключ Microsoft Office Mac для Дома и Бизнеса 2011. Язык интерфейса - Русский. Только для установки и использования на одном компьютере Mac. Лицензию нельзя перенести на другой компьютер Mac. Операционная система: Mac OS X версии 10.5.8 и... Купить Microsoft Office Mac для Дома и Бизнеса 2011 Русский, полная версия, электронный ключ
Русская версия Windows 8 . Version Upgrades (VUP) — обновление имеющейся версии программного продукта Microsoft на текущую версию. Коробочная версия продукта Version Upgrades (VUP) — обновление. Позволяет обновить компьютер с Windows 7 (не... Microsoft Windows 8 Russian, апгрейд с предыдущих версий Windows, коробка
Microsoft BizTalk Server Developer 2013 лицензируется на пользователя только для разработки и тестирования. Microsoft BizTalk Server Developer 2013 Sngl OPEN 1 License No Level
Электронный ключ, подписка на 1 год Office 365 для дома расширенный (Home Premium). Язык интерфейса - Все языки. Продукт не предназначен для коммерческого использования. Идеальное решение для семей, имеющих дома до 5 ПК или планшетов. Подписка на 1... Microsoft Office 365 для дома расширенный, русский. 5 ПК или MAC. Электронный ключ на 1 год.
Русская версия. Полнофункциональная коробочная версия продукта. Комплект поставки включает коробку, сертификат подлинности СОА (наклеен на коробку, на нем указано наименование продукта), Лицензионное соглашение конечного пользователя EULA (часто... Microsoft Windows 8.1 Russian, полная коробочная версия
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
CubeX - следующее обновление предшественника Cube. Теперь в принтере увеличена камера построения (275 × 265 × 240 мм). CubeX
Воспользуйтесь новыми стандартами в трехмерной печати! Четвертое поколение принтеров от компании MakerBot является лучшим настольным 3D принтером, предлагаемым в настоящее время на рынке.  С разрешающей способностью в 100 микрон и объемом печати 6700... MakerBot Replicator 2
Sense имеет гибкую область сканирования и может захватить все, от кекса до  человека в полный рост, обрабатывая данные за секунды и мгновенно формируя файл для 3D печати. 3D сканер SENSE
Новые стандарты в персональной 3D печати! Профессиональный персональный 3D-принтер MakerBot Replicator устанавливает новые стандарты в мире 3D-печати. Область печати на 11% больше, чем у MakerBot Replicator 2. MakerBot Replicator 5th GEN
Пластик этого цвета был специально создан по нашей спецификации, и он получился ярким, кричащим и блестящим на солнце. Также он отлично выглядит при люминесцентном освещении. Мы потратили много времени, чтобы добиться этого результата и пользователи... Катушка ABS-пластика Myriwell 1.75 мм 1кг., красная
 
Другие предложения...
 
Книжный магазин   WWW.ITSHOP.RU
Перед вами официальный учебный курс по программе Adobe Flash CS6, входящий в знаменитую серию «Classroom in a Book». Эта полноцветная книга предназначена как начинающим, так и опытным пользователям, желающим получить от Adobe Flash максимум... Adobe Flash CS6 (+ CD-ROM)
Настоящая работа вводит понятия общей теории имитационного моделирования и предназначена, прежде всего, для специалистов широкого спектра отраслей хозяйства, не являющихся программистами или математиками, но желающих применить самую современную... Имитационное моделирование. Учебное пособие
Рассмотрен широкий круг практических вопросов по программированию в Delphi: трюки и хитрости использования стандартных компонентов, готовые решения для повседневных задач, работа с СУБД (ORACLE, Interbase/Firebird, MySQL, MS SQL Server),... Delphi. Народные советы (+ CD-ROM)
Программа AutoCAD уже многие годы является одним из наиболее мощных и широко распространенных инструментов проектирования. Данная книга представляет собой превосходное практическое руководство по AutoCAD 2014 - новейшей версии программы. Издание... AutoCAD 2014 ( + CD с видеокурсом) (+ CD-ROM)
CorelDRAW X5 — это векторный графический редактор, который заслуженно считается одним из наиболее популярных среди пользователей всего мира вот уже несколько десятилетий. Это мощный программный продукт, содержащий колоссальное количество возможностей... CorelDRAW X5. Понятный самоучитель
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Утиль - лучший бесплатный софт для Windows
Windows и Office: новости и советы
eManual - электронные книги и техническая документация
3D и виртуальная реальность. Все о Macromedia Flash MX.
 
Рассылки Maillist.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Новости ITShop.ru - ПО, книги, документация, курсы обучения
MS Windows и MS Office
eManual - электронные книги и техническая документация
3Ds max. Вопросы и ответы
SoftParad - обзоры лучших программ и утилит
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
70-671 экзмен на русском языке. (357)
Уже в третий раз пытался сдать экзамен MSP 70-671 на русском языке и все без результатно,...
 
Помощь по MS Access (270)
Доброе время суток. Случайно оказался на этом сайте, искал статьи по OLAP. Вижу, что...
 
Служба Windows Installer (280)
При очередной установке С++Builder выскочила ошибка: Не удается получить доступ к сужбе Windows...
 
Где можно найти «Пакет анализа» для Excel ? (53)
Коллеги, подскажите, где можно скачать надстройку к Excel под названием «Пакет анализа», после...
 
генератор ключей активации для WinXP (13)
Хотелось бы у вас узнать использование программы MiCROSOFT XP Suite KEYGEN Считается...
 
 
 



    
rambler's top100 Rambler's Top100