Excel-functies kunnen buitengewoon krachtig zijn als u het combineren van verschillende formules onder de knie krijgt. Dingen die misschien onmogelijk leken, zouden plotseling op kinderspel gaan lijken.
Een voorbeeld hiervan is het vinden van de dichtstbijzijnde overeenkomst van een opzoekwaarde in een gegevensset in Excel.
Er zijn een aantal handige opzoekfuncties in Excel (zoals VERT.ZOEKEN & INDEX MATCH), die in een paar eenvoudige gevallen de beste overeenkomst kunnen vinden (zoals ik zal laten zien met onderstaande voorbeelden).
Maar het beste is dat u deze opzoekfuncties kunt combineren met andere Excel-functies om nog veel meer te doen (inclusief het vinden van de beste overeenkomst van een opzoekwaarde in een ongesorteerde lijst).
In deze zelfstudie laat ik u zien hoe u de dichtstbijzijnde overeenkomst van een opzoekwaarde in Excel vindt met opzoekformules.
Vind de dichtstbijzijnde overeenkomst in Excel
Er kunnen veel verschillende scenario's zijn waarin u moet zoeken naar de dichtstbijzijnde overeenkomst (of de dichtstbijzijnde overeenkomende waarde).
Hieronder staan de voorbeelden die ik in dit artikel zal behandelen:
- Vind het commissietarief op basis van verkopen
- Vind de beste kandidaat (op basis van de dichtstbijzijnde ervaring)
- De volgende evenementdatum vinden
Laten we beginnen!
Klik hier om het voorbeeldbestand te downloaden
Vind commissietarief (op zoek naar de dichtstbijzijnde verkoopwaarde)
Stel u heeft een dataset zoals hieronder weergegeven waar u de commissietarieven van al het verkooppersoneel wilt vinden.
De commissie wordt toegekend op basis van de verkoopwaarde. En dit wordt berekend met behulp van de tabel aan de rechterkant.
Als een verkoper bijvoorbeeld de totale verkoop van 5000 doet, dan is de commissie 0% en als hij/zij de totale verkoop van 15000 doet, is de commissie 5%.
Om het commissietarief te krijgen, moet u het dichtstbijzijnde verkoopbereik vinden dat net lager is dan de verkoopwaarde. Voor een verkoopwaarde van 15.000 zou de commissie bijvoorbeeld 10.000 zijn (wat 5% is) en voor een verkoopwaarde van 25.000 zou het commissiepercentage 20.000 zijn (dat is 7%).
Om de dichtstbijzijnde verkoopwaarde te vinden en het commissietarief te krijgen, kunt u de geschatte overeenkomst in VERT.ZOEKEN gebruiken.
De onderstaande formule zou dit doen:
=VERT.ZOEKEN(B2,$E$2:$F$6,2,1)
Merk op dat in deze formule het laatste argument 1 is, wat de formule vertelt om een benadering bij benadering te gebruiken. Dit betekent dat de formule de verkoopwaarden in kolom E zou doorlopen en de waarde zou vinden die net lager is dan de opzoekwaarde.
Vervolgens geeft de formule VERT.ZOEKEN het commissietarief voor deze waarde.
Opmerking: Om dit te laten werken, moet u de gegevens in oplopende volgorde hebben gesorteerd.Klik hier om het voorbeeldbestand te downloaden
Vind de beste kandidaat (op basis van de dichtstbijzijnde ervaring)
In het bovenstaande voorbeeld moesten de gegevens in oplopende volgorde worden gesorteerd. Maar er kunnen gevallen zijn waarin de gegevens niet worden gesorteerd.
Laten we dus een voorbeeld bespreken en kijken hoe we de beste overeenkomst in Excel kunnen vinden met behulp van een combinatie van formules.
Hieronder vindt u een voorbeeldgegevensset waarbij ik de naam van de werknemer moet vinden met de werkervaring die het dichtst bij de gewenste waarde ligt. De gewenste waarde in dit geval over 2,5 jaar.
Houd er rekening mee dat de gegevens niet zijn gesorteerd. Ook kan de dichtstbijzijnde ervaring minder of meer zijn dan de gevende ervaring. Bijvoorbeeld, 2 jaar en 3 jaar liggen beide even dicht bij elkaar (verschil van 0,5 jaar).
Hieronder is de formule die ons het resultaat zal geven:
=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))
De truc in deze formule is om de opzoekarray en de opzoekwaarde te wijzigen om het minimale ervaringsverschil tussen vereiste en werkelijke waarden te vinden.
Laten we eerst begrijpen hoe je het handmatig zou doen (en dan zal ik uitleggen hoe deze formule werkt).
Als je dit handmatig doet, ga je door elke cel in kolom B en vind je het verschil in de ervaring tussen wat nodig is en degene die een persoon heeft. Als je eenmaal alle verschillen hebt, zul je degene vinden die minimaal is en de naam van die persoon ophalen.
Dat is precies wat we doen met deze formule.
Laat het me uitleggen.
De opzoekwaarde in de MATCH-formule is MIN(ABS(D2-B2:B15)).
Dit deel geeft je het minimale verschil tussen de gegeven ervaring (die 2,5 jaar is) en alle andere ervaringen. In dit voorbeeld retourneert het 0.3
Merk op dat ik ABS heb gebruikt om ervoor te zorgen dat ik op zoek ben naar de dichtstbijzijnde (wat meer of minder kan zijn dan de gegeven ervaring).
Nu wordt deze minimumwaarde onze opzoekwaarde.
De opzoekarray in de MATCH-functie is ABS(D2-$B$2:$B$15).
Dit geeft ons een reeks getallen waarvan 2,5 (de vereiste ervaring) is afgetrokken.
Dus nu hebben we een opzoekwaarde (0,3) en een opzoekarray ({6.8;0.8;19.5;21.8;14.5;11.2;0.3;9.2;2;9.8;14.8;0.4;23.8;2.9})
MATCH-functie vindt de positie van 0.3 in deze array, wat ook de positie is van de naam van de persoon die de dichtstbijzijnde ervaring heeft.
Dit positienummer wordt vervolgens gebruikt door de INDEX-functie om de naam van de persoon te retourneren.
Let op: Als er meerdere kandidaten zijn met dezelfde minimale ervaring, geeft de bovenstaande formule de naam van de eerste overeenkomende medewerker.
Vind de volgende evenementdatum
Dit is een ander voorbeeld waarbij u opzoekformules kunt gebruiken om de volgende datum van een gebeurtenis te vinden op basis van de huidige datum.
Hieronder staat de dataset waar ik evenementnamen en de evenementdatums heb.
Wat ik wil is de naam van het volgende evenement en de datum van het evenement voor dit komende evenement.
Hieronder staat de formule die de naam van het aankomende evenement zal geven:
=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)
En onderstaande formule geeft de datum van het aanstaande evenement:
=INDEX($B$2:$B$11,MATCH(E1,$B$2:$B$11,1)+1)
Laat me uitleggen hoe deze formule werkt.
Om de gebeurtenisdatum te krijgen, zoekt de MATCH-functie naar de huidige datum in kolom B. In dit geval zoeken we niet naar een exacte overeenkomst, maar naar een geschatte. En daarom is het laatste argument van de MATCH-functie 1 (die de grootste waarde vindt die kleiner is dan of gelijk is aan de opzoekwaarde).
Dus de MATCH-functie retourneert de positie van de cel met de datum die net kleiner is dan of gelijk is aan de huidige datum. Dus de volgende gebeurtenis zou in dit geval in de volgende cel zijn (omdat de lijst in oplopende volgorde is gesorteerd).
Dus om de datum van de aanstaande gebeurtenis te krijgen, voegt u er gewoon een toe aan de celpositie die wordt geretourneerd door de MATCH-functie, en u krijgt de celpositie van de volgende gebeurtenisdatum.
Deze waarde wordt dan gegeven door de INDEX-functie.
Om de gebeurtenisnaam te krijgen, wordt dezelfde formule gebruikt en wordt het bereik in de INDEX-functie gewijzigd van kolom B in kolom A.
Klik hier om het voorbeeldbestand te downloaden
Het idee van dit voorbeeld kwam bij mij op toen een vriend mij benaderde met een verzoek. Hij had een lijst met alle verjaardagen van zijn vrienden/familieleden in een kolom en wilde de volgende verjaardag weten (en de naam van de persoon).Dit zijn drie voorbeelden die laten zien hoe u de dichtstbijzijnde overeenkomende waarde in Excel kunt vinden met behulp van opzoekformules.
Misschien vind je de volgende Excel-tips/zelfstudies misschien ook leuk
- Haal het laatste nummer uit een lijst met de functie VERT.ZOEKEN.
- Krijg meerdere opzoekwaarden zonder herhaling in een enkele cel.
- VERT.ZOEKEN Vs. INDEX/MATCH
- Excel INDEX MATCH
- Vind het laatste voorkomen van een opzoekwaarde een lijst in Excel
- Zoek en verwijder duplicaten in Excel
- Conditionele opmaak.
