5348

Информационная технология поиска решения

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

Информатика, кибернетика и программирование

Информационная технология поиска решения Цель работы: ознакомиться со средствами поиска решения MS Excel на примере задач линейного программирования. Краткая теория Методы линейного программирования эффективно используются для решения задач опт...

Русский

2012-12-08

89 KB

55 чел.

Информационная технология поиска решения

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

Краткая теория

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

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

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

Задачи линейного и нелинейного программирования ограниченной размерности эффективно решаются в среде MS Excel с использованием специальной надстройки  Поиск решения (Сервис/Надстройка). Основным ограничением модели в Excel является максимальное число переменных (200).

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

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

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

Задание 1

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

Условие задачи

Предприятие располагает тремя производственными ресурсами (сырьем, оборудованием, электроэнергией) и может организовать производственный процесс двумя различными способами. При первом способе за один месяц предприятие выпускает 3 тыс. изделий, при втором – 4 тыс. изделий.

Исходные данные приведены в таблице:

Производственный ресурс

Расход ресурсов за месяц

Общий ресурс

1 –й способ

2 –й способ

Сырье

1

2

4

Оборудование

1

1

3

Электроэнергия

2

1

8

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

Порядок выполнения задания:

  1.  На рабочем листе определите блок ячеек для переменных х1 и х2.  Под заголовками имен переменных оставьте пустые ячейки для значений переменных (a4:b4). Эти ячейки используются для ссылок на переменные в формулах и вывода результатов поиска решения
  2.  Создайте блок клеток для целевой функции. Введите заголовок (a6) и в смежной ячейке (b6)   запишите функцию цели, как это показано на рисунке 1. Аргументами в формуле являются ссылки на пустые ячейки для значений переменных (a4 и b4). После ввода формулы в ячейке b6 появится нулевое значение.
  3.  Создайте блок клеток, содержащих ограничения, накладываемые на переменные. Введите заголовок и формулы ограничений в виде текста (a9:a12). В смежном столбце введите левые части неравенств системы ограничений (ячейки b10:b12), а в ячейки c10:c12 – правые части ограничений. Аргументами в формулах также будут являться ссылки на свободные ячейки для значений переменных. Результат подготовленных данных разработанной математической модели на рабочем листе MS Excel представлен на рисунке 1.

A

B

C

1

2

3

x1

x2

4

5

6

функция

=3*A4+4*B4 

7

8

9

ограничения

10

x1+2*x2<=4

0

4

11

x1+x2<=3

0

3

12

2x1+х2<=8

0

8

Рисунок 1 – Пример решения задачи линейного программирования

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

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

Задание 2

Предположим, что предприятие имеет 4 фабрики и четыре пункта, в которые поставляется продукция. Фабрики располагаются в пунктах А, Б, В, Г с производственными возможностями 20, 60, 50 и 30 ед. продукции ежедневно. В пункты поставки К, Л, М, Н предприятие должно отправлять ежедневно 25, 35, 80 и 20 ед. продукции соответственно. Хранение на фабрике единицы продукции, не поставленной вовремя, в день обходится предприятию 15 ден. ед. Штраф за просроченную поставку единицы продукции составляет 50 ден.ед. в день.

Транспортные расходы приведены в таблице 1.

Таблица 1 – Транспортные расходы

Стоимость перевозки единицы продукции

6

3

4

5

5

2

3

3

3

4

2

4

5

6

2

7

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

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

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

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

Для решения задачи рассмотрим ее математическую модель. Неизвестными в этой задаче являются объемы перевозок. Пусть объем перевозок с i-той фабрики в j-тый центр поставок Xij. Функция цели – это суммарные транспортные расходы, т.е.

Где Cij – стоимость перевозки единицы продукции с I-той фабрики в j-тый центр поставок.

Неизвестные в этой модели должны удовлетворять следующим условиям:

  •  Объем перевозок не может быть отрицательным
  •  Так как модель сбалансирована, то вся продукция должна быть вывезена с фабрик, а заказы поставщиков должны быть выполнены.

В результате получается следующая модель:

Минимизировать

При ограничениях   

Где ai – объем производства на i-той фабрике, bj – спрос j- того центра поставок.

Выполним решение задачи средствами MS Excel.

Порядок действий:

  1.  Ввести исходные данные о стоимости перевозок в диапазон A2:D5.

Рисунок 2 – Вид рабочего листа с данными и формулами

  1.  В ячейки A13:D13 ввести формулы, вычисляющие объем продукции, ввозимой в пункты поставок.
  2.  В ячейки E9:E12 ввести формулы, вычисляющие объем продукции, вывозимой с фабрик.
  3.  В ячейку E13 ввести целевую функцию =СУММПРОИЗВ(A2:D6;A9:D12).
  4.  Выполнить команду меню Сервис/ Поиск решения и заполнить диалоговое окно Поиск решения.
  5.  Заполним диалоговое окно следующим образом:
    •  Установить целевую функцию                 $E$13

  •  Минимальному значению                              0

  •  Изменяя ячейки                                            $A$9:$D$12

  •  Ограничения                                                 $A$13:$D$13=$A$14:$D$14

                                                                                    $A$9:$D$12>=0

                                                                                    $E$9:$E$12=$F$9:F$12

  1.  В Параметрах поиска решения установите флажок Линейная модель.
  2.  После нажатия кнопки Выполнить средство поиска решения найдет оптимальный план поставок продукции и соответствующие ему транспортные расходы.

Задание к лабораторной работе

Задание 1

Фирма выпускает изделия двух типов А и Б. Виды и нормы расхода сырья каждого вида на единицу изделия приведены в таблице.

Изделие

Сырье

1

2

3

4

А

2

1

0

2

Б

3

0

1

1

Запасы сырья первого вида составляют 21 ед., второго вида – 4 ед., третьего вида – 6 ед. и четвертого – 10 ед. Выпуск одного изделия типа А приносит доход 300 ден. ед., одного изделия типа Б – 200 ден. ед.

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

Задание 2

Сформулируйте задание, постройте модель и решите транспортную задачу, используя средство «Поиск решения».

2

7

7

6

15

1

1

1

2

50

5

5

3

1

10

2

8

1

4

25

3

2

1

5

10

40

30

20

20

Содержание отчета

  1.  Титульный лист с постановкой задач
  2.  Результаты решения задания 1 (с формулами)
  3.  Результаты решения задания 1 (с рассчитанными значениями)
  4.  Результаты решения задания 2 (с формулами)
  5.  Результаты решения задания 2 (с рассчитанными значениями)

 Пример ввода формулы


 

А также другие работы, которые могут Вас заинтересовать

81026. Экономическая глобализация на современном этапе международных отношений 35.92 KB
  Экономическая глобализация началась в средние века завершается образованием глобальных сверкорпораций. Глобализация экономики с одной стороны создает условия доступа стран к передовым достижениям человечества обеспечивает экономию ресурсов стимулирует мировой прогресс а с другой закрепляет периферийные модели экономики потерю сырьевых интеллектуальных и трудовых ресурсов странами не входящих в золотой миллиард разорение малого бизнеса и безальтернативную конкуренцию между сильным и слабым. Экономическая глобализация процесс более...
81027. Политическая глобализация на современном этапе международных отношений 38.13 KB
  В тоже время политическая глобализация несет угрозу суверенитету любого государства. Политическая глобализация обеспечивается сетью транснациональных связей и отношений: деятельность международных организаций международных трибуналов экономических организаций МВФ Всемирный Банк. Тем не менее глобализация является объективным явлением современного мира которое воздействуя на мировое сообщество приводит его к новому состоянию.
81028. Культурная глобализация на современном этапе международных отношений 37.16 KB
  Как процесс многогранный и по всей видимости необратимый глобализация характеризуется такими явлениями которые обуславливают собой неоднозначное отношение к ним. Существует другая точка зрения согласно которой не смотря на то что люди во всем мире все более и более подвергаются воздействию иных культур через торговлю путешествия и СМИ глобализация рынков не приведет к снижению разнородности культур.
81029. Антиглобализм и альтерглобализм: особенности и сходства двух движений 39.62 KB
  Это многовекторное политическое движение направленное против процесса глобализации в его современных формах. Его основными чертами являются: признание объективного характера глобализации; отрицание безальтернативности существующей модели глобализации неолиберальноамериканоцентричной навязывания ее всему миру; разработка альтернативных моделей глобализации и альтернативного пути развития современной цивилизации; ведение борьбы с конструктивных позиций. Однако в последнее время чаще всего антиглобалисты отрицают только некоторые аспекты...
81030. Россия в процессах глобализации 37.65 KB
  Глобализация российским населением в большинстве случаев оценивается негативно. сокращение трудовых ресурсов странысокращение высокотехнологичных производств и расширение иностранного или филиального производстваПолитические перспективыПолитическая глобализация несет угрозу суверенитету любого государства. Политическая глобализация обеспечивается сетью транснациональных связей и отношений: деятельность международных организаций международных трибуналов экономических организацийБольшинство существующих международных организаций создающих...
81031. История создания и устав ООН. Структура ООН 41.01 KB
  Структура ООН Организация Объединённых Наций международная организация созданная для поддержания и укрепления международного мира и безопасности развития сотрудничества между государствами. История создания ООН во многом определяется именно этими факторами. Предшественником ООН была Лига Наций Вторая мировая война дала сильный толчок общественной и правительственной инициативе по организации безопасности и мира.
81032. Реформы ООН 47.61 KB
  Соответственно растёт востребованность ООН как механизма коллективного поиска путей преодоления вызовов XXI века. С другой стороны реформа нужна потому что без неё ООН постепенно будет утрачивать нынешние позиции окажется отодвинутой на обочину мировой политики превратится в статиста беспомощно взирающего на то как группы государств или отдельные страны решают спорные вопросы по своему усмотрению. 2 декабря 2004 года был обнародован доклад ldquo;Более безопасный мир: наша общая ответственностьrdquo;[1] который подготовила Группа...
81033. Миротворческие операции ООН 45.56 KB
  На практике такие операции осуществлялись до недавнего времени главным образом в рамках ООН по решениям СБ в отдельных случаях ГА ООН. Силы действуют под руководством СБ ООН но находятся под командованием Генерального секретаря выступающего от имени ООН и имеющего политические установки от Совета Безопасности. Что касается состава сил то они комплектуются за счет персонала из состава вооруженных полицейских сил и гражданских представителей различных государств на основе соглашений достигнутых правительствами этих стран с Генеральным...
81034. Международный валютный фонд (МВФ). Всемирная торговая организация (ВТО) 43.14 KB
  Всемирная торговая организация ВТО Международный валютный фонд МВФ англ. ВТО является преемницей Генерального соглашения по тарифам и торговле ГАТТ заключенного в 1947 году и на протяжении почти 50 лет фактически выполнявшего функции международной организации. ВТО отвечает за разработку и внедрение новых торговых соглашений а также следит за соблюдением членами организации всех соглашений подписанных большинством стран мира и ратифицированных их парламентами. ВТО строит свою деятельность исходя из решений принятых в 1986 1994 годах в...