Excel -formler fungerer ikke: Mulige årsaker og hvordan du kan fikse det!

Hvis du jobber med formler i Excel, vil du før eller siden støte på problemet der Excel -formler ikke fungerer i det hele tatt (eller gi feil resultat).

Selv om det hadde vært flott hvis det bare hadde vært noen få mulige årsaker til feilfunksjonelle formler. Dessverre er det for mange ting som kan gå galt (og ofte gjør det).

Men siden vi lever i en verden som følger Pareto -prinsippet, hvis du ser etter noen vanlige problemer, vil det sannsynligvis løse 80% (eller kanskje til og med 90% eller 95% av problemene der formler ikke fungerer i Excel).

Og i denne artikkelen vil jeg markere de vanlige problemene som sannsynligvis er årsaken til din Excel -formler fungerer ikke.

Så la oss komme i gang!

Feil syntaks for funksjonen

La meg begynne med å påpeke det åpenbare.

Hver funksjon i Excel har en bestemt syntaks - for eksempel antall argumenter den kan ta eller typen argumenter den kan godta.

Og i mange tilfeller kan årsaken til at Excel -formlene ikke fungerer eller gir feil resultat være et feil argument (eller manglende argumenter).

For eksempel tar VLOOKUP -funksjonen tre obligatoriske argumenter og ett valgfritt argument.

Hvis du kommer med et feil argument eller ikke angir det valgfrie argumentet (der det er nødvendig for at formelen skal fungere), vil det gi deg et feil resultat.

Anta for eksempel at du har et datasett som vist nedenfor der du trenger å vite poengsummen til Mark i eksamen 2 (i celle F2).

Hvis jeg bruker formelen nedenfor, får jeg feil resultat, fordi jeg bruker feil verdi i det tredje argumentet (en som ber om kolonneindeksnummer).

= VISNING (A2, A2: C6,2, FALSK)

I dette tilfellet beregner formelen (ettersom den returnerer en verdi), men resultatet er feil (i stedet for poengsum i eksamen 2 gir det poengsummen i eksamen 1).

Et annet eksempel der du må være forsiktig med argumentene, er når du bruker VLOOKUP med den omtrentlige kampen.

Siden du må bruke et valgfritt argument for å angi hvor du vil at VLOOKUP skal gjøre en eksakt samsvar eller en omtrentlig samsvar, kan det føre til problemer hvis du ikke spesifiserer dette (eller bruker feil argument).

Nedenfor er et eksempel der jeg har karakterdataene for noen studenter, og jeg vil trekke ut karakterer i eksamen 1 for studentene i tabellen til høyre.

Når jeg bruker formelen VLOOKUP nedenfor, gir det meg en feil for noen navn.

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

Dette skjer ettersom jeg ikke har spesifisert det siste argumentet (som brukes til å avgjøre om jeg skal gjøre en eksakt match eller omtrentlig match). Når du ikke spesifiserer det siste argumentet, gjør det automatisk en omtrentlig samsvar som standard.

Siden vi måtte gjøre en eksakt samsvar i dette tilfellet, returnerer formelen en feil for noen navn.

Selv om jeg har tatt eksemplet på VLOOKUP -funksjonen, er dette i dette tilfellet noe som kan være aktuelt for mange Excel -formler som også har valgfrie argumenter.

Les også: Identifiser feil ved hjelp av feilsøking i Excel Formula

Ekstra mellomrom som forårsaker uventede resultater

Ledende, etterfølgende mellomrom er vanskelig å finne ut og kan forårsake problemer når du bruker en celle som har disse i formler.

For eksempel, i eksemplet nedenfor, hvis jeg prøver å bruke VLOOKUP for å hente poengsummen for Mark, gir det meg #N/A -feilen (den ikke tilgjengelige feilen).

Selv om jeg kan se at formelen er riktig og navnet "Merk" tydelig er det listen, er det jeg ikke kan se at det er et etterfølgende mellomrom i cellen som har navnet (i celle D2).

Excel anser ikke innholdet i disse to cellene som det samme, og derfor anser det det som et feil samsvar når du henter verdien ved hjelp av VLOOKUP (eller det kan være en annen oppslagsformel).

For å fikse dette problemet må du fjerne disse ekstra mellomromstegnene.

Du kan gjøre dette ved å bruke en av følgende metoder:

1. Rengjør cellen og fjern mellomrom/bakrom før du bruker den i formler
2. Bruk TRIM -funksjonen i formelen for å sikre at eventuelle mellomrom/bakre/doble mellomrom blir ignorert.

I eksemplet ovenfor kan du bruke formelen nedenfor i stedet for å sikre at den fungerer.

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

Selv om jeg har tatt VLOOKUP -eksemplet, er dette også et vanlig problem når jeg jobber med TEKST -funksjoner.

For eksempel, hvis jeg bruker LEN -funksjonen til å telle det totale antallet tegn i en celle, hvis det er ledende eller etterfølgende mellomrom, vil disse også bli talt og gi feil resultat.

Ta bort / Hvordan fikse: Rengjør dataene dine hvis det er mulig ved å fjerne mellomrom/etterfølgende eller doble mellomrom før du bruker disse i formlene. Hvis du ikke kan endre de originale dataene, bruker du TRIM -funksjonen i formler for å ta vare på dette.

Bruke manuell beregning i stedet for automatisk

Denne innstillingen kan gjøre deg gal (hvis du ikke vet at det er det som forårsaker alle problemene).

Excel har to beregningsmoduser - Automatisk og Håndbok.

Som standard er automatisk modus aktivert, noe som betyr at i tilfelle jeg bruker en formel eller foretar endringer i de eksisterende formlene, gjør den automatisk (og umiddelbart) beregningen og gir meg resultatet.

Dette er innstillingen vi alle er vant til.

I den automatiske innstillingen, når du foretar noen endringer i regnearket (for eksempel å skrive inn en ny formel for til og med litt tekst i en celle), beregner Excel automatisk alt på nytt (ja, alt).

Men i noen tilfeller aktiverer folk den manuelle beregningsinnstillingen.

Dette gjøres stort sett når du har en tung Excel -fil med mye data og formler. I slike tilfeller vil du kanskje ikke at Excel skal beregne alt på nytt når du gjør små endringer (da det kan ta noen sekunder eller minutter) før denne beregningen er fullført.

Hvis du aktiverer manuell beregning, beregnes ikke Excel med mindre du tvinger det til.

Og dette kan få deg til å tro at formelen din ikke beregner.

Alt du trenger å gjøre i dette tilfellet er enten å sette beregningen tilbake til automatisk eller tvinge en ny beregning ved å trykke på F9 -tasten.

Nedenfor er trinnene for å endre beregningen fra manuell til automatisk:

1. Klikk på Formel -fanen
2. Klikk på Beregningsalternativer
3. Velg Automatisk

Viktig: Hvis du endrer beregningen fra manuell til automatisk, er det en god idé å lage en sikkerhetskopi av arbeidsboken din (bare hvis dette får arbeidsboken til å henge eller få Excel til å krasje)

Ta bort / Hvordan fikse: Hvis du merker at formlene dine ikke gir det forventede resultatet, kan du prøve noe enkelt i en hvilken som helst celle (for eksempel å legge 1 til en eksisterende formel. Når du har identifisert problemet som det der beregningsmodus må endres, gjør du en kraftberegning ved å bruke F9.

Slette rader/kolonner/celler som fører til #REF! Feil

En av tingene som kan ha en ødeleggende effekt på de eksisterende formlene i Excel er når du sletter en rad/kolonne som har blitt brukt i beregninger.

Når dette skjer, noen ganger, justerer Excel selve referansen og sørger for at formlene fungerer bra.

Og noen ganger… jeg kan ikke.

Heldigvis er #REF! feil i cellene. Dette er en referansefeil som forteller deg at det er et problem med referansene i formelen.

La meg vise deg hva jeg mener med et eksempel.

Nedenfor har jeg brukt SUM -formelen for å legge til cellene A2: A6.

Hvis jeg sletter noen av disse cellene/radene, returnerer SUM -formelen en #REF! feil. Dette skjer fordi når jeg slettet raden, vet ikke formelen hva jeg skal referere til nå.

Du kan se at det tredje argumentet i formelen har blitt #REF! (som tidligere refererte til cellen som vi slettet).

Ta bort / Hvordan fikse: Før du sletter data som brukes i formler, må du kontrollere at det ikke er noen feil etter slettingen. Det anbefales også at du lager en sikkerhetskopi av arbeidet ditt regelmessig for å sikre at du alltid har noe å falle tilbake på.

Feil plassering av parantes (BODMAS)

Etter hvert som formlene dine begynner å bli større og mer komplekse, er det en god idé å bruke parentes for å være helt klar over hvilken del som hører sammen.

I noen tilfeller kan du ha parentesen på feil sted, noe som enten kan gi deg et feil resultat eller en feil.

Og i noen tilfeller anbefales det å bruke parentes for å sikre at formelen forstår hva som må grupperes og beregnes først.

Anta for eksempel at du har følgende formel:

=5+10*50

I formelen ovenfor er resultatet 505 ettersom Excel først gjør multiplikasjonen og deretter tillegg (ettersom det er en prioritetsrekkefølge når det gjelder operatører).

Hvis du vil at den først skal gjøre tillegg og deretter multiplikasjonen, må du bruke formelen nedenfor:

=(5+10)*50

I noen tilfeller kan prioritetsrekkefølgen fungere for deg, men det anbefales fortsatt at du bruker parentesen for å unngå forvirring.

I tilfelle du er interessert, er rekkefølgen for forskjellige operatører ofte brukt i formler nedenfor:

Operatør	Beskrivelse	Rekkefølge
: (kolon)	Område	1
(enkeltrom)	Kryss	2
, (komma)	fagforening	3
-	Negasjon (som i -1)	4
%	Prosentdel	5
^	Eksponentiering	6
* og /	Multiplikasjon og divisjon	7
+ og -	Addisjon og subtraksjon	8
&	Forsoning	9
= =	Sammenligning	10

Ta bort / Hvordan fikse: Bruk alltid parentes for å unngå forvirring, selv om du kjenner rekkefølgen og bruker den riktig. Å ha parentes gjør det lettere å revidere formler.

Feil bruk av absolutte/relative cellereferanser

Når du kopierer og limer inn formler i Excel, justeres referansene automatisk. Noen ganger er dette akkurat det du vil (for det meste når du kopierer inn formler nedover kolonnen), og noen ganger vil du ikke at dette skal skje.

En absolutt referanse er når du fikser en cellereferanse (eller områdereferanse) slik at den ikke endres når du kopierer og limer inn formler, og en relativ referanse er en som endres.

Du kan lese mer om absolutte, relative og blandede referanser her.

Du kan få et feil resultat hvis du glemmer å endre referansen til en absolutt (eller omvendt). Dette er noe som skjer ganske ofte for meg når jeg bruker oppslagsformler.

La meg vise deg et eksempel.

Nedenfor har jeg et datasett der jeg vil hente poengsummen i eksamen 1 for navnene i kolonne E (en enkel VLOOKUP -brukstilfelle)

Nedenfor er formelen jeg bruker i celle F2 og deretter kopierer til alle cellene under den:

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

Som du kan se at denne formelen gir en feil i noen tilfeller.

Dette skjer fordi jeg ikke har låst tabellmatriseargumentet - det er A2: B6 i celle F2, mens den burde vært $ A $ 2: $ B $ 6

Ved å ha disse dollartegnene før radnummeret og kolonnealfabetet i en cellereferanse, tvinger jeg Excel til å holde disse cellereferansene faste. Så selv om jeg kopierer denne formelen ned, vil tabelloppsettet fortsette å referere til A2: B6

Pro tips: For å konvertere en relativ referanse til en absolutt, velg den cellereferansen i cellen og trykk på F4 -tasten. Du vil merke at det endres ved å legge til dollartegn. Du kan fortsette å trykke F4 til du får referansen du vil ha.

Feil referanse til navn på ark / arbeidsbok

Når du refererer til andre ark eller arbeidsbøker i en formel, må du følge et bestemt format. Og hvis formatet er feil, får du en feilmelding.

For eksempel, hvis jeg vil referere til celle A1 i Sheet2, vil referansen være = Ark2! A1 (der det er et utropstegn etter bladnavnet)

Og hvis det er flere ord i arkenavnet (la oss si at det er eksempeldata), vil referansen være = 'Eksempeldata'! A1 (der navnet er omsluttet av enkle anførselstegn etterfulgt av et utropstegn).

Hvis du refererer til en ekstern arbeidsbok (la oss si at du refererer til celle A1 i "Eksempelark" i arbeidsboken med navnet "Eksempel arbeidsbok"), vil referansen være som vist nedenfor:

= '[Eksempel på arbeidsbok.xlsx] Eksempelark'! $ A $ 1

Og hvis du lukker arbeidsboken, ville referansen endres til å inkludere hele banen til arbeidsboken (som vist nedenfor):

= 'C: \ Users \ sumit \ Desktop \ [Eksempel på arbeidsbok.xlsx] Eksempelark'! $ A $ 1

I tilfelle du endrer navnet på arbeidsboken eller regnearket som formelen refererer til, vil det gi deg en #REF! feil.

Les også: Slik finner du eksterne lenker og referanser i Excel

Sirkulære referanser

En sirkulær referanse er når du refererer (direkte eller indirekte) til den samme cellen der formelen beregnes.

Nedenfor er et enkelt eksempel, hvor jeg bruker SUM -formelen i celle A4 mens jeg bruker den i selve beregningen.

= SUMMER (A1: A4)

Selv om Excel viser deg en melding som gir deg beskjed om sirkulærreferansen, vil den ikke gjøre det for hver forekomst. Og dette kan gi deg feil resultat (uten advarsel).

Hvis du mistenker sirkulær referanse i spill, kan du sjekke hvilke celler som har den.

For å gjøre dette, klikk på Formel-fanen og i gruppen "Formelrevisjon" klikker du på rullegardinikonet Feilkontroll (den lille nedoverpilen).

Hold markøren over alternativet Sirkulær referanse, og det viser deg cellen som har problemet med sirkulær referanse.

Celler formatert som tekst

Hvis du befinner deg i en situasjon der du ser formelen i stedet for verdien så snart du skriver formelen som Enter, så er det et tydelig tilfelle av cellen som formateres som tekst.

Når en celle er formatert som tekst, betrakter den formelen som en tekststreng og viser den som den er.

Det tvinger det ikke til å beregne og gi resultatet.

Og den har en enkel løsning.

1. Endre formatet til 'Generelt' fra 'Tekst' (det er i Hjem -fanen i Numbers -gruppen)
2. Gå til cellen som har formelen, gå inn i redigeringsmodus (bruk F2 eller dobbeltklikk på cellen) og trykk Enter

Hvis trinnene ovenfor ikke løser problemet, er en annen ting å sjekke om cellen har en apostrof i begynnelsen. Mange legger til en apostrof for å konvertere formler og tall til tekst.

Hvis det er en apostrof, kan du ganske enkelt fjerne den.

Tekst blir automatisk konvertert til datoer

Excel har denne dårlige vanen med å konvertere som ser ut som en dato til en faktisk dato. For eksempel, hvis du angir 1/1, vil Excel konvertere den til 01-Jan i inneværende år.

I noen tilfeller kan dette være akkurat det du vil, og i noen tilfeller kan dette fungere mot deg.

Og siden Excel lagrer dato- og klokkeslettverdier som tall, så snart du skriver inn 1/1, konverterer det det til et tall som representerer 1. januar i inneværende år. I mitt tilfelle, når jeg gjør dette, konverterer det det til nummeret 43831 (for 01-01-2020).

Dette kan rote med formlene dine hvis du bruker disse cellene som et argument i en formel.

Hvordan fikse dette?

Igjen, vi vil ikke at Excel automatisk skal velge formatet for oss, så vi må tydelig spesifisere formatet selv.

Nedenfor er trinnene for å endre formatet til tekst slik at det ikke automatisk konverterer tekst til datoer:

1. Velg cellene/området der du vil endre formatet
2. Klikk på kategorien Hjem
3. Klikk på rullegardinmenyen Format i Nummer-gruppen
4. Klikk på Tekst

Når du skriver inn noe i de valgte cellene, vil det bli betraktet som tekst, og ikke endret automatisk.

Merk: Trinnene ovenfor fungerer bare for data som er lagt inn etter at formateringen er endret. Det vil ikke endre noen tekst som er konvertert til dato før du gjorde denne formateringsendringen.

Et annet eksempel hvor dette kan være veldig frustrerende er når du skriver inn en tekst/et tall som har ledende nuller. Excel fjerner automatisk disse ledende nullene, da de anser dem som ubrukelige. For eksempel, hvis du skriver inn 0001 i en celle, vil Excel endre den til 1. Hvis du vil beholde disse ledende nullene, bruker du trinnene ovenfor.

Skjulte rader/kolonner kan gi uventede resultater

Dette er ikke et tilfelle av formel som gir deg feil resultat, men om å bruke feil formel.

Anta for eksempel at du har et datasett som vist nedenfor, og jeg vil få summen av alle de synlige cellene i kolonne C.

I celle C12 har jeg brukt SUMM -funksjonen for å få den totale salgsverdien for alle disse oppgitte postene.

Så langt så bra!

Nå bruker jeg et filter på varekolonnen for bare å vise postene for skriversalg.

Og her er problemet - formelen i celle C12 viser fremdeles det samme resultatet - dvs. summen av alle postene.

Som jeg sa, gir ikke formelen feil resultat. Faktisk fungerer SUM -funksjonen helt fint.

Problemet er at vi har brukt feil formel her.

SUM -funksjonen kan ikke redegjøre for de filtrerte dataene og gi deg resultatet for alle cellene (skjult eller synlig). Hvis du bare vil få summen/tellingen/gjennomsnittet av synlige celler, bruker du SUBTOTAL- eller AGGREGATE -funksjoner.

Viktig takeaway - Forstå riktig bruk og begrensninger av en funksjon i Excel.

Dette er noen av de vanlige årsakene til at jeg har sett hvor din Excel -formler fungerer kanskje ikke eller gir uventede eller feil resultater. Jeg er sikker på at det er mange flere grunner til at en Excel -formel ikke fungerer eller oppdateres.

Hvis du vet om en annen grunn (kanskje noe som irriterer deg ofte), gi meg beskjed i kommentarfeltet.

Håper du synes denne opplæringen var nyttig!