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

Angenommen, Sie haben eine Liste von Zahlen in Spalte B und möchten ermitteln, welcher Wert einem bestimmten Wert am nächsten ist – zum Beispiel18. Mit einer Matrixformel in Excel können Sie dies effizient herausfinden, ohne die Liste manuell durchsuchen zu müssen.

Wählen Sie dazu zunächst eine leere Zelle aus und geben Sie die folgende Formel ein. Nachdem Sie die Formel eingegeben haben, drücken Sie bitte statt nur der Eingabetaste die Tastenkombination Ctrl + Shift + Enter. Nur so wird die Formel als Matrixformel ausgeführt, was für die korrekte Funktionsweise erforderlich ist:

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

Diese Vorgehensweise eignet sich am besten, wenn Sie aus einem fortlaufenden Bereich genau den nächstgelegenen Wert ermitteln müssen. Sie ist besonders hilfreich, wenn numerische Genauigkeit und exakte Übereinstimmung entscheidend sind. Beachten Sie jedoch, dass Matrixformeln bei sehr großen Datenmengen ressourcenintensiv sein können. Wenn Leistungsprobleme oder Fehlermeldungen wie #WERT! auftreten, überprüfen Sie Ihre Zellbezüge sorgfältig und stellen Sie sicher, dass Sie die Tastenkombination Ctrl + Shift + Enter korrekt verwenden.

Manchmal benötigen Sie nicht nur den einen nächstgelegenen Wert, sondern möchten alle Zahlen auswählen, die innerhalb eines bestimmten Bereichs um Ihren Zielwert liegen – oftmals als Abweichungsbereich bezeichnet. Kutools für Excel bietet hierfür mit der Funktion Bestimmte Zellen auswählen eine praktische Lösung, mit der Sie schnell alle Werte auswählen können, die eine festgelegte Differenz zu Ihrem Ziel aufweisen.

Angenommen, Ihr Zielwert ist18 und Ihr Abweichungswert beträgt2. Das bedeutet, Sie möchten alle Werte in Ihrem Bereich auswählen, die zwischen16 (18–2) und20 (18+2) liegen. So gehen Sie Schritt für Schritt vor:

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

2. Im Dialogfeld Bestimmte Zellen auswählen:

• Wählen Sie unter Auswahltyp die Option Zelle.
• In Typ angeben:
  - Wählen Sie in der ersten Dropdown-Liste Größer als oder gleich und geben Sie 16 in das Feld ein.
  - Legen Sie die zweite Dropdown-Liste auf Kleiner als oder gleich und geben Sie 20.

3ein. Klicken Sie auf OK um auszuführen. Kutools informiert Sie, wie viele Zellen die Kriterien erfüllen und hebt alle nächstgelegenen Werte innerhalb der angegebenen Abweichung wie unten gezeigt hervor:

Diese Lösung ist ideal, um alle nahegelegenen Werte schnell und in großer Menge zu identifizieren – insbesondere bei umfangreichen Bereichen mit variablen Toleranzen. Beachten Sie, dass die Genauigkeit der Auswahl davon abhängt, wie klar Sie Ihre Abweichung festlegen. Ist die Abweichung zu eng oder zu weit, könnten relevante Daten übersehen oder unerwünschte Werte eingeschlossen werden.

Für Anwender, die eine Automatisierung wünschen oder eine individuell angepasste Suche nach dem nächstgelegenen Wert – ob für Zahlen oder Textdaten – über mehrere Blätter oder große Datenmengen hinweg durchführen möchten, ist ein VBA-Makro eine effiziente und flexible Lösung. Mit einer programmatischen Überprüfung aller Kandidaten können Sie nicht nur die nächste Zahl, sondern auch die nächstgelegene Zeichenfolge anhand der Textentfernung ermitteln.

Dieser Ansatz eignet sich besonders, wenn eine automatisierte Lösung benötigt wird – etwa bei Bereichen, die zu groß für manuelle Verfahren sind, oder bei häufig wiederkehrenden Aufgaben. Beachten Sie jedoch, dass VBA-Makros das Aktivieren von Makros sowie Grundkenntnisse in der VBA-Umgebung erfordern. Sichern Sie immer Ihre Daten, bevor Sie ein Makro ausführen, um ungewollten Datenverlust zu vermeiden.

1. Klicken Sie auf Entwickler  > Visual Basic. Im Fenster Microsoft Visual Basic for Applications klicken Sie auf Einfügen > Modul und kopieren 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 Prüfen oder Präsentieren von Daten ist es oft hilfreich, Werte, die einem Ziel nahekommen, visuell hervorzuheben, ohne die Daten filtern oder umsortieren zu müssen. Die integrierte Funktion Bedingte Formatierung in Excel ermöglicht es, die Zellen hervorzuheben, die dem gewünschten Zielwert am nächsten liegen, sodass sie auf einen Blick erkennbar sind. Auch wenn mit dieser Methode der genaue Wert nicht direkt ausgegeben wird, eignet sie sich sehr gut für schnelle Datenanalysen und visuelle Hervorhebung.

Der Hauptvorteil dieser Methode liegt in der nicht-destruktiven, dynamischen Hervorhebung, die sich automatisch anpasst, wenn Daten oder Zielwerte sich ändern. Sie ist besonders geeignet für Dashboards, Präsentationen und Prüf-Szenarien, in denen Sichtbarkeit entscheidend ist. Wenn jedoch mehrere Werte denselben Abstand zum Ziel aufweisen, kann die Methode weniger präzise sein und gibt keine einzelnen Werte zur Weiterverarbeitung aus.

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

2. Klicken Sie im Register Start auf Bedingte Formatierung > Neue Regel.

3. Wählen Sie im Dialogfeld die Option Formel zur Ermittlung der zu formatierenden Zellen verwenden . 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, anschließend auf OK und nochmals OK, um die Regel anzuwenden.

Dadurch werden alle Zellen im ausgewählten Bereich hervorgehoben, deren Werte am nächsten zum Zielwert in E2 liegen.

Wenn Sie mit großen Bereichen arbeiten oder unerwartete Ergebnisse erhalten, überprüfen Sie Ihre Zellbezüge sorgfältig sowie die Verwendung von absoluten/relativen Bezügen (verwenden Sie $ zur Fixierung der Zielzelle und Bereiche).