Sammenligne to kolonner i Excel
Kort svar
Den enkleste måten å sammenligne to kolonner på er =ANTALL.HVIS(kolonne2; A2), som gir 0 for verdier som mangler i den andre listen. Skal du hente en verdi samtidig, bruker du XOPPSLAG. Skal du bare se forskjellene visuelt, bruker du betinget formatering. Skal sammenligningen gjentas hver måned, hører den hjemme i Power Query.
«Sammenligne to kolonner» kan bety to helt forskjellige ting, og det er verdt å avklare hvilken du er ute etter før du velger metode:
- Rad mot rad: er verdien i A2 lik verdien i B2?
- Liste mot liste: finnes verdien i A2 et eller annet sted i kolonne B?
Det andre er det folk oftest trenger, og det er den vi bruker mest plass på.
Metode 1: ANTALL.HVIS – enkleste svar
=ANTALL.HVIS($B$2:$B$500; A2)
Formelen teller hvor mange ganger verdien i A2 finnes i kolonne B. 0 betyr at
den mangler. Vil du ha et lesbart svar med en gang:
=HVIS(ANTALL.HVIS($B$2:$B$500; A2)=0; "Mangler i B"; "Finnes")
Fordelen er at den er lett å lese og lett å filtrere på etterpå. Ulempen er at den bare svarer ja eller nei – du får ikke hentet noen verdi.
Merk at tallet den gir er nyttig i seg selv: står det 3, finnes verdien tre
ganger i den andre listen, og da har du sannsynligvis et duplikatproblem i
tillegg. Se fjerne duplikater i Excel.
Merk: ANTALL.HVIS skiller ikke mellom store og små bokstaver, og den tolker
* og ? som jokertegn. Har du produktkoder med stjerne i, gir det uventede
treff. Da bruker du =SUMMERPRODUKT(--EKSAKT($B$2:$B$500; A2)) i stedet.
Metode 2: XOPPSLAG – når du også skal hente noe
Skal du ikke bare vite om verdien finnes, men også hente prisen, statusen eller datoen som hører til:
=XOPPSLAG(A2; $B$2:$B$500; $C$2:$C$500; "Finnes ikke")
Har du Excel 2019 eller eldre:
=HVISFEIL(INDEKS($C$2:$C$500; SAMMENLIGNE(A2; $B$2:$B$500; 0)); "Finnes ikke")
Se XOPPSLAG i Excel for detaljene, og #I/T i Excel hvis oppslaget ikke treffer.
Metode 3: FILTER – få bare avvikene i en liste
Har du Microsoft 365 eller Excel 2021, slipper du hjelpekolonnen helt. Denne formelen gir deg en ferdig liste over alt som finnes i A, men ikke i B:
=FILTER(A2:A500; ANTALL.HVIS($B$2:$B$500; A2:A500)=0; "Ingen avvik")
Bytt om på kolonnene for å få listen andre veien. To slike formler ved siden av hverandre gir deg en fullstendig avstemming på to linjer, som oppdaterer seg selv når dataene endres.
Dette er ofte den raskeste veien fra spørsmål til svar, og det som er lettest å sende videre til noen andre – de slipper å filtrere på en hjelpekolonne for å se poenget.
Metode 4: betinget formatering – bare se forskjellene
Skal du ikke regne videre på resultatet, men bare se hva som skiller seg ut:
- Marker kolonne A, fra A2 og nedover.
- Hjem → Betinget formatering → Ny regel → Bruk en formel.
- Skriv
=ANTALL.HVIS($B$2:$B$500; A2)=0. - Velg en farge.
Nå markeres alle verdier i A som ikke finnes i B. Merk at reglene bruker den øverste cellen i markeringen som utgangspunkt – står du i A2 når du lager regelen, må formelen referere til A2. Er du i tvil, sjekk regelen etterpå under Betinget formatering → Behandle regler.
Excel har også en innebygd variant: marker begge kolonnene, velg Betinget formatering → Regler for merking av celler → Dupliserte verdier. Den er rask, men markerer duplikater innenfor hver kolonne også, ikke bare på tvers – så den svarer på et litt annet spørsmål enn du tror.
Metode 5: Power Query – når det skal gjentas
Skal sammenligningen gjøres hver måned med nye filer, er formler feil verktøy. I Power Query laster du inn begge listene, og bruker Slå sammen spørringer med koblingstypen:
- Venstre anti – rader som bare finnes i den første listen.
- Høyre anti – rader som bare finnes i den andre.
- Indre – rader som finnes i begge.
Resultatet er en tabell du kan oppdatere med ett klikk neste gang. Du ser også umiddelbart hvor mange rader som ikke fant treff, noe som er lett å overse med formler. Mer om dette i Power Query i Excel.
Gevinsten er størst når sammenligningen er en fast rutine – en månedlig avstemming mellom to systemer, for eksempel. Da er jobben gjort én gang, og alle senere kjøringer er ett klikk.
Sammenligne rad mot rad
Er de to kolonnene allerede parvis sortert – gammelt beløp mot nytt beløp, for eksempel – er spørsmålet et helt annet:
=HVIS(A2=B2; "Lik"; "Ulik")
Skal store og små bokstaver telle med:
=HVIS(EKSAKT(A2; B2); "Lik"; "Ulik")
Og for tall der små avrundingsforskjeller ikke skal regnes som avvik:
=HVIS(ABS(A2-B2)<0,01; "Lik"; "Avvik")
Den siste er viktigere enn den ser ut. To beløp som begge vises som 1 250,00
kan være 1250,004 og 1249,997 bak kulissene, og da gir =A2=B2 USANN uten
at noe egentlig er galt.
Rask snarvei uten formel: marker begge kolonnene, trykk F5 → Utvalg → Radforskjeller. Excel markerer da alle celler som avviker fra cellen lengst til venstre i sin rad. Gi dem en farge med én gang, så ser du avvikene uten å ha lagt til en eneste kolonne.
Sammenligne kolonner i to ulike ark eller filer
I samme arbeidsbok er det bare å peke på det andre arket:
=ANTALL.HVIS(Fjor!$A$2:$A$500; A2)
Mellom to filer virker det også, så lenge begge er åpne:
=ANTALL.HVIS([Fjor.xlsx]Ark1!$A$2:$A$500; A2)
Men vær klar over hva du får: formelen inneholder nå en filsti. Flytter noen
filen, får du #REF!. Er filen lukket, blir oppdateringen treg. Skal to filer
sammenlignes mer enn én gang, er Power Query det riktige verktøyet – det leser
fra lukkede filer uten problemer.
Når verdiene ser like ut, men ikke matcher
Dette er den vanligste årsaken til at sammenligningen gir feil svar:
| Problem | Slik oppdager du det | Løsning |
|---|---|---|
| Tall lagret som tekst | =ERTALL(A2) gir USANN |
Data → Tekst til kolonner → Fullfør |
| Mellomrom på slutten | =LENGDE(A2) er høyere enn antall tegn |
=TRIMME(A2) |
| Hardt mellomrom | TRIMME hjelper ikke | =BYTT.UT(A2; TEGNKODE(160); "") |
| Ledende nuller | 00241 mot 241 |
Standardiser formatet i kilden |
| Ulik skrivemåte | Manuelt gjennomsyn | Standardiser i kilden |
| Skjulte tegn fra eksport | =RENSK(A2) endrer lengden |
=RENSK(TRIMME(A2)) |
| Avrunding på beløp | =A2-B2 er ikke null |
=ABS(A2-B2)<0,01 |
Har du mange rader, lag én ren hjelpekolonne med
=TRIMME(BYTT.UT(A2; TEGNKODE(160); "")) i begge listene, og sammenlign de rene
kolonnene mot hverandre. Det er raskere enn å pakke inn hver enkelt formel, og
det gjør at du kan se selv hva som faktisk sammenlignes.
Sjekk begge veier
En feil som går igjen: du sjekker hva som finnes i A, men ikke i B – og stopper der. En fullstendig avstemming krever begge retninger:
| Spørsmål | Formel |
|---|---|
| Hva mangler i B? | =ANTALL.HVIS($B$2:$B$500; A2)=0 |
| Hva mangler i A? | =ANTALL.HVIS($A$2:$A$500; B2)=0 |
| Hvor mange i alt? | =SUMMERPRODUKT(--(ANTALL.HVIS($B$2:$B$500; A2:A500)=0)) |
Den siste gir deg antallet direkte, uten hjelpekolonne. Det er tallet du bør notere før og etter en opprydding, så du kan vise at avviket faktisk ble mindre.
Kort oppsummert
| Du vil | Bruk |
|---|---|
| Vite om verdien finnes | ANTALL.HVIS |
| Hente en verdi som hører til | XOPPSLAG |
| Få en ferdig liste over avvikene | FILTER |
| Se forskjellene visuelt | Betinget formatering |
| Gjøre det hver måned | Power Query |
| Sammenligne rad mot rad | =HVIS(A2=B2; "Lik"; "Ulik") |
| Sammenligne beløp med avrunding | =ABS(A2-B2)<0,01 |
Vanlige spørsmål
Hvorfor sier Excel at verdiene er ulike når de ser like ut?
Nesten alltid mellomrom eller tall lagret som tekst. Test med =LENGDE(A2) mot antall synlige tegn, og med =ERTALL(A2) i begge kolonnene.
Kan jeg sammenligne to kolonner i forskjellige filer?
Ja, men det gjør filen treg og skjør. Er begge filene åpne, virker en vanlig formelreferanse. Skal det gjentas jevnlig, er Power Query langt bedre.
Hvordan finner jeg rader som er ulike, ikke bare verdier som mangler?
Sammenlign rad for rad med =HVIS(A2=B2; "Lik"; "Ulik"). Det tester én rad mot samme rad – ikke om verdien finnes et sted i den andre listen.
Hvordan sammenligner jeg to kolonner og markerer forskjellene med farge?
Marker den første kolonnen, velg Hjem, Betinget formatering, Ny regel, Bruk en formel, og skriv =ANTALL.HVIS($B$2:$B$500; A2)=0. Velg en farge. Da markeres alle verdier som ikke finnes i den andre kolonnen.
Hvordan får jeg en liste over verdiene som mangler, i stedet for en kolonne med ja og nei?
Med Microsoft 365 eller Excel 2021 bruker du FILTER: =FILTER(A2:A500; ANTALL.HVIS($B$2:$B$500; A2:A500)=0; "Ingen avvik"). Da får du bare avvikene, ferdig samlet.
Hvordan sammenligner jeg beløp som avviker med noen øre?
Test på differansen i stedet for på likhet: =HVIS(ABS(A2-B2)<0,01; "Lik"; "Avvik"). Små avvik skyldes ofte avrunding eller at det ene tallet er lagret med flere desimaler enn det vises med.
Skiller sammenligningen mellom store og små bokstaver?
Nei. Både = og ANTALL.HVIS behandler «nordvik» og «Nordvik» som like. Trenger du at de skal skilles, bruker du EKSAKT: =EKSAKT(A2; B2).
