Dichtstbijzijnde overeenkomst opzoeken
Om de dichtstbijzijnde overeenkomst van een zoekwaarde in een numerieke dataset in Excel te vinden, combineert u de functies INDEX, VERGELIJKEN, ABS en MIN.
Hoe vindt u de dichtstbijzijnde overeenkomst in Excel?
Om te bepalen welke verkoper de verkoop het dichtst bij het doel van $20.000 heeft gerealiseerd, zoals hierboven weergegeven, helpt een formule die de functies INDEX, VERGELIJKEN, ABS en MIN combineert als volgt: de ABS-functie maakt van alle verschilwaarden tussen de verkoop van elke verkoper en het verkoopdoel positieve getallen; vervolgens vindt MIN het kleinste verschil, wat de dichtstbijzijnde overeenkomst aangeeft. Nu kunnen we de VERGELIJKEN-functie gebruiken om de positie van die dichtstbijzijnde overeenkomst te bepalen en INDEX om de waarde op de overeenkomstige positie op te halen.
Algemene syntaxis
=INDEX()return_range,MATCH(MIN(ABS()))lookup_array-lookup_value)),ABS(lookup_array-lookup_value),0))
√ Opmerking: Dit is een matrixformule waarvoor u moet invoeren met Ctrl+Shift+Enter.
- return_range: Het bereik waaruit u wilt dat de combinatieformule de verkoper retourneert. In dit geval verwijst het naar het naam-bereik.
- lookup_array: Het celbereik met de waarden die vergeleken moeten worden met de zoekwaarde. In dit geval verwijst het naar het verkoopbereik.
- Zoekwaarde: De waarde waarmee wordt vergeleken om de dichtstbijzijnde overeenkomst te vinden. In dit geval verwijst dat naar het verkoopdoel.
Om te weten welke verkoper de verkoop het dichtst bij het doel van $20,000 heeft gerealiseerd, kopieert of voert u de onderstaande formule in cel F5 in en drukt u opCtrl +Shift +Enter om het resultaat te krijgen: to get the result:
=INDEX()B5:B10,VERGELIJKEN(MIN(ABS()))C5:C10-20000)),ABS(C5:C10-20000),0))
Of gebruik Een celverwijzing om de formule dynamisch te maken:
=INDEX()B5:B10,VERGELIJKEN(MIN(ABS()))C5:C10-F4)),ABS(C5:C10-F4),0))
Uitleg van de formule
=INDEX()B5:B10,MATCH()MIN()ABS(C5:C10-F4)),ABS(C5:C10-F4),0))
- ABS(C5:C10-F4): Het deel C5:C10-F4 levert alle verschilwaarden op tussen elke verkoop in het bereik C5:C10 en de doelverkoop $20.000 in cel F4, in een matrix zoals deze: {-4322;2451;6931;-1113;6591;-4782}. De ABS-functie maakt van alle negatieve getallen positieve getallen, zoals hier: {4322;2451;6931;1113;6591;4782}.
- MIN()ABS(C5:C10-F4))=MIN(){4322;2451;6931;1113;6591;4782}): De MIN-functie zoekt het kleinste getal in de matrix {4322;2451;6931;1113;6591;4782}, wat het kleinste verschil aangeeft – oftewel de dichtstbijzijnde overeenkomst. De functie retourneert dus 1113.
- VERGELIJKEN()MIN()ABS(C5:C10-F4)),ABS(C5:C10-F4) De overeenkomsttype 0 dwingt de VERGELIJKEN-functie om de positie te vinden van het exacte getal 1113 in de matrix {4322;2451;6931;1113;6591;4782}. De functie retourneert 4, omdat het getal op de 4de positie staat.
- INDEX()B5:B10,VERGELIJKEN()MIN()ABS(C5:C10-F4)),ABS(C5:C10-F4)De INDEX-functie retourneert de 4de waarde in het bereik B5:B10, namelijk Bale.
Gerelateerde functies
De Excel INDEX-functie retourneert de Weergegeven waarde op basis van een gegeven positie uit een bereik of een matrix.
De Excel VERGELIJKEN-functie zoekt naar een specifieke waarde in een bereik cellen en retourneert de relatieve positie van die waarde.
De ABS-functie retourneert de Absolute waarde van een getal. Negatieve getallen worden met deze functie omgezet naar positieve getallen, maar positieve getallen en nul blijven ongewijzigd.
Gerelateerde formules
Dichtstbijzijnde overeenkomstwaarde opzoeken met meerdere criteria
In sommige gevallen moet u de dichtstbijzijnde of benaderende overeenkomstwaarde opzoeken op basis van meer dan één criterium. Met de combinatie van de functies INDEX, VERGELIJKEN en ALS kunt u dit snel realiseren in Excel.
Benaderende overeenkomst met INDEX en VERGELIJKEN
Er zijn momenten waarop we benaderende overeenkomsten moeten vinden in Excel om de prestaties van medewerkers te beoordelen, cijfers van studenten te bepalen, portokosten te berekenen op basis van gewicht, enzovoort. In deze handleiding bespreken we hoe u de functies INDEX en VERGELIJKEN kunt gebruiken om de gewenste resultaten op te halen.
Dichtstbijzijnde overeenkomstwaarde opzoeken met meerdere criteria
In sommige gevallen moet u de dichtstbijzijnde of benaderende overeenkomstwaarde opzoeken op basis van meer dan één criterium. Met de combinatie van de functies INDEX, VERGELIJKEN en ALS kunt u dit snel realiseren in Excel.
De beste Office-productiviteitshulpmiddelen
Kutools voor Excel – Helpt u om op te vallen tussen de massa
Kutools voor Excel Beschikt over meer dan 300 functies,zodat u alles wat u nodig heeft binnen één klik bereik heeft...
Office Tab – Schakel tabbladenlezen en -bewerken in Microsoft Office (inclusief Excel) in
- Schakel in één seconde tussen tientallen geopende documenten!
- Bespaar honderden muisklikken per dag en zeg vaarwel tegen muisarm.
- Verhoog uw productiviteit met 50 % bij het bekijken en bewerken van meerdere documenten.
- Brengt efficiënte tabs naar Office (inclusief Excel), net als in Chrome, Edge en Firefox.