Oc-windows.ru

IT Новости из мира ПК
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Решение злп в excel

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

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

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

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

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

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

Линейное программирование: примеры в 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 водителей, в различной степени знакомых с дорогами европейских стран (в % от экскурсионного маршрута).
Необходимо распределить водителей так, чтобы общий показатель освоения маршрутов был максимальным.

Читать еще:  Рабочая книга ms excel может содержать

Задача 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

Как организовать дистанционное обучение во время карантина?

Помогает проект «Инфоурок»

Описание презентации по отдельным слайдам:

Определение оптимального объема производства Решение задач линейного программирования

Общий вид задачи линейного программирования

Задача Предприятие производит 2 вида продукции А и В. Для производства используются три вида ресурсов d, s, t. Известно, расход каждого из ресурсов для выпуска продукции А и В, а также их количество на складе. Прибыль от продажи продукции Р1 и Р2 приведены в таблицею

Оптимальный план выпуска мебельного цеха Цех может выпускать два вида продукции: шкафы и тумбы для телевизора. На каждый шкаф расходуется 3,5м стандартных ДСП, 1 м листового стекла и 1 человеко-день трудозатрат. На тумбу – 1м ДСП, 2 м стекла и 1 человеко-день трудозатрат. Прибыль от продажи 1 шкафа составляет 200 у.е., а 1 тумбы – 100 у.е. Материальные и трудовые ресурсы ограничены: в цехе работают 150 рабочих, в лень нельзя израсходовать больше 350 м ДСП и более 240 м стекла. Какое количество шкафов и тумб должен выпускать цех, чтобы сделать прибыль максимальной?

Решение Исходные данные: Ресурсы Продукты Запасы Шкаф Тумбы ДСП 3,5 1 350 Стекло 1 2 240 Труд 1 1 150 Прибыль 200 100

Элементы математической модели: Переменные решения Целевая функция х1– количество шкафов х2– количество тумб, изготавливаемых ежедневно F= 200 х1+ 100 х2 ежедневная прибыль цеха Ограничения

Решение задачи об оптимальном плане выпуска продукции с помощью ЭТ Введите исходные данные, ограничения и целевую функцию

Выбираем пункт меню СервисПоиск решения а) в поле окна «Установить целевую ячейку» отметим ячейку В16, в поле окна «изменяя ячейки» ячейки В12:С12. б) установим переключатель на отметке «Равной максимальному значению» в) в поле окна «изменяя ячейки» отметим ячейки В12:С12 г) добавим ограничения. д) затем по кнопке Выполнить.

В результате появится окно «Результаты поиска решения»

Ответ: Объем выпуска мебельного цеха: Шкафы — 80 Тумбы — 70 Прибыль — 23000 руб.

Бесплатный
Дистанционный конкурс «Стоп коронавирус»

  • Жевтило Ирина АскольдовнаНаписать 1252 09.04.2017

Номер материала: ДБ-343907

Добавляйте авторские материалы и получите призы от Инфоурок

Еженедельный призовой фонд 100 000 Р

Читать еще:  Съемка видео через веб камеру

    09.04.2017 502
    08.04.2017 574
    08.04.2017 1777
    08.04.2017 761
    08.04.2017 504
    08.04.2017 1554
    08.04.2017 711

Не нашли то что искали?

Вам будут интересны эти курсы:

Все материалы, размещенные на сайте, созданы авторами сайта либо размещены пользователями сайта и представлены на сайте исключительно для ознакомления. Авторские права на материалы принадлежат их законным авторам. Частичное или полное копирование материалов сайта без письменного разрешения администрации сайта запрещено! Мнение редакции может не совпадать с точкой зрения авторов.

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

Поиск решения EXCEL (6.1). Задача линейного программирования (ЛП)

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

В этой статье мы отойдем от формулировки практических задач и решим задачу линейного программирования в абстрактных терминах: вектор переменных х, матрица ограничений A х , вектор b , целевая функция cTx (вместо более привычных: объем производства, количество комплектующих разного вида, максимальный доход). Задача линейного программирования (ЛП) есть задача максимизации линейной функции при линейных ограничениях. Задачу ЛП можно записать несколькими стандартными способами. Мы сформулируем ее в форме max < cTx : Ax 0>

Задача

Необходимо максимизировать целевую функцию cTx: max 50* x1 + 30* x2 + 25* x3 + 30* x4 при условии, что: 2* x1 + 2,5* x2 + 3* x3 + 1,8* x4 = 50 x3 >= 30 x1; x2; x3; x4 >= 0

cTx — это векторное произведение векторов cT (транспонированный вектор с) и х.

Примечание : эта задача эквивалентна задаче определения оптимальной структуры производства с целью максимизации дохода (см. статью Поиск решения MS EXCEL (1.1). Оптимальная структура выпускаемой продукции ). Сформулируем эту задачу в общем виде: Предприятие планирует производить n видов продукции, используя m видов ресурсов. Для производства единицы j-го продукта требуется aij единиц i-го ресурса. Стоимость единицы j-го продукта равна cj. В наличии имеется bi единиц i-го ресурса. Нужно определить план производства с целью максимизировать прибыль. Обозначив хj — объем выпуска продукции j-го вида (j =1;…;n), мы можем записать задачу поиска оптимального производственного плана следующим образом:

Или в матричной форме:

Получается, что в исходной задаче:

  • вектор с (стоимость продукции) равен (50; 30; 25; 30)
  • вектор x (количество продукции) необходимо найти для заданных условий
  • n=4 (4 вида продукции)
  • m=3 (3 вида ресурсов)
  • вектор b (количество ресурсов) равен (800; 400; 380)
  • матрица A (количество единиц ресурсов для изготовления продукта) равна (2; 2,5; 3; 1,8 : 1,2; 1; 2; 0,8 : 1,5; 1,2; 1,5; 0,8)

Теперь создадим модель.

Создание модели

На рисунке ниже приведена модель, созданная для решения задачи (см. файл примера ).

Для решения задачи на листе MS EXCEL необходимо записать матрицу А , вектора b и cT (предварительно все неравенства переведены в форму меньше или равно путем умножения соответствующих уравнений на -1):

Примечание : для удобства настройки Поиска решения используются именованные диапазоны .

Совет : Вводная статья про Поиск решения в MS EXCEL 2010 находится здесь .

Значение целевой функции cTx получено путем матричного умножения векторов cT и x (используйте функцию МУМНОЖ() , которая вводится как формула массива ). Аналогично получена функция ограничений Ах , путем умножения матрицы А на х . Так как матрица Ах имеет размерность 5х1, то перед вводом формулы = МУМНОЖ(Матрица_А;Вектор_Х) необходимо выделить столбец из 5 ячеек, затем после записи формулы в Строке формул , нажмите CTRL + SHIFT + ENTER для ее ввода.

Настроить Поиск решения нужно следующим образом:

Решение задачи линейного программирования в 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 итераций найти решение. Подробнее о параметрах поиска решения можно почитать здесь.

Ссылка на основную публикацию
Adblock
detector