Forskellige metoder til at matche data i Excel
Der er forskellige metoder til at matche data i Excel, hvis vi vil matche dataene i den samme kolonne, lad os sige, at vi vil kontrollere, om der er dobbelt, kan vi bruge betinget formatering fra hjemmefanen, ellers hvis vi vil matche dataene i to eller flere forskellige kolonner kan vi bruge betingede funktioner som hvis funktion.
- Metode nr. 1 - Brug af Vlookup-funktion
- Metode nr. 2 - Brug af Index + Match-funktion
- Metode nr. 3 - Opret din egen opslagsværdi
Lad os nu diskutere hver af metoderne detaljeret
Du kan downloade denne Excel-skabelon til matchdata her - Match Data Excel-skabelon# 1 - Match data ved hjælp af VLOOKUP-funktionen
VLOOKUP bruges ikke kun til at hente de krævede oplysninger fra datatabellen, men det kan også bruges som et afstemningsværktøj. Når det kommer til afstemning eller matchning af data, fører VLOOKUP-formlen tabellen.
For et eksempel se nedenstående tabel.
Vi har to datatabeller her, den første er Data 1 og den anden er Data 2.
Nu er vi nødt til at afstemme, om dataene i to tabeller matcher eller ej. Den allerførste måde at matche data på er SUM-funktionen i Excel til to tabeller for at få det samlede salg.
Data 1 - tabel
Data 2 - tabel
Jeg har anvendt SUM-funktionen for begge tabelens kolonne Salgsmængde. I selve begyndelsestrinet fik vi forskellen i værdier. Data 1- tabel, der viser det samlede salg på 2,16,214 og Data 2- tabel, der viser det samlede salg på 2,10,214 .
Nu skal vi undersøge dette detaljeret. Så lad os anvende VLOOKUP-funktionen for hver dato.
Vælg tabelarrayet som Data 1- interval.
Vi har brug for dataene fra den anden kolonne, og opslagsområdet er FALSK, dvs. eksakt match.
Outputtet er angivet nedenfor:
I den næste celle fratrækkes den oprindelige værdi med ankomstværdien.
Efter fradrag får vi resultatet som nul.
Kopier og indsæt nu formlen til alle cellerne for at få variansværdierne.
I celle G6 og G12 fik vi forskellene.
I data 1 har vi 12104 for datoen 04. marts 2019 og i data 2 har vi 15104 for samme dato, så der er en forskel på 3000.
Tilsvarende har vi for datoen 18. marts 2019 i Data 1 19351 og i Data 2 har vi 10351, så forskellen er 9000.
# 2 - Match data ved hjælp af INDEX + MATCH-funktion
Til de samme data kan vi bruge funktionen INDEX + MATCH. Vi kan bruge dette som et alternativ til VLOOKUP-funktionen.
INDEX-funktion bruges til at hente værdien fra den valgte kolonne baseret på det angivne række nummer. For at give række nummeret skal vi bruge MATCH-funktionen baseret på LOOKUP-værdien.
Åbn INDEX-funktionen i F3-cellen.
Vælg matrixen som resultatkolonneområde dvs. B2 til B14.
For at få række nummer skal du åbne MATCH-funktionen nu som det næste argument.
Vælg opslagsværdien som D3-celle.
Vælg derefter opslagsarray som salgsdatakolonne i Data 1.
I kamptypen skal du vælge “0 - Præcis match”.
Luk to parenteser og tryk på Enter-tasten for at få resultatet.
Dette giver også det samme resultat som kun VLOOKUP. Da vi har brugt de samme data, fik vi tallene, som de er
# 3 - Opret din egen opslagsværdi
Nu har vi set hvordan man matcher data ved hjælp af excel-funktioner. Nu vil vi se det forskellige scenario i realtid. For dette eksempel se nedenstående data.
I ovenstående data har vi Zone-Wise og Date-vis salgsdata som vist ovenfor. Vi skal igen foretage datatilpasningsprocessen. Lad os anvende VLOOKUP-funktionen som i det foregående eksempel.
Vi har mange afvigelser. Lad os undersøge hver enkelt sag for sag.
I celle I5 fik vi variansen 8300. Lad os se på hovedtabellen.
Selvom værdien i hovedtabellen er 12104, har vi værdien 20404 fra VLOOKUP-funktionen. Årsagen til dette er, at VLOOKUP kan returnere værdien af den først fundne opslagsværdi.
I dette tilfælde er vores opslagsværdi dato, dvs. 20. marts 2019. I ovenstående celle for nordzone for samme dato har vi en værdi på 20404, så VLOOKUP har også returneret denne værdi for østzone.
For at løse dette problem er vi nødt til at skabe unikke opslagsværdier. Kombiner zone, dato og salgsmængde i både data 1 og data 2.
Data 1 - tabel
Data 2 - tabel
Nu har vi skabt unik værdi for hver zone med den kombinerede værdi af Zone, salgsdato og salgsbeløb.
Ved hjælp af disse unikke værdier kan vi anvende VLOOKUP-funktionen.
Anvend formlen på alle cellerne, vi får variansen nul i alle cellerne.
Som dette kan vi ved hjælp af excel-funktioner matche dataene og finde afvigelser. Før vi anvender formlen, skal vi se på duplikaterne i opslagsværdien for nøjagtig afstemning. Ovenstående eksempel er den bedste illustration af duplikatværdier i opslagsværdi. I sådanne scenarier er vi nødt til at skabe vores egne unikke opslagsværdier og nå resultatet.