Екцел формуле не раде: могући разлози и како то поправити!

Ако радите са формулама у Екцелу, пре или касније ћете наићи на проблем где Екцел формуле уопште не функционишу (или дају погрешан резултат).

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

Али пошто живимо у свету који следи Паретов принцип, ако проверите неке уобичајене проблеме, вероватно ће решити 80% (или можда чак 90% или 95% проблема у којима формуле не функционишу у Екцелу).

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

Па да почнемо!

Нетачна синтакса функције

Дозволите ми да почнем тако што ћу указати на очигледно.

Свака функција у Екцелу има одређену синтаксу - као што је број аргумената које може узети или врста аргумената које може прихватити.

У многим случајевима разлог зашто ваше Екцел формуле не функционишу или даје погрешан резултат могао би бити нетачан аргумент (или недостајући аргументи).

На пример, функција ВЛООКУП узима три обавезна аргумента и један опциони аргумент.

Ако наведете погрешан аргумент или не наведете опциони аргумент (тамо где је потребно да формула ради), то ће вам дати погрешан резултат.

На пример, претпоставимо да имате скуп података као што је приказано испод где треба да знате оцену оцене на испиту 2 (у ћелији Ф2).

Ако користим доњу формулу, добићу погрешан резултат, јер користим погрешну вредност у трећем аргументу (онај који тражи број индекса колоне).

= ВЛООКУП (А2, А2: Ц6,2, ФАЛСЕ)

У овом случају формула израчунава (враћа вредност), али резултат није тачан (уместо оцене на испиту 2, даје резултат на испиту 1).

Још један пример где морате бити опрезни у вези са аргументима је када користите ВЛООКУП са приближним подударањем.

Пошто морате да употребите опционални аргумент да бисте навели где желите да ВЛООКУП изврши потпуно или приближно подударање, ако не наведете ово (или употребите погрешан аргумент), можете изазвати проблеме.

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

Када користим доњу формулу ВЛООКУП, то ми даје грешку за нека имена.

= ВЛООКУП (Е2, $ А $ 2: $ Ц $ 6,2)

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

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

Иако сам узео пример функције ВЛООКУП, у овом случају ово је нешто што се може применити на многе Екцел формуле које имају и опционалне аргументе.

Такође прочитајте: Идентификујте грешке помоћу исправљања грешака у Екцел формули

Додатни простори изазивају неочекиване резултате

Водећи, последњи размаци се тешко откривају и могу изазвати проблеме када користите ћелију која их има у формулама.

На пример, у доњем примеру, ако покушам да помоћу ВЛООКУП -а дохватим резултат за Марка, то ми даје грешку #Н/А (грешка није доступна).

Иако видим да је формула тачна и да је име „Означи“ очигледно да постоји листа, оно што не видим је да у ћелији постоји задњи простор који има назив (у ћелији Д2).

Екцел не сматра садржај ове две ћелије истим и стога сматра да се он не подудара при преузимању вредности помоћу ВЛООКУП -а (или би то могла бити било која друга формула за претраживање).

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

То можете учинити на било који од следећих начина:

1. Очистите ћелију и уклоните све почетне/задње размаке пре него што је употребите у формулама
2. Користите функцију ТРИМ у формули како бисте били сигурни да се водећи/задњи/двоструки размаци занемарују.

У горњем примеру уместо тога можете користити доњу формулу да бисте се уверили да ради.

= ВЛООКУП (ТРИМ (Д2), $ А $ 2: $ Б $ 6,2,0)

Иако сам узео пример ВЛООКУП -а, ово је такође уобичајен проблем при раду са ТЕКСТ функцијама.

На пример, ако користим функцију ЛЕН за бројање укупног броја знакова у ћелији, ако постоје водећи или крајњи размаци, они би се такође пребројали и дали погрешан резултат.

Однеси / Како поправити: Ако је могуће, очистите податке уклањањем водећих/задњих или двоструких размака пре него што их употребите у формулама. Ако не можете да промените оригиналне податке, употребите функцију ТРИМ у формулама да бисте се побринули за то.

Коришћење ручног израчунавања уместо аутоматског

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

Екцел има два начина израчунавања - Аутоматиц и Упутство.

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

Ово је окружење на које смо сви навикли.

У аутоматском подешавању, кад год унесете било какву промену на радном листу (као што је унос нове формуле за чак и неки текст у ћелији), Екцел аутоматски поново израчунава све (да, све).

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

То се углавном ради када имате тешку Екцел датотеку са пуно података и формула. У таквим случајевима можда нећете желети да Екцел поново израчуна све када направите мале измене (јер може проћи неколико секунди или чак минута) да се ово поновно израчунавање заврши.

Ако омогућите ручно израчунавање, Екцел неће израчунати ако то не приморате.

Због тога можете помислити да ваша формула није прорачуната.

Све што требате учинити у овом случају је или вратити прорачун на аутоматско или присилити поновно израчунавање притиском на типку Ф9.

У наставку су наведени кораци за промену израчунавања из ручног у аутоматско:

1. Кликните на картицу Формула
2. Кликните на Опције израчунавања
3. Изаберите Аутоматски

Важно: У случају да мењате израчунавање из ручног у аутоматско, добра је идеја да направите резервну копију радне свеске (само у случају да због тога ваша радна свеска виси или Екцел падне)

Однеси / Како поправити: Ако приметите да ваше формуле не дају очекивани резултат, покушајте нешто једноставно у било којој ћелији (на пример додавање 1 постојећој формули. Једном када идентификујете проблем као онај у коме је потребно променити начин израчунавања, извршите прорачун силе помоћу Ф9.

Брисање редова/колона/ћелија које воде до #РЕФ! Грешка

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

Када се то догоди, понекад Екцел сам прилагођава референцу и брине се да формуле раде добро.

И понекад… не могу.

Срећом, један јасан показатељ који добијате када се формуле прекину при брисању ћелија/редова/колона је #РЕФ! грешка у ћелијама. Ово је грешка референце која вам говори да постоји проблем са референцама у формули.

Дозволите ми да вам покажем на шта мислим користећи пример.

Испод сам користио формулу СУМ за додавање ћелија А2: А6.

Сада, ако избришем било коју од ових ћелија/редова, формула СУМ ће вратити #РЕФ! грешка. То се догађа јер када сам избрисао ред, формула не зна на шта се сада позива.

Можете видети да је трећи аргумент у формули постао #РЕФ! (што се раније односило на ћелију коју смо избрисали).

Однеси / Како поправити: Пре него што избришете све податке који се користе у формулама, уверите се да нема грешака након брисања. Такође се препоручује да редовно правите резервну копију свог рада како бисте били сигурни да увек имате на шта да се ослоните.

Нетачно постављање заграда (БОДМАС)

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

У неким случајевима можда имате заграде на погрешном месту, што вам може дати погрешан резултат или грешку.

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

На пример, претпоставимо да имате следећу формулу:

=5+10*50

У горњој формули резултат је 505 јер Екцел прво врши множење, а затим сабирање (пошто постоји ред првенства када су оператори у питању).

Ако желите да прво изврши сабирање, а затим и множење, морате користити доњу формулу:

=(5+10)*50

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

Такође, у случају да вас занима, испод је редослед приоритета за различите операторе који се често користе у формулама:

Оператер	Опис	Редослед
: (дебело црево)	Домет	1
(један размак)	Раскрсница	2
, (зарез)	унија	3
-	Негација (као у -1)	4
%	Проценат	5
^	Експоненција	6
* и /	Множење и дељење	7
+ и -	Сабирање и одузимање	8
&	Цонцатненатион	9
= =	Поређење	10

Однеси / Како поправити: Увек користите заграде како бисте избегли забуну, чак и ако знате редослед приоритета и правилно га користите. Заграђивање олакшава ревизију формула.

Нетачна употреба апсолутних/релативних референци ћелија

Када копирате и залепите формуле у Екцел, он аутоматски прилагођава референце. Понекад то управо желите (углавном када копирате формуле у колону), а понекад не желите да се то догоди.

Апсолутна референца је када поправите референцу ћелије (или референцу опсега) тако да се не промени када копирате и залепите формуле, а релативна референца се мења.

Овде можете прочитати више о апсолутним, релативним и мешовитим референцама.

Можда ћете добити погрешан резултат у случају да заборавите да промените референцу на апсолутну (или обрнуто). То ми се често дешава када користим формуле за тражење.

Дозволите ми да вам покажем пример.

Испод имам скуп података у којем желим да добијем резултат на испиту 1 за имена у колони Е (једноставан случај употребе ВЛООКУП -а)

Испод је формула коју користим у ћелији Ф2, а затим копирам у све ћелије испод ње:

= ВЛООКУП (Е2, А2: Б6,2,0)

Као што видите да ова формула у неким случајевима даје грешку.

То се дешава зато што нисам закључао аргумент поља табеле - то је А2: Б6 у ћелији Ф2, док је требало $ А $ 2: $ Б $ 6

Имајући ове знакове долара испред броја реда и абецеде колоне у референци ћелије, приморавам Екцел да одржава ове референце ћелија фиксним. Дакле, чак и када препишем ову формулу, низ табела ће се и даље односити на А2: Б6

Про врх: Да бисте релативну референцу претворили у апсолутну, изаберите ту референцу ћелије унутар ћелије и притисните тастер Ф4. Приметили бисте да се то мења додавањем знакова долара. Можете наставити да притискате Ф4 док не добијете референцу коју желите.

Нетачно упућивање на називе листова / радне свеске

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

На пример, ако желим да се позовем на ћелију А1 у листу 2, референца би била = Лист2! А1 (где се иза назива листа налази ускличник)

А у случају да у називу листа постоји више речи (рецимо да су то примери података), референца би била = 'Примери података'! А1 (где је име затворено под наводницима иза којих следи знак узвика).

У случају да се позивате на спољну радну свеску (рецимо да се позивате на ћелију А1 у „Пример листа“ у радној свесци под називом „Пример радне свеске“), референца ће бити приказана у наставку:

= '[Пример радне свеске.клск] Пример листа'! $ А $ 1

А у случају да затворите радну свеску, референца би се променила тако да обухвати целу путању радне свеске (као што је приказано испод):

= 'Ц: \ Усерс \ сумит \ Десктоп \ [Екампле Воркбоок.клск] Пример листа'! $ А $ 1

У случају да промените назив радне свеске или радног листа на који се формула односи, добићете #РЕФ! грешка.

Такође прочитајте: Како пронаћи спољне везе и референце у Екцелу

Цирцулар Референце

Кружна референца је када упућујете (директно или индиректно) на исту ћелију у којој се израчунава формула.

Испод је једноставан пример, где користим формулу СУМ у ћелији А4 док је користим у самом прорачуну.

= ЗБИР (А1: А4)

Иако вам Екцел приказује упит који вас обавештава о кружној референци, то неће учинити за сваку инстанцу. А ово вам може дати погрешан резултат (без икаквог упозорења).

У случају да сумњате да је кружна референца у игри, можете проверити које ћелије је имају.

Да бисте то урадили, кликните на картицу Формула и у групи „Ревизија формуле“ кликните на падајућу икону Провера грешака (мала стрелица окренута надоле).

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

Ћелије форматиране као текст

Ако се нађете у ситуацији да чим унесете формулу као хит ентер, видите формулу уместо вредности, то је јасан случај да се ћелија форматира као текст.

Када се ћелија форматира као текст, она формулу сматра текстуалним низом и приказује је такву каква јесте.

Не приморава га да израчуна и даје резултат.

И има лако решење.

1. Промените формат у „Опште“ из „Текст“ (налази се на картици Почетна у групи Бројеви)
2. Идите у ћелију која има формулу, уђите у режим уређивања (користите Ф2 или двапут кликните на ћелију) и притисните Ентер

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

Ако постоји апостроф, можете га једноставно уклонити.

Аутоматски претварање текста у датуме

Екцел има ову лошу навику претварања које изгледа као датум у стварни датум. На пример, ако унесете 1/1, Екцел ће то претворити у 01. јануар текуће године.

У неким случајевима ово може бити управо оно што желите, а у неким случајевима то може деловати против вас.

А пошто Екцел складишти вредности датума и времена као бројеве, чим унесете 1/1, претвара их у број који представља 1. јануар текуће године. У мом случају, када то урадим, претвара се у број 43831 (за 01.01.2020.).

Ово би могло да забрља ваше формуле ако ове ћелије користите као аргумент у формули.

Како ово поправити?

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

Испод су кораци за промену формата у текст тако да текст не претвара аутоматски у датуме:

1. Изаберите ћелије/опсег у којем желите да промените формат
2. Кликните на картицу Почетна
3. У групи Број кликните на падајући изборник Обликовање
4. Кликните на Тект

Сада, кад год унесете било шта у изабране ћелије, то ће се сматрати текстом, а не аутоматски мењати.

Напомена: Горе наведени кораци функционисаће само за податке унете након промене форматирања. Неће променити текст који је конвертован у датум пре него што сте извршили ову промену обликовања.

Још један пример где ово може бити заиста фрустрирајуће је када унесете текст/број који има водеће нуле. Екцел аутоматски уклања ове водеће нуле јер их сматра бескорисним. На пример, ако унесете 0001 у ћелију, Екцел ће је променити у 1. У случају да желите да задржите ове водеће нуле, користите горе наведене кораке.

Скривени редови/колоне могу дати неочекиване резултате

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

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

У ћелији Ц12 користио сам функцију СУМ да добијем укупну вредност продаје за све наведене записе.

Засада је добро!

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

И ту је проблем - формула у ћелији Ц12 и даље показује исти резултат - тј. Збир свих записа.

Као што сам рекао, формула не даје погрешан резултат. У ствари, функција СУМ ради сасвим добро.

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

Функција СУМ не може узети у обзир филтриране податке и дати вам резултат за све ћелије (скривене или видљиве). Ако желите само да добијете збир/број/просек видљивих ћелија, користите функције СУБТОТАЛ или АГГРЕГАТЕ.

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

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

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

Надам се да вам је овај водич био користан!