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