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

     Запуск  и результаты работы оптимизатора.

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

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

     Отчет «Устойчивость» содержит сведения о  чувствительности решения к малым  изменениям в формуле модели или в формулах ограничений. 

     Делаем  выводы, что что Microsoft Excel – это достаточно удобная программа для работы с электронными таблицами и списками. MS Excel обладает широким сектором функциональных средств, которые облегчают работу пользователя  с различными базами данных. А с помощью оптимизатора можно находить множество значений переменных удовлетворяющих критериям оптимизации.

2. Основы  линейной оптимизации  в Microsoft Excel

 

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

Оптимальное решение, если оно существует, не может  лежать внутри области допустимых решений, а только на ее границе. Также оно может быть и не единственным.  

       Оптимизация

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

Для принятия решения задач существенными являются следующие общие элементы:

1. Множества переменных и параметров. В их число входят:

• множество разрешающих или эндогенных переменных, значения которых рассчитываются лицом, принимающим решение;

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

2. Модель – множество соотношений, связывающих все переменные и параметры.

3. Целевая функция – функция, значение которой зависит от значений эндогенных переменных. Эта функция позволяет лицу, принимающему решения оценивать варианты.
4. Численные методы – методы, с помощью которых можно систематически оценивать результаты различных решений.

Методы оптимизации  классифицируют в соответствии с  задачами оптимизации:

• Локальные методы: сходятся к какому-нибудь локальному экстремуму целевой функции. В случае унимодальной целевой функции, этот экстремум единственен, и будет глобальным максимумом/минимумом.
• Глобальные методы: имеют дело с многоэкстремальными целевыми функциями. При глобальном поиске основной задачей является выявление тенденций глобального поведения целевой функции.

Существующие  в настоящее время методы поиска можно разбить на три большие  группы:

1. детерминированные;
2. случайные (стохастические);
3. комбинированные.

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

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

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

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

3. Оптимизация управленческих и  экономических решений

 

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

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

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

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

 

На текущий  момент в фирме работает 45 человек.

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

   Решение включает в себя три этапа:

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

 
 
 

      1 этап.

1.1. Определим  возможные режимы работы и  занесем их в 1-ый столбец  таблицы №1.

 
 

    Составим  предварительный график работы.

    1.2. Обозначим   – число сотрудников, имеющих выходные в ПН т ВТ; в ВТ, СР; – СР, ЧТ; – ЧТ, ПТ; – ПТ, СБ; – СБ, ВС; – ВС, ПН.

    Согласно  условиям задачи большего всего сотрудников  должны работать в среду, значит, число  отдыхающих  в этот день должно быть меньше всего. Например, возьмем  = =5. Тогда = = = = = =7

    Занесем значения Х во второй столбец таблицы.

    1.3. Введем индексы выхода на работу  в остальные столбцы, где, если  это “1” –рабочий день, a “0” – выходной день.

    1.4. Рассчитаем число выходов на  работу каждый день. Для расчета  числа выходов в ПН нужно  перемножить и сложить значения  столбца Х столбца ПН. И так  далее.

    1.5. Рассчитываем целевую функцию  задачи – фонд зарплаты за  неделю.

    Z=B K

    Где В – зарплата сотрудника за день, а К – число выходов на работу за неделю.

    К=31+33+35+33+31+31+31=225

    Z=225 230=51750 рублей

    На  модель накладываются следующие  ограничения:

1. переменные Х не отрицательные.
2. Х – целые

 
 

3. Количество выходящих на работу по графику не может быть меньше требуемого числа сотрудников.

 
 
 

2 этап. Построение  начального плана.

2.1. В ячейках  А1:I9 разместим начальный график работы согласно таблице №1.

2.2. Расчет числа  выхода по графику:

а) в ячейке B10 вычислим общее число сотрудников фирмы необходимое для данного графика.

б) в С10 введем формулу для вычисления количества сотрудников, работающих  в понедельник.

в) копируем формулу  из ячейки C10 в ячейки D10:I10.

2.3. В строку 11 заносим требования к графику  работы согласно условиям задачи.

3 этап. Оптимизация  решения.

3.1. Запускаем режим «Поиск решения» (сервис-поиск решения)

3.2. В окно  поиска решения вводим:

- в целевую  ячейку $C$14

- установить  минимальные значения

- изменяя ячейки  В3:В9

3.3. Добавить  ограничения:

- В3:В9 >=0

- B3:B9 – целые

- C10:C10>=C11:I11 

После проделанных действий щелкаем на «Выполнить»!

Вид таблицы  и результаты: