Excel -formler fungerar inte: Möjliga orsaker och hur man fixar det!

Om du arbetar med formler i Excel kommer du förr eller senare att stöta på problemet där Excel -formler inte fungerar alls (eller ge fel resultat).

Även om det hade varit bra hade det bara funnits några få anledningar till felaktiga formler. Tyvärr är det för många saker som kan gå fel (och ofta gör).

Men eftersom vi lever i en värld som följer Pareto -principen, om du letar efter några vanliga problem kommer det troligtvis att lösa 80% (eller kanske till och med 90% eller 95% av de frågor där formler inte fungerar i Excel).

Och i den här artikeln kommer jag att belysa de vanliga problemen som sannolikt är orsaken till din Excel -formler fungerar inte.

Så låt oss komma igång!

Felaktig syntax för funktionen

Låt mig börja med att påpeka det självklara.

Varje funktion i Excel har en specifik syntax - till exempel antalet argument den kan ta eller typen av argument den kan acceptera.

Och i många fall kan orsaken till att dina Excel -formler inte fungerar eller ger fel resultat vara ett felaktigt argument (eller saknade argument).

Till exempel tar VLOOKUP -funktionen tre obligatoriska argument och ett valfritt argument.

Om du anger ett fel argument eller inte anger det valfria argumentet (där det behövs för att formeln ska fungera) kommer det att ge dig ett felaktigt resultat.

Anta till exempel att du har en dataset som visas nedan där du behöver veta poängen för Mark i Exam 2 (i cell F2).

Om jag använder nedanstående formel får jag fel resultat, eftersom jag använder fel värde i det tredje argumentet (ett som frågar efter kolumnindexnummer).

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

I detta fall beräknar formeln (eftersom det returnerar ett värde), men resultatet är felaktigt (i stället för poäng i Exam 2 ger det poängen i Exam 1).

Ett annat exempel där du måste vara försiktig med argumenten är när du använder VLOOKUP med den ungefärliga matchningen.

Eftersom du måste använda ett valfritt argument för att ange var du vill att VLOOKUP ska göra en exakt matchning eller en ungefärlig matchning kan det orsaka problem om du inte anger detta (eller använder fel argument).

Nedan är ett exempel där jag har betygsdata för vissa studenter och jag vill extrahera betyg i Exam 1 för studenterna i tabellen till höger.

När jag använder nedanstående VLOOKUP -formel ger det mig ett fel för vissa namn.

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

Detta händer eftersom jag inte har angett det sista argumentet (som används för att avgöra om en exakt matchning eller ungefärlig matchning ska göras). När du inte anger det sista argumentet gör det automatiskt en ungefärlig matchning som standard.

Eftersom vi behövde göra en exakt matchning i det här fallet returnerar formeln ett fel för vissa namn.

Medan jag har tagit exemplet med VLOOKUP -funktionen, är det i det här fallet något som kan vara tillämpligt för många Excel -formler som också har valfria argument.

Läs också: Identifiera fel med Excel Formula Debugging

Extra utrymmen orsakar oväntade resultat

Ledande, bakre mellanslag är svåra att ta reda på och kan orsaka problem när du använder en cell som har dessa i formler.

Till exempel, i exemplet nedan, om jag försöker använda VLOOKUP för att hämta poängen för Mark, ger det mig #N/A -felet (det inte tillgängliga felet).

Även om jag kan se att formeln är korrekt och namnet 'Mark' tydligt finns finns listan, men det jag inte kan se är att det finns ett bakre utrymme i cellen som har namnet (i cell D2).

Excel anser inte innehållet i dessa två celler detsamma och därför anser det att det är en felaktig överensstämmelse när värdet hämtas med VLOOKUP (eller det kan vara någon annan uppslagsformel).

För att åtgärda problemet måste du ta bort dessa extra mellanslagstecken.

Du kan göra detta genom att använda någon av följande metoder:

1. Rengör cellen och ta bort alla ledande/bakre mellanslag innan du använder den i formler
2. Använd TRIM -funktionen i formeln för att se till att alla ledande/bakre/dubbla mellanslag ignoreras.

I exemplet ovan kan du istället använda formeln nedan för att se till att det fungerar.

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

Medan jag har tagit VLOOKUP -exemplet är detta också ett vanligt problem när jag arbetar med TEXT -funktioner.

Till exempel, om jag använder LEN -funktionen för att räkna det totala antalet tecken i en cell, om det finns ledande eller bakre mellanslag, skulle dessa också räknas och ge fel resultat.

Ta bort / Hur man åtgärdar: Om möjligt, rengör dina data genom att ta bort eventuella inledande/bakre eller dubbla mellanslag innan du använder dessa i formler. Om du inte kan ändra originaldata använder du TRIM -funktionen i formler för att ta hand om detta.

Använda manuell beräkning istället för automatisk

Den här inställningen kan göra dig galen (om du inte vet att det är det som orsakar alla problem).

Excel har två beräkningslägen - Automatisk och Manuell.

Som standard är det automatiska läget aktiverat, vilket innebär att om jag använder en formel eller gör några ändringar i de befintliga formlerna gör det automatiskt (och direkt) beräkningen och ger mig resultatet.

Detta är inställningen vi alla är vana vid.

I den automatiska inställningen, när du gör någon ändring i kalkylbladet (t.ex. att ange en ny formel för att till och med lite text i en cell), beräknar Excel automatiskt om allt (ja, allt).

Men i vissa fall aktiverar människor inställningen för manuell beräkning.

Detta görs oftast när du har en tung Excel -fil med mycket data och formler. I sådana fall kanske du inte vill att Excel ska räkna om allt när du gör små ändringar (eftersom det kan ta några sekunder eller till och med minuter) innan denna omräkning är klar.

Om du aktiverar manuell beräkning beräknas inte Excel om du inte tvingar det.

Och detta kan få dig att tro att din formel inte beräknas.

Allt du behöver göra i det här fallet är antingen att sätta tillbaka beräkningen till automatisk eller tvinga fram en ny beräkning genom att trycka på F9 -tangenten.

Nedan följer stegen för att ändra beräkningen från manuell till automatisk:

1. Klicka på fliken Formel
2. Klicka på Beräkningsalternativ
3. Välj Automatisk

Viktigt: Om du ändrar beräkningen från manuell till automatisk är det en bra idé att skapa en säkerhetskopia av din arbetsbok (bara om detta får din arbetsbok att hänga eller få Excel att krascha)

Ta bort / Hur man åtgärdar: Om du märker att dina formler inte ger det förväntade resultatet, prova något enkelt i vilken cell som helst (till exempel att lägga till 1 i en befintlig formel. När du har identifierat problemet som det där beräkningsläget behöver ändras, gör en kraftberäkning genom att använda F9.

Radera rader/kolumner/celler som leder till #REF! Fel

En av de saker som kan ha en förödande effekt på dina befintliga formler i Excel är när du tar bort en rad/kolumn som har använts i beräkningar.

När detta händer, ibland, justerar Excel själva referensen och ser till att formlerna fungerar bra.

Och ibland… det kan inte.

Tack och lov är en tydlig indikation som du får när formler bryts när du tar bort celler/rader/kolumner #REF! fel i cellerna. Detta är ett referensfel som berättar att det finns något problem med referenserna i formeln.

Låt mig visa dig vad jag menar med ett exempel.

Nedan har jag använt SUM -formeln för att lägga till cellerna A2: A6.

Om jag nu tar bort någon av dessa celler/rader returnerar SUMM -formeln en #REF! fel. Detta händer eftersom när jag raderade raden vet inte formeln vad jag ska referera till nu.

Du kan se att det tredje argumentet i formeln har blivit #REF! (som tidigare hänvisade till cellen som vi tog bort).

Ta bort / Hur man åtgärdar: Innan du tar bort data som används i formler, se till att det inte finns några fel efter borttagningen. Det rekommenderas också att du skapar en säkerhetskopia av ditt arbete regelbundet för att se till att du alltid har något att luta dig tillbaka på.

Felaktig placering av parentes (BODMAS)

När dina formler börjar bli större och mer komplexa är det en bra idé att använda parenteser för att vara helt klar över vilken del som hör ihop.

I vissa fall kan du ha parentesen på fel plats, vilket antingen kan ge dig ett felaktigt resultat eller ett fel.

Och i vissa fall rekommenderas det att använda parentes för att se till att formeln förstår vad som måste grupperas och beräknas först.

Anta till exempel att du har följande formel:

=5+10*50

I formeln ovan är resultatet 505 eftersom Excel först gör multiplikationen och sedan tillägget (eftersom det finns en prioritetsordning när det gäller operatörer).

Om du vill att det först ska göra tillägget och sedan multiplikationen måste du använda följande formel:

=(5+10)*50

I vissa fall kan prioritetsordningen fungera för dig, men det rekommenderas fortfarande att du använder parentesen för att undvika förvirring.

Om du är intresserad, nedan är prioritetsordningen för olika operatörer som ofta används i formler:

Operatör	Beskrivning	Rangordning
: (kolon)	Räckvidd	1
(enda utrymme)	Genomskärning	2
, (kommatecken)	union	3
-	Negation (som i -1)	4
%	Procentsats	5
^	Exponentiering	6
* och /	Multiplikation & division	7
+ och -	Addition och subtraktion	8
&	Försoning	9
= =	Jämförelse	10

Ta bort / Hur man åtgärdar: Använd alltid parentes för att undvika förvirring, även om du vet prioritetsordningen och använder den korrekt. Att ha parentes gör det lättare att granska formler.

Felaktig användning av absoluta/relativa cellreferenser

När du kopierar och klistrar in formler i Excel justeras referenserna automatiskt. Ibland är detta exakt vad du vill (mestadels när du kopierar in formler i kolumnen), och ibland vill du inte att detta ska hända.

En absolut referens är när du fixar en cellreferens (eller intervallreferens) så att den inte ändras när du kopierar och klistrar in formler, och en relativ referens är en som ändras.

Du kan läsa mer om absoluta, relativa och blandade referenser här.

Du kan få ett felaktigt resultat om du glömmer att ändra referensen till en absolut (eller vice versa). Detta är något som händer mig ganska ofta när jag använder uppslagsformler.

Låt mig visa dig ett exempel.

Nedan har jag en dataset där jag vill hämta poängen i Exam 1 för namnen i kolumn E (ett enkelt VLOOKUP -användningsfall)

Nedan är formeln som jag använder i cell F2 och sedan kopierar till alla cellerna under den:

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

Som du kan se att denna formel ger ett fel i vissa fall.

Detta händer för att jag inte har låst tabellmatrisargumentet - det är det A2: B6 i cell F2, medan det borde ha varit $ A $ 2: $ B $ 6

Genom att ha dessa dollarstecken före radnumret och kolumnalfabetet i en cellreferens tvingar jag Excel att hålla dessa cellreferenser fixade. Så även när jag kopierar ner denna formel kommer tabellmatrisen att fortsätta att hänvisa till A2: B6

Proffstips: Om du vill konvertera en relativ referens till en absolut referens markerar du den cellreferensen i cellen och trycker på F4 -tangenten. Du skulle märka att det ändras genom att lägga till dollarstecknen. Du kan fortsätta att trycka på F4 tills du får den referens du vill ha.

Felaktig referens till blad / arbetsboknamn

När du refererar till andra blad eller arbetsböcker i en formel måste du följa ett specifikt format. Och om formatet är fel får du ett fel.

Till exempel, om jag vill hänvisa till cell A1 i Sheet2, skulle referensen vara = Sheet2! A1 (där det finns ett utropstecken efter bladnamnet)

Och om det finns flera ord i bladnamnet (låt oss säga att det är exempeldata), skulle referensen vara = 'Exempeldata'! A1 (där namnet är inneslutet i enkla citattecken följt av ett utropstecken).

Om du hänvisar till en extern arbetsbok (låt oss säga att du hänvisar till cell A1 i "Exempelblad" i arbetsboken med namnet "Exempel arbetsbok"), kommer referensen att vara som visas nedan:

= '[Exempel arbetsbok.xlsx] Exempelblad'! $ A $ 1

Och om du stänger arbetsboken skulle referensen ändras för att inkludera hela sökvägen till arbetsboken (som visas nedan):

= 'C: \ Users \ sumit \ Desktop \ [Exempel Workbook.xlsx] Exempelblad'! $ A $ 1

Om du slutar ändra namnet på arbetsboken eller det kalkylblad som formeln refererar till kommer det att ge dig en #REF! fel.

Läs också: Så här hittar du externa länkar och referenser i Excel

Cirkulära referenser

En cirkulär referens är när du hänvisar (direkt eller indirekt) till samma cell där formeln beräknas.

Nedan är ett enkelt exempel, där jag använder SUM -formeln i cell A4 medan jag använder den i själva beräkningen.

= SUMMA (A1: A4)

Även om Excel visar dig en uppmaning att informera dig om cirkulärreferensen, kommer det inte att göra det för varje instans. Och detta kan ge dig fel resultat (utan förvarning).

Om du misstänker cirkulär referens i spelet kan du kontrollera vilka celler som har den.

För att göra detta klickar du på fliken Formel och i gruppen "Formelgranskning" klickar du på rullgardinsmenyn Felkontroll (den lilla nedåtpilen).

Håll muspekaren över alternativet Cirkulär referens så visar den cellen som har problemet med cirkulär referens.

Celler formaterade som text

Om du befinner dig i en situation där så fort du skriver formeln när du trycker på enter, ser du formeln istället för värdet, det är ett tydligt fall där cellen formateras som text.

När en cell formateras som text betraktar den formeln som en textsträng och visar den som den är.

Det tvingar det inte att beräkna och ge resultatet.

Och det har en enkel fix.

1. Ändra formatet till "Allmänt" från "Text" (det finns på fliken Hem i gruppen Numbers)
2. Gå till cellen som har formeln, gå in i redigeringsläget (använd F2 eller dubbelklicka på cellen) och tryck på Retur

Om stegen ovan inte löser problemet, är en annan sak att kontrollera om cellen har en apostrof i början. Många människor lägger till en apostrof för att konvertera formler och siffror till text.

Om det finns en apostrof kan du helt enkelt ta bort den.

Text omvandlas automatiskt till datum

Excel har denna dåliga vana att konvertera det som ser ut som ett datum till ett faktiskt datum. Till exempel, om du anger 1/1, skulle Excel konvertera det till 01-jan av innevarande år.

I vissa fall kan detta vara precis vad du vill, och i vissa fall kan detta fungera emot dig.

Och eftersom Excel lagrar datum- och tidsvärden som siffror, så snart du anger 1/1, konverterar det det till ett nummer som representerar 1 januari det aktuella året. I mitt fall, när jag gör detta, konverterar det det till numret 43831 (för 01-01-2020).

Detta kan röra med dina formler om du använder dessa celler som ett argument i en formel.

Hur fixar man detta?

Återigen vill vi inte att Excel automatiskt väljer formatet för oss, så vi måste tydligt ange formatet själva.

Nedan följer stegen för att ändra formatet till text så att det inte automatiskt konverterar text till datum:

1. Markera cellerna/intervallet där du vill ändra formatet
2. Klicka på fliken Hem
3. Klicka på listrutan Format i gruppen Nummer
4. Klicka på Text

Nu, när du anger något i de markerade cellerna, skulle det betraktas som text och ändras inte automatiskt.

Obs! Stegen ovan fungerar bara för data som matats in efter att formateringen har ändrats. Det kommer inte att ändra någon text som har konverterats till datum innan du gjorde denna formateringsändring.

Ett annat exempel där detta kan vara riktigt frustrerande är när du anger en text/ett nummer som har inledande nollor. Excel tar bort dessa ledande nollor automatiskt eftersom de anser dessa värdelösa. Till exempel, om du anger 0001 i en cell, kommer Excel att ändra den till 1. Om du vill behålla dessa ledande nollor använder du stegen ovan.

Dolda rader/kolumner kan ge oväntade resultat

Detta är inte fallet med formel som ger dig fel resultat utan att använda fel formel.

Anta till exempel att du har en dataset som visas nedan och jag vill få summan av alla synliga celler i kolumn C.

I cell C12 har jag använt SUM -funktionen för att få det totala försäljningsvärdet för alla dessa angivna poster.

Än så länge är allt bra!

Nu applicerar jag ett filter på artikelkolumnen för att bara visa posterna för skrivarförsäljning.

Och här är problemet - formeln i cell C12 visar fortfarande samma resultat - det vill säga summan av alla poster.

Som jag sa ger formeln inte fel resultat. Faktum är att SUM -funktionen fungerar bra.

Frågan är att vi har använt fel formel här.

SUM -funktionen kan inte redogöra för filtrerade data och ge dig resultatet för alla celler (dolda eller synliga). Om du bara vill få summan/antalet/genomsnittet av synliga celler, använd funktionerna SUBTOTAL eller AGGREGATE.

Nyckelhämtning - Förstå rätt användning och begränsningar av en funktion i Excel.

Det här är några av de vanligaste orsakerna till att jag har sett var din Excel -formler kanske inte fungerar eller ger oväntade eller felaktiga resultat. Jag är säker på att det finns många fler anledningar till att en Excel -formel inte fungerar eller uppdateras.

Om du känner till någon annan anledning (kanske något som irriterar dig ofta), låt mig veta i kommentarfältet.

Hoppas du tyckte att denna handledning var användbar!