Hoe het dichtstbijzijnde of meest nabije getal in Excel te vinden?

Stel dat je een lijst met getallen hebt in kolom B en je moet bepalen welke waarde het dichtst bij een bepaald getal ligt — bijvoorbeeld 18. Met behulp van een matrixformule in Excel kun je dit efficiënt identificeren zonder de lijst handmatig door te nemen.

Om te beginnen selecteer je een lege cel en voer je de volgende formule in. Nadat je de formule hebt getypt, zorg er dan voor dat je op Ctrl + Shift + Enter drukt in plaats van alleen op Enter. Dit zorgt ervoor dat de formule wordt uitgevoerd als een matrixformule, wat noodzakelijk is voor correcte werking:

=INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0))

Deze aanpak is het meest geschikt wanneer je het enkele dichtstbijzijnde getal uit een continu bereik wilt ophalen. Het werkt goed in de meeste gevallen waar numerieke nauwkeurigheid en exacte overeenkomsten cruciaal zijn. Houd er echter rekening mee dat matrixformules resource-intensief kunnen zijn bij zeer grote datasets. Als je prestatieproblemen ondervindt of foutmeldingen zoals #WAARDE! krijgt, controleer dan je celverwijzingen en zorg ervoor dat je Ctrl + Shift + Enter correct indrukt.

Er zijn momenten waarop je niet alleen het enkele dichtstbijzijnde getal nodig hebt, maar in plaats daarvan alle getallen wilt selecteren die binnen een bepaald bereik van je doelwaarde vallen — vaak aangeduid als afwijkingbereik. Kutools voor Excel biedt een praktische oplossing via de functie Specifieke cellen selecteren, waarmee je snel alle waarden kunt selecteren binnen een gespecificeerde afwijking van je doel.

Stel bijvoorbeeld dat je doelwaarde 18 is en je hebt een afwijking van 2 vastgesteld. Dit betekent dat je alle waarden in je bereik wilt selecteren die tussen 16 (18–2) en 20 (18+2) liggen. Hier is hoe je dit stap voor stap kunt doen:

1. Selecteer het bereik dat je wilt doorzoeken (bijvoorbeeld B3:B22), ga vervolgens naar Kutools > Selecteer > Specifieke cellen selecteren.

2. In het dialoogvenster Specifieke cellen selecteren:

• Kies onder Selectietype Cel.
• In Specifiek type:
  - Stel de eerste keuzelijst in op Groter dan of gelijk aan en voer 16 in het vak in.
  - Stel de tweede drop-down in op Kleiner dan of gelijk aan en voer 20.

3in. Klik OK om uit te voeren. Kutools zal je melden hoeveel cellen aan je criteria voldeden en alle dichtstbijzijnde waarden binnen de gespecificeerde afwijking markeren, zoals hieronder weergegeven:

Deze oplossing is ideaal om snel alle nabijgelegen waarden in bulk te identificeren, vooral bij het verwerken van brede bereiken met variabele toleranties. Houd er rekening mee dat de nauwkeurigheid van je selectie afhangt van het duidelijk instellen van je afwijking — als je afwijking te smal of breed is, kun je relevante gegevens missen of ongewenste waarden opnemen.

Voor gebruikers die automatisering zoeken of die aangepaste zoekopdrachten naar de dichtstbijzijnde waarde moeten uitvoeren — voor numerieke of tekstgegevens — over verschillende werkbladen of grote datasets, kan een VBA-macro een efficiënte en flexibele oplossing zijn. Door Excel te programmeren om systematisch het verschil tussen je doel en alle kandidaten te controleren, kun je niet alleen het dichtstbijzijnde getal ophalen, maar ook de dichtstbijzijnde tekststring op basis van tekstafstand.

Deze aanpak is voordelig wanneer geïntegreerde automatisering vereist is, vooral bij bereiken die te groot zijn voor handmatige methoden of bij herhalende taken. Houd er echter rekening mee dat VBA-macros het inschakelen van macros vereisen en een basisbekendheid met de VBA-omgeving. Maak altijd een back-up van je gegevens voordat je een macro uitvoert om onbedoeld verlies te voorkomen.

1. Klik op Ontwikkelaar  > Visual Basic. Klik in het venster Microsoft Visual Basic for Applications op Invoegen > Module, en kopieer de volgende code naar de module:

Function FindClosest(rng As Range, target As Double) As Double
    Dim cell As Range
    Dim minDiff As Double
    Dim closestValue As Double
    
    minDiff = 1E+99
    For Each cell In rng
        If Abs(cell.Value - target) < minDiff Then
            minDiff = Abs(cell.Value - target)
            closestValue = cell.Value
        End If
    Next cell  
    FindClosest = closestValue
End Function 

2. Ga vervolgens naar je werkblad en voer deze formule in: =FindClosest(B3:B22, E2) in een lege cel. Druk op Enter om de dichtstbijzijnde waarde te krijgen.

Bij het bekijken of presenteren van gegevens is het vaak handig om visueel de waarden te identificeren die het dichtst bij een doel liggen zonder je gegevens te filteren of te herschikken. De ingebouwde functie Voorwaardelijke opmaak in Excel stelt je in staat om de cellen die het dichtst bij je doelwaarde liggen te markeren, zodat ze gemakkelijk opvallen. Hoewel deze methode niet de exacte waarde zelf retourneert, is het effectief voor snelle data-analyse en visuele nadruk.

Het belangrijkste voordeel van deze methode is niet-destructieve, dynamische markering die zich aanpast als gegevens of doelwaarden veranderen. Het is vooral geschikt voor dashboards, presentaties en review-scenario's waar zichtbaarheid cruciaal is. Het kan minder nauwkeurig zijn als meer dan één waarde dezelfde 'nabijheid' deelt, en het geeft de waarde zelf niet uit voor verdere verwerking.

1. Selecteer het bereik van cellen dat je wilt analyseren (bijvoorbeeld B3:B22).

2. Klik op het tabblad Start op Voorwaardelijke opmaak > Nieuwe regel.

3. Kies Gebruik een formule om te bepalen welke cellen te formatteren in het dialoogvenster. Voer vervolgens in het formulevak de volgende formule in:

=ABS(B3-$E$2)=MIN(ABS($B$3:$B$22-$E$2))

4. Klik op Opmaak en kies een markeerkleur, klik vervolgens op OK en nogmaals op OK om de regel toe te passen.

Dit zal alle cellen in je geselecteerde bereik markeren waarvan de waarden even dicht bij de doelwaarde in E2 liggen.

Als je werkt met grote bereiken of onverwachte resultaten ervaart, controleer dan of je verwijzingen correct zijn en dat absolute/relatieve verwijzingen zoals bedoeld zijn ingesteld (gebruik $ om de doelcel en de bereikverwijzingen te vergrendelen).