Slik oppdager du feil i komplekse formler med Excels intelligente feilsøkingsmotor

Det kan være fantastisk å jobbe med regneark fulle av formler ... helt til noe går galt og en mystisk Excel-feil dukker opp som du ikke kan finne ut av. Det er viktig å gjenkjenne de vanligste feilene i Excel-formler . Etter hvert som formler blir lengre og mer komplekse, blir det nesten umulig å oppdage kilden til problemet visuelt, og mye tid går bort på å sjekke celle for celle.

Med sin nye intelligente feilsøkingsmotor og feilsøkingsverktøy blir Excel stadig mer nyttig for å finne, forstå og korrigere feil i komplekse formler . For å få mest mulig ut av disse funksjonene er det imidlertid viktig å mestre noen grunnleggende regler for å skrive formler, forstå betydningen av forskjellige feilmeldinger og vite hvordan man automatiserer massekorrigeringer, spesielt når man importerer data fra andre programmer og bruker uvanlig formatering.

Grunnleggende konsepter for å sikre at formler ikke feiler fra starten av

Før man dykker ned i intelligente motorer og avanserte feilsøkingsprogrammer, er det viktig å forstå noen grunnleggende regler som forhindrer de fleste feil. Excel er ganske streng med syntaks, og enhver liten detalj kan føre til at en formel tolkes som tekst eller feiler fullstendig.

Det første og viktigste er at hver formel må begynne med et likhetstegn (=) . Hvis det utelates, forstår ikke Excel at du vil utføre en beregning, men snarere at du skriver inn tekst eller en dato. Hvis du for eksempel skriver SUM(A1:A10) uten likhetstegnet, vil cellen ganske enkelt vise SUM(A1:A10) og ikke utføre noen beregning. Noe lignende skjer med datoer: hvis du skriver 11/2 og celleformatet er Generelt, kan Excel vise 2-nov (2. november) i stedet for å dele 11 med 2, fordi den tolker at du gir den en dato og ikke en beregning. Dette er relatert til Excels merkelige feil med datoer.

Et annet viktig poeng er riktig bruk av parenteser. Alle funksjoner som bruker dem må ha en riktig plassert åpnings- og lukkeparentes . Når man arbeider med nestede funksjoner , er det veldig vanlig å legge til eller utelate en ekstra parentes, noe som forårsaker syntaksfeil. Tenk deg en formel som =HVIS(B5<0;"Ikke gyldig";B5*1,05). Hvis du ved et uhell skriver =HVIS(B5<0;"Ikke gyldig";B5*1,05)) med en ekstra lukkeparentes, vil formelen slutte å virke fordi antallet parenteser ikke samsvarer. Det er viktig å alltid sjekke at det er samme antall åpnings- og lukkeparenteser, og at de er i riktig posisjon, for å unngå feil som er vanskelige å finne.

Det er også viktig å håndtere områder riktig. For å referere til flere celler på rad, må du bruke et kolon (:) mellom den første og siste cellen . Området skrives for eksempel som A1:A5. Hvis du utelater kolon og ender opp med noe sånt som =SUMMER(A1 A5), vil Excel tolke dette som ugyldige referanser mellom urelaterte celler og returnere en #NULL! -feil , som vises når det ikke finnes skjæringspunkter eller inkonsistente referanser.

Til slutt er det viktig å respektere antallet og rekkefølgen på argumenter for hver funksjon. Noen funksjoner krever spesifikke argumenter på en bestemt posisjon , mens andre tillater valgfrie parametere. Hvis du ikke oppgir nok argumenter, eller legger til flere enn nødvendig, vil Excel vise feilmeldinger relatert til funksjonen. Å forstå den nøyaktige syntaksen til hver funksjon (antall argumenter, forventet datatype osv.) bidrar til å minimere problemer før de oppstår.

Argumenttyper: tall, tekst og hekkende grenser

Excel-funksjoner krever ikke bare et bestemt antall argumenter, men de forventer også riktig datatype for hvert argument . En funksjon som er utformet for å legge sammen tall er ikke den samme som en som er utformet for å manipulere tekst. Hvis de byttes om, kan resultatene være uventede eller til og med produsere en feil.

For eksempel krever funksjoner som SUMMER, GJENNOMSNITT eller PRODUKT numeriske argumenter. Hvis du sender dem tekstverdier der de forventer tall, kan Excel returnere 0, ignorere celler eller vise en feil avhengig av konteksten. Omvendt er funksjoner som ERSTATT, SAMMENK eller HØYRE utformet for å fungere med tekststrenger. I disse må minst ett av argumentene være en tekstverdi eller en referanse til en celle som inneholder tekst. Hvis du tvinger et tall til å bli behandlet som tekst uten riktig håndtering, kan det hende at funksjonen ikke oppfører seg som forventet.

Videre setter Excel en betydelig grense for hekkende funksjoner. Du kan ikke hekke mer enn 64 nivåer av funksjoner innenfor en enkelt formel . Dette betyr at hvis du har en formel med HVIS-setninger inni HVIS-setninger inni HVIS-setninger, og så videre, er det et punkt der Excel ikke vil godta flere nivåer. Selv om få personer faktisk når disse ytterpunktene, kan denne grensen overskrides i svært komplekse eller dårlig utformede modeller, noe som fører til feil som er vanskelige å tolke. Utover et visst nivå av kompleksitet er det vanligvis mer fornuftig å dele logikken inn i flere hjelpeceller i stedet for å prøve å konsentrere alt i en enkelt, ekstremt lang formel. Av denne grunn er det også lurt å gjennomgå årsakene til og løsningene for ytelsesproblemer i Excel.

Et typisk eksempel på en feil relatert til typen og antallet argumenter er ABS-funksjonen. Denne funksjonen godtar bare ett numerisk argument og returnerer dets absolutte verdi. Hvis du skriver noe som =ABS(-2134), vil Excel vise en feil fordi funksjonen ikke vet hva den skal gjøre med to argumenter atskilt med et semikolon. Den riktige måten å gjøre dette på ville være noe som =ABS(-2134) eller =ABS(A1) hvis celle A1 inneholder det negative tallet du vil konvertere til positivt.

På den annen side er det viktig å vurdere bruken av skilletegn avhengig av de regionale innstillingene. I mange spanske installasjoner av Excel brukes semikolon (;) som argumentskilletegn og komma (,) for desimaldelen. Hvis en formel feilaktig blander komma og semikolon på upassende steder, kan det generere ytterligere syntaksfeil som kompliserer formelens lesbarhet og feilsøking.

Oppsummert er det tre grunnleggende søyler for å redusere forekomsten av feil i sofistikerte formler å velge riktig datatype som skal sendes til hver funksjon, respektere antallet argumenter som er definert i syntaksen, og ikke overskride hekkegrensene.

Referanser til andre sider og bøker: hvordan unngå lenkefeil

Når formler begynner å lenke til andre ark i samme arbeidsbok eller til og med til eksterne arbeidsbøker, er det ganske enkelt å gjøre små skrivefeil som resulterer i ugyldige referansemeldinger. Excel krever at ark-, arbeidsbok- og banenavn skrives svært presist for å finne dataene riktig.

Hvis du refererer til et ark i en formel der navnet inneholder mellomrom eller andre tegn enn bokstaver (tall, bindestreker, symboler osv.), må navnet alltid være omsluttet av enkle anførselstegn . Hvis du for eksempel har et ark som heter «Kvartalsdata», vil den riktige referansen til celle D3 i det arket være ='Kvartalsdata'!D3. Hvis arket heter 123, vil du på samme måte skrive ='123'!A1, slik at Excel forstår arknavnet og ikke forveksler det med et enkelt tall.

Videre, når en formel refererer til et annet ark, må et utropstegn (!) plasseres rett etter arknavnet . Dette markerer overgangen mellom arkidentifikatoren og den spesifikke cellereferansen. Et komplett eksempel ville være: ='Kvartalsdata'!D3. Hvis utropstegnet utelates eller plasseres feil, vil formelen være ugyldig, og det vil oppstå referansefeil.

Når dataene er i en annen arbeidsbok, trenger Excel mer kontekst. I disse tilfellene må den eksterne referansen inkludere filnavnet i hakeparenteser, arknavnet og området . Hvis du for eksempel vil telle antall rader i området A1:A8 i arbeidsboken Operations T2.xlsx, kan du på Salg-arket bruke en formel som =RADER('Salg'!A1:A8). Hvis filen ikke er åpen, må du også oppgi hele filbanen, for eksempel 'C:\Mine dokumenter\Salg'!A1:A8, i formelen.

En typisk formel i denne sammenhengen ville være noe sånt som =RADER('C:\Mine dokumenter\Salg'!A1:A8). Denne instruksjonen returnerer antall rader i området A1:A8 i den andre arbeidsboken, som i dette tilfellet ville være 8. Hvis det gjøres en feil når du skriver inn banen, arbeidsbokens navn, arknavnet eller hakeparentesene, vil resultatet være en referansefeil eller en verdi som ikke oppdateres fordi Excel ikke finner den eksterne datakilden.

Det er også lurt å overvåke hva som skjer når filer som inneholder koblede data flyttes, gis nytt navn eller slettes. Feilsøkingsmotoren kan oppdage noen problemer, men hvis den eksterne filen ikke lenger finnes på den forventede plasseringen, må du manuelt oppdatere koblingene eller omdefinere stiene for å gjenopprette formelfunksjonaliteten.

Tallformatering i formler: feil på grunn av symboler og skilletegn

Et av de vanligste problemene i regneark med store mengder data, spesielt når dataene kommer fra andre applikasjoner, er bruk av feil tallformater i formler . Excel skiller tydelig mellom den faktiske verdien som er lagret i en celle og formatet som verdien vises i. Å forveksle disse to nivåene fører ofte til subtile feil.

Tall skal ikke formateres i formler. Det vil si at hvis en verdi er 1000 euro, skal den vises i formelen som 1000, uten €-tegnet, tusenskilletegn eller komma. Hvis du skriver inn 1.000 eller 1,000 (avhengig av de regionale innstillingene), vil Excel sannsynligvis tolke det som et argumentskilletegn eller en annen verdi enn den du hadde til hensikt. Valutaformatering, tusenskilletegn og desimalskilletegn brukes på tall etterpå , ved hjelp av celleformateringsalternativer, ikke i uttrykket. Hvis du vil ha mer informasjon om hvordan du bruker formatering riktig, kan du se Dataformatering i Excel 365.

Tenk deg at du vil legge til 3100 til innholdet i celle A3. Hvis du instinktivt skriver =SUM(3.100,A3) og tror du mener 3100, vil Excel tolke dette som å legge til 3 og 100 først, og deretter legge til verdien av A3 til resultatet. Med andre ord vil den beregne (3 + 100) + A3, som ikke er det samme som A3 + 3100. Den riktige formelen ville være =SUM(3100,A3), uten å forsøke å representere tusenseparatorformatet.

Noe lignende skjer med funksjoner som bare godtar et bestemt antall argumenter, for eksempel ABS, som bare godtar én enkelt numerisk verdi. Hvis du prøver å skrive komma eller semikolon i tallet for å simulere tusenvis, vil Excel behandle det som flere separate argumenter, og funksjonen vil returnere en syntaksfeil . Derfor fungerer ikke uttrykk som =ABS(-2;134), mens =ABS(-2134) er gyldig.

Dette er spesielt viktig å huske på når du importerer data fra regnskapssystemer, ERP-er eller faktureringsprogrammer. Mange av disse eksporterer beløp med formatering, valutasymboler, mellomrom eller til og med ordet «EUR» på slutten. Alt dette forvandler det som skal være en numerisk verdi til en tekststreng som Excel ikke kan bruke direkte i beregningene sine, noe som fører til skrivefeil, tomme resultater eller summer som ikke summerer seg.

Den beste fremgangsmåten er å lagre data som rådall i celler uten spesiell formatering og deretter bruke visuell formatering som valuta, prosent eller tusenskilletegn fra formateringsmenyen. Dette forhindrer at symboler forstyrrer beregninger og lar feilsøkingsmotoren bedre oppdage inkonsekvenser.

Intelligent feilsjekking og feilsøking i Excel

I tillegg til å korrigere syntaks, inneholder Excel et feilkontrollsystem som analyserer formler og foreslår rettelser når det oppdager noe uvanlig. Denne stadig mer intelligente motoren hjelper med å finne manglende referanser, inkonsekvenser og typeproblemer i komplekse formler, noe som reduserer tiden det tar å finne kilden til feilen.

I skrivebordsversjonen av Excel kan du få tilgang til feilkontroll fra fanen Formler, i gruppen Formelredigering, der alternativet Feilkontroll vises. Denne veiviseren skanner celler med advarsler og viser meldinger med mulige årsaker til problemet, samt foreslår spesifikke endringer som kan brukes med et enkelt klikk. Den er nyttig for å oppdage inkonsekvente områder, formler som ikke har blitt oppdatert da de ble dratt, referanser til tomme celler eller inkonsekvenser i totaler.

I Excel Online (nettversjonen) er det imidlertid for øyeblikket ikke mulig å konfigurere eller bruke disse avanserte feilkontrollreglene på samme måte. Skytjenesten tilbyr grunnleggende funksjoner, men inkluderer ikke hele settet med formelkontrollregler som er tilgjengelige på skrivebordet. Derfor er det mest praktisk å åpne arbeidsbøker med skrivebordsprogrammet når du arbeider med svært komplekse arbeidsbøker, for å dra full nytte av feilsøkingsmotoren.

Hvis du har skrivebordsversjonen, kan du bruke knappen «Åpne med Excel» i Excel Online for å starte arbeidsboken i det fullstendige programmet. Når du er der, kan du utføre alle nødvendige kontroller for å oppdage potensielle formelfeil, aktivere spesifikke regler og gjennomgå eventuelle advarsler systemet gir. Det er en effektiv måte å kombinere bekvemmeligheten ved å jobbe på nett med kraften til skrivebordsprogrammet.

For å holde deg oppdatert på nye funksjoner i feilsøkingsmotoren og forbedringer av de smarte feilsøkingsverktøyene, er det lurt å sjekke den offisielle Microsoft Excel-bloggen fra tid til annen . Den publiserer nyheter, oppdateringer og endringer for både skrivebords- og nettversjonen, slik at du vet når nye feilsøkingsfunksjoner er tilgjengelige.

Hvis du trenger mer omfattende tilgang til alle Office-programmer (Word, Excel, PowerPoint osv.) og tilhørende tjenester, kan du alltid prøve eller kjøpe hele pakken via Office.com, slik at du har de mest avanserte feilkontrollverktøyene for alle regnearkene dine.

Feil forårsaket av tall lagret som tekst og hvordan du retter dem samtidig

Et veldig vanlig scenario, spesielt i regnskaps- eller finansmiljøer, oppstår når man eksporterer en detaljert hovedbok (detaljert GL) fra administrasjonsprogramvare. I mange tilfeller eksporterer disse applikasjonene numeriske beløp som tekst . Resultatet er at når filen åpnes i Excel, gjenkjenner ikke formler som prøver å legge til, trekke fra eller analysere disse feltene verdiene som tall, og operasjonene mislykkes eller gir feil resultater.

Excels innebygde feilkontroll kan oppdage dette problemet og vise den klassiske grønne trekanten i hjørnet av cellene, som indikerer at «tallet er lagret som tekst». Ved å klikke på advarselsikonet får Excel muligheten til å konvertere teksten til et tall. Ulempen er at hvis du bruker feilkontrollverktøyet fra Formler-fanen > Redigere formler > Feilkontroll, brukes korrigeringen vanligvis celle for celle , noe som kan være utrolig frustrerende i en generell arbeidsbok med tusenvis av rader.

Videre, hvis hele dokumentet er valgt, fokuserer ikke feilkontrollen utelukkende på numeriske kolonner, ettersom andre kolonner som inneholder datoer, tekst og beskrivelser også kan utløse varsler, noe som ytterligere kompliserer massekorrigeringsprosessen. Det logiske spørsmålet i denne sammenhengen er om det finnes en måte å velge hele arket og få Excel til å korrigere alle disse feilene ved å konvertere teksten til faktiske tall i ett jafs.

Det finnes flere strategier du kan bruke direkte i Excel for å løse disse situasjonene. En av de enkleste er å bruke Lim inn spesial med en matematisk operasjon . Du kan for eksempel skrive inn tallet 1 i en tom celle, kopiere den cellen, velge celleområdet som inneholder tall lagret som tekst (for eksempel hele kolonnen med beløp), og bruke Lim inn spesial > Multipliser. Denne operasjonen får Excel til å tolke hver "tekst"-oppføring som et tall, multiplisere det med 1 og lagre resultatet som en reell numerisk verdi, og bevare det opprinnelige beløpet. Det er en veldig effektiv måte å konvertere store mengder data i bulk.

Et annet alternativ er å bruke VERDI-funksjonen i en hjelpekolonne. Hvis for eksempel tekstbeløpene er i kolonne B, kan du opprette en formel som =VERDI(B2) i kolonne C, kopiere den nedover, og deretter kopiere og lime inn resultatene som verdier i den opprinnelige kolonnen, og erstatte teksten. Denne teknikken lar deg også visuelt bekrefte at alt samsvarer før du sletter de opprinnelige dataene.

I noen tilfeller lar advarslene om «tall lagret som tekst» deg velge et bredere område og bruke konverteringen på flere celler samtidig fra den lille grønne trekanten-kontekstmenyen. Effektiviteten deres avhenger imidlertid av hvordan dataene ble generert og om Excel oppdager alle tilfeller som samme type feil.

Når du arbeider med store mengder regnskapsdata, er det også lurt å gjennomgå Excels regionale innstillinger og eksportformatet til kildeprogrammet. Justering av disse parameterne for å sikre kompatibilitet med desimaltegn, valutasymboler og datoformater reduserer forekomsten av tall i tekstformat og utbredte feil betraktelig, og gjør at formler fungerer riktig fra starten av.

Kort sagt, selv om standard feilsjekk pleier å fungere rad for rad, er det mulig å korrigere store blokker med dårlig formaterte data i bulk og gjenopprette deres numeriske status uten å måtte gå celle for celle ved å kombinere advarslene med teknikker som lim inn spesial og konverteringsfunksjoner.

Hele dette settet med anbefalinger angående likhetstegnet, parenteser, områder, argumenttyper, tallformatering, eksterne referanser og feilkontrollverktøy har et felles mål: å gjøre det mulig for Excels nye intelligente feilsøkingsmotor å fungere med maksimal effektivitet. Når formler er godt konstruert og data bruker riktig type og format, blir systemet med varsler og forslag en kraftig alliert for raskt å finne inkonsekvenser, ødelagte referanser eller problematiske celler, selv i regneark med svært komplekse formler og data importert fra flere kilder.

Relatert artikkel:

De vanligste feilene i Excel-formler og hvordan du retter dem

Isaac

Lidenskapelig forfatter om verden av bytes og teknologi generelt. Jeg elsker å dele kunnskapen min gjennom å skrive, og det er det jeg skal gjøre i denne bloggen, vise deg alle de mest interessante tingene om dingser, programvare, maskinvare, teknologiske trender og mer. Målet mitt er å hjelpe deg med å navigere i den digitale verden på en enkel og underholdende måte.