Меню
Главная
Авторизация/Регистрация
 
Главная arrow Информатика arrow Системи підтримки прийняття рішень

Підтримка процесу прийняття рішень засобами 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

Хід розв'язання задачі в 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).

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

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

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

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

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

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

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

Рекомендації з використання засобу аналізу даних MS Excel “Диспетчер сценаріїв”.

Диспетчер сценаріїв - це засіб Excel, що може бути використаний для підтримки прийняття рішень у складних ситуаціях, які потребують аналізу типу “що…, якщо?”. Даний засіб дозволяє створювати кілька варіантів розв'язку однієї задачі та допускає використання до 32-х параметрів.

Вцілому, сценарій - це набір значень, що у програмі Microsoft Office Excel зберігаються і можуть автоматично підставлятися у робочий аркуш. Сценарії можна використовувати для прогнозу результатів моделей, виконаних розрахунків на аркуші. Існує можливість створити і зберегти на одному аркуші різні групи значень, а потім перемикатися на будь-який з цих нових сценаріїв, щоб проглядати різні результати.

Створення сценарію:

1. Виберіть команду Сценарии в меню Сервис.

2. Натисніть кнопку Добавить.

3. Введіть в поле Название сценария назву сценарію.

4. У полі Изменяемые ячейки введіть посилання на комірки, які потрібно змінити.

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

5. Встановите необхідні прапорці в наборі прапорців Защита.

6. Натисніть кнопку OK.

7. Введіть необхідні значення в діалоговому вікні Значения ячеек сценария.

8. Щоб створити сценарій, натисніть кнопку OK.

9. Якщо потрібно створити додаткові сценарії, знову натисніть кнопку Добавить, а потім повторіть цю процедуру. Після завершення створення сценаріїв натисніть кнопку OK, а потім - кнопку Закрыть в діалоговому вікні Диспетчер сценариев.

Правка сценарію.

Якщо після внесення змін до сценарію він буде збережений з первинним ім'ям, нові значення змінних осередків замінять значення в початковому сценарії.

1. Виберіть команду Сценарии в меню Сервис.

2. Виберіть назву змінного сценарію, а потім натисніть кнопку Изменить.

3. Внесіть необхідні зміни.

4. Введіть необхідні значення в діалоговому вікні Значения ячеек сценария.

5. Виконайте одну з наступних дій:

§ щоб зберегти зміни, натисніть кнопку OK;

§ щоб повернутися в діалогове вікно Диспетчер сценариев без зміни поточного сценарію, натисніть кнопку Отмена.

Завдання 2. За допомогою програми MS Excel розрахувати індекс припливу в країну прямих іноземних інвестицій. Із використанням засобу MS Excel “Диспетчер сценаріїв”виконати розрахунок індексу за кількома сценаріями. Оформити результати у вигляді звіту на робочому аркуші MS Excel та вибрати оптимальний варіант.

Дано (млрд. у. о.):

§ Валовий внутрішній продукт країни - 15,71;

§ Валовий світовий продукт - 12556,69;

§ Обсяги інвестицій у країну - 0,48;

§ Обсяги інвестицій по всім країнам - 364,28.

Додатково виконати розрахунок ще за 5 сценаріями, крім тих, що наведені у прикладі розв'язання.

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

Таблиця 3 Варіанти завдань

№ варіанту

Валовий внутрішній продукт країни

Валовий світовий продукт

Обсяги інвестицій у країну

Обсяги інвестицій по всім країнам

1

26,8

37689,23

0,97

245,35

2

27,3

37928,45

1,03

256,47

3

27,8

38167,67

1,09

267,59

4

28,3

38406,89

1,15

278,71

5

28,8

38646,11

1,21

289,83

6

29,3

38885,33

1,27

300,95

7

29,8

39124,55

1,33

312,07

8

30,3

39363,77

1,39

323,19

9

30,8

39602,99

1,45

334,31

10

31,3

39842,21

1,51

345,43

11

31,8

40081,43

1,57

356,55

12

32,3

40320,65

1,63

367,67

13

32,8

40559,87

1,69

378,79

14

33,3

40799,09

1,75

389,91

15

33,8

41038,31

1,81

401,03

16

34,3

41277,53

1,87

412,15

17

34,8

41516,75

1,93

423,27

18

35,3

41755,97

1,99

434,39

19

35,8

41995,19

2,05

445,51

20

36,3

42234,41

2,11

456,63

Приклад розв'язання. Індекс припливу в країну прямих іноземних інвестицій розраховується за такою формулою (1):

INV = (Ii / Iw) / (Gi / Gw)

Де G - сукупний світовий продукт країни (і) та всього світу (w); I - приплив інвестицій у країну (і) та в усі країни світу (w). INV - індекс припливу в країну прямих іноземних інвестицій.

Даний показник характеризує відносні успіхи країни в залученні інвестицій, однак не вимірює фактори, які є причинами цього.

Якщо індекс припливу в країну інвестицій більше за 1, це означає, що вона отримує прямих іноземних інвестицій більше ніж можна було передбачати на базі порівняння вищезгаданих коефіцієнтів. Така країна має додаткові інші переваги для транснаціональних компаній (ТНК), наприклад, більш ліберальний порівняно з іншими країнами, режим для іноземних інвесторів.

В MS Excel потрібно створити таблицю наступного вигляду (рис. 5):

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

У комірку С7 вводимо формулу =(C5/C6)/(C3/C4). Далі виділяємо комірки С3:С6 і виконуємо команду команду Сценарии в меню Сервис. У вікні Диспетчер сценариев натискаємо кнопку Добавить. У вікні Добавление сценария в полі Название сценария вводимо назву сценарію, наприклад “Сценарій1” та натискаємо кнопку ОК. Діалогове вікно Значения ячеек сценария заповняємо як показано на рис 6:

Рис. 6. Заповнення вікна Значения ячеек сценария (“Сценарій1”).

Після натискання кнопки ОК повертаємося у вікно Диспетчер сценариев. Аналогічно створюємо “Сценарій2” та “Сценарій3” (рис. 7, рис. 8):

Рис. 7. Заповнення вікна Значения ячеек сценария (“Сценарій2”).

Рис. 8. Заповнення вікна Значения ячеек сценария (“Сценарій3”).

Після виконання даних дій повертаємося у вікно Диспетчер сценариев та натискаємо кнопку Отчет. У вікні, що з'явилося, у полі Тип отчета вибираємо Сводная таблица, а у полі Ячейки результата записуємо C7:C8. Після натискання кнопки ОК у робочій книзі Excel створюється додатковий лист із назвою Сводная таблица по сценарию. На цьому аркуші розміщені в одну колонку назви виконаних сценаріїв, а в іншу - результати їх виконання, тобто отримуємо таблицю 4:

Таблиця Результат роботи засобу MS Excel “Диспетчер сценаріїв”

Сценарій1

5,781478401

Сценарій2

2,192068084

Сценарій3

3,067210106

Як видно із таблиці 4, Сценарій1 видає найбільше значення (5,78). Цей сценарій і є оптимальним.

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

1. Дайте визначення поняттю сценарій.

2. Для чого призначений засіб MS Excel “Пошук розв'язку”?

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

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

5. Які варіанти звіту можуть бути використані у Диспетчері сценаріїв? Чим вони відрізняються?

6. По закінченню роботи Диспетчер сценаріїв виводить:

· список усіх варіантів;

· лише оптимальний варіант?

7. Скільки параметрів можливо використовувати піл час роботи засобу MS Excel “Диспетчер сценаріїв”?

 
Если Вы заметили ошибку в тексте выделите слово и нажмите Shift + Enter
< Предыдущая   СОДЕРЖАНИЕ   Следующая >
 
Предметы
Агропромышленность
Банковское дело
БЖД
Бухучет и аудит
География
Документоведение
Естествознание
Журналистика
Информатика
История
Культурология
Литература
Логика
Логистика
Маркетинг
Математика, химия, физика
Медицина
Менеджмент
Недвижимость
Педагогика
Политология
Право
Психология
Религиоведение
Социология
Статистика
Страховое дело
Техника
Товароведение
Туризм
Философия
Финансы
Экология
Экономика
Этика и эстетика
Прочее