(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 Team Foundation Server (TFS) 2013, созданный для реализации полного потенциала групп разработчиков, представляет собой центр управления жизненным циклом приложений для Visual Studio. Microsoft Visual Studio Team Foundation Server 2013 Russian OPEN 1 License No Level
Microsoft Lync Server Standard CAL 2013 – это новые возможности подключения, преобразующие коммуникации во взаимодействие, которое подразумевает больше сотрудничества, больше вовлеченности, а также доступно практически из любого местоположения... Microsoft Lync Server Std CAL 2013 Sngl OPEN 1 License No Level Device CAL Device CAL
Электронный ключ Microsoft Office Профессиональный 2013. Язык интерфейса - Русский. Для установки и использования на 1 ПК. Операционная система: Windows 7, Windows 8, Windows 2008 R2 с .NET 3.5 или более поздней версии. 32-разрядный (х86) или... Купить Microsoft Office Профессиональный 2013 Russian, полная версия, электронный ключ
Лицензия для легализации Microsoft Windows, позволяет использовать любую версию Microsoft Windows (XP, Vista, Windows 7 Professional, Windows 8 Professional, Windows 8.1 Professional). Get Genuine Windows Agreement предлагается в рамках программы... Microsoft Windows Professional 8.1 Sngl OPEN 1 License No Level Legalization Get Genuine wCOA
Подписка MSDN Operating Systems — оптимальный способ получить все, что необходимо для реализации сложного проекта приложения для Windows. Доступ к операционным системам Windows и Windows Server. Подписка MSDN Operating Systems позволяют тестировать .... Microsoft MSDN OS All Lng License/Software Assurance Pack OPEN 1 License No Level Qualified
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
Пластик этого цвета был специально создан по нашей спецификации, и он получился ярким, кричащим и блестящим на солнце. Также он отлично выглядит при люминесцентном освещении. Мы потратили много времени, чтобы добиться этого результата и пользователи... Катушка ABS-пластика Myriwell 1.75 мм 1кг., красная
Купить Picaso 3D Builder - первый российский доступный 3D принтер для работы в офисе, дома, на производстве, в школе и в творческих мастерских! Picaso 3D Builder
Новый PICASO 3D Designer 1.2. PICASO 3D Designer (Серебро)
Новый PICASO 3D Designer 1.2. PICASO 3D Designer (Красный)
Новые стандарты в персональной 3D печати! Профессиональный персональный 3D-принтер MakerBot Replicator устанавливает новые стандарты в мире 3D-печати. Область печати на 11% больше, чем у MakerBot Replicator 2. MakerBot Replicator 5th GEN
 
Другие предложения...
 
Книжный магазин   WWW.ITSHOP.RU
Эта книга представляет собой руководство по развертыванию, настройке, защите и управлению Windows Server 2012. Здесь рассмотрены такие вопросы, как новый подход к администрированию Windows Server и новые возможности Active Directory, примеры работы с... Администрирование Microsoft Windows Server 2012
В современном мире, где компьютер есть практически у каждого, несоблюдение компьютерной безопасности может плачевно закончиться для любого пользователя. В лучшем случае закончится потерей информации, в худшем - вы можете лишиться своих сбережений,... Бесплатные антивирусы и защита компьютера без страха для тех, кому за... (+ DVD)
Книга позволит быстро и эффективно освоить редактирование фотографий с помощью Adobe Photoshop CS4. Рассматривается решение самых востребованных и распространенных задач коррекции, ретуши и монтажа фотографий, создания панорамных снимков, исправления... Photoshop CS4 - это просто. Экспресс-методы обработки фотографий (+ DVD)
Данное издание предназначено для читателей, которые уже знакомы с программой Photoshop и ищут новые способы редактирования изображений. Книга проведет вас через девять уникальных авторских уроков, проиллюстрированных большим количеством цветных... Photoshop CS6
С помощью данной книги вы сможете установить, переустановить, обновить, восстановить работоспособность Windows 8, настроить ее и решить проблемы с настройками. При этом не требуется никаких предварительных компьютерных навыков и знаний. Все... Установка, переустановка, восстановление, настройка Windows 8. Экспресс-курс по решению проблем с системой
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Утиль - лучший бесплатный софт для Windows
Windows и Office: новости и советы
eManual - электронные книги и техническая документация
Программирование на Microsoft Access
 
Рассылки Maillist.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Новости ITShop.ru - ПО, книги, документация, курсы обучения
MS Windows и MS Office
Oracle с нуля
eManual - электронные книги и техническая документация
 
Статьи по теме
 
Новинки каталога 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