Превключване между различни набори от стойности с помощта на сценарии

Има три вида инструменти за анализ на 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 са стойностите, които се променят между сценариите. Ако изберете променящите се клетки в работния лист, преди да добавите Сценарий, диспечерът на сценарии автоматично ще вмъкне клетките вместо вас, в противен случай можете да ги въведете на ръка или да използвате диалоговия прозорец за избор на клетки отдясно на диалоговия прозорец Промяна на клетки.

Защита – Можете също да защитите своите сценарии, така че в раздела Защита проверете желаните опции или премахнете отметката от тях, ако не искате каквато и да е защита.

• Изберете Предотвратяване на промени, за да предотвратите редактирането на сценария, когато работният лист е защитен.

• Изберете Скрито , за да предотвратите показването на сценария, когато работният лист е защитен.

Да предположим, че вашият сценарий за бюджет за най-добър случай е брутен приход от 150 000 лв. и разходи за продадени стоки в размер на 26 000 лв., като се оставят 124 000 лв. в брутна печалба. За да дефинирате този набор от стойности като сценарий, трябва да създадете друг сценарий, да го наречете Най-добър случай и да предоставите различни стойности за клетка B2 (150 000) и клетка B3 (26 000). Тъй като брутната печалба (клетка B4) е формула – разликата между приходите (B2) и разходите (B3) – не променяте клетка B4 за сценария за най-добър случай.

След като запишете сценарий, той става достъпен в списъка със сценарии, които можете да използвате във вашите усл. анализи. Имайки предвид стойностите в горната илюстрация, ако сте избрали да покажете сценария "Най-добър случай", стойностите в работния лист ще се променят така, че да наподобяват следната илюстрация:

Понякога може да имате цялата информация в един работен лист или работна книга, необходима за създаването на всички сценарии, които искате да имате предвид. Може обаче да поискате да съберете информация за сценария от други източници. Да предположим например, че се опитвате да създадете бюджет на фирма. Можете да събирате сценарии от различни отдели, като например "Продажби", "Заплати", "Производство", "Маркетинг" и "Правни", тъй като всеки от тези източници има различна информация, която да се използва при създаването на бюджета.

Можете да съберете тези сценарии в един работен лист с помощта на командата Обединяване . Всеки източник може да предостави толкова или малко променящи се стойности на клетки, колкото желаете. Например може да искате всеки отдел да предоставя прогнози за разходите, но от някои се нуждаете само от прогнози за приходите.

Когато изберете да обедините, диспечерът на сценарии ще зареди съветник за обединяване на сценарии, който ще покаже всички работни листове в активната работна книга, както и списък на всички други работни книги, които може да сте отворили по това време. Съветникът ще ви покаже колко сценария имате във всеки работен лист източник, който изберете.

Когато събирате различни сценарии от различни източници, трябва да използвате една и съща структура на клетките във всяка от работните книги. Например приходите винаги може да отиват в клетка B2, а разходите може винаги да отиват в клетка B3. Ако използвате различни структури за сценариите от различни източници, може да е трудно да обедините резултатите.

За да сравните няколко сценария, можете да създадете отчет, който ги обобщава на една и съща страница. Отчетът може да изброи сценариите един до друг или да ги представи в отчет с обобщена таблица.

Отчет за резюме на сценарий, базиран на предходните два примерни сценария, ще изглежда подобно на следното:

Ще забележите, че Excel автоматично е добавил нива на групиране вместо вас, което ще разгъне и свие изгледа, когато щракнете върху различните селектори.

В края на обобщаващия отчет се показва бележка, която обяснява, че колоната Текущи стойности представя стойностите на променящите се клетки към момента на създаване на отчета за резюме на сценарий и че клетките, които се променят за всеки сценарий, са осветени в сиво.