Решение задач оптимизации с помощью MS Excel

Целевая функция - минимум стоимости рациона:1,5x₁+2,3x₂->min

:

Рисунок 6 - Ввод исходных данных в ячейки

Итак, в ячейки А2 и А3 вводим начальные значения x₁ и x₂ - нули.

В ячейки А4 и А5 вводим левые части ограничений (первоначально получатся нули), в ячейки В4 и В5 - правые части соответствующих ограничений.

В ячейку А6 вводим целевую функцию. Ввод исходных данных завершен.

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

Последовательностью команд меню Сервис - Поиск решения вызываем инструмент "Поиск решения".

Рисунок 7 - Надстройка Поиск решений

Итак, с использованием красной стрелки (переход на рабочий  лист) Устанавливаем целевую ячейку - $A$6 равной минимальному значению

Изменяя ячейки - $A$2:$A$3 с использованием кнопки Добавить последовательно добавляем три  исходных ограничения. Нажимаем кнопку Выполнить.

После вычислений на рабочем  листе получили следующие результаты.(Рисунок 8).

Рисунок 8 - Таблица с полученными результатами

При кормлении:

• 0,909 кг продукта М ;
• 0,318 кг продукта N

Потребности животного в питании будут удовлетворены, при этом стоимость рациона будет минимальной и составит 2,10 руб.

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

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

Было выявлено, что использование оптимизации в Excel помогает более быстрыми способами решать задачи [1].

 

2 Решение задач оптимизации  с помощью табличного процессора  Excel

Распределение и транспортировка продуктов  по отелям

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

Имеются следующие  исходные данные.

Наличие продуктов  на складах.

Таблица 1 –  Наличие продуктов на складах

Склады	Наличие продуктов
Точка №1	200
Точка №2	190
Точка №3	220
Точка №4	145
Точка №5	280

Потребность в различных продуктах.

Таблица 2 - Потребность  в различных продуктах

Отели	Потребность в  продуктах
1 отель	200
2 отель	150
3 отель	220
4 отель	330

Расстояния между складами и пунктами доставки.

Таблица 3 - Расстояния между складами и пунктами доставки

	Отель 1	Отель 2	Отель 3	Отель 4
Точка№1	6	4	5	11
Точка №2	12	6	4	9
Точка №3	15	7	10	4
Точка №4	9	5	12	5
Точка №5	3	7	12	11

На пересечении  столбца конкретного пункта доставки со строкой склада находится информация о расстояниях между этими пунктом доставки и складом. Например, расстояние между 3 пунктом и точкой №3 равно 10 километрам.

Теперь данные необходимо занести в Excel (рис. 1).

Рисунок 1 – Изменяемые ячейки

Значения ячеек  по столбцу В с четвертой по восьмую строку определяются суммированием данных ячеек соответствующих строк начиная со столбца С до столбца F .

Например, значение ячейки B4=СУММ(C4:F4)

Значения ячеек  по 9 строке по столбцам от С до F определяются суммированием данных ячеек соответствующих столбцов с 4 по 8 строки.

Например, значение ячейки С9=СУММ(C4:C8).

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

В нижней строке (строка 9) суммируется общее количество продуктов, поставляемых в определенный отель, а во втором столбце (столбец В) суммируется количество доставленного с конкретного склада.

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

Рисунок 2 –  Исходная информация

В строке 16 по столбцам C-F определяется грузооборот по каждому пункту доставки. К примеру, для 1 пункта (ячейка С16) это рассчитывается с помощью формулы

С16=С4*С11+С5*С12+С6*С13+С7*С14+С8*С15

либо с помощью функции СУММПРОИЗВ

С16=СУММПРОИЗВ(C4:C8;C11:C15)

В ячейке С4 находится количество продуктов, перевозимых с точки №1 в 1 отель, а в ячейке С11 – расстояние от точки №1 до 1 отеля. Соответственно первое слагаемое в формуле означает полный грузооборот по данному маршруту. Вся же формула вычисляет полный грузооборот перевозок продуктов в 1 отель.

В ячейке В16  по формуле  =СУММ(С16:F16) нужно вычислить общий объем грузооборота продуктов.

Таким образом, информация на рабочем листе примет следующий вид (рис. 3).

Рисунок 3 – Рабочий лист, подготовленный для решения транспортной задачи

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

После выбора данной команды появится диалоговое окно (рис. 4).

Рисунок 4 – Диалоговое окно Поиск решения

Поскольку в качестве критерия оптимизации была выбрана минимизация грузооборота, то в поле Установить целевую ячейку необходимо ввести ссылку на ячейку, содержащую формулу расчета общего объема грузооборота продуктов. В нашем случае это ячейка $B$16. Чтобы минимизировать значение конечной ячейки путем изменения значений влияющих ячеек (влияющими, в данном случае это и изменяемые ячейки, являются ячейки, которые предназначены для хранения значений искомых неизвестных), метку нужно установит в положение к минимальному значению.

В поле Изменяя ячейки нужно ввести ссылки на изменяемые ячейки, разделяя их запятыми; либо, если ячейки находятся рядом, указывая первую и последнюю ячейку, разделяя их двоеточием ($С$4:$F$8). Это означает, что для достижения минимального грузооборота перевозок будут меняться значения в ячейках с С4 по F8, то есть будут изменяться количество груза, перевезенного по конкретному маршруту.

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

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

Рисунок 5 – Диалоговое окно Добавление ограничения

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

Рисунок 6 – Диалоговое окно Поиск решения с заполненными полями

Для изменения  и удаления ограничений в списке Ограничения диалогового окна Поиск решения существуют команды Изменить и Удалить.

Первое условие $B$4:$B$8 <=$B$11:$B$12. Оно означает, что значение в ячейке В4 должно быть меньше или равно значению в В11, в В5 меньше или равно, чем в В12, и так далее до В8 и В15.

В ячейках с В4 по В8 на листе находятся объемы поставок с конкретных складов. В ячейках с В11 по В15 – запасы на этих же складах. Так как невозможно вывести со склада больше, чем на нем есть, первое значение должно быть не больше второго.

Второе условие $С$4:$F$8>=0. Оно означает, что объем перевозок не может быть отрицательным, то есть, если на складе не хватает продуктов, их не везут с пункта доставки, на который эти продукты были завезены ранее. Грузопоток имеет только одно направление – от складов к пунктам доставки удобрений.

Третье, и последнее условие $С$9:$F$9>=$C$10:$F$10. Оно означает, что значения в ячейках девятой строки должны быть больше или равны значениям в ячейках десятой строки, то есть запросы пунктов доставки минеральных удобрений должны быть выполнены полностью. Перевыполнение объема поставок допустимо, а недовыполнение – нет.

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

Рисунок 7 – Диалоговое окно Результаты поиска решения

Нажав кнопку ОК, заносим результат на лист (рис. 7).

 

Рисунок 7 - Решенная транспортная задача

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

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

Оптимизация помогает более быстрыми темпами находить решения поставленных задач.

 

Заключение

Тема курсовой работы – “Решение задач оптимизации с помощью MS Excel”.

Объем работы – 30 листов.

При написании  курсовой работы была поставлена следующая цель –раскрыть понятие “оптимизация” и научиться применять ее методы в решении задач.

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

• изучить основные сведения об оптимизации;
• рассмотреть решение задачи оптимизации с помощью табличного процессора Excel.

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

• поставка сырья;
• оптимальный выпуск продукции;
• оптимальное управление запасами ;
• оптимальное распределение ресурсов;
• планирования инвестиций;
• оптимальный рацион (смесь, сплав);
• назначение на должность.

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

Решение задач оптимизации состоит в поиске оптимального плана с использованием математических моделей и вычислительных методов, которые реализуются с помощью компьютеров и специальных программ-оптимизаторов. Все расчёты сделаны популярной оптимизационною программою Поиск решений, встроенной в табличную программу MS Excel.

С ее помощью  можно определить, при каких значениях  указанных влияющих ячеек формула  в целевой ячейке принимает нужное значение (минимальное, максимальное или равное какой-либо величине). Для процедуры поиска решения можно задать ограничения, причем не обязательно, чтобы при этом использовались те же влияющие ячейки. Для расчета заданного значения применяются различные математические методы поиска. Вы можете установить режим, в котором полученные значения переменных автоматически заносятся в таблицу. Кроме того, результаты работы программы могут быть оформлены в виде отчета.

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