Диспетчер сценаріїв

Таблиці даних – хороший засіб, коли в розрахунках бере участь не більше двох змінних Але реальні ситуації часто складніше, і тоді вам на допомогу прийде інше ефективний засіб Excel – диспетчер сценаріїв Цей інструмент дозволяє моделювати завдання, які оперують не однієї або двома змінними, а тридцятьма двома Щоб запустити диспетчер сценаріїв, виберіть у меню Сервіс команду Сценарії (Scenarios)

Домовимося, що сценарієм (Scenario) ми будемо називати набір значень, заданих для однієї або декількох змінюваних осередків у моделі «що-якщо» На рис 188 представлений робочий лист, на якому реалізована зазначена модель, і показано, як зміна значень змінних впливає на кінцевий результат обчислень записує, відстежує і застосовує всі зміни змінних величин в будь-яких комбінаціях

дозволяє:

► створити кілька сценаріїв для однієї моделі «що-якщо», кожен з яких може мати власний набір змінних

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

Рис 188 Тут продемонстровано використання диспетчера сценаріїв в рамках економічної задачі при зміні значень в діапазонах D2: D3, D5 і Е8: Е13

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

► друкувати звіти з докладною інформацією про всі змінюваних і підсумкових комірках

► захищати сценарії від змін за допомогою пароля і навіть приховувати їх

► порівнювати між собою сценарії, створені різними користувачами, за допомогою інструментів Структура сценарію (Scenario Summary) або Зведена таблиця (Scenario PivotTable) Останній детально розглядається в розділі 30 «Аналіз даних за допомогою зведених таблиць»

Щоб зрозуміти, як працює диспетчер сценаріїв, уявіть, що ви – директор невеликого магазинчика і для оцінки його комерційної діяльності створили модель розрахунків, представлену на рис 188 Числа в діапазонах D2: D5 і Е8: Е13 – це середні дані за останній рік роботи магазину Потрібно взнати, як зміна даних параметрів відібється на прибутку, тобто на значенні в комірці Е16

РАДА

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

Створення сценарію

Щоб срздать новий сценарій, виконайте такі дії:

1 У меню Сервіс виберіть команду Сценарії

2 У діалоговому вікні (Scenario Manager), поки-

занном на рис 189, клацніть на кнопці Додати (Add)

Рис 189 Діалогове вікно

3 У діалоговому вікні Додавання сценарію (Add Scenario), зображеному на рис 1810, вкажіть імя для нового сценарію Зверніть увагу, що, як тільки ви почнете вводити в наступне полі цього вікна посилання на змінювані комірки, діалогове вікно стане називатися Зміна сценарію (Edit Scenario)

РАДА

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

Рис 1810 Введіть посилання на змінювані комірки, відокремлюючи їх один від одного знаком крапки з комою

4 У полі Змінні комірки (Changing Cells) вкажіть осередку, вміст яких планується варіювати Посилання на ці комірки можна ввести вручну або ж виділивши осередку за допомогою миші Щоб виділити мишею несуміжні клітинки і діапазони, утримуйте при виділенні клавішу Ctrl При введенні з клавіатури між посиланнями розміщуйте символ крапки з комою, як показано на рис 1810

5 Натисніть кнопку ОК, щоб створити перший сценарій Відкриється діалогове вікно Значення комірок сценарію (Scenario Values) з полями для кожної змінною осередки Якщо цим осередкам присвоєні імена, вони будуть відображатися поруч з полями, як на рис 1811 В іншому випадку в якості імен виступатимуть посилання на ці комірки

Рис 1811 Оскільки змінним осередкам раніше були присвоєні імена, вони відображаються в діалоговому вікні

6 Тут при бажанні можна змінити всі представлені величини У прикладі ми залишили все як є Клацніть на кнопці ОК

РАДА —-

У поля діалогового вікна Значення осередків сценарію допускається поміщати як числа, так і формули Наприклад, щоб на рис 1811 збільшити значення першої змінної, потрібно клацнути на відповідному їй полі і ввести перед посиланням формулу = 1,1 * Таким чином, поточне значення осередки буде масштабувати з коефіцієнтом 1,1 Зауважте, що, хоча введення формул в поля діалогового вікна і дозволений, після натискання кнопки ОК ці формули будуть замінені їх повертаними значеннями, про що Excel попередить вас

7 Щоб створити інший сценарій, знову натисніть кнопку Додати і повторіть кроки 36

Перегляд сценаріїв

Виділіть імя сценарію в списку діалогового вікна і натисніть кнопку Вивести (Show) Програма замінить вихідні значення змінних робочого листа їх значеннями, заданими при створенні обраного сценарію На рис 1812 показано, як виглядає робочий лист зі сценарієм, за яким середня кількість відвідувачів магазину збільшиться на 5%, але доходи від кожного покупця стануть менше на ті ж 5%

Рис 1812 При перегляді обраного сценарію диспетчер замінює поточні значення робочого листа значеннями, заданими при створенні обраного сценарію

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

Додавання, редагування та видалення сценаріїв

У момент збереження книги сценарії записуються разом з усіма іншими даними робочого аркуша, при цьому кожен аркуш книги може містити власний набір сценаріїв Щоб додати в книгу новий сценарій, як уже говорилося, досить клацнути на кнопці Додати в діалоговому вікні і повторити описану вище процедуру створення сценарію А коли потрібно внести зміни в існуючий варіант, виберіть у цьому ж діалоговому вікні команду Змінити (Edit) – на екрані зявиться діалогове вікно Зміна сценарію (Edit Scenario), ідентичне діалогового вікну Додавання сценарію Тут можна відредагувати назву обраного сценарію, додати або видалити змінювані комірки Щоб видалити сценарій, виберіть його імя в списку збережених сценаріїв в діалоговому вікні і клацніть на кнопці Видалити (Delete)

Відстеження авторства змін

Коли ви вносите сценарій, в поле Примітка (Comment) діалогового вікна Excel після рядкаАвтордодає рядокАвтор змін

Завдяки цьому легко в будь-який момент не тільки дізнатися імя творця сценарію, але й простежити, хто і коли вносив до нього правку Дана інформація особливо корисна, якщо моделі розрахунків передаються іншим користувачам з тим, щоб після спільної роботи над проектом обєднати всі сценарії в одну загальну модель «що-якщо»

ПРИМІТКА

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

Обєднання сценаріїв

Якщо частина вашої роботи полягає в розробці моделей «що-якщо» або в плануванні діяльності організації, то вам, безсумнівно, доводиться витрачати багато часу на збір воєдино корисної інформації від своїх колег, а також про зміни і тенденції на ринку, які в майбутньому здатні вплинути на діяльність і розвиток фірми Щоб скласти більш повну картину, необхідно опитати чимало співробітників, що представляють різні сторони діяльності компанії Для полегшення такого роду роботи Excel пропонує дві можливості –розсилку документів і обєднання сценаріїв

Якщо компютер підключений до мережі з сумісною системою електронної пошти, значить, ви можете організувати розсилання поточної книги своїм колегам Для цього приєднайте її до електронного письма, обравши в меню Файл команду Надіслати ► По маршруту (Send To ► Routing Recipient) До сумісним систем електронної пошти відносяться Microsoft Outlook, Microsoft Mail, Lotus cc: Mail та інші додатки, що працюють за стандартом MAPI (Messaging Application Programming Interface) або VIM (Vendor Independent Messaging) Також для розсилки підходить мережу Інтернет, що дозволяє пересилати документи як вебсторінки або через FTP-сайти

ДИВИСЬ ТАКОЖ

Детальніше про організацію розсилки за маршрутом буде сказано в главі 19, використання Інтернету під час роботи в Excel описується в главі 20

Якщо компютер не підключений до мережі, можна просто створити електронні копії книги і поширити їх серед колег старим надійним способом – на гнучких дисках Припустимо, ви хочете розповсюдити модель «що-якщо» між декількома

товаришами по службі: Іванової, відмінним фахівцем по роботі з клієнтами, Крилової,

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

іменованих копій для кожного співробітника Після того як всі сторони внесли зміни в модель або додали свої власні таблиці «що-якщо», необхідно злити всі сценарії в один Для цього відкрийте всі книги, що містять потрібні сценарії, активізуйте аркуш, на якому зберігатимуться результати обєднання, і натисніть кнопку Обєднати (Merge) в діалоговому вікні На екрані зявиться діалогове вікно Обєднання сценаріїв (Merge Scenarios), представлене на рис 1813

Рис 1813 Діалогове вікно Обєднання сценаріїв дозволяє імпортувати сценарії з будь-якого листа в будь-яку відкриту книгу

РАДА

Злиття сценаріїв проходить «гладко», коли всі листи мають однакову базову структуру Така однорідність не є жорстким обмеженням, але обєднання сценаріїв з листів з різною структурою даних в змозі спричинити появу значень змінюваних осередків у найнесподіваніших місцях робочого аркуша З цієї причини варто спробувати ще один метод Для отримання необхідних даних розподіліть серед своїх колег тільки питання, потім за допомогою зовнішніх посилань звяжіть запитувані дані з відповідними позиціями на підсумковому аркуші і самостійно створіть загальний сценарій

У цьому вікні виберіть книгу, а потім лист, сценарії якого потрібно обєднати Як показано на рис 1813, в нижній частині діалогового вікна зявляється повідомлення про кількість сценаріїв, доступних для злиття Після натискання кнопки ОК всі сценарії обраного робочого листа копіюються на активний аркуш Коли ви виконаєте всі необхідні дії, діалогове вікно диспетчера сценаріїв буде виглядати приблизно так, як зображено на рис 1814

Зверніть увагу, що в поле Примітка (Comment) у нижній частині діалогового вікна відображається імя творця (і автора змін) виділеного сценарію Зауважте також, що на рис 1814 список Сценарії (Scenarios) містить сценарії з схожими іменами Це сталося тому, що в даному прикладі всі співробітники назвали свої моделі однаково У такій ситуації, щоб уникнути плутанини, до кожного назвою програмно додається дата створення сценарію і порядковий номер на випадок, якщо сценарії створені в один день Будь-який з них завжди доступний для перейменування за допомогою кнопки Змінити (Edit)

У кожній групі сценаріїв, наданої співробітниками, встановлені різні змінювані комірки На аркуші Іванової – це осередки D2, D3 і D5, Крилової – Е8, а Андрєєвої – Е11 Тепер, обєднавши сценарії, можна оцінити вплив різних комбінацій змінних величин на перспективи компанії

Рис 1814 Обєднані сценарії зявляються на поточному аркуші

РАДА

Простіше і швидше за все отримати доступ до сценаріїв – скористатися кнопкою Сценарії, яку попередньо потрібно додати на панель інструментів Для цього в меню Вигляд виберіть команду Панелі інструментів ► Настройка Перейдіть на вкладку Команди (Commands) і виберіть у списку категорію Сервіс (Tools) Знайдіть у правій частині діалогового вікна Налаштування (Customize) команду Сценарії (Scenario) і перетягніть її на будь-яку видиму панель інструментів У списку відображаються всі сценарії поточного робочого аркуша, і щоб звернутися до якого-небудь з них, досить вибрати в списку його назву

Створення звітів за сценаріями

Після обєднання сценаріїв спочатку проста модель у нашому прикладі стала досить складною Однак ви можете створювати і ще більш складні моделі, що включають в себе будь-яку кількість сценаріїв (звичайно, все залежить від можливостей компютера), що мають до 32 змінних в кожному пропонує два типи звітів: звіт Структура (Scenario Summary), в якому відображаються всі можливі шляхи розвитку модельної ситуації, і звіт Зведена таблиця (PivotTable Report) з набором додаткових інструментів для роботи з елементами самого звіту

Якщо клацнути на кнопці Звіт (Summary) у діалоговому вікні , на екрані зявиться діалогове вікно Звіт за сценарієм (Scenario Summary), показане на рис 1815 З його допомогою створюються звіти, що містять інформацію про значення, що заносяться по кожному із сценаріїв в змінювані комірки У полі Осередки результату (Result Cells) діалогового вікна через кому вказуються осередку,

в яких будуть відображатися результати при певних значеннях змінних сценарію У нашому випадку в якості місця для результату обрані осередки Е1б (Чистий прибуток) і Е6 (Загальний прибуток)

Рис 1815 Діалогове вікно Звіт за сценарієм використовується для завдання типу звіту та осередків результату

Звіт Структура

При виборі варіанту Структура (Scenario Summary) на новому аркуші створюється повністю відформатований звіт, подібний представленому на рис 1816 (Для наочності формат таблиці був дещо змінений)

Рис 1816 Якщо вибрати варіант Структура, Excel генерує на новому робочому аркуші відформатований звіт з імям Структура сценарію

Придивіться: на малюнку всі величини в змінюваних комірках стовпчика Е зафарбовані сірим кольором Заливанням виділяються осередки, змінювані за сценарієм, назва якого фігурує в заголовку стовпця Також зверніть увагу на символи структури, що зявилися у самого верхнього і лівого країв вікна, – вони дозволяють приховувати і показувати деталі звіту Наприклад, якщо клацнути на кнопці зі значком «плюс» лівіше заголовка рядка 3, на екран будуть виведені приховані дані: вміст поля Примітки діалогового вікна з датами створення і зміни кожного сценарію (рис 1817)

ДИВИСЬ ТАКОЖ

Детальніше про створення структурованих листів Excel і роботі з ними розповідається в розділі «Структурування листів» глави 9

Рис 1817 Примітки, введені в діалоговому вікні ,

знаходяться в прихованій четвертому рядку звіту

Звіт Зведена таблиця

Зведені таблиці – Потужний інструмент аналізу і оптимальний варіант для роботи зі складними моделями «що-якщо», що включають в себе створені різними користувачами сценарії з безліччю змінюваних осередків При виборі в діалоговому вікні Звіт за сценарієм варіанту Зведена таблиця (PivotTabte Report), як і в попередньому випадку, створюється новий лист в поточній книзі Але у зведеній таблиці допускається перемішувати і поєднувати між собою різні сценарії, причому будь-яка операція виконується за допомогою миші Потім, природно, можна спостерігати, як всі ці дії вплинули на комірки з результатами На рис 1818 показана зведена таблиця, створена на основі раніше обєднаних нами сценаріїв

ДИВИСЬ ТАКОЖ –

Детальніше робота зі зведеними таблицями і панеллю інструментів Зведені таблиці розглядається в розділі 30

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

Рис 1818 Разом зі зведеною таблицею за звітом на екран виводиться і панель інструментів Зведені таблиці

РАДА

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

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*