Таблиці даних - хороший засіб, коли в розрахунках бере участь не більше двох змінних. Але реальні ситуації часто складніше, і тоді вам на допомогу прийде інше ефективний засіб Excel - диспетчер сценаріїв. Цей інструмент дозволяє моделювати завдання, які оперують не однієї або двома змінними, а тридцятьма двома! Щоб запустити диспетчер сценаріїв, виберіть у меню Сервіс команду Сценарії (Scenarios).
Домовимося, що сценарієм (scenario) ми будемо називати набір значень, заданих для однієї або декількох змінних осередків в моделі «що-якщо». На рис. 18.8 представлений робочий лист, на якому реалізована зазначена модель, і показано, як зміна значень змінних впливає на кінцевий результат обчислень. Диспетчер сценаріїв записує, відстежує та застосовує всі зміни змінних величин в будь-яких комбінаціях.
створити кілька сценаріїв для однієї моделі «що-якщо», кожен з яких може мати власний набір змінних;
розподілити моделювання між декількома членами робочої групи таким чином, що кожен з них буде вирішувати окреме завдання; потім об'єднати всі сценарії в загальному звіті;
Мал. 18.8. Тут продемонстровано використання диспетчера сценаріїв в рамках економічної задачі при зміні значень в діапазонах D2: D3, D5 і Е8: Е13
без праці стежити за корективами сценаріїв, що вносяться розробниками, так як диспетчер зберігає дату і ім'я користувача при кожній зміні сценарію;
друкувати звіти з докладною інформацією про всі змінюваних і підсумкових комірках;
захищати сценарії від змін за допомогою пароля і навіть приховувати їх;
порівнювати між собою сценарії, створені різними користувачами, за допомогою інструментів Структура сценарію (Scenario Summary) або Зведена таблиця (Scenario PivotTable). Останній детально розглядається в главі 30 «Аналіз даних за допомогою зведених таблиць».
Щоб зрозуміти, як працює диспетчер сценаріїв, уявіть, що ви - директор невеликого магазинчика і для оцінки його комерційної діяльності створили модель розрахунків, представлену на рис. 18.8. Числа в діапазонах D2: D5 і Е8: Е13 - це середні дані за останній рік роботи магазину. Потрібно дізнатися, як зміна даних параметрів відіб'ється на прибутку, тобто на значенні в осередку Е16.
Перш ніж почати роботу з диспетчером сценаріїв, має сенс привласнити імена осередкам, які будуть потім використовуватися в якості змінних, а також осередкам, які містять формули, що посилаються на змінювані осередки. Це необов'язкове вимога, але даний крок дозволить створювати більш наочні і зручні для роботи звіти за сценарієм. Процедура іменування осередків розглядається в розділі «Присвоєння імен осередкам і діапазонам» глави 12.
Щоб срздать новий сценарій, виконайте наступні дії:
1. У меню Сервіс виберіть команду Сценарії.
2. У діалоговому вікні (Scenario Manager), пока-
занном на рис. 18.9, клацніть на кнопці Додати (Add).
Мал. 18.9. Діалогове вікно
3. У діалоговому вікні Додавання сценарію (Add Scenario), зображеному на рис. 18.10, вкажіть ім'я для нового сценарію. Зверніть увагу, що, як тільки ви почнете вводити в наступне поле цього вікна посилання на змінювані осередки, діалогове вікно стане називатися Зміна сценарію (Edit Scenario).
Ім'я нового сценарію найкраще скласти з назв параметрів, тобто величин, які будуть фігурувати в цьому сценарії в якості змінних. Наприклад, можна назвати сценарій Початкові значення або Минулий рік, як це зроблено в представленому прикладі. В іншому випадку велика ймовірність, що ви дуже скоро забудете, до чого відносяться зроблені розрахунки, і навряд чи пригадаєте всі передумови, на яких була побудована модель «що-якщо».
Мал. 18.10. Введіть посилання на змінювані осередки, відокремлюючи їх один від одного знаком крапки з комою
5. Натисніть кнопку ОК, щоб створити перший сценарій. Відкриється діалогове вікно Значення осередків сценарію (Scenario Values) з полями для кожної змінної осередки. Якщо цим осередкам присвоєні імена, вони будуть відображатися поруч з полями, як на рис. 18.11. В іншому випадку в якості імен виступатимуть посилання на ці осередки.
Мал. 18.11. Оскільки змінним осередкам раніше були присвоєні імена, вони відображаються в діалоговому вікні
6. Тут при бажанні можна змінити все представлені величини. У прикладі ми залишили все як є. Клацніть на кнопці ОК.
У поля діалогового вікна Значення осередків сценарію допускається поміщати як числа, так і формули. Наприклад, щоб на рис. 18.11 збільшити значення першої змінної, потрібно клацнути на відповідному їй поле і ввести перед посиланням формулу = 1,1 *. Таким чином, поточне значення осередки буде масштабувати з коефіцієнтом 1,1. Зауважте, що, хоча введення формул в поля діалогового вікна і дозволений, після натискання кнопки ОК ці формули будуть замінені їх повертаються значеннями, про що Excel попередить вас.
7. Щоб створити інший сценарій, знову натисніть кнопку Додати і повторіть кроки 36.
Виділіть ім'я сценарію в списку діалогового вікна Диспетчер сценаріїв і натисніть кнопку Вивести (Show). Програма замінить вихідні значення змінних робочого листа їх значеннями, заданими при створенні обраного сценарію. На рис. 18.12 показано, як виглядає робочий лист зі сценарієм, за яким середня кількість відвідувачів магазину збільшиться на 5%, але доходи від кожного покупця стануть менше на ті ж 5%.
Додавання, редагування та видалення сценаріїв
У момент збереження книги сценарії записуються разом з усіма іншими даними робочого аркуша, при цьому кожен лист книги може містити власний набір сценаріїв. Щоб додати в книгу новий сценарій, як уже говорилося, досить клацнути на кнопці Додати в діалоговому вікні та повторити описану вище процедуру створення сценарію. А коли потрібно внести зміни в існуючий варіант, виберіть у цьому ж діалоговому вікні команду Змінити (Edit) - на екрані з'явиться діалогове вікно Зміна сценарію (Edit Scenario), ідентичне діалогового вікна Додавання сценарію. Тут можна відредагувати назву обраного сценарію, додати або видалити змінювані осередки. Щоб видалити сценарій, виберіть його ім'я в списку збережених сценаріїв в діалоговому вікні та клацніть на кнопці Видалити (Delete).
Завдяки цьому легко в будь-який момент не тільки дізнатися ім'я творця сценарію, а й простежити, хто і коли вносив до нього правку. Дана інформація особливо корисна, якщо моделі розрахунків передаються іншим користувачам з тим, щоб після спільної роботи над проектом об'єднати всі сценарії в одну загальну модель «що-якщо».
При редагуванні сценаріїв поле Примітка також доступно для зміни; ці модифікації відображаються на всіх діалогових вікнах, а також на датах створення і редагування сценарію. Такий вплив може бути небажаним, особливо коли потрібно відстежувати всі зміни або необхідно запобігти несанкціонованим втручання. В цьому випадку потрібно вжити заходів щодо захисту елементів документа, які детально описані в розділі «Захист даних» глави 5.
Якщо частина вашої роботи полягає в розробці моделей «що-якщо» або в плануванні діяльності організації, то вам, безсумнівно, доводиться витрачати багато часу на збір воєдино корисної інформації від своїх колег, а також про зміни і тенденції на ринку, які в майбутньому здатні вплинути на діяльність і розвиток фірми. Щоб скласти більш повну картину, необхідно опитати чимало співробітників, що представляють різні сторони діяльності компанії. Для полегшення такого роду роботи Excel пропонує дві можливості - розсилку документів і об'єднання сценаріїв.
Якщо комп'ютер підключений до мережі з сумісною системою електронної пошти, значить, ви можете організувати розсилання поточної книги своїм колегам. Для цього підключіть її до електронного листа, вибравши в меню Файл команду Відправити. По маршруту (Send To. Routing Recipient). Сумісні системам електронної пошти відносяться Microsoft Outlook, Microsoft Mail, Lotus cc: Mail і інші програми, що працюють за стандартом MAPI (Messaging Application Programming Interface) або VIM (Vendor Independent Messaging). Також для розсилки підходить мережу Інтернет, яка дає можливість пересилати документи як вебсторінки або через FTP-сайти.
Якщо комп'ютер не підключений до мережі, можна просто створити електронні копії книги і поширити їх серед колег старим надійним способом - на гнучких дисках. Припустимо, ви хочете розповсюдити модель «що-якщо» між декількома
товаришами по службі: Іванової, відмінним фахівцем по роботі з клієнтами, Крилової,
іменованих копій для кожного співробітника. Після того як всі сторони внесли зміни в модель або додали свої власні таблиці «що-якщо», необхідно злити всі сценарії в один. Для цього відкрийте всі книги, які містять потрібні сценарії, активізуйте лист, на якому будуть зберігатися результати об'єднання, і натисніть кнопку Об'єднати (Merge) в діалоговому вікні Диспетчер сценаріїв. На екрані з'явиться діалогове вікно Об'єднання сценаріїв (Merge Scenarios), представлене на рис. 18.13.
Мал. 18.13. Діалогове вікно Об'єднання сценаріїв дозволяє імпортувати сценарії з будь-якого листа в будь-яку відкриту книгу
Злиття сценаріїв проходить «гладко», коли всі листи мають однакову базову структуру. Така однорідність не є жорстким обмеженням, але об'єднання сценаріїв з листів з різною структурою даних в стані спричинити появу значень змінних осередків в найнесподіваніших місцях робочого листа. З цієї причини варто спробувати ще один метод. Для отримання необхідних даних розподіліть серед своїх колег тільки питання, потім за допомогою зовнішніх посилань зв'яжіть запитувані дані з відповідними позиціями на підсумковому аркуші і самостійно створіть загальний сценарій.
У цьому вікні виберіть книгу, а потім лист, сценарії якого потрібно об'єднати. Як показано на рис. 18.13, в нижній частині діалогового вікна з'являється повідомлення про кількість сценаріїв, доступних для злиття. Після натискання кнопки ОК все сценарії обраного робочого листа копіюються на активний лист. Коли ви виконаєте всі необхідні дії, діалогове вікно диспетчера сценаріїв буде виглядати приблизно так, як зображено на рис. 18.14.
У кожній групі сценаріїв, наданої співробітниками, встановлено різні змінні комірки. На аркуші Іванової - це осередки D2, D3 і D5, Крилової - Е8, а Андрєєвої - Е11. Тепер, об'єднавши сценарії, можна оцінити вплив різних комбінацій змінних величин на перспективи компанії.
Мал. 18.14. Об'єднані сценарії з'являються на поточному аркуші
Створення звітів за сценаріями
Після об'єднання сценаріїв спочатку проста модель в нашому прикладі стала досить складною. Однак ви можете створювати і ще більш складні моделі, що включають в себе будь-яку кількість сценаріїв (звичайно, все залежить від можливостей комп'ютера), що мають до 32 змінних в кожному! пропонує два типи звітів: звіт Структура (Scenario Summary), в якому відображаються всі можливі шляхи розвитку модельної ситуації, і звіт Зведена таблиця (PivotTable Report) з набором додаткових інструментів для роботи з елементами самого звіту.
Якщо клацнути на кнопці Звіт (Summary) в діалоговому вікні. на екрані з'явиться діалогове вікно Звіт за сценарієм (Scenario Summary), показане на рис. 18.15. З його допомогою створюються звіти, що містять інформацію про значеннях, що заносяться по кожному із сценаріїв в змінювані осередки. В поле Осередки результату (Result Cells) діалогового вікна через кому вказуються осередки,
в яких будуть відображатися результати при певних значеннях змінних сценарію. У нашому випадку в якості місця для результату обрані осередки Е1б (Чистий прибуток) і Е6 (Загальний прибуток).
Мал. 18.18. Разом зі зведеною таблицею за звітом на екран виводиться і панель інструментів Зведені таблиці
Звіт Зведена таблиця найкраще підходить для роботи зі складними моделями з безліччю параметрів. Створення такого звіту може тривати довше і вимагає більше пам'яті, ніж створення звіту Структура. Якщо ви формуєте всі сценарії самостійно і в кожному з них зберігаєте один і той же безліч змінюваних осередків, то зручніше скористатися варіантом Структура, так как_в цьому випадку у вас все одно не вийде оцінити всі достоїнства звіту Зведена таблиця.