5348

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

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

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

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

Русский

2012-12-08

89 KB

57 чел.

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

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

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


 

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

53685. Линейные алгоритмы 278 KB
  Развивающие: развитие алгоритмического и логического мышления, познавательный интерес обучающихся; развитие творческой активности обучающихся; формирование интереса к изучению предмета;
53686. Технология обработки баз данных. Основные понятия и возможности. Работа с готовой базой данных 75 KB
  Цели: Образовательные: Сформировать представления о назначении и области применения баз данных. Сформировать основные понятия темы База данных Информационная система Система управления базами данных СУБД.
53687. Работа с клавиатурным тренажером 39.5 KB
  Цели: Научить работать с клавиатурным тренажером Потренироваться печатать. Работа по теме урока: Сейчас мы приступим к работе с клавиатурным тренажером. Работа с клавиатурным тренажером. Работа в Блокноте 10 25 4.
53688. Информационные процессы 136 KB
  Цели урока: Ввести понятие информационных процессов познакомить учащихся с понятиями: источник и приемник информации канал связи носитель информации исполнитель. Задачи: Образовательная: изучить понятия: информационные процессы виды информационных процессов; изучить способы хранения передачи и обработки информации; научить приводить примеры получения передачи и обработки информации; научить учеников решать практические задачи на использование изученных понятий. Что такое информация для человека Назовите некоторые...
53690. Рисование с натуры натюрморта «Дары осени» 69 KB
  Ознакомить с натюрмортом как жанром изобразительного искусства; научить выполнять изображение с натуры, различать оттенки красок; развить умение анализировать форму и цветовую окраску овощей.
53691. Рисование с натуры яблока 68.5 KB
  Цели урока: Образовательная: научиться рисовать яблоко с натуры. Воспитательные: воспитать интерес к рисункам с натуры; способствовать формированию положительного отношения к изобразительному искусству пробудить желание творить. Задачи: научить построению рисунка с натуры с применением пропорции; способствовать развитию чувства цвета; стимулировать учащихся к самоконтролю и дисциплине.
53692. Объемные изображения в скульптуре. Тобольская резная кость 62.5 KB
  Словарь: скульптура лепка рельеф План урока Организационный момент. Слово скульптура нам известно уже давно но вот какими возможностями обладает объёмное изображение какие виды скульптурных изображений существуют мы познакомимся сегодня на уроке. Скульптура древнейший вид искусства возникший на заре существования человечества. Что же представляет собой скульптура и чем она отличается от других видов искусства В живописи изображение создаётся красками на плоскости холста.
53693. Декоративное рисование «Кокошник» 38.5 KB
  Давайте проверим все ли пришли сегодня на урок или кто то решил в такую замечательную погоду пойти вместо школы погулять в парке отмечаются присутствующие У: А какое у нас сейчас время года ребята А какой месяц А число Не забываем что отвечать нужно полным ответом. Ответ детей если затрудняются оказать помощь У: Молодцы ребята У: сегодня на уроке мы будем рисовать красивый головной убор русских девушек кокошник. Подготовительная беседа:...