Оптимизационные задачи в электроэнергетике

 

 

Рисунок 1- Исходные данные.

 

Как видно на рисунке 1, в таблицу введены названия элементов модели (затенённые ячейки), но они являются лишь оформлением решения задачи и на результат не влияют.

В созданную форму, введите  исходные данные (Рисунок 2).

 

Рисунок 2- Вводимые данные.

Коэффициенты целевой  функции, выражающие прибыль от производства единицы продукции каждого вида (единичная прибыль), записаны в ячейки B6:E6.

Коэффициенты ресурсных  ограничений, определяющие потребность  в каждом из видов ресурсов для производства единицы продукции, размещены в ячейках  B9:E11. В ячейках Н9:Н11 записаны правые части ограничений на ресурсы, выражающие их имеющиеся в наличии объёмы.

Для независимых переменных задачи – искомых объёмов производства Прод1-Прод4 - зарезервированы ячейки В3:Е3. Решение задачи и должно определить оптимальные значения именно этих величин.

Переменные, определяющие

принимаемое решение:

 П1     П2     П3     П4

  ¯        ¯       ¯        ¯

Ячейки электронной  таблицы:

  B3      C3      D3      E3

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

Целевая функция в  виде

алгебраического уравнения:

60*П1+70*П2+120*П3+130*П4

    ¯          ¯            ¯            ¯

Целевая функция в  виде

Формулы ЭТ:

B6*B3+C6*C3+D6*D3+E6*E3

Клеточную формулу целевой  функции можно вводить в виде выражения B6*B3+C6*C3+D6*D3+E6*E3, но удобне в виде функции СУММПРОИЗВ.

Введем целевую функцию  в виде этой функции в ячейку F6:

- Установить курсор  на ячейку F6 ;

- В Строке формул нажать кнопку Изменить формулу (=);

- После появления в поле ввода формул знака равенства, в поле Функции отображается функция, которая была использована последней. Если это функция СУММПРОИЗВ, щелкните по ней мышкой, если нет, щелкните стрелку справа от поля функций для просмотра списка из десяти последних функций, если и среди них нет СУММПРОИЗВ, выберите пункт Другие функции в нижней части списка, для вывода на экран диалогового окна Мастер функций. (Появившееся на экране диалоговое окно может закрыть саму таблицу, его можно перетащить ниже таблицы, «ухватившись» мышкой за заголовок окна);

• В окне Мастер функций – шаг 1 из 2 в окне Категория щелкните Математические, а в окне Функция на СУММПРОИЗВ и ОК;

 

 

Рисунок 3 - диалоговое окно СУММПРОИЗВ.

На экране появляется диалоговое окно СУММПРОИЗВ (Рисунке 3), в поля которого Массив1, Массив2 вводятся адреса блоков ячеек, содержимое которых надо сначала попарно перемножить, а затем сложить. В поле Массив1 надо ввести адрес блока B3:E3.

Напомним, что ЭТ Excel автоматически изменяет относительные адреса ячеек (B3:E3) при копировании или переносе формул. Адрес блока независимых переменных надо будет копировать в другие ячейки без изменения, поэтому его необходимо записать в абсолютной форме - B$3:E$3 (или $B$3:$E$3).

Этот и последующие адреса в  формулах можно непосредственно  вводить с клавиатуры, но удобнее и быстрее вводить адреса методом протаскивания мыши по ячейкам, чьи адреса следует ввести. Если адрес блока введен протаскиванием, необходимо, нажав клавиши F4, преобразовать его из относительной формы в абсолютную, добавив знаки $.

В окно Массив2 введите B6:E6, после чего щелкните ОК

Формулы для наглядности  можно вводить (но не обязательно) в  режиме представления формул (включение/выключение этого режима - команда Сервис - Параметры – вкладка Вид – Формулы).

 Следующий шаг связан с построением ограничений. Для каждого ограничения, содержащегося в алгебраической модели, необходимо создать клеточную формулу и поместить её в соответствующую ячейку.

Алгебраическая формула  для ограничений:

- по первому ресурсу:

            1  *  П1 + 1  *  П2 +  1  *  П3 +  1  * П4

¯      ¯      ¯      ¯       ¯       ¯       ¯      ¯

- формула для ячейки F8:

            B9 * B3 + C9 * C3 + D9 * D3 + E9 * E3

- по второму ресурсу:

           4  *  П1 + 6  * П2 +  10 *  П3 +  13 * П4

¯      ¯      ¯      ¯       ¯      ¯       ¯      ¯

- формула для ячейки F9:

            B10*B3 + C10*C3 + D10*D3 +  E10*E3

- по третьему ресурсу:

               6 *  П1 +  5  *  П2 +  4  *  П3 +  3  * П4

    ¯      ¯       ¯      ¯        ¯       ¯      ¯      ¯

- формула для ячейки F10:

 B11*B3 + C11*C3 + D11* D3+ E11* E3

Вышеприведённые формулы  ограничений отличаются от формулы  целевой функции только одним сомножителем, поэтому формулу из ячейки F6 можно скопировать в буфер (ячейка после этого будет отмечена «бегущей» рамкой), а из буфера вставить в ячейку F9. При копировании она преобразуется в формулу для ограничения по первому ресурсу:

СУММПРОИЗВ(B$3:E$3;B9:E9)

Далее формулу из ячейки F9 необходимо скопировать в ячейки F10 и F11 (либо также через буфер, либо, что быстрее,  за маркер ячейки с нажатой левой клавишей мыши). В этих ячейках формулы примут вид - СУММПРОИЗВ (B$3:E$3;B10:E10) и СУММПРОИЗВ (B$3:E$3;B11:E11).

 

 

Рисунок. 4 - отображения формул ЭТ

 

После выполнения этих действий в режиме отображения формул ЭТ примет вид, показанный на рисунке. 4.

Введя исходные данные и  зависимости для целевой функции  можно приступить к поиску решения. Для решения оптимизационных  задач в ЭТ Excel используется команда ПОИСК РЕШЕНИЙ меню СЕРВИС.

Эта команда оперирует  с тремя основными компонентами построенной в ЭТ оптимизируемой модели:

• ячейкой, содержащей целевую функцию задачи;
• изменяемыми ячейками, содержащими независимые переменные;
• ячейками, содержащими левые части ограничений на имеющиеся ресурсы, а также простые ограничения на независимые переменные.

Рассмотрим последовательность ввода этих компонентов.

• Курсор в ячейку F6 и команда Сервис – Поиск решения. На экране диалоговое окно Поиск решения (рисунок 5).

 

 

Рисунок 5 - Поиск решения.

 

В окне Поиск решений  пока заполнено только поле Установить целевую ячейку, в котором должен стоять адрес $F$6;

Установить флажок на поиск максимального значения, если он не установлен (если бы решение должно было быть минимальным или равным заданной величине, устанавливаются соответствующие флажки и вводится значение заданной величины).

В поле Изменяя ячейки ввести адреса искомых переменных –$B3:$E3, набором на клавиатуре или протаскиванием мыши.

Затем следует ввести ограничения - щелкнуть по кнопке Добавить – на экране появляется диалоговое окно Добавление ограничения (рисунок 6).

 

 

Рисунок 6 - Добавление ограничения.

 

Первоначально вводятся граничные условия на значения переменных П1 - П4. При этом в левое поле вводится адрес ячейки (указанием мышью), содержимое которой должно удовлетворять заданному  ограничению. В следующее поле вводится знак отношения для сравнения с ограничением – больше (>=), равно (=) или меньше (<=).Правое поле служит для задания значения ограничения в виде числовой константы или указания адреса ячейки, где такое значение содержится. Если ограничения по видам ресурсов имеют разные знаки отношений, то их придется вводить поочередно, например – В3 >= В4; B3 <= B5 и т.д. В нашем примере объёмы производства П1 – П4 - числа положительные, т.е. все больше или равны 0, поэтому нижние границы, т.е. ячейки В4:Е4 либо заполняются нулями, либо остаются пустыми, что равнозначно нулям. В этом случае ограничения можно ввести блоками В3:Е3>=В4:Е4. В завершение ввода ограничения – щелчок по кнопке Добавить (Рисунок 7).

Рисунок 7 - Добавление ограничения

 

Всего в нашем примере необходимо ввести 7 ограничений двумя блоками – четыре на объёмы производства по видам

продукции (уже введены), и три на ресурсы, которые и  необходимо добавить. Как уже говорилось, величина потребного ресурса должна быть меньше или равна имеющемуся ресурсу, т.е. можно записать, что блок F9:F11 <= Н9:Н11.

После ввода последнего ограничения и щелчка по кнопке Добавить, появляется очередное окно Добавление ограничения, которое нужно отменить, поскольку все ограничения уже введены. После закрытия окна Добавление ограничения в поле Ограничения окна Поиск решения появятся все введенные ограничения (Рисунок 8).

 

Рисунок 8 - Поиск решения

Теперь, когда все ограничения  для поиска оптимального решения  заданы, воспользовавшись кнопками Изменить и  Удалить  можно ввести  изменения  либо  удалить ряд ограничений из их списка. Для того, чтобы пользователь мог, изменяя параметры, несколько раз последовательно повторить поиск оптимального решения, записи в диалоговом окне Поиск решения сохраняются на протяжении всего сеанса работы с текущей рабочей книгой. Если необходимо сохранить эти параметры до следующего сеанса работы, следует сохранить рабочую книгу.

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

Параметры в диалоговом окне Поиск решения (Рисунок 8). В открывшемся диалоговом окне Параметры поиска решения (Рисунок 9), необходимо установить флажки соответствующие линейным моделям.

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

 

 

Рисунок 9 - Параметры поиска решения.

 

Нажав кнопку ОК в окне Параметры поиска решения, на экран  вновь вызывается окно Поиск решения (Рисунок 8), на котором необходимо, нажав кнопку Выполнить, запустить процесс вычислений.

 

Рисунок 10 - Результаты поиска решения.

 

Если вычисления оказались  успешными, после завершения поиска решения новые значения будут вставлены в таблицу, и на экране появится диалоговое окно Результаты поиска решения, содержащее информацию о завершении процесса поиска решения (Рисунок 10). На рисунке видно, что в оптимальном решении :

- объём выпуска продукции  П1 составляет 10 единицы (В3=10);

- объём выпуска продукции  П3 составляет 6 единицы (D3=6);

- объёмы выпуска продукции П2 и П4 составляет 0 единиц.

При этом максимальная прибыль  будет составлять 1320 единиц (F6), а количество использованных ресурсов равно:

- трудовых – 16 единиц, т.е. использован весь ресурс без остатка;

- сырьё – 84 единицы, т.е. 26 единиц остатка;

- финансы – 100 единиц, т.е. использован весь ресурс.

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

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

 

4.Анализ полученного решения

 Последовательность действий при отрицательных результатах поиска решения.

 На практике при решении задач линейного программирования  часто не удается получить оптимального решения. Это может по двум следующим причинам:

- ограничения несовместны, либо из-за неправильной математической модели, либо из-за неправильных исходных данных. В таких случаях Excel будет выдавать сообщение «Поиск не может найти подходящего решения»;

- целевая функция не ограничена сверху (при максимизации ЦФ) или снизу (при минимизации ЦФ). Неограниченность ЦФ – следствие ошибки в математической модели. В этом случае ЭТ Excel будет выдавать сообщение «Значения целевой ячейки не сходятся».

Рассмотрим последовательность действий пользователя в случаях не нахождения программой подходящего решения.

Если условия несовместны, на экране появляется диалоговое окно с сообщением, что поиск не может найти подходящего решения (Рисунок 11).

Как следует поступать, если условия задачи оказались несовместными? Для получения несовместности в учебных целях изменим условия задачи. В полученном оптимальном решении было определено, что для выпуска 10 единиц Прод1 и 6 единиц Прод3 трудовые ресурсы и финансы будут использованы полностью. Дополнительно назначим необходимость выпуска ещё 5 единиц Прод2. Очевидно, что для выпуска такого количества продукции ресурсов будет недостаточно. Внесем соответствующие изменения в условия задачи и рассмотрим последовательность действий по преодолению несовместности.

Вызовите исходную таблицу (рис. 1.4.2).

Вызовите диалоговое окно Поиск решения (п.4.1.11 и далее).