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 fanen Hjem, 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
# 1 - Match data ved hjælp af VLOOKUP-funktion
VLOOKUP bruges ikke kun til at hente de krævede oplysninger fra datatabellen; snarere kan det også bruges som et forsoningsværktøj. Når det kommer til afstemning eller matchning af data, fører VLOOKUP-formlen tabellen.
Se f.eks. Nedenstående tabel.
Vi har to datatabeller her, den første er Data 1 og den anden er Data 2.
Nu skal vi afstemme, om dataene i de 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- tabellen, 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 træ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 for datoen 18. marts 2019 i Data 1 har vi 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-funktionen 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 et 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 en D3-celle.
Vælg derefter opslagsarray som salgsdatakolonne i Data 1.
Vælg "0 - Præcist match" i matchtypen .
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 de forskellige scenarier i realtid. Se dette nedenstående data for dette eksempel.
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 hvert enkelt tilfælde.
I celle I5 fik vi variansen 8300. Lad os se på hovedtabellen.
Selvom hovedtabelværdien er 12104, fik 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 en dato, dvs. 20. marts 2019. I den ovennævnte celle for nordzonen for samme dato har vi en værdi på 20404, så VLOOKUP har også returneret denne værdi for den østlige zone.
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.
Brug disse unikke værdier, lad os 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å frem til resultatet.
