Для решения задачи откроем новую рабочую книгу Excel и выполним следующие подготовительные действия:
1. Внесем необходимые надписи в ячейки рабочего листа, как это изображено на рис. 1 и рис. 2.
Рис. 1. Исходные данные и табличная модель (начало)
Рис. 2. Исходные данные и табличная модель (продолжение)
2. В ячейки C3:H3 (см. рис. 1) и L3:Q3 (см. рис. 2) введем порядковые номера автомобилей, которые используются для обслуживания заказов.
3. В ячейки C6:H6 (см. рис. 1) и L6:Q6 (см. рис. 2) введем марку/тип подвижного состава, который используется для обслуживания заказов.
4. В ячейки C8:H8 (см. рис. 1) и L8:Q8 (см. рис. 2) введем значения затрат на аренду автомобиля, руб./день.
5. В ячейки B9:B23 (см. рис. 1) введем значение кода (идентификационного номера) клиентов из табл. 1.
6. В ячейки I9:I23 (см. рис. 1) введем значения веса заказа, кг, из табл. 1.
7. В ячейки C26:H26 (см. рис.1) введем значения грузоподъемности автомобиля, кг.
8. В ячейки L25:Q25 (см. рис. 2) введем значения ограничения на максимальное число заказов, которое может быть обслужено одним рейсом.
|
|
9. В ячейки C9 введем формулу: =$I9*L9 и распространим эту формулу на весь диапазон ячеек C9:H23 (см. рис. 1). Данная формула позволяет рассчитать фактический вес заказа, доставленного данным автомобилем.
10. В ячейку C25 введем формулу: =C26*L26 и распространим эту формулу на диапазон ячеек C25:H25 (см. рис. 1). Данная формула позволяет рассчитать провозную способность автомобиля, которая равна его грузоподъемности, если данный автомобиль используется или равна 0, если данный автомобиль не используется.
11. В ячейку C27 введем формулу: =C24/C26, которая позволяет рассчитать коэффициент использования грузоподъемности, и распространим эту формулу на диапазон ячеек C27:H27 (см. рис. 1).
12. В ячейках R9:R23 суммируются значения переменной по строкам.
13. В ячейках L24:Q24 суммируются значения переменной по столбцам.
14. В ячейку C29 введем формулу: =СУММПРОИЗВ (L8:Q8;L26:Q26), которая представляет собой целевую функцию (2) рассматриваемой задачи.
Остальные данные и вычисления, произведенные на рабочем листе, носят вспомогательный характер.
Для дальнейшего решения задачи следует вызвать мастер поиска решения, для чего необходимо выполнить операцию главного меню: Сервис→Поиск решения.
После появления диалогового окна Поиск решения следует выполнить следующие действия:
1. В поле с именем Установить целевую ячейку: ввести абсолютный адрес ячейки $C$29.
2. Для группы Равной: выбрать вариант поиска решения - минимальному значению.
3. В поле с именем Изменяя ячейки: ввести абсолютный адрес диапазона ячеек $L$9:$Q$23;$L$26:$Q$26.
4. В поле с именем Ограничения: ввести ограничения, как показано на рис. 3
|
|
Рис. 3. Окно Поиск решения с введенными ограничениями
5. В окне дополнительных параметров поиска решения выбрать отметки Линейная модель и Неотрицательные значения.
После задания ограничений и целевой функции можно приступить к поиску численного решения, для чего следует нажать кнопку Выполнить. Результат выполнения расчетов программой MS Excel представлен на рис. 4 и 5.
Рис. 4. Результат решения задачи (начало)
Рис. 5. Результат решение задачи (окончание)
Вывод. В представленном решении используются две единицы подвижного состава: ГАЗ-52 грузоподъемностью 2500 кг – 1 ед.; ГАЗ-3302 «Газель» грузоподъемностью 1500 кг – 1 ед. Автомобилем ГАЗ-52 обслуживается 12 заказов, общий вес которых составляет 2384 кг. Автомобилем ГАЗ-3302 «Газель» обслуживается 3 заказа, общий вес которых составляет 988 кг. Коэффициент использования грузоподъемности при выполнении рейса автомобилем ГАЗ-52 составляет 0,95, а при выполнении рейса автомобилем ГАЗ-3302 «Газель» – 0,66. Общие затраты на перевозку при этом минимальны и составляют 6000 руб.