– ставим точку напротив слов максимальному значению, это означает, что целевая ячейка должна быть максимальной. В действительности же программа будет искать маршрут с наибольшим коэффициентом использования пробега.
– устанавливаем поле изменяемых ячеек. В поле Изменяя ячейки: пишем
$C$71:$L$90
Рисунок 7 – Поле изменяемых ячеек и окно инструмента Поиск решения
Рисунок 8 – Окно Добавление ограничения
Задаём ограничения.
1) Каждая клетка поля изменяемых ячеек должна содержать либо «0», либо «1».
2) Нужно, что бы вершины контура ставились в клетки с цифрой, а не в пустую клетку.
3) Нужно, что бы контур состоял из вертикальных и горизонтальных отрезков.
4) Нужно, обеспечить попеременность клеток из оптимального плана возврата и заданного плана.
5) Нужно, что бы контур состоял как минимум из 4 вершин.
Для программы эти ограничения будут выглядеть следующим образом:
1) Нажимая на кнопку Добавить (рисунок 7) переходим в окно Добавление
ограничения (рисунок 8). В поле Ссылка на ячейку: нажимаем на значок
, мышкой выделяем поля C71:L90, нажимаем Enter, получаем запись $C$71:$L$90, в среднем поле из списка выбираем слово двоич ,
в поле Ограничения:автоматически пишется двоичное, таким образом мы получаем что все поле изменяемых
ячеек C71:L90
будет содержать двоичную систему.
2) Нажимая на кнопку Добавить (рисунок 7) переходим в окно Добавление
ограничения (рисунок 8). В поле Ссылка на ячейку: нажимаем на значок
, мышкой выделяем поля C71:L80, нажимаем Enter, получаем запись $C$71:$L$80, в среднем поле из списка выбираем знак «<=» (меньше или равно).
В поле Ограничения:нажимаем на значок 
, мышкой выделяем поля C58:L67,
нажимаем Enter, получаем запись $C$58:$L$67, получим запись в поле Ограничения (рисунок 7):
$C$71:$L$80<=$C$58:$L$67.
Эта запись означает, что числа ячеек C71:L80 (верхняя половина изменяемых ячеек) будут меньше либо равны числам ячеек C58:L67 (матрица №8).
Аналогично и для нижней части изменяемых ячеек:
$C$81:$L$90<=$N$58:$W$67
Например: если в ячейке С58 матрицы №8 стоит «0», то в ячейке С71 программа поставит только «0», а если в ячейке D59 стоит «1», то программа поставит в ячейке D72 либо «1» (вершину контура), либо «0» (вершины контура нет).
3) В ячейке C91 пишем формулу =СУММ(C71:C80), переносим формулу до конца строки. Таким образом, строка ячеек C91:L91 выражает сумму чисел по каждому столбцу поля ячеек C71:L80. Точно также и для поля C81:L90 составляем сумму по каждому столбцу в строке ячеек C92:L92. Добавляем ограничение:
$C$91:$L$91=$C$92:$L$92
То есть сумма чисел из верхней половины поля изменяемых ячеек будет равна сумме чисел из нижней половины по каждому столбцу. Это ограничение выражает принцип вертикальных отрезков.
В ячейке М71 пишем формулу =СУММ(C71:L71), переносим формулу до конца столбца. Таким образом, столбец ячеек М71:М90 выражает сумму чисел по каждой строке поля изменяемых ячеек. Добавляем ограничение:
$М$71:$М$80=$М$81:$М$90
То есть сумма чисел из верхней половины поля изменяемых ячеек будет равна сумме чисел из нижней половины по каждой строке. Это ограничение выражает принцип горизонтальных отрезков.
4) На каждую строку и каждый столбец каждой половины изменяемых ячеек должна приходиться одна вершина контура. Это задается следующими ограничениями:
$C$91:$L$91<=1
$M$71:$M$90<=1
5) Суммируем все вершины контура, в ячейке М91 пишем формулу =СУММ(M71:M90). Задаем ограничение:
$M$91>=4
2.4. Контур маршрута и показатели работы на маршруте.
Программа подставляет в поле не абсолютные «0» и «1», а приближенные к ним числа, поэтому нужно округлит их до целого числа. Функция =ОКРУГЛ(число;число_разрядов) означает операцию округления числа до ближайшего целого с точностью указанного числа знаков после запятой. Для этого в ячейки С96 (рисунок 9) пишем формулу
=ОКРУГЛ(C71;0)
Переносим формулы на всю матрицу, таким образом, получаем матрицу №10 (поле ячеек С96:L115).
Что бы показать маршрут в матрице №13 нужно сложить числа из верхней и нижней частей матрицы №10.
В ячейке C120 записываем
=C96+C106
Переносим формулы на всю матрицу, таким образом, получаем матрицу №12 (поле ячеек С120:L129).
Затем перемножаем матрицы №12 и №4. В ячейке C133 записываем
=C120*C44
Переносим формулу на остальные ячейки. Таким образом получаем контур маршрута с указанием количества перевозимого груза в матрице №13.
Рисунок 9 – Матрицы для вывода контура маршрута и показателей работы на маршруте
Далее считаем пробег с грузом и без него. Перемножаем матрицу №10 с таблицей расстояний: в ячейке N96 записываем формулу
=C96*Лист1!C3
Переносим формулу на поле ячеек N96:W105.
В ячейке N106 записываем формулу
=C106*Лист1!C3
Переносим формулу на поле ячеек N106:W115.Таким образом, получаем матрицу №12, в которой в верхней половине (N96:W105) показывается пробег с грузом, а в нижней части (N106:W115) пробег без груза. Чтобы получить общий пробег с грузом (ячейка X105) и без него (ячейка X115) нужно суммировать каждые части по отдельности: в ячейке X105 пишем
=СУММ(N96:W105)
а в ячейке X115
=СУММ(N106:W115)
Рядом с матрицей №13 записываем формулы для подсчета следующих параметров:
а) Пробег с грузом 
. В ячейке S132 пишем
=Х105.
б) Общий пробег 
. В ячейке S133 пишем
=X105+X115.
в) Коэффициент использования пробега 
. В ячейке S134 пишем (целевая ячейка)
=X105/(X105+X115+0,000001).
Раскрываем меню Сервис, открываем
инструмент Поиск решения, в поле установить целевую ячейку
нажимаем на значок 
,
мышкой нажимаем на ячейку S134,
нажимаем Enter, получаем запись $S$134.
На первом этапе программа подставляет только одни нули
в поле изменяемых ячеек (хотя это противоречит ограничениям) и считает целевую
ячейку, получается неопределённость 
, и программа выводит сообщение об
ошибке и прекращает дальнейшие вычисления. Чтобы этого избежать нужно в
знаменатель ввести коэффициент 0,000001 (столь малый коэффициент не
повлияет на точность), получим выражение
и программа продолжит дальнейшие вычисления.
г) Количество перевозимого груза. В ячейке S135 пишем
=МИН(C133:L142)
Эта функция выводит минимальное число из поля C133:L142.
Для более понятного отображения контура маршрута можно создать матрицу №14 (рисунок 10). В которой к каждой клетке будет показываться расстояние до поставщиков (вверху клетки), цифры из плана перевозок (внизу слева клетки), цифры из оптимального плана возврата порожних автомобилей (внизу справа клетки).
Для изображения расстояний до поставщиков в ячейке C147 пишем:
Уважаемый посетитель!
Чтобы распечатать файл, скачайте его (в формате Word).
Ссылка на скачивание - внизу страницы.