5348

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

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

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

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

Русский

2012-12-08

89 KB

54 чел.

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

Цель работы: ознакомиться со средствами поиска решения 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 (с рассчитанными значениями)

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


 

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

19604. Конструкційні матеріали та їх види 26.42 KB
  Урок 6. Конструкційні матеріали та їх види 2 год. Мета уроку. Засвоєння знань про види та способи вибору конструкційних матеріалів; формування практичних вмінь визначати види конструкційних матеріалів за їх властивостями; сприяти розвитку пам’яті. Виховувати інтерес ...
19605. Види та способи вибору конструкційних матеріалів. Породи дерев та їх будова 102.03 KB
  Урок 7. Види та способи вибору конструкційних матеріалів. Породи дерев та їх будова 1год. Мета уроку. Засвоєння знань про породи деревини та її будову сортамент виготовлення шпону фанери ДВП ДСП та їх призначення; формування умінь здійснювати вибір матеріалу для виг...
19606. Техніка. Короткі відомості з історії розвитку техніки 31.2 KB
  Техніка і технологічні процеси виготовлення виробів з конструкційних матеріалів Урок 8. Техніка. Короткі відомості з історії розвитку техніки 1 год. Мета. Засвоєння знань про історію розвитку техніки та роль машин у сучасному виробництві і побуті як знарядь праці; п
19607. Типові та спеціальні деталі. Види з’єднань 28.39 KB
  Урок 9. Типові та спеціальні деталі. Види з’єднань 1 год. Мета уроку. Засвоєння знань про типові та спеціальні деталі види з’єднань формування практичних вмінь у з’єднанні деталей. Розвиток в учнів пам’яті. Виховання основ культури праці. Об’єкт навчальної праці: мех...
19608. Ознайомлення з механізмами у шкільній майстерні 77.59 KB
  Урок 10. Ознайомлення з механізмами у шкільній майстерні 1 год. Мета. Засвоєння знань про графічне зображення механізмів кінематичні схеми. Формування вмінь виконувати графічне зображення деталей і механізмів. Розвивати просторову уяву. Виховувати інтерес до технік...
19609. Виробничий процес. Етапи виготовлення виробу. Прийоми вимірювання лінійкою і кутником 31.79 KB
  Урок 11. Виробничий процес. Етапи виготовлення виробу. Прийоми вимірювання лінійкою і кутником 1 год. Мета уроку. Засвоєння знань про виробничий процес етапи виготовлення виробу вимірювання і розмічання; формування вмінь вимірювати і розмічати заготовки. Розвивати т
19610. Прийоми пиляння фанери ножівкою 58.93 KB
  Урок 12. Прийоми пиляння фанери ножівкою 1 год. Мета уроку. Засвоєння знань про способи пиляння деревини. Засвоєння вмінь виконувати пиляння фанери ножівкою. Розвивати окомір. Виховувати акуратність дисциплінованість. Об’єкт навчальної праці: кухонна дошка Облад...
19611. Прийоми пиляння фанери лобзиком 26.59 KB
  Урок 13 14. Прийоми пиляння фанери лобзиком 2 год. Мета уроку. Формування вмінь виконувати пиляння фанери лобзиком; закріплення знань про пиляння деревини. Розвивати точність окомір. Виховувати акуратність виконавчу дисципліну творче ставлення до праці. Об’єкт нав...
19612. Свердління отворів коловоротом і ручним дрилем 55.43 KB
  Урок 15. Свердління отворів коловоротом і ручним дрилем 1 год. Мета уроку. Засвоєння знань про будову і принцип дії коловорота та ручного дриля; формування вмінь користуватись ручним дрилем та коловоротом. Розвивати окомір точність. Виховувати економне ставлення до ма...