Решение задач линейного программирования в Excel

Изучив алгоритмы «ручного» решения задач линейного программирования, полезно познакомиться и со способом упростить этот процесс. Ясно, что чем сложнее задача, чем больше в ней переменных и условий, тем утомительнее и дольше ее решать. В таких случаях удобно использовать специальные математические пакеты, или доступную многим программу MS Excel.

Решить задачи линейного программирования в Excel достаточно просто: 1) внести исходные данные задачи и ограничения, 2) запустить надстройку Поиск решения, 3) установить нужные параметры решения и запустить выполнение. Программа подберет оптимальное решение, выдаст отчеты для анализа решения задачи.

Подробнее все эти этапы с пояснениями и скриншотами разобраны ниже в примерах на разных задачах линейного программирования — изучайте, ищите похожие, решайте. Если вам нужна помощь в выполнении заданий, перейдите в раздел: Контрольные работы по линейному программированию.

Линейное программирование: примеры решений в Excel

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

Задача 2. Цех производит 8 различных видов деталей для двигателей A, B, C1, C2, C3, D, E6, F имея в своем распоряжении перечисленный ниже парк из 7 видов универсальных станков: 2 шт. — ADF, 3 шт. — SHG, 3 шт. — BSD, 1 шт. — AVP, 1 шт. — BFG, 3 шт. — ABM, 2 шт. — RL.
Время, требуемое для обработки единицы каждого продукта на каждом станке, вклад в прибыль от производства единицы каждого продукта и рыночный спрос на каждый продукт за месяц даны в таблице.
Цех работает 12 часов в день. Каждый месяц содержит 26 рабочих дней. Для упрощения задачи считаем, что возможен произвольный порядок обработки деталей на различных станках.
Составьте оптимальный план производства.
Определите, производство каких продуктов лимитировано рынком, и каких – техническими возможностями цеха. Какие машинные ресурсы должны быть увеличены в первую очередь, чтобы добиться максимального увеличения прибыли (при заданных потребностях рынка)?
Есть ли продукт, который невыгодно производить? Почему? Что нужно изменить, чтобы все продукты стало выгодно производить?

Задача 3. Необходимо составить самый дешевый рацион питания цыплят, содержащий необходимое количество определенных питательных веществ тиамина Т и ниацина Н. Пищевая ценность рациона (в калориях) должна быть не менее заданной. Смесь для цыплят изготавливается из двух продуктов — К и С. Известно содержание тиамина и ниацина в этих продуктах, а также питательная ценность К и С (в калориях). Сколько К и С надо взять для одной порции куриного корма, чтобы цыплята получили необходимую им дозу веществ Н и Т и калорий (или больше), а стоимость порции была минимальна? Исходные данные для расчетов приведены в таблице.

Задача 4. Фирма «Компьютер-сервис» поставляет компьютеры под ключ четырех базовых комплектаций: «домашний», «игровой», «офисный» и «экстрим». Известны средние затраты времени на сборку, проверку и подключение компьютеров. Каждый компьютер приносит определенный уровень прибыли, но спрос ограничен. Кроме того, в плановом периоде ограничен ресурс человеко-часов, отведенных на выполнение каждой производственной операции. Определить, сколько компьютеров каждого типа необходимо произвести в плановом периоде, имея целью максимизировать прибыль.

Задача 5. На лесопилку поступают доски длиной 10 м. По контракту лесопилка должна поставить клиенту не менее 100 досок длиной 5 м, не менее 200 досок длиной 4 м и не менее 300 досок длиной 3 м. Как работникам лесопилки выполнить условия контракта, разрезав наименьшее количество досок?

Задача 6. Компания «Евростройтур» организует экскурсионные автобусные туры по странам Европы. Компания получила 4 новых автобуса и предполагает направить их на маршруты во Францию, Италию, Чехию и Испанию. Каждый автобус обслуживают 2 водителя. Компанией приглашены 8 водителей, в различной степени знакомых с дорогами европейских стран (в % от экскурсионного маршрута).
Необходимо распределить водителей так, чтобы общий показатель освоения маршрутов был максимальным.

Задача 7. Решить задачу методом ветвей и границ, решая отдельные задачи линейного нецелочисленного программирования с помощью функции «Поиск решения» в Microsoft Excel (в случае, если первая же задача ЛП выдает целочисленное решение, не позволяя ветвить задачу, немного изменить начальные условия).
Состав еды рядовых регламентируется верховной ставкой главнокомандующего, которая устанавливает нижние нормы питания в сутки по основным компонентам: 1500 килокалорий, 100 г белков, 280 г углеводов, 90 г жиров, 1 кг воды. На складах есть 4 вида продуктов, которые выдают защитникам Родины сухим пайком: лимонад, тушенка в маленьких банках, унифицированные наборы горбушек и пирожки с ежевикой. Стоимость этих четырех продуктов соответственно 12 руб., 34 руб., 3 руб. и 20 руб. Какова минимальная сумма, которую должен затратить прапорщик на питание одного солдата?

Задача 8. Предприятие выпускает два вида продукции: Изделие 1 и Изделие 2. На изготовление единицы Изделия 1 требуется затратить a11 кг сырья первого типа, a21 кг сырья второго типа, a31 кг сырья третьего типа.
На изготовление единицы Изделия 2 требуется затратить a12 кг сырья первого типа, a22 кг сырья второго типа, a32 кг сырья третьего типа.
Производство обеспечено сырьем каждого типа в количестве b1 кг, b2 кг, b3 кг соответственно.
Рыночная цена единицы Изделия 1 составляет c1 тыс. руб., а единицы Изделия 2 — c2 тыс. руб.
Требуется:
1) построить экономико – математическую модель задачи;
2) составить план производства изделий, обеспечивающий максимальную выручку от их реализации при помощи графического метода решения задачи линейного программирования.
3) составить план производства изделий, обеспечивающий максимальную выручку от их реализации при помощи табличного симплекс – метода решения задачи линейного программирования.
4) составить план производства изделий, обеспечивающий максимальную выручку от их реализации, используя надстройку «Поиск решения» в среде MS EXCEL.

Линейное программирование в Excel

В Excel 2007 для включения пакета анализа надо нажать перейти в блок Параметры Excel, нажав кнопку в левом верхнем углу, а затем кнопку «Параметры Excel» внизу окна:

Для того чтобы решить задачу ЛП в табличном процессоре Microsoft Excel , необходимо выполнить следующие действия:

1. Ввести условие задачи:

a) создать экранную форму для ввода условия задачи:

· целевой функции (ЦФ),

b) ввести исходные данные в экранную форму:

· коэффициенты при переменных в ограничениях,

· правые части ограничений;

c) ввести зависимости из математической модели в экранную форму:

· формулу для расчета ЦФ,

· формулы для расчета значений левых частей ограничений;

d) задать ЦФ (в окне «Поиск решения»):

· направление оптимизации ЦФ;

e) ввести ограничения и граничные условия (в окне «Поиск решения»):

· ячейки со значениями переменных,

· граничные условия для допустимых значений переменных,

· соотношения между правыми и левыми частями ограничений.

2. Решить задачу:

a) установить параметры решения задачи (в окне «Поиск решения»);

b) запустить задачу на решение (в окне «Поиск решения»);

c) выбрать формат вывода решения (в окне «Результаты поиска решения»).

Рассмотрим подробно использование MS Excel на примере решения следующей задачи.

Фабрика «GRM pic» выпускает два вида каш для завтрака — «Crunchy» и «Chewy». Используемые для производства обоих продуктов ингредиенты в основ­ном одинаковы и, как правило, не являются дефицитными. Основным ограничением, накладываемым на объем выпуска, является наличие фонда рабочего времени в каждом из трех цехов фабрики.

Управляющему производством Джою Дисону необходимо разработать план производства на месяц. В приведенной ниже таблице указаны общий фонд рабочего времени и число человеко-часов, требуемое для производства 1 т продукта.

В. Добавка приправ

Доход от производства 1 т «Crunchy» составляет 150 ф. ст., а от производства «Chewy» — 75 ф, ст. На настоящий момент нет никаких ограничений на возможные объемы продаж. Имеется возможность продать всю произведенную продукцию.

а) Сформулировать модель линейного программирования, максимизи­рующую общий доход фабрики за месяц.

б) Решить ее c помощью MS Excel.

Формальная постановка данной задачи имеет вид:

(1)
Ввод исходных данных
Создание экранной формы и ввод исходных данных

Экранная форма для решения в MS Excel представлена на рисунке 1.

В экранной форме на рисунке 1 каждой переменной и каждому коэффициенту задачи поставлена в соответствие конкретная ячейка на листе Excel. Имя ячейки состоит из буквы, обозначающей столбец, и цифры, обозначающей строку, на пересечении которых находится объект задачи ЛП. Так, например, переменным задачи 1 соответствуют ячейки B4 ( ), C4 ( ), коэффициентам ЦФ соответствуют ячейки B6 ( 150), C6 ( 75), правым частям ограничений соответствуют ячейки D18 ( 1000), D19 ( 360), D20 ( 600) и т. д.
Ввод зависимостей из формальной постановки задачи в экранную форму

Для ввода зависимостей определяющих выражение для целевой функции и ограничений используется функция MS Excel СУММПРОИЗВ, которая вычисляет сумму попарных произведений двух или более массивов.

Одним из самых простых способов определения функций в MS Excel является использование режима «Вставка функций», который можно вызвать из меню «Вставка» или при нажатии кнопки « « (рисунок 2) на стандартной панели инструментов.

Так, например, выражение для целевой функции из задачи 1 определяется следующим образом:

· нажав кнопку « «, вызовите окно«Мастер функций — шаг 1 из 2»;

· выберите в окне «Категория» категорию «Математические»;

· в окне «Функция» выберите функцию СУММПРОИЗВ (рис. 3);

· в появившемся окне «СУММПРОИЗВ» в строку «Массив 1» введите выражение B$4:C$4, а в строку «Массив 2» — выражение B6:C6 (рис. 4);

Левые части ограничений задачи (1) представляют собой сумму произведений каждой из ячеек, отведенных для значений переменных задачи (B3, C3), на соответствующую ячейку, отведенную для коэффициентов конкретного ограничения (B13, C13 — 1-е ограничение; B14, С14 — 2-е ограничение и B15, С15 — 3-е ограничение). Формулы, соответствующие левым частям ограничений, представлены в табл.1.

Таблица 1.
Формулы, описывающие ограничения модели (1)

Решение задачи линейного программирования в excel

Менеджерами не рождаются, менеджерами становятся

Решение задачи линейного программирования в Excel

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

При решении задач линейного программирования, во-первых, необходимо составить модель, то есть сформулировать условия на математическом языке. После этого решение может быть найдено графически (см., например, здесь), с использованием надстройки Excel «Поиск решения» (рассмотрено в настоящей заметке) или с помощью специализированных компьютерных программ (см., например, здесь).

Рассмотрим линейное программирование в Excel на примере задачи, ранее решенной графическим методом.

Задача. Николай Кузнецов управляет небольшим механическим заводом. В будущем месяце он планирует изготавливать два продукта (А и В), по которым удельная маржинальная прибыль оценивается в 2500 и 3500 руб., соответственно. Изготовление обоих продуктов требует затрат на машинную обработку, сырье и труд. На изготовление каждой единицы продукта А отводится 3 часа машинной обработки, 16 единиц сырья и 6 единиц труда. Соответствующие требования к единице продукта В составляют 10, 4 и 6. Николай прогнозирует, что в следующем месяце он может предоставить 330 часов машинной обработки, 400 единиц сырья и 240 единиц труда. Технология производственного процесса такова, что не менее 12 единиц продукта В необходимо изготавливать в каждый конкретный месяц. Необходимо определить количество единиц продуктов А и В, которые Николай доложен производить в следующем месяце для максимизации маржинальной прибыли.

Скачать заметку в формате Word, пример в формате Excel

1. Воспользуемся математической моделью построенной в упомянутой заметке. Вот эта модель:

Максимизировать: Z = 2500 * х1 + 3500 *х2

При условии, что: 3 * х1 + 10 * х2 ≤ 330

2. Создадим экранную форму и введем в нее исходные данные (рис. 1).

Рис. 1. Экранная форма для ввода данных задачи линейного программирования

Обратите внимание на формулу в ячейке С7. Это формула целевой функции. Аналогично, в ячейки С16:С18 введены формулы для расчета левой части ограничений.

3. Проверьте, если у вас установлена надстройка «Поиск решения» (рис. 2), пропустите этот пункт.

Рис. 2. Надстройка Поиск решения установлена; вкладка «Данные», группа «Анализ»

Если надстройки «Поиск решения» вы на ленте Excel не обнаружили, щелкните на кнопку Microsoft Office, а затем Параметры Excel (рис. 3).

Рис. 3. Параметры Excel

Выберите строку Надстройки, а затем в самом низу окна «Управление надстройками Microsoft Excel» выберите «Перейти» (рис. 4).

Рис. 4. Надстройки Excel

В окне «Надстройки» установите флажок «Поиск решения» и нажмите Ok (рис. 5). (Если «Поиск решения» отсутствует в списке поля «Надстройки», чтобы найти надстройку, нажмите кнопку Обзор. В случае появления сообщения о том, что надстройка для поиска решения не установлена на компьютере, нажмите кнопку Да, чтобы установить ее.)

Рис. 5. Активация надстройки «Поиск решения»

После загрузки надстройки для поиска решения в группе Анализ на вкладке Данные становится доступна команда Поиск решения (рис. 2).

4. Следующим этапом заполняем окно Excel «Поиск решения» (рис. 6)

Рис. 6. Заполнение окна «Поиск решения»

В поле «Установить целевую ячейку» выбираем ячейку со значением целевой функции – $C$7. Выбираем, максимизировать или минимизировать целевую функцию. В поле «Изменяя ячейки» выбираем ячейки со значениями искомых переменных $C$4:$D$4 (пока в них нули или пусто). В области «Ограничения» с помощью кнопки «Добавить» размещаем все ограничения нашей модели. Жмем «Выполнить». В появившемся окне «Результат поиска решения» выбираем все три типа отчета (рис. 7) и жмем Ok. Эти отчеты нужны для анализа полученного решения. Подробнее о данных, представленных в отчетах, можно почитать здесь.

Рис. 7. Выбор типов отчета

На основном листе появились значения максимизированной целевой функции – 130 000 руб. и изменяемых параметров х1 = 10 и х2 = 30. Таким образом, для максимизации маржинального дохода Николаю в следующем месяце следует произвести 10 единиц продукта А и 30 единиц продукта В.

Если вместо окна «Результат поиска решения» появилось что-то иное, Excel`ю найти решение не удалось. Проверьте правильность заполнения окна «Поиск решения». И еще одна маленькая хитрость. Попробуйте уменьшить точность поиска решения. Для этого в окне «Поиск решения» щелкните на Параметры (рис. 8.) и увеличьте погрешность вычисления, например, до 0,001. Иногда из-за высокой точности Excel не успевает за 100 итераций найти решение. Подробнее о параметрах поиска решения можно почитать здесь.