2018-03-09
1433
Значительное число задач в экономике составляют задачи распределения ресурсов. Наиболее часто математической моделью таких задач является задача линейного программирования. Частным случаем задачи линейного программирования является транспортная задача, т.е. задача на минимизацию расходов на перевозку грузов.
Задание 1: мебельная фабрика производит столы и стулья. Расход ресурсов на их производство и прибыль от их реализации представлена в следующей таблице:
| Ресурсы | Нормы затрат ресурсов на одно изделие | Общее количество ресурсов | |
| Столы | Стулья | ||
| Древесина, м 3 | 0,5 | 0,04 | 200 |
| Трудоемкость, чел.-ч. | 12 | 0,6 | 1800 |
| Прибыль, руб. | 180 | 20 | - |
Кроме того, на производство 80 столов заключен контракт с муниципалитетом, который должен быть выполнен. Необходимо найти такой план производства столов и стульев, при котором прибыль от их реализации максимальна.
Обозначим через x1 количество столов, а через x2 – количество стульев.
Тогда математическая модель примет следующий вид:
|
|
|
F(x) = 180 x1 + 20 x2 à max – целевая функция, описывающая суммарную прибыль мебельной фабрики от реализации столов и стульев;
0,5 x1 + 0,04 x2 £ 200 – ограничение по первому ресурсу (древесине),
12 x1 + 0,6 x2 £ 1800 – ограничение по второму ресурсу (трудоемкости),
x1 ³ 80 – ограничение на количество столов согласно контракту с муниципалитетом,
x1 ³ 0, x2 ³ 0 – произведенное количество столов должно быть больше или равно 0,
x1, x2 – целые числа – план производства не может быть выражен в дробных числах.
1. Создайте следующую таблицу на Листе1 новой рабочей книги Excel:
| A | B | C | D | E | F | |
| 1 | Ресурсы | Столы | Стулья | Ограничения | Кол-во ресурсов | |
| 2 | Древесина | 0,5 | 0,04 | <= | 200 | |
| 3 | Трудоемкость | 12 | 0,6 | <= | 1800 | |
| 4 | Прибыль от ед. изд. | 180 | 20 | |||
| 5 | План производства | |||||
| 6 | ||||||
| 7 | Целевая функция |
Область ячеек, отмеченная серым цветом, предназначена для размещения результатов расчета: найденных значений объемов производства столов и стульев и значения целевой функции – суммарной прибыли мебельной фабрики.
2. В ячейки D2:D3 вставьте формулы для расчета объемов ресурсов, которые расходуются в процессе производства:
D2 = B2*B5+C2*C5; D3 = B3*B5+C3*C5.
3. В ячейки B7:C7 вставьте формулы для расчета прибыли от реализации соответственно столов и стульев: B7 = B4*B5; C7 = C4*C5.
4. В ячейку D7 вставьте формулу для расчета суммарной прибыли от реализации столов и стульев: D7 = B7+C7.
5. Для решения задачи вызовите меню Сервис è Поиск решения… В открывшемся диалоговом окне Поиск решения укажите:
· Установить целевую ячейку D7 равной: максимальному значению
|
|
|
· Изменяя ячейки B5:C5; ввести ограничения:
B5 >= 80 – ограничение на количество столов согласно контракту с муниципалитетом;
B5:C5 = целое – план производства не может быть выражен в дробных числах;
B5:C5 >= 0 – произведенное количество столов должно быть больше или равно 0;
D2 <= F2 – ограничение по первому ресурсу (древесине);
D3 <= F3 – ограничение по второму ресурсу (трудоемкости).
Для нахождения оптимального решения нажмите кнопку Выполнить. В результате получите значение целевой функции 42400 руб. при x1 = 80 и x2 = 1400.
6. Сохраните текущую рабочую книгу под именем Поиск-1.xls.
Задание 2: Транспортные задачи
В городе Сочи существует спрос на следующие товары:
| Наименование товара | Спрос, единиц |
| Товар1 | 500 |
| Товар2 | 600 |
| Товар3 | 500 |
| Товар4 | 5000 |
| Товар5 | 10000 |
Товары находятся в разных городах на складах. Запасы товара на складах (единиц) в различных городах представлены в следующей таблице:
| Наименование товара | Ростов | Москва | Ставрополь | Краснодар |
| Товар1 | 500 | 200 | 400 | 200 |
| Товар2 | 600 | 2500 | 2000 | 2300 |
| Товар3 | 1000 | 6000 | 200 | 2000 |
| Товар4 | 2000 | 2500 | 200 | 2356 |
| Товар5 | 3600 | 25000 | 2355 | 222 |
Стоимость доставки единицы товара в город Сочи (руб.) представлена в следующей таблице:
| Наименование товара | Ростов | Москва | Ставрополь | Краснодар |
| Товар1 | 2 | 4 | 2 | 2 |
| Товар2 | 20 | 50 | 30 | 23 |
| Товар3 | 40 | 80 | 60 | 30 |
| Товар4 | 1 | 4 | 2 | 1 |
| Товар5 | 225 | 500 | 150 | 600 |
Необходимо найти такой план перевозок товаров в город Сочи, при котором суммарная стоимость перевозок была бы минимальной, при условии полного удовлетворения спроса.
1. Создайте следующую таблицу на Листе2 текущей рабочей книги MS Excel:
| A | B | C | D | E | F | G | H | I | J | |
| 1 | Запасы товара на складах, ед. | |||||||||
| 2 | ||||||||||
| 3 | Наим. товара | Ростов | Москва | Ставрополь | Краснодар | |||||
| 4 | Товар1 | 500 | 200 | 400 | 200 | |||||
| 5 | Товар2 | 600 | 2500 | 2000 | 2300 | |||||
| 6 | Товар3 | 1000 | 6000 | 200 | 2000 | |||||
| 7 | Товар4 | 2000 | 2500 | 200 | 2356 | |||||
| 8 | Товар5 | 3600 | 25000 | 2355 | 222 | |||||
| 9 | ||||||||||
| 10 | Стоимость доставки единицы товара в г. Сочи, руб. | |||||||||
| 11 | ||||||||||
| 12 | Наим. товара | Ростов | Москва | Ставрополь | Краснодар | |||||
| 13 | Товар1 | 2 | 4 | 2 | 2 | |||||
| 14 | Товар2 | 20 | 50 | 30 | 23 | |||||
| 15 | Товар3 | 40 | 80 | 60 | 30 | |||||
| 16 | Товар4 | 1 | 4 | 2 | 1 | |||||
| 17 | Товар5 | 225 | 500 | 150 | 600 | |||||
| 18 | ||||||||||
| 19 | Результаты расчета | |||||||||
| 20 | ||||||||||
| 21 | Наим. товара | Ростов | Москва | Ставрополь | Краснодар | Итого | Max | Спрос | ||
| Товар1 | <= | = | 500 | |||||||
| Товар2 | <= | = | 600 | |||||||
| Товар3 | <= | = | 500 | |||||||
| Товар4 | <= | = | 5000 | |||||||
| Товар5 | <= | = | 10000 | |||||||
| 27 | ||||||||||
| 28 | Целевая функция | |||||||||
Область ячеек, отмеченная серым цветом, предназначена для размещения результатов расчета: найденных значений объемов перевозок из каждого пункта отправления и значения целевой функции – суммарной стоимости перевозок.
2. В ячейки F22:F26 вставьте формулы для расчета суммарного объема перевозок по каждому товару:
F22=СУММ(B22:E22); F23=СУММ(B23:E23); F24=СУММ(B24:E24); F25=СУММ(B25:E25); F26=СУММ(B26:E26).
2. Объем перевозок каждого товара не может превышать суммарного запаса этого товара, имеющегося на складах. В ячейки H22:H26 вставьте
|
|
|
формулы для расчета предельных объемов перевозок по каждому товару:
H22 = СУММ(B4:E4); H23 = СУММ(B5:E5); H24 = СУММ(B6:E6); H25 = СУММ(B7:E7); H26 = СУММ(B8:E8).
4. Целевая функция описывает суммарную стоимость перевозок всех товаров. Стоимость перевозки каждого товара равна произведению стоимости перевозки единицы товара на количество перевозимого товара. Суммарная стоимость перевозок равна сумме стоимостей перевозок всех товаров. В ячейку B28 вставьте формулу для расчета целевой функции:
B28 = СУММПРОИЗВ(B13:E17;B22:E26).
5. Для решения задачи вызовите меню Сервис è Поиск решения… В открывшемся диалоговом окне Поиск решения укажите:
· Установить целевую ячейку B28 Равной: минимальному значению;
· Измен я я ячейки B22:E26; введите ограничения:
F22:F26 <= H22:H26 – объем перевозок товара не может быть больше имеющегося в данный момент на складах количества данного товара;
F22:F26 = J22:J26 – объем перевозок должен полностью удовлетворять спрос на товар;
B22:E26 >= 0 – объем перевозок по каждому товару должен быть больше или равен 0;
B22:E26 <= B4:E8 – объем перевозок по каждому товару из каждого пункта отправления не должен превышать имеющегося в данном пункте отправления запаса данного товара.
Для нахождения оптимального решения нажмите кнопку Выполнить. В результате получите значение целевой функции 3220282 руб. при следующих объемах перевозок:
| Наименование товара | Ростов | Москва | Ставрополь | Краснодар |
| Товар1 | 250 | 0 | 125 | 125 |
| Товар2 | 600 | 0 | 0 | 0 |
| Товар3 | 0 | 0 | 0 | 500 |
| Товар4 | 2000 | 444 | 200 | 2356 |
| Товар5 | 3600 | 4045 | 2355 | 0 |
6. Сохраните текущую рабочую книгу: Файл è Сохранить.
Варианты дополнительных заданий
1). Торговое предприятие планирует организовать продажу 4-х видов товара (A, B, C, D), используя при этом только два вида ресурсов: рабочее время продавцов в количестве 840 ч. и площадь торгового зала 180 м2.
При этом известны плановые нормативы затрат этих ресурсов в расчете на единицу товаров A, B, C и D и прибыль от их продажи, которые представлены в следующей таблице:
|
|
|
| Ресурсы | Нормы затрат ресурсов на одно изделие | Общее количество ресурсов | |||
| A | B | C | D | ||
| Расход рабочего времени на единицу товара, ч. | 0,6 | 0,8 | 0,6 | 0,4 | 840 |
| Использование площади торгового зала на единицу товара, м 2 | 0,1 | 0,2 | 0,4 | 0,1 | 180 |
| Прибыль, руб. | 5 | 8 | 7 | 9 | - |
Необходимо найти оптимальную структуру товарооборота, обеспечивающую торговому пред-приятию максимум прибыли.
2). В пункте назначения N существует спрос на следующие товары:
| Наименование товара | Спрос, единиц |
| Товар1 | 8000 |
| Товар2 | 9000 |
| Товар3 | 15000 |
Товары находятся на нескольких складах. Запасы товара на складах (единиц) представлены в следующей таблице:
| Наименование товара | Склад1 | Склад2 | Склад3 | Склад4 | Склад5 |
| Товар1 | 6000 | 200 | 400 | 200 | 6000 |
| Товар2 | 6000 | 2500 | 2000 | 2300 | 2000 |
| Товар3 | 2300 | 8000 | 200 | 2000 | 6000 |
Стоимость доставки единицы товара в пункт назначения N (руб.) представлена в таблице:
| Наименование товара | Склад1 | Склад2 | Склад3 | Склад4 | Склад5 |
| Товар1 | 50 | 95 | 23 | 50 | 46 |
| Товар2 | 79 | 12 | 61 | 88 | 38 |
| Товар3 | 81 | 5 | 76 | 14 | 8 |
Необходимо найти такой план перевозок товаров в пункт назначения N, при котором суммарная стоимость перевозок была бы минимальной, при условии полного удовлетворения спроса.
