10 tips for feilsøking av Excel-formler og funksjoner

Å feilsøke formler og funksjoner for et Excel-regneark kan være en stor jobb. Feil kommer bare med pakken. Excel tilbyr noen verdifulle revisjonsverktøy, som du finner på Formler-fanen i Formula Auditing-gruppen, men du trenger ikke alltid så mye krefter. Noen ganger kan bare litt spesiell kunnskap hjelpe deg med å løse en feil. Disse 10 tipsene vil hjelpe selv de mest erfarne Excel-brukerne med å finne vanlige feil raskt.

1: Feilverdier er en ledetråd, ikke en plage

Hvis Excel ikke kan beregne en formel, viser det en feilverdi. Disse verdiene er verdifulle ledetråder og det å vite hva de betyr kan hjelpe deg raskt å oppdage feil. Følgende feilverdier er det første trinnet ditt, og ofte det eneste trinnet, for å feilsøke en feil:

  • # DIV / 0 : Excel kan ikke dele med 0 og formelen din refererer til en celle som inneholder eller evaluerer til 0 eller er tom. Dette er noe av det enkleste å finne og fikse.
  • #NAVN? : Denne feilen oppstår når du refererer til et område feil eller glemmer å legge ved tekst i anførselstegn. Når Excel møter tekst i en formel, prøver den å tolke teksten som en cellereferanse, et områdenavn eller et funksjonsnavn. Når den ikke kan gjenkjenne teksten som noen av disse tingene, viser den denne feilverdien. Kontroller funksjonsnavnene, cellehenvisningene og rekkeviddenavnene (sørg for at rekkeviddenavnet faktisk eksisterer hvis du har stavet det riktig). Hvis alle områdene er riktige, må du kontrollere at du har avgrenset tekst ved å bruke anførselstegn.
  • #NULL! : Du spesifiserte et kryss mellom to områder som ikke krysser hverandre, for eksempel A1: C3 og D4: F6. Hvis områdene krysser hverandre, har du sannsynligvis brukt et mellomromstegn (fagforeningsoperatøren) i stedet for et komma for å skille de to områdene (A1: C3 D4: F6 i stedet for A1: C3, D4: F6).
  • #NUM! : Denne feilverdien indikerer et problem med et tall i formelen. Enten er argumentet ugyldig, eller resultatet er for stort eller lite.
  • #REF !: Du har referert til en celle feil. Mest sannsynlig har du slettet en celle. For eksempel vil ikke uttrykket = A1 + B1 returnere en feil hvis du sletter verdien i B1. Men hvis du sletter kolonne B, vil den gjøre det.
  • #VERDI! : Du har uttrykt feil datatype eller operatør. Den vanligste årsaken til denne feilverdien er en henvisning til tekst ved en numerisk operasjon.

2: Bruk feilhåndtering for # N / A

Excel's feilverdi er ikke som resten - det er vanligvis ikke noe galt med selve formelen. Det betyr at Excel ikke kan returnere et resultat som er fornuftig. Denne feilen viser seg mye med VLOOKUP () og HLOOKUP (). Når en av disse funksjonene returnerer # N / A, betyr det vanligvis at den ikke finner verdien du leter etter, eller at du ikke har sortert listen (som ikke alltid er nødvendig). Å fikse denne feilen krever feilhåndtering. I dette tilfellet vil du spesifikt innpakke den opprinnelige funksjonen i en feilhåndteringsrutine som viser en meningsfull melding eller mer passende verdi. For eksempel kan du vise meldingen "verdi ikke funnet" - brukeren forstår det - mens # N / A-verdien kan forårsake forvirring.

3: Evaluer komponenter

En av de raskeste måtene å spore opp et problem på er å evaluere individuelle komponenter i formelfeltet eller til og med rett i cellen. Spesielt markerer du en cellereferanse eller enkel ligning, trykker F9, og Excel evaluerer det uthevede uttrykket og returnerer resultatet i formelfeltet. Uansett kan du raskt finne spesifikke problemer med logikk. (For å redigere i cellen, dobbeltklikker du bare på cellen. Hvis det ikke fungerer, sjekk alternativene for avansert redigering.) Trykk på Esc når du er ferdig.

Figur A viser en enkel IF () -funksjon som returnerer strengen "er nede" hvis årstalet for 2011 er mindre enn 2010 - vel, det skal den. Når du evaluerer uttrykket i formelfeltet, som vist i figur B, kan du raskt se at de sanne og falske handlingene ikke stemmer overens med den betingede uttalelsens intensjon. Dette fungerer denne gangen, fordi uttrykket følger den betingede logikken: $ 44.075 er mindre enn $ 45.962. Men det vil ikke alltid være tilfelle. Du kan også bruke Formel-linjen for å lære en konstant verdi og for å oppdage feil forårsaket av ugyldige data. Det er ikke bare for feilsøking av formler.

Figur A

Bør ikke dette uttrykket returnere "er nede?"

Figur B

Evaluering av en del av formelen i formelbjelken hjelper deg å finne logiske feil.

Alternativet Evaluer formel på Formler-fanen (i gruppen Formelrevisjon) fungerer på samme måte. Det er litt mer metodisk, men absolutt nyttig. I Excel 2003 finner du Evaluer formel på Verktøy-menyen under Revisjon.

4: Se etter flere linjer

Sjekk den høyre enden av formelfeltet hvis en formel ser for enkel ut. Figur C viser en dobbel pil som indikerer at formelen omfatter flere linjer. Klikk på pil ned for å gå gjennom linjene, eller klikk på Utvid formelrad-knappen (til høyre) for å vise hele formelen.

Figur C

Pil ned til høyre indikerer at det er mer med denne formelen.

For å skille formelkomponenter, plasserer du bare markøren der du vil at formelen skal pakkes til en ny linje og trykker på Alt + Enter. Å bryte et uttrykk på tvers av linjer kan hjelpe deg med å begrense uttrykkslogikken, der hver linje inneholder et skritt mot den resulterende verdien. (Å gjøre det ofte forvirrer brukere.)

5: Vis formler for enkle kontroller

For raskt å vise alle formler, trykk Ctrl + ~ (det er tegnet til venstre for 1). Figur D viser et arks formler. Det er en veksling, så å trykke Ctrl + ~ en gang til vil vise resultater. Vis formler for å sjekke konsistens og se potensielle feil. Når du skriver ut formlene, kan du fortsette tilsynet bort fra datamaskinen. De fleste brukere vet allerede hvordan de skal gjøre dette, men du kan bruke dette tipset til å jobbe mer effektivt ved å bruke to vinduer, ett med formler, ett med resultater, som vist i figur E.

Figur D

Å veksle mellom formler og resultatene deres kan bidra til å oppdage feil og uoverensstemmelser.

Figur E

Vis begge formlene og resultatene deres for å fungere mer effektivt mens du feilsøker.

Du kan også klikke på Vis formler i gruppen Formelrevisjon i Formler-fanen for å veksle mellom disse to visningene. I Excel 2003 velger du Valg fra Verktøy-menyen. Klikk deretter på kategorien Vis og merk eller fjern merket for Formler-alternativene i vinduet Alternativer-delen.

6: Velg formelceller

Å finne alle formlene i et sammensatt ark kan være kjedelig arbeid, og du kan savne noen hvis du er avhengig av minnet eller synet ditt. La Excel i stedet gjøre arbeidet for deg som følger:

  1. Trykk på F5 for å vise Gå til-dialogboksen og klikk på Spesial-knappen nederst. I Excel 2003 velger du GoTo fra Rediger-menyen og deretter Spesial.
  2. I det resulterende vinduet, sjekk alternativet Formler, som vist i figur F.
  3. Klikk OK. Figur G viser resultatet - i dette tilfellet et lite utvalg av formler (B2: B4).

Figur F

Merk av for Formler-alternativet.

Figur G

Bruk alternativet Spesial for å velge celler som inneholder formler.

Hvis du trenger en mer permanent løsning, kan du lese Identifisere hvilke celler i Excel som er formelceller.

7: Det er en date; nei, det er et tall; nei, det er en feil!

Noen ganger returnerer en formel en dato- eller tidsverdi i stedet for antallet du forventet. Dette skjer vanligvis når en formel refererer til en celle du har formatert som en dato eller tid. I dette tilfellet er det ingenting galt med formelen; bare omformaterer den refererte cellen riktig, og feilverdien skal forsvinne.

8: Kontroller antall tegn

Eldre versjoner av Excel-begrensningsformler til 1.024 tegn; Excel 2007 har økt maksimumsgrensen til 8192. Det er tvilsomt at Ribbon-brukere noen gang vil treffe den veggen, men de som fremdeles bruker menyversjonen, kan ha en begrensning på 1 024 tegn i tankene. I tillegg har hekkingen av funksjonene økt fra syv til 64 - det svirker tankene mine. Les følgende artikler for flere grenser for Excel 2003 og Excel 2007/2010:

  • Excel-spesifikasjoner og -grenser (2003)
  • Excel-spesifikasjoner og -grenser (2010)

9: Når funksjonen returnerer funksjonen

Hvis du går inn i en funksjon, trykker du på Enter, og Excel viser funksjonen, Excel tolker celleinnholdet som tekst. Det er to mulige fikser:

  • Hvis det er et apostrofkarakter i begynnelsen av funksjonen, slett den.
  • Forsikre deg om at du ikke har formatert cellen som tekst. Hvis du gjorde det, endre det til Generelt eller noe mer passende.

10: Når formelen din ikke beregner på nytt

Du vet at formelen eller funksjonen fungerer - det var bra til du endret en av de avhengige verdiene. Da du gjorde det, beregnet ikke funksjonen på nytt og viste det oppdaterte resultatet. Den mest sannsynlige årsaken er at du har slått av Excel automatisk omberegning. Det er enkelt å slå av dette alternativet mens du jobber, og glem å slå det på igjen. Tilbakestill bare beregningsmetoden til automatisk som følger:

  1. Velg Fil-fanen og klikk Alternativer. I Excel 2007, klikk på Office-knappen og klikk på Excel-alternativer. I Excel 2003 velger du Valg fra Verktøy-menyen.
  2. Velg Formler i venstre rute. I Excel 2003 klikker du på kategorien Beregning.
  3. Kontroller alternativet Automatisk i beregningsdelene.
  4. Klikk OK.

Tilleggsressurser

  • Fem tips for å unngå dataregistreringsfeil i Excel
  • Fem tips for feilsøking av formler i Excel
  • De 10 viktigste tingene å lære Excel-brukerne dine
  • 10 Excel-formateringsvedtak som kan ha uheldige konsekvenser
  • 10 feil å unngå når du jobber med flere regneark
  • 10 måter å skru opp regnearkdesignet på
  • Fem av mine favoritt Excel-regnearkstips
  • 10 måter å forhindre at Excel biter deg i baken

Topp 10 nyhetsbrev

Vend deg til disse må-lesne primerne for å få den magre på de hotteste teknologiske emner, strategier og analyser. Leveres fredager

Registrer deg i dag

© Copyright 2021 | pepebotifarra.com