Рис. 1. Исходные данные для решения транспортной задачи

↑

⇐ ПредыдущаяСтр 22 из 24Следующая ⇒

Таблица 2

A

B

C

D

Количество

Прибыль/шт

Доход

Товар А

Товар В

Товар С

Всего

Изменяя значения ячеек В3:В5 нужно подобрать такие значения количества товаров, при которых может быть получен наибольший суммарный доход. При решении данной задачи должны быть учтены следующие ограничения:

· общий объем производства – всего 300 изделий в день;

· должно быть произведено не менее 50 изделий А;

· должно быть произведено не менее 40 изделий В;

· должно быть произведено не более 40 изделий С.

15. Введите в новый рабочий лист данные табл. 2 для вычисления прибыли от продажи трех видов продукции, причем в ячейки столбца D, и в ячейку B6должны быть введены формулы.

16. Запустите задачу поиска решений. Для этого выполните команду Данные/Поиск решения …и в диалоговом окне Поиск решениявведите данные:

·  в поле Установить целевую ячейку укажите адрес $D$6,щелкнув мышью по соответствующей ячейке;

·  установите переключатель Равной максимальному значению;

·  в поле Изменяя ячейки определите изменяемые ячейки ($B$3:$B$5);

· в поле Ограничения по одному добавьте каждое из следующих четырех ограничений задачи ($B$6=300; $B$3>=50; $B$4>=40; $B$5<=40),для чего:

- щелкните кнопку Добавить и в появившемся окне Добавление ограничениявведите ссылку на ячейку $B$6 (щелкая по ней мышью), оператор ограничения (=) и значение (300);

- для добавления следующего ограничения щелкните кнопку Добавить и повторите процедуру добавления ограничения;

- после ввода последнего ограничения щелкните кнопку ОК;

· в диалоговом окне Поиск решения щелкните кнопку Выполнить;

· в диалоге Результаты поиска решения установите переключатель Сохранить найденное решение, в окне Тип отчета выберите Результаты и нажмите кнопку OK;

· ознакомьтесь с отчетом по результатам, помещенным на новом листе.

17. С помощью средства Поиск решения решите задачу минимизации расходов на перевозку.

Постановка задачи

Компания имеет 3 склада, территориально расположенных в разных районах города. Заказы на перевозку грузов поступают из сети розничных магазинов, распределенных по всей территории города. Цель задачи – удовлетворить потребность всех шести розничных магазинов в товарах, находящихся на трех складах, и сохранить при этом общие расходы на перевозку на минимальном уровне.

Исходные таблицы с данными для решения поставленной задачи представлены на рис.1.

A

B

C

D

E

F

Стоимость перевозок грузов

Склад1

Склад2

Склад3

Магазин 1

Магазин 2

Магазин 3

Магазин 4

Магазин 5

Магазин 6

Потреб-
ность

Количество перевезенного
товара со склада

Переве-
зено

в товаре

Склад1

Склад2

Склад3

всего

Магазин 1

Магазин 2

Магазин 3

Магазин 4

Магазин 5

Магазин 6

Всего

Запасы на складе

Запасы после
отпуска

Стоимость
перевозок

Ячейки, которые должны содержать расчетные формулы, выделены серым фоном и жирным шрифтом значений.

Для осуществления поиска решения введите в новый рабочий лист все таблицы, представленные на рис. 1, причем в выделенные ячейки введите следующие формулы:

· в ячейку F12 введите формулу автосуммирования для вычисления суммы товаров, перевезенных в магазин 1 со всех трех складов:

=СУММ(С12:Е12);

· размножьте формулу на диапазон ячеек F13:F17;

· в ячейку В18 введите формулу для подсчета суммарной потребности в товаре:

=СУММ(В12:В17);

· размножьте формулу на диапазон ячеек С18:F18 для вычисления сумм товаров, перевезенных с каждого из трех складов в шесть магазинов и суммарного количества перевезенного товара;

· в ячейку С21 введите формулу для вычисления запасов товаров на складе 1 после отпуска товаров:

= С20-С18;

· размножьте формулу на диапазон ячеек D21:E21;

· в ячейку С24 введите формулу для вычисления суммы произведений стоимостей перевозок на количество перевозок во все магазины по каждому складу:

=СУММПРОИЗВ(С3:С8;С12:С17);

· размножьте формулу на диапазон ячеек D24:E24;

· в ячейку F24 введите формулу для подсчета общей стоимости перевозок для всех заказов:

=СУММ(С24:Е24)

Процедура поиска решений должна найти такие значения диапазона ячеек C12:E17 (количества перевезенных товаров с каждого из трех складов в каждый из шести магазинов), при которых каждый розничный магазин будет получать желаемое количество товара и общая стоимость перевозок (значение ячейки F24) будет минимальной. Нужно минимизировать значение, вычисляемое в ячейке F24, изменяя значения ячеек диапазона C12:E17 с учетом следующих ограничений (всего 27):

· количества необходимого товара для каждого розничного магазина, представленные в ячейках с B12 по B17, должны быть равны количествам перевезенного товара (ячейки с F12 по F17 соответственно), т.е. все заказы должны быть выполнены;

· значения в изменяемых ячейках (диапазон С12:Е17), соответствующие количествам перевезенных товаров с каждого из трех складов в каждый из шести магазинов, не могут быть отрицательными;

· количество запасов после отпуска на каждом складе (диапазон С21:Е21) не должно быть отрицательным.

   18. Самостоятельно решите задачу нахождения максимальной прибыли.

Познавательные статьи:



Техника нижней прямой подачи мяча

Комплекс физических упражнений для развития мышц плечевого пояса

Стандарт Порядок надевания противочумного костюма

Общеразвивающие упражнения без предметов