Динамични диаграми във финансовото моделиране

Когато създавате диаграми във финансови модели или отчети, все пак трябва да следвате най-добрите практики и да се опитате да направите моделите си възможно най-гъвкави и динамични. Винаги трябва да свързвате колкото е възможно повече във вашите модели и това важи и за диаграмите. Има смисъл, когато промените един от входните данни за вашия модел, това трябва да бъде отразено в данните на диаграмата, както и в заглавията и етикетите.

Изграждане на диаграмата върху данни, управлявани от формула

Изтеглете файл 0901.xlsx . Отворете го и изберете раздела с етикет 9-23, за да го изпробвате сами.

Промяна на падащото поле.

Ако скриете данни в изходния си лист, те няма да се покажат на диаграмата. Тествайте това, като скриете една от колоните на финансовия лист и проверете дали месецът е изчезнал на графиката. Можете да промените опциите под Избор на източник на данни, така че да показва скрити клетки.

Свързване на заглавията на диаграмите с формули

Тъй като всички данни са свързани с падащото поле, можете лесно да създадете динамично заглавие в диаграмата, като създадете формула за заглавието и след това свържете това заглавие с диаграмата. Следвай тези стъпки:

В клетка A1 на този модел променете заглавието на следното: =„Петгодишни стратегически прогнозни разходи за кол център – „&F1.

Амперсандът (&) служи като конектор, който ще нареди текст и стойности от формули заедно.

Вместо амперсанда, можете също да използвате функцията CONCATENATE, която работи много подобно чрез свързване на единични клетки заедно, или функцията TEXTJOIN е ново допълнение към Excel 2016, което ще обедини големи количества данни.

Когато формулата в клетка A1 работи, трябва да свържете заглавието в диаграмата с клетка A1.

Щракнете върху заглавието на диаграмата.

Тази част може да бъде трудна. Уверете се, че сте избрали само заглавието на диаграмата.

Щракнете върху лентата с формули.

Въведете = и след това щракнете върху клетка A1.

Натиснете Enter.

Заглавието на диаграмата се променя, за да покаже какво има в клетка A1.

Свързване на заглавията на диаграмите с формули.

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

Създаване на динамичен текст

Разгледайте месечния бюджетен отчет. Вече сме изградили формули в колони F и G, които автоматично ще се актуализират при промяна на данните и ще показват как вървим в сравнение с бюджета.

Създаване на клъстерирана колонна диаграма.

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

Маркирайте данните, показващи стойностите на сметката, действителните и бюджетните стойности съответно в колони B, C и D.

Изберете първата опция за 2-D колона от секцията Диаграми на раздела Вмъкване на лентата, за да създадете клъстерирана колонна диаграма.

В клетка A1 създайте заглавие с динамична дата.

Свържете заглавието на диаграмата с формулата в клетка A1.

Редактирайте диаграмата, така че заглавията да са хоризонтално подравнени и променете цветовете.

Тази диаграма ще изглежда много по-добре, ако е сортирана така, че по-големите ленти да са от лявата страна.

Маркирайте всички данни, включително заглавията, и щракнете върху бутона Сортиране (в секцията Сортиране и филтриране на раздела Данни в лентата).

Появява се диалоговият прозорец Сортиране.

Сортиране по Действително от най-голямо към най-малко.

Сортиране на данните от диаграмата.

Много е лесно да объркате формули при сортиране, така че не забравяйте да маркирате всички колони от колони A до G, преди да приложите сортирането.

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

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

Можете да направите това с формулата =COUNTA(G3:G12)-COUNT(G3:G12), която изчислява колко непразни клетки има в колона G.

Можете да видите, че две договорени позиции са над бюджета, така че преобразувайте това в динамичен текст с формулата =COUNTA(G3:G12)-COUNT(G3:G12)&“ Елементи над бюджет.“

Завършена диаграма с динамично текстово поле.

Вмъкнете текстово поле в диаграмата, като натиснете бутона Текстово поле в групата Текст на раздела Вмъкване в лентата.

Щракнете върху диаграмата веднъж.

Появява се текстовото поле.

Внимателно изберете външната част на текстовото поле с мишката, точно както направихте в последния раздел, когато свързвате заглавията на диаграмата.

Сега отидете на лентата с формули и въведете =.

Щракнете върху клетка A15 и щракнете върху Enter.

Преоразмерете и преместете текстовото поле, ако е необходимо.

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