Задача в Excel На кондитерской фабрике выпускаются конфеты трёх сортов. Известны расходы компонентов (в граммах) на 1 кг каждого сорта и общие запасы, имеющиеся на складе: «Гвоздика» «Романтика» «Карнавал» Запасы, кг Какао 310 255 215 125 Сахар 100 70 150 44 Наполнитель 90 175 135 61,5 Установлены такие цены за 1 кг: конфеты «Гвоздика» – 180 рублей, «Романтика» – 200 рублей и «Карнавал» – 170 рублей. Сколько килограммов конфет каждого сорта нужно выпустить, чтобы доход фабрики была наибольший?
Нужно выпустить:
конфеты «Гвоздика» = 190,1 кг
«Романтика» = 195,5 кг
«Карнавал» = 75,3 кг
,чтобы доход фабрики была наибольший = 86137,5 руб
Объяснение:
1) Составим таблицу с данными в Эксель (рисунок "4.png") и сразу граммы переведем в кг в компонентах, поделив на 1000.
2) Создадим таблицу с переменными Х1,Х2,Х3 (килограммов конфет каждого сорта) и оставляем их пустыми.
3) Напишем формулу целевой функции F(x) = X1*P1+X2*P2+X3*P3
Р - цена
F(x) = X1*180+X2*200+X3*170 - стремится на макс прибыль
в Экселе: F(х)=СУММПРОИЗВ(Х1:Х3;Р1:Р3).
4) Существуют ограничения на каждый компонент конфет в виде определенного кол-ва запаса на складе, т.е. составим уравнения:
Какао: X1*0,31+X2*0,255+X3*0,215 <= 125
Сахар: X1*0,1+X2*0,07+X3*0,15 <= 44
Наполнитель: X1*0,09+X2*0,175+X3*0,135 <= 61,5
А так же не стоит забывать про ограничение, переменные не должны быть отрицательными числами (т. е. мы не можем произвести в минус продукцию). Данное условие может быть поставлено автоматически в "Поиск решения", просто поставив галочку на "Сделать переменные без ограничений неотрицательными". (рисунок "6.png")
5) Переходим к "Поиск решения".
Встаем на ячейку с целевой функцией и переходим на вкладку "Данные" и в самом правом углу будет находиться"Поиск решения".(рисунок "5.png")
Далее заполняем поля:
Оптимизировать цел. функцию = ячейка, где целевая функция
До: в нашей задаче необходимо "Максимум"
Изменяя ячейки переменных: выделить ячейки, которые являются переменными
В соответствии с ограничениями: вводим все ограничения, которые выше
Не забываем про галку из пункта 4.
Метод решения: Поиск решения лин. задач симплекс-методом
Далее жмем Найти решение - Ок. (рисунок "6.png")
Шаг 1: Определение переменных
Пусть x1, x2 и x3 - количество килограммов конфет сорта "Гвоздика", "Романтика" и "Карнавал" соответственно.
Шаг 2: Запись целевой функции
Целевой функцией будет являться доход фабрики, который мы хотим максимизировать. Для этого нам нужно сложить доходы от каждого сорта конфет, умноженные на их количество:
Доход = 180x1 + 200x2 + 170x3
Шаг 3: Ограничения
У нас есть несколько ограничений в задаче:
1) Расходы компонентов для каждого сорта конфет должны быть меньше или равным их общим запасам на складе.
2) Количество килограммов каждого сорта конфет должно быть неотрицательным числом.
Расходы_kомпонента_для_сорта_конфет <= Запасы_компонента_на_складе
Подставляя значения из условия задачи, получаем следующие неравенства:
310x1 + 255x2 + 215x3 <= 125 (для Какао)
100x1 + 70x2 + 150x3 <= 44 (для Сахар)
90x1 + 175x2 + 135x3 <= 61.5 (для Наполнитель)
Шаг 4: Решение задачи с использованием Excel
Объединим все наши условия в одну таблицу в Excel, и воспользуемся функцией "Solver".
1. Запишите значения переменных (количество килограммов каждого сорта конфет) в ячейки Excel.
2. Создайте ячейку для записи целевой функции (дохода фабрики).
3. Создайте ячейки для ограничений (расходы компонентов <= запасы на складе).
4. Установите Solver следующим образом:
- Указываем целевую ячейку, которую нужно максимизировать.
- Указываем переменные ячейки, которые должны быть изменяемыми.
- Указываем ограничения.
- Указываем, что переменные должны быть неотрицательными числами.
- Запускаем Solver для поиска оптимального решения.
Шаг 5: Анализ решения
После завершения Solver, в Excel появятся найденные значения переменных (количество килограммов каждого сорта конфет). Также, вам будет предоставлено значение целевой функции (доход фабрики), когда он был максимален.
Шаг 6: Проверка результатов
Чтобы убедиться, что решение является оптимальным, можно проверить выполнение всех ограничений. Должно быть выполнено условие расходов компонентов для каждого сорта конфет и их общих запасов на складе.
Если все ограничения выполняются, то найденное решение является оптимальным и указывает, сколько килограммов каждого сорта конфет нужно выпустить, чтобы доход фабрики был максимальным.