В этом параграфе мы выполнили:

1. Построение математической модели.
2. Построение начального плана.
3. Оптимизация решения.

Делаем выводы, что с помощью команды «Поиск решений» можем выполнить ряд  непростых задач с минимальными затратами времени и сил. Это  устраняет ошибки и неточности в собственных выполнениях, делая работу оптимизированной.

1.4 Решение уравнений и задач оптимизации

Для решения  задач оптимизации широкое применение находят различные средства Excel.  
В этом разделе рассмотрим команды:

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

Подбор  параметров

Основной командой для решения оптимизационных  задач в Excel является команда Сервис/Подбор параметра. Эта команда определяет неизвестную величину, приводящую к требуемому результату. 
Если команда Подбор параметра отсутствует в меню Сервис, выполните команду Сервис/Надстройка и установите флажок Пакет анализа в окне диалога Надстройка 
Для работы с командой Подбор параметра необходимо подготовить лист, чтобы в листе находились:

• формула для расчета;
• пустая ячейка для искомого значения;

• другие величины, которые используются в формуле.  
  Ссылка на пустую ячейку должна обязательно присутствовать в формуле, так как именно она является переменной, значение которой ищет Excel. Во время подбора параметра в переменную ячейку непрерывно заносятся новые значения, пока не будет найдено решение поставленной задачи. 
  Такой процесс называется итерацией, и продолжается он до тех пор, пока редактор не выполнит 100 попыток или не найдет решения, лежащее в пределах точности 0,001 от точного значения (настройка этих параметров осуществляется с помощью  команды Сервис/Параметры, вкладка Вычисления) 
   
  Оптимизация с помощью команды Подбор параметров выполняется так:

1. Создайте лист, например, с формулой =B1*B2 в ячейке B3, пустой (переменной) ячейкой (B2) и  другими данными (B1), которые могут  понадобиться при вычислениях.  Например, необходимо определить  количество книг по цене 23,75 грн., которые необходимо продать, чтобы объем продаж составил 10000,00 грн. 

 

2. Выделите ячейку  листа (B3), в которой содержится  формула (эта ячейка появится  в поле "Установить в ячейке" в окне диалога Подбор параметра). Выполните команду Сервис/Подбор параметра. Открывается окно диалога Подбор параметра.. 

 

3. Введите в  текстовое поле Значение число,  соответствующее объему продаж - 10000. Переместите курсор в текстовом  поле Изменяя значения ячейки. Выделите ту ячейку, в которой должен содержаться ответ (переменная ячейка). Ее содержимое будет подобрано и подставлено в формулу командой Подбор параметра. Выделенная ячейка (B2) выделяется на листе рамкой. Нажмите кнопку ОК, чтобы найти решение. 

 

После завершения итерационного цикла в окне диалога Результат подбора параметра появляется сообщение, а результат заносится в ячейку листа. Решение показывает, что для достижения объема продаж 10000 грн. необходимо продать 421 книгу  по цене 23,75 грн. Для закрытия окна диалога Результат подбора параметра щелкните на кнопке ОК.

Команда Поиск решения

Для решения  сложных задач, требующих применения линейного и нелинейного программирования, а также методов исследования операций применяется надстройка - Поиск решения. Чтобы использовать надстройку Поиск решения не обязательно знать методы программирования и исследования операций, но необходимо определять, какие задачи можно решать этими методами. 
Пользователь должен уметь с помощью диалоговых окон надстройки Поиск решения правильно сформулировать условия задачи, и если решение существует, то “Поиск решения” отыщет его. В основе надстройки  лежат итерационные методы. 
В том случае, когда оптимизационная задача содержит несколько переменных величин, для анализа сценария необходимо воспользоваться надстройкой Поиск решения. “Поиск решения” позволяет использовать одновременно большое количество  изменяемых ячеек (до 200) и  задавать ограничения для изменяемых ячеек.

Общие свойства, которые характерны для задач, решаемых с помощью надстройки Поиск решения:

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

Постановка задачи

Первым шагом  при работе с командой Поиск решения  является создание специализированного  листа. Для этого необходимо создать  целевую ячейку, в которую вводится основная формула. 
Кроме того, лист может включать другие значения и формулы, использующие значения целевой и переменных ячеек. Формула в целевой ячейке должна опираться в вычислениях на значения переменных ячеек. 
После того, как задача оптимизации будет подготовлена на листе, можно приступать к работе. 
1. Выделите на листе целевую ячейку, в которую введена формула. 
2. Выполните команду Сервис/Поиск решения. Открывается окно диалога Поиск решения. Поскольку была выделена ячейка, в текстовом поле «Установить целевую ячейку» появится правильная ссылка на ячейку. В группе «Равной» переключатель по умолчанию устанавливается в положение «Максимальному значению».

 
3. Перейдите к полю "Изменяя  ячейки" и введите переменные  ячейки листа 
4. Добавьте ограничения на переменные в изменяемых ячейках. Для ввода ограничений нажмите кнопку Добавить, чтобы задать первое ограничение в окне диалога, затем можно ввести второе, третье и т.д.  
5. Когда оптимизационная задача будет готова к выполнению, можно нажать кнопку Выполнить для получения ответа. Появится окно диалога с описанием результатов процесса оптимизации. 
6. Чтобы отобразить найденное решение в ячейках листа, установите переключатель "Сохранить найденное решение" и нажмите кнопку ОК. Найденная максимальная величина помещается в целевую ячейку, а переменные ячейки заполняются оптимальными значениями переменных, которые удовлетворяют установленным ограничениям.

В этом разделе мы рассмотрели команды:

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

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

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

      2 Решение задач линейной оптимизации средствами Microsoft Excel

      2.1 Создание  базы данных для коксохимического производства

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

     - сумма начисленной заработной  платы по участку;

     - сумма заработной платы по  цеху.

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

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

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

     В результате следует получить ведомость  со следующими реквизитами: код цеха, код участка, сумма заработной платы.

     Организационно-экономическая  сущность задачи.

     1. Наименование задачи: расчет начисления  заработной платы по цехам  и участкам.

     2. Место решения задачи: табельная  ОАО «НЛМК», коксохимическое производство.

     3. Цель решения задачи: определение  суммы заработной платы по  цехам и участкам.

     4. Периодичность решения задачи: ежемесячно  до 4 числа каждого месяца.

     5.Для  кого предназначено решение задачи: для руководства комбината.

     6. Источники получения исходных документов: мастера цехов.

     7. Информационная модель задачи. 

     

 

     8. Экономическая сущность задачи: ведомость по начисленной заработной  плате необходима для расчетного  отдела, который на ее основании  делает выплаты ежемесячной заработной платы работникам в полном объеме, в ведомости отражается сумма заработной платы, подлежащая выплате работникам.

      2.2 Анализ данных  при решении задач используемых для расчета заработной платы на производстве.

     Описание  входной информации.

     В данном разделе приводится перечень всех первичных документов, используемых для решения задачи.

     В качестве входной информации используется документ «Табель». На основании этого  документа создается следующий  машинный документ. 

     Табель