(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 Visual Studio Professional MSDN 3013 — это интегрированная среда разработки профессионального качества, которая упрощает задачи создания, отладки и развертывания приложений для устройств и платформ... Microsoft Visual Studio Pro w/MSDN Retail 2013 Russian Programs 1 License Russia Only Medialess Renewal
Microsoft Visio профессиональный 2013 (Microsoft Visio Professional 2013) позволяет отдельным пользователям и группам с невиданной легкостью создавать профессионально оформленные разноплановые схемы, доступно представляющие сложную.... Microsoft Visio Professional 2013 Russian OPEN 1 License No Level
Электронный ключ Microsoft Office для Дома и Учебы 2013. Язык интерфейса - Русский. Для установки и использования на 1 ПК. Не предназначен для коммерческого использования. Срок поставки - в течении 1 дня. Купить Microsoft Office для Дома и Учебы 2013 Russian, полная версия, электронный ключ
Microsoft Office 365 для среднего бизнеса. Подписка на 1 год. Цена за 1 лицензию. АКЦИЯ до 30 сентября. Office 365 для среднего бизнеса – это идеальное решения для предприятий с числом сотрудников до 250 человек. Лицензия для 1 пользователя... Офис 365 для среднего бизнеса
Электронный ключ Microsoft Office Mac Home Business 2011. Язык интерфейса - Английский. Только для установки и использования на одном компьютере Mac. Лицензию нельзя перенести на другой компьютер Mac. Операционная система: Mac OS X версии 10.5.8 и... Купить Microsoft Office Mac Home Business 1 PK 2011 English, полная версия, электронный ключ
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
Мировая классика, этот цвет один из самых темных и самый черный цвет в мире. Мы долго трудились над тем, чтобы сделать его наиболее насыщенно черным. Катушка ABS-пластика Myriwell 1.75 мм 1кг., черная
Новый PICASO 3D Designer 1.2. PICASO 3D Designer (Красный)
Пластик этого цвета был специально создан по нашей спецификации, и он получился ярким, кричащим и блестящим на солнце. Также он отлично выглядит при люминесцентном освещении. Мы потратили много времени, чтобы добиться этого результата и пользователи... Катушка ABS-пластика Myriwell 1.75 мм 1кг., красная
Makerbot Replicator персональный 3D-принтер с возможностью двухцветной печати, который позволяет печатать довольно крупные вещи! MakerBot Replicator 2-head
Купить Picaso 3D Builder - первый российский доступный 3D принтер для работы в офисе, дома, на производстве, в школе и в творческих мастерских! Picaso 3D Builder
 
Другие предложения...
 
Книжный магазин   WWW.ITSHOP.RU
Разработка приложений для мобильных устройств — это новый прорыв в веб-разработке. Скоро пользоваться Интернетом будут чаще с помощью мобильных телефонов и планшетов, чем персональных компьютеров. Ваш бизнес нуждается в мобильной стратегии, но вы не... Разработка веб-сайтов для мобильных устройств
Подробно рассмотрены основные методы и нотации, применяемые при разработке сложного программного обеспечения. Особое внимание уделено проектированию программных систем с использованием структурного и объектного подходов. Приведена классификация и... Технология программирования
В данном официальном руководстве по работе с графическим редактором CorelDRAW, подготовленном автором в тесном сотрудничестве с разработчиками корпорации Corel, рассмотрены все возможности программы, начиная с настройки рабочего пространства и работе... CorelDRAW® X5. Официальное руководство
С помощью данной книги вы сможете установить, переустановить, обновить, восстановить работоспособность Windows 8, настроить ее и решить проблемы с настройками. При этом не требуется никаких предварительных компьютерных навыков и знаний. Все... Установка, переустановка, восстановление, настройка Windows 8. Экспресс-курс по решению проблем с системой
В книге показано, как вопросы архитектурного моделирования решаются с помощью пакета 3ds Max. Рассматривается создание геометрий, в том числе декора, особенности моделирования и представления ландшафтов, проблемы расчета инсоляции зданий, детальное... Архитектурное моделирование в 3ds Max + Видеокурс (+ CD-ROM)
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Утиль - лучший бесплатный софт для Windows
Windows и Office: новости и советы
eManual - электронные книги и техническая документация
Компьютерные книги. Рецензии и отзывы
 
Рассылки Maillist.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Corel DRAW - от идеи до реализации
MS Windows и MS Office
eManual - электронные книги и техническая документация
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
70-672 (14)
Ребята, дайте пожалуйста ДАМП на Майкрософт 070-672 экзамен, желательно на русском...
 
70-671 экзмен на русском языке. (359)
Уже в третий раз пытался сдать экзамен MSP 70-671 на русском языке и все без результатно,...
 
Помощь по MS Access (272)
Доброе время суток. Случайно оказался на этом сайте, искал статьи по OLAP. Вижу, что...
 
Служба Windows Installer (280)
При очередной установке С++Builder выскочила ошибка: Не удается получить доступ к сужбе Windows...
 
Где можно найти «Пакет анализа» для Excel ? (53)
Коллеги, подскажите, где можно скачать надстройку к Excel под названием «Пакет анализа», после...
 
 
 



    
rambler's top100 Rambler's Top100