In dit artikel zullen we leren hoe u de beste match kunt opzoeken met de INDEX & MATCH-functie in Excel.
Scenario:
We moeten bijvoorbeeld de numeriek dichtste overeenkomst en de bijbehorende resultaten uit de tabel vinden. We hebben een vaste formule nodig die helpt bij het vinden van de beste match om de vereiste waarde te krijgen.
Hoe het probleem op te lossen?
Om de formule eerst te begrijpen, moeten we de volgende functies een beetje herzien:
- INDEX-functie
- MATCH-functie
Nu gaan we een formule maken met behulp van de bovenstaande functies. MATCH-functie retourneert de index van de dichtstbijzijnde overeenkomst uit het bereik. De functie INDEX neemt de rij-index als argument en retourneert de bijbehorende vereiste resultaten.
Generieke formule:
{ = INDEX (data, MATCH (MIN (ABS (data-waarde)), ABS (data-waarde), 0))}
gegevens : reeks datumwaarden
waarde : gegeven datumwaarde waarbij het resultaat het dichtst bij is
match_type : 1 (exact of op één na kleinste) of 0 (exacte overeenkomst) of -1 (exact of op één na grootste)
NIET DOEN gebruik accolades handmatig. accolades aangebracht met behulp van de Ctrl + Shift + Enter in plaats van gewoon Binnenkomen.
Voorbeeld:
De bovenstaande uitspraken kunnen ingewikkeld zijn om te begrijpen. Laten we dit dus begrijpen door de formule in een voorbeeld te gebruiken
Hier hebben we enkele willekeurige datumwaarden en een bepaalde datumwaarde.
Benoemd bereik gebruikt voor gegevens (D4:D12).
Hier hebben we in plaats daarvan het benoemde bereik gebruikt voor celverwijzingsarray omdat het gemakkelijk te begrijpen is. We moeten zoeken naar de datum die het dichtst bij de opgegeven datum in de G6-cel ligt.
Gebruik de bovenstaande formule om de dichtstbijzijnde datum te krijgen.
Gebruik de formule:
{ = INDEX (gegevens, MATCH (MIN (ABS (gegevens - G6)), ABS (gegevens - G6), 0)) }
Gebruik accolades NIET handmatig. accolades aangebracht met behulp van de Ctrl + Shift + Enter in plaats van gewoon Binnenkomen.
Uitleg:
- De ABS-functie retourneert het positieve gehele getal na de aftrekking die is uitgevoerd over (gegevens - G6) datumwaarden.
- ABS (data - G6) maakt een array door alle datumwaarden af te trekken van de gegeven datumwaarde in G6-cel. De geretourneerde array is.
{ 160; 49 ; 105 ; 1048 ; 600 ; 6 ; 702 ; 822 ; 673 }
- MIN (ABS (data - G6)) retourneert de dichtstbijzijnde waarde uit de array die is geretourneerd door de bovenstaande stap.
= INDEX (gegevens, MATCH (6, {160; 49; 105; 1048; 600; 6; 702; 822; 673}, 0))
- MATCH-functie komt overeen met de dichtstbijzijnde minimale waarde-overeenkomst in de geretourneerde array en retourneert de rij-index naar de INDEX-functie.
- De functie INDEX vindt de waarde die de ROW-index heeft geretourneerd.
data met de naam bereik gebruikt voor de data-array. Gebruik nu Ctrl + Shift + Enter op zijn plaats voor de zojuist Enter top krijg het resultaat omdat dit een matrixformule is. Matrixformule is een formule waarbij een reeks waarden van het ene functieresultaat als argument naar de buitenste functie gaat.
Zoals u kunt zien aan de bovenstaande momentopname, hebben we de dichtstbijzijnde overeenkomst verkregen van de gegeven datum in de tabel. Extraheer details uit de tabel met behulp van de bovengenoemde formule.
Hier zijn enkele observatienotities bij het gebruik van de bovenstaande formule.
Opmerkingen:
- De functie retourneert de fout #NA als het argument van de opzoekarray voor de functie VERGELIJKEN niet dezelfde lengte heeft als de tabelarray.
- De formule retourneert een fout als lookup_value niet overeenkomt met de waarde in de tabel lookup_array.
- De functie komt overeen met de exacte waarde, aangezien het argument voor het type overeenkomst met de functie VERGELIJKEN 0 is.
- De opzoekwaarden kunnen worden opgegeven als celverwijzing of rechtstreeks met het aanhalingsteken ( " ) in de formule als argumenten.
- Gebruik accolades NIET handmatig. accolades aangebracht met behulp van de Ctrl + Shift + Enter in plaats van gewoon Binnenkomen of anders retourneert het een fout.
Ik hoop dat je hebt begrepen hoe je de laagste of kleinste overeenkomst kunt opzoeken met behulp van de INDEX & MATCH-functie in Excel. Ontdek hier meer artikelen over Excel-opzoekwaarde en Excel 2019-functies. Aarzel niet om uw vragen hieronder in het opmerkingenveld te vermelden. We zullen je zeker helpen.
Als je onze blogs leuk vond, deel deze dan met je vrienden op Facebook. En je kunt ons ook volgen op Twitter en Facebook. We horen graag van je, laat ons weten hoe we ons werk kunnen verbeteren, aanvullen of vernieuwen en het voor jou beter kunnen maken. Schrijf ons op de e-mailsite
Gebruik INDEX en MATCH om waarde op te zoeken : INDEX & MATCH-functie om desgewenst de waarde op te zoeken.
SUM-bereik met INDEX in Excel : Gebruik de INDEX-functie om de SUM van de waarden te vinden zoals vereist.
Hoe de INDEX-functie in Excel te gebruiken? : Zoek de INDEX van array met behulp van de INDEX-functie uitgelegd met een voorbeeld.
Hoe de MATCH-functie in Excel te gebruiken? : Zoek de MATCH in de array met behulp van de INDEX-waarde in de MATCH-functie uitgelegd met een voorbeeld.
Hoe de LOOKUP-functie in Excel te gebruiken? : Zoek de opzoekwaarde in de array met behulp van de LOOKUP-functie die met een voorbeeld wordt uitgelegd.
Hoe de VERT.ZOEKEN-functie in Excel te gebruiken? : Zoek de opzoekwaarde in de array met behulp van de functie VERT.ZOEKEN, uitgelegd aan de hand van een voorbeeld.
populaire artikels
50 Excel-snelkoppeling om uw productiviteit te verhogen
Een vervolgkeuzelijst bewerken
Absolute referentie in Excel
Indien met voorwaardelijke opmaak
Indien met jokertekens
De Excel 2019 CONCAT-functie
De Excel 2019 MAXIFS-functie