Добавил:
Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:
СППР.doc
Скачиваний:
15
Добавлен:
04.03.2016
Размер:
468.99 Кб
Скачать

Перелік контрольних запитань:

  1. Для чого призначений засіб MS Excel “Підбір параметра”?

  2. Для чого призначений засіб MS Excel “Таблиці підстановки”?

  3. Які можливості дають засоби “Підбір параметра” та “Таблиці підстановки” для СППР.

  4. Якими командами викликаються дані засоби?

  5. Як розраховується розмір суми виплат по депозитним вкладам?

  6. Чи можна призупинити виконання задачі засобом MS Excel “Підбір параметра”, а потім знов відновити?

  7. Де повинні розташовуватися формули для таблиці підстановки?

  8. Як повинні бути розташовані вихідні дані для задачі, вирішуваної методом “Таблиць підстановки”?

Практичне заняття №3

Тема: Підтримка процесу прийняття рішень засобами MS Excel. Робота із засобами “Пошук розв’язку”, “Диспетчер сценаріїв”.

Мета: вивчення можливостей засобів “Пошук розв’язку”, “Диспетчер сценаріїв” у MS Excel для моделювання СППР.

Хід роботи:

  1. Вивчити рекомендації для користування інструментами аналізу даних MS Excel.

  2. Проаналізувати можливості їх застосування як засобів моделювання у складі СППР.

  3. Виконати завдання згідно з варіантом (табл. 2, 3).

  4. Оформити звіт із лабораторної роботи, який включає висновки щодо можливостей використання засобів аналізу даних MS Excel у СППР, опис прикладів, ілюстративний матеріал.

Рекомендації з використання засобу аналізу даних ms Excel “Пошук розв’язку”

Процедура пошуку розв’язку дозволяє за заданим значенням критерію оптимізації знайти множину значень змінних, що задовольняють зазначеним обмеженням. Результати оптимізації можуть бути оформлені у вигляді звітів трьох типів.

Для виклику процедури пошуку розв’язку необхідно вибрати команду Сервис/Поиск решения після чого відкриється діалогове вікно Поиск решения” (рис. 1):

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

Елементи діалогового вікна “Поиск решения”:

  1. Установить целевую ячейку – служить для вказівки цільової комірки, значення якої необхідно максимізувати, мінімізувати або встановити рівним заданому числу. Ця комірка повинна містити формулу.

  2. Равной – служить для вибору варіанту оптимізації значення цільової комірки (максимізація, мінімізація або підбір заданого числа). Щоб встановити число, введіть його в поле.

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

  4. Ограничения – служить для відображення списку поточних обмежень для поставленого завдання. Добавить – служить для відображення діалогового вікна Добавить ограничение. Изменить – служить для відображення діалогового вікна Изменить ограничение. Удалить – служить для зняття вказаного обмеження. Выполнить – служить для запуску пошуку рішення поставленої задачі. Закрыть – служить для виходу з діалогового вікна без рішення задачі. При цьому зберігаються зміни, виконані з використанням кнопок Параметры, Добавить, Изменить, або Удалить.

  5. Параметры – служить для відображення діалогового вікна Параметры поиска решения, в якому можна завантажити або зберегти модель, що оптимізується та вказати передбачені варіанти пошуку рішення.

  6. Восстановить – служить для очищення полів вікна діалогу та відновлення значень параметрів пошуку рішення, використовуваних за умовчанням.

Завдання 1. Деяке підприємство виробляє чотири види продукції А, В, С, D, використовуючи для цього три види ресурсів.

Таблиця 1.

Норми витрат ресурсів на виробництво

Ресурси

Вид продукції

Об’єм ресурсів

А

В

С

D

Сировина, кг

Робоча сила, год.

Обладнання, год.

8

36

12

6

12

13

4

15

17

2

25

15

70

420

245

Прибуток на один. товару, у.о.

35

30

48

46

Норми витрат ресурсів на виробництво кожного виду продукції (в умовних одиницях) наведено в таблиці 1.

Який вид продукції та скільки її одиниць потрібно випускати, щоб прибуток був максимальним?

Умова завдання подана у таблиці 2. Наступні показники залишаються незмінні:

  • Прибуток на один. товару, у. о.;

  • Об’єм ресурсів.

Варіант вибирається згідно номера студента по списку в журналі.

Таблиця 2.

Варіанти завдань

№ варіанту

Ресурси

Види продукції

A

B

C

D

1

Сировина, кг

13

11

9

7

Робоча сила, год

41

17

20

30

Обладнання, год

17

18

22

20

2

Сировина, кг

18

16

14

12

Робоча сила, год

46

22

25

35

Обладнання, год

22

23

27

25

3

Сировина, кг

23

21

19

17

Робоча сила, год

51

27

30

40

Обладнання, год

27

28

32

30

4

Сировина, кг

28

26

24

22

Робоча сила, год

56

32

35

45

Обладнання, год

32

33

37

35

5

Сировина, кг

33

31

29

27

Робоча сила, год

61

37

40

50

Обладнання, год

37

38

42

40

6

Сировина, кг

38

36

34

32

Робоча сила, год

66

42

45

55

Обладнання, год

42

43

47

45

7

Сировина, кг

43

41

39

37

Робоча сила, год

71

47

50

60

Обладнання, год

47

48

52

50

8

Сировина, кг

48

46

44

42

Робоча сила, год

76

52

55

65

Обладнання, год

52

53

57

55

9

Сировина, кг

53

51

49

47

Робоча сила, год

81

57

60

70

Обладнання, год

57

58

62

60

10

Сировина, кг

58

56

54

52

Робоча сила, год

86

62

65

75

Обладнання, год

62

63

67

65

11

Сировина, кг

63

61

59

57

Робоча сила, год

91

67

70

80

Обладнання, год

67

68

72

70

12

Сировина, кг

68

66

64

62

Робоча сила, год

96

72

75

85

Обладнання, год

72

73

77

75

13

Сировина, кг

73

71

69

67

Робоча сила, год

101

77

80

90

Обладнання, год

77

78

82

80

14

Сировина, кг

78

76

74

72

Робоча сила, год

106

82

85

95

Обладнання, год

82

83

87

85

15

Сировина, кг

83

81

79

77

Робоча сила, год

111

87

90

100

Обладнання, год

87

88

92

90

16

Сировина, кг

88

86

84

82

Робоча сила, год

116

92

95

105

Обладнання, год

92

93

97

95

17

Сировина, кг

93

91

89

87

Робоча сила, год

121

97

100

110

Обладнання, год

97

98

102

100

18

Сировина, кг

98

96

94

92

Робоча сила, год

126

102

105

115

Обладнання, год

102

103

107

105

19

Сировина, кг

103

101

99

97

Робоча сила, год

131

107

110

120

Обладнання, год

107

108

112

110

20

Сировина, кг

108

106

104

102

Робоча сила, год

136

112

115

125

Обладнання, год

112

113

117

115

Приклад розв’язання. Позначимо:

Х1 – кількість виробленої продукції А;

Х2 – кількість виробленої продукції В;

Х3 – кількість виробленої продукції С;

Х4 – кількість виробленої продукції D;

F – цільова функція, величина максимального прибутку.

Побудуємо математичну модель задачі (1):

F = 35 Х1 + 30 Х2 + 48 Х3 + 46 Х4

(1)

Хід розв’язання задачі в MS Excel:

  1. На робочому аркуші створити таблицю як зображено на рис. 2.

У комірки В9 – В12 записуємо:

  • В9: =B3*B$8+C3*C$8+D3*D$8+E3*E$8;

  • В10: =B4*B$8+C4*C$8+D4*D$8+E4*E$8;

  • В11: =B5*B$8+C5*C$8+D5*D$8+E5*E$8;

  • В12: =B6*B8+C6*C8+D6*D8+E6*E8.

Дані формули відповідають математичній моделі задачі (1).

  1. виконуємо команду Сервис/Поиск решения та заповняємо діалогове вікно Поиск решения як показано на рис 3.

Рис 2. Таблиця для розрахунків в MS Excel.

Рис. 3. Приклад діалогового вікна Поиск решения”.

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

  1. Після виконання даних кроків таблиця на робочому аркуші MS Excel буду мати вигляд як показано на рис. 4:

Рис 4. Таблиця в MS Excel після виконання розрахунків.

Як видно із рис. 3 максимальний прибуток складає 751,33 грн. при умові, що підприємство виробить 17 одиниць продукції виду D.