Ulike metoder for å matche data i Excel
Det er forskjellige metoder for å matche data i Excel, hvis vi vil matche dataene i den samme kolonnen, la oss si at vi vil sjekke om det er duplikat, kan vi bruke betinget formatering fra hjemmefanen, ellers hvis vi vil matche dataene i to eller mer forskjellige kolonner kan vi bruke betingede funksjoner som om funksjon.
- Metode nr. 1 - Bruke Vlookup-funksjonen
- Metode nr. 2 - Bruk av indeks + trefffunksjon
- Metode nr. 3 - Lag din egen oppslagsverdi
La oss nå diskutere hver av metodene i detalj
Du kan laste ned denne Excel-malen for Matchdata her - Match Data Excel-malen# 1 - Match data ved hjelp av VLOOKUP-funksjonen
VLOOKUP brukes ikke bare for å få den nødvendige informasjonen fra datatabellen, men den kan også brukes som et forsoningsverktøy. Når det gjelder avstemming eller matching av data, viser VLOOKUP-formelen tabellen.
For et eksempel se tabellen nedenfor.
Vi har to datatabeller her, den første er Data 1 og den andre er Data 2.
Nå må vi forene om dataene i to tabeller samsvarer eller ikke. Den aller første måten å matche dataene på er SUM-funksjonen i Excel til to tabeller for å få totalt salg.
Data 1 - Tabell
Data 2 - Tabell
Jeg har brukt SUM-funksjonen for begge tabellens kolonne Salgsmengde. Ved selve begynnelsesteget fikk vi forskjellen i verdier. Data 1- tabell som viser totalt salg på 2,16,214 og Data 2- tabell som viser totalt salg på 2,10,214 .
Nå må vi undersøke dette i detalj. Så la oss bruke VLOOKUP-funksjonen for hver dato.
Velg tabelloppsettet som Data 1- område.
Vi trenger dataene fra den andre kolonnen, og rekkevidden for oppslag er FALSE, dvs. eksakt samsvar.
Resultatet er gitt nedenfor:
I den neste cellen trekker du ut den opprinnelige verdien med ankomstverdien.
Etter å ha trukket får vi resultatet som null.
Kopier og lim inn formelen til alle cellene for å få variansverdiene.
I celle G6 og G12 fikk vi forskjellene.
I Data 1 har vi 12104 for datoen 04. mars 2019 og i Data 2 har vi 15104 for samme dato, så det er en forskjell på 3000.
Tilsvarende, for datoen 18. mars 2019 i Data 1, har vi 19351 og i Data 2 har vi 10351, så forskjellen er 9000.
# 2 - Match data ved hjelp av INDEX + MATCH-funksjon
For de samme dataene kan vi bruke INDEX + MATCH-funksjonen. Vi kan bruke dette som et alternativ til VLOOKUP-funksjonen.
INDEX-funksjonen brukes til å hente verdien fra den valgte kolonnen basert på angitt radnummer. For å oppgi radnummeret må vi bruke MATCH-funksjonen basert på LOOKUP-verdien.
Åpne INDEX-funksjonen i F3-cellen.
Velg matrisen som resultatkolonneområde, dvs. B2 til B14.
For å få radnummeret, åpne MATCH-funksjonen nå som neste argument.
Velg oppslagsverdien som D3-celle.
Velg deretter oppslagsmatrise som salgsdatakolonne i Data 1.
I kamptypen velger du “0 - Nøyaktig samsvar”.
Lukk to parenteser og trykk enter-tasten for å få resultatet.
Dette gir også det samme resultatet som bare VLOOKUP. Siden vi har brukt de samme dataene, fikk vi tallene som de er
# 3 - Lag din egen oppslagsverdi
Nå har vi sett hvordan vi kan matche data ved hjelp av Excel-funksjoner. Nå vil vi se det forskjellige scenariet i sanntid. For dette eksemplet, se på dataene nedenfor.
I dataene ovenfor har vi Zone-Wise og Date-vis salgsdata som vist ovenfor. Vi må igjen gjøre datatilpasningsprosessen. La oss bruke VLOOKUP-funksjonen som i forrige eksempel.
Vi har mange avvik. La oss undersøke hvert enkelt tilfelle.
I celle I5 fikk vi variansen 8300. La oss se på hovedtabellen.
Selv om verdien i hovedtabellen er 12104, fikk vi verdien 20404 fra VLOOKUP-funksjonen. Årsaken til dette er at VLOOKUP kan returnere verdien til den første funnet oppslagsverdien.
I dette tilfellet er oppslagsverdien vår dato dvs. 20. mars 2019. I cellen ovenfor for Nord-sone for samme dato har vi en verdi på 20404, så VLOOKUP har også returnert denne verdien for Øst-sone.
For å løse dette problemet, må vi lage unike oppslagsverdier. Kombiner sone, dato og salgsmengde i både data 1 og data 2.
Data 1 - Tabell
Data 2 - Tabell
Nå har vi opprettet unik verdi for hver sone med den kombinerte verdien Sone, Salgsdato og Salgsbeløp.
Ved å bruke disse unike verdiene kan vi bruke VLOOKUP-funksjonen.
Bruk formelen på alle cellene, vi får variansen null i alle cellene.
Slik kan vi ved hjelp av excel-funksjoner matche dataene og finne avvik. Før du bruker formelen, må vi se på duplikatene i oppslagsverdien for nøyaktig avstemming. Ovenfor eksempel er den beste illustrasjonen av duplikatverdier i oppslagsverdi. I slike scenarier må vi lage våre egne unike oppslagsverdier og komme frem til resultatet.