Лекция 10 Аналіз даних в Excel: пошук рішення, підбір параметра,  консолідація даних, сценарії.

План

1. Засоби аналізу "what-if"

• Сценарій 

• Таблиці даних

• Підбір параметра

        2. Надбудова Пошук рішення 

        3. Консолідація даних

В Excel передбачено засоби аналізу "what-if" ("якщо") трьох видів: сценарії, таблиці даних і підбір параметра. Сценарії та таблиці даних прогнозують можливі результати на основі наборів вхідних значень. Підбір параметра працює навпаки: прогнозує вхідні значення, які могли дати певний результат.

Сценарій – це набір значень, що зберігаються в Excel і можуть автоматично заміняти один одного на аркуші. Ви можете створити різні групи значень, зберегти їх як сценарії та переключатися між ними, щоб переглянути різні результати. Якщо в сценаріях потрібно використовувати певні відомості від кількох людей, ви можете зібрати ці відомості в окремих книгах, а потім об’єднати сценарії з різних книг в один. Маючи всі потрібні сценарії, ви можете створити звіт за сценарієм, який містить відомості з усіх сценаріїв. Сценаріями можна керувати за допомогою майстра Диспетчер сценаріїв (вкладка Дані, група Аналіз «якщо»).Кожен сценарій може включати до 32 змінних значень. Якщо потрібно проаналізувати понад 32 значення, які представляють лише одну-дві змінні, можна скористатися таблицями даних. Хоча в таблиці даних можна використати лише одну-дві змінні (одна для клітинки введення рядків і одна для клітинки введення стовпців), зате можна додати скільки завгодно різних змінних значень. Сценарій може мати не більше 32 різних значень, але ви можете створити безліч сценаріїв.

Приклад використання у відео:

Таблиці даних

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

Таблиці даних з однією змінною    

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

На наведеному нижче рисунку, у клітинці D2 міститься формула оплати, = PMT (B3/12, B4,-B5), що посилається на клітинку з вхідними полями B3.

Таблиці даних із двома змінними    

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

На наведеному нижче рисунку клітинка C2 містить формулу виплат, = PMT (B3/12, B4,-B5), у якому використовуються дві клітинки для входу, B3 і B4.

 Обчислення таблиці даних    

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

Приклад використання у відео:

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

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

Розгляньмо попередній приклад, крок за кроком.

Оскільки потрібно обчислити відсоткова ставка кредиту, необхідні для мети, використовується функція PMT. Функція PMT обчислює щомісячний сума платежу. У цьому прикладі щомісячний плати – це завдання, яке ви шукаєте.

Підготовка аркуша

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

1. У клітинку A1 введіть Сума позики.

2. У клітинку A2 введіть Строк у місяцях.

3. У клітинку A3 введіть Відсоткова ставка.

4. У клітинку A4 введіть Платіж.

5. Потім додайте відомі значення.

6. У клітинку B1 введіть 100000. Це сума, яку потрібно позичити.

7. У клітинку B2 введіть 180. Це кількість місяців, протягом яких потрібно повернути гроші.

Примітка.: Хоча сума платежу вам відома, вводити її не потрібно, адже вона є результатом формули. Натомість, використовуючи функцію "Підбір параметра", пізніше слід ввести формулу та зазначити суму платежу.

Потім додайте формулу, результат якої вам уже відомий. Ми скористаємося функцією PMT.

У клітинку B4 введіть =PMT(B3/12;B2;B1). 

Формула обчисляє суму платежу. У цьому прикладі ви плануєте сплачувати 900 гривень щомісяця. Не потрібно вводити тут цю суму, тому що ви маєте визначити відсоткову ставку за допомогою функції "Підбір параметра", а для цього потрібно почати з формули.

Формула містить посилання на клітинки B1 і B2, в яких зберігаються введені раніше значення. Вона також містить посилання на клітинку B3, у якій функція "Підбір параметра" виведе відсоткову ставку. Значення клітинки B3 потрібно розділити на 12, тому що ви вказали щомісячний платіж, а функція PMT припускає щорічну відсоткову ставку.

Оскільки в клітинці B3 немає значення, у програмі Excel робиться припущення, що відсоткова ставка становить 0 %, і на основі цих значень повертається результат 555,56₴. Наразі цей результат можна ігнорувати.

Визначення відсоткової ставки за допомогою функції "Підбір параметра"

1. На вкладці Дані у групі Знаряддя даних натисніть кнопку Аналіз "якщо" та виберіть пункт Підбір параметра.

2. У поле Установити у клітинці введіть посилання на клітинку з формулою, яку потрібно вирішити. У нашому прикладі це клітинка B4.

3. У поле Значення введіть потрібний результат формули. У нашому прикладі це –900. Зверніть увагу: число від’ємне, адже вказує на виплату.

4. У поле Змінюючи значення клітинки введіть посилання на клітинку зі значенням, яке потрібно скоригувати. У нашому прикладі це клітинка B3.

Примітка.: Формула у клітинці, зазначеній у полі Установити у клітинці, має містити посилання на клітинку, значення якої змінює функція "Підбір параметра".

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

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

Нарешті, відформатуйте цільову клітинку (B3), щоб представити результат у вигляді відсотків.

На вкладці Основне у групі Число натисніть кнопку Відсотковий формат.

Натисніть кнопку Збільшити розрядність або Зменшити розрядність, щоб настроїти кількість десяткових розрядів.

Приклад використання у відео:

На додачу до цих трьох інструментів, можна інсталювати надбудови, які допомагають виконувати аналіз "what-if", наприклад надбудову "Пошук розв’язання". Ця надбудова схожа на підбір параметра, але може включати більше змінних. Прогнози також можна створити за допомогою маркера заповнення й різноманітних команд, вбудованих у програму Excel. Для складніших моделей можна скористатися надбудовою "Пакет аналізу".

Перегляньте відео про надбудову "Пошук рішення " і запишіть як її використовувати

Консолідація даних

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

1. У новій книзі, яка міститиме консолідовані дані встановити курсор в комірку А1 і на вкладці Дані в групі Знаряддя даних вибрати команду Консолідація. 

2. У діалоговому вікні, що відкрилося, Консолідація зі списку Функція вибрати вид обчислень, які повинні проводитися з даними 

3. Встановивши курсор в полі Посилання на вкладці Огляд за допомогою кнопки Перейти в інше вікно  відкрити по черзі необхідні файли і виділити в них діапазон даних (разом із шапкою таблиць), які консолідуватимемо. 

4.  У полі Посилання з'явиться посилання на файл і діапазон комірок. Натиснути кнопку Додати, для додавання цього діапазону комірок в Список діапазонів. 

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

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

7. Після вказівки усіх даних натиснути кнопку ОК. 

Приклад використання у відео:

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