Како додати и користити израчунато поље Екцел заокретне табеле

Често, када креирате изведену табелу, постоји потреба да проширите своју анализу и укључите више података/прорачуна као њен део.

Ако вам је потребна нова тачка података која се може добити коришћењем постојећих тачака података у изведеној табели, не морате да се враћате и да је додате у изворне податке. Уместо тога, можете користити а Израчунато поље изведене табеле да уради ово.

Преузмите скуп података и пратите даље.

Шта је израчунато поље изведене табеле?

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

Претпоставимо да имате скуп података о малопродаји и креирате изведену табелу као што је приказано испод:

Горња изведена табела сумира вредности продаје и добити за малопродавце.

Шта ако желите да знате и колика је била профитна маржа ових трговаца (где је маржа профита „добит“ подељена са „продаја“).

Постоји неколико начина за то:

1. Вратите се на оригинални скуп података и додајте ову нову тачку података. Тако да можете уметнути нову колону у изворне податке и израчунати маржу профита у њој. Када то учините, потребно је да ажурирате изворне податке изведене табеле да бисте добили ову нову колону као њен део.
   • Иако је ова метода могућа, морали бисте се ручно вратити на скуп података и извршити прорачуне. На пример, можда ћете морати да додате другу колону да бисте израчунали просечну продају по јединици (продаја/количина). Опет ћете морати да додате ову колону у изворне податке, а затим ажурирате изведену табелу.
   • Ова метода такође повећава вашу изведену табелу док јој додајете нове податке.
2. Додајте прорачуне изван изведене табеле. Ово може бити опција ако се вероватно неће променити структура ваше изведене табеле. Али ако промените изведену табелу, прорачун се можда неће ажурирати у складу с тим и може вам дати погрешне резултате или грешке. Као што је доле приказано, израчунао сам профитну маржу када су у низу били трговци. Али када сам га променио из корисника у регион, формула је дала грешку.
3. Коришћење израчунатог поља изведене табеле. Ово је најефикаснији начин да бисте користили постојеће податке изведене табеле и израчунали жељену метрику. Израчунајте поље као виртуелну колону коју сте додали користећи постојеће колоне из изведене табеле. Употреба израчунатог поља изведене табеле има много предности (као што ћемо видети за минут):
   • Не захтева да рукујете формулама или ажурирате изворне податке.
   • Он је скалабилан јер ће аутоматски узети у обзир све нове податке које можете додати у своју изведену табелу. Када додате поље за израчунавање, можете га користити као и свако друго поље у изведеној табели.
   • Лако се ажурира и управља. На пример, ако се показатељи промене или морате да промените прорачун, то можете лако учинити из саме изведене табеле.

Додавање израчунатог поља у изведену табелу

Погледајмо како додати израчунато поље изведене табеле у постојећу изведену табелу.

Претпоставимо да имате изведену табелу као што је приказано испод и желите да израчунате маржу профита за сваког продавца:

Ево корака за додавање израчунатог поља изведене табеле:

• Изаберите било коју ћелију у изведеној табели.
• Идите на Алатке изведене табеле -> Анализа -> Израчуни -> Поља, ставке и скупови.
• Из падајућег менија изаберите Израчунато поље.
• У дијалогу Уметање израчунатих датотека:
  • Дајте му име тако што ћете га унијети у поље Име.
  • У пољу Формула креирајте формулу коју желите за израчунато поље. Имајте на уму да можете бирати између назива поља наведених испод њега. У овом случају, формула је „= добит/ продаја“. Можете ручно да унесете називе поља или двапут кликните на назив поља који је наведен у пољу Поља.
• Кликните на Додај и затворите оквир за дијалог.

Чим додате израчунато поље, оно ће се појавити као једно од поља у листи поља изведене табеле.

Сада ово израчунато поље можете користити као било које друго поље изведене табеле (имајте на уму да не можете користити израчунато поље изведене табеле као филтер извештаја или исечак).

Као што сам већ поменуо, предност коришћења израчунатог поља изведене табеле је то што можете променити структуру изведене табеле и она ће се аутоматски прилагодити.

На пример, ако превучем и испустим регион у области редова, добићете резултат као што је приказано испод, где се вредност профитне марже пријављује за продавце, као и за регион.

У горњем примеру, користио сам једноставну формулу (= Добит/Продаја) за уметање израчунатог поља. Међутим, можете користити и неке напредне формуле.

Пре него што вам покажем пример коришћења напредне формуле за креирање поља за израчунавање изведене табеле, ево неколико ствари које морате знати:

• НЕ МОЖЕТЕ да користите референце или именоване опсеге при креирању израчунатог поља изведене табеле. То би искључило многе формуле као што су ВЛООКУП, ИНДЕКС, ОФФСЕТ итд. Међутим, можете користити формуле које могу радити без референци (као што су СУМ, ИФ, ЦОУНТ итд.).
• У формули можете користити константу. На пример, ако желите да знате предвиђену продају за коју се предвиђа раст од 10%, можете користити формулу = Продаја*1,1 (где је 1,1 константна).
• Редослед првенства се следи у формули која чини израчунато поље. Као најбољу праксу, користите заграде како бисте били сигурни да не морате да се сећате редоследа првенства.

Погледајмо сада пример коришћења напредне формуле за креирање израчунатог поља.

Претпоставимо да имате скуп података као што је приказано у наставку и морате да прикажете предвиђену вредност продаје у изведеној табели.

За предвиђену вриједност, морате користити повећање продаје од 5% за велике малопродаје (продаја изнад 3 милиона) и 10% повећање продаје за мале и средње трговце (продаја испод 3 милиона).

Напомена: Бројеви продаје овде су лажни и коришћени су за илустрацију примера у овом водичу.

Ево како то учинити:

• Изаберите било коју ћелију у изведеној табели.
• Идите на Алатке изведене табеле -> Анализа -> Израчуни -> Поља, ставке и скупови.
• Из падајућег менија изаберите Израчунато поље.
• У дијалогу Уметање израчунатих датотека:
  • Дајте му име тако што ћете га унијети у поље Име.
  • У пољу Формула користите следећу формулу: = ИФ (Регион = ”Југ”, продаја *1,05, продаја *1,1)
• Кликните на Додај и затворите оквир за дијалог.

Ово додаје нову колону у изведену табелу са вредношћу предвиђања продаје.

Кликните овде за преузимање скупа података.

Проблем са израчунатим пољима изведене табеле

Израчунато поље је невероватна карактеристика која заиста повећава вредност ваше изведене табеле помоћу израчунавања поља, а истовремено одржава све скалабилним и управљивим.

Међутим, постоји проблем са израчунатим пољима изведене табеле које морате знати пре него што га употребите.

Претпоставимо да имам изведену табелу као што је приказано испод где сам користио израчунато поље да добијем прогнозиране продајне бројеве.

Имајте на уму да међузбир и велики износи нису тачни.

Иако би ово требало да дода вредност појединачне прогнозе продаје за сваког продавца, у стварности она следи исту израчунату формулу поља коју смо креирали.

Дакле, за Соутх Тотал, док би вриједност требала бити 22.824.000, Соутх Тотал погрешно извјештава о 22.287.000. То се дешава јер користи формулу 21,225,800*1,05 да добије вредност.

Нажалост, не можете то исправити.

Најбољи начин да се то реши било би уклањање међузбира и укупних збројева из изведене табеле.

Такође можете проћи кроз нека иновативна решења која је Дебра показала да решава овај проблем.

Како изменити или избрисати израчунато поље изведене табеле?

Када креирате израчунато поље изведене табеле, можете изменити формулу или је избрисати помоћу следећих корака:

• Изаберите било коју ћелију у изведеној табели.
• Идите на Алатке изведене табеле -> Анализа -> Израчуни -> Поља, ставке и скупови.
• Из падајућег менија изаберите Израчунато поље.
• У пољу Име кликните на падајућу стрелицу (мала стрелица надоле на крају поља).
• Са листе изаберите израчунато поље које желите да избришете или измените.
• Промените формулу у случају да желите да је измените или кликните на Избриши у случају да желите да је избришете.

Како добити листу свих израчунатих формула поља?

Ако креирате много поља за израчунавање изведене табеле, не брините о томе да пратите формулу која се користи у свакој од њих.

Екцел вам омогућава да брзо креирате листу свих формула које се користе за креирање израчунатих поља.

Ево корака за брзо добијање листе формула свих израчунатих поља:

• Изаберите било коју ћелију у изведеној табели.
• Идите на Алатке изведене табеле -> Анализа -> Поља, ставке и скупови -> Формуле листе.

Чим кликнете на Формуле листе, Екцел би аутоматски уметнуо нови радни лист који ће садржати детаље о свим израчунатим пољима/ставкама које сте користили у изведеној табели.

Ово може бити заиста користан алат ако морате послати свој рад клијенту или га поделити са својим тимом.

Такође би вам могли бити корисни следећи водичи за изведену табелу:

• Припрема изворних података за изведену табелу.
• Коришћење резача у Екцел заокретној табели: Водич за почетнике.
• Како груписати датуме у изведеним табелама у Екцелу.
• Како груписати бројеве у изведеној табели у програму Екцел.
• Како филтрирати податке у изведеној табели у програму Екцел.
• Како заменити празне ћелије нулама у Екцел заокретним табелама.
• Како применити условно обликовање у изведеној табели у програму Екцел.
• Заокретна кеш меморија у програму Екцел - шта је то и како је најбоље користити?