Задача коммивояжера в экселе

Решим один из вариантов задачи коммивояжера для небольшого количества городов (5−10) с помощью надстройки Поиск решения. Построим нелинейную модель.

Рассмотрим задачу коммивояжера (англ. Travelling Salesman Problem, TSP) заключающуюся в отыскании самого короткого маршрута, проходящего через заданные города по одному разу с последующим возвратом в исходный город (также рассмотрим вариант без возврата).

Задача

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

Решение

Так как даны координаты городов, то сначала найдем расстояния между ними (см. файл примера, лист 5 городов ).


Расстояния рассчитаем с помощью формулы:
= КОРЕНЬ((ИНДЕКС($C$7:$D$11;$F7+1;1) -ИНДЕКС($C$7:$D$11;G$6+1;1))^2 +(ИНДЕКС($C$7:$D$11;$F7+1;2) -ИНДЕКС($C$7:$D$11;G$6+1;2))^2)

Теперь создадим модель для Поиска решения.

Совет: Вводная статья про Поиск решения в MS EXCEL 2010 находится здесь.

Переменные (выделено зеленым). В качестве переменных модели следует взять номера городов. Так как начальная и конечная точка известны (Город0), то переменных будет 4.
Ограничения (выделено синим). Необходимо, чтобы номера городов не повторялись. Это означает, что количество уникальных (неповторяющихся) номеров городов должно быть равно 4. Для этого используется формула для подсчета уникальных значений:
= СУММПРОИЗВ(1/СЧЁТЕСЛИ(B16:B19;B16:B19))

Целевая функция (выделено красным). Длина маршрута должна быть минимальной.

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

Выберите Эволюционный метод поиска решения, т.к. созданная модель не является линейной из-за наложенного на переменные ограничения.

Найденное Решение

Поиск решения найдет (должен найти) самый короткий маршрут, т.е. последовательность 0−1−4−2−3−0 (или обратную).

В этой простейшей задаче можно проверить, действительно ли этот маршрут имеет минимальную длину, путем перебора всех вариантов маршрутов. Это реализовано в файле примера .

Совет. Таблицы перебора перестановок от 1 до 5, от 1 до 6, … от 1 до 9 можно найти в этой статье Перебор всех возможных Перестановок в MS EXCEL.

Результаты Эволюционного метода сильно зависят от начальных условий и параметров его настройки (скорость изменения, размер совокупности). Повторный поиск, с теми же начальными условиями и параметрами, может привести к различным результатам. Убедиться в этом можно с помощью модели, созданной в файле примера на листе 11 городов.

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

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

В файле примера также приведено решение задачи для замкнутого и незамкнутого маршрута посещения 9 городов. Решения найдены с помощью Эволюционного метода со следующими параметрами: Целочисленная оптимальность 0%, Использовать автоматическое масштабирование, Скорость изменения варьировалась 0,25−0,5; Размер совокупности варьировался 10−50.
Оба решения проверены с помощью таблиц перебора всех маршрутов (см. таблицы перебора перестановок). Если при первом прогоне Поиска решения не удавалось найти оптимальное решение, то он запускался повторно, при этом 1 или 2 параметра изменялись. После второго прогона оптимальное решение, как правило, было найдено.


Вывод: задачу коммивояжера (граф полностью связный, гамильтоновый цикл) можно решить стандартным Поиском решения с помощью построения нелинейных моделей. При количестве вершин графа (городов) Совет: Решение задачи о поиске кратчайшего пути между 2-мя заданными городами можно найти в статье Поиск решения MS EXCEL (6.4). Кратчайший путь (неполный граф, линейная модель).

Читайте также:  Извлечь айфон айтюнс что это

Метод полного перебора

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

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

Размер задачи, которую можно решить с помощью базовой версии этой программы, ограничивается такими предельными показателями: количество неизвестных (decision variable) — 200; количество формульных ограничений (explicit constraint) на неизвестные — 100; количество предельных условий (simple constraint) на неизвестные — 400.

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

«Сотруднику компании ООО «Новые технологии» Петрову Н.И. необходимо обновить программный продукт автоматизированного учета в пяти организациях: А, Б, В, Г и Д. Он решил начать свой обход с организации «А», так как она находится на первом этаже дома, в котором проживает Петров. Сотруднику необходимо, спланировать свой маршрут таким образом, чтобы к концу рабочего дня обойти все организации в определенном порядке и выполнив свою работу, вернутся домой (в пункт «А»). В каком порядке Петрову следует обходить организации, чтобы его замкнутый тур был кратчайшим? Если расстояния между каждой парой организаций заданы следующей квадратной матрицей (5×5):

Решение: Разместим исходные данные на рабочем листе (рис 2.1). Заменим знак числом 10000 (на результат решения исключение пути не оказывает влияния).

Рисунок 2.1 Исходные данные задачи

Ячейка Формула Примечание
B9 = СУММ(B4:B8) Распространяем на диапазон B9:F9
G4 = СУММ(B4:F4) Распространяем на диапазон G4:G8
C19 =СУММПРОИЗВ(B4:F8;B13:F17) Целевая функция
E19 =B4+C5+D6+E7+F8 Исключение пути
B23 =$C$10-C10+4*C5 Распространяем на диапазон B23:E23
B24 =$D$10-C10+4*C6 Распространяем на диапазон B24:E24
B25 =$E$10-C10+4*C7 Распространяем на диапазон B25:E25
B26 =$F$10-C10+4*C8 Распространяем на диапазон B26:E26

1. Запускаем надстройку MS Excel «Поиск решения» командой Сервис/ Поиск решения. И заполняем (рис. 2.2).

2. Для того чтобы выполнялись условия однократного посещения сотрудником организаций и в то же время запланированный Петровым маршрут был пройден полностью, введем ограничения: в строки B9, G4 заводим формулы из таблицы 1 и распространяем их на соответствующие диапазоны B9:F9 и G4:G8. Задаем следующие данные $B$9:$F$9=1 и $G$4:$G$8=1 в Ограничения окна «Поиск решения». Таким образом, мы можем отследить порядок обхода организаций сотрудником, оценить правильность выбора и оптимальность его маршрута.

3. Выбираем ячейку B19 и устанавливаем ее адрес в Целевую ячейку окна «Поиск решения», чтобы определить длину наикратчайшего маршрута. Для этого в ячейку B19 предварительно заносим соответствующую формулу из таблицы 1. Когда программа «Поиск решения» вычислит оптимальный маршрут Петрова и станет известен порядок обхода организаций (из Матрицы переменных) будут известны и расстояния между конкретными парами организаций. Затем при помощи простых математических подсчетов программа рассчитает протяженность оптимального маршрута.

Читайте также:  Доступ к файлу невозможен проверьте следующее excel

4. Устанавливаем еще одно ограничение в окно «Поиск решения»: $E$19=0. В указанную ячейку вводим формулу из таблицы 1 и исключаем таким образом, заведомо ложный порядок движения Петрова в порядке обхода организаций.

5. В связи с тем, что ячейки диапазона B4:F8 — изменяемые, в Ограничение окна «Поиск решения» необходимо добавить строку B$4$:F$8$=двоичное.

6. Заводим в ячейки B23; B24; B25; B26 соответствующие формулы из таблицы 1 и распространяем их на следующие диапазоны: B23:E23 B24:E24; B25:E25; B26:E26 для учета всех возможных вариантов обхода организаций сотрудником и выбора из них оптимального. Формулы задаем таким образом, чтобы обеспечить исключение ложного пути, соблюдая условие задачи об обходе всех организаций по одному разу.

7. Добавляем в Ограничения окна «Поиск решения» $B$23:$E$26 ≤ 3.

Рисунок 2.2 Окно «Поиск решения»

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

Рисунок 2.3 Окно «Параметры поиска решения»

Таким образом, получаем следующий результат. Если Петров переходит из организации в организацию, то на рис. 2.4 в диапазоне B4:F8 мы будем наблюдать порядок его перемещений. Если видим, что в ячейке, которая отнесена к организации «В» стоит единица, значит сотрудник посетил эту организацию следующей за пунктом «А». Если в ячейке ноль — сотрудник организацию не посещал.

Рисунок 2.4 Результаты решения задачи коммивояжера

В ходе анализа полученных результатов, приходим к выводу: наиболее оптимальным маршрут Петрова будет в том случае, если он начал свой путь с организации «А», посетит другие организации в следующем порядке «В», затем «Д», далее «Б» и «Г», из которой вернется к началу своего пути (в организацию «А»). представим путь схематически:

Длина кратчайшего маршрута (значение целевой ячейки) в результате составит — 21.

Задача решена. Кратчайший маршрут Петрова найден.

ЗАКЛЮЧЕНИЕ

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

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

Среди современных практических приложений задачи можно выделить: доставку продуктов в магазин со склада, работу почтальона по разноске корреспонденции, мониторинг объектов (нефтяные вышки, базовые станции сотовых операторов), изготовление отверстий на специализированном станке.

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

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

Читайте также:  Заходер где поставить запятую читать

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

Министерство образования и науки РФ

ГБОУ СПО «Шадринский политехнический колледж»

Заведующая учебной частью

____________ Блинова Н.А.

Курсовая работа

по дисциплине «Математические методы»

на тему «Задача о коммивояжере. Метод полного перебора»

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

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

— число прохождений пути из города в город . Переменная принимает значение 1, если коммивояжер переезжает из города в город и 0 в противном случае.

— расстояния между городами (в общем случае матрица несимметрична, т.е. .

Кроме того, должны быть заданы два вектора:

— ресурс выезда (число выездов из каждого города);

— ресурс въезда (число въездов в каждый город).

Целевая функция определяет пройденный путь коммивояжера, который должен быть минимальным. Она равна скалярному произведению матриц:

Множество допустимых решений ограничивается ресурсами въезда и ресурсами выезда:

— число прохождений пути из города в город не может превышать заданный ресурс выезда из города ;

— число прохождений пути из города в город не может превышать заданный ресурс въезда в город .

Кроме того, для предупреждения петель вводятся дополнительные условия:

или

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

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

В диалоговом окне «Параметры поиска решения» вводим ограничения въездов и выездов заданными ресурсами:

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

Не нашли то, что искали? Воспользуйтесь поиском:

Лучшие изречения: Как то на паре, один преподаватель сказал, когда лекция заканчивалась — это был конец пары: «Что-то тут концом пахнет». 8410 — | 8028 — или читать все.

78.85.5.224 © studopedia.ru Не является автором материалов, которые размещены. Но предоставляет возможность бесплатного использования. Есть нарушение авторского права? Напишите нам | Обратная связь.

Отключите adBlock!
и обновите страницу (F5)

очень нужно

Оцените статью
Adblock detector