3.3. Доход от производства всех конфет одного сорта определяется как произведение плана производства конфет этого сорта на доход от производства 1 кг конфет этого сорта:
· Активизируйте ячейку С10;
· Составьте и наберите формулу;
Ячейки D10:G10 заполните маркером заполнения.
3.5. Самостоятельно заполните ячейку Н10, в которой рассчитывается доход от всего производства конфет (это сумма средств, полученных от производства конфет разного вида).
4. Командой Сервис/ Поискрешения вызовите диалоговое окно Поиска решения:
Внимание: Все ссылки в окне Поискарешения получают щелчком по соответствующей ячейке, диапазоны – выделяют в технике перетаскивания, а числовые значения набором с клавиатуры.
4.1. Опишите целевуюячейку:
· Щелчком вызовите ссылку на ячейку $Н$10;
· Определите её характер – максимальное значение.
4.2. Перетаскиванием выделите изменяемые ячейки : $C$8:$G$8;
4.3. Организуйте ограничения:
· Значения всех изменяемых ячеек – это положительные числа, т. е. первое ограничение: $C$8:$G$8≥0:
Подсказка: Если значение ограничения и условие для смежных ячеек совпадают, то несколько ограничений можно объединить в одно. Например $С$5>=8, $С$6>=8 и $С$7>=8 объединяется в ограничение $С$5:$С$7>=8.
· Нажмите кнопку Добавить окна Поиск решения;
· В левой части окна Добавление ограничений выделением определите ссылки на ячейки, на которые накладываются ограничения: $C$8:$G$8;
· Через раскрывающийся список определитевид ограничения: ≥;
· В правой части окна Добавление ограничений наберитезначение ограничения : 0;
· Нажмите кнопку Добавить окна Добавление ограничений.
· Расход сырья не может превышать его наличие, т. е. второе ограничение: $Н$3:$Н$7≤$В$3:$В$7:
· В левой части окна определите ссылки на ячейки, на которые накладываются ограничения: $Н$3:$Н$7;
· Через раскрывающийся список определитевид ограничения: ≤;
· В правой части окна наберитезначение ограничений : $В$3:$В$7;
· Нажмите кнопку Добавить.
· Значения всех изменяемых ячеек – это целые числа, т. е. третье ограничение: $C$8:$G$8=целое:
· Определите ссылки на ячейки, на которые накладываются ограничения: $C$8:$G$8;
· Выберитевид ограничения: ЦЕЛ;
· Нажмите кнопку Ок, т. к. это последнее вводимое ограничение.
4.4. Запустите поиск, нажатием кнопки Выполнить в окне Поиска решения.
4.5. Нажмите кнопку Ok, чтобы сохранить результат поиска решения в одноимённом окне.
5. Закройте MS Excel, сохранив файл в своей папке под именем Upr26.
УРОК 33-34.
Тема урока:
Решение задачи оптимального плана перевозок.
Упражнение.
Задание:
Требуется минимизировать затраты на перевозку товаров от поставщиков к потребителю. При этом надо учесть возможности каждого поставщика: наличие продукции на Складе №1 100 единиц продукции; на Складе №2 – 50 единиц продукции; на Складе №3 ‑200 единиц продукции; на Складе №4 ‑150 единиц продукции, и запрос каждого потребителя: Конторе требуется 200 единиц продукции; для Цеха 1 ‑ 120 единиц продукции; для Цеха 2 ‑ 100 единиц продукции.
Составьте план перевозок, если стоимости доставки на единицу продукции приведена в таблице:
| Стоимость доставки | ||||
| Поставщики | Потребители | |||
| Контора | Цех 1 | Цех 2 | ||
| Склад №1 | 5,00р. | 4,00р. | 6,00р. | |
| Склад №2 | 2,00р. | 3,00р. | 2,00р. | |
| Склад №3 | 3,00р. | 5,00р. | 4,00р. | |
| Склад №4 | 2,00р. | 7,00р. | 4,00р. |
Образец
Метод расчета:
Затраты на перевозку товаров считаются по формуле:
,
где аij стоимость доставки единицы продукции от i‑го поставщика j‑му потребителю (например: а11 стоимость доставки единицы продукции со Склада 1 в Контору, а12 ‑ со Склада 1 для Цеха 1, а13 ‑ со Склада 1 для Цеха 2, а21 ‑ стоимость доставки единицы продукции со Склада 2 в Контору, а22 ‑ Склада 2 для Цеха 1, а23 ‑ Склада 2 для Цеха 2 и т.д.)
где хij план доставки продукции от i‑го поставщика j‑му потребителю (например: х11 план доставки продукции со Склада 1 в Контору, х12 ‑ со Склада 1 для Цеха 1, х13 ‑ со Склада 1 для Цеха 2, х21 ‑ план доставки продукции со Склада 2 в Контору, х22 ‑ Склада 2 для Цеха 1, х23 ‑ Склада 2 для Цеха 2 и т.д.)
По смыслу задачи каждое значение хij – целое положительное число.
При этом должны обеспечиваться ограничения по наличию продукции у поставщиков:
Где bi наличие продукции у i‑го поставщика, (например: b1 наличие продукции на Складе 1; b2 ‑ на Складе 2, b3 ‑ на Складе 3, b4 ‑ на Складе 4.)
Необходимо выполнение ограничений по доставке продукции потребителю:
Где zi запрос продукции у i‑ым потребителем, (например: z1 запрос продукции Конторой; z2 – Цехом 1, z3 – Цехом 2.)
Ход выполнения:
1. Откройте MS Excel.
2. Заполните таблицу исходными данными по образцу:
Подсказка: Ячейки выделенные фоном содержат формулы, в остальные введите числа или текст
2.1. В ячейки А1:А7, А10:А15, B2:E3, C8:E8, A18:D18, B10:E11, C16:E16, F4:G7, F12:G15 наберите текст по образцу. Ячейки, содержащие текст можнообъединять, поскольку данные, находящиеся в них, не используются в расчетах при поискерешения.
2.2. Ячейки В4:Е7, С9:Е9, С12:Е15 заполните числами.
3. Организуйте в таблице формулы:
3.1. Вывоз продукции с каждого склада - это сумма доставленной продукции с этого склада каждому потребителю:
· Активизируйте ячейку В12;
· Составьте и наберите формулу;
· Ячейки В13:В15 заполните маркером заполнения.
