(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
Продукт Team Foundation Server Express предоставляется БЕСПЛАТНО и предлагает небольшим группам, содержащим не более пяти разработчиков, средства управления исходным кодом, отслеживания рабочих элементов и автоматизации сборок для программных... Microsoft Visual Studio Team Foundation Server Express 2013 с обновлением 2 - Русский
Электронная лицензия Microsoft Office Multi Language Pack 2013. Для коммерческого использования. Пакет многоязыковой поддержки Office Multi-Language Pack адресован пользователям, которые создают или редактируют документы на нескольких языках.... Купить Microsoft Office Multi Language Pack 2013 Sngl OPEN 1 License No Level
Электронный ключ для обновления Windows 8.1 до Windows 8.1 Professional. Доставка в течении 10 минут. Microsoft Windows 8.1 Professional, upgrade c Windows 8.1, электронный ключ
•• Word 2013 •• Excel 2013 •• PowerPoint 2013 •• OneNote 2013 •• Outlook 2013 •• включает Microsoft Office для дома и бизнеса 2013. Программное обеспечение Microsoft Office для дома и бизнеса 2013 предназначено для пользователей, работающих из дома .... Купить Microsoft Office для Дома и Бизнеса 2013 Russian, полная версия, электронный ключ
Электронный ключ Microsoft Office для Дома и Бизнеса 2013. Язык интерфейса - Английский. Для установки и использования на 1 ПК. Операционная система: Windows 7, Windows Vista, Windows XP с пакетом обновления 3 (SP3), Windows Server 2003 с SP2 и MSXML... Купить Microsoft Office Home and Business 2013 English, полная версия, электронный ключ
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
3D принтер ProJet 1500 – персональный принтер, использующий технологию FTI, также как V-flash. Он также применяет пластик в качестве модельного материала. Его главное отличие от V-flash – работает материалами шести цветов, что позволяет получать... 3D принтер ProJet 1500
PrintBox3D One - новый российский 3D принтер, спроектированный с участием лучших инженеров по ЧПУ оборудованию. Благодаря этому точность печати достигает 50 мкм! Это в два раза меньше, чем размер пикселя экрана Apple iPhone 4! PrintBox3D One
Новый PICASO 3D Designer 1.2. PICASO 3D Designer (Желтый)
Makerbot Replicator персональный 3D-принтер с возможностью двухцветной печати, который позволяет печатать довольно крупные вещи! MakerBot Replicator 2-head
Воспользуйтесь новыми стандартами в трехмерной печати! Четвертое поколение принтеров от компании MakerBot является лучшим настольным 3D принтером, предлагаемым в настоящее время на рынке.  С разрешающей способностью в 100 микрон и объемом печати 6700... MakerBot Replicator 2
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Утиль - лучший бесплатный софт для Windows
Краткие описания программ и ссылки на них
CASE-технологии
Один день системного администратора
 
Рассылки Maillist.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Новости ITShop.ru - ПО, книги, документация, курсы обучения
SoftParad - обзоры лучших программ и утилит
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
Помощь по MS Access (282)
Доброе время суток. Случайно оказался на этом сайте, искал статьи по OLAP. Вижу, что...
 
70-671 экзмен на русском языке. (360)
Уже в третий раз пытался сдать экзамен MSP 70-671 на русском языке и все без результатно,...
 
Служба Windows Installer (285)
При очередной установке С++Builder выскочила ошибка: Не удается получить доступ к сужбе Windows...
 
70-672 (14)
Ребята, дайте пожалуйста ДАМП на Майкрософт 070-672 экзамен, желательно на русском...
 
Где можно найти «Пакет анализа» для Excel ? (53)
Коллеги, подскажите, где можно скачать надстройку к Excel под названием «Пакет анализа», после...
 
 
 



    
rambler's top100 Rambler's Top100