10 tips för felsökning av Excel-formler och -funktioner

Felsökning av Excel-kalkylbladets formler och funktioner kan vara ett stort jobb. Fel kommer bara med paketet. Excel erbjuder några värdefulla granskningsverktyg som finns på fliken Formler i gruppen Formelrevision, men du behöver inte alltid så mycket kraft. Ibland kan bara lite speciell kunskap hjälpa dig att lösa ett fel. Dessa 10 tips hjälper även de mest erfarna Excel-användare att hitta vanliga fel snabbt.

1: Felvärden är en ledtråd, inte en olägenhet

Om Excel inte kan beräkna en formel, visar det ett felvärde. Dessa värden är värdefulla ledtrådar och att veta vad de betyder kan hjälpa dig att snabbt upptäcka fel. Följande felvärden är ditt första steg, och ofta det enda steget, att felsöka ett fel:

  • # DIV / 0 : Excel kan inte dela med 0 och din formel avser en cell som innehåller eller utvärderar till 0 eller är tom. Detta är en av de enklaste att hitta och fixa.
  • #NAMN? : Det här felet uppstår när du refererar till ett område felaktigt eller glömmer att bifoga text i citattecken. När Excel möter text i en formel försöker den tolka texten som en cellreferens, ett intervallnamn eller ett funktionsnamn. När den inte kan känna igen texten som någon av dessa saker, visas det här felvärdet. Kontrollera dina funktionsnamn, cellreferenser och intervallnamn (se till att intervallnamnet faktiskt finns om du har stavat det korrekt). Om alla intervall är korrekta, se till att du har avgränsat text med citattecken.
  • #NULL! : Du specificerade ett skärningspunkt mellan två områden som inte korsar varandra, till exempel A1: C3 och D4: F6. Om områdena korsar, har du antagligen använt ett mellanslagstecken (fackföreningsoperatören) istället för ett komma för att separera de två områdena (A1: C3 D4: F6 istället för A1: C3, D4: F6).
  • #OGILTIGT! : Detta felvärde indikerar ett problem med ett nummer i formeln. Antingen är argumentet ogiltigt eller resultatet är för stort eller litet.
  • #REF !: Du har refererat till en cell felaktigt. Du har troligt tagit bort en cell. Exempelvis kommer uttrycket = A1 + B1 inte att returnera ett fel om du tar bort värdet i B1. Men om du tar bort kolumn B kommer den att göra det.
  • #VÄRDE! : Du har uttryckt fel datatyp eller operatör. Den vanligaste orsaken till detta felvärde är en hänvisning till text med en numerisk operation.

2: Använd felhantering för # N / A

Excels felvärde är inte som resten - det är vanligtvis inget fel med själva formeln. Det betyder att Excel inte kan returnera ett resultat som är vettigt. Det här felet visar sig mycket med VLOOKUP () och HLOOKUP (). När en av dessa funktioner returnerar # N / A betyder det vanligtvis att den inte kan hitta det värde du letar efter eller att du inte har sorterat listan (vilket inte alltid är nödvändigt). Att fixa detta fel kräver felhantering. Specifikt, i det här fallet, skulle du linda in den ursprungliga funktionen i en felhanteringsrutin som visar ett meningsfullt meddelande eller mer lämpligt värde. Till exempel kan du visa meddelandet "värde inte hittat" - användaren förstår det - medan värdet # N / A kan orsaka förvirring.

3: Utvärdera komponenter

Ett av de snabbaste sätten att hitta ett problem är att utvärdera enskilda komponenter i formelfältet eller till och med rätt i cellen. Specifikt markerar du en cellreferens eller enkel ekvation, trycker på F9, och Excel utvärderar det markerade uttrycket och returnerar resultatet i formelfältet. Hursomhelst kan du snabbt identifiera specifika problem med logik. (För att redigera i cellen, dubbelklicka bara på cellen. Om det inte fungerar, kolla avancerade redigeringsalternativ.) Tryck på Esc när du är klar.

Figur A visar en enkel IF () -funktion som returnerar strängen "är nere" om årstalet för 2011 är mindre än 2010 - det är tänkt. När du utvärderar uttrycket i formelfältet, som visas i figur B, kan du snabbt se att de sanna och falska handlingarna inte stämmer med villkorsuttalandets avsikt. Detta fungerar, den här gången, eftersom uttrycket följer den villkorade logiken: $ 44, 075 är mindre än $ 45, 962. Men det kommer inte alltid att vara fallet. Du kan också använda formelfältet för att lära dig ett konstantvärde och för att upptäcka fel orsakade av ogiltiga data. Det är inte bara för felsökningsformler.

Figur A

Bör inte detta uttryck återvända "är nere?"

Figur B

Om du utvärderar en del av formeln i formelfältet kan du identifiera logiska fel.

Alternativet Utvärdera formler på fliken Formler (i gruppen Formelrevision) fungerar på liknande sätt. Det är lite mer metodiskt men säkert användbart. I Excel 2003 hittar du Utvärdera formler på Verktyg-menyn under Granskning.

4: Kontrollera om det finns flera rader

Kontrollera den högra änden av formelfältet om en formel ser ut för enkel. Figur C visar en dubbel pil som indikerar att formeln innefattar flera linjer. Klicka på nedåtpilen för att gå igenom raderna eller klicka på Expand Formula Bar-knappen (till höger) för att visa hela formeln.

Figur C

Nedåtpilen till höger indikerar att det finns mer med den här formeln.

För att separera formelkomponenter, placera helt enkelt markören där du vill att formeln ska lindas till en ny rad och tryck på Alt + Enter. Att bryta ett uttryck över linjer kan hjälpa dig att begränsa uttryckslogiken, där varje rad innehåller ett steg mot det resulterande värdet. (Att göra så ofta förvirrar dock användare.)

5: Visa formler för enkla kontroller

För att snabbt visa alla formler trycker du på Ctrl + ~ (det är tegnet på sidan till vänster om 1). Figur D visar ett arks formler. Det är en växling, så att du trycker på Ctrl + ~ en andra gång för att visa resultat. Visa formler för att kontrollera konsistens och upptäcka potentiella fel. Genom att skriva ut formlerna kan du fortsätta granska din dator. De flesta användare vet redan hur man gör detta, men du kan använda detta tips för att arbeta mer effektivt genom att använda två fönster, ett med formler, ett med resultat, som visas i figur E.

Figur D

Växling mellan formler och deras resultat kan hjälpa till att upptäcka fel och inkonsekvenser.

Figur E

Visa både formler och resultat för att fungera mer effektivt vid felsökning.

Du kan också klicka på Visa formler i gruppen Formelrevisioner på fliken Formler för att växla mellan dessa två vyer. I Excel 2003 väljer du Alternativ på Verktyg-menyn. Klicka sedan på fliken Visa och markera eller avmarkera alternativet Formler i avsnittet Fönsteralternativ.

6: Välj formelceller

Att hitta alla formler i ett komplext ark kan vara tråkigt arbete och du kan missa några om du litar på ditt minne eller syn. Låt istället Excel göra jobbet för dig på följande sätt:

  1. Tryck på F5 för att visa dialogrutan Gå till och klicka på specialknappen längst ner. I Excel 2003 väljer du GoTo från Edit-menyn och väljer sedan Special.
  2. I det resulterande fönstret, kolla alternativet Formler, som visas i figur F.
  3. Klicka på OK. Figur G visar resultatet - i detta fall ett litet intervall med formler (B2: B4).

Figur F

Markera alternativet Formler.

Figur G

Använd alternativet Special för att välja celler som innehåller formler.

Om du behöver en mer permanent lösning, läs Identifiera vilka celler i Excel som är formelceller.

7: Det är ett datum; nej, det är ett nummer; nej, det är ett fel!

Ibland returnerar en formel ett datum- eller tidsvärde istället för det antal du förväntade dig. Detta händer vanligtvis när en formel refererar till en cell som du har formaterat som ett datum eller en tid. I det här fallet är det inget fel med formeln; formatera helt enkelt den refererade cellen korrekt och felvärdet bör försvinna.

8: Kontrollera antalet tecken

Äldre versioner av Excel-begränsningsformler till 1 024 tecken; Excel 2007 har ökat den maximala karaktärsgränsen till 8192. Det är tveksamt att Ribbon-användare någonsin kommer att träffa den väggen, men de som fortfarande använder menyversionen kan hålla begränsningen på 1.024 tecken i åtanke. Dessutom har funktionshäckningen ökat från sju till 64 - det svårt för mig. För följande gränser för Excel 2003 och Excel 2007/2010, läs följande artiklar:

  • Excel-specifikationer och -gränser (2003)
  • Excel-specifikationer och gränser (2010)

9: När din funktion returnerar funktionen

Om du anger en funktion, trycker du på Enter och Excel visar funktionen, Excel tolkar cellinnehållet som text. Det finns två möjliga korrigeringar:

  • Om det finns en apostrofkaraktär i början av funktionen, ta bort den.
  • Se till att du inte har formaterat cellen som text. Om du gjorde det, ändra det till General eller något mer lämpligt.

10: När din formel inte beräknar om

Du vet att formeln eller funktionen fungerar - det var bra tills du ändrade ett av de beroende värdena. När du gjorde det beräknade funktionen inte om och visade det uppdaterade resultatet. Det mest troliga skälet är att du har stängt av Excel automatisk omberäkning. Det är lätt att stänga av det här alternativet när du arbetar och glömmer att slå på det igen. Återställ bara beräkningsmetoden till automatiskt enligt följande:

  1. Välj fliken Arkiv och klicka på Alternativ. I Excel 2007 klickar du på Office-knappen och klickar på Excel-alternativ. I Excel 2003 väljer du Alternativ på Verktyg-menyn.
  2. Välj Formler i den vänstra rutan. I Excel 2003 klickar du på fliken Beräkning.
  3. Kontrollera alternativet Automatisk i beräkningsavsnitten.
  4. Klicka på OK.

Ytterligare resurser

  • Fem tips för att undvika datainmatningsfel i Excel
  • Fem tips för felsökningsformler i Excel
  • De 10 viktigaste sakerna att lära ut dina Excel-användare
  • 10 Excel-formateringsbeslut som kan ha olyckliga följder
  • 10 misstag att undvika när du arbetar med flera kalkylblad
  • 10 sätt att skruva upp kalkylarkdesignen
  • Fem av mina favorittips för Excel-kalkylblad
  • 10 sätt att förhindra att Excel biter dig i rumpan

Topp 10 nyhetsbrev

Vänd dig till dessa måste-läsa primrar för att få de magra på de hetaste tekniska ämnen, strategier och analyser. Levereras fredagar

Registrera dig idag

© Copyright 2020 | mobilegn.com