Hoe vind je de dichtstbijzijnde of meest nabije waarde in Excel?

Stel, je hebt een lijst met getallen in Kolom B en je wilt bepalen welke waarde het dichtst in de buurt komt van een bepaald getal, bijvoorbeeld18. Met een matrixformule in Excel kun je dit efficiënt achterhalen zonder de lijst handmatig door te zoeken.

Om te beginnen selecteer je een lege cel en voer je de volgende formule in. Nadat je de formule hebt getypt, druk je op Ctrl + Shift + Enter in plaats van alleen Enter. Hierdoor wordt de formule als matrixformule uitgevoerd, wat noodzakelijk is voor de correcte werking:

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

Deze methode is het meest geschikt wanneer je slechts één dichtstbijzijnde getal uit een aaneengesloten bereik wilt ophalen. Dit werkt goed in de meeste situaties waarin numerieke nauwkeurigheid en exacte overeenkomsten van belang zijn. Houd er echter rekening mee dat matrixformules veel rekenkracht kunnen vergen bij zeer grote datasets. Krijg je te maken met prestatieproblemen of foutmeldingen zoals #WAARDE!, controleer dan je celverwijzingen en zorg ervoor dat je Ctrl + Shift + Enter juist hebt ingedrukt.

Soms heb je niet alleen de enkele dichtstbijzijnde waarde nodig, maar wil je alle getallen selecteren die binnen een bepaald bereik van je doelwaarde vallen—vaak een afwijkingsbereik genoemd. Kutools voor Excel biedt hiervoor een praktische oplossing via de functie Specifieke cellen selecteren, waarmee je snel alle waarden binnen een opgegeven verschil van je doel kunt selecteren.

Stel, je doelwaarde is18 en je hebt een afwijkingswaarde van2 bepaald. Dat betekent dat je alle waarden in je bereik wilt selecteren die tussen16 (18–2) en20 (18+2) liggen. Volg hiervoor deze stappen:

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

2. In het dialoogvenster Specifieke cellen selecteren:

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

3. Klik OK om uit te voeren. Kutools laat weten hoeveel cellen voldoen aan je criteria en markeert alle dichtstbijzijnde waarden binnen het opgegeven afwijkingsbereik, zoals hieronder weergegeven:

Deze oplossing is ideaal om snel in bulk alle nabije waarden te identificeren, vooral bij grote bereiken met verschillende toleranties. Let erop dat de nauwkeurigheid van je selectie afhankelijk is van het correct instellen van je afwijking—als die te smal of te breed is, kun je relevante gegevens missen of ongewenste waarden opnemen.

Voor wie automatisering zoekt of aangepaste zoekopdrachten naar de dichtstbijzijnde waarde moet uitvoeren—zowel voor numerieke als tekstgegevens—over meerdere bladen of grote datasets, is een VBA-macro een efficiënte en flexibele oplossing. Door Excel te programmeren om systematisch het verschil tussen je doelwaarde en alle kandidaten na te gaan, kun je niet alleen het dichtstbijzijnde getal, maar ook de dichtstbijzijnde tekst op basis van tekstafstand ophalen.

Deze aanpak is vooral handig wanneer geautomatiseerde integratie vereist is, met name bij bereiken die te groot zijn voor handmatige methoden of bij terugkerende taken. Let er wel op dat VBA-macro's het inschakelen van macro's vereisen en enige basiskennis van de VBA-omgeving. Maak altijd eerst een back-up van je gegevens alvorens een macro uit te voeren, om onbedoeld verlies te voorkomen.

1. Klik op Ontwikkelaars  > Visual Basic. In het Microsoft Visual Basic for Applications-venster klik je op Invoegen > Module, en kopieer je 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 dan naar je werkblad en voer deze formule in: =FindClosest(B3:B22, E2) in een lege cel. Druk op Enter om de dichtstbijzijnde waarde te verkrijgen.

Bij het controleren of presenteren van gegevens is het vaak handig om de waarden die het dichtst bij een doel liggen visueel te markeren zonder te filteren of de gegevens te herschikken. Dankzij de ingebouwde functie Voorwaardelijke opmaak gebruiken in Excel kun je de cellen markeren die het dichtst bij je doelwaarde liggen, zodat je ze snel herkent. Hoewel deze methode niet direct de exacte waarde geeft, is het effectief voor snelle data-analyse en visuele accenten.

Het belangrijkste voordeel van deze methode is dat het dynamische, niet-destructieve markeringen biedt, die zich automatisch aanpassen wanneer de gegevens of doelwaarden wijzigen. Ze zijn bijzonder geschikt voor dashboards, presentaties en beoordeelomgevingen waar overzicht belangrijk is. Houd er rekening mee dat deze methode minder nauwkeurig kan zijn als meerdere waarden even dichtbij liggen, en dat de exacte waarde niet als resultaat beschikbaar is 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 gebruiken > Nieuwe regel.

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

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

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

Nu worden alle cellen in het geselecteerde bereik gemarkeerd waarvan de waarden het dichtst bij de doelwaarde in E2 liggen.

Werk je met grote bereiken of krijg je onverwachte resultaten, controleer dan of je verwijzingen correct zijn en of de absolute/relatieve verwijzingen goed zijn ingesteld (gebruik $ om de doelcel en reeksen vast te zetten).