Verschillende methoden om gegevens in Excel te matchen
Er zijn verschillende methoden om gegevens in Excel te matchen, als we de gegevens in dezelfde kolom willen matchen, laten we zeggen dat we willen controleren op dupliciteit, we kunnen voorwaardelijke opmaak gebruiken vanaf het starttabblad of anders als we de gegevens in twee of meer verschillende kolommen kunnen we voorwaardelijke functies gebruiken, zoals if-functie.
- Methode # 1 - Vlookup-functie gebruiken
- Methode # 2 - Index + Match-functie gebruiken
- Methode # 3 - Creëer uw eigen opzoekwaarde
Laten we nu elk van de methoden in detail bespreken
U kunt deze Match Data Excel-sjabloon hier downloaden - Match Data Excel-sjabloon# 1 - Gegevens matchen met de functie VERT.ZOEKEN
VERT.ZOEKEN wordt niet alleen gebruikt om de vereiste informatie uit de gegevenstabel te halen, maar kan ook worden gebruikt als afstemmingshulpmiddel. Als het gaat om afstemming of afstemming van de gegevens, leidt VLOOKUP-formule de tabel.
Kijk voor een voorbeeld in onderstaande tabel.
We hebben hier twee gegevenstabellen, de eerste is Data 1 en de tweede is Data 2.
Nu moeten we afstemmen of de gegevens in twee tabellen overeenkomen of niet. De allereerste manier om de gegevens te matchen is de SOM-functie in Excel met twee tabellen om de totale verkoop te krijgen.
Gegevens 1 - Tabel
Gegevens 2 - Tabel
Ik heb de SOM-functie toegepast voor de kolom Verkoopbedrag van de tabel. Bij het begin zelf kregen we het verschil in waarden. Tabel met gegevens 1 met de totale verkoop van 2,16,214 en tabel met gegevens 2 met de totale verkoop van 2,10,214 .
Nu moeten we dit in detail onderzoeken. Laten we dus de functie VERT.ZOEKEN voor elke datum toepassen.
Selecteer de tabelmatrix als bereik Gegevens 1 .
We hebben de gegevens uit de tweede kolom nodig en het opzoekbereik is FALSE, dwz Exact Match.
De output wordt hieronder gegeven:
Trek in de volgende cel de oorspronkelijke waarde af met de aankomstwaarde.
Na aftrek krijgen we het resultaat als nul.
Kopieer en plak nu de formule in alle cellen om de variantie-waarden te krijgen.
In cel G6 en G12 hebben we de verschillen.
In Data 1 hebben we 12104 voor de datum 04-mrt-2019 en in Data 2 hebben we 15104 voor dezelfde datum, dus er is een verschil van 3000.
Evenzo hebben we voor de datum 18-maart-2019 in Data 1 19351 en in Data 2 10351, dus het verschil is 9000.
# 2 - Match gegevens met behulp van INDEX + MATCH-functie
Voor dezelfde gegevens kunnen we de INDEX + MATCH-functie gebruiken. We kunnen dit gebruiken als een alternatief voor de functie VERT.ZOEKEN.
INDEX-functie die wordt gebruikt om de waarde uit de geselecteerde kolom op te halen op basis van het opgegeven rijnummer. Om het rijnummer op te geven, moeten we de MATCH-functie gebruiken op basis van de LOOKUP-waarde.
Open de INDEX-functie in de F3-cel.
Selecteer de array als resultaatkolombereik, dwz B2 tot B14.
Om het rijnummer nu te openen MATCH-functie als het volgende argument.
Selecteer de opzoekwaarde als D3-cel.
Selecteer vervolgens de opzoekmatrix als kolom Verkoopdatum in Gegevens 1.
Selecteer in het zoektype "0 - Exact Match".
Sluit twee haakjes en druk op de Enter-toets om het resultaat te krijgen.
Dit geeft ook hetzelfde resultaat als alleen VERT.ZOEKEN. Omdat we dezelfde gegevens hebben gebruikt, hebben we de cijfers zoals ze zijn
# 3 - Creëer uw eigen opzoekwaarde
Nu hebben we gezien hoe we gegevens kunnen matchen met behulp van Excel-functies. Nu zullen we het verschillende scenario van realtime zien. Bekijk voor dit voorbeeld de onderstaande gegevens.
In de bovenstaande gegevens hebben we verkoopgegevens voor Zone-Wise en Date, zoals hierboven weergegeven. We moeten het gegevensafstemmingsproces opnieuw uitvoeren. Laten we de functie VERT.ZOEKEN toepassen zoals in het vorige voorbeeld.
We hebben veel verschillen. Laten we elk geval per geval bekijken.
In cel I5 hebben we de variantie van 8300. Laten we naar de hoofdtabel kijken.
Hoewel in de hoofdtabel de waarde 12104 is, hebben we de waarde van 20404 uit de functie VERT.ZOEKEN. De reden hiervoor is dat VERT.ZOEKEN de waarde van de eerst gevonden zoekwaarde kan retourneren.
In dit geval is onze opzoekwaarde de datum, dwz 20 maart 2019. In de bovenstaande cel voor zone Noord voor dezelfde datum hebben we een waarde van 20404, dus VERT.ZOEKEN heeft deze waarde ook voor zone Oost geretourneerd.
Om dit probleem op te lossen, moeten we unieke opzoekwaarden maken. Combineer zone, datum en verkoopbedrag in zowel data 1 als data 2.
Gegevens 1 - Tabel
Gegevens 2 - Tabel
Nu hebben we een unieke waarde gecreëerd voor elke zone met de gecombineerde waarde van Zone, Verkoopdatum en Verkoopbedrag.
Met behulp van deze unieke waarden kunnen we de functie VERT.ZOEKEN toepassen.
Pas de formule toe op alle cellen, we krijgen de variantie van nul in alle cellen.
Op deze manier kunnen we door Excel-functies te gebruiken de gegevens matchen en de varianties vinden. Voordat we de formule toepassen, moeten we de duplicaten in de opzoekwaarde bekijken voor een nauwkeurige afstemming. Het bovenstaande voorbeeld is de beste illustratie van dubbele waarden in opzoekwaarde. In dergelijke scenario's moeten we onze eigen unieke opzoekwaarden creëren en tot het resultaat komen.
