Wie finden Sie in Excel den nächstgelegenen bzw. am nächsten liegenden Wert?

Angenommen, Sie haben eine Liste von Zahlen in Spalte B und möchten herausfinden, welcher Wert dem vorgegebenen Wert – beispielsweise 18 – am nächsten liegt. Mit einer Matrixformel in Excel ermitteln Sie das Ergebnis effizient, ohne die Liste manuell durchsuchen zu müssen.

Wählen Sie zunächst eine leere Zelle aus und geben Sie die folgende Formel ein. Drücken Sie nach der Eingabe unbedingt Strg + Umschalt + Enter statt nur Enter. Dadurch wird sichergestellt, dass die Formel als Matrixformel ausgeführt wird – eine Voraussetzung für ihre korrekte Funktion:

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

Dieser Ansatz eignet sich ideal, wenn Sie aus einem zusammenhängenden Bereich die nächstgelegene Zahl abrufen möchten. Er funktioniert in den meisten Fällen hervorragend, in denen numerische Genauigkeit und exakte Übereinstimmungen entscheidend sind. Beachten Sie jedoch, dass Matrixformeln bei sehr großen Datensätzen ressourcenintensiv sein können. Sollten Sie Leistungsprobleme bemerken oder Fehlermeldungen wie #WERT! erhalten, überprüfen Sie Ihre Zellbezüge und stellen Sie sicher, dass Sie Strg + Umschalt + Enter korrekt drücken.

Es gibt Situationen, in denen Sie nicht nur den nächstgelegenen Wert benötigen, sondern alle Zahlen auswählen möchten, die innerhalb eines bestimmten Bereichs um Ihren Zielwert liegen – häufig als Abweichungsbereich bezeichnet. Kutools für Excel bietet hierfür eine praktische Lösung mit der Funktion Spezielle Zellen auswählen, mit der Sie im Handumdrehen alle Werte erfassen können, die sich innerhalb einer festgelegten Differenz zu Ihrem Zielwert befinden.

Angenommen, Ihr Zielwert beträgt 18 und Sie haben eine Abweichung von 2 festgelegt. Das bedeutet, Sie möchten alle Werte in Ihrem Bereich auswählen, die zwischen 16 (18 – 2) und 20 (18 + 2) liegen. So geht’s Schritt für Schritt:

1. Wählen Sie den Bereich aus, den Sie durchsuchen möchten (z. B. B3:B22), und navigieren Sie anschließend zu Kutools > Auswählen > Bestimmte Zellen auswählen.

2. Im Dialogfeld Bestimmte Zellen auswählen:

• Unter Auswahltyp wählen Sie Zelle.
• In Typ angeben:
  – Stellen Sie die erste Dropdown-Liste auf Größer als oder gleich ein und geben Sie 16 in das Feld ein.
  – Stellen Sie die zweite Dropdown-Liste auf Kleiner als oder gleich ein und geben Sie 20 ein.

3. Klicken Sie auf OK, um die Aktion auszuführen. Kutools zeigt Ihnen an, wie viele Zellen Ihren Kriterien entsprechen, und hebt alle Werte innerhalb der festgelegten Abweichung wie unten dargestellt hervor:

Diese Lösung eignet sich ideal, um alle nahegelegenen Werte schnell und gebündelt zu identifizieren – besonders bei breiten Bereichen mit variablen Toleranzen. Beachten Sie: Die Genauigkeit Ihrer Auswahl hängt entscheidend davon ab, wie präzise Sie Ihre Abweichung festlegen. Ist die Abweichung zu eng oder zu weit gefasst, riskieren Sie, relevante Daten zu übersehen oder unerwünschte Werte einzuschließen.

Für Anwender, die Automatisierung benötigen oder eine maßgeschneiderte Suche nach dem nächstgelegenen Wert – sowohl bei numerischen als auch bei Textdaten – über mehrere Arbeitsblätter oder umfangreiche Datensätze hinweg durchführen möchten, bietet ein VBA-Makro eine effiziente und flexible Lösung. Indem Sie Excel anweisen, systematisch die Differenz zwischen Ihrem Zielwert und allen Kandidaten zu prüfen, ermitteln Sie nicht nur die nächstgelegene Zahl, sondern auch die ähnlichste Zeichenkette basierend auf der Textdistanz.

Dieser Ansatz bietet klare Vorteile, wenn integrierte Automatisierung erforderlich ist – besonders bei Bereichen, die zu groß für manuelle Methoden sind, oder bei wiederkehrenden Aufgaben. Beachten Sie jedoch, dass VBA-Makros das Aktivieren von Makros sowie grundlegende Kenntnisse der VBA-Umgebung voraussetzen. Sichern Sie Ihre Daten stets vor dem Ausführen eines Makros, um unbeabsichtigten Datenverlust zu vermeiden.

1. Klicken Sie auf Entwicklertools > Visual Basic. Klicken Sie im Fenster Microsoft Visual Basic für Applikationen auf Einfügen > Modul, und kopieren Sie den folgenden Code in das Modul:

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. Wechseln Sie dann zu Ihrem Arbeitsblatt und geben Sie die folgende Formel in eine leere Zelle ein:=FindClosest(B3:B22;E2) Drücken Sie die Eingabetaste, um den nächstgelegenen Wert zu erhalten.

Beim Überprüfen oder Präsentieren von Daten ist es oft hilfreich, Werte, die einem Zielwert am nächsten liegen, visuell zu identifizieren – ohne die Daten zu filtern oder neu anzuordnen. Mit der integrierten Funktion Bedingte Formatierung verwenden in Excel können Sie genau diese Zellen hervorheben, sodass sie auf einen Blick erkennbar sind. Obwohl diese Methode nicht den exakten Wert selbst zurückgibt, eignet sie sich hervorragend für schnelle Datenanalysen und eine wirkungsvolle visuelle Hervorhebung.

Der entscheidende Vorteil dieser Methode liegt in ihrer zerstörungsfreien, dynamischen Hervorhebung, die sich automatisch anpasst, sobald sich Daten oder Zielwerte ändern. Sie eignet sich ideal für Dashboards, Präsentationen und Überprüfungsszenarien, bei denen maximale Sichtbarkeit entscheidend ist. Allerdings kann sie weniger präzise sein, wenn mehrere Werte dieselbe „Nähe“ aufweisen, und liefert den Wert selbst nicht zur weiteren Verarbeitung aus.

1. Wählen Sie den Zellbereich aus, den Sie analysieren möchten (z. B. B3:B22).

2. Klicken Sie auf der Registerkarte Start auf Bedingte Formatierung verwenden > Neue Regel.

3. Wählen Sie im Dialogfeld Formel zur Bestimmung der zu formatierenden Zellen verwenden. Geben Sie dann im Formelfeld die folgende Formel ein:

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

4. Klicken Sie auf Format und wählen Sie eine Hervorhebungsfarbe aus. Klicken Sie anschließend auf OK und erneut auf OK, um die Regel anzuwenden.

Dadurch werden alle Zellen in Ihrem Bereich hervorgehoben, deren Werte dem Zielwert in E2 am nächsten liegen.

Wenn Sie mit großen Bereichen arbeiten oder unerwartete Ergebnisse erhalten, prüfen Sie, ob Ihre Bezüge korrekt und absolute bzw. relative Bezüge wie gewünscht eingestellt sind (verwenden Sie $, um die Zielzelle und die Bereichsbezüge zu fixieren).