Hoe vindt u de dichtstbijzijnde waarde in Excel?

Stel dat u een lijst met getallen hebt in kolom B en wilt bepalen welke waarde het dichtst bij een bepaald getal ligt—bijvoorbeeld 18. Met een matrixformule in Excel identificeert u dit op efficiënte wijze, zonder de lijst handmatig te hoeven doorzoeken.

Selecteer om te beginnen een lege cel en voer de volgende formule in. Nadat u de formule hebt getypt, drukt u op Ctrl + Shift + Enter in plaats van alleen op Enter. Zo zorgt u ervoor dat de formule als een matrixformule wordt uitgevoerd, wat essentieel 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 u het enkele dichtstbijzijnde getal uit een aaneengesloten bereik wilt ophalen. Ze werkt uitstekend in de meeste situaties waar numerieke nauwkeurigheid en exacte overeenkomsten cruciaal zijn. Houd er echter rekening mee dat matrixformules aanzienlijk systeembronnen kunnen verbruiken bij zeer grote datasets. Als u prestatieproblemen ondervindt of foutmeldingen zoals #WAARDE! krijgt, controleer dan uw celverwijzingen en zorg ervoor dat u correct op Ctrl + Shift + Enter drukt.

Soms hebt u niet alleen de dichtstbijzijnde waarde nodig, maar wilt u alle getallen selecteren die binnen een bepaald bereik van uw doelwaarde vallen—vaak een tolerantiebereik genoemd. Kutools voor Excel biedt hiervoor een handige oplossing via de functie Speciale cellen selecteren, waarmee u in één keer alle waarden kunt markeren die binnen het opgegeven verschil van uw doelwaarde liggen.

Stel bijvoorbeeld dat uw doelwaarde 18 is en u een tolerantie van 2 heeft ingesteld. Dan wilt u alle waarden in uw bereik selecteren die tussen 16 (18 – 2) en 20 (18 + 2) liggen. Zo doet u dat stap voor stap:

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

2. In het dialoogvenster Specifieke cellen selecteren:

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

3. Klik op OK om uit te voeren. Kutools meldt hoeveel cellen aan uw criteria voldoen en markeert alle dichtstbijzijnde waarden binnen de opgegeven tolerantie, zoals hieronder wordt weergegeven:

Deze oplossing is ideaal om in één keer alle nabije waarden snel te identificeren, vooral bij uitgebreide bereiken met variabele toleranties. Let op: de nauwkeurigheid van uw selectie hangt af van hoe scherp u uw tolerantie instelt—bij een tolerantie die te smal of juist te breed is, loopt u het risico relevante gegevens over het hoofd te zien of ongewenste waarden mee te nemen.

Voor gebruikers die automatisering zoeken of op maat gemaakte zoekopdrachten willen uitvoeren naar de dichtstbijzijnde waarde—zowel voor numerieke als tekstgegevens—over meerdere werkbladen of grote datasets, biedt een VBA-macro een efficiënte en flexibele oplossing. Door Excel te programmeren om systematisch het verschil tussen uw doelwaarde en alle kandidaten te beoordelen, haalt u niet alleen het dichtstbijzijnde getal op, maar ook de meest vergelijkbare tekstreeks op basis van tekstafstand.

Deze aanpak is ideaal wanneer geïntegreerde automatisering nodig is, vooral bij bereiken die te groot zijn voor handmatige methoden of bij herhalende taken. Houd er echter rekening mee dat VBA-macro’s het inschakelen van macro’s vereisen en basiskennis van de VBA-omgeving. Maak altijd een back-up van uw gegevens voordat u een macro uitvoert om onbedoeld gegevensverlies 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 uw werkblad en voer de volgende formule in een lege cel in:=FindClosest(B3:B22; E2). Druk op Enter om de dichtstbijzijnde waarde te verkrijgen.

Bij het controleren of presenteren van gegevens is het vaak handig om waarden die het dichtst bij een doelwaarde liggen visueel te markeren, zonder de gegevens te filteren of te herschikken. Met de ingebouwde functie Voorwaardelijke opmaak gebruiken in Excel kunt u direct de cellen markeren die het dichtst bij uw doelwaarde liggen, zodat ze in één oogopslag opvallen. Hoewel deze methode de exacte waarde zelf niet retourneert, is ze zeer effectief voor snelle data-analyse en visuele nadruk.

Het belangrijkste voordeel van deze methode is niet-destructieve, dynamische markering die zich aanpast wanneer gegevens of doelwaarden veranderen. Deze methode is vooral geschikt voor dashboards, presentaties en controle-scenario’s waarbij zichtbaarheid essentieel is. De methode kan minder nauwkeurig zijn als meerdere waarden even dicht bij de doelwaarde liggen, en levert de waarde zelf niet op voor verdere verwerking.

1. Selecteer het celbereik dat u wilt analyseren (bijvoorbeeld B3:B22).

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

3. Kies in het dialoogvenster Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt en 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 daarna op OK en nogmaals op OK om de regel toe te passen.

Hiermee worden alle cellen in uw bereik gemarkeerd waarvan de waarden even dicht bij de doelwaarde in E2 liggen.

Als u met grote bereiken werkt of onverwachte resultaten krijgt, controleert u dan of uw verwijzingen correct zijn en of absolute of relatieve verwijzingen juist zijn ingesteld (gebruik $ om de doelcel en bereikverwijzingen vast te zetten).