Excel-functies kunnen erg krachtig zijn als je het combineren van verschillende formules onder de knie hebt.Wat ooit onmogelijk leek, kan plotseling kinderspel worden.
Een voorbeeld hiervan is het vinden van de dichtstbijzijnde waarde in een Excel-gegevensset.
Er zijn verschillende handige opzoekfuncties in Excel (zoals VERT.ZOEKEN en INDEX MATCH) die de dichtstbijzijnde值(zoals weergegeven in het onderstaande voorbeeld).
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 met een opzoekwaarde in een ongesorteerde lijst).
In deze zelfstudie laat ik u zien hoe u een opzoekformule gebruikt om de beste overeenkomst met een opzoekwaarde in Excel te vinden.
Er zijn veel verschillende scenario's waarin u de dichtstbijzijnde overeenkomst (of de dichtstbijzijnde waarde) moet vinden.
Hier is een voorbeeld dat ik in dit artikel zal behandelen:
- Vind commissietarieven op basis van verkopen
- Vind de beste kandidaat (op basis van ervaring die het dichtst in de buurt komt)
- Vind de volgende evenementdatum
laten we beginnen!
Inhoud
Vind commissietarieven (zoek naar de dichtstbijzijnde verkoopwaarde)
Stel dat u een dataset heeft zoals hieronder waar u de commissietarieven voor alle verkopers wilt vinden.
Commissies worden verdeeld op basis van de waarde van de verkoop.Dit wordt berekend aan de hand van de tabel hiernaast.
Als de totale omzet van de verkoper bijvoorbeeld 5000 is, is de commissie 0% en als zijn/haar totale omzet 15000 is, is de commissie 5%.
Om een commissietarief te krijgen, moet u het dichtstbijzijnde verkoopbereik vinden dat net onder de verkoopwaarde ligt.Voor 15000 verkopen is de commissie bijvoorbeeld 10,000 (of 5%) en voor 25000 verkopen is het commissiepercentage 20,000 (of 7%).
Om de dichtstbijzijnde verkoopwaarde te vinden en een commissietarief te krijgen, kunt u benaderende overeenkomsten gebruiken in VERT.ZOEKEN.
De volgende formule kan dit doen:
= VERT.ZOEKEN (B2, $ E $ 2: $ F $ 6,2,1)
Merk op dat in deze formule de laatste parameter 1 is, wat de formule vertelt om benaderend opzoeken te gebruiken.Dit betekent dat de formule de verkoopwaarde in kolom E doorloopt en de waarde net onder de opzoekwaarde vindt.
De formule VERT.ZOEKEN geeft dan het commissietarief voor die waarde.
注意: Om dit te doen, moet u de gegevens in oplopende volgorde sorteren.
Vind de beste kandidaat (op basis van ervaring die het dichtst in de buurt komt)
In het bovenstaande voorbeeld moeten de gegevens in oplopende volgorde worden gesorteerd.Maar er kunnen gevallen zijn waarin de gegevens niet worden gesorteerd.
Laten we dus een voorbeeld nemen en kijken hoe we formulecombinaties kunnen gebruiken om de beste overeenkomst in Excel te vinden.
Hieronder staat een voorbeelddataset waarin ik de namen moet vinden van werknemers wiens werkervaring het dichtst bij de gewenste waarde ligt.De verwachte waarde is in dit geval 2.5 jaar.
Houd er rekening mee dat de gegevens niet zijn gesorteerd.Bovendien kunnen de dichtstbijzijnde ervaringen minder of meer zijn dan een bepaalde ervaring.2 jaar en 3 jaar liggen bijvoorbeeld even dicht bij elkaar (0.5 jaar verschil).
Hier is de formule die ons het resultaat geeft:
=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 opzoekwaarden te wijzigen om het kleinste empirische verschil tussen de gewenste en werkelijke waarden te vinden.
Laten we eerst begrijpen hoe je het handmatig zou doen (en dan zal ik uitleggen hoe deze formule werkt).
Wanneer je dit handmatig doet, ga je door elke cel in kolom B en vind je het verschil tussen de ervaring die je wilt en de ervaring die je hebt.Als je alle verschillen hebt, vind je de kleinste en krijg je de naam van die persoon.
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)).
Deze sectie geeft je het minimale verschil tussen de gegeven ervaring (dwz 2.5 jaar) en alle andere ervaringen.In dit geval retourneert het 0.3
Merk op dat ik ABS gebruik om ervoor te zorgen dat ik op zoek ben naar de dichtstbijzijnde (waarschijnlijk meer of minder 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 (ervaring vereist) wordt afgetrokken.
所以現在我們有一個查找值 (0.3) 和一個查找數組 ({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})
De MATCH-functie vindt de positie van 0.3 in deze array, wat de positie is van de naam van de persoon met de dichtstbijzijnde ervaring.
De INDEX-functie gebruikt vervolgens het taaknummer om de naam van de persoon terug te geven.
Let op: Indien er meerdere kandidaten zijn met dezelfde minimale ervaring, geeft bovenstaande formule de naam van de eerste matchende medewerker.
Vind de volgende evenementdatum
Hier is nog een voorbeeld waarbij u een opzoekformule kunt gebruiken om de volgende datum van een gebeurtenis te vinden op basis van de huidige datum.
Hieronder staat de dataset waarin ik evenementnamen en evenementdatums heb.
Wat ik wil is de naam van het volgende evenement en de evenementdatum van dit aanstaande evenement.
Hier is de formule die de naam van het aanstaande evenement geeft:
=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)
De volgende formule geeft de datums van de komende evenementen:
=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 in kolom B naar de huidige datum.In dit geval zoeken we niet naar een exacte match, maar naar een benaderende match.Daarom is de laatste parameter van de MATCH-functie 1 (deze vindt de grootste waarde kleiner dan of gelijk aan de opzoekwaarde).
Dus de MATCH-functie retourneert de positie van de cel waarvan de datum kleiner is dan of gelijk is aan de huidige datum.Dus in dit geval zal de volgende gebeurtenis in de volgende cel staan (omdat de lijst in oplopende volgorde is gesorteerd).
Dus, om de aanstaande gebeurtenisdatum 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, gebruikt u dezelfde formule en wijzigt u het bereik in de INDEX-functie van kolom B in kolom A.
Dit voorbeeld kwam in me op toen een vriend een verzoek deed.Hij somt de verjaardagen van al zijn vrienden/familieleden op in een kolom en wil weten of de volgende verjaardag eraan komt (en de naam van de persoon).
De bovenstaande drie voorbeelden laten zien hoe u de opzoekformule gebruikt om de dichtstbijzijnde waarde in Excel te vinden.
