Использование Microsoft Excel для решения задач линейного программирования .

В Excel 2007 для включения пакета анализа надо нажать перейти в блок Параметры Excel , нажав кнопку в левом верхнем углу, а затем кнопку «Параметры Excel » внизу окна:


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

Для того чтобы решить задачу ЛП в табличном процессоре Microsoft Excel , необходимо выполнить следующие действия:

1. Ввести условие задачи:

a) создать экранную форму для ввода условия задачи :

· переменных,

· целевой функции (ЦФ),

· ограничений,

· граничных условий;

b) ввести исходные данные в экранную форму :

· коэффициенты ЦФ,

· коэффициенты при переменных в ограничениях,

· правые части ограничений;

c) ввести зависимости из математической модели в экранную форму :

· формулу для расчета ЦФ,

· формулы для расчета значений левых частей ограничений;

d) задать ЦФ (в окне "Поиск решения" ):

· целевую ячейку,

· направление оптимизации ЦФ;

e) ввести ограничения и граничные условия (в окне "Поиск решения" ):

· ячейки со значениями переменных,

· граничные условия для допустимых значений переменных,

· соотношения между правыми и левыми частями ограничений.

2. Решить задачу:

a) установить параметры решения задачи (в окне "Поиск решения" );

b) запустить задачу на решение (в окне "Поиск решения" ) ;

c) выбрать формат вывода решения (в окне "Результаты поиска решения" ).

Рассмотрим подробно использование MS Excel на примере решения следующей задачи.

Задача.

Фабрика "GRM pic" выпускает два вида каш для завтрака - "Crunchy" и "Chewy". Используемые для производства обоих продуктов ингредиенты в основ-ном одинаковы и, как правило, не являются дефицитными. Основным ограничением, накладываемым на объем выпуска, является наличие фонда рабочего времени в каждом из трех цехов фабрики.

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


Цех
Необходимый фонд рабочего времени
чел.-ч/т
Общий фонд рабочего времени
чел.-ч. в месяц

"Crunchy"
"Chewy"

А. Производство


10
4
1000

В. Добавка приправ


3
2
360

С. Упаковка


2
5
600

Доход от производства 1 т "Crunchy" составляет 150 ф. ст., а от производства "Chewy" - 75 ф, ст. На настоящий момент нет никаких ограничений на возможные объемы продаж. Имеется возможность продать всю произведенную продукцию.

Требуется:

а) Сформулировать модель линейного программирования, максимизи-рующую общий доход фабрики за месяц.

б) Решить ее c помощью MS Excel.

Формальная постановка данной задачи имеет вид:

(1)
Ввод исходных данных
Создание экранной формы и ввод исходных данных

Экранная форма для решения в MS Excel представлена на рисунке 1.


Рисунок 1.

В экранной форме на рисунке 1 каждой переменной и каждому коэффициенту задачи поставлена в соответствие конкретная ячейка на листе Excel. Имя ячейки состоит из буквы, обозначающей столбец, и цифры, обозначающей строку, на пересечении которых находится объект задачи ЛП. Так, например, переменным задачи 1 соответствуют ячейки B4 (), C4 (), коэффициентам ЦФ соответствуют ячейки B6 (150), C6 (75), правым частям ограничений соответствуют ячейки D 18 (1000), D 19 (360), D 20 (600) и т.д.
Ввод зависимостей из формальной постановки задачи в экранную форму

Для ввода зависимостей определяющих выражение для целевой функции и ограничений используется функция MS Excel СУММПРОИЗВ , которая вычисляет сумму попарных произведений двух или более массивов.

Одним из самых простых способов определения функций в MS Excel является использование режима "Вставка функций", который можно вызвать из меню "Вставка" или при нажатии кнопки "

Рисунок 2

Так, например, выражение для целевой функции из задачи 1 определяется следующим образом:

· курсор в поле D 6;

· нажав кнопку "

· в окне "Функция" выберитефункцию СУММПРОИЗВ (рис. 3);


Рисунок 3

· в появившемся окне "СУММПРОИЗВ" в строку "Массив 1" введите выражение B $4: C $4 , а в строку "Массив 2" - выражение B 6: C 6 (рис. 4);

Рисунок 4

Левые части ограничений задачи (1) представляют собой сумму произведений каждой из ячеек, отведенных для значений переменных задачи (B 3, C 3 ), на соответствующую ячейку, отведенную для коэффициентов конкретного ограничения (B 13, C 13 - 1-е ограничение; B 14, С14 - 2-е ограничение и B 15, С15 - 3-е ограничение). Формулы, соответствующие левым частям ограничений, представлены в табл.1.

Таблица 1.
Формулы, описывающие ограничения модели (1)


Левая часть ограничения
Формула Excel


=СУММПРОИЗВ(B 4: C 4; B 13: C 13))


=СУММПРОИЗВ(B 4: C 4; B 14: C 14))


=СУММПРОИЗВ(B 4: C 4; B 15: C 15)

Задание ЦФ

Дальнейшие действия производятся в окне "Поиск решения" , которое вызывается из меню "Сервис" (рис.5):

· поставьте курсор в поле "Установить целевую ячейку" ;

· введите адрес целевой ячейки $ D $6 или сделайте одно нажатие левой клавиши мыши на целевую ячейку в экранной форме ¾ это будет равносильно вводу адреса с клавиатуры;

· введите направление оптимизации ЦФ, щелкнув один раз левой клавишей мыши по селекторной кнопке "максимальному значению".


Рисунок 5
Ввод ограничений и граничных условий
Задание ячеек переменных

В окно "Поиск решения" в поле "Изменяя ячейки" впишите адреса $ B $4:$С$4 . Необходимые адреса можно вносить в поле "Изменяя ячейки" и автоматически путем выделения мышью соответствующих ячеек переменных непосредственно в экранной форме.
Задание граничных условий для допустимых значений переменных

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

· Нажмите кнопку "Добавить" , после чего появится окно "Добавление ограничения" (рис.6).

· В поле "Ссылка на ячейку" введите адреса ячеек переменных $ B $4:$С$4 . Это можно сделать как с клавиатуры, так и путем выделения мышью всех ячеек переменных непосредственно в экранной форме.

· В поле знака откройте список предлагаемых знаков и выберите .

· В поле "Ограничение" введите 0.

Рис.6 - Добавление условия неотрицательности переменных задачи (1)
Задание знаков ограничений , , =

· Нажмите кнопку "Добавить" в окне "Добавление ограничения" .

· В поле "Ссылка на ячейку" введите адрес ячейки левой части конкретного ограничения, например $ B $18 . Это можно сделать как с клавиатуры, так и путем выделения мышью нужной ячейки непосредственно в экранной форме.

· В соответствии с условием задачи (1) выбрать в поле знака необходимый знак, например, .

· В поле "Ограничение" введите адрес ячейки правой части рассматриваемого ограничения, например $ D $18 .

· Аналогично введите ограничения: $ B $19<=$ D $19 , $ B $20<=$ D $20 .

· Подтвердите ввод всех перечисленных выше условий нажатием кнопки OK .

Окно "Поиск решения" после ввода всех необходимых данных задачи (1) представлено на рис. 5.

Если при вводе условия задачи возникает необходимость в изменении или удалении внесенных ограничений или граничных условий, то это делают, нажав кнопки "Изменить" или "Удалить" (см. рис. 5).
Решение задачи
Установка параметров решения задачи

Задача запускается на решение в окне "Поиск решения". Но предварительно для установления конкретных параметров решения задач оптимизации определенного класса необходимо нажать кнопку "Параметры" и заполнить некоторые поля окна "Параметры поиска решения" (рис. 7).

Рис. 7 - Параметры поиска решения, подходящие для большинства задач ЛП

Параметр "Максимальное время" служит для назначения времени (в секундах), выделяемого на решение задачи. В поле можно ввести время, не превышающее 32 767 секунд (более 9 часов).

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

Параметр "Относительная погрешность" служит для задания точности, с которой определяется соответствие ячейки целевому значению или приближение к указанным границам. Поле должно содержать число из интервала от 0 до 1. Чем меньше количество десятичных знаков во введенном числе, тем ниже точность. Высокая точность увеличит время, которое требуется для того, чтобы сошелся процесс оптимизации.

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

Параметр "Сходимость" применяется только при решении нелинейных задач.Установка флажка "Линейная модель" обеспечивает ускорение поиска решения линейной задачи за счет применение симплекс-метода.

Подтвердите установленные параметры нажатием кнопки " OK " .
Запуск задачи на решение

Запуск задачи на решение производится из окна "Поиск решения" путем нажатия кнопки "Выполнить".

После запуска на решение задачи ЛП на экране появляется окно "Результаты поиска решения" с сообщением об успешном решении задачи, представленном на рис. 8.


Рис. 8 -. Сообщение об успешном решении задачи

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

Если при заполнении полей окна "Поиск решения" были допущены ошибки, не позволяющие Excel применить симплекс-метод для решения задачи или довести ее решение до конца, то после запуска задачи на решение на экран будет выдано соответствующее сообщение с указанием причины, по которой решение не найдено. Иногда слишком малое значение параметра "Относительная погрешность" не позволяет найти оптимальное решение. Для исправления этой ситуации увеличивайте погрешность поразрядно, например от 0,000001 до 0,00001 и т.д.

В окне "Результаты поиска решения" представлены названия трех типов отчетов: "Результаты", "Устойчивость", "Пределы" . Они необходимы при анализе полученного решения на чувствительность. Для получения же ответа (значений переменных, ЦФ и левых частей ограничений) прямо в экранной форме просто нажмите кнопку " OK ". После этого в экранной форме появляется оптимальное решение задачи (рис. 9).


Рис.9 - Экранная форма задачи (1) после получения решения

Решим данную задачу графическим методом в табличном редакторе Microsoft Excel (рис. 1). Для построения ОДР, и линий уровня воспользуемся Мастером диаграмм . ОДР представляет собой многоугольник с вершинами в точках: (0;0), (0;6), (2;5), (4;3), (5;0).

При перемещении линии уровня в направлении вектора получаем оптимальное решение в точке с координатами (2;5).

Аналогичным образом можно решить данную задачу графическим методом в табличном редакторе OpenOffice.org Calc воспользовавшись пунктом меню Диаграмма .



Решение ЗЛП в Microsoft Excel и OpenOffice.org Calc с помощью встроенной функции Поиск решения

В табличном процессоре Microsoft Excel существует встроенная функция Поиск решения , с помощью которой можно решить задачу линейного программирования. Если данный модуль установлен, его можно запустить выбрав команду Сервис/Поиск решения (рис. 2). На экране появится диалоговое окно Поиск решения (рис. 3).

Р и с. 2. Р и с. 3.

Если такого пункта в меню Сервис не оказалось, следует загрузить соответствующую программу-надстройку. Для этого выберите команду Сервис/Надстройки (рис. 4) и в диалоговом окне Надстройки установите флажок в строке Поиск решения (рис. 5).

Разберем решение ЗЛП с помощью функции Поиск решения на примере задачи 1.

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

2. Введем начальные нулевые значения для и .

3. Зададим целевую функцию в ячейке D41 и ограничения в ячейках Е39, Е40 и E41 (рис. 6).

Р и с. 4. Р и с. 5.

4. Выберем команду Сервис/Поиск решения , в открывшемся окне Поиск решения установим целевую ячейку D41, зададим условие отыскания максимального значения (рис. 7).

5. В поле Изменяя ячейки установим ссылку на ячейки С40 и С41, которые будут изменены (можно ввести адреса или имена ячеек с клавиатуры или указать диапазон ячеек на рабочем листе с помощью мыши). При щелчке на кнопке Предположить автоматически выделяются ячейки, на которые есть прямая или косвенная ссылка в формуле целевой ячейки (рис. 7).


6. Определим ограничения, для этого щелчком по кнопке Добавить откроем диалоговое окно Добавление ограничения . Введем ограничения для ячеек E39, E40, E41. Ограничения можно задать как для изменяемых ячеек, так и для целевой ячейки, а также для других ячеек, прямо или косвенно присутствующих в модели (рис. 8, 9).

Р и с. 8. Р и с. 9.

7. Щелчком на кнопке Параметры откроем диалоговое окно Параметры поиска решения . В данном окне выберем линейную модель и неотрицательные значения (неотрицательные значения для ячеек С40 и С41 можно было также установить при определении ограничений). Подробнее узнать о задаваемых параметрах можно щелкнув на кнопке Справка (рис. 10).

8. После того как все параметры и ограничения заданы, запускаем поиск решения, щелкнув на кнопке Выполнить (рис. 9). По мере того как идет поиск, отдельные его шаги отражаются в строке состояния. Когда поиск будет закончен, в таблицу будут внесены новые значения и на экране появится диалоговое окно Результаты поиска решения , сообщающие о завершении операции (рис. 11).

Решение найдено. Все ограничения и условия оптимальности выполнены. Сохраним найденное решение. В этом случае таблица будет обновлена. В случае необходимости всегда можно будет восстановить исходные данные с помощью отчета. Для выбора типа отчета достаточно выделить название нужного отчета в списке Тип отчета (или несколько названий, удерживая нажатой клавишу Сtrl ). Они будут вставлены на отдельных листах в рабочую книгу перед листом с исходными данными.

Предлагаемые отчеты содержат следующую информацию:

отчет Результаты содержит сведения о начальных и текущих значениях целевой ячейки и изменяемых ячеек, а также о соответствии значений заданным ограничениям;

отчет Устойчивость отражает найденный результат, а также нижние и верхние предельные значения для изменяемых ячеек;

отчет Пределы показывает зависимость решений от изменения формулы или ограничений.

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

Аналогично Поиск решения осуществляется в OpenOffice.org Calc.

Задание

1. Решить задачи 2 и 3 графическим методом.

2. Решить задачи 2 и 3 в редакторе Microsoft Excel или OpenOffice.org Calc используя встроенную функцию Поиск решения .

3. Сравнить и проанализировать полученные результаты.

4. Ответить на контрольные вопросы.

5. Оформить отчет.

Задача 2. Фармацевтическая фирма Ozark ежедневно производит не менее 800 фунтов некой пищевой добавки – смеси кукурузной и соевой муки, состав которой представлен в таблице 2.

Таблица 2

Диетологи требуют, чтобы в пищевой добавке было не менее 30% белка и не более 5% клетчатки. Фирма Ozark хочет определить рецептуру смеси минимальной стоимости с учетом требований диетологов.

Задача 3. Предприятие, специализирующееся на производстве трикотажного полотна двух видов, использует для своего производства четыре вида сырья (шерстяную, хлопковую, вискозную, и акриловую нити), запасы которого на планируемый период составляют соответственно 80, 80, 260 и 410 бобин. В приведенной ниже таблице даны технологические коэффициенты, т.е. расход каждого вида сырья на производство одного метра каждого вида трикотажа.

Таблица 3

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

Контрольные вопросы

1. Что означает составить математическую модель ЗЛП?

2. Из каких этапов состоит графический метод решения ЗЛП?

3. Какова геометрическая интерпретация решения системы линейных неравенств с двумя переменными?

4. Как определяется направление наискорейшего возрастания целевой функции?

5. Какое решение называется оптимальным решением ЗЛП?

6. В каком случае ЗЛП имеет множество решений?

7. При каких условиях ЗЛП может быть неразрешима?

8. Как установить модуль Поиск решения ?

9. Для чего предназначена кнопка Предположить в окне Поиск решения ?

10. Какие типы отчетов можно получить при решении ЗЛП с помощью встроенной функции Поиск решения ?

Лабораторная работа №2

Симплексный метод. Задача определения оптимального плана выпуска продукции. Использование встроенных функций редакторов Microsoft Excel и OpenOffice.org Calc для построения математической модели и решения ЗЛП.

Цель лабораторного занятия:

Приобретение навыков решения ЗЛП симплекс-методом. Освоение приемов записи математической модели ЗЛП с большим количеством неизвестных в табличных редакторах Microsoft Excel и OpenOffice.org Calc с помощью встроенной функций СУММПРОИЗВ. Приобретение навыков решения ЗЛП с большим количеством неизвестных с помощью функции Поиск решения .

Задачи лабораторного занятия:

1. Освоение симплекс-метода решения ЗЛП.

2. Построение математической модели задачи в табличных редакторах Microsoft Excel и OpenOffice.org Calc с помощью встроенной функций СУММПРОИЗВ.

3. Нахождение максимума (минимума) целевой функции с помощью команды Поиск решения .

4. Анализ полученных результатов.

5. Оформление отчета.

1. Краткие теоретические сведения.

2. Решение ЗЛП симплекс методом без использования табличных редакторов.

3. Решение ЗЛП на определение оптимального плана выпуска продукции в Microsoft Excel и OpenOffice.org Calc с помощью встроенной функции Поиск решения .

4. Задание.

5. Контрольные вопросы.

Краткие теоретические сведения

В основу симплекс-метода (симплексного метода) легла идея последовательного улучшения решения.

Геометрический смысл симплексного метода состоит в последовательном переходе от одной вершины многогранника ограничений (называемой первоначальной) к соседней, в которой линейная целевая функция принимает лучшее или, по крайней мере, не худшее значение. Этот процесс осуществляется до тех пор, пока не будет найдено оптимальное решение – вершина, где достигается оптимальное значение целевой функции (если задача имеет конечный оптимум).

Реализация симплекс-метода предусматривает содержание трех основных элементов:

1. Определение какого-либо первоначального допустимого базисного решения задачи (базисное решение называется допустимым, если значения, входящих в него переменных неотрицательны);

2. Правила перехода к лучшему (точнее, не худшему) решению;

3. Критерий проверки оптимальности найденного решения.

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

Практические расчеты при решении прикладных задач симплексным методом выполняются в настоящее время с помощью компьютерных программ, таких как табличный процессор Microsoft Excel, пакеты прикладных программ MathCAD, Math Lab и др. Однако, если расчеты осуществляются вручную, удобно использовать так называемые симплексные таблицы.

Нижегородский Государственный Технический Университет

Павловский филиал

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

по информатике на тему:

“Технология решения задач линейного программирования с помощью Поиска решений приложения Excel” .

Выполнила : Бородулина Д.А.

Группа 05-АМ.

Проверила : Ловыгина М.Б.

Павлово 2006 г.

Введение……………………………………………………………………………стр. 3

Решение задач с помощью надстройки Поиск решения

  1. Установка программы Поиск решения…………………………………………..…стр.4
  2. Диалоговое окно Поиск решения…………………………………………………..…стр.4
  3. Ввод и редактирование ограничений………………………………………………..стр.5
  4. Настройка параметров алгоритма и программы……………………………….стр.6
  1. Сохранение модели оптимизации…………………………………………………....стр.9
  2. Загрузка модели оптимизации……………………………………………………….стр.9

Вычисления и результаты решения задачи………………………………..стр. 10

Просмотр промежуточных результатов поиска решения…………...стр.11

Возникающие проблемы и сообщения процедуры поиска решения…...стр.12

Итоговые сообщения процедуры поиска решения……………………....стр.13

Примеры выполнения задач

  1. Пример № 1………………………………………………………………………………стр.15
  2. Пример № 2 (графическим способом)……………………………………………...стр..20

Вывод……………………………………………………………………………....стр.24

Список литературы…………………………………………………………....стр.25

Введение

Линейная оптимизация – это раздел математического программирования, посвящённый нахождению экстремума линейных функций нескольких переменных при дополнительных линейных ограничениях, которые налагаются на переменные. Методы, с помощью которых решаются задачи, подразделяются на универсальные (например, симплексный метод) и специальные. С помощью универсальных методов решаются любые задачи линейного программирования. Особенностью задач линейного программирования является то, что экстремум целевой функции достигается на границе области допустимых решений.

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

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

Такие задачи в Excel решают с помощью Поиска решения .

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

Решение задач с помощью надстройки Поиск решения

1. Установка программы Поиск решения

В меню Сервис выберите команду Надстройки.

В диалоговом окне Надстройки установите флажок Поиск решения. Если диалоговое окно Надстройки не содержит команды Поиск решения , нажмите кнопку Обзор и укажите диск и папку, в которой содержится файл надстройки Solver. xla (как правило, это папка Library\ Solver folder) или запустите программу Setup , если найти файл не удаётся.

Надстройка, указанная в диалоговом окне Надстройки , остаётся активной до тех пор, пока она не будет удалена.

2. Диалоговое окно Поиск решения

Окно Поиск решения (рис. 1) вызывается командой меню Сервис>Поиск решения.

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

Рис.1.Диалоговое окно Поиск решения.

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

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

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

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

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

Команда Изменить Изменение ограничения.

Команда Удалить служит для снятия указанного курсором ограничения.

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

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

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

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

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

Диалоговые окна изменения и добавления ограничений одинаковы, рис.2.

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

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

Чтобы приступить к набору нового условия, нажмите кнопку Добавить.

Чтобы вернуться в диалоговое окно Поиск решения, нажмите кнопку ОК.

Условные операторы целого и двоичного типа можно применять только при наложении ограничений на изменяемые ячейки.

Рис.2.Диалоговое окно Изменение ограничения.

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

Настройка параметров алгоритма и программы производится в диалоговом окне Параметры поиска решения , рис. 3.

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

Рис. 3. Диалоговое окно Параметры поиска решения.

Поле Максимальное время служит для ограничения времени, отпускаемого на поиск решения задачи. В поле можно ввести время (в секундах) не превышающее 32767; значение 100, используемое по умолчанию, подходит для решения большинства лабораторных работ.

Поле Предельное число итераций служит для управления временем решения задачи, путём ограничения числа промежуточных вычислений. В поле можно ввести время (в секундах) не превышающее 32767; значение 100, используемое по умолчанию, подходит для решения большинства простых задач.

При достижении отведённого временного интервала или при выполнении отведённого числа итераций, на экране появляется диалоговое окно

Поле Относительная погрешность служит для задания точности (допустимой погрешности), с которой определяется соответствие ячейки целевому значению или приближение к указанным границам. Поле должно содержать число из интервала от 0 (нуля) до1. Низкая точность соответствует введённому числу, содержащему меньшее количество десятичных знаков, чем число, используемое по умолчанию, например, 0,0001. Высокая точность увеличит время, которое требуется для того, чтобы сошёлся процесс оптимизации. Чем меньше введённое число, тем выше точность результатов.

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

Поле Сходимость результатов поиска решения применяется только к нелинейным задачам. Когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа, указанного в поле Сходимость , поиск прекращается. Условием сходимости служит дробь из интервала от 0 (нуля) до 1. Лучшую сходимость характеризует большее количество десятичных знаков, например, 0,0001 – это меньшее относительное изменение, чем 0,01. Чем меньше его значение, тем выше точность результатов. Лучшая сходимость требует больше времени на поиск оптимального решения.

Флажок Линейная модель служит для ускорения поиска решения линейной задачи оптимизации или линейной аппроксимации нелинейной задачи.

Флажок Неотрицательные значения позволяет установить нулевую нижнею границу для тех влияющих ячеек, для которых она не была указана в поле Ограничение диалогового окна Добавить ограничение .

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

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

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

Линейная служит для использования линейной экстраполяции вдоль касательного вектора.

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

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

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

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

Кнопки Метод поиска служат для выбора алгоритма оптимизации (метод Ньютона или сопряжённых градиентов).

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

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

Для решения линейных задач используются алгоритмы симплексного метода. Для решения целочисленных задач используется метод ветвей и границ.

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

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

1. Сохранение модели оптимизации

  1. В меню Сервис выберите команду Поиск решения.
  2. Нажмите кнопку Параметры.
  3. Нажмите кнопку Сохранить модель. Появляется окно Сохранить модель, рис. 4.
  4. В поле Задайте область модели введите ссылку на верхнюю ячейку столбца, в котором хотите разместить модель оптимизации.

Рис. 4. Диалоговое окно Сохранить модель.

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

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

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

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

1 В меню Сервис выберите команду Поиск решения.

2. Нажмите кнопку Параметры.

3. Нажмите кнопку Загрузить модель. Появляется окно, аналогичное окну Сохранить модель.

Диалоговое окно Загрузить модель используется для задания ссылки на область загружаемой (ранее сохранённой) модели оптимизации. Ссылка должна адресовать область модели целиком не достаточно указать только первую ячейку.

Вычисления и результаты решения задачи

Для запуска оптимизатора нажмите кнопку Выполнить в окне Поиск решения.

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

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

По окончании счёта появляется диалоговое окно Результаты поиска решения, рис. 5.

Рис. 5. Диалоговое окно Результаты поиска решения.

Поле Тип отчёта служит для указания типа отчёта, размещаемого на отдельном листе книги.

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

Отчёт Устойчивость используется для создания отчёта, содержащего сведения о чувствительности решения к малым изменениям в формуле модели или в формулах ограничений. Такой отчёт не создаётся для моделей, значения в которых ограничены множеством целых чисел. В случае нелинейных моделей отчёт содержит данные для градиентов и множителей Лагранжа. В отчёт по нелинейным моделям включаются ограниченные затраты, фиктивные цены, а также диапазоны ограничений.

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

К сожалению, эти отчёты очень неудобны. Они перегружены плохо читаемыми абсолютными ссылками со знаками доллара. Желает лучшего и перевод с английского на русский.

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

Просмотр промежуточных результатов поиска решения

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

В диалоговом окне Поиск решения нажмите кнопку Параметры.

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

На экране появится диалоговое окно , рис. 6, а влияющие ячейки листа изменят свои значения.

Чтобы остановить поиск решения и вывести на экран диалоговое окно Результаты поиска решения, нажмите кнопку Стоп.


Рис.6. Диалоговое окно Текущее состояние поиска решения.

Чтобы выполнить следующую итерацию и просмотреть её результаты, нажмите кнопку Продолжить.

Возникающие проблемы и сообщения процедуры поиска решения

Оптимальное решение не найдено

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

Пользователь прервал процесс поиска.

Команда Показывать результаты итераций в диалоговом окне Параметры поиска решения выбрана перед Выполнить.

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

Установлен флажок Линейная модель в диалоговом окне Параметры поиска решения, в то время как решаемая задача не линейна.

Значение, заданное в поле Установить целевую диалогового окна Поиск решения , неограниченно увеличивается или уменьшается. Необходимо уменьшить значения полей Максимальное время или Итерации в диалоговом окне Параметры поиска решения.

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

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

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

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

Итоговые сообщения процедуры поиска решения

1. Если поиск решения успешно завершён, в диалоговом окне Результаты поиска решения

Решение найдено. Все ограничения и условия оптимальности выполнены.

Все ограничения соблюдены с установленной точностью и найдено заданное значение целевой ячейки.

Поиск свёлся к текущему решению. Все ограничения выполнены.

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

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

Поиск не может улучшить текущее решение. Все ограничения выполнены.

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

3. Поиск остановлен (истекло заданное на поиск время).

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

4. Поиск остановлен (достигнуто максимальное число итераций).

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

5. Значения целевой ячейки не сходятся.

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

6. Поиск не может найти подходящее решение.

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

7. Поиск остановлен по требованию пользователя.

Нажата кнопка Стоп в диалоговом окне Текущее состояние поиска решения после прерывания поиска решения в процессе выполнения итераций.

8. Условия для линейной модели не удовлетворяются.

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

9. При поиске решения обнаружено ошибочное значение в целевой ячейке или в ячейке ограничения.

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

Набрано неверное имя или формула в окне Добавить ограничение или окне Изменить ограничение или в поле Ограничения были заданы целое или двоичное ограничение. Чтобы ограничить значения ячейки множеством целых чисел, выберите оператор целого ограничения в списке условных операторов. Чтобы установить двоичное ограничение, выберите оператор для двоичного ограничения.

10. Мало памяти для решения задачи.

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

Примеры выполнения задач

ПРИМЕР № 1

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

Какое количество продукции каждого вида должно изготовляться, чтобы доход от реализации был максимальным?

1. Формулировка математической модели задачи :

· переменные для решения задачи: x 1 – суточный объём изготовления продукции А, x 2 – суточный объём изготовления продукции Б, x 3 – суточный объём изготовления продукции В, x 4 – суточный объём изготовления продукции Г;

· определение функции цели (критерия оптимизации). Суммарная суточная прибыль от изготовления всех видов продукции равна:

F=12* x 1 +7* x 2 +18* x 3 +10* x 4,

поэтому цель состоит в том, чтобы среди всех допустимых значений x 1, x 2, x 3, x 4 найти такие, которые максимизируют суммарную прибыль от изготовления продуктов F:

· ограничения на переменные:

1. объём производства продукции не может быть отрицательным, т. е.

2. расход исходного продукта для изготовления всех видов продукции не может превосходить максимально возможного запаса данного исходного продукта, т. е.

Таким образом, получаем следующую математическую модель задачи:

· Найти максимум следующей функции:

F=12* x 1 +7* x 2 +18* x 3 +10* x 4 max;

· При ограничениях вида:

1* x 1 +2* x 2 +1* x 3 +0* x 4 ≤ 18,

1* x 1 +1* x 2 +2* x 3 +1* x 4 ≤ 30,

1* x 1 +3* x 2 +3* x 3 +2* x 4 ≤ 40,

x 1 ≥ 0, x 2 ≥ 0, x 3 ≥ 0, x 4 ≥ 0;

2. Подготовка листа рабочей книги MS Excel для вычислений – на рабочий лист вводим необходимый текст, данные и формулы в соответствии с рис. 7. Переменные задачи x 1, x 2, x 3, x 4 находятся соответственно в C3, С4, С5, С6 . Целевая функция находится в ячейке С8 и содержит формулу:

12*C3+7*C4+18*C5+10*C6

Ограничения на задачу учтены в ячейках С10:С12.

3. Работа с надстройкой Поиск решения – воспользовавшись командой Сервис | Поиск решения, вводим необходимые данные для рассматриваемой задачи (установка данных в окне Поиск решения приведена на рис. 8). Результат работы по поиску решения помещён на рис. 9 – 14.

Рис. 7. Рабочий лист MS Excel для решения задачи .

Рис. 8. Установка необходимых параметров задачи в окне Поиск решения .

Рис.9. Результаты расчёта надстройки Поиск решения.

Рис. 10. Отчёт по результатам поиска решения.

Рис. 11. Отчёт по устойчивости поиска решения.


Рис. 12. Отчёт по пределам поиска решения.

ВЫВОД : из решения видно, что оптимальный план выпуска предусматривает изготовление продукции видов "А" и "Г". А продукцию видов "Б" и "В" производить не стоит. Полученная Вами прибыль составит 326 усл. ед.

ПРИМЕР № 2

Задача распределения ресурсов

Предприятие изготавливает и продает краску двух видов: для внутренних и внешних работ. Для производства краски используется два исходных продукта A и B. Расходы продуктов A и B на 1 т. соответствующих красок и запасы этих продуктов на складе приведены в таблице:

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

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

I. Составление математической модели задачи.

1) Переменные задачи.

Обозначим: x 1 - количество производимой краски для

внутренних работ;

x 2 - соответствующее количество краски

для наружных работ.

2) Ограничения, которым должны удовлетворять переменные задачи:

по расходу продукта A: x 1 + 2x 2 3;

по расходу продукта B: 3x 1 + x 2 3;

В левых частях последних двух неравенств определены расходы продуктов A и B, а в правых частях неравенств записаны запасы этих продуктов.

3) Целевая функция задачи.

Обозначим Z доход от продажи краски (в тысячах рублей), тогда целевая функция задачи записывается так:

Z = 2x 1 + x 2 ,

таким образом, задача состоит в том, чтобы найти max Z=2x 1 +x 2 , при ограничениях:

x 1 + 2x 2 3 (A)

3x 1 + x 2 3 (B)

x 1 , x 2 0 .

Так как переменные задачи x 1 и x 2 входят в целевую функцию и ограничения задачи линейно , то соответствующая задача оптимизации называется задачей линейного программирования (ЛП)

В рассматриваемом примере содержатся только две переменные x 1 и x 2 , поэтому задачу можно решить графически.

1) На плоскости x 1 , x 2 строим область допустимых значений переменных, определяемую ограничениями задачи:

x 1 + 2x 2 3 (A)

3x 1 + 1x 2 3 (B)

x 1 , x 2 0 .

Последнее ограничение определяет первый квадрант плоскости. Чтобы построить множество точек удовлетворяющих неравенству (А) нанесем на плоскость график прямой, определяющий границу этого множества: x 1 +2x 2 =3 (A).

Линии уровня целевой функции. Линией уровня называется множество точек, на которых функция принимает постоянное значение:

Z = 2x 1 + x 2 = К,

где К - задаваемая постоянная.

При К = 1 уравнение линии уровня будет:

2x 1 + x 2 = 1

или (в отрезках) :

При К = 2, аналогично:

2x 1 + x 2 = 2 , или .

Нанеся линии уровня на область допустимых решений (рис.13), получим, что при увеличении значения Z соответствующая линия уровня перемещается параллельно предыдущей вправо и вверх. Таким образом, точкой из многоугольника ABCD в которой целевая функция Z имеет максимальное значение будет вершина С. Эта точка и определяет решение задачи.

x 1 + 2x 2 = 3 (A)

3x 1 + x 2 = 3 (B)

x 1 * = 0.6 ; x 2 * = 1.2 ;

максимальное значение Z:

Z * = 2*0.6 + 1.2 = 2.4.

Надстройка Поиск решения в Microsoft Excel даёт возможность найти решение, оптимальное при нескольких входных значениях и наборе ограничений на решение. Программа Поиск решения содержит параметры, управляющие процессом поиска решения: максимальное время, число итераций, точность, допустимое отклонение. Каждый из этих параметров имеет значение по умолчанию, подходящее для большинства задач. Использование новых установок параметров обычно необходимо для проведения серьёзных исследований сложных систем управления. Диспетчер сценариев способен запомнить несколько решений, найденных данным средством, и сгенерировать на этой основе отчёт. Надстройка Поиск решения готовит три вида отчётов, которые характеризуют найденное решение задачи: отчёт по результатам, отчёт по устойчивости и отчёт по пределам. Режим пошагового поиска позволяет наблюдать последовательность приближений к оптимальному решению задачи. Во многих случаях это помогает «почувствовать» сходимость процесса и установить причины неудач и тупиков при поиске оптимального решения. В результате поиска решения EXCEL выводит сообщения о том, удалось ли получить оптимальное решение задачи.

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

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

Список литературы

1. Л. В. Рудикова «Microsoft Excel для студента», Санкт – Петербург, БХВ-Петербург, 2005;

2. «Лабораторные работы на персональном компьютере» И. Ф. Цисарь, издательство «Экзамен», Москва, 2002;

3. Додж М. и др. «Эффективная работа с Microsoft Excel», 2000.СПб.:Питер, 2001.

4. Солодовников А. С. «Введение в линейную алгебру и линейное программирование». Москва, издательство «Просвещение», 1966. – 184 с.

5. Стрейвер А. «Теория линейного и целочисленного программирования» в двух томах, том 1: перевод с английского. – Москва: Мир, 1991. – 360 с.

6. Ашманов С.А.«Линейное программирование». - М.: Наука, 1981.

7. Банди Б. «Основы линейного программирования»: Пер. с англ. - М.: Радио и связь, 1989.

8. Кораблин М. А. «Информатика поиска управленческих решений», Москва, СОЛОН-Пресс, 2003.

9. Габасов Р., Кириллова Ф.М. Методы линейного программирования. Ч.1. Общие задачи, Минск, Изд-во БГУ им. В.И. Ленина, 1977. - 176 с.

Введение

4.1. Исходные данные

4.2. Формулы для вычислений

4.3. Заполнение диалогового окна «Поиск решения»

4.4. Результаты решения

Заключение

Cписок литературы

Введение

линейный программирование excel оптимизационный задача

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

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

1. Исходные данные для решения задачи

Исходные данные включают в себя - схему расположения угольных бассейнов (УБ) и электрических станций (ЭС) с указанием транспортных связей между ними, таблицы, содержащие сведения о годовой производительности и удельной цене топлива УБ, установленной мощности, числе часов использования установленной мощности и удельный расход топлива на ЭС, расстояниях между УБ и ЭС и удельной стоимости перевозки топлива по трассам УБ-ЭС.

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

2. Краткие сведения об электронных таблицах MS Excel

Рис. 2. Вид окна приложения

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

.Использовать сложные формулы, содержащие встроенные функции.

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

.Создавать сводные таблицы.

.Применять к таблицам сортировку и фильтрацию данных.

.Осуществлять консолидацию данных (объединение данных из нескольких таблиц в одну).

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

.Выполнять автоматизированный поиск ошибок в формулах.

.Защищать данные.

.Использовать структурирование данных (скрывать и отображать части таблиц).

.Применять автозаполнение.

.Применять макросы.

.Строить диаграммы.

.Использовать автозамену и проверку орфографии.

.Использовать стили, шаблоны, автоформатирование.

.Обмениваться данными с другими приложениями.

Ключевые понятия :

.Рабочая книга - основные документы, хранится в файле.

2.Лист (объем: 256 столбцов, 65536 строк).

.Ячейка - наименьшая структурная единица размещения данных.

.Адрес ячейки - определяет положение ячейки в таблице.

.Формула - математическая запись вычислений.

.Ссылка - запись адреса ячейки в составе формулы.

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

Ввод данных :

Данные могут быть следующих типов -

·Числа.

·Текст.

·Функции.

·Формулы.

Вводить можно -

·В ячейки.

·В строку формул.

Если на экране в ячейке после ввода появляется ########, значит число длинное и в ячейке не помещается, то надо увеличить ширину ячейки.

Формулы - определяют, каким образом величины в ячейках связаны друг с другом. Т.е. данные в ячейке получаются не заполнением, а автоматически вычисляются. При изменении содержимого ячеек, на которые есть ссылка в формуле, меняется и результат в вычисляемой ячейке. Все формулы начинаются знаком =. Далее могут следовать -

·Ссылка на ячейку (например, А6).

·Функция.

·Арифметический оператор (+, -, /, *).

·Операторы сравнения (>, <, <=, =>, =).

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

Функции - это стандартные формулы для выполнения определенных задач. Функции используются только в формулах.

Способ: Вставка - Функция или в строке формул щелкнуть на = . Появится диалоговое окно со списком десяти недавно использованных функций. Для расширения списка выбрать Другие функции…, откроется другое диалоговое окно, где функции сгруппированы по типам (категориям), приведено описание назначения функции и их параметров.

Полное описание по работе с электронными таблицами MS Excel, можно найти в учебниках и пособиях (специализированных).

3. Математическая постановка задачи

По критерию минимума затрат на топливо для ЭС указанного района электроснабжения необходимо определить их оптимальное топливоснабжение от трех угольных бассейнов с учетом ограничения по потребностям ЭС и производительности УБ.

Исходные данные задачи и переменные, подлежащие определению в ходе ее решения, можно представить в виде табл.3


Обозначение данных:

Вуб1, Вуб2, Вуб3 - производительность угольных бассейнов, тыс.тонн;

Суб1, Суб2, Суб3 - стоимость топлива на угольных бассейнах, у.е./тонн;

Lу - длина железнодорожного пути между УБ к ЭС, км;

Су - удельная стоимость перевозки топлива по трассе от УБ к ЭС, у.е./тонна*км (С11=С12=С13=С21=С22=С23=С31=С32=С33);

Ву - объем топлива, доставляемого от УБ на ЭС, тыс.тонн;

ВЭС1, ВЭС2, ВЭС3 - годовая потребность в топливе первой, второй, третьей ЭС соответственно, тыс.тонн;

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

Необходимо определить оптимальный объем топлива (Ву), доставляемые от УБ к каждой из ЭС, при которых суммарные затраты на топливо для всех трех ЭС будут минимальными.

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

4. Решение задачи линейного программирования

.1 Исходные данные

Рис. 4. Исходные данные

4.2 Формулы для вычислений

Рис.5. Промежуточные расчеты

4.3 Заполнение диалогового окна «Поиск решения»

Рис. 6. Процесс оптимизации.

Рис.6.1.Задание ограничений (топлива должно быть>0).

Рис.6.2.Задание ограничений (кол-во привез. = кол-ву потреблен. топлива).

Рис.6.3.Задание ограничений (годовая отгрузка, не превышать производ. УБ1).

Рис.6.4.Задание ограничений (годовая отгрузка, не превышать производ. УБ2).

Рис.6.5.Задание ограничений (годовая отгрузка, не превышать производ. УБ3).

.4 Результаты решения

Рис.8. Результаты решения задачи

Ответ: Количество топлива (тыс. тонн), доставлено на:

ЭС4 из УБ1 составляет 118,17тн;

ЭС6 из УБ1 составляет 545,66тн;

ЭС5 из УБ2 составляет 19,66тн;

ЭС6 из УБ2 составляет 180,34тн;

ЭС5 из УБ3 составляет 277,94тн;

ЭС6 из УБ3 составляет 526,00тн;

ЭС4 всего 118,17тн;

ЭС5 всего 297,60тн;

ЭС6 всего 1252,00тн;

Затраты на топливо составили (у.е.):

Для ЭС4 - 496314,00.

Для ЭС5 - 227064,75.

Для ЭС6 - 23099064,78.

Суммарные затраты для всех ЭС составляют - 23822443,53 у.е.;

Заключение

Краткие сведения об электронных таблицах MS Excel. Решение задачи линейного программирования. Решение с помощью средств Microsoft Excel экономической оптимизационной задачи, на примере "транспортной задачи". Особенности оформления документа MS Word.

В курсовой работе показано как создавать и работать при оформлении документа MS Word, в рамках которого рассмотрено решение экономической оптимизационной задачи, на примере «транспортная задача», взятой из области общей энергетики, средствами Microsoft Excel.

Пример решения задачи линейного программирования с помощью MS Excel

Хозяйство специализируется в полеводстве на производстве зерна, сахарной свеклы и подсолнечника. В с.-х. предприятии имеются 3200 га пашни, трудовые ресурсы в объеме 7 000 чел.-дней и минеральные удобрения в объеме 15000 ц.д.в. Требуется найти такое сочетание посевных площадей, которое обеспечило бы получение максимума прибыли.

Следует также учесть, что

- площадь посева технических культур (сахарной свеклы и подсолнечника) не должна превышать 25% общей площади пашни;

- хозяйством заключен договор на продажу зерна в объеме 65000 ц.

Для разработки экономико-математической модели необходима подготовка входной информации (табл. 1).

Таблица 1

Показатели

Сельскохозяйственные культуры

зерновые

сахарная свекла

подсолнечник

Урожайность, ц/га

Цена реализации 1 ц продукции, руб./ц.

Стоимость товарной продукции с 1 га, тыс. руб.

5,59

20,62

6,73

Затраты на 1 га:

МДС, тыс. руб.

12,7

труда, чел.-дней.

минеральных удобрений, ц.д.в.

Прибыль с 1 га, руб.

2,89

7,93

3,63

За неизвестные примем площади посева сельскохозяйственных культур по видам:

X 1 - зерновых культур

X 2 - сахарной свеклы

X 3 - подсолнечника

Для построения экономико-математической модели задачи необходимо учесть все условия. В данном случае, по этим условиям можно составить пять ограничений:

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

1) Х1+Х2+Х3<=3200

- сумма площадей посева технических культур не должна превышать площади, которая может быть отведена для этой цели (3200*0,25=800 га). Коэффициентами при неизвестных в этом ограничении характеризуют расход пашни, отведенной под посевы технических культур, на 1 га каждой технической сельскохозяйственной культуры. В данном случае технико-экономические коэффициенты по неизвестным Х2 и Х3 будут равняться единице, а по нетехническим сельскохозяйственным культурам (Х3) - нулю. В правой части записывается максимальная площадь пашни, которая может быть отведена под посевы технических культур.

2) Х2+Х3<=800

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

3) 1,5Х1+4,5Х2+1,5Х3<=7000

4) 2Х1+15Х2+2,3Х3<=15000

- пятое ограничение гарантирует производство запланированного объема зерна. В качестве коэффициентов при переменных выступает выход зерна с 1 га площади посева с.-х. культур. При неизвестной Х1 это урожайность зерновых (таблица 1). При переменных Х2 и Х3 этот коэффициент равен нулю. В правой части записывается план производства зерна.

5) 26Х1>=65000

В результате получена система пяти линейных неравенств с тремя неизвестными. Требуется найти такие неотрицательные значения этих неизвестных Х1>=0; Х2>=0; Х3>=0, которые бы удовлетворяли данной системе неравенств и обеспечивали получение максимума прибыли от отрасли растениеводства в целом:

Z max = 2,89Х1+7,93Х2+3,53Х3

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

Поскольку данная задача решается с помощью MS Excel , то и подготовку всей входной информации для построения экономико-математической модели целесообразно осуществлять также с использованием этого табличного процессора (рис 1). Это облегчает не только расчеты технико-экономических коэффициентов и других данных, но и дает в дальнейшем возможность автоматического обновления информации в экономико-математической модели.

Рисунок 1

Вся разработанная информация сводится в развернутую экономико-математическую модель и заносится в рабочий лист MS Excel . (Рис. 2.)


Рисунок 2

Данные в модель рекомендуется заносить в виде ссылок на ячейки с соответствующей информацией в расчетных рабочих листах или рабочих листах с исходными сведениями. На рисунке 3 показано, как в ячейке F9 представлена информация по норме затрат удобрений на 1 га посева подсолнечника.

Рисунок 3

В столбцы А («№»), В («Ограничения»), С («Единицы измерения») и H («Тип ограничений») вводятся соответствующие данные непосредственно в модель (рис.1). Они не используются в расчетах и служат для информативности и облегчения понимания содержания модели. В столбец I («Объем ограничений») вводятся ссылки на ячейки, содержащие соответствующую названию столбца информацию (значения правых частей построенных ранее неравенств).

Для искомых величин переменных Х1 , Х2 , Х3 нами были оставлены пустые ячейки - соответственно D5 , E 5 , F 5 . Изначально пустые ячейки программа MS Excel воспринимает как ячейки, значение которых равно нулю. Столбец G , названный нами «Сумма произведений », предназначен для определения суммы произведений значений искомых неизвестных (ячейки D5 , E 5 , F 5 ) и технико-экономических коэффициентов по соответствующим ограничениям (строки 6-10) и целевой функции (строка 11). Таким образом, в столбце G определяется:

- - количество используемых ресурсов (ячейка G6 – общей площади пашни; G7 – пашни, которая может быть использована под посевы технических культур; G8 – трудовых ресурсов; G9 – минеральных удобрений);

- - количество произведенного зерна (ячейка G10 );

- - величина прибыли (ячейка G11 ).

На рисунке 2 показано, как в ячейке G11 реализуется запись суммы произведений значений переменных (площадей посева с.-х. культур - ячейки D5 , E 5 , F 5 ) на соответствующие прибыли с 1 га их посева(ячейки D11 , E 11 , F 11 )с помощью функции MS Excel «СУММПРОИЗВ ». Так как при написании данной формулы использованы абсолютные адресации на ячейки от D5 до F 5 ,эта формула может быть скопирована в другие ячейки от G 6 до G10 .

Таким образом, построен опорный план (рис. 2) и получено первое допустимое решение. Значения неизвестных Х1 , Х2 , Х3 равны нулю (ячейки D5 , E 5 , F 5 - пустые ячейки), ячейки столбца G «Сумма произведений» по всем ограничениям (строкам 6-10) и целевой строке (строка 11) также имеют нулевые значения.

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

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

После выбора данной команды появится диалоговое окно (рис. 4).


Рисунок 4

Поскольку в качестве критерия оптимизации нами выбрана максимизация прибыли, в поле Установить целевую ячейку введите ссылку на ячейку, содержащую формулу расчета прибыли. В нашем случае это ячейка $G$11 . Чтобы максимизировать значение конечной ячейки путем изменения значений влияющих ячеек (влияющими, в данном случае это и изменяемые ячейки, являются ячейки, которые предназначены для хранения значений искомых неизвестных), переключатель установите в положение максимальному значению ;

В поле Изменяя ячейки введите ссылки на изменяемые ячейки, разделяя их запятыми; либо, если ячейки находятся рядом, указывая первую и последнюю ячейку, разделяя их двоеточием ($ D $5:$ F $5 ).

В поле Ограничения введите все ограничения, накладываемые на поиск решения. Добавление ограничения рассмотрим на примере добавления первого ограничения по общей площади пашни.

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

Рисунок 5

В поле Ссылка на ячейку введите адрес ячейки, на значение которой накладываются ограничения. В нашем случае, это ячейка $ G $6 , где находится формула расчета используемой пашни в текущем плане.

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

В поле Ограничение введите ссылку на ячейку, в которой находится значение наличия площади пашни в хозяйстве, либо ссылка на это значение. В нашем случае, это ячейка $ I $6

В результате диалоговое окно примет следующий вид (рис. 6).

Рисунок 6

Чтобы принять ограничение и приступить к вводу нового, нажмите кнопку Добавить . Аналогично вводятся и другие ограничения. Чтобы вернуться в диалоговое окно Поиск решения , нажмите кнопку OK .

После выполнения вышеперечисленных инструкций диалоговое окно Поиск решения будет иметь следующий вид (рис. 7).


Рисунок 7

Для изменения и удаления ограничений в списке Ограничения диалогового окна Поиск решения укажите ограничение, которое требуется изменить или удалить. Выберите команду Изменить и внесите изменения либо нажмите кнопку Удалить .

Флажок Линейная модель в диалоговом окне Параметры Поиска решения (рис. 8) позволяет задать любое количество ограничений. Флажок Неотрицательные значения позволит соблюсти условие неотрицательности переменных (при решении нашей задачи – поставить обязательно). Остальные параметры можно оставить без изменений, либо установить нужные для вас параметры, при необходимости используя справку.


Рисунок 8

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

- чтобы восстановить исходные данные, выберите вариант Восстановить исходные значения .


Рисунок 9

Для того чтобы прервать поиск решения, нажмите клавишу ESC .

Лист Microsoft Excel будет пересчитан с учетом найденных значений влияющих ячеек. В результате решения и сохранения результатов поиска на листе модель примет следующий вид (табл. 10).


Рисунок 10

В ячейках D5 -F5 получены значения искомых неизвестных (площади посева равны: зерновых -2500 га, сахарной свеклы - 661 га, подсолнечника – 39 га), в ячейках G6 -G9 определены объемы используемых ресурсов (общей площади пашни – 3200 га; площади пашни, которая может быть использована под посевы технических культур – 700 га; трудовых – 6781,9 чел.-дней; минеральных удобрений – 15000 ц.д.в.), в ячейке G10 установлено количество произведенного зерна (65000 ц.). При всех этих значениях величина прибыли достигает 12603,5 тыс. руб. (ячейка G11 ).

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


Рисунок 11

Одной из наиболее часто встречающихся причин невозможности найти оптимальное решение является такая ситуация, когда в результате решения задачи выясняется, что имеются ограничения, которые не выполняются. Сохранив найденное решение на листе, требуется построчно сравнить полученные значения столбцов «Сумма произведений» и «Объем ограничений» и проверить, удовлетворяет ли отношение между ними ограничению, стоящему в столбце «Тип ограничений». Найдя, таким образом, невыполняемые ограничения необходимо найти и ликвидировать причины, обуславливающие невозможность соблюдения данного конкретного условия (это может быть, например, слишком большие или, наоборот, очень маленькие запланированные объемы ограничений и т.п.).

Если ограничений в модели очень много, то визуально достаточно трудно сравнивать и проверять на верность каждую строку. Для облегчения рекомендуется добавить в модель еще один столбец «Проверка», где с помощью функций MS Excel «ЕСЛИ » и «ОКРУГЛ » можно организовать автоматическую проверку (рис. 12).


Рисунок 12