Аналіз чутливості допомагає побачити, як зміниться результат розрахункової моделі, якщо підправити її вхідні значення — наприклад, ціну або кількість продажів. Це корисно для планів і прогнозів у бізнесі. У Excel це зручно робити через What-If Analysis, зокрема таблицями даних (Data Table): для одного параметра та для двох одразу.
Підготуйте модель
- Розмістіть кожен вхід моделі в окремій клітинці. Не «зашивайте» числа всередину формул.
- Створіть підсумкову формулу, що посилається на ці входи. Приклад (прибуток фудтрака в B6): =B2*(B3-B4)-B5.
- Переконайтесь, що всі посилання правильні: зміна будь-якого з B2:B5 має одразу впливати на результат у B6.
Однофакторний аналіз: таблиця даних по одному вводу
Коли потрібно подивитися, як змінюється підсумок при зміні лише одного параметра (наприклад, ціни), використовуйте однофакторну Data Table. Вона підставляє кожне значення зі списку у «клітинку вводу» моделі та зчитує результат.
- Підготуйте стовпець зі значеннями вводу для перевірки (наприклад, список цін у стовпці D).
- У клітинці, що на один рядок вище першого значення списку і на одну колонку праворуч від нього (наприклад, E2), введіть посилання на результат моделі: =B6.
- Виділіть прямокутник зі «шапкою результату» та списком значень (наприклад, D2:E9).
- На вкладці Data натисніть What-If Analysis → Data Table.
- У полі Column input cell вкажіть клітинку вводу моделі, яку тестуєте (наприклад, B3 — ціна). Поле Row input cell залиште порожнім.
- Натисніть OK.
Як це працює: Excel підставляє кожне значення зі стовпця в обрану «клітинку вводу» моделі (у прикладі — B3) і записує результат поруч. У рядку формул для будь-якого результату побачите масивну формулу на кшталт {=TABLE(,B3)}. Видаляти або редагувати окремі клітинки всередині такої таблиці не можна — вона працює як єдине ціле.
Двофакторний аналіз: таблиця даних по двох вводах
Щоб одночасно подивитися вплив двох змінних (наприклад, ціни та кількості продажів), використовуйте двофакторну Data Table: одні значення розміщуються зверху в рядку, інші — зліва у стовпці.
- Занесіть перший набір значень у верхній рядок (наприклад, кількість штук у E2:I2), а другий — у лівий стовпець (наприклад, ціни в D3:D9).
- У клітинці перетину «шапок» таблиці (кут) введіть посилання на результат моделі (наприклад, у D2 введіть =B6).
- Виділіть увесь прямокутник, включно з кутовою клітинкою та сіткою результатів (наприклад, D2:I9).
- На вкладці Data натисніть What-If Analysis → Data Table.
- У полі Row input cell вкажіть клітинку вводу моделі, що відповідає значенням у верхньому рядку (наприклад, B2 — кількість).
- У полі Column input cell вкажіть клітинку вводу моделі, що відповідає значенням у лівому стовпці (наприклад, B3 — ціна).
- Натисніть OK.
Корисні поради та часті помилки
- Працездатні версії: за даними джерела, Data Tables і Scenario Manager працюють у Excel 365, 2024, 2021, 2019 і 2016. У Excel для вебу результати можна переглядати, але створювати — лише в настільній програмі.
- Клітинка вводу має бути на тому самому аркуші, що й таблиця даних; інакше з’явиться помилка «Input cell reference is not valid».
- Обирайте саме ту клітинку, яку реально використовує формула моделі. Якщо вказати іншу — таблиця поверне однакові результати в усіх рядках/стовпцях без помилки.
- Оновлення розрахунків: великі таблиці даних можуть сповільнювати книгу. Якщо результати не оновлюються автоматично, перевірте Formulas → Calculation Options: режим Automatic Except for Data Tables залишає старі значення, доки ви не натиснете F9 або не перемкнетеся назад на Automatic.
- Редагування: не можна видалити або перезаписати окрему клітинку в межах Data Table. Щоб очистити результати, виділіть увесь діапазон результатів і натисніть Delete.
Фото: Trump Excel
