Линейное программирование в Excel (Содержание)
- Введение в линейное программирование в Excel
- Методы решения линейного программирования с помощью Excel Solver
Введение в линейное программирование в Excel
Линейное программирование является наиболее важным, а также увлекательным аспектом прикладной математики, который помогает в оптимизации ресурсов (либо минимизации потерь, либо максимизации прибыли при данных ресурсах). Если у нас есть ограничения и целевая функция хорошо определены, мы можем использовать систему, чтобы предсказать оптимальное решение для данной проблемы. В Excel у нас есть Excel Solver, который помогает нам решать проблемы линейного программирования или LPP. В этой статье мы увидим, как использовать Excel Solver для оптимизации ресурсов, связанных с бизнес-задачами, с помощью линейного программирования.
Первое первым. Давайте посмотрим, как мы можем включить Excel Solver (ключевой компонент LPP в Excel).
Методы решения линейного программирования с помощью Excel Solver
Давайте разберемся, как использовать линейное программирование через Excel Solver с некоторыми методами.
Вы можете скачать этот шаблон Excel для линейного программирования здесь - Шаблон Excel для линейного программирования
Способ № 1 - Включение Солвера в Microsoft Excel
В Microsoft Excel мы можем найти Солвер на вкладке Данные, которую можно найти на ленте Excel, расположенной в самой верхней части, как показано ниже:
Если вы не можете увидеть этот инструмент утилиты там, вам нужно включить его через параметры Excel. Выполните следующие шаги, чтобы включить Solver в Excel.
Шаг 1: Перейдите к меню «Файл» и нажмите «Параметры», что является последним в этом списке.
Шаг 2: появится новое окно с именем Excel Options. Нажмите на Надстройки из списка опций, присутствующих в левой части окна.
Шаг 3: В разделе «Управление» в нижней части окна выберите «Надстройки Excel» из выпадающего списка и нажмите кнопку «Перейти…», расположенную рядом.
Шаг 4: Как только вы нажмете кнопку «Перейти…», вы сможете увидеть список всех надстроек, доступных в Excel, в новом окне. Установите флажок, чтобы выбрать надстройку Solver, чтобы использовать ее на вкладке «Данные» для решения уравнений. Нажмите кнопку ОК после того, как отметите выбор надстройки Solver.
Таким образом, вы можете включить Excel Solver в Microsoft Excel.
Метод № 2 - Решение проблемы линейного программирования с помощью Excel Solver
Теперь мы попытаемся решить задачу линейного программирования с помощью инструмента Excel Solver.
Пример: химический завод производит два продукта, а именно A и B. Этим двум продуктам требуется сырье, как показано ниже: продукту A требуется три вида сырья - материал_1 20 кг, материал_2 30 кг, материал_3 как 5 кг. Аналогичным образом, для продукта B требуется 10 кг материала_1, 30 кг материала_2 и 10 кг материала_3. Производитель требует минимум 460 кг или материала_1, 960 кг материала_2 и 220 кг материала_3. Если цена за единицу продукции для продукта A составляет 30 долларов США, а для продукта B - 35 долларов США, сколько продуктов производитель должен смешать, чтобы выполнить минимальные требования к материалам при минимально возможных затратах? Давайте использовать информацию, представленную в этом примере, для моделирования уравнений.
Шаг 1: Мы можем увидеть все ограничения уравнения, которые мы можем сформировать, используя информацию, представленную в примере выше.
Шаг 2: Используйте эти уравнения для добавления ограничений по ячейкам в Excel через A2: C8 данного листа. Смотрите на скриншот ниже:
Шаг 3: Теперь нам нужно использовать формулу Количество * Стоимость единицы продукции и суммировать ее для обоих продуктов, чтобы получить фактические потребности в материалах. Вы можете увидеть это в столбце D для всех ячеек, содержащих ограничения B3, B4, C3). Пожалуйста, смотрите прикрепленный скриншот ниже:
Если вы внимательно посмотрите на эту формулу, мы использовали B3 и C3 в качестве фиксированных элементов для каждой формулы в разных ячейках столбца D. Это связано с тем, что B3 и C3 являются ячейками, обозначающими количества для Продукта A и Продукта B соответственно. Эти величины появятся, как только система уравнений будет решена с помощью Excel Solver.
Шаг 4: Нажмите на вкладку «Данные», а затем на Солвер, который присутствует в разделе «Анализ» на вкладке.
Шаг 5: После того, как вы нажмете на Solver, откроется новая вкладка с именем «Solver Parameter», в которой вам нужно установить параметры для этого набора уравнений, которые необходимо решить.
Шаг 6: Первое, что нам нужно определить, это установить цель: поскольку наша цель - определить общую стоимость, чтобы ее можно было минимизировать, установите для нее значение D4.
Шаг 7: Поскольку нам нужно минимизировать стоимость при максимально возможном производстве, установите следующий параметр как Мин. Вы можете сделать это, нажав кнопку Min.
Шаг 8: в разделе «Изменение переменных ячеек» мы должны упомянуть B3 и C3, поскольку эти ячейки будут содержать количества для продукта A и продукта B соответственно после того, как проблема будет решена.
Шаг 9: Теперь добавьте ограничения. Нажмите кнопку «Добавить» в разделе «В зависимости от ограничений:», и откроется новое окно для добавления ограничений. Под этим окном - B3: C3 в качестве ссылки на ячейку, > = и 0 в качестве ограничений. Это мы и делаем, поскольку основное ограничение в любом LPP состоит в том, что X и Y должны быть больше нуля.
Шаг 10: Снова нажмите на кнопку «Добавить», и на этот раз используйте B3: C3 в качестве ссылки на ячейку и F6: F8 в качестве ограничения с неравенством как> =. Нажмите кнопку ОК, чтобы добавить это ограничение также под решатель.
Солвер теперь имеет все параметры, необходимые для решения этого набора линейных уравнений, и выглядит так:
Шаг 11: Теперь нажмите кнопку «Решить» в нижней части окна, чтобы решить это линейное уравнение и найти оптимальное решение.
Как только мы нажимаем кнопку «решить», система начинает поиск оптимального решения для поставленной нами задачи, и мы получаем значения для B3, C3, используя которые мы также получаем значения в столбце F для F4, F6: F8. Каковы оптимальные затраты и материальные ценности, которые можно использовать для Продукта A и Продукта B.
Это решение информирует нас о том, что, если нам необходимо минимизировать стоимость производства для продукта A и продукта B при оптимальном использовании материалов_1, материалов_2 и материалов_3, мы должны произвести 14 количеств продукта A и 18 количеств продукта B.
Это из этой статьи. Давайте подведем итоги, чтобы запомнить некоторые моменты:
Что нужно помнить о линейном программировании в Excel
- Решение задач линейного программирования обязательно с помощью Excel Solver. Нет другого метода, который мы можем сделать, используя это.
- У нас всегда должны быть ограничения и переменная объекта, которая будет готова с нами.
- Если Солвер не включен, вы можете включить его в настройках надстройки Excel.
Рекомендуемые статьи
Это руководство по линейному программированию в Excel. Здесь мы обсуждаем, как использовать линейное программирование в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
- Интерполировать в Excel
- Программирование в Excel
- Перемещение столбцов в Excel
- Обратная матрица в Excel