Ячейки Н4:Н7 заполните маркером заполнения.

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‑му потребителю (например: а₁₁ стоимость доставки единицы продукции со Склада 1 в Контору, а₁₂ ‑ со Склада 1 для Цеха 1, а₁₃ ‑ со Склада 1 для Цеха 2, а₂₁ ‑ стоимость доставки единицы продукции со Склада 2 в Контору, а₂₂ ‑ Склада 2 для Цеха 1, а₂₃ ‑ Склада 2 для Цеха 2 и т.д.)

где х_ij план доставки продукции от i‑го поставщика j‑му потребителю (например: х₁₁ план доставки продукции со Склада 1 в Контору, х₁₂ ‑ со Склада 1 для Цеха 1, х₁₃ ‑ со Склада 1 для Цеха 2, х₂₁ ‑ план доставки продукции со Склада 2 в Контору, х₂₂ ‑ Склада 2 для Цеха 1, х₂₃ ‑ Склада 2 для Цеха 2 и т.д.)

По смыслу задачи каждое значение х_ij – целое положительное число.

При этом должны обеспечиваться ограничения по наличию продукции у поставщиков:

Где b_i наличие продукции у i‑го поставщика, (например: b₁ наличие продукции на Складе 1; b₂ ‑ на Складе 2, b₃ ‑ на Складе 3, b₄ ‑ на Складе 4.)

Необходимо выполнение ограничений по доставке продукции потребителю:

Где z_i запрос продукции у i‑ым потребителем, (например: z₁ запрос продукции Конторой; z₂ – Цехом 1, z₃ – Цехом 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 заполните маркером заполнения.