Поиск решения. Уменьшение затрат на перевозку грузов

  • doc
  • 08.05.2020
Публикация на сайте для учителей

Публикация педагогических разработок

Бесплатное участие. Свидетельство автора сразу.
Мгновенные 10 документов в портфолио.

Иконка файла материала 84. Поиск решения. Уменьшение затрат на перевозку грузов.doc

ЛАБОРАТОРНАЯ РАБОТА 5

Поиск решения. Уменьшение затрат на перевозку грузов.

 

ЗАДАЧА. Допустим, что ваша фирма занимается переработкой мяса на нескольких заводах, расположенных в разных районах Москвы. Мясо поставляется объединениями фермеров со складов, расположенных в нескольких городах Московской области. Стоимость мяса одинаковая, однако перевозка со склада на завод зависит от расстояния и отличается для каждого склада и завода. Потребность заводов в мясе различна, и запасы на каждом складе ограничены. Требуется определить: с какого склада, на какой завод поставлять, сколько мяса для минимизации общих затрат на перевозку.

 

Решение задачи:

Создайте на листе Транспортные расходы таблицу (рис. 8).

Для этого:

Ø  В ячейку А1 введите текст «Оптимизация транспортных потоков»;

Ø  В ячейку В2 введите текст «Потребители->»;

Ø  В ячейки С2:F2 введите названия мясоперерабатывающих заводов;

Ø  В ячейку А3 введите текст «Поставщики»

Ø  В ячейки А4:А8 названия складов.

Ø  Установите курсор в ячейку В4 и нажмите кнопку , после чего выделите ячейки с С4 по Е4. В строке формул появится формула =СУММ(С4:Е4). Нажмите кнопку , расположенную справа в строке формул, и формула будет введена.

Ø  Скопируйте содержимое ячейки В4 в ячейки В5:В8.

Ø  Выделите ячейки с С4 до F8. Введите цифру 1 и нажмите кнопку , Нажмите комбинацию клавиш Ctrl+D (автозаполнение столбцов в выделенной области), а затем нажмите Ctrl+R (автозаполнение строк в выделенной области). Все выделенные ячейки будут заполнены единицами. Установите формат ячеек выделенной области Числовой.

Ø  В ячейку A9 введите текст «Факт->».

Ø  В ячейку С9 введите формулу =СУММ(С4:С8). Скопируйте формулу в ячейки D9:F9.

 

Рисунок 8 – Пример таблицы

 

Подготовка первой части таблицы закончена. Каждое значение в ячейках на пересечении столбца конкретного завода и строки склада означает количество тонн, поставляемых в месяц с этого склада на данный завод. В нижней строке суммируется общее количество мяса, поставляемого на определенный завод, во втором столбце суммируется общее количество закупленного у конкретного склада мяса.

Введите требуемые объемы поставок и цены поставок. Для этого:

Ø  Введите в ячейку A10 текст «Запросы ->». В десятой строке вводятся значения потребляемого каждым из заводов мяса в тоннах.

Ø  В ячейки этой строки введите соответственно:

B11

C10

240

D10

115

E10

280

F10

370

 

 
300

B12

240

B13

170

B14

120

B15

320

 

Ø  Выделите ячейки с A4 по A8. Нажмите клавишу Ctrl и, не отпуская ее, подведите курсор мыши к краю выделенного интервала, нажмите левую клавишу мыши и двигайте мышь. Появится серый прямоугольник размером с выделенную область. Расположите его в ячейки с А11 по А15, затем отпустите клавишу мыши и клавишу Ctrl. Названия складов будут скопированы.

Ø  В ячейки второго столбца занесите объемы месячных запасов на различных складах в тоннах соответственно.

Ø  В ячейки с С11 по F15 занесите стоимость перевозки тонны мяса с конкретного склада на конкретный завод. Для этого введите в ячейки с С11 по F15 следующие данные:

47000

41500

45000

32650

39000

32300

38000

41000

23650

27300

21000

18000

19500

19400

9000

24000

39000

36000

27500

44000

 

Ø  В ячейку А16 введите текст «Всего».

Ø  В ячейку С16 введите формулу

=С4*С11+С5*С12+С6*С13+ С7*С14+С8*С15.

 

В ячейке С4 находится количество мяса, перевозимого со склада в Наро-Фоминске на завод в Лужниках, а в ячейке С11 — цена перевозки тонны груза по этому маршруту. Соответственно, первое слагаемое в формуле означает полную стоимость перевозок по данному маршруту. Вся же формула вычисляет полную стоимость перевозок мяса на завод в Лужниках.

 

Ø  Скопируйте формулу из ячейки С16 в ячейки D16:F16

Ø  В ячейку В16 введите формулу =СУММ(С16:F16). В данной ячейке будет вычисляться общая стоимость перевозки мяса.

Ø  В ячейку А18 введите текст «Всего на перевозки требуется», а в ячейку Е18 —«млн.руб.».

Ø  Для вычисления суммы в миллионах в ячейку D18 введите формулу =В16/1000000.

Выполните форматирование таблицы в соответствии с рис. 8. Скопируйте лист Транспортные расходы (Правка — Переместить/Скопировать лист) для возможного восстановления начального вида таблицы. Переименуйте скопированный лист, дав ему название Поиск решения.

Выполнить поиск решения (Сервис — Поиск решения) с целью определения минимальных затрат на перевозки при соблюдении следующих условий (рис. 9):

Ø  Объем поставок с конкретного склада должен быть меньше или равен запасам на складе.

Ø  Объем перевозок не должен быть отрицательным.

Ø  Запросы заводов должны быть выполнены полностью. Перевыполнение поставок допустимо, а недовыполнение — нет:

Рисунок 9 – Поиск решения

 

Сохраните результаты поиска решения. Проверьте правильность полученных результатов (рис. 10).

Рисунок 10 – Результаты поиска решения


Скачано с www.znanio.ru