Файл: ДКР Информационные технологии 2Б.docx

ВУЗ: Не указан

Категория: Методичка

Дисциплина: Информатика

Добавлен: 21.10.2018

Просмотров: 2181

Скачиваний: 23

ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.

Р = 20х1 + 24х2 + 28х3mах

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

В пакете Excel данная задача решается с помощью команды Поиск решения. Если на вкладке Данные отсутствует команда Поиск решения, то для ее установки необходимо выполнить команду Office - Параметры, Надстройки - Выделить строку Пакет анализа и щелкнуть по кнопке Пе­рейти - поставить флажок Поиск Решения и нажать ОК. После этого на вкладке Данные в группе Анализ появится команда Поиск Решения.

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

а) подготовить электронную таблицу с исходными данными (рисунок 1):

Рисунок 1 - Электронная таблица для решения задачи с исходными данными

  • Отведем ячейки В7:D7 под значения переменных, введя туда про­извольные начальные приближения, например, х1 = 1, х2 = 1, x3 = 1.

  • В ячейки F3:F5 введём формулы левых частей ограничений:

= 2x1 + x2 + 3x3

= x1 + 3x2 + 4x3

= 3x1 + 2x2 + x3

  • В ячейки В8:D8 введём общую прибыль каждой продукции.

  • В ячейку Е8 введем функцию цели (общую прибыль продукции):

= 20xx + 24x2 + 28x3

В режиме отображения формул эта таблица представлена на рисунке 2.

Рисунок 2 - Электронная таблица для решения задачи в режиме отображения формул

В режиме отображения значений эта таблица представлена на рисунке 3.

Рисунок 3 - Электронная таблица для решения задачи

б) После этого выберем команду Данные - Поиск решения и запол­ним открывшееся диалоговое окно Поиск решения (рисунок 4).

В поле Установить целевую ячейку дается ссылка на ячейку с функ­цией, для которой будет находиться максимум, минимум или заданное зна­чение. В нашей задаче в это поле вводим $Е$8.

Тип взаимосвязи между решением и целевой ячейкой задается путем установки переключателя в группе Равной. Для нахождения минимального или максимального значения функции этот переключатель ставится в поло­жение Maксимальному значению или Muнимальному значению соответ­ственно, для нахождения значения целевой функции, равного некоторому числу, переключатель ставится в положение 3начения. Тогда в соответст­вующее поле вводится требуемое число.

В поле Изменяя ячейки указываются ячейки, которые должны изме­няться в процессе поиска решения задачи, т.е. ячейки, отведенные под пере­менные задачи. В нашем случае вводим в это поле диапазон $В$7 : $D$7.

Рисунок 4- Диалоговое окно Поиск решения

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


Рисунок 5 - Диалоговое окно Добавление ограничения

В поле Ссылка на ячейки вводится первая часть ограничения - $F$3(щелчком по соответствующей ячейке), а в поле Ограничение - правая часть - $E$3. С помощью раскрывающегося списка вводится тип соотноше­ния между левой и правой частями ограничения (<,=,>,>=,<, цел). В нашем примере это знак <=

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

Кнопки Добавить, Изменить и Удалить позволяют, соответственно, ввести дополнительное ограничение, изменить вид выделенного в списке ог­раничения или удалить его.

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

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

Поле Максимальное время служит для ограничения времени, отпус­каемого на поиск решения. Время не должно превышать 32767 сек., что со­ставляет = 9,1 часа. По умолчанию задается 100 секунд.

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

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

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

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

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


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

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

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

Установив переключатель в положение Сохранить найденное реше­ние, можно сохранить найденное решение задачи в соответствующих ячей­ках электронной таблицы. В ячейках В7:D7 появятся оптимальные значения переменных, а в ячейке E8 - максимальное значение целевой функции.

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

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

На основе полученных результатов можно сделать следующий вывод: Кондитерскому цеху надо выпустить продукции первого вида M1 в количест­ве 920 усл. единиц, третьего вида M3 - в количестве 270 усл. единиц, про­дукцию второго вида М2 выпускать невыгодно. При таком плане выпуска продукции с учётом имеющихся ограничений будет получена максимальная прибыль в размере 25960 усл. единиц.

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

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

Устойчивость - отчет содержит сведения о чувствительности решения к малым изменениям в формуле целевой функции или формулах ограниче­ний. Этот отчет имеет 2 раздела: один для изменяемых ячеек, а второй для ограничений. Правый столбец в каждой ячейке содержит информацию о чув­ствительности. Раздел для изменяемых ячеек содержит значение для норми­рованного коэффициента, которое показывает, как целевая ячейка реагирует на увеличение значения в соответствующей изменяемой ячейке на одну еди­ницу. Подобным образом множитель Лагранжа в разделе для ограничений показывает, как целевая ячейка реагирует на увеличение соответствующего значения ограничения на одну единицу.

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

Информационное обеспечение:

  1. Михеева Е.В. Информационные технологии в профессиональной деятельности: учеб. пособие. – М.:ОИЦ «Академия», 2012, стр. 127-158.

  2. Филимонова Е.В. Информационные технологии в профессиональной деятельности: учебник – Ростов н/Д: Феникс, 2012, стр. 131-161.


Задание 5

Условие транспортной задачи задано величинами aj– запасов поставщиков, bj–потребностей потребителей, сij – тарифов на перевозку грузов. Требуется составить план перевозок груза с минимальными транспортными издержками. Решить задачу в Excel.

Номера заданий

1

bj

aj

7

7

7

7

2

2

bj

aj

19

19

19

19

4



4

16

30

17

10

16


20

15

11

22

19

1



6

30

27

26

9

23


20

21

18

11

4

3



10

13

4

22

3

1


20

26

29

23

26

24



10

3

1

5

4

24


20

21

10

3

19

27

















3

bj

aj

9

24

9

9

9

4

bj

aj

15

15

15

15

20



15

10

17

9

20

30


21

30

24

11

12

25



15

13

4

24

26

26


19

26

4

29

20

24



19

22

24

30

27

29


15

27

14

14

10

18



11

25

12

11

24

23


25

6

14

28

8

2

















5

bj

aj

11

11

11

11

16

6

bj

aj

8

9

13

8

12



15

17

20

29

26

25


9

5

15

3

6

10



15

3

4

5

15

24


11

23

8

13

27

12



15

19

2

22

4

13


14

30

1

5

24

25



15

20

27

1

17

19


16

8

26

7

28

9

















7

bj

aj

12

12

12

12

12

8

bj

aj

7

7

7

7

42


13

20

23

24

26

29


22

9

17

29

28

8


17

15

20

29

26

23


13

13

21

27

16

29


17

4

10

27

30

7


17

20

30

24

7

26


13

9

16

29

20

3


18

11

19

30

6

2















9

bj

aj

8

8

8

8

28

10

bj

aj

6

6

13

20

15


18

21

22

2

13

7


16

30

2

5

6

15


12

27

10

4

24

9


15

5

29

9

5

7


17

3

16

25

5

4


14

16

24

14

6

26


13

28

11

17

10

29


15

13

28

4

25

8