Пример решения задачи
Обязательное условие для расчетов: в таблице 7 и 8 нужно установить числовой формат ячейки без знаков после запятой (, числовые форматы — Числовой. Число десятичных знаков — 0). В диалоговое окно «Поиск решения» заносятся целевая ячейка, диапазон изменяемых ячеек и ограничения Свод параметров модели представлен в таблице 9. При введении ограничения двоичности может возникнуть сложность в введении… Читать ещё >
Пример решения задачи (реферат, курсовая, диплом, контрольная)
Рассмотрим условный пример. Допустим, нам необходимо сформировать развозочные маршруты для обслуживания пяти клиентов, вес партии товара каждого из них колеблется в диапазоне от 0,8 до 1,45 т, а общий вес всех товаров составляет 5,9 т. В нашем распоряжении имеется семь автомобилей: пять автомобилей ??3−3302 «Газель» грузоподъемностью 1,5 т и два автомобиля ??3−53 грузоподъемностью 3 т. Стоимость аренды автомобиля ГАЗ-3302 «Газель» составляет 1 тыс. руб., а автомобиля ??3−53 — 1,5 тыс. руб. Таким образом, имеется избьток грузовых возможностей, следовательно, необходимо определить подвижной состав, использование которого минимизирует транспортные издержки, и закрепить его за клиентами.
Для решения задачи на рабочем листе Excel разработаем модель рассматриваемой задачи. Разрабатываемую модель необходимо представить в виде трех таблиц: матрицы теневых цен Сij, матрицы переменных Хij и матрицы произведения Сij*Хij. Для решения задачи необходимо связать значения таблиц формулами. Зависимости, связывающие переменные модели, представлены в таблицах 6−8.
В таблице 6 мы видим, что теневые цены рассчитываются по формуле (1), для чего в ячейку В6 занесена формула: В6=($I6/В$ 12)*В$ 5, которая затем распространяется на весь диапазон ячеек В6: Н10, содержащих теневые цены.
Фактическую загрузку подвижного состава рассчитывают по формуле (4), которая занесена в ячейке В11 в виде В11=СУММПРОИЗВ ($I6:$I10;L6:L10). Аналогично данная формула распространяется на весь диапазон ячеек В11: Н 11, содержащих значения загрузки.
В таблице 7 мы видим, что в диапазоне L6: R10 содержатся изменяемые ячейки, формулы занесенные в диапазон S6: S10, суммируют значения изменяемых ячеек по строкам, а занесенные в диапазон L11: R11 — по столбцам. Функция, занесенная в ячейки строки «Выбор», возвращает значение 1, если в ячейках строки «Сумма» находится значение, большее или равное 1, и значение 0 в противном случае.
Обязательное условие для расчетов: в таблице 7 и 8 нужно установить числовой формат ячейки без знаков после запятой (, числовые форматы — Числовой. Число десятичных знаков — 0).
Представленные в таблице 8 формулы служат для вычисления целевой функции, т. е. суммы теневых цен для обслуженных клиентов.
В диалоговое окно «Поиск решения» заносятся целевая ячейка, диапазон изменяемых ячеек и ограничения Свод параметров модели представлен в таблице 9.
В результате использования программы «Поиск решения» осуществляется оптимизация транспортного плана.
Таблица 6.
Зависимости, связывающие переменные в матрице теневых цен Сij
А. | В. | С. | D. | Е. | F. | G. | Н. | I. | ||
Клиенты. | Номер рейса. | Заказано тонн. | ||||||||
Затраты на рейс, руб. | ||||||||||
=($I6/B$ 12)*B$ 5. | =($I6/С$ 12)*С$ 5. | =($ 16/. | =($ 16. | =($ 16. | =($ 16/. | =($ 16/. | 0,8. | |||
=($I7/В$ 12)*В$ 5. | =($I7/С$ 12)*С$ 5. | =($ 17/. | =($ 17. | =($ 17. | =($ 17/. | =($ 17/. | 1,2. | |||
=($I8/В$ 12)*В$ 5. | =($I8/С$ 12)*С$ 5. | =($ 18/. | =($ 18. | =($ 18. | =($ 18/. | =($ 18/. | 1,45. | |||
=($I9/В$ 12)*В$ 5. | =($I9/С$ 12)*С$ 5. | =($ 19/. | =($ 19. | =($ 19. | =($ 19/. | =($ 19/. | 1,45. | |||
=($I10/В$ 12)*В$ 5. | =($I10/С$ 12)*С$ 5. | =($ 110/. | =($П. | =($ 11. | =($ 110/. | =($ 110/. | ||||
Загрузка ПС, тонн. | =СУММПРОИЗВ. ($I6:$I10;L6:L10). | =СУММПРОИЗВ ($ 16:$ 110:М6:М10). | =СУМ. | =СУ. | =СУ. | =СУМ. | =СУМ. | |||
Грузоподъемность. | 1,5. | 1,5. | 1,5. | 1,5. | 1,5. | |||||
Таблица 7.
Зависимости, связывающие переменные в матрице переменных Хij
К. | L. | М. | N. | O. | Р. | Q. | R. | S. | |
Клиенты. | Номер рейса. | ||||||||
Сумма. | |||||||||
=СУММ (L6:R6). | |||||||||
=СУММ (L7:R7). | |||||||||
=СУММ (L8:R8). | |||||||||
=CУMM (L9:R9). | |||||||||
=СУММ (L10:R10). | |||||||||
II. | Сумма. | =CУMM (L6:L10). | =СУММ (М6:М. | =СУМ. | =СУ. | =. | =. | =. | =CУMM (S6:S10). |
Выбор | =ECЛИ (L11>=1;1;0). | =ЕСЛИ (М11>=1. | =ЕСЛ. | =ЕС. | =Е. | =. | =. | =CУMM (L12:R12). | |
Таблица 8.
Матрица произведения Сij*Хij
U. | V. | W. | X. | Y. | Z. | AA. | AB. | AC. | |
Клиенты. | Номер рейса. | Сумма. | |||||||
=B6*L6. | =C6*M6. | =D. | =E. | =F. | =G. | =H. | =СУММ (V6:AB6). | ||
=B7*L7. | =C7*M7. | =D. | =E. | =F. | =G. | =H. | =СУММ (V7:AB7). | ||
=B8*L8. | =C8*M8. | =D. | =E. | =F. | =G. | =H. | =CУMM (V8:AB 8). | ||
=B9*L9. | =C9*M9. | =D. | =E. | =F. | =G. | =H. | =CУMM (V9:AB9). | ||
=B10*L10. | =C10*M10. | =D. | =E. | =F. | =G. | =H. | =СУMM (V10:AB10). | ||
Сумма. | =CУMM (V6:V10). | =CУMM (W6:W10). | =. | =. | =. | =. | =. | =СУMM (AC6:AC10). | |
Таблица 9.
Параметры задачи. | Ячейки. | Семантика. |
Результат. | $AC$ 11. | Цель — уменьшение общих транспортных затрат. |
Изменяемые данные. | $L$ 6:$R$ 10. | Количество транспортных средств, используемых при перевозках. |
Ограничения. | $B$ 11:$H$ 11<=$B$ 12:SH$ 12. | Фактическая загрузка подвижного состава не должна превышать его грузоподъемности. |
SLS6:SR$ 10=двоичное. | Двоичность переменных Хij, т. е. значениями переменных могут быть только 0 и 1. | |
$S$ 6:$S$ 10=1. | Ограничение гарантирует обслуживание клиента лишь одним автомобилем, т. е. заказы клиентов дробить нельзя. |
При заполнении формы Поиск решения получаем следующее:
При введении ограничения двоичности может возникнуть сложность в введении этого параметра. Поэтому необходимо задавать их следующим образом.
Параметры Поиска решения приведены на рисунке:
В результате получается следующий результат:
