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

Обновлено: 21.11.2024

Линейное программирование (ЛП) — один из наиболее широко применяемых О.Р. методы и обязаны своей популярностью главным образом симплекс-методу Джорджа Данцига (Danzig 1963) и революции в вычислительной технике. Это очень мощная техника для решения проблем распределения, которая стала стандартным инструментом для многих предприятий и организаций. Хотя симплекс-метод Данцига позволяет генерировать решения вручную, итеративный характер получения решений настолько утомителен, что, если бы компьютер никогда не был изобретен, линейное программирование оставалось бы интересной академической идеей, отнесенной к классу математики. К счастью, компьютеры были изобретены, и поскольку они стали настолько мощными при такой низкой цене, линейное программирование стало, возможно, одним из самых распространенных применений персонального ПК.

Конечно, существует множество программных пакетов, предназначенных для решения линейных программ (и других типов математических программ), из которых, возможно, наиболее популярны LINDO, GAMS и XPRESS-MP. Все эти пакеты, как правило, основаны на DOS и предназначены для специализированного рынка, которому требуются инструменты, предназначенные для решения LP. Однако в последние годы некоторые стандартные бизнес-пакеты, такие как электронные таблицы, начали включать опцию решения LP, и Microsoft Excel не является исключением. Включение возможности решения LP в такие приложения, как Excel, привлекательно как минимум по двум причинам. Во-первых, Excel, пожалуй, самая популярная электронная таблица, используемая как в бизнесе, так и в университетах, и поэтому она очень доступна. Во-вторых, электронная таблица предлагает очень удобные функции ввода и редактирования данных, которые позволяют учащимся лучше понять, как создавать линейные программы.

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

  1. Выберите пункт меню Инструменты | Add_Ins (загрузка необходимого файла займет несколько минут).
  2. В представленном диалоговом окне установите флажок для надстройки Solver.
  3. Нажав кнопку «ОК», вы сможете получить доступ к параметру «Решатель» из нового пункта меню «Инструменты | Решатель (отображается в разделе Инструменты | Сценарии . )

Чтобы проиллюстрировать Excel Solver, я рассмотрю достаточно известный пример Hillier & Lieberman, задачу Wyndor Glass Co. (Hillier & Lieberman, 1995). Проблема касается производителя стекла, который использует три производственных предприятия для сборки своей продукции, в основном стеклянных дверей (1 шт.) и окон с деревянными рамами (2 шт.). Каждому продукту требуется разное время на трех заводах, и существуют определенные ограничения на доступное время производства на каждом заводе. Обладая этой информацией и знанием вклада двух продуктов в прибыль, руководство компании желает определить, какое количество каждого продукта они должны производить, чтобы максимизировать прибыль. Другими словами, задача Wyndor Glass Co. — это классическая, хотя и очень простая задача о ассортименте продукции.

Задача формулируется в виде следующей линейной программы:

Сформулировав задачу, а у вас может быть значительно больше переменных и ограничений решения, вы можете перейти к ее вводу в Excel. Лучший подход к вводу задачи в Excel — сначала перечислить в столбце имена целевой функции, переменных решения и ограничений. Затем вы можете ввести произвольные начальные значения в ячейки для переменных решения, обычно нулевые, как показано на рисунке 1. Excel будет изменять значения ячеек по мере определения оптимальных решений. Назначив переменным решения некоторые произвольные начальные значения, вы можете затем явно использовать эти ссылки на ячейки при написании формул для целевой функции и ограничений, не забывая начинать каждую формулу со знака '=' .

Рисунок 1. Настройка задачи в Excel

Вводя формулы для цели и ограничений, целевая функция в B5 будет иметь вид:

Ограничения будут заданы (помещение правых значений в соседние ячейки):

Теперь вы готовы использовать Solver.

При выборе пункта меню Инструменты | Появится диалоговое окно Solver, показанное на рисунке 2, и если вы выберете целевую ячейку перед вызовом Solver, будет определена правильная целевая ячейка. Это значение, которое Solver попытается максимизировать или минимизировать.

Рисунок 2. Диалоговое окно решателя

Выберите, хотите ли вы минимизировать это или максимизировать проблему, в этом случае вы хотите установить целевую ячейку (цель) на Макс. Обратите внимание, что вы можете использовать Solver, чтобы найти результат, который достигнет указанного значения для целевой ячейки, нажав «Значение:». При этом вы можете использовать Solver как прославленного искателя цели.Затем вы вводите диапазон ячеек, которые вы хотите, чтобы Решатель варьировал, переменные решения. Нажмите на белое поле и выберите ячейки B9 и B10 или, альтернативно, введите их. Обратите внимание, что вы можете попытаться заставить Solver угадать, какие ячейки вы хотите изменить, нажав кнопку «Угадай». Если вы логически определили свою проблему, Solver, как правило, должен сделать это правильно.

Теперь вы можете ввести ограничения, сначала нажав кнопку "Добавить ..". Откроется диалоговое окно, показанное на рисунке 3.

Рисунок 3. Ввод ограничений

Ссылка на ячейку указывает на ячейку, содержащую вашу формулу ограничения, поэтому для ограничения Plant One введите B14. По умолчанию

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

Функции Excel, формулы, диаграммы, форматирование, создание информационной панели Excel и др.

Прежде всего. Давайте посмотрим, как мы можем включить Excel Solver (ключевой компонент LPP в Excel).

Методы решения задач линейного программирования с помощью Excel Solver

Давайте разберемся, как использовать линейное программирование с помощью решателя Excel с некоторыми методами.

В Microsoft Excel мы можем найти Решатель на вкладке «Данные», которую можно найти на ленте Excel, расположенной в самой верхней части, как показано ниже:

Если вы не видите эту утилиту там, вам нужно включить ее в параметрах Excel. Выполните следующие действия, чтобы включить Solver в Excel.

Шаг 1. Перейдите к меню «Файл» и нажмите «Параметры» — последнее в этом списке.

Шаг 2. Появится новое окно с названием «Параметры Excel». Нажмите «Надстройки» в списке параметров, представленных в левой части окна.

Excel Advanced Training (16 курсов, более 23 проектов) 16 онлайн-курсов | 23 практических проекта | 140+ часов | Поддающийся проверке сертификат об окончании | Пожизненный доступ
4,8 (10 130 оценок)

Шаг 3. В разделе «Управление» в нижней части окна выберите «Надстройки Excel» в раскрывающемся списке и нажмите кнопку «Перейти…», расположенную рядом.

Шаг 4. Как только вы нажмете кнопку «Перейти…», вы сможете увидеть список всех надстроек, доступных в Excel, в новом окне. Установите флажок, чтобы выбрать надстройку Solver, чтобы ее можно было использовать на вкладке «Данные» для решения уравнений. Нажмите кнопку «ОК» после выбора надстройки «Поиск решения».

Таким образом, вы можете включить Excel Solver в Microsoft Excel.

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

Пример: Химический завод производит два продукта: A. Для этих двух продуктов требуется сырье, как показано ниже: Для продукта A требуется три типа сырья — Материал_1 20 кг, Материал_2 30 кг, Материал_3 как 5 кг. Аналогично, для продукта B требуется 10 кг материала_1, 30 кг материала_2 и 10 кг материала_3. Производителю требуется минимум 460 кг материала_1, 960 кг материала_2 и 220 кг материала_3. Если стоимость единицы продукта А составляет 30 долларов, а стоимость продукта В — 35 долларов, сколько продуктов должен смешать производитель, чтобы удовлетворить минимальные требования к материалам при минимально возможных затратах? Давайте используем информацию, представленную в этом примере, для моделирования уравнений.

Шаг 1. Мы можем увидеть все ограничения уравнения, которые мы можем сформировать, используя информацию, представленную в приведенном выше примере.

Шаг 2. Используйте эти уравнения, чтобы добавить ограничения по ячейкам в Excel на A2: C8 данного листа. Смотрите скриншот, как показано ниже:

Шаг 3. Теперь нам нужно использовать формулу Количество * Затраты на единицу и просуммировать ее для обоих продуктов, чтобы получить фактические потребности в материалах. Вы можете увидеть это в столбце D для всех ячеек, содержащих ограничения B3, B4, C3). Пожалуйста, смотрите прикрепленный скриншот ниже:

Если вы внимательно посмотрите на эту формулу, мы использовали B3 и C3 в качестве фиксированных элементов для каждой формулы в разных ячейках в столбце D. Это связано с тем, что B3 и C3 — это ячейки, обозначающие количества для продукта A и продукта. Б соответственно. Эти величины появятся после того, как система уравнений будет решена с помощью Excel Solver.

Шаг 4. Нажмите на вкладку "Данные", а затем на "Решатель", который находится в разделе "Анализ" на вкладке.

Шаг 5. После того, как вы нажмете «Решатель», откроется новая вкладка «Параметры решателя», в которой вам нужно установить параметры для этого набора уравнений, которые необходимо решить.

Шаг 6. Первое, что нам нужно определить, это Set. Цель: поскольку наша цель – определить общие затраты, чтобы их можно было минимизировать, установите для этого параметра значение D4.

Шаг 7. Так как нам нужно минимизировать затраты при максимально возможной производительности, установите следующий параметр как Min. Это можно сделать, нажав на переключатель Min.

Шаг 8. В разделе «Изменение ячеек переменных:» нам нужно указать B3 и C3, так как эти ячейки будут содержать количества для продукта A и продукта B соответственно после решения проблемы.

Шаг 9. Теперь добавьте ограничения. Нажмите кнопку Добавитьдобавить в разделе Тема uв разделе Ограничения: откроется новое окно для добавления ограничений. Под этим окном – B3:C3 в качестве ссылки на ячейку, >= и 0 в качестве ограничений. Это мы делаем, поскольку основным ограничением в любом LPP является то, что X и Y должны быть больше нуля.

Шаг 10. Снова нажмите кнопку «Добавить» и на этот раз используйте B3:C3 в качестве ссылки на ячейку и F6:F8 в качестве ограничений с неравенством >=. Нажмите кнопку ОК, чтобы добавить это ограничение под решатель.

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

Шаг 11. Теперь нажмите кнопку "Решить" в нижней части окна, чтобы решить это линейное уравнение и найти оптимальное решение.

Как только мы нажимаем кнопку решения, система начинает поиск оптимального решения поставленной нами задачи, и мы получаем значения для B3, C3, используя которые мы также получаем значения в столбце F для F4, F6:F8, оптимальные затраты и стоимость материалов, которые можно использовать для Продукта А и Продукта Б.

Это решение информирует нас о том, что если нам нужно минимизировать стоимость производства продукта A и продукта B при оптимальном использовании Material_1, Material_2 и Material_3, мы должны произвести 14 единиц продукта A и 18 единиц продукта B.< /p>

Это из этой статьи. Давайте подведем итог, добавив несколько моментов, о которых следует помнить:

Что следует помнить о линейном программировании в Excel

  • Обязательно решать задачи линейного программирования с помощью Excel Solver. Мы не можем сделать это другим способом.
  • У нас всегда должны быть готовые ограничения и объектная переменная.
  • Если Solver не включен, вы можете включить его в параметрах надстройки Excel.

Рекомендуемые статьи

Это руководство по линейному программированию в Excel. Здесь мы обсуждаем, как использовать линейное программирование в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете ознакомиться с другими нашими рекомендуемыми статьями –

Использование встроенного решателя Excel — пример сочетания продуктов

Знакомство с диалоговыми окнами решателя Excel

Чтобы сообщить решателю Excel, какие ячейки на листе представляют переменные решения, ограничения и целевую функцию, мы нажимаем кнопку «Решатель» на вкладке «Данные Excel» или кнопку «Премиум-решатель» на вкладке «Надстройки», которая отображает «Решатель». Диалог параметров. В поле «Установить цель» (или «Установить целевую ячейку») мы вводим или нажимаем на ячейку F5, целевую функцию. В поле редактирования By Changeable Variable Cells мы набираем B4:E4 или выбираем эти ячейки с помощью мыши. (Нажмите на изображение, чтобы увидеть его в полном размере.)

Чтобы добавить ограничения, мы нажимаем кнопку «Добавить» в диалоговом окне «Параметры решателя», выбираем ячейки F8:F11 в поле редактирования «Ссылка на ячейку» (левая сторона) и выбираем ячейки G8: G11 в поле редактирования "Ограничение" (правая сторона); отношение по умолчанию плюс моделирование Монте-Карло и анализ рисков, деревья решений, интеллектуальный анализ данных, интеллектуальный анализ текста и машинное обучение и многое другое! Это невероятное предложение, и вы можете попробовать его бесплатно.

Подробнее

Если вы добрались до этого места, поздравляем! Вы успешно настроили и решили простую задачу оптимизации с помощью Microsoft Excel. Если хотите, вы можете посмотреть, как настроить и решить ту же проблему с набором продуктов с помощью платформы решения рисков в Excel или с помощью программы Visual Basic .NET, которая вызывает SDK платформы решения для решения Frontline. Если вы еще не читали другие части руководства, вы можете вернуться к началу руководства и прочитать обзоры «Для чего нужны решатели?», «Как определить модель?», «Какого рода Решение, которое я могу ожидать?» и "Что затрудняет решение модели?"

Это был пример задачи линейного программирования. Другие типы задач оптимизации могут включать квадратичное программирование, смешанно-целочисленное программирование, программирование с ограничениями, гладкую нелинейную оптимизацию и негладкую оптимизацию. Чтобы узнать больше, щелкните Типы задач оптимизации. Чтобы получить более подробное объяснение линейности и разреженности в задачах оптимизации, перейдите к нашему расширенному учебному пособию.

Линейное программирование — одно из важных понятий в статистике. На основе имеющихся данных переменных мы можем провести прогнозный анализ. В нашей предыдущей статье «Линейная регрессия в Excel» мы подробно обсуждали «Линейную регрессию». Однако в Excel у нас есть опция под названием «Решатель в Excel», которую можно использовать для решения задачи линейного программирования. С помощью этого решателя мы можем использовать линейное программирование для оптимизации ресурсов.

В этой статье мы подробно покажем вам, как решить задачу линейного программирования в Excel. Прочитайте всю статью, чтобы узнать об этом.

Как решать задачи линейного программирования с помощью Excel Solver?

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

Проблема: производитель хочет изменить производственную модель текущего продукта. У него есть два вида продуктов: «Товар 1» и «Товар 2». Для Продукта 1 требуется три вида сырья: Сырье 1 20 кг, Сырье 2 30 кг и Сырье 3 5 кг. Точно так же для Продукта 2 требуется три вида сырья: Сырье 1 – 10 кг, Сырье 2 – 25 кг и Сырье 3 – 10 кг.

Производителям требуется минимум сырья 1 550 кг, сырья 2 800 кг и сырья 3 250 кг. Если Продукт 1 стоит рупий.30 за единицу, а Продукт 2 стоит 35 за единицу, сколько единиц каждого продукта должен производитель смешать, чтобы удовлетворить минимальные требования к сырью при максимально низкой цене, и какова стоимость?

Теперь введите всю эту информацию в электронную таблицу Excel в следующем формате.

После настройки нам нужно перейти к инструменту решения в Excel. Инструмент решателя доступен на вкладке "Данные" в Excel.

Включить надстройку "Поиск решения"

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

Теперь мы видим «Надстройку Solver» на вкладке «ДАННЫЕ».

Решение задач линейного программирования с помощью Excel Solver

В приведенном выше окне наша первая опция — «Установить цель».

Таким образом, используя SOLVER, мы можем решить линейное программирование в Excel.

Что нужно помнить

  • Решатель по умолчанию недоступен для использования.
  • Решатель не ограничивается только языком линейного программирования, мы также можем решать многие другие задачи. См. нашу статью «Решатель в Excel».
  • Установка целевой ячейки важна.
  • Добавление ограничений должно быть подготовлено заранее.

Рекомендуемые статьи

Это руководство по линейному программированию в Excel. Здесь мы обсуждаем, как решить задачу линейного программирования в Excel, используя вариант решения с примером и загружаемым шаблоном Excel. Вы можете узнать больше об Excel из следующих статей –

Читайте также: