Лабораторна робота № 4. Microsoft EXCEL 97. Аналіз даних, їх прогнозування та побудова сценаріїв.

 

Microsoft EXCEL 97. Аналіз даних, їх прогнозування та побудова сценаріїв.

Мета роботи: ознайомитись з деякими методами аналізу та прогнозування даних у середовище MS Excel.

 

І. Теоретична частина.

При відповіді на питання скористатися матеріалом лекцій та відомостями даної лабораторної роботи.

4. Використання таблиць підставлення.

5. Призначення метода підбору параметрів.

6. Прогнозування за допомогою сценаріїв.

 

ІІ. Практична частина.

 

1. Розв’язування завдань за допомогою таблиць підставлення.

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

 

Приклад використання таблиці підставлення.

Припустимо, що менеджер вирішив придбати оптом 1000 кг рису за 2000 гривень. Постає питання: чи вигідна йому буде ця покупка, якщо він в роздріб продасть по 2,50 за кілограм. Тобто, треба розрахувати прибуток на всю партію товару і підібрати такий варіант змінних (сума закупівлі і ціна продажу за один кг), при яких менеджера влаштує величина прибутку.

 

Проведемо підготовчу роботу для розв’язання задачі:

1. Величину прибутку згідно умови можна розрахувати за формулою:

Прибуток = Ціна продажу за 1 кг * Кількість продукції - Сума закупівлі.

2. Занесемо назви змінних в таблицю:

A1 – Ціна продажу за 1 кг, B1 – Кількість продукції, C1 – Сума закупівлі, D1 – Прибуток.

3. Занесемо значення змінних у відповідні чарунки:

A2 – 2,3 B2 – 1000 C2 – 2000

4. Занесемо формулу в чарунку D2:

=A2*B2-C2

 

У таблиці підставлення з двома змінними формула для визначення значень повинна знаходитися на перетині списків значень (у нашому випадку D2).

 

Розв’язування задачі:

1. Задамо в матричній формі формулу (цільову функцію), яка залежить від значень змінних A2 і C2: тобто в першій колонці матриці під формулою (починаючи з чарунки D3) розташуємо серію значень A2 в сторону зменшення і збільшення їх, наприклад - 2,00; 2,25; 2,50; 2,75 і 3,00.

2. Задамо серію значень для змінної C2 справа від місцезнаходження цільової функції (починаючи з чарунки E2), хай це будуть значення: 1800, 2000, 2200, 2400.

3. Виділимо область таблиці з матрицею, куди входять: чарунка з цільовою функцією і значення для підставлення в змінні A2 і C2.

4. Виконаємо команду: Данные / Таблица подстановки …

5. Заповнимо параметри у діалоговому вікні, що з’явиться:

У поле Подставлять значения по столбцам в вкажіть адресу чарунки C2.

У поле Подставлять значения по строкам в вкажіть адресу чарунки A2.

6. Натисніть кнопку ОК. На екрані з'явиться таблиця підставлення з результатами.

7. Проаналізуйте отримані результати.

8. Відформатуйте таблицю за своїм розсудом.

 

2. Розв’язування рівнянь за допомогою методу підбору параметра.

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

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

Припустимо, що менеджер хоче одержати прибуток рівний 1000 грн. Необхідно визначити яка повинна бути роздрібна ціна за 1 кг рису при постійному значенні величин: розміру закупівлі (1000 кг) і суми закупівлі (2000 грн.)

 

Розв’язування задачі:

1. Установіть курсор в чарунку D2, відведену для результату.

2. Виконайте команду Сервис / Подбор параметра. На екрані з'явиться діалогове вікно Подбор параметра. Адреса виділеної чарунки автоматично буде вставлена у поле Установить в ячейке - в нашому випадку це адреса чарунки з цільовою функцією D2.

3. В поле Значение вкажіть цільове значення: 500.

4. Установіть курсор в поле Изменяя значения ячейки – A2.

5. Натисніть кнопку ОК.

6. Результат обчислення відобразиться в діалоговому вікні Результат подбора параметра.

7. Натисніть кнопку ОК діалоговому вікні Результат подбора параметра. Обчислене значення з’явиться у чарунці D3, де розташована цільова функція.

8. Проаналізуйте результати роботи функції Подбор параметра.

 

3. Прогнозування за допомогою сценаріїв.

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

Розв’язування задачі

3.1. Створення сценаріїв.

3.1.1. Виконайте команди Сервис / Сценарии. На екрані з'явиться вікно Диспетчер сценариев.

3.1.2. Клацніть на кнопці Добавить. На екрані з'явиться вікно Добавление сценария.

3.1.3. Вкажіть ім'я базового сценарію в поле Название сценария, наприклад Базовий, щоб у разі потреби завжди можна було повернутися до базового варіанта і переглянути його. У противному ж випадку існує ризик втратити основний сценарій, оскільки він не буде збережений автоматично після початку роботи з диспетчером сценаріїв.

3.1.4. В поле Изменяя ячейки вкажіть адреси всіх чарунок, що повинні змінюватися в даному сценарії, тобто A2:C2.

3.1.5. Клацніть на кнопці ОК.

З'явиться діалогове вікно Значення ячеек сценария. Кожна зі змінних чарунок показана в цьому вікні разом з її попереднім значенням. Для створення базового сценарію нічого не потрібно змінювати.

3.1.6. Клацніть на кнопці ОК,для збереження сценарію і повернення в діалогове вікно Диспетчер сценариев.

3.1.7. Описаним вище способом створіть ще один сценарій з ім'ям Кращий. У цьому сценарії значення змінних чарунок повинні бути збільшені на 25%. Змінювані чарунки залишаться колишніми (A2:C2).

У діалоговому вікні Значения ячеек сценария необхідно ввести для кожної змінної чарункинаступні формули:

Чарунка	Зміст
$A$2	=A2*1,25
$B$2	=B2*1,25
$C$2	=C2*1,25

 

Після натискання на кнопку ОК на екрані з'явиться повідомлення про те, що програма виконала зазначені обчислення.

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

3.1.8. Самостійно створіть сценарій з ім'ям Гірший,де значення змінних чарунок A2:C2 будуть менше значень цих же чарунок у базовому варіанті сценарію на 25%.

 

3.2. Аналіз даних за допомогою сценарію.

3.2.1. Виділіть ім'я сценарію у вікні Диспетчер сценариев.

3.2.2. Натисніть кнопку Вывести.

В результаті вихідні значення будуть вставлені в змінні чарунки в тому форматі, що заданий для цих чарунок.

3.2.3. Проаналізуйте отриманий результат.

3.2.4. Перегляньте всі створені нами сценарії.

3.2.5. Натисніть кнопку Отчет.

3.2.6. Виберіть команду Структура та вкажіть Ячейку результата,в нашому випадку – D2.

3.2.7. Натисніть кнопку ОК.

3.2.8. Проаналізуйте зведені дані.

3.3. Редагування сценаріїв.

3.3.1. Виконайте команди Сервис / Сценарии.

3.3.2. Виберіть у списку ім'я сценарію, що редагується, наприклад, Кращий.

3.3.3. Натисніть кнопку Изменить.

Відкриється діалогове вікно Изменение сценария.

3.3.4. Натисніть кнопку ОК.

З'явиться діалогове вікно Значения ячеек сценария. Кожна зі змінних чарунок показана в цьому вікні разом з її попереднім значенням. Збільшить попередні значення змінних чарунок (В2 та С2) на 100.

3.3.5. Клацніть на кнопці ОК.

3.3.6. Натисніть кнопку Вывести.

Проаналізуйте отриманий результат.

3.4. Друкування сценаріїв.

Роздрукувати сценарії можна так само, як звичайні робочі аркуші. Для цього:

3.4.1. Виберіть потрібні сценарії (стовпчик з назвою Текущие значения краще видалити).

3.4.2. Закрийте вікно Диспетчер сценариев.

3.4.3. Роздрукуйте робочий лист.

 

III. Індивідуальне завдання

 

6. Розв’язати задачу з використанням Таблиці підставлення.

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

Річна зарплата обчислюється по наступній формулі:

РЗ=К*СПМ*12, де:

РЗ - річна зарплата;

К – відсоток комісійних;

СПМ – суми продажів за місяць.

 

Розв’яжіть задачу відповідно до варіанта, заданого викладачем:

 

№ вар.	Зміна комісійних, % (крок)	Зміна суми продажів за місяць (крок)
	Від 1 до 4 (0,5)	Від 1000 до 2000 (100)
	Від 1 до 4 (0,3)	Від 1000 до 2000 (200)
	Від 1,5 до 4,5 (0,5)	Від 2000 до 3000 (100)
	Від 1,5 до 4,5 (0,3)	Від 2000 до 3000 (200)
	Від 2 до 5 (0,5)	Від 3000 до 4000 (100)
	Від 2 до 5 (0,3)	Від 3000 до 4000 (200)
	Від 2,5 до 5,5 (0,5)	Від 4000 до 5000 (100)
	Від 2,5 до 5,5 (0,3)	Від 4000 до 5000 (200)
	Від 3 до 6 (0,5)	Від 5000 до 6000 (100)
	Від 3 до 6 (0,3)	Від 5000 до 6000 (200)
	Від 3,5 до 6,5 (0,5)	Від 6000 до 7000 (100)
	Від 3,5 до 6,5 (0,3)	Від 6000 до 7000 (200)
	Від 4 до 7 (0,5)	Від 7000 до 8000 (100)
	Від 4 до 7 (0,3)	Від 7000 до 8000 (200)
	Від 4,5 до 7,5 (0,5)	Від 2500 до 3500 (100)
	Від 4,5 до 7,5 (0,3)	Від 2500 до 3500 (200)
	Від 5 до 8 (0,5)	Від 3500 до 4500 (100)
	Від 5 до 8 (0,3)	Від 3500 до 4500 (200)
	Від 5,5 до 8,5 (0,5)	Від 4500 до 5500 (100)
	Від 5,5 до 8,5 (0,3)	Від 4500 до 5500 (200)
	Від 6 до 10 (0,5)	Від 5500 до 6500 (100)
	Від 6 до 10 (0,25)	Від 5500 до 6500 (200)
	Від 6,5 до 10,5 (0,5)	Від 6500 до 7500 (100)
	Від 6,5 до 10,5 (0,25)	Від 6500 до 7500 (200)
	Від 7 до 11 (0,5)	Від 7500 до 8500 (100)
	Від 7 до 11 (0,25)	Від 7500 до 8500 (200)
	Від 7,5 до 11 (0,5)	Від 8500 до 9500 (100)
	Від 7,5 до 12 (0,25)	Від 8500 до 9500 (200)
	Від 0,5 до 3,5 (0,5)	Від 9500 до 10500 (100)
	Від 0,5 до 3,5 (0,3)	Від 9500 до 10500 (200)

 

2. Розв’язати рівняння з використанням програми: Подбор параметра.

Розв’яжіть алгебраїчне рівняння виду: , де

n_гр – номер академічної групи;

N - порядковий номер студента у списку групи.

Для досягнення більш високої точності необхідно виконати команди Сервис / Параметры, відкрити вкладку Расчеты іу поле Относительная погрешность ввести 0,0001.

 

3. Розв’язати задачу за допомогою сценаріїв.

3.1. Створіть наступну таблицю:

 

Найменування продукції	Кількість	Ціна	Вартість	Витрати	Прибуток
Товар1
Товар2
Товар3

 

3.2. Введіть у стовпці 1,2 і 3 дані свого варіанту, а у 5 значення за своїм розсудом.

3.3. Значення в стовпці 4 виходять шляхом множення відповідних значень зі стовпців 2 і 3.

3.4. Значення в стовпці 6 виходять шляхом віднімання значень вартості і витрат.

3.5. Визначить, як впливає збільшення значень у стовпці Ціна на значення Прибуток.

Для цього:

3.5.1. Створіть 2 сценарії: Базовий і Збільшений, де значення ціни будуть збільшені на 50 % для кожного виду товару в порівнянні з базовим варіантом.

3.5.2. Зробіть аналіз даних.

3.5.3. Створіть звіт по сценаріях.