Цель: Изучить возможности Excel для решения классических транспортных задач.
Теоретические сведения и рекомендации к выполнению заданий
При перевозке груза от поставщиков потребителям кампания – перевозчик часто сталкивается со следующей проблемой. Пусть имеется m поставщиков A1, A2,…Amнекоторого товара, который нужно доставить n потребителям B1, B2,….Bn.Известно количество данного товара у каждого поставщика (предложениеР1, Р2,……Рm), а также потребности каждого потребителя (спросС1, С2,….Сn). Также известны стоимости доставки единицы товара aij от поставщика Ai потребителю Bj. Построить оптимальный план доставки товара, максимально удовлетворяющий спрос/предложение, и минимизирующий стоимость доставки всего товара.
Эта задача относится к классу так называемых традиционных транспортных задач. Методы поиска оптимального решения вполне изучены, однако даже при небольшой размерности транспортной матрицы аналитическое решение весьма громоздко. Возможности Excel, а именно встроенный модуль «Поиск решения», позволяют получать оптимальное решение для матриц большой размерности практически моментально.
Построение математической модели и реализация решения средствами EXCEL
В1 | В2 | В3 | В4 | Предложение | |
A1 | a11 | a12 | a13 | a14 | Р1 |
A2 | a21 | a22 | a23 | a24 | Р2 |
A3 | a31 | a32 | a33 | a34 | Р3 |
Спрос | С1 | С2 | С3 | С4 |
Обозначим переменные x11, x12, ……xmn,
гдехij – количество единиц товара, поставляемого от поставщика Аi потребителю Вj.
|
|
Целевая функция (суммарные затраты на доставку товара)
F(x) = а11 х11 + а12 х12 + а13 х13 + ……+ аmn хmn→ min
Система ограничений по спросу (количество доставленного товара не превышает спрос на этот товар):
х11 + х21 + ….+ хm1 ≤ С1
х12 + х22 + ….+ хm2 ≤ С2
……………………………..
х1n + х2n + ….+ хmn ≤ Сn
Система ограничений по предложению (поставщик не может поставить больше, чем есть в наличии):
х11 + х12 + ….+ х1n ≤ P1
х21 + х22 + ….+ х2n ≤ Р2
……………………………
хm1 + хm2 + ….+ хmn ≤ Рm
Сбалансированная модель:
Цель: найти такие значения переменных x11, x12, ……xmn, чтобы максимально реализовать спрос-предложение и при этом доставить минимум целевой функции F(x).
Алгоритм реализации решения в Excel.
1. Сначала определяют сбалансированность модели, т.е. вычисляют (с помощью автосуммы) суммарный спрос и предложение. Если модель сбалансирована, ограничения будут иметь вид равенств, в противном случае ограничения будут неравенствами.
2. Переменным x11, x12, ……xmn, (в табличной форме) присваивают некоторые произвольные значения (опорный план). В ходе реализации решения эти значения будут автоматически изменены.
3. По известному (опорному) плану вычисляем целевую функцию (общую стоимость доставки товара).
|
|
Теперь можно заполнять модуль «Поиск решения» (Главное меню/Сервис/ Поиск решения).
Результат вычислений получим, нажав кнопку «Выполнить».
Например, для исходных данных, содержащихся в диапазоне A1:G6, содержимое B2:F5 – стоимости доставки единицы товара aij от поставщика Ai потребителю Bj, . G2: G5 – наличие товара у поставщиков, В6: F6 – спрос потребителей на товар, результат выполнения задания: І8 – общая стоимость доставки, наименьшая из всех возможных, B9:F12 – количество товара, поставляемого от поставщика Аi потребителю Вj.
Дата добавления: 2018-05-12; просмотров: 204; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!