Excel -formler fungerer ikke: Mulige årsager og hvordan man retter det!

Hvis du arbejder med formler i Excel, vil du før eller siden støde på problemet, hvor Excel -formler slet ikke virker (eller give det forkerte resultat).

Selvom det ville have været fantastisk, havde der kun været nogle få mulige årsager til, at formler ikke fungerede. Desværre er der for mange ting, der kan gå galt (og ofte gør).

Men da vi lever i en verden, der følger Pareto -princippet, vil det sandsynligvis løse 80% (eller måske endda 90% eller 95% af de problemer, hvor formler ikke fungerer i Excel), hvis du tjekker nogle almindelige problemer.

Og i denne artikel vil jeg fremhæve de almindelige problemer, der sandsynligvis er årsagen til din Excel -formler virker ikke.

Så lad os komme i gang!

Forkert syntaks for funktionen

Lad mig starte med at påpege det indlysende.

Hver funktion i Excel har en bestemt syntaks - f.eks. Antallet af argumenter, den kan tage, eller den type argumenter, den kan acceptere.

Og i mange tilfælde kan årsagen til, at dine Excel -formler ikke fungerer eller giver det forkerte resultat, være et forkert argument (eller manglende argumenter).

For eksempel tager VLOOKUP -funktionen tre obligatoriske argumenter og et valgfrit argument.

Hvis du angiver et forkert argument eller ikke angiver det valgfrie argument (hvor det er nødvendigt for at formlen fungerer), vil det give dig et forkert resultat.

Antag f.eks., At du har et datasæt som vist nedenfor, hvor du skal kende karakteren af ​​Mark i eksamen 2 (i celle F2).

Hvis jeg bruger nedenstående formel, får jeg det forkerte resultat, fordi jeg bruger den forkerte værdi i det tredje argument (en der beder om kolonneindeksnummer).

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

I dette tilfælde beregner formlen (da den returnerer en værdi), men resultatet er forkert (i stedet for score i eksamen 2 giver det scoren i eksamen 1).

Et andet eksempel, hvor du skal være forsigtig med argumenterne, er når du bruger VLOOKUP med den omtrentlige match.

Da du skal bruge et valgfrit argument til at angive, hvor du vil have VLOOKUP til at foretage et eksakt match eller et omtrentligt match, kan det ikke forårsage problemer at angive dette (eller bruge det forkerte argument).

Nedenfor er et eksempel, hvor jeg har karakterdataene for nogle elever, og jeg vil udtrække karakterer i eksamen 1 for eleverne i tabellen til højre.

Når jeg bruger nedenstående VLOOKUP -formel, giver det mig en fejl for nogle navne.

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

Dette sker, da jeg ikke har angivet det sidste argument (som bruges til at afgøre, om der skal foretages et eksakt match eller omtrentligt match). Når du ikke angiver det sidste argument, foretager det automatisk et omtrentligt match som standard.

Da vi havde brug for at foretage en nøjagtig match i dette tilfælde, returnerer formlen en fejl for nogle navne.

Selvom jeg har taget eksemplet på VLOOKUP -funktionen, er dette i dette tilfælde noget, der også kan bruges til mange Excel -formler, der også har valgfrie argumenter.

Læs også: Identificer fejl ved hjælp af Excel Formula Debugging

Ekstra rum, der forårsager uventede resultater

Ledende, efterfølgende mellemrum er svære at finde ud af og kan forårsage problemer, når du bruger en celle, der har disse i formler.

For eksempel i nedenstående eksempel, hvis jeg prøver at bruge VLOOKUP til at hente scoren for Mark, giver det mig #N/A fejlen (den ikke tilgængelige fejl).

Selvom jeg kan se, at formlen er korrekt, og navnet 'Mark' tydeligt er der, er listen, men jeg kan ikke se, at der er et efterfølgende mellemrum i cellen, der har navnet (i celle D2).

Excel betragter ikke indholdet i disse to celler som det samme, og derfor betragter det det som et misforhold, når værdien hentes ved hjælp af VLOOKUP (eller det kan være en anden opslagsformel).

For at løse dette problem skal du fjerne disse ekstra mellemrumstegn.

Du kan gøre dette ved at bruge en af ​​følgende metoder:

1. Rengør cellen, og fjern eventuelle mellemrum/efterfølgende mellemrum, før du bruger den i formler
2. Brug TRIM -funktionen inden for formlen for at sikre, at eventuelle mellemliggende/efterfølgende/dobbelte mellemrum ignoreres.

I ovenstående eksempel kan du i stedet bruge nedenstående formel for at sikre, at den fungerer.

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

Selvom jeg har taget VLOOKUP -eksemplet, er dette også et almindeligt problem, når jeg arbejder med TEKST -funktioner.

For eksempel, hvis jeg bruger LEN -funktionen til at tælle det samlede antal tegn i en celle, hvis der er indledende eller efterfølgende mellemrum, vil disse også blive talt og give det forkerte resultat.

Take away / Sådan repareres: Hvis det er muligt, skal du rense dine data ved at fjerne eventuelle mellemliggende/efterfølgende eller dobbelte mellemrum, før du bruger dem i formler. Hvis du ikke kan ændre de originale data, skal du bruge TRIM -funktionen i formler til at tage sig af dette.

Brug af manuel beregning i stedet for automatisk

Denne ene indstilling kan gøre dig til vanvid (hvis du ikke ved, at det er det, der forårsager alle problemerne).

Excel har to beregningsmetoder - Automatisk og Brugervejledning.

Som standard er den automatiske tilstand aktiveret, hvilket betyder, at hvis jeg bruger en formel eller foretager ændringer i de eksisterende formler, foretager den automatisk (og øjeblikkeligt) beregningen og giver mig resultatet.

Det er den indstilling, vi alle er vant til.

Når du foretager en ændring i regnearket i den automatiske indstilling (f.eks. Indtastning af en ny formel for selv tekst i en celle), genberegner Excel automatisk alt (ja, alt).

Men i nogle tilfælde aktiverer folk den manuelle beregningsindstilling.

Dette gøres for det meste, når du har en tung Excel -fil med mange data og formler. I sådanne tilfælde vil du måske ikke have, at Excel genberegner alt, når du foretager små ændringer (da det kan tage et par sekunder eller endda minutter), før denne genberegning er gennemført.

Hvis du aktiverer manuel beregning, beregner Excel ikke, medmindre du tvinger det til.

Og det kan få dig til at tro, at din formel ikke beregner.

Alt du skal gøre i dette tilfælde er enten at sætte beregningen tilbage til automatisk eller tvinge en genberegning ved at trykke på F9 -tasten.

Nedenfor er trinene til at ændre beregningen fra manuel til automatisk:

1. Klik på fanen Formel
2. Klik på Beregningsmuligheder
3. Vælg Automatisk

Vigtigt: Hvis du ændrer beregningen fra manuel til automatisk, er det en god idé at oprette en sikkerhedskopi af din projektmappe (bare hvis dette får din projektmappe til at hænge eller få Excel til at gå ned)

Take away / Sådan repareres: Hvis du bemærker, at dine formler ikke giver det forventede resultat, kan du prøve noget simpelt i en hvilken som helst celle (f.eks. Tilføje 1 til en eksisterende formel. Når du har identificeret problemet som det, hvor beregningstilstand skal ændres, skal du foretage en kraftberegning ved hjælp af F9.

Sletning af rækker/kolonner/celler, der fører til #REF! Fejl

En af de ting, der kan have en ødelæggende effekt på dine eksisterende formler i Excel, er, når du sletter enhver række/kolonne, der er blevet brugt i beregninger.

Når dette sker, nogle gange, justerer Excel selve referencen og sørger for, at formlerne fungerer fint.

Og nogle gange… det kan ikke.

Heldigvis er #REF! fejl i cellerne. Dette er en referencefejl, der fortæller dig, at der er et problem med referencerne i formlen.

Lad mig vise dig, hvad jeg mener med et eksempel.

Nedenfor har jeg brugt SUM -formlen til at tilføje cellerne A2: A6.

Nu, hvis jeg sletter nogen af ​​disse celler/rækker, returnerer SUM -formlen en #REF! fejl. Dette sker, fordi da jeg slettede rækken, ved formlen ikke, hvad jeg skal henvise til nu.

Du kan se, at det tredje argument i formlen er blevet til #REF! (som tidligere henviste til den celle, som vi slettede).

Take away / Sådan repareres: Inden du sletter data, der bruges i formler, skal du sørge for, at der ikke er fejl efter sletningen. Det anbefales også, at du opretter en sikkerhedskopi af dit arbejde regelmæssigt for at sikre, at du altid har noget at falde tilbage til.

Forkert placering af parantes (BODMAS)

Når dine formler begynder at blive større og mere komplekse, er det en god idé at bruge parentes til at være helt klar over, hvilken del der hører sammen.

I nogle tilfælde kan du have parentesen på det forkerte sted, hvilket enten kan give dig et forkert resultat eller en fejl.

Og i nogle tilfælde anbefales det at bruge parentes for at sikre, at formlen forstår, hvad der skal grupperes og beregnes først.

Antag f.eks., At du har følgende formel:

=5+10*50

I ovenstående formel er resultatet 505, da Excel først gør multiplikationen og derefter tilføjelsen (da der er en prioritetsrækkefølge, når det kommer til operatører).

Hvis du vil have det til først at tilføje og derefter multiplikationen, skal du bruge nedenstående formel:

=(5+10)*50

I nogle tilfælde kan prioritetsrækkefølgen virke for dig, men det anbefales stadig, at du bruger parentesen for at undgå forvirring.

Hvis du er interesseret, er nedenstående rækkefølge for forskellige operatører, der ofte bruges i formler:

Operatør	Beskrivelse	Prioritetens orden
: (kolon)	Rækkevidde	1
(enkelt rum)	Vejkryds	2
, (komma)	Union	3
-	Negation (som i -1)	4
%	Procent	5
^	Eksponentiering	6
* og /	Multiplikation & division	7
+ og -	Addition og subtraktion	8
&	Forligelse	9
= =	Sammenligning	10

Take away / Sådan repareres: Brug altid parentes for at undgå forvirring, selvom du kender rækkefølgen og bruger den korrekt. At have parentes gør det lettere at kontrollere formler.

Forkert brug af absolutte/relative cellereferencer

Når du kopierer og indsætter formler i Excel, justeres referencerne automatisk. Nogle gange er det præcis, hvad du vil (mest når du kopierer og sætter formler ned i kolonnen), og nogle gange vil du ikke have, at dette skal ske.

En absolut reference er, når du retter en cellereference (eller områdereference), så den ikke ændres, når du kopierer og indsætter formler, og en relativ reference er en, der ændres.

Du kan læse mere om absolutte, relative og blandede referencer her.

Du får muligvis et forkert resultat, hvis du glemmer at ændre referencen til en absolut (eller omvendt). Dette er noget, der sker ret ofte for mig, når jeg bruger opslagsformler.

Lad mig vise dig et eksempel.

Nedenfor har jeg et datasæt, hvor jeg vil hente scoren i eksamen 1 for navnene i kolonne E (en simpel VLOOKUP -brugssag)

Nedenfor er formlen, som jeg bruger i celle F2 og derefter kopierer til alle cellerne under den:

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

Som du kan se, giver denne formel i nogle tilfælde en fejl.

Dette sker, fordi jeg ikke har låst tabel array -argumentet - det er A2: B6 i celle F2, mens den burde have været $ A $ 2: $ B $ 6

Ved at have disse dollartegn før rækketallet og kolonnealfabetet i en cellereference, tvinger jeg Excel til at holde disse cellereferencer fast. Så selv når jeg kopierer denne formel ned, vil tabel arrayet fortsat henvise til A2: B6

Pro tip: Hvis du vil konvertere en relativ reference til en absolut, skal du markere denne cellereference i cellen og trykke på F4 -tasten. Du vil bemærke, at det ændres ved at tilføje dollartegn. Du kan fortsætte med at trykke på F4, indtil du får den ønskede reference.

Forkert henvisning til ark- / projektmappenavne

Når du refererer til andre ark eller projektmapper i en formel, skal du følge et bestemt format. Og hvis formatet er forkert, får du en fejl.

For eksempel, hvis jeg vil henvise til celle A1 i Sheet2, ville referencen være = Ark2! A1 (hvor der er et udråbstegn efter bladnavnet)

Og hvis der er flere ord i bladnavnet (lad os sige, det er eksempeldata), ville referencen være = 'Eksempeldata'! A1 (hvor navnet er omsluttet af enkelte anførselstegn efterfulgt af et udråbstegn).

Hvis du refererer til en ekstern projektmappe (lad os sige, at du refererer til celle A1 i 'Eksempelark' i projektmappen med navnet 'Eksempel -projektmappe'), vil referencen være som vist nedenfor:

= '[Eksempel Workbook.xlsx] Eksempelark'! $ A $ 1

Og hvis du lukker projektmappen, ville referencen ændre sig til at omfatte hele stien til projektmappen (som vist nedenfor):

= 'C: \ Brugere \ sumit \ Desktop \ [Eksempel Workbook.xlsx] Eksempelark'! $ A $ 1

Hvis du ender med at ændre navnet på den projektmappe eller det regneark, som formlen henviser til, vil det give dig en #REF! fejl.

Læs også: Sådan finder du eksterne links og referencer i Excel

Cirkulære referencer

En cirkulær reference er, når du refererer (direkte eller indirekte) til den samme celle, hvor formlen beregnes.

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

= SUM (A1: A4)

Selvom Excel viser dig en prompt, der fortæller dig om cirkulærreferencen, vil den ikke gøre det for hver instans. Og dette kan give dig det forkerte resultat (uden advarsel).

Hvis du har mistanke om cirkulær reference i spil, kan du kontrollere, hvilke celler der har den.

For at gøre dette skal du klikke på fanen Formel og i gruppen 'Formelrevision' skal du klikke på ikonet Fejlkontrol (den lille pil nedad).

Hold markøren over indstillingen Cirkulær reference, og den viser dig den celle, der har det cirkulære referenceproblem.

Celler formateret som tekst

Hvis du befinder dig i en situation, hvor du, så snart du skriver formlen som hit enter, ser formlen i stedet for værdien, det er et klart tilfælde, hvor cellen formateres som tekst.

Når en celle er formateret som tekst, betragter den formlen som en tekststreng og viser den som den er.

Det tvinger det ikke til at beregne og give resultatet.

Og det har en let løsning.

1. Skift format til 'Generelt' fra 'Tekst' (det er på fanen Startside i gruppen Numbers)
2. Gå til cellen, der har formlen, kom ind i redigeringstilstanden (brug F2 eller dobbeltklik på cellen) og tryk på Enter

Hvis ovenstående trin ikke løser problemet, er en anden ting at kontrollere, om cellen har en apostrof i begyndelsen. Mange mennesker tilføjer en apostrof for at konvertere formler og tal til tekst.

Hvis der er en apostrof, kan du simpelthen fjerne den.

Tekst bliver automatisk konverteret til datoer

Excel har denne dårlige vane at konvertere, der ligner en dato til en faktisk dato. Hvis du f.eks. Indtaster 1/1, konverterer Excel det til 01-jan. I indeværende år.

I nogle tilfælde kan dette være præcis, hvad du vil, og i nogle tilfælde kan dette virke imod dig.

Og da Excel gemmer dato- og tidsværdier som tal, så snart du indtaster 1/1, konverterer det det til et tal, der repræsenterer 1. januar i indeværende år. I mit tilfælde, når jeg gør dette, konverterer det det til nummeret 43831 (for 01-01-2020).

Dette kan ødelægge dine formler, hvis du bruger disse celler som et argument i en formel.

Hvordan løser man dette?

Igen vil vi ikke have, at Excel automatisk vælger formatet for os, så vi skal klart angive formatet selv.

Nedenfor er trinene til at ændre formatet til tekst, så det ikke automatisk konverterer tekst til datoer:

1. Vælg de celler/område, hvor du vil ændre formatet
2. Klik på fanen Startside
3. Klik på rullemenuen Format i gruppen Tal
4. Klik på Tekst

Nu, når du indtaster noget i de markerede celler, vil det blive betragtet som tekst og ikke ændret automatisk.

Bemærk: Ovenstående trin fungerer kun for data, der er indtastet, efter at formateringen er blevet ændret. Det ændrer ikke nogen tekst, der er blevet konverteret til dato, før du foretog denne formateringsændring.

Et andet eksempel, hvor dette kan være virkelig frustrerende, er, når du indtaster en tekst/et tal, der har ledende nuller. Excel fjerner automatisk disse førende nuller, da de anser dem for ubrugelige. Hvis du f.eks. Indtaster 0001 i en celle, ændrer Excel den til 1. Hvis du vil beholde disse førende nuller, skal du bruge trinene ovenfor.

Skjulte rækker/kolonner kan give uventede resultater

Dette er ikke et tilfælde af formel, der giver dig det forkerte resultat, men at bruge den forkerte formel.

Antag for eksempel, at du har et datasæt som vist nedenfor, og jeg vil have summen af ​​alle de synlige celler i kolonne C.

I celle C12 har jeg brugt SUM -funktionen til at få den samlede salgsværdi for alle disse givne poster.

Så langt så godt!

Nu anvender jeg et filter på varekolonnen for kun at vise posterne for printersalg.

Og her er problemet - formlen i celle C12 viser stadig det samme resultat - dvs. summen af ​​alle poster.

Som jeg sagde, giver formlen ikke det forkerte resultat. Faktisk fungerer SUM -funktionen fint.

Problemet er, at vi har brugt den forkerte formel her.

SUM -funktionen kan ikke redegøre for de filtrerede data og give dig resultatet for alle cellerne (skjult eller synligt). Hvis du kun ønsker at få summen/antallet/gennemsnittet af synlige celler, skal du bruge funktionerne SUBTOTAL eller AGGREGATE.

Nøgle takeaway - Forstå den korrekte anvendelse og begrænsninger af en funktion i Excel.

Dette er nogle af de almindelige årsager til, at jeg har set, hvor din Excel -formler virker muligvis ikke eller giver uventede eller forkerte resultater. Jeg er sikker på, at der er mange flere grunde til, at en Excel -formel ikke fungerer eller opdateres.

Hvis du kender nogen anden årsag (måske noget der irriterer dig ofte), så lad mig vide det i kommentarfeltet.

Håber du fandt denne vejledning nyttig!