Finne avvik i Excel – 5 enkle teknikker forklart

Når du arbeider med store datasett i Microsoft Excel, er det uunngåelig å støte på avvik i dataene. Å identifisere og fikse disse inkonsekvensene er avgjørende for å sikre nøyaktig rapportering og analyse.

De gode nyhetene? kommer med en rekke verktøy og teknikker for å hjelpe deg med å finne og løse disse problemene. I denne artikkelen vil du lære om noen av de mest effektive metodene. Dette inkluderer filtrering, betinget formatering og bruk av avanserte formler.

Ved å inkludere disse teknikkene i den daglige arbeidsflyten din, blir du mer effektiv i å administrere og analysere dataene dine. Til slutt fører til bedre beslutningstaking og forbedrede resultater for prosjektene dine.

Innholdsfortegnelse

• Forstå avvik i Excel
• 5 måter å finne avvik i Excel
• #1 – Finne avvik via manuell gjennomgang i Excel
• #2 – Bruk av filter i Excel for å oppdage og fikse inkonsistente data
• 3# – Utheving av avvik med betinget formatering
• #4 – Finne avvik med avanserte Excel-funksjoner
  • IF og IS funksjoner
  • VLOOKUP, HLOOKUP & XLOOKUP
  • KAMP
• #5 – Bruk av Excel-tillegg for avviksdeteksjon
• Hvordan kan du lage regler for datavalidering for å forhindre avvik?
• Vårt siste ord

Forstå avvik i Excel

Uoverensstemmelser i rader og kolonner kan skyldes menneskelige inndatafeil, dupliserte oppføringer eller uregelmessigheter i dataformatering.

Ta en titt på følgende data. Du vil legge merke til at kjønnet til Walter White og Wayne Cena er feilstavet – Mael i stedet for Male. Det er faktisk enkelt å legge merke til og fikse feilene i dette scenariet. Men det er ikke tilfellet med regneark som inneholder tusenvis av rader og kolonner.

For å finne og administrere disse avvikene kan du bruke følgende funksjoner:

• Filtre: Bruk filtre på spesifikke kolonner for å se unike verdier og raskt oppdage inkonsistente data.
• Betinget formatering: Bruk formateringsregler for å fremheve celler som oppfyller spesifikke kriterier eller data som ikke samsvarer mellom kolonner.
• Avanserte Excel-funksjoner: Bruk funksjoner som MATCH og for å sammenligne datasett og identifisere avvik mellom dem.

Ved å forstå hvordan du bruker disse funksjonene i Excel, kan du effektivt oppdage og rette opp avvik i datasettene dine.

5 måter å finne avvik i Excel

Det er 5 hovedmåter vi liker å takle avvik i Excel:

1. Manuell gjennomgang
2. Filtrering
3. Betinget formatering
4. Avanserte Excel-funksjoner
5. Excel-tillegg

La oss gå gjennom hver av dem slik at du kan finne den beste måten som passer deg.

#1 – Finne avvik via manuell gjennomgang i Excel

Når du arbeider med , kan en manuell gjennomgang være en nyttig metode for å identifisere avvik i dataene dine. Denne prosessen krever nøye undersøkelse av datasettene for å oppdage eventuelle inkonsekvenser eller feil.

Her er noen trinn du kan følge for å utføre en manuell gjennomgang:

• Begynn med å forstå datasettene du jobber med tydelig. Gjør deg kjent med radene, kolonnene og formateringen.
• Bla gjennom dataene og følg nøye med informasjonen som presenteres i hver celle. Du kan bruke verktøy som celleutheving med fyllfarge, tekstformatering eller betinget formatering for å gjøre data lettere å lese.
• Sammenlign en rad med en annen eller en kolonne mot en annen. Se etter feilaktig informasjon, dupliserte oppføringer osv. som kan forårsake unøyaktigheter i Excel-arkene dine.
• Når du identifiserer avvik, lag en liste eller bruk kommentarer til å dokumentere dem. Senere kan disse fungere som viktige referanser for å rette opp problemene.

Når du manuelt gjennomgår data for avvik i et Excel-regneark, er tålmodighet nøkkelen. Denne prosessen er tidkrevende. Men det er et godt utgangspunkt for å identifisere potensielle feil.

#2 – Bruk av filter i Excel for å oppdage og fikse inkonsistente data

En av de enkleste måtene å oppdage inkonsistente data på er å bruke Excels filter .

Med Filter isolerer vi feil data i Kjønn- kolonnen i følgende datasett:

1. Velg Sorter og filtrer i kategorien Hjem .

2. Velg Filter – sørg for at markøren er innenfor dataområdet.

3. Klikk på rullegardinlistepilen i kolonnen med inkonsistente data.

4. Fjern nå alle de riktige samsvarende dataene. I vårt tilfelle er det Mann . Klikk deretter OK for å lagre endringene.

5. Excel-regnearket ditt vil nå bare vise rader med feil verdier. For å fikse dem, klikk Finn og velg i Hjem- fanen.

6. Klikk på Erstatt når rullegardinlisten vises.

7. Skriv inn feil verdi i Finn hva og den riktige i Erstatt med . Klikk deretter Erstatt alle .

8. En dialogboks vil vises som bekrefter endringene. Klikk OK for å fortsette.

3# – Utheving av avvik med betinget formatering

En annen måte å identifisere kolonne- og radforskjeller på er å bruke betinget formatering.

Du kan få tilgang til regler som fremheve celler, topp/bunn, datalinjer, fargeskalaer og jernsett. I tillegg kan du opprette en ny regel og administrere eller slette tidligere regler.

Vi bruker en eksempeltabell for å vise deg kraften i betinget formatering:

1. Velg tabellen eller foretrukne rader og kolonner. Vi velger kolonne A (selskap) og kolonne B (land). Deretter velger du Betinget formatering på Hjem- fanen.

2. Velg en regeltype og deretter regelen. Vi velger fremheve celleregler og dupliserte verdier...

3. Nå må vi velge om du vil utheve dupliserte eller unike verdier og fyllfarge og skrift for å formatere celler. Når du er ferdig, klikker du OK .

4. Alle dupliserte verdier er nå uthevet med lys rød fyll med mørk rød tekst.

#4 – Finne avvik med avanserte Excel-funksjoner

IF og IS funksjoner

For å sammenligne celler og identifisere forskjeller, kan du bruke IF- og IS-funksjoner.

For eksempel har vi brukt formelen =IF(A2=B2,"MATCH","NOT MATCHED")for å sammenligne cellene A2 og B2. Og vi har dratt formelen for å bruke den på andre celler. Så identiske celler returnerer MATCH ellers IKKE MATCHED .

Du kan kombinere IF med IS-funksjoner som ISNUMBER eller ISTEXT for å se etter spesifikke datatyper. For eksempel vil formelen =IF(ISNUMBER(B2), "Number", "NaN")returnere Number i kolonne c hvis B2 er numerisk og NaN ellers.

VLOOKUP, HLOOKUP & XLOOKUP

VLOOKUP (Vertikal) og HLOOKUP (Horisontal) funksjoner er nyttige når du sammenligner verdier i forskjellige kolonner eller rader i to tabeller. For å gjøre det, bruk følgende formel: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) eller =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).

Erstatt lookup_value med verdien du vil finne i den andre tabellen, table_array med celleområdet i den andre tabellen, col_index_num eller row_index_num med indeksnummeret til kolonnen eller raden du vil returnere en verdi fra, og [range_lookup] med FALSE for eksakte treff eller TRUE for en omtrentlig treff.

I den følgende opplæringen bruker vi VLOOKUP-funksjonen på kolonne E for å sammenligne medarbeidernavn i kolonne A og kolonne D for å trekke ut lønnen deres fra kolonne B hvis en match blir funnet.

XLOOKUP er en oppdatert versjon av VLOOKUP og HLOOKUP tilgjengelig på Excel 2021 og Excel 365. Til XLOOKUP-formelen er: =XLOOKUP(lookup_value, lookup_array, return_array,[if_not_found]).

Erstatt lookup_value med verdien du vil finne i den andre tabellen, lookup_array med celleområdet som skal søkes i den andre tabellen, return_array med matrisen du vil returnere en verdi fra, og [if_nout_found] med en tekstverdi hvis ingen samsvarer verdier er funnet.

Her er et raskt eksempel på XLOOKUP med de samme tabellene:

KAMP

MATCH-funksjonen kan også brukes til å sammenligne to lister for avvik.

MATCH-funksjonen, =MATCH(lookup_value, lookup_array, [match_type]), søker etter et spesifisert element i et område og returnerer den relative posisjonen til elementet innenfor dette området.

Her finner vi posisjonen til vannmeloner i matrisen A2:A8 ved å bruke MATCH. Vi bruker match_type som 0 for å finne den første verdien som er nøyaktig lik lookup_value .

#5 – Bruk av Excel-tillegg for avviksdeteksjon

Excel tilbyr ulike tillegg og innebygde verktøy som kan hjelpe deg med å oppdage og analysere avvik mer effektivt. I denne delen vil vi diskutere noen av disse nyttige tilleggene.

Vurder først å bruke Spreadsheet Inquire -tillegget. Dette kan hjelpe deg med å sammenligne to arbeidsbøker og fremheve forskjeller celle for celle. Slik aktiverer du Spreadsheet Inquire-tillegget:

1. Klikk på Fil- fanen.

2. Velg Alternativer , og klikk deretter kategorien Tillegg .

3. I Administrer- boksen velger du COM-tillegg og klikker på Gå .

4. Merk av for Spreadsheet Inquire- boksen og klikk OK .

5. Når den er aktivert, naviger til kategorien Spør for å bruke kommandoen Sammenlign filer .

Et annet nyttig verktøy for å oppdage avvik er Analysis ToolPak . Dette tillegget gir avanserte statistiske funksjoner og dataanalyseverktøy, som kan være nyttige når du analyserer avvik.

For å aktivere Analysis ToolPak, følg de samme trinnene som å aktivere Spreadsheet Inquire-tillegget. Men velg Excel-tillegg i Administrer- boksen og merk av for Analysis ToolPak- boksen.

Oppsummert tilbyr Excel ulike tillegg for å hjelpe deg med å oppdage og håndtere avvik i dataene dine. Du kan forbedre datanøyaktigheten ved å gjøre deg kjent med disse verktøyene og formlene.

Hvordan kan du lage regler for datavalidering for å forhindre avvik?

Følg disse trinnene for å lage datavalideringsregler i Excel som kan bidra til å forhindre avvik:

1. Velg cellene du vil begrense. Dette kan være en enkelt celle, et celleområde eller en hel kolonne.

2. Gå til kategorien Data på verktøylinjen, og klikk på knappen Datavalidering (merket med to horisontale bokser, en grønn hake og en sirkel med rødt kryss).

3. I vinduet Datavalidering, sørg for at du er på Innstillinger- fanen. Her vil du ha muligheten til å definere dine valideringskriterier basert på dine behov.

Det finnes ulike typer valideringskriterier tilgjengelig i Excel, hvorav noen inkluderer:

• Heltall – tillater bare hele tall innenfor et spesifisert område.
• Desimal – tillater bare desimaltall innenfor et spesifisert område.
• Liste – begrenser inndata til en forhåndsdefinert liste over akseptable verdier.
• Dato – krever datoer innenfor en bestemt datoperiode.
• Tid – godtar kun tider innenfor et bestemt tidsrom.
• Tekstlengde – håndhever begrensninger på lengden på teksten som legges inn.
• Egendefinert – lar deg lage tilpassede valideringsregler ved hjelp av Excel-formler.

Etter å ha valgt de riktige valideringskriteriene, spesifiser parametrene etter behov. For eksempel, hvis du velger helt tall, må du angi minimum og maksimum med en dataområdevalidator som mellom, lik, mindre enn osv.

I tillegg til valideringskriteriene kan du også sette opp egendefinerte feilmeldinger og input-tips for å hjelpe brukere med å forstå og overholde valideringsreglene. For å gjøre dette, bytt til kategoriene Input Message og Error Alert for å legge inn ønskede meldinger.

Ved å implementere regler for datavalidering reduserer du sannsynligheten for datainkonsekvenser betydelig og forbedrer den generelle nøyaktigheten til Excel-regnearkene dine.

Vårt siste ord

I denne artikkelen har du lært ulike metoder for å finne og adressere avvik i Excel-dataene dine. La oss oppsummere disse nøkkelstrategiene:

• Bruke Filter- rullegardinmenyene i kolonner for å identifisere unike verdier og inkonsistente data.
• Bruke betinget formatering i utvalgte celler for å fremheve avvik ved å bruke en haug med regelmaler.
• Bruk av avanserte Excel-funksjoner som MATCH , VLOOKUP , XLOOKUP osv. funksjoner for å krysssjekke data i to lister eller tabeller.
• Bruk av Excel-tillegg for å oppdage avvik.
• Sette opp datavalideringsregler for å forhindre feil i data

Med disse teknikkene i Excel-kompetansesettet vil dataene dine være mer nøyaktige og pålitelige.

Husk å øve på disse metodene regelmessig for å bli mer effektiv til å oppdage avvik i regnearkene dine.

Fortsett å finpusse ferdighetene dine ved å registrere deg for vår , og fortsett å utforske nye funksjoner og funksjoner for å bli enda dyktigere i Excel.