Лабораторна робота 7 на тему Підтримка процесу прийняття рішень засобами MS Excel
« Назад
ЛАБОРАТОРНА РОБОТА № 7Тема: Підтримка процесу прийняття рішень засобами MS Excel. Робота із засобами “Підбір параметра” та “Таблиці підстановки” Мета: вивчення можливостей засобів “Підбір параметра” та “Таблиці підстановки” у MS Excel як елементів моделювання СППР.
Хід роботи
Рекомендації з використання засобу аналізу даних MS Excel “Підбір параметра”За допомогою функції підбору параметра MS Excel здійснює пошук такого значення параметра формули, яке приводить до бажаного значення результату розрахунку за формулою. Під час підбору параметра MS Excel змінює значення у вказаній комірці доти, доки вміст потрібної комірки не набере бажаного значення. Підбір параметра використовується тоді, коли потрібно знайти значення комірки через зміну значення лише однієї іншої комірки. Для виконання підбору параметра потрібно виконати такі дії:
Рекомендації з використання засобу аналізу даних MS Excel “Таблиці підстановки”Із використанням засобу аналізу даних MS Excel “Таблиці підстановки” можна оцінити вплив кількох параметрів на деяку величину. Створення таблиці підстановки здійснюється за допомогою команди Данные/Таблица подстановки. Перед викликом цієї команди у комірку робочого аркуша слід ввести формулу, що відображає досліджувану залежність. Якщо створюється таблиця для однієї змінної, то формула повинна включати посилання на одну комірку таблиці, у яку під час формування таблиці підстановки будуть підставлятися значення зі списку. Відповідно у разі створення таблиці підстановки для двох змінних початкова формула має включати посилання на дві комірки. Комірки з аргументами формули розташовуються в робочому аркуші поза межами зони таблиці підстановки. Вхідні дані для таблиці підстановки мають бути подані у вигляді списку. Для таблиці підстановки з одним параметром вхідні дані можуть розміщуватись або в рядку, або у стовпці робочого аркуша. Для таблиці підстановки з двома параметрами значення одного з них розміщуються у стовпці, значення іншого — у рядку, а результати розрахунку — на перетині відповідних рядків та стовпців. Формули для таблиці підстановки з одним параметром мають розташовуватись у першому рядку (стовпці) таблиці підстановки, оскільки значення списку, розміщеного у стовпці (рядку) можуть бути зчитані тільки в напрямі зростання номерів рядків (стовпців). У разі створення таблиці підстановки з двома параметрами формула розташовується на перетині стовпця та рядка із вхідними даними. Під час створення таблиці підстановки необхідно виконати такі дії:
Завдання 1. Розрахувати розмір депозиту при відомому терміні вкладу, процентній ставці та сумі виплат по закінченню терміна вкладу. Умова завдання подана у таблиці 1. Варіант вибирається згідно номера студента по списку в журналі. Таблиця 1 Варіанти завдань
Приклад розв’язання. В MS Excel потрібно створити таблицю наступного вигляду (рис. 1). У комірці С6 записуємо формулу =C3*(1+C5/100)^C4. Цей запис еквівалентний формулі (1) для розрахунку суми виплат по депозитним вкладам: де: S – суми виплат по депозитним вкладам після n років; P – розмір депозиту; i – процентна ставка. Далі потрібно викликати команду Сервис/Подбор параметра і заповнити діалогове вікно як показано на рис. 2. Після натискання ОК у зазначеному вікні, в комірку Розмір депозиту буде вставлене значення 1066,02. Це і буде сума депозиту в грн. Завдання 2. Із використанням засобу аналізу даних MS Excel “Таблиці підстановки” оцінити вплив наступних параметрів на суму виплат по закінченню терміна вкладу: 1) зміна терміни вкладу; 2) зміна процентної ставки. Розмір депозиту незмінний – 1066,02 грн. Умова завдання подана у таблиці 1. Варіант вибирається згідно номера студента по списку в журналі. Приклад розв’язання. Для розв’язання поставленої задачі потрібно виконати такі дії:
Таблиця 2 Результати роботи засобу “Таблиці підстановки” MS Excel
Перелік контрольних запитань:
ЛАБОРАТОРНА РОБОТА № 8Тема: Підтримка процесу прийняття рішень засобами MS Excel. Робота із засобами “Пошук розв’язку”, “Диспетчер сценаріїв” Мета: вивчення можливостей засобів “Пошук розв’язку”, “Диспетчер сценаріїв” у MS Excel для моделювання СППР.
Хід роботи:
Рекомендації з використання засобу аналізу даних MS Excel “Пошук розв’язку”Процедура пошуку розв’язку дозволяє за заданим значенням критерію оптимізації знайти множину значень змінних, що задовольняють зазначеним обмеженням. Результати оптимізації можуть бути оформлені у вигляді звітів трьох типів. Для виклику процедури пошуку розв’язку необхідно вибрати команду Сервис/Поиск решения після чого відкриється діалогове вікно “Поиск решения” (рис. 1). Елементи діалогового вікна “Поиск решения”:
Завдання 1. Деяке підприємство виробляє чотири види продукції А, В, С, D, використовуючи для цього три види ресурсів. Таблиця 1 Норми витрат ресурсів на виробництво
Норми витрат ресурсів на виробництво кожного виду продукції (в умовних одиницях) наведено в таблиці 1. Який вид продукції та скільки її одиниць потрібно випускати, щоб прибуток був максимальним? Умова завдання подана у таблиці 2. Наступні показники залишаються незмінні:
Варіант вибирається згідно номера студента по списку в журналі. Таблиця 2 Варіанти завдань
Приклад розв’язання. Позначимо: Х1 – кількість виробленої продукції А; Х2 – кількість виробленої продукції В; Х3 – кількість виробленої продукції С; Х4 – кількість виробленої продукції D; F – цільова функція, величина максимального прибутку. Побудуємо математичну модель задачі (1): F = 35 Х1 + 30 Х2 + 48 Х3 + 46 Х4 Хід розв’язання задачі в MS ExcelНа робочому аркуші створити таблицю як зображено на рис. 2. У комірки В9 – В12 записуємо:
Дані формули відповідають математичній моделі задачі (1). Виконуємо команду Сервис/Поиск решения та заповняємо діалогове вікно “Поиск решения” як показано на рис 3. Для виконання розрахунку натиснути кнопку Выполнить діалогового вікна Поиск решения. Після завершення розрахунку одержані значення будуть вставлені в таблицю, і відкриється вікно з повідомленням про завершення пошуку розв'язку. Для оновлення таблиці слід встановити в цьому вікні перемикач Сохранить найденное решение. Для створення звіту з результатами розрахунку належить вибрати тип звіту у відповідному полі. Після виконання даних кроків таблиця на робочому аркуші MS Excel буду мати вигляд як показано на рис. 4: Як видно із рис. 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. Виконайте одну з наступних дій:
Завдання 2. За допомогою програми MS Excel розрахувати індекс припливу в країну прямих іноземних інвестицій. Із використанням засобу MS Excel “Диспетчер сценаріїв”виконати розрахунок індексу за кількома сценаріями. Оформити результати у вигляді звіту на робочому аркуші MS Excel та вибрати оптимальний варіант. Дано (млрд. у. о.):
Додатково виконати розрахунок ще за 5 сценаріями, крім тих, що наведені у прикладі розв’язання. Умова завдання подана у таблиці 3. Варіант вибирається згідно номера студента по списку в журналі. Таблиця 3 Варіанти завдань
Приклад розв’язання. Індекс припливу в країну прямих іноземних інвестицій розраховується за такою формулою (1): INV = (Ii / Iw) / (Gi / Gw) (1) Де G – сукупний світовий продукт країни (і) та всього світу (w); I – приплив інвестицій у країну (і) та в усі країни світу (w). INV – індекс припливу в країну прямих іноземних інвестицій. Даний показник характеризує відносні успіхи країни в залученні інвестицій, однак не вимірює фактори, які є причинами цього. Якщо індекс припливу в країну інвестицій більше за 1, це означає, що вона отримує прямих іноземних інвестицій більше ніж можна було передбачати на базі порівняння вищезгаданих коефіцієнтів. Така країна має додаткові інші переваги для транснаціональних компаній (ТНК), наприклад, більш ліберальний порівняно з іншими країнами, режим для іноземних інвесторів. В MS Excel потрібно створити таблицю наступного вигляду (рис. 5): У комірку С7 вводимо формулу =(C5/C6)/(C3/C4). Далі виділяємо комірки С3:С6 і виконуємо команду команду Сценарии в меню Сервис. У вікні Диспетчер сценариев натискаємо кнопку Добавить. У вікні Добавление сценария в полі Название сценария вводимо назву сценарію, наприклад “Сценарій1” та натискаємо кнопку ОК. Діалогове вікно Значения ячеек сценария заповняємо як показано на рис 6: Після натискання кнопки ОК повертаємося у вікно Диспетчер сценариев. Аналогічно створюємо “Сценарій2” та “Сценарій3” (рис. 7, рис. 8): Після виконання даних дій повертаємося у вікно Диспетчер сценариев та натискаємо кнопку Отчет. У вікні, що з’явилося, у полі Тип отчета вибираємо Сводная таблица, а у полі Ячейки результата записуємо C7:C8. Після натискання кнопки ОК у робочій книзі Excel створюється додатковий лист із назвою Сводная таблица по сценарию. На цьому аркуші розміщені в одну колонку назви виконаних сценаріїв, а в іншу – результати їх виконання, тобто отримуємо таблицю 4: Таблиця Результат роботи засобу MS Excel “Диспетчер сценаріїв”
Як видно із таблиці 4, Сценарій1 видає найбільше значення (5,78). Цей сценарій і є оптимальним.
Перелік контрольних запитань
З повагою ІЦ "KURSOVIKS"! |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||