Сценарият е набор от стойности, които Excel записва и може да замести автоматично в работния лист. Можете да създавате и записвате различни групи от стойности като сценарии и след това да превключвате между тях, за да видите различните резултати.
Ако няколко души имат конкретна информация, която искате да използвате в сценарии, можете да съберете информацията в различни работни книги и след това да обедините сценариите от различните работни книги в една.
След като разполагате с всички необходими сценарии, можете да създадете отчет за резюме на сценарий, включващ информация от всички сценарии.
Сценариите се управляват със съветника за диспечер на сценарии от групата "Условен анализ " в раздела " Данни ".
Видове анализ на What-If
Има три вида инструменти за анализ на What-If, които се предоставят с Excel: сценарии, таблици с данни и търсене на цел. Сценариите и таблиците с данни вземат набори от входни стойности и прогнозират напред, за да определят възможните резултати. Търсенето на цел се различава от сценариите и таблиците с данни по това, че приема резултат и се проектира назад, за да определи възможните входни стойности, които водят до този резултат.
Всеки сценарий може да поеме до 32 променливи стойности. Ако искате да анализирате повече от 32 стойности и стойностите представляват само една или две променливи, можете да използвате таблици с данни. Въпреки че е ограничена само до една или две променливи (една за входната клетка на реда и една за входната клетка на колоната), таблицата с данни може да включва толкова различни променливи стойности, колкото желаете. Един сценарий може да има най-много 32 различни стойности, но можете да създадете колкото сценария пожелаете.
Освен тези три инструмента, можете да инсталирате добавки, които ви помагат да извършвате What-If анализ, като например добавката Solver. Добавката Solver е подобна на "Търсене на цел", но тя може да поеме повече променливи. Можете също така да създавате прогнози с помощта на манипулатора за попълване и различни команди, вградени в Excel. За по-разширени модели можете да използвате добавката Analysis ToolPak.
Създаване на сценарии
Да предположим, че искате да създадете бюджет, но не сте сигурни за вашите приходи. Като използвате сценарии, можете да дефинирате различни възможни стойности за приходите и след това да превключвате между сценариите, за да извършите условни анализи.
Да приемем, че вашият най-лош сценарий за бюджет е брутен приход от 50 000 лв. и разходи за продадените стоки от 13 200 лв., оставащи брутна печалба 36 800 лв. За да дефинирате този набор от стойности като сценарий, първо въвеждате стойностите в работен лист, както е показано на следната илюстрация:
Променящите се клетки съдържат стойности, които вие въвеждате, докато клетката за резултат съдържа формула, която се базира на променящите се клетки (на тази илюстрация в клетка B4 е формулата =B2-B3).
След това използвайте диалоговия прозорец "Диспечер на сценарии ", за да запишете тези стойности като сценарий. Отидете на раздела > "Данни" What-If добавяне на диспечер > на сценарии за анализ>.
В диалоговия прозорец за име на сценарий дайте име на сценария "Най-лош случай" и посочете, че клетки B2 и B3 са стойностите, които се променят между сценариите. Ако изберете "Променяне на клетки " във вашия работен лист, преди да добавите сценарий, диспечерът на сценарии автоматично ще вмъкне клетките вместо вас, в противен случай можете да ги въведете на ръка или да използвате диалоговия прозорец за избор на клетка отдясно на диалоговия прозорец "Променяне на клетки".
Забележка
Въпреки че този пример съдържа само две променящи се клетки (B2 и B3), един сценарий може да съдържа до 32 клетки.
Защита – можете също така да защитите своите сценарии, така че в секцията "Защита" отметнете желаните опции или махнете отметката от тях, ако не искате защита.
- Изберете "Предотвратяване на промените ", за да предотвратите редактирането на сценария, когато работният лист е защитен.
- Изберете "Скрит ", за да предотвратите показването на сценария, когато работният лист е защитен.
Забележка
Тези опции се отнасят само за защитени работни листове. За повече информация относно защитените работни листове вижте "Защита на работен лист"
Да предположим сега, че вашият сценарий за най-добър случай на бюджет е брутен приход от 150 000 лв. и разходи за продадените стоки от 26 000 лв., оставащи брутна печалба от 124 000 лв. За да дефинирате този набор от стойности като сценарий, създавате друг сценарий, наричате го "Най-добър регистър" и предоставяте различни стойности за клетките B2 (150 000) и B3 (26 000). Тъй като брутният приход (клетка B4) е формула – разликата между приходи (B2) и разходи (B3) – не променяте клетка B4 за сценария за най-добър случай.
След като запишете сценарий, той става наличен в списъка със сценарии, който можете да използвате във вашите условни анализи. Като се имат предвид стойностите в горната илюстрация, ако изберете да покажете сценария за най-добър случай, стойностите в работния лист ще се променят, за да приличат на следната илюстрация:
Сценарии на обединяване
В някои случаи може да имате цялата информация в един работен лист или работна книга, необходима за създаване на всички сценарии, които искате да разгледате. Може обаче да искате да съберете информация за сценария от други източници. Да предположим например, че се опитвате да създадете бюджет на фирмата. Можете да събирате сценарии от различни отдели, като например "Продажби", "Заплати", "Производство", "Маркетинг" и "Правни", защото всеки от тези източници има различна информация за използване при създаването на бюджета.
Можете да съберете тези сценарии в един работен лист с помощта на командата "Обединяване ". Всеки източник може да предостави толкова или малко променящи се стойности на клетки, колкото желаете. Например може да искате всеки отдел да предоставя прогнози за разходите, но да ви трябват само прогнози за приходите от няколко отдела.
Когато изберете да обедините, диспечерът на сценарии ще зареди съветник за сценарий на обединяване, който ще покаже списък на всички работни листове в активната работна книга, както и всички други работни книги, които може да сте отворили по това време. Съветникът ще ви съобщи колко сценария имате във всеки избран от вас работен лист източник.
Когато събирате различни сценарии от различни източници, трябва да използвате една и съща структура на клетки във всяка от работните книги. Например "Приходи" може винаги да отива в клетка B2, а "Разходи" може винаги да отива в клетка B3. Ако използвате различни структури за сценариите от различни източници, може да е трудно да обедините резултатите.
Съвет
Помислете първо да създадете сценарий сами, а след това да изпратите на колегите си копие на работната книга, която съдържа този сценарий. Така е по-лесно да сте сигурни, че всички сценарии са структурирани по еднакъв начин.
Отчети за резюме на сценарий
За да сравните няколко сценария, можете да създадете отчет, който ги обобщава на една и съща страница. Отчетът може да показва списък на сценариите един до друг или да ги представя в отчет с обобщена таблица.
" Отчетът за резюме на сценарий, базиран на предишните два примерни сценария, би изглеждал приблизително така:
Ще забележите, че Excel автоматично е добавил нива на групиране вместо вас, които ще разгъват и свиват изгледа, като щраквате върху различните селектори.
В края на отчета за резюме се показва бележка, в която се обяснява, че колоната "Текущи стойности " представя стойностите на променящите се клетки по време на създаването на отчета за резюме на сценарий и че клетките, които са променени за всеки сценарий, са маркирани в сиво.
Забележка
- По подразбиране обобщеният отчет използва препратки към клетки, за да идентифицира клетките за променяне и клетките за резултат. Ако създадете наименувани диапазони за клетките, преди да изпълните обобщения отчет, отчетът ще съдържа имената, а не препратките към клетки.
- Отчетите за сценарий не се преизчисляват автоматично. Ако промените стойностите на сценарий, тези промени няма да се покажат в съществуващ отчет за резюме, но ще се показват, ако създадете нов отчет за резюме.
- Не са ви нужни клетки с резултати, за да генерирате отчет за резюме на сценарий, но са ви нужни за отчета с обобщена таблица за сценарий.
Имате нужда от още помощ?
Винаги можете да попитате експерт в техническата общност за Excel или да получите поддръжка в общностите.
Вж. също
Дефиниране и решаване на проблем с помощта на Solver
Използване на Analysis ToolPak за извършване на сложен анализ на данни
Общ преглед на формулите в Excel
Начини за избягване на повредени формули
Намиране и коригиране на често срещани грешки във формули