Wie findet man den nächstgelegenen oder nächsten Wert in Excel?

Angenommen, Sie haben eine Liste von Zahlen in Spalte B und müssen den Wert ermitteln, der einem bestimmten Wert am nächsten kommt – zum Beispiel 18. Mit einer Array-Formel in Excel können Sie dies effizient identifizieren, ohne die Liste manuell durchsuchen zu müssen.

Um zu beginnen, wählen Sie eine leere Zelle aus und geben Sie die folgende Formel ein. Sobald Sie die Formel eingegeben haben, drücken Sie unbedingt Strg + Umschalt + Eingabetaste anstatt nur Eingabe. Dadurch wird sichergestellt, dass die Formel als Array-Formel ausgeführt wird, was für ihre korrekte Funktion notwendig ist:

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

Dieser Ansatz ist am besten geeignet, wenn Sie den einzigen nächstgelegenen Wert aus einem zusammenhängenden Bereich abrufen möchten. Er funktioniert gut in den meisten Fällen, in denen numerische Genauigkeit und exakte Übereinstimmungen entscheidend sind. Beachten Sie jedoch, dass Array-Formeln bei sehr großen Datensätzen ressourcenintensiv sein können. Wenn Sie Leistungsprobleme oder Fehlermeldungen wie #WERT! erhalten, überprüfen Sie Ihre Zellbezüge und stellen Sie sicher, dass Sie Strg + Umschalt + Eingabetaste korrekt gedrückt haben.

Es gibt Zeiten, in denen Sie möglicherweise nicht nur den einzigen nächstgelegenen Wert benötigen, sondern stattdessen alle Zahlen auswählen möchten, die innerhalb eines bestimmten Bereichs um Ihren Zielwert liegen – oft als Abweichungsbereich bezeichnet. Kutools für Excel bietet eine praktische Lösung durch seine Funktion "Bestimmte Zellen auswählen", mit der Sie schnell alle Werte innerhalb einer angegebenen Differenz zu Ihrem Zielwert auswählen können.

Wenn Ihr Zielwert beispielsweise 18 beträgt und Sie eine Abweichung von 2 festgelegt haben, bedeutet dies, dass Sie alle Werte in Ihrem Bereich auswählen möchten, die zwischen 16 (18–2) und 20 (18+2) liegen. Hier erfahren Sie, wie Sie dies Schritt für Schritt erreichen können:

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

2. Im Dialogfeld "Bestimmte Zellen auswählen":

• Wählen Sie unter Auswahltyp Zelle aus.
• In Spezifischer Typ:
  - Setzen Sie die erste Dropdown-Liste auf Größer als oder gleich und geben Sie 16 in das Feld ein.
  - Setzen Sie die zweite Dropdown-Liste auf Kleiner als oder gleich und geben Sie 20.

3ein. Klicken Sie auf OK um auszuführen. Kutools teilt Ihnen mit, wie viele Zellen Ihren Kriterien entsprechen, und hebt alle nächstgelegenen Werte innerhalb der angegebenen Abweichung hervor, wie unten gezeigt:

Diese Lösung ist ideal, um schnell alle nahegelegenen Werte in großer Zahl zu identifizieren, insbesondere beim Umgang mit breiten Bereichen und variablen Toleranzen. Beachten Sie, dass die Genauigkeit Ihrer Auswahl davon abhängt, dass Sie Ihre Abweichung klar festlegen – falls Ihre Abweichung zu eng oder zu breit ist, könnten Sie relevante Daten übersehen oder unerwünschte Werte einschließen.

Für Benutzer, die Automatisierung suchen oder benutzerdefinierte Suchen nach dem nächstgelegenen Wert – sowohl für numerische als auch Textdaten – über mehrere Blätter oder große Datensätze hinweg durchführen müssen, kann ein VBA-Makro eine effiziente und flexible Lösung sein. Indem Sie Excel programmieren, systematisch die Differenz zwischen Ihrem Ziel und allen Kandidaten zu überprüfen, können Sie nicht nur die nächstgelegene Zahl, sondern auch die nächstgelegene Zeichenfolge durch Textdistanz abrufen.

Dieser Ansatz ist vorteilhaft, wenn integrierte Automatisierung erforderlich ist, insbesondere bei Bereichen, die zu groß für manuelle Methoden sind, oder bei wiederkehrenden Aufgaben. Beachten Sie jedoch, dass VBA-Makros das Aktivieren von Makros und grundlegende Kenntnisse der VBA-Umgebung erfordern. Sichern Sie Ihre Daten immer, bevor Sie ein Makro ausführen, um unbeabsichtigten Datenverlust zu verhindern.

1. Klicken Sie auf Entwickler  > Visual Basic. Im Fenster Microsoft Visual Basic for Applications klicken Sie 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. Gehen Sie dann zu Ihrem Arbeitsblatt und geben Sie diese Formel ein: =FindClosest(B3:B22, E2) in eine leere Zelle. Drücken Sie die Eingabetaste, um den nächstgelegenen Wert zu erhalten.

Beim Überprüfen oder Präsentieren von Daten ist es oft hilfreich, visuell die Werte zu identifizieren, die einem Zielwert am nächsten liegen, ohne die Daten zu filtern oder neu anzuordnen. Excels eingebaute Funktion zur bedingten Formatierung ermöglicht es Ihnen, die Zellen hervorzuheben, die Ihrem Zielwert am nächsten liegen, sodass sie auf einen Blick leicht zu erkennen sind. Obwohl diese Methode den genauen Wert selbst nicht zurückgibt, ist sie dennoch effektiv für schnelle Datenanalysen und visuelle Betonung.

Der Hauptvorteil dieser Methode ist die nicht-destruktive, dynamische Hervorhebung, die sich anpassen kann, wenn sich Daten oder Zielwerte ändern. Sie ist besonders gut für Dashboards, Präsentationen und Überprüfungszenarien geeignet, in denen Sichtbarkeit entscheidend ist. Sie kann weniger präzise sein, wenn mehrere Werte die gleiche „Nähe“ aufweisen, und gibt 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 > Neue Regel.

3. Wählen Sie im Dialogfeld "Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen ". Geben Sie dann in das Formelfeld die folgende Formel ein:

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

4. Klicken Sie auf Formatieren und wählen Sie eine Hervorhebungsfarbe aus, klicken Sie dann auf OK und nochmal auf OK, um die Regel anzuwenden.

Dies wird alle Zellen in Ihrem ausgewählten Bereich hervorheben, deren Werte dem Zielwert in E2 am nächsten liegen.

Wenn Sie mit großen Bereichen arbeiten oder unerwartete Ergebnisse erhalten, überprüfen Sie, ob Ihre Bezüge korrekt sind und absolute/relative Bezüge wie beabsichtigt gesetzt sind (verwenden Sie $, um die Zielzelle und die Bereichsbezüge zu sperren).