Когато създавате диаграми във финансови модели или отчети, все пак трябва да следвате най-добрите практики и да се опитате да направите моделите си възможно най-гъвкави и динамични. Винаги трябва да свързвате колкото е възможно повече във вашите модели и това важи и за диаграмите. Има смисъл, когато промените един от входните данни за вашия модел, това трябва да бъде отразено в данните на диаграмата, както и в заглавията и етикетите.
Изграждане на диаграмата върху данни, управлявани от формула
Изтеглете файл 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.
Преоразмерете и преместете текстовото поле, ако е необходимо.
Тествайте модела, като промените числата, така че повече елементи да надхвърлят бюджета, и се уверете, че коментарът в текстовото поле се променя.