Российская  международная академия туризма

Курсовая  работа

По  курсу: Информатика

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

 
 
 
 
 
 
 
 
 
                               Выполнила: студентка группы

                               ФИО

                               Специальность: менеджмент гостеприимства

                               Проверил: ст. преподаватель  
 
 
 
 

2009 
 

      Реферат

      Курсовая  работа выполнена на тему: «Решение задач линейной оптимизации средствами Microsoft Excel».

       Актуальность данной работы заключается в том, что Microsoft Excel - является на сегодняшний день самым популярным табличным редактором в мире. Это простое и удобное средство, позволяющее проанализировать данные и, при необходимости, проинформировать о результате заинтересованную аудиторию, используя Internet.

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

      Объект исследования: Решение задачи с помощью Microsoft Excel для производства на предприятии.

      Предмет исследования: расчет заработной платы для Коксохимического производства средствами Microsoft Excel.

      Цель данной работы заключается в исследовании средств программы  Microsoft Excel при решении линейной оптимизации с помощью Microsoft Excel.

     В соответствии с целью, были поставлены следующие задачи:

     1) изучить программу оптимизации;

     2) проанализировать основы линейной оптимизации в Excel;

     3) рассмотреть оптимизацию управленческих  и экономических решений;

     4) оценить решение уравнений и  задач оптимизации;

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

     Курсовая работа содержит 35 страниц.

     Работа  состоит из введения; основной части, которая, в свою очередь, содержит две главы: « Теоретические основы задач линейной оптимизации в Microsoft Excel», «Решение задач линейной оптимизации средствами Microsoft Excel»; заключения и списка использованной литературы.

     Теоретической основой курсовой работы являются научные  труды:

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

     Во  второй главе описывается решение задач линейной оптимизации средствами Microsoft Excel для расчета заработных плат на производстве.

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

Содержание

Введение

      Курсовая  работа выполнена на тему: «Решение задач линейной оптимизации средствами Microsoft Excel».

       Актуальность данной работы заключается в том, что Microsoft Excel - является на сегодняшний день самым популярным табличным редактором в мире. Это простое и удобное средство, позволяющее проанализировать данные и, при необходимости, проинформировать о результате заинтересованную аудиторию, используя Internet.

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

      Объект исследования: Решение задачи с помощью Microsoft Excel для производства на предприятии.

      Предмет исследования: расчет заработной платы для Коксохимического производства средствами Microsoft Excel.

      Цель данной работы заключается в исследовании средств программы  Microsoft Excel при решении линейной оптимизации с помощью Microsoft Excel.

     В соответствии с целью, были поставлены следующие задачи:

     1) изучить программу оптимизации;

     2) проанализировать основы линейной оптимизации в Excel;

     3) рассмотреть оптимизацию управленческих  и экономических решений;

     4) оценить решение уравнений и  задач оптимизации;

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

     Курсовая  работа содержит 34 страницы.

     Работа  состоит из введения; основной части, которая, в свою очередь, содержит две главы:« Теоретические основы задач линейной оптимизации в Microsoft Excel», «Решение задач линейной оптимизации средствами Microsoft Excel»; заключения и списка использованной литературы.

     Теоретической основой курсовой работы являются научные  труды:

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

     Во  второй главе описывается решение задач линейной оптимизации средствами Microsoft Excel для расчета заработных плат на производстве.

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

      1 Теоретические основы  ведения баз данных в Excel

1. Программа оптимизации в  Microsoft Excel

Microsoft Excel - является на сегодняшний день самым популярным табличным редактором в мире. Это средство для работы с электронными таблицами, первая версия данного продукта была разработана фирмой Microsoft в 1985 году.

       Microsoft Excel - это простое и удобное средство, позволяющее проанализировать данные и, при необходимости, проинформировать о результате заинтересованную аудиторию, используя Internet.

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

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

      Рабочее поле Excel поделено вертикальными и горизонтальными линиями на прямоугольные ячейки. Объединенные по вертикали ячейки составляют столбцы, а по горизонтали – строки. Для облегчения работы пользователя упрощены основные функции, создание формул, форматирование, печать и построение графиков. 

     Программа оптимизации в Excel.

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

     Программа оптимизации в Excel вызывается командой Сервис /Поиск решения. В результате выполнения команды появляется диалоговое окно «Поиск решения».

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

     В указанной целевой ячейке должна храниться формула.

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

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

     Команда «Выполнить» служит для запуска  программы оптимизации.

     Команда «Закрыть» служит для выхода из диалогового  окна без запуска оптимизатора, при  этом все введенные установки  сохраняются.

     Кнопка  «Параметры» служит для настройки алгоритма и программы оптимизации.

     Кнопка  «Восстановить» служит для очистки  полей диалогового окна, при этом принимаются параметры, принятые по умолчанию. 

     Ввод  и редактор ограничений.

     В окне «Поиск решения» щелкнуть по кнопке «Добавить», в результате появляется еще одно окно.

     В поле «Ссылка на ячейку» вводится адрес на ячейку или диапазон ячеек  на значение которых вводится ограничения.

     В поле «Ограничения» вводится адрес  ячейки или диапазон ячеек, в которых  содержится или вычисляется ограничиваемое значение.

     Кнопка  «Добавить» нужна для добавления следующего ограничения.

     При нажатии «ОК» возврат в окно «Поиск решения». 

     Настройка параметров алгоритма  и программы.

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

     В окне «Параметры поиска решения» задаются:

• В поле «Максимальное время» устанавливаются ограничения на время время решения задачи. По умолчанию время решения задачи равно 100 секунд. Максимальное время – 32767 секунд.
• В поле «Относительная погрешность» задается точность рения задачи. В поле должно содержаться число в интервале от 0 до 1. Чем меньше введенное число, тем выше точность результата. С другой стороны, чем выше точность результата, тем больше требуется времени на нахождение решения. По умолчанию относительная погрешность равна 0,000001.
• Поле «Сходимость» необходимо для решения нелинейных задач.
• Поле «Допустимое отклонение» используется для решения целочисленных задач.
• Кнопки «Оценка», «Разность», «Метод поиска» служат для выбора алгоритмов оптимизации и их параметров.

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

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

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

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

     Делаем  выводы, что что 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 

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

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