Решение с помощью Excel

Для решения математической модели с помощью Excel необходимо на рабочем листе отразить следующую информацию:

1. Стоимость поставки электроэнергии от каждой станции в каждый город;

2. Количество электроэнергии, поставляемой от каждой станции в каждый город (план поставок);

3. Суммарное количество электроэнергии, поставленной каждой станцией;

4. Суммарное количество электроэнергии, полученной каждым городом;

5. Суммарная стоимость поставки электроэнергии.

На Рис. 12 приведен пример построения рабочего листа Excel для задачи составления оптимального плана поставки электроэнергии фирмы Powerco.

Рис. 12 Пример построения таблицы для задачи Powerco

Исходные данные. Исходные данные — параметры задачи выделены синей рамкой. В ячейках C6:F8 находятся значения стоимости поставки 1 млн. квт/ч от каждой станции в каждый город. В ячейках I13:I15 записаны значения мощности станций, а в ячейках C18:F18 — максимальные потребности городов.

План поставки (изменяемые ячейки). Ввести любые произвольные начальные значения объемов поставки электроэнергии с каждой станции в каждый город в ячейки C13:F15. Это будут изменяемые ячейки — они выделены красной рамкой.

Объемы поставок электроэнергии с каждой станции (ограничения). Для того, чтобы быть уверенным, что каждая станция не должна поставлять электроэнергии больше, чем ее мощность, необходимо подсчитать суммарное количество электроэнергии, поставленной каждой станцией. Количество электроэнергии, поставленной станцией 1, подсчитывается в ячейке G13 с помощью формулы

=СУММ(C13:F13)

Для расчета количества электроэнергии, поставленной остальными станциями эту формулу надо скопировать в ячейки G14:G15.

Количество электроэнергии, полученной каждым городом (ограничения). Для того чтобы быть уверенными, что каждый из городов будет обеспечен электроэнергией (с учетом пиковых потребностей) в интервале ячеек C16:F16 необходимо подсчитать суммарное количество электроэнергии, полученной каждым из городов. Так, количество электроэнергии, полученной городом 1, подсчитывается в ячейке C16 с помощью формулы

=СУММ(C13:C15)

Для расчета количества электроэнергии, полученной остальными городами необходимо скопировать эту формулу в ячейки D16:F16.

Суммарная стоимость поставки электроэнергии (целевая функция). Суммарная стоимость поставленной фирмой Powerco электроэнергии подсчитывается в ячейке I9 с помощь формулы

=СУММПРОИЗВ(C6:F6,C13:F15)

которая просто перемножает стоимость поставки 1 млн. квт/ч на количество поставленных млн. квт/ч.

Поиск решения/Solver

Целевая функция. Выбрать ячейку I9 (суммарная стоимость) в качестве целевой ячейки.

Изменяемые ячейки. Выбрать интервал ячеек C13:F15 в качестве изменяемых ячеек. Ограничения на эти ячейки состоят в том, что они должны быть неотрицательны. В этих ячейках содержатся количества электроэнергии, поставленной станциями различным городам.

Ограничения по возможностям поставки (мощностям). Добавить ограничения G13:G15<=I13:I15. Эти ограничения означают, что каждая станция не может поставить больше электроэнергии, чем ее мощность.

Ограничения по спросу. Добавить ограничения C16:F16>=C18:F18. Эти ограничения необходимы для того, чтобы обеспечить потребности каждого из городов в электроэнергии (с учетом пиковых потребностей).

Линейность модели и оптимизация. На вкладке «Параметры» проверить, что отмечены параметры «Линейная модель» и «Неотрицательные значения».

Сохраните найденные оптимальные значения, изобразите графически план поставок электроэнергии от каждой станции городам.

Минимальная стоимость обеспечения городов требуемой электроэнергией равна $1020, план поставок содержится в таблице.

Дата добавления: 2014-12-29