Частина 2

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

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

Завдання:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

· Валовий внутрішній продукт країни – 27,3;

· Валовий світовий продукт – 37928,45;

· Обсяги інвестицій у країну – 1,03;

· Обсяги інвестицій по всім країнам – 256,47.

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

1. В MS Excel створив таблицю:

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

Сценарій (варіант 22)

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

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

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

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

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

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