ЗАДАНИЯ ДЛЯ КОНТРОЛЬНОЙ РАБОТЫ № 1

 

Решить методом потенциалов транспортную задачу.

На трех заводах производится однородная продукция в количестве единиц. Четырем потребителям требуется соответственно единиц продукции. Расходы по перевозке единицы продукции с -го завода - му потребителю известны (см. Транспортную таблицу). Требуется спланировать перевозку продукции так, чтобы затраты на транспортировку были минимальными.

Транспортная таблица

Заводы	Потребители	Запас продукции, ед.
В1	В2	В3	В4
А1
А2
А3
Потребность в продукции, ед.

 

 

Исходные данные по вариантам заданий указаны в таблице:

 

Вар.

 

Решение транспортной задачи средствами Excel.

 

Пусть имеются 3 производителя и известны объемы запасов по каждому производителю: . Известна потребность в грузах каждого из 4 пунктов назначения . Задана матрица транспортных издержек от каждого производителя к каждому потребителю: . Необходимо рассчитать оптимальный план перевозок, т.е. определить, сколько груза должно быть отправлено из каждого i – го пункта отправления в каждый j – й пункт назначения c минимальными транспортными издержками.

Данные записываются в матричном виде:

 

Математическая модель задачи.

(2.1)

; (2.2)

; (2.3)

(2.6)

(2.5)

 

Подготовка к работе

Запустите Excel. Создастся новая книга с тремя листами.

Так как вам для работы потребуется только один лист, удалите лишние листы. Для этого щелкните мышью по ярлычку «Лист2» («Sheet 2»), затем по кнопке , расположенной рядом.

Далее нажмите клавишу Shift, и, не отпуская ее, щелкните мышью по ярлычку «Лист3» («Sheet 3»). Все листы со второго по третий выделятся белой заливкой.

Выполните команду меню Правка-Удалить листы (Edit-Delete Sheet). Появится диалог с предупреждением, в котором нажмите кнопку ОК. Все выделенные листы будут удалены.

Для удобства работы дайте осознанное имя листу рабочей книги. Для этого щелкните правой клавишей мыши по ярлычку «Лист 1» («Sheet 1»). Появится контекстное меню, в котором следует выбрать команду Переименовать (Rename). В появившемся диалоге Переименование листа (Rename) введите новое имя листа «Транспортная задача» вместо «Лист 1» («Sheet 1»). Нажмите кнопку ОК, после чего название листа изменится.

Нажмите кнопку . В появившемся диалоге Сохранение документа (Save as) введите имя файла «Транспортная задача» и нажмите кнопку Сохранить (Save).

Ввод данных и формул

Введите все названия, числа и формулы в таблицу, для чего вы полните следующие действия:

Щелкните мышью в ячейке А1 и введите «Транспортная задача: минимизация затрат на перевозку грузов».

Щелкните мышью в ячейке В2 и введите «Потребители» Таким же образом введите текст в соответствующие ячейки:

 

С2	B1
D2	B2
Е2	B3
F2	B4

Щелкните мышью в ячейке A3 и введите «Производители». Таким же образом введите текст в соответствующие ячейки:

 

A4	A1
A5	A2
A6	A3

 

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

Подведите курсор мыши к маленькому чёрному квадратику, расположенному в правом нижнем углу ячейки В4. Курсор изменится на Нажмите левую клавишу мыши, и, не отпуская ее, сдвиньте курсор вниз на две ячейки. Ячейки с В4 по В6 будут обведены рамкой. Отпустите клавишу мыши; при этом произойдет автозаполнение ячеек формулами.

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

На выделенном фрагменте нажмите правую клавишу мыши. В появившемся контекстном меню выберите команду Формат ячеек (Format Cells). На вкладке Число (Number) появившегося диалога Формат ячеек (Format Cells) выберите в Числовые форматы (Category) значение Числовой (Number) и нажмите кнопку ОК.

В ячейку В7 введите слово – «Факт»

Щелкните мышью по ячейке С7 и нажмите кнопку . В ячейку автоматически вставится формула =СУММ(С4:С6) (=SUM(C4:C6). Нажмите клавишу Enter и она будет введена.

Подведите курсор мыши к маленькому чёрному квадратику, расположенному в правом нижнем углу ячейки С7. Нажав левую клавишу мыши и передвинув курсор, размножьте формулу в ячейках с D7 по F7.

Мы подготовили первую часть таблицы, в которой находя изменяемые ячейки.

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

В ячейках С7-F7 суммируется общее количество товара, поставляемого определенному потребителю, а в ячейках В4-В6 суммируются количество товара, вывезенного от производителя.

Далее необходимо ввести требуемые объемы поставок и транспортные издержки. Для этого необходимо выполнить следующие действия:

Введите в ячейку В8 «Спрос». В ячейки С8-F8 вводятся компоненты вектора .

Выделите ячейки с А3 по А6. Нажмите клавишу Ctrl, и, не отпуская ее, подведите курсор мыши к краю выделенного интервала, нажмите левую клавишу мыши и двигайте мышь. Появится серый прямоугольник размером с выделенную область. Расположите его в ячейки с A10 пo A13, затем отпустите клавишу мыши и клавишу Ctrl. Названия производителей будут скопированы.

В ячейки B11 - В13 занесите компоненты вектора .

В ячейки с С11 по F13 занесите стоимость перевозки от конкретного производителя к каждому потребителю; компоненты матрицы . В ячейку А16 введите «Сумма транспортных издержек».

В ячейку С16 введите формулу: =С4*С11+С5*С12+С6*С13, которая соответствует сумме из формулы (2.1).

Подведите курсор мыши к маленькому квадратику, расположенному в правом нижнем углу рамки ячейки вокруг ячейки С16. Нажав левую клавишу мыши и передвинув курсор, размножьте формулу ячейках с D16 по F16. Таким образом, вы поместили в соответствующие ячейки формулы ,

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

Рис. 2.1. Исходные данные.

Итак, все данные в таблицу занесены (Рис. 2.1). Вы даже можете самостоятельно подобрать вариант решения, меняя числа в ячейках с С4 по F6. Однако не следует заниматься этим утомительным и малоинтересным делом - Excel самостоятельно подберет оптимальный вариант решения.

 

Поиск решения

Щёлкните мышью по ячейке В16 и выполните команду меню Сервис-Поиск решения. (см рис. 2.2).

Рис. 2.2

На экране появится диалог Поиск решения (Рис 2.3).

Рис. 2.3. Диалог Поиск решения (Solver Parameters).

 

В поле Установить целевую ячейку появится адрес $B$16.

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

Установите переключатель Равной (Equal to) в положение Минимальному значению (Min). Это означает, что при переборе вариантов решений Excel будет стремиться минимизировать значение в ячейке В16, где находится общая стоимость транспортных издержек.

В поле Изменяя ячейки (By Changing Cells) введите $C$4:$F$6. Это означает, что для достижения минимальной стоимости перевозок будут меняться значения в ячейках с С4 по F6, то есть будут изменяться количество груза, перевезенного по конкретному маршруту.

Если сейчас запустить процесс подбора параметров, то будет найден вариант, где все переменные равны нулю. И это правильно если не перевозить ничего, то это самый дешевый вариант.

Но нам необходимо привезти товар, поэтому надо наложить некоторые ограничения для поиска решения.

В группе полей Ограничение: (Subject to the Cоnstraints) нажмите кнопку Добавить (Add). Появится диалог Добавление Ограничения (Add Constraint) (рис. 2.4)

Рис. 2.4. Диалог Добавление Ограничения (Add Constraint)

 

Следует ввести левую часть ограничения в левое поле, выбрать знак условия, накладываемого на значение и ввести правую часть ограничения; нажать кнопку Добавить(Add).

По окончании ввода всех ограничений нажмите кнопку ОК. В диалоге появятся строки введённых ограничений (Рис. 2.5.)

Рис. 2.5 Диалог Поиск решения с введёнными ограничениями.

 

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

Первое условие $В$4:$В$6 = $В$11:$В$13 соответствует ограничению (2.2).

Второе условие $C$4:$F$8 >= 0 означает, что объем перевозок не может быть отрицательным – ограничение (2.6). Грузопоток имеет только одно направление - от производителей к потребителям.

И, наконец, третье, и последнее условие $C$7:$F$7 = $C$8:$F$8, соответствует ограничению (2.3).

Введенные условия должны позволить найти наиболее оптимальный вариант решения задачи. Нажмите кнопку Выполнить (Solve) и Excel 7 начнет подбор решения

После нахождения решения появляется диалог Результаты поиска решения (Solver Results) (Рис. 2.6).

Рис. 2.6. Диалог Результаты поиска решения (Solver Results)

Нажав кнопку ОК, вы занесете вариант решения на рабочий лист (рис. 2.7).

Рис. 2.7 Решение транспортной задачи.

Минимальная сумма затрат на перевозки при соблюдении всех условий равна 3165 усл. ден. ед.

При выборе в поле Тип отчета (Reports) в диалоге Результаты поиска решения (Solver Results) одного или нескольких типов отчетов Excel вставляет дополнительные листы с отчетами в рабочую книгу. В этих отчетах содержится информация, которая может заинтересовать человека, знакомого с численными методами вычислений, применяемыми в Excel.