Решение задачи средствами Microsoft Excel





Вызовите Microsoft Excel. В новой рабочей книге переименуйте "Лист№1" в ЛР 2 Вариант №… Отчет (Фамилия № группы)

Задание Исходных данных задачи

Начиная с ячейки с именем А1 на Листе окна Excel постройте следующую таблицу (рис. 1.):

Ячейки с адресами В4:Е7 резервируются для переменных (рис. 1) – это изменяемые ячейки. В ячейках F4:F7 заносятся запасы грузов в пунктах отправления, включая и


фиктивный, в ячейках B9:E9 заносятся потребности (спрос) в грузах в пунктах назначения.

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

В ячейки с адресами В15:Е18 занесены коэффициенты матрицы затрат на перевозку грузов.

Ячейки G4:G7 содержат формулы для расчета ограничений (6) – (9). Формула ограничения (6) занесена в ячейку G4 и имеет вид =СУММ(B4:E4). Соответственно ограничения (7), (8) и (9) занесены в ячейки G5, G6 и G7 в виде формул =СУММ(B5:E5), =СУММ(B6:E6) и =СУММ(B7:E7).

В ячейках В10:Е10 содержатся формулы ограничений (9)–(12), которые соответственно имеют вид: =СУММ(B4:B7), =СУММ(C4:C7), =СУММ(D4:D7), =СУММ(E4:E7).

В ячейки В15:Е18 занесены матрица затрат на перевозки между всеми пунктами отправления груза и назначения. Четвертая строка этой матрицы соответствует перевозкам от фиктивного поставщика.

Формула для расчета Целевой функции (13) занесена в ячейку С19 и имеет вид: =СУММПРОИЗВ(B15:E18;B4:E7). В эту же ячейку будет занесено вычисленное значение целевой функции.


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

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

Войти в меню Сервис, выбрать в нем Поиск решения и щелкнуть на нем левой кнопкой мыши. На экране появится диалоговое окно Поиск решения (рис. 2).

 

 
 


В поле Установить целевую ячейку заносится $С$19. Для этого проще всего установить курсор мыши внутрь ячейки, щелкнуть в ней левой кнопкой мыши, затем щелкнуть мышью на ячейке С17.

Поскольку ищется минимум целевой функции, то после слова Равной выделим Минимальному значению, щелкнув в кружочке мышью.

В поле Изменяя ячейки занесем диапазон $B$4:$Е$7 так как именно эти ячейки отведены под значения вычисляемых переменных. Для этого поставим курсор в поле Изменяя ячейки, затем поставим курсор на ячейке В4 и при нажатой левой кнопке мыши переведем курсор на ячейку Е7. В поле Изменяя ячейки появится необходимый диапазон ячеек.

В поле Ограничения занесем ограничения (6)–(12), (14), а также условия неотрицательности переменных. Для этого щелкнем мышью на кнопке Добавить. Появится диалоговое окно Добавление ограничения (рис. 3). Третье ограничение (рис. 2) занесено следующим образом: в окне Поиск решения щелкнуть на кнопке Добавить. Откроется диалоговое окно Добавление ограничения. В поле Ссылка на ячейку поставить курсор мыши нажать на левую ее кнопку. Затем поставить курсор мыши на ячейку G4, глее введено ограничение и при нажатой кнопке мыши провести вплоть до ячейки с адресом G7. В среднем поле окна Добавить ограничения выбираем знак равенства, а в последнем поле описанным выше способом заносим ячейки F4:F7, содержащие запасы поставщиков. Аналогично заносятся и другие ограничения (рис. 2).

Снова в поле Поиск решения (рис. 2). Щелкнуть мышью на кнопке Параметры.

На экране появится диалоговое окно Параметры поиска решения. В этом окне (рис. 4) устанавливаются параметры поиска решения. Здесь отметить квадратики Линейная модель, Неотрицательные значения, Автоматическое масштабирование. Щелкнем на кнопке ОК.

 
 

Снова попадаем в диалоговое окно Поиск решения. В этом окне (рис. 2) щелкнем левой кнопкой мыши на кнопку Выполнить. На экран выводится окно Результаты поиска решения (рис. 5).

Одновременно на Листе экрана также появляются результаты решения задачи (рис. 6): в столбце и строке Ограничения выводятся их рассчитанные значения. В ячейках В4:Е7 содержатся значения рассчитанных переменных – объемов перевозок от каждого пункта отправления к каждому пункту назначения. В ячейке с целевой функцией С19 – рассчитанное значение целевой функции.

Итак найдено решение: Fmin = 5200, x11 = 0, x12 = 0, x13.= 300, x14 = 300, x21 = 200, x22.= 600, x23 = 0, x24 =0, x31 = 700, x32.= 0, x33 = 0, x34.= 300, и фиктивные переменные x41 = x42 =+x44.= 0, x43.= 500.

 
 

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


Индивидуальные задания:

Вариант № 1.

Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе. Тарифы на перевозку единицы продукции, объёмы запасов продукции на складах, а также объёмы заказанной продукции представлены в таблице №1.

  Склад Магазины заказчики Запасы на складе (ед. прод)
  “Анна” “Вада” “Ева” “Алла” “Мех”  
“Таганка”            
“ВВЦ”            
“Щёлково”            
“Коньково”            
Объём заказа (ед. прод)            

 

Вариант № 2.

Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:

Таблица тарифов на перевозку продукции и объёмов запасов на складе и заказов:

Магазин   Склад “Росс- тек”   “Шер” “Тка-ни”   “Мода”   “Вита” Запасы на складе (ед.прод)
Иваново            
Москва            
Новгород            
Серпухов            
Объём заказа (ед.прод)            

 

Вариант № 3.

Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:

Таблица тарифов на перевозку продукции и объёмов запасов на складе и заказов:

Магазин   Склад “Всё для дома” “Здоровый сон”   “Фея” “Ночное царство”   “Мех” Запасы на складе (ед.прод)
“Вороново”            
“Фили”            
“Беляево”            
“Выхино”            
Объём заказа (ед.прод)            
               

 

Вариант № 4.

Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:

Таблица тарифов на перевозку продукции и объёмов запасов на складе и заказов:

Магазин   Склад   ВДНХ   Юго—Запад-ная   Фили Арбат-ская Соколь-ники Запасы на складе (ед.прод)
Пролетарская            
Митино            
Строгино            
Объём заказа (ед.прод)            

 

Вариант № 5.

Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:

Таблица тарифов на перевозку продукции и объёмов запасов на складе и заказов:

 

Магазин   Склад Тверь   Рязань Тула Чехов Запасы на складе (ед.прод)
Москва          
Санкт-Петербург          
Саратов          
Самара          
Объём заказа (ед.прод)          

 

Вариант № 6.

Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:

Таблица тарифов на перевозку продукции и объёмов запасов на складе и заказов:

Магазин   Склад   Сокол Риж-ская ВДНХ Киев-ская Царицыно Запасы на складе (ед.прод)
Пражская            
Волжская            
Курская            
Савёловская            
Объём заказа (ед.прод)            

Вариант № 7.

Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:

Таблица тарифов на перевозку продукции и объёмов запасов на складе и заказов:

Магазин   Склад Новго-род     Москва   Самара   Саратов   Тверь   Запасы складов (ед.прод)
Нижний Новгород   0,5        
Саратов       0,5      
Самара         0,5    
Санкт- Петербург       4,5    
Объём заказа (ед.прод)            

Вариант № 8.

Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:

Таблица тарифов на перевозку продукции и объёмов запасов на складе и заказов:

Магазин   Склад   “Колбасы”   “Мясо” “Мясные деликатесы” “Ди-на” Запасы на складе (ед.прод)
Черкизово     0,5    
Царицыно          
Бородино   2,5      
Вешняки     1,5    
Объём заказа (ед.прод)          

 

Вариант № 9.

Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:

Таблица тарифов на перевозку продукции и объёмов запасов на складе и заказов:

Магазин   Склад “Булоч-ная”     “Хлеб” “Сла- дос- ти” “Сдо-ба” “Сладко- ежка” Запасы на складе (ед.прод)
“Крекер” 2,5       1,5  
“Славянка” 3,5     1,6    
“Сластёна”     2,5      
Объём заказа (ед.прод)            

 

Вариант № 10.

Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:

Таблица тарифов на перевозку продукции и объёмов запасов на складе и заказов:

 

 

Магазин   Склад   “Диана”   ГУМ   ЦУМ   “Прага”   “Елена” Запасы на складе (ед.прод)
“Перово”     1,5      
“Волжская”            
“Пражская”     2,5   3,5  
“Беговая”   3,5     1,5  
Объём заказа (ед.прод)            

 

Вариант № 11.

Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:

Таблица тарифов на перевозку продукции и объёмов запасов на складе и заказов:

Магазин   Склад   Москва   Тверь Санкт-Петербург   Саратов Запасы на складе (ед.прод)
Москва     1,5    
Екатеринбург          
Саратов   2,5      
Вологда          
Объём заказа (ед.прод)          

 

Вариант № 12.

Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:

 

Таблица тарифов на перевозку продукции и объёмов запасов на складе и заказов:

Магазин   Склад   “Сумки”   “Мода”   “Анна” “Галан-терея”   Запасы на складе (ед.прод)
Выхино       2,5  
Арбатская   2,5 1,4    
Каховская          
Сокол 1,7   3,5 0,5  
Объём заказа (ед.прод)          

 

Вариант № 13.

Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:

Таблица тарифов на перевозку продукции и объёмов запасов на складе и заказов:

Магазин   Склад “Канц. товары” “Школь-ник” “Детский мир” “Учебные принадлеж-ности Запасы на складе (ед.прод)
Смоленская 1,5   0,5    
Митино 0,5     2,5  
Перово          
Калужская   0,5 1,5    
Объём заказа (ед.прод)          

 

Вариант № 14.

Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:

Таблица тарифов на перевозку продукции и объёмов запасов на складе и заказов:

Заказчик   Склад Типогра-фия №2 “Школь-ник” “Детский мир” “Книги” Запасы на складе (ед.прод)
Рижская   1,5   0,5  
Фили          
Выхино   0,5   2,5  
Объём заказа (ед.прод)          

 

Вариант № 15.

Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:

Таблица тарифов на перевозку продукции и объёмов запасов на складе и заказов:

Магазин   Склад “Свадебный салон”   ГУМ   ЦУМ “Салон для новобрачных” Запасы на складе (ед.прод)
Динамо     0,5    
Сокольники          
Кусково       0,5  
Объём заказа (ед.прод)          

 

Вариант № 16.

Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:

Таблица тарифов на перевозку продукции и объёмов запасов на складе и заказов:

Магазин Склад “Мужская одежда” “Руслан” ГУМ “Элита” Запасы на скла-де (ед.прод)
Профсоюзная       2,5  
Каховская   0,5 1,5    
Семёновская          
Пражская 3,5 1,5      
Объём заказа (ед.прод)          

 

Литература

1. Акулич И.Л. Математическое программирование в примерах и задачах. – М.: Высш. Шк., 1986

2. Костевич Л.С. Математическое программирование: Информ. технологии оптимальных решений. – Мн.: Новое знание, 2003

 

 



Поделиться:




Поиск по сайту

©2015-2024 poisk-ru.ru
Все права принадлежать их авторам. Данный сайт не претендует на авторства, а предоставляет бесплатное использование.
Дата создания страницы: 2017-12-29 Нарушение авторских прав и Нарушение персональных данных


Поиск по сайту: