Установить целевую ячейку G7 равной максимальному значению.
Тогда затраты на перевозку составят 280 тыс. руб. и весь товар будет взят со второго склада.
Задача 4. Положим имеется неохваченный связью регион, в котором расположены пять поселков А, Б, В, Г, Д с координатами Xi, Yi. Требуется найти такие координаты Xs, Ys (клетки B7 и C7 на рис. 2-4) расположения телефонной станции, чтобы суммарное расстояние от нее до всех поселков было минимально.
Здесь надо вычислить радиусы (вспомним теорему Пифагора) от станции до каждого из поселков, а затем минимизировать их сумму (D7). После определения положения станции следует построить точечную диаграмму их расположения, где точку Xs, Ys выделить другим цветом. Затем изменить координаты каких-либо поселков и и посмотреть, что произойдет после новой оптимизации. Решите задачу самостоятельно.
Задача 5. Задача о рюкзаке. Имеется 6 предметов (А-Е), о которых известны их вес и цена. Выбрать такие из них, чтобы их вес не превышал 20 кг, а суммарная цена была максимальной. Ответ должен быть получен в двоичной форме 1/0 (выбран/не выбран). В C8 вносим формулу =СУММПРОИЗВ( D2:D7;C2:C7). В окне Поиск решений задаем параметры:
Установить целевую ячейку: C8 равной значению: 20.
Изменяя ячейки: D2:D7. Ограничения: D2:D7=двоичное.
| A | B | C | D | E | F | |
| Р1 | Р2 | Р3 | Р4 | |||
| И1 | ||||||
| И2 | ||||||
| И3 | ||||||
| И4 | ||||||
| Стоимость: | ||||||
| И1 | ||||||
| И2 | ||||||
| И3 | ||||||
| И4 | ||||||
| Рис.2- 6 |
На рис. 2-5а показана исходная таблица, на рис. 2-5б – после оптимизации. Видим – выбраны предметы А, В, Г, Е.
| A | B | C | D | A | B | C | D | - | C | D | |||
| Поселки | X | Y | Радиус | Предмет | Цена | Вес | Выбор | Вес | Выбор | ||||
| А | ? | А | |||||||||||
| Б | ? | Б | |||||||||||
| В | ? | В | |||||||||||
| Г | ? | Г | |||||||||||
| Д | ? | Д | |||||||||||
| S (станция) | ? | Е | |||||||||||
| Рис.2-4 | Всего: | Рис.2-5а | Рис.2-5б |
Задача 6. Задача о назначениях. Имеется (рис. 2-6) четыре вида работ (Р1-Р4) и четыре исполнителя (И1-И2). Известна стоимость выполнения каждой работы каждым из исполнителей (область B2:E5). Нужно назначить каждого работника на одну из работ так, чтобы общая стоимость работ (E7) была минимальна. Создадим таблицу назначений (A8:E11). Первоначально она пуста. Нам понадобятся функция
E7=СУММПРОИЗВ(B8:E11; B2:E5),
а также суммы по вертикали: F8÷F11 и горизонтали: B12÷E12.
В окне Поиск решения вводим параметры:
Установить целевую ячейку: E7 Равной: минимальному значению
Изменяя ячейки: B8:E11
Ограничения: B8:E11=двоичное; B12:E12=1; B8:E11=1
Условия B12:E12=1; B8:E11=1 обеспечивают назначение единственного рабочего на единственную работу.
После оптимизации видим, что общая стоимость работ составила 16 единиц.
| Задание. Найти графическое решение задачи линейного программирования (варианты см. ниже), а затем проверить его, пользуясь средствами Excel. Здесь следует определить максимальное и минимальное значения целевой функции F(А, В) и значения аргументов, при которых они получены. Для всех вариантов: А³0, В³0. |
| 1А+2В£10 | 7А+2В³14 | 3А+1В³9 | 4А+4В£16 | 2А+1В£10 | 2А+2В³4 | 4А-2В£12 | 7А+2В³14 | 2А+1В£10 | 2А+3В£18 |
| -2А+3В£6 | 5А+6В£30 | 1А+2В£8 | 1А+2В³2 | -1А+2В£2 | 6А+8В£48 | -1А+3В£6 | -1А+2В£2 | -2А+3В£6 | 1А-2В£2 |
| 4А+6В³24 | 3А+8В³24 | 1А+6В³12 | -1А+1В£-1 | 2А+4В³8 | 2А-2В£4 | 2А+4В³8 | 4А+6В£24 | 2А+4В³8 | 2А-1В³6 |
| 1А+1В=F | -2А+5В=F | 4А+6В=F | 2А+5В=F | 1А+1В=F | 5А+4В=F | 1А+2В=F | 3А-2В=F | 2А+3В=F | 4А+2В=F |