=Лист1!C3
В ячейке E147 пишем:
=Лист1!D3
Таким образом, заполняем все ячейки до конца первой строки.
Для второй строки в ячейке С149 пишем:
=Лист1!C4
В ячейке E149 пишем:
=Лист1!D4
И так далее заполняем все верхние ячейки каждой клетки матрицы №14.
Для изображения цифр из плана перевозок в ячейке C148 пишем:
=C133*C96
В ячейке E148 пишем:
=D133*D96
Таким образом, заполняем все ячейки до конца первой строки.
Для второй строки в ячейке C150 пишем:
=C134*C97
В ячейке E150 пишем:
=D134*D97
И так далее заполняем все нижние левые ячейки каждой клетки матрицы №14.
Для изображения цифр из оптимального плана возврата порожних автомобилей в ячейке D148 пишем:
=C133*C106
В ячейке F148 пишем:
=D133*D106
Таким образом, заполняем все ячейки до конца первой строки.
Для второй строки в ячейке D150 пишем:
=C133*C106
В ячейке F150 пишем:
=D133*D106
И так далее заполняем все нижние правые ячейки каждой клетки матрицы №14.
Рисунок 10 – Матрица №14 с контуром маршрута и показатели работы на маршруте
Далее указываем параметры инструмента Поиск решения. Раскрываем меню Сервис, открываем инструмент Поиск решения нажимаем кнопку Параметры, откроется окно Параметры поиска решения. В полях Относительная погрешность и Сходимость ставим число 0,01, ставим галочку напротив слов Неотрицательные значения. Нажимаем ОК.
Все условия для расчета первого маршрута выполнены, раскрываем меню Сервис, открываем инструмент Поиск решения нажимаем кнопку Выполнить, программа начнет рассчитывать первый маршрут. В матрице №13 и более понятно в матрице №14 программа выведет контур первого маршрута и показатели работы на маршруте (рисунок 10).
Так как формула для расчета количества груза выводит минимальное число из матрицы №13, а таким числом является ноль, то надо выделить мышкой все нули и удалить, нажав кнопку на клавиатуре Delete.
Рисунок 11 – Матрица №13 и матрица №14 с контуром маршрута и показатели работы на маршруте №1
Шифр маршрута будет такой:
А3 – Б1 – 11км – 160т; Б1 – А2 –13 км;
А2 – Б2 – 12 км – 160т; Б2 – А1 – 9км;
А1 – Б4 – 14 км – 160т; Б4 – А3 – 5км.
Коэффициент использования пробега на данном маршруте
.
3. Составления алгоритма решения второго маршрута.
3.1. Исключение цифр первого маршрута из совмещенной матрицы.
Нужно из совмещенной матрицы вычесть количество перевозимого груза первого маршрута (рисунок 11). Для этого выделяем оставшиеся цифры из первого маршрута (матрице №15) и складываем с неиспользованными цифрами из совмещенной матрицы (матрице №16).
В ячейке C173 записываем:
=(C133-$R$136)*C120
Переносим формулы на остальные ячейки, получаем матрицу №15. Знаки $ указывают на абсолютный адрес ячейки, то есть ячейка R136 (количество перевозимого груза) будет присутствовать во всех формулах матрицы №15. Например, в ячейке D173 будет следующая запись:
=(D133-$R$136)*D120
Матрица №15 отображает цифры из первого маршрута за минусом количества перевозимого груза в этом маршруте.
В ячейке N173 записываем
=N44-C120
Переносим формулы на остальные ячейки, получаем матрицу №16, которая отображает совмещённую матрицу без первого маршрута в двоичной системе.
Далее складываем матрицы №15 и №16, то есть, составляем совмещенную матрицу без первого маршрута.
В ячейке C187 записываем формулу
=C173+N173*C44.
Переносим формулы на остальные ячейки, получаем матрицу №17.
Рисунок 12 – Матрицы для исключения первого маршрута из совмещенной матрицы
Далее из матрицы №17 выделяем заданный план и оптимальный план возврата.
В ячейке C201 записываем формулу
=C187*C58.
Переносим формулы на остальные ячейки, получаем матрицу №18, которая содержит заданный план без первого маршрута.
В ячейке N201 записываем формулу
=C187* N58.
Переносим формулы на остальные ячейки, получаем матрицу №19, которая содержит оптимальный план возврата без первого маршрута.
3.2. Расчет второго маршрута.
Матрицы №18 и №19 будут являться исходными данными для расчета второго маршрута. Расчет маршрута будем вести на новом листе. Чтобы не заполнять все матрицы на новом листе заново, скопируем их. Для этого нажимаем и держим клавишу Ctrl затем нажимаем латинскую A (команда Ctrl +A «выделить всё»), далее нажимаем Ctrl +С – команда «копировать», переходим на новый лист (чтобы создать новый лист нажимаем правую кнопку мышки на закладку Лист1, из меню нажимаем на слово Добавить… , затем кнопку ОК) и нажимаем Ctrl +V – команда «вставить». Таким образом, на листе №3 получаем все матрицы со всеми формулами, но матрицы №4 и №5 нам не нужны, так как, маятниковые маршруты исключены, поэтому выделяем мышкой строки, с 14 по 39 и нажимаем на правую кнопку мышки, затем в вышедшем меню нажимаем Удалить. Далее меняем исходные данные в совмещенной матрице. На листе №3 (рисунок 13) в ячейке С4 записываем
=Лист2!C201
в ячейке D4 записываем
=Лист2!N201
в ячейке E4 записываем
=Лист2!D201
в ячейке F4 записываем
=Лист2!O201
Таким образом, заполняя первую строку, затем переносим формулы на остальные строки, получаем матрицу №3 на листе №3. Она показывает совмещенную матрицу без первого маршрута.
Так как мы удалили матрицы №4 и №5, меняем формулы матрицах №6, №8, №9. Для матрицы №6 в ячейке С18 пишем формулу
=МАКС(C4;D4)
в ячейке D18 пишем
=МАКС(E4;F4)
Таким образом, заполняем всю первую строку, затем переносим формулы на всю матрицу.
Для матрицы №8 в ячейке С32 пишем формулу
=ЗНАК(C4)
в ячейке D32 пишем
=ЗНАК(E4)
Таким образом, заполняем всю первую строку, затем переносим формулы на всю матрицу.
Для матрицы №9 в ячейке N32 пишем формулу
=ЗНАК(D4)
в ячейке O32 пишем
=ЗНАК(F4)
Таким образом, заполняем всю первую строку, затем переносим формулы на всю матрицу.
Далее аналогично первому маршрута составляем ограничения для инструмента Поиск решения и рассчитываем второй маршрут.
Рисунок 13 – Расчет маршрута №2 на листе №3
Расчеты следующих маршрутов ведём аналогично пункту 3.2. до тех пор, пока все маршруты не будут найдены. Для наглядности цифры можно выделить разными цветами, матрицы выделить в рамки. На слабых компьютерах можно задавать матрицы меньшего размера (8х8, 4х3 клеток и т.д.), задавать матрицы больше чем 10х10 клеток не рекомендуется. Полученный алгоритм решения нужно сохранить на жесткий диск, предварительно подставив нули в исходные данные и поля изменяемых ячеек (в матрице №13 в ячейки, где были удалены нули, нужно заново записать формулы), для использования в дальнейшем.
Уважаемый посетитель!
Чтобы распечатать файл, скачайте его (в формате Word).
Ссылка на скачивание - внизу страницы.