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 et mystisk problem dukker opp. En feil i Excel som du ikke vet hvor den kommer fra og gjenkjenne De vanligste feilene i Excel-formlerEtter hvert som formler blir lengre og mer komplekse, blir det nesten umulig å oppdage kilden til problemet med øyet, og mye tid går bort på å sjekke celle for celle.

Med den nye intelligente feilsøkingsmotoren og feilsøkingsverktøyene tilbyr Excel mer og mer hjelp til finne, forstå og korrigere feil i komplekse formlerFor å få mest mulig ut av dem er det imidlertid viktig å mestre noen grunnleggende regler for å skrive formler, forstå hva de forskjellige feilmeldingene betyr, og vite hvordan man automatiserer massekorrigeringer, spesielt når data importert fra andre programmer og de kommer i en rekke uvanlige formater.

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

Før man dykker ned i intelligente søkemotorer 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 forårsake problemer. en formel tolkes som tekst eller feiler fullstendig.

Det første og viktigste er det Hver formel må begynne med likhetstegnet (=)Hvis likhetstegnet 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 inn SUM(A1:A10) uten likhetstegnet, vil cellen bokstavelig talt vise SUM(A1:A10) og ikke utføre noen beregning. Noe lignende skjer med datoer: hvis du skriver inn 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 datofeil.

Et annet viktig poeng er riktig bruk av parenteser. Alle funksjoner som bruker dem må ha en velplassert åpnings- og lukkeparentesNår man jobber med nestede funksjonerDet er veldig vanlig å la én ekstra eller én manglende parentes være igjen, 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 tykktarmen (:) mellom den første og siste cellenOmrådet skrives for eksempel som A1:A5. Hvis du utelater kolonet og ender opp med noe sånt som =SUMMER(A1 A5), vil Excel tolke dette som ugyldige referanser mellom urelaterte celler og returnere en feil. #NULL!, som er den som vises når det ikke-eksisterende skjæringspunkter eller inkonsistente referanser.

Til slutt må antallet og rekkefølgen på argumentene for hver funksjon respekteres. Noen funksjoner krever obligatoriske argumenter og i en bestemt posisjonNoen funksjoner krever flere argumenter, mens andre tillater valgfrie parametere. Hvis du går tom for argumenter, eller legger til flere enn nødvendig, vil Excel advare deg med feilmeldinger relatert til funksjonen. Å kjenne 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å å bli sendt riktig datatype i hvert argumentEn 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 hvordan man kan kombinere funksjoner innenfor hverandre. Du kan ikke neste mer enn 64 funksjonsnivåer i en enkelt formelDette 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å i praksis når disse ytterpunktene, kan denne grensen overskrides i svært komplekse eller dårlig utformede modeller, noe som forårsaker feil som er vanskelige å tolke. Utover et visst nivå av kompleksitet er det vanligvis klokere å dele logikken inn i flere hjelpeceller i stedet for å prøve å konsentrere alt til en enkelt, ekstremt lang formel. For dette er det også lurt å gjennomgå Årsaker og løsninger på ytelsesproblemer i Excel.

Et typisk eksempel på en feil på grunn av argumenttype og -nummer er ABS-funksjonen. Denne funksjonen godtar bare et enkelt numerisk argument og returnerer den absolutte verdien. Hvis du skriver noe som =ABS(-2;134), 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 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 må bruk av skilletegn tas i betraktning avhengig av de regionale innstillingene. I mange spanske installasjoner av Excel brukes følgende: semikolon (;) som argumentseparator og kommaet (,) 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 ender opp med å generere ugyldige referansemeldinger. Excel trenger deg til å Navnene på ark, bøker og ruter er skrevet på en veldig presis måte for å finne dataene riktig.

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

I tillegg, når en formel peker til et annet ark, må det plasseres en bindestrek rett etter navnet. utropstegn (!)Utropstegnet (```) indikerer 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 informasjonen er i en annen arbeidsbok, trenger Excel mer kontekst. I disse tilfellene må den eksterne referansen inkludere filnavnet i parentes, arknavnet og områdetHvis du for eksempel vil telle antall rader i området A1:A8 i arbeidsboken Operasjoner T2.xlsx, kan du bruke en formel som =RADER('Salg'!A1:A8) i salgsarket. 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 man skriver inn banen, arbeidsbokens navn, arknavnet eller hakeparentesene, vil resultatet være en feil. 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 mye data, spesielt når det kommer fra andre applikasjoner, er bruken av Feil tallformater i formlerExcel skiller tydelig mellom den faktiske verdien som er lagret i en celle og formateringen som brukes til å vise den verdien. Å 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, må den vises i formelen som 1000, uten €-tegnet, tusenskilletegn eller komma. Hvis du skriver 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 tenkt. Tall formateres med valuta, tusenskilletegn eller desimalskilletegn. deretterBruk av celleformateringsalternativer, ikke innenfor uttrykket; for mer informasjon om hvordan du bruker formater riktig, se dataformat 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 en numerisk verdi. Hvis du prøver å skrive komma eller semikolon i tallet for å simulere tusener, vil Excel anse det som flere separate argumenter, og funksjonen vil ikke fungere. vil returnere en syntaksfeilDerfor fungerer ikke uttrykk som =ABS(-2;134), mens =ABS(-2134) er gyldige.

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 å oppbevare lagrede data som rå tall i celler uten spesiell formatering Bruk deretter den visuelle formateringen for valuta, prosent eller tusenskilletegn fra formateringsmenyen. Dette forhindrer at symboler forstyrrer beregningen og lar feilsøkingsmotoren bedre oppdage inkonsekvenser.

Intelligent feilsjekking og feilsøking i Excel

I tillegg til å korrigere syntaks, inneholder Excel et system med feilkontroll som analyserer formler og foreslår rettelser Når den oppdager noe uvanlig, hjelper denne stadig mer intelligente motoren 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 det komplette settet med regler for formelgjennomgang Tilgjengelig 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 verifiseringsmotoren og forbedringer av smarte feilsøkingsverktøy, anbefales det å sjekke dokumentasjonen fra tid til annen. Microsoft Excel offisielle bloggDet er her nyheter, oppdateringer og endringer i skrivebords- og nettversjonene publiseres, slik at du vet når nye feilrettingfunksjoner 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 er denne typen applikasjoner De eksporterer de numeriske beløpene som tekstResultatet 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 fanen Formler > Redigere formler > Feilkontroll, blir korrigeringen vanligvis brukt som standard. celle for cellenoe som i en GL med tusenvis av rader kan være vanvittig.

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 som kan brukes direkte fra Excel på skrivebordet for å løse denne typen situasjoner. En av de mest direkte innebærer å bruke spesiallim med matematisk operasjonDu 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, slik at det samme beløpet bevares. 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 å kombinere advarslene med teknikker som spesielle lime- og konverteringsfunksjoner å korrigere store blokker med feilformaterte data i bulk og gjenopprette deres numeriske status uten å måtte gå celle for celle.

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.