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

 

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

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

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

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

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

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

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

F = 35 Х₁ + 30 Х₂ + 48 Х₃ + 46 Х₄  (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).

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):

INV = (I_i / I_w) / (G_i / G_w) (1)

Де 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 “Диспетчер сценаріїв”

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

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

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

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

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

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

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

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

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

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

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