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 Index + Match-funksjon
- Metode nr. 3 - Lag din egen oppslagsverdi
La oss nå diskutere hver av metodene i detalj
# 1 - Match data ved hjelp av VLOOKUP-funksjonen
VLOOKUP brukes ikke bare for å hente nødvendig informasjon fra datatabellen; heller, det kan også brukes som et forsoningsverktøy. Når det gjelder avstemming eller samsvar med dataene, fører VLOOKUP-formelen tabellen.
Se for eksempel på 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 de to tabellene 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 oppslagsområdet er FALSE, dvs. eksakt samsvar.
Resultatet er gitt nedenfor:
I den neste cellen trekker du 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 har vi for datoen 18. mars 2019 i Data 1 19351, og i Data 2 har vi 10351, så forskjellen er 9000.
# 2 - Match data ved hjelp av INDEX + MATCH-funksjonen
For de samme dataene kan vi bruke INDEX + MATCH-funksjonen. Vi kan bruke dette som et alternativ til VLOOKUP-funksjonen.
INDEKS-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 et resultatkolonneområde, dvs. B2 til B14.
For å få radnummeret, åpne MATCH-funksjonen nå som neste argument.
Velg oppslagsverdien som en D3-celle.
Velg deretter oppslagsmatrise som salgsdatakolonne i Data 1.
I samsvarstypen 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 de forskjellige scenariene i sanntid. For dette eksemplet, se på dataene nedenfor.
I ovennevnte data har vi Zone-Wise og Date-wise 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 hovedtabellverdien 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 en dato, dvs. 20. mars 2019. I cellen ovenfor for Nord-sonen for samme dato har vi en verdi på 20404, så VLOOKUP har også returnert denne verdien for Øst-sonen.
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, la oss bruke VLOOKUP-funksjonen.
Bruk formelen på alle cellene; vi vil få variansen null i alle cellene.
Som dette, ved å bruke excel-funksjoner, kan vi matche dataene og finne avvik. Før du bruker formelen, må vi se på duplikatene i oppslagsverdien for nøyaktig avstemming. Ovenstående eksempel er den beste illustrasjonen av duplikatverdier i oppslagsverdi. I slike scenarier må vi lage våre egne unike oppslagsverdier og komme til resultatet.
