Материал: 5442

51

Рисунок 33 – Постановка задачи на распределение ресурсов

Для реализации модели с помощью процедуры поиска решения в меню

Сервис выберите команду Поиск решения.

Если команда Поиск решения отсутствует в меню Сервис, выполните команду Надстройка и установите флажок Поиск решения.

Вокне диалога Поиск решения (рисунок 34) в поле Установить целевую ячейку введите ссылку на целевую ячейку (С12).

Задайте для целевой ячейки одно из следующих значений:

чтобы максимизировать значение конечной ячейки путём изменения значений влияющих ячеек, установите переключатель в положение макси-

мальному значению;

чтобы минимизировать значение конечной ячейки путём изменения значений влияющих ячеек, установите переключатель в положение мини-

мальному значению;

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

Вполе Изменяя ячейки введите имена или ссылки на изменяемые ячейки

(В10:С10).

52

Рисунок 34 – Работа с окном Поиск решения

Вполе Ограничения введите все ограничения, накладываемые на поиск решения. Для добавления ограничений щёлкните по кнопке Добавить.

Вполе Ссылка на ячейку (рисунок 35) введите адрес ячейки, на значение которой накладываются ограничения.

Выберите из раскрывающегося списка условный оператор ( <=, =, >=, цел или двоич), который должен располагаться между ссылкой и ограничением. Если выбрано цел, в поле Ограничение появится «целое». Если выбрано двоич, в поле Ограничение появится «двоичное». Условные операторы типа цел и двоич можно применять только при наложении ограничений на изменяемые ячейки.

Рисунок 35 – Работа с окном Добавление ограничения

В поле Ограничение введите число, ссылку на ячейку или её имя либо формулу.

Выполните одно из следующих действий:

чтобы принять ограничение и приступить к вводу нового, нажмите кнопку Добавить;

чтобы принять ограничение и вернуться в диалоговое окно Поиск решения, нажмите кнопку OK.

53

С помощью кнопок Изменить и Удалить можно откорректировать заданные ограничения.

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

Нажмите кнопку Выполнить и выполните одно из следующих действий:

чтобы сохранить найденное решение на листе, выберите в диалоговом окне Результаты поиска решения вариант Сохранить найденное решение;

чтобы восстановить исходные данные, выберите вариант Восстановить исходные значения.

Рисунок 36. Вид окна Результаты поиска решения

Если планируется использовать созданную модель в дальнейшем, найденное решение можно сохранить как сценарий. Для этого в диалоговом окне Ре-

зультаты поиска решения щёлкните на кнопке Сохранить сценарий.

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

отчёт Результаты содержит сведения о начальных и текущих значениях целевой ячейки и изменяемых ячеек, а также о соответствии значений заданным ограничениям;

отчёт Устойчивость отражает найденный результат, а также нижние и верхние предельные значения для изменяемых ячеек;

отчёт Пределы показывает зависимость решений от изменения формулы или ограничений.

Вид листа Excel, соответствующий оптимальному значению, показан на рисунке 37.

54

Рисунок 37 – Результаты поиска решения

Решение транспортной задачи

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

Транспортная задача состоит в таком планировании перевозок, которое обеспечивает:

удовлетворение спроса потребителей;

вывоз всей продукции;

минимизацию транспортных затрат.

При постановке транспортной задачи необходимо задать таблицу транспортных издержек для перевозки единицы груза Сij от i-го поставщика к j-му потребителю. Эта таблица имеет m строк (по числу поставщиков) и n столбцов (по числу потребителей).

Таблица перевозок Xij имеет те же размеры (m х n) и содержит переменные решения.

55

Необходимо также задать запасы поставщиков – Si, готовые к вывозу и величины заказов потребителей – Dj в виде дополнительных строки и столбца к таблице транспортных издержек.

Требования вывезти запасы каждого i-го поставщика и удовлетворить заказ каждого j-го потребителя означают соответственно, что сумма переменных решения вдоль каждой i-й строки должна быть равна запасу этого поставщика Si, а сумма переменных решения вдоль каждого j-го столбца должна быть равна заказу соответствующего потребителя Dj.

Чтобы получить целевую функцию (суммарные издержки), необходимо просуммировать произведение каждой ячейки таблицы транспортных издержек на соответствующую ячейку таблицы перевозок:

mn

ССij X ij .

i1 j 1

Для практического решения транспортной задачи с помощью Excel рассмотрим пример.

Пусть имеются 4 поставщика и 5 потребителей. Издержки перевозки единицы груза от i-го поставщика в j-пункт назначения, запасы поставщиков и заказы потребителей даны в таблице 4. Оптимизировать план перевозок.

Таблица 4 – Исходные данные

Организуем данные на рабочем листе Excel (рисунок 38).