1 Построение математической модели задачи

Составить математическую модель объекта оптимизации, значит определить переменные модели, составить целевую функцию, установить систему ограничений, которые накладываются на переменные. Таким образом, построение математической модели поставленной задачи осуществим в три этапа.

Определение переменных математической модели. Примем следующие обозначения

Обозначения

Номер склада (i=1,2,…,m)Номер пункта потребления (j=1,2,…,n)Количество продукта, имеющиеся на i-ом складеКоличество продукта, необходимое для j-го пункта потребленияСтоимость перевозки единицы продукта с i-го склада в j-ый пункт назначения

Так как требуется определить план перевозок груза, то переменными модели будут xij - количество груза, планируемого к перевозке от i - того производителя к j - тому потребителю, где i=1, 2, 3, j=1, 2, 3, 4.

Формирование целевой функции. Так как затраты на транспортировку груза по всем маршрутам известны, то общие затраты по всем направлениям составляют (в рублях):

f()=23х11+15х12+21х13+20х14+20х21+17х22+14х23+19х24+17х31+13х32+26х33+28х34

Это и есть целевая функция, минимум которой необходимо найти.

Можно дать следующую математическую формулировку целевой функции: определить значения переменных xij , i=1, 2, 3, j=1, 2, 3, 4, минимизирующих целевую функцию f().

Формирование системы ограничений. Проверим, выполняется ли условие баланса: мощность поставщиков равна:

=300+460+355=1115 единиц товара,

а общая потребность в грузе составляет

=350+400+190+260=1200 единиц товара.

Следовательно, условие баланса не выполнятся - транспортная задача открытая.

Т.к.

>, то должны быть учтены следующие ограничения на аргументы целевой функции

Ограничения

а) условие неполного удовлетворения потребностейб) условие полного распределения запасовв) условие неотрицательности x11+x21+x31 ? 350, x12+x22+x32 ? 400, x13+x23+x33 ? 190, x14+x24+x34 ? 260; x11+x12+x13+x14 =300, x21+x22+x23+x24 =460, x31+x32+x33+x34 =355xij ?0Вывод: задача курсовой работы относится к условной задаче линейного программирования на определение минимума целевой функции.

2 Реализация модели задачи в ms Excel

Составим на рабочем листе MS Excel две таблицы.

Заполним формулами, необходимыми для создания ограничений на запасы, ячейки G13:G15 столбца «Использовано запасов». Для этого в ячейку G13 вводим формулу =СУММ(B13:E13). Копируем эту формулу до ячейки G15.

оптимизационная модель табличный excel

Ввод исходных данных

Заполним формулами, необходимыми для создания ограничений на потребности, ячейки В17:Е17 строки «Удовлетворено потребностей».

Для этого в ячейку В17 вводим аналогично формулу =СУММ(B13:B15), которую копируем до ячейки Е17.

Запишем формулу для целевой функции. В ячейку А18 вводим текст: «Итоговые затраты на перевозку груза», а в ячейку G18 с помощью Мастера функций вводим формулу: =СУММПРОИЗВ(B5:E7;B13:E15) из категории Математические, где B5:E7 - массив 1, B13:E15 - массив 2. Полученная формула дает сумму из 12 попарных произведений указанных ячеек двух таблиц, которая и является для нас целевой функцией. В ячейки G18, G13:G15, В17:Е17 пока запишутся нулевые значения.

Укажем необходимые ссылки на ячейки и ограничения для целевой функции. Для этого во вкладке Данные выберем Поиск решения, после чего появится диалоговое окно Поиск решения.

Выбор надстройки Поиск решения

Заполним поля диалогового окна Поиск решения.

В поле Установить целевую ячейку указываем ячейку G18, для этого надо щелкнуть на этой ячейке, если она видна из-под диалогового окна, в противном случае следует щелкнуть на указателе перехода , чтобы свернуть окно (для возврата в диалоговое окно надо снова щелкнуть на указателе перехода). Результат - абсолютная ссылка $G$18.

Установим значение минимальному значению переключателя Равной, щелкнув на соответствующем кружке.

В поле Изменяя ячейки укажем диапазон ячеек $В$13:$Е$15 либо прямым выделением этого диапазона с помощью мыши, либо с использованием указателя перехода .

В поле Ограничения создаем список всех ограничений задачи после нажатия на кнопку Добавить .

Ввод ограничений в MS Excel

Ограничение Смысловое значение$B$13:$E$15>=0число перевозок не может быть отрицательным$B$17:$E$17<=$B$16:$E$16все потребности не могут быть удовлетворены из-за меньшей мощности производителей$G$13:$G$15=$F$13:$F$13все запасы производителей должны быть использованы

Каждое новое ограничение добавляется в этом же окне щелчком на кнопке Добавить. Заканчивается ввод по щелчку на кнопке ОК, при этом возвращается диалоговое окно.

Запускаем поиск оптимального решения кнопкой Выполнить. Диалоговое окно Результаты поиска решения сообщит об успехе поиска.

Ввод данных в надстройку Поиск решения

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

Сохраняем найденное решение.

Результат решения