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

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

Оценка 4.8
doc
08.05.2020
Поиск решения. Уменьшение затрат на перевозку грузов
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

ЛАБОРАТОРНАЯ РАБОТА 5 Поиск решения

ЛАБОРАТОРНАЯ РАБОТА 5 Поиск решения

Выделите ячейки с A 4 по A 8

Выделите ячейки с A 4 по A 8

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

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