Excel formule ne delujejo: možni razlogi in kako to odpraviti!

Če delate s formulami v Excelu, boste slej ko prej naleteli na težavo, pri kateri formule Excel sploh ne delujejo (ali dajejo napačen rezultat).

Čeprav bi bilo super, če bi obstajalo le nekaj možnih razlogov za nepravilno delovanje formul. Na žalost je preveč stvari, ki bi lahko šle narobe (in se pogosto zgodijo).

Ker pa živimo v svetu, ki sledi Paretovemu načelu, bo to, če preverite nekaj pogostih težav, verjetno rešilo 80% (ali morda celo 90% ali 95% težav, pri katerih formule ne delujejo v Excelu).

In v tem članku bom izpostavil tista pogosta vprašanja, ki so verjetno vzrok za vas Formule Excel ne delujejo.

Pa začnimo!

Nepravilna sintaksa funkcije

Naj najprej opozorim na očitno.

Vsaka funkcija v Excelu ima določeno skladnjo - na primer število argumentov, ki jih lahko sprejme, ali vrsto argumentov, ki jih lahko sprejme.

V mnogih primerih je lahko razlog, da vaše formule Excel ne delujejo ali dajejo napačen rezultat, napačen argument (ali manjkajoči argumenti).

Funkcija VLOOKUP na primer sprejme tri obvezne argumente in en izbirni argument.

Če navedete napačen argument ali ne navedete izbirnega argumenta (kjer je potrebno, da formula deluje), boste dobili napačen rezultat.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, kjer morate poznati rezultat ocene na izpitu 2 (v celici F2).

Če uporabim spodnjo formulo, bom dobil napačen rezultat, ker uporabljam napačno vrednost v tretjem argumentu (tisti, ki zahteva številko stolpca).

= VLOOKUP (A2, A2: C6,2, FALSE)

V tem primeru formula izračuna (saj vrne vrednost), vendar je rezultat napačen (namesto ocene na izpitu 2 poda oceno na izpitu 1).

Drug primer, pri katerem morate biti previdni pri argumentih, je, ko uporabljate VLOOKUP s približnim ujemanjem.

Ker morate uporabiti neobvezen argument, da določite, kje želite, da VLOOKUP izvede natančno ali približno ujemanje, lahko to, če tega ne navedete (ali uporabite napačen argument), povzroči težave.

Spodaj je primer, kjer imam podatke o ocenah za nekatere študente in želim izvleči ocene iz izpita 1 za študente v tabeli na desni.

Ko uporabim spodnjo formulo VLOOKUP, se pri nekaterih imenih prikaže napaka.

= VLOOKUP (E2, $ A $ 2: $ C $ 6,2)

To se zgodi, ker nisem navedel zadnjega argumenta (ki se uporablja za določitev, ali naj se izvede natančno ali približno ujemanje). Če ne navedete zadnjega argumenta, se samodejno privzeto samodejno približa.

Ker smo morali v tem primeru narediti natančno ujemanje, formula vrne napako za nekatera imena.

Čeprav sem vzel primer funkcije VLOOKUP, je v tem primeru to mogoče uporabiti za številne formule Excel, ki imajo tudi izbirne argumente.

Preberite tudi: Prepoznavanje napak z odpravljanjem napak v formuli Excel

Dodatni prostori, ki povzročajo nepričakovane rezultate

Vodilne in zaledne presledke je težko ugotoviti in lahko povzročijo težave, če uporabite celico, ki ima te v formulah.

Na primer, v spodnjem primeru, če poskušam uporabiti VLOOKUP za pridobitev ocene za Mark, mi prikaže napako #N/A (napaka ni na voljo).

Čeprav vidim, da je formula pravilna in da je ime 'Mark' očitno na seznamu, ne vidim, da je v celici zadnji prostor z imenom (v celici D2).

Excel ne meni, da je vsebina teh dveh celic enaka, zato meni, da je neskladje pri pridobivanju vrednosti z uporabo VLOOKUP (ali pa je to katera koli druga iskalna formula).

Če želite odpraviti to težavo, morate odstraniti te dodatne presledke.

To lahko storite na enega od naslednjih načinov:

1. Pred uporabo v formulah očistite celico in odstranite vse začetne/zaključne presledke
2. Uporabite funkcijo TRIM v formuli in se prepričajte, da so prezrti/zadnji/dvojni presledki prezrti.

V zgornjem primeru lahko uporabite spodnjo formulo in se prepričate, da deluje.

= VLOOKUP (TRIM (D2), $ A $ 2: $ B $ 6,2,0)

Čeprav sem vzel primer VLOOKUP, je to tudi pogosta težava pri delu s funkcijami TEXT.

Na primer, če uporabljam funkcijo LEN za štetje skupnega števila znakov v celici, če obstajajo začetni ali zadnji presledki, bi se ti tudi šteli in dali napačen rezultat.

Odnesi / Kako popraviti: Če je mogoče, očistite svoje podatke tako, da odstranite vse začetne/zadnje ali dvojne presledke, preden jih uporabite v formulah. Če izvirnih podatkov ne morete spremeniti, za to uporabite funkcijo TRIM v formulah.

Uporaba ročnega izračuna namesto samodejnega

Ta nastavitev vas lahko zmeša (če ne veste, da je to tisto, kar povzroča vse težave).

Excel ima dva načina izračuna - Samodejno in Priročnik.

Privzeto je samodejni način omogočen, kar pomeni, da v primeru, da uporabim formulo ali spremenim obstoječe formule, samodejno (in takoj) izvede izračun in mi da rezultat.

To je nastavitev, ki smo je vsi vajeni.

V samodejni nastavitvi, kadar koli spremenite delovni list (na primer vnesete novo formulo za celo besedilo v celici), Excel samodejno preračuna vse (da, vse).

Toda v nekaterih primerih ljudje omogočijo nastavitev ročnega izračuna.

To se večinoma zgodi, če imate težko datoteko Excel z veliko podatki in formulami. V takih primerih morda ne želite, da Excel pri vseh majhnih spremembah preračuna vse (saj lahko traja nekaj sekund ali celo minut), da se to preračunavanje dokonča.

Če omogočite ročni izračun, Excel ne bo izračunal, razen če ga prisilite.

Zaradi tega boste morda pomislili, da vaša formula ni izračunana.

V tem primeru morate samo nastaviti izračun nazaj na samodejno ali prisiliti ponovni izračun s pritiskom na tipko F9.

Spodaj so navedeni koraki za spremembo izračuna iz ročnega v samodejni:

1. Kliknite zavihek Formula
2. Kliknite Možnosti izračuna
3. Izberite Samodejno

Pomembno: v primeru, da izračun spremenite iz ročnega v samodejni, je dobro, da ustvarite varnostno kopijo delovnega zvezka (v primeru, da zaradi tega vaš delovni zvezek visi ali se Excel zruši)

Odnesi / Kako popraviti: Če opazite, da vaše formule ne dajo pričakovanega rezultata, poskusite v kateri koli celici nekaj preprostega (na primer dodajte obstoječi formuli 1. Ko težavo prepoznate kot tisto, pri kateri je treba spremeniti način izračuna, naredite izračun sile z uporabo F9.

Brisanje vrstic/stolpcev/celic, ki vodijo do #REF! Napaka

Ena od stvari, ki lahko uničujoče vpliva na vaše obstoječe formule v Excelu, je, ko izbrišete katero koli vrstico/stolpec, ki je bil uporabljen pri izračunih.

Ko se to včasih zgodi, Excel sam prilagodi sklic in poskrbi, da formule dobro delujejo.

In včasih… ne more.

Na srečo je en jasen znak, ki ga dobite, ko se formule zlomijo pri brisanju celic/vrstic/stolpcev, #REF! napaka v celicah. To je referenčna napaka, ki vam pove, da je pri referencah v formuli nekaj težav.

Naj vam na primeru pokažem, kaj mislim.

Spodaj sem uporabil formulo SUM za dodajanje celic A2: A6.

Če izbrišem katero koli od teh celic/vrstic, bo formula SUM vrnila #REF! napaka. To se zgodi, ker ko sem izbrisal vrstico, formula ne ve, na kaj naj se sklicuje.

Vidite lahko, da je tretji argument v formuli postal #REF! (ki se je prej nanašalo na celico, ki smo jo izbrisali).

Odnesi / Kako popraviti: Preden izbrišete vse podatke, ki se uporabljajo v formulah, se prepričajte, da po brisanju ni napak. Priporočljivo je tudi, da redno ustvarjate varnostno kopijo svojega dela, da se prepričate, da imate vedno na čem nasloniti.

Nepravilna postavitev oklepajev (BODMAS)

Ko vaše formule postajajo vse večje in bolj zapletene, je dobro, da uporabite oklepaje, da boste popolnoma jasni, kateri del spada skupaj.

V nekaterih primerih imate lahko oklepaje na napačnem mestu, kar lahko povzroči napačen rezultat ali napako.

V nekaterih primerih je priporočljivo uporabiti oklepaje, da zagotovite, da formula razume, kaj je treba najprej združiti in izračunati.

Recimo, da imate naslednjo formulo:

=5+10*50

V zgornji formuli je rezultat 505, saj Excel najprej pomnoži in nato sestavi (saj pri operaterjih obstaja vrstni red prednosti).

Če želite, da najprej izvede seštevanje in nato množenje, morate uporabiti spodnjo formulo:

=(5+10)*50

V nekaterih primerih vam lahko vrstni red ustreza, vendar je še vedno priporočljivo, da uporabite oklepaje, da se izognete zmedi.

Če vas zanima, je spodaj vrstni red različnih operaterjev, ki se pogosto uporabljajo v formulah:

Operater	Opis	Vrstni red
: (debelo črevo)	Domet	1
(enojni presledek)	Presečišče	2
, (vejica)	sindikat	3
-	Negacija (kot v -1)	4
%	Odstotek	5
^	Eksponentiranje	6
* in /	Množenje in deljenje	7
+ in -	Seštevanje in odštevanje	8
&	Prikazovanje	9
= =	Primerjava	10

Odnesi / Kako popraviti: Vedno uporabite oklepaje, da se izognete zmedi, tudi če poznate prednostni vrstni red in ga pravilno uporabljate. Z oklepaji je lažje revidirati formule.

Nepravilna uporaba absolutnih/relativnih referenc celic

Ko kopirate in prilepite formule v Excelu, samodejno prilagodi sklice. Včasih si želite točno tega (večinoma, ko formule kopirate in prilepite v stolpec), včasih pa ne želite, da se to zgodi.

Absolutna referenca je, ko določite sklic na celico (ali referenco obsega), tako da se ne spremeni, ko kopirate in prilepite formule, relativni sklic pa se spremeni.

Več o absolutnih, relativnih in mešanih referencah lahko preberete tukaj.

Morda boste dobili napačen rezultat, če pozabite spremeniti sklic na absolutnega (ali obratno). To se mi pogosto dogaja, ko uporabljam iskalne formule.

Naj vam pokažem primer.

Spodaj imam nabor podatkov, v katerem želim pridobiti rezultat na izpitu 1 za imena v stolpcu E (preprost primer uporabe VLOOKUP)

Spodaj je formula, ki jo uporabim v celici F2 in nato kopiram v vse celice pod njo:

= VLOOKUP (E2, A2: B6,2,0)

Kot lahko vidite, da ta formula v nekaterih primerih povzroči napako.

To se zgodi, ker nisem zaklenil argumenta matričnega polja - je A2: B6 v celici F2, medtem ko bi morala biti $ A $ 2: $ B $ 6

Ker imam te znake za dolar pred številko vrstice in abecedo stolpca v sklicu na celico, prisilim Excel, da te sklice na celice ohrani fiksne. Torej, tudi ko kopiram to formulo navzdol, se bo matrika še naprej sklicevala na A2: B6

Nasvet za profesionalce: Če želite relativno referenco pretvoriti v absolutno, izberite to referenco celice v celici in pritisnite tipko F4. Opazili bi, da se spremeni z dodajanjem znakov dolarja. Še naprej lahko pritiskate tipko F4, dokler ne dobite želene reference.

Nepravilno sklicevanje na imena listov / delovnih zvezkov

Ko se v formuli sklicujete na druge liste ali delovne zvezke, morate upoštevati določeno obliko. Če je oblika napačna, se prikaže napaka.

Na primer, če se želim sklicevati na celico A1 v listu 2, bi bila referenca = List2! A1 (kjer je za imenom lista klicaj)

Če je v imenu lista več besed (recimo, da so to primeri podatkov), bi bila referenca = 'Primer podatkov'! A1 (kjer je ime zaprto z enojnimi narekovaji, ki jim sledi klicaj).

Če se sklicujete na zunanji delovni zvezek (recimo, da se sklicujete na celico A1 v »Primer lista« v delovnem zvezku z imenom »Primer delovnega zvezka«), bo sklic, kot je prikazano spodaj:

= '[Primer delovnega zvezka.xlsx] Primer lista'! ​​$ A $ 1

Če zaprete delovni zvezek, se sklic spremeni tako, da vključuje celotno pot delovnega zvezka (kot je prikazano spodaj):

= 'C: \ Users \ sumit \ Desktop \ [Example Workbook.xlsx] Primer lista'! ​​$ A $ 1

Če na koncu spremenite ime delovnega zvezka ali delovnega lista, na katerega se formula nanaša, vam bo dal #REF! napaka.

Preberite tudi: Kako najti zunanje povezave in reference v Excelu

Krožne reference

Krožna referenca je, če se nanašate (neposredno ali posredno) na isto celico, v kateri se izračuna formula.

Spodaj je preprost primer, kjer uporabljam formulo SUM v celici A4, medtem ko jo uporabljam v samem izračunu.

= SUM (A1: A4)

Čeprav vam Excel prikaže poziv, ki vas obvesti o krožni referenci, tega ne bo storil za vsak primerek. To pa lahko povzroči napačen rezultat (brez opozorila).

Če sumite, da je v igri krožna referenca, lahko preverite, katere celice jo imajo.

Če želite to narediti, kliknite zavihek Formula in v skupini »Revizija formul« kliknite spustno ikono Preverjanje napak (majhna puščica navzdol).

Kazalec miške premaknite na možnost Krožna referenca in prikazala se bo celica, ki ima težavo s krožno referenco.

Celice, oblikovane kot besedilo

Če se znajdete v situaciji, ko takoj, ko formulo vnesete kot hit enter, vidite formulo namesto vrednosti, je to jasen primer, da je celica oblikovana kot besedilo.

Ko je celica oblikovana kot besedilo, upošteva formulo kot besedilni niz in jo prikaže kot je.

Ne sili ga v izračun in podajanje rezultata.

In ima enostavno rešitev.

1. Spremenite obliko v »Splošno« iz »Besedilo« (to je na zavihku Domov v skupini Številke)
2. Pojdite v celico s formulo, pojdite v način urejanja (uporabite F2 ali dvokliknite celico) in pritisnite Enter

Če zgornji koraki ne rešijo težave, je treba preveriti, ali ima celica na začetku apostrof. Veliko ljudi doda apostrof za pretvorbo formul in številk v besedilo.

Če obstaja apostrof, ga lahko preprosto odstranite.

Besedilo se samodejno pretvori v datume

Excel ima to slabo navado pretvorbe, ki je videti kot datum v dejanski datum. Če na primer vnesete 1/1, jo Excel pretvori v 01. januar tekočega leta.

V nekaterih primerih je to točno tisto, kar želite, v nekaterih primerih pa lahko deluje proti vam.

Ker Excel shrani vrednosti datuma in časa kot številke, jih takoj, ko vnesete 1/1, pretvori v številko, ki predstavlja 1. januar tekočega leta. V mojem primeru, ko to naredim, se pretvori v številko 43831 (za 01.01.2020).

Če te celice uporabljate kot argument v formuli, bi to lahko povzročilo zmedo.

Kako to popraviti?

Spet ne želimo, da nam Excel samodejno izbere obliko, zato moramo sami jasno določiti obliko.

Spodaj so navedeni koraki za spremembo oblike v besedilo, da se besedilo ne pretvori samodejno v datume:

1. Izberite celice/obseg, kjer želite spremeniti obliko
2. Kliknite zavihek Domov
3. V skupini Število kliknite spustni meni Oblika
4. Kliknite na Besedilo

Zdaj, kadar koli vnesete karkoli v izbrane celice, bi to veljalo za besedilo in se ne bi samodejno spremenilo.

Opomba: Zgornji koraki bodo delovali samo za podatke, vnesene po spremembi oblikovanja. Ne bo spremenilo nobenega besedila, ki je bilo pretvorjeno v datum, preden ste spremenili oblikovanje.

Drug primer, kjer je to lahko zelo frustrirajuće, je, ko vnesete besedilo/številko z vodilnimi ničlami. Excel samodejno odstrani te vodilne ničle, saj se jim zdijo neuporabne. Če na primer v celico vnesete 0001, jo bo Excel spremenil v 1. Če želite ohraniti te vodilne ničle, uporabite zgornje korake.

Skrite vrstice/stolpci lahko dajo nepričakovane rezultate

Ne gre za formulo, ki vam daje napačen rezultat, ampak za uporabo napačne formule.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želim dobiti vsoto vseh vidnih celic v stolpcu C.

V celici C12 sem uporabil funkcijo SUM, da dobim skupno prodajno vrednost za vse te dane zapise.

Zaenkrat tako dobro!

Zdaj za stolpec postavk uporabim filter, ki prikazuje le zapise o prodaji tiskalnikov.

In tu je problem - formula v celici C12 še vedno prikazuje enak rezultat - torej vsoto vseh zapisov.

Kot sem rekel, formula ne daje napačnega rezultata. Pravzaprav funkcija SUM deluje v redu.

Težava je v tem, da smo tukaj uporabili napačno formulo.

Funkcija SUM ne more upoštevati filtriranih podatkov in vam dati rezultat za vse celice (skrite ali vidne). Če želite dobiti samo vsoto/število/povprečje vidnih celic, uporabite funkcije SUBTOTAL ali AGGREGATE.

Ključni odlomki - Razumeti pravilno uporabo in omejitve funkcije v Excelu.

To so nekateri pogosti vzroki, ki sem jih videl tam, kjer ste Formule v Excelu morda ne bodo delovale ali bodo dale nepričakovane ali napačne rezultate. Prepričan sem, da obstaja veliko več razlogov, da formula Excel ne deluje ali se posodobi.

Če poznate kakšen drug razlog (morda nekaj, kar vas pogosto moti), mi to sporočite v razdelku s komentarji.

Upam, da vam je bila ta vadnica koristna!