Excel-Formel-Magie: Wie Sie die Zahl ausgeben, die einem bestimmten Wert am nächsten ist

Haben Sie sich jemals gefragt, wie Sie in Excel schnell die Zahl finden können, die einem bestimmten Wert am nächsten liegt? Ob es sich um eine Datenbank mit Preisen, eine Liste mit Messwerten oder eine andere Datensammlung handelt, Excel bietet leistungsstarke Formeln, mit denen Sie genau das erreichen können. In diesem Artikel tauchen wir tief in die Welt der Excel-Formeln ein und zeigen Ihnen, wie Sie diese „magische” Aufgabe elegant lösen können.

Das Problem: Einen Wert in der Nähe finden

Stellen Sie sich vor, Sie haben eine lange Liste mit Zahlen, und Sie müssen die Zahl finden, die einer bestimmten Zielzahl am nächsten liegt. Eine manuelle Suche kann mühsam und fehleranfällig sein, insbesondere bei großen Datensätzen. Glücklicherweise bietet Excel effiziente Wege, diesen Prozess zu automatisieren. Wir werden verschiedene Ansätze erkunden, die sich jeweils für unterschiedliche Szenarien eignen.

Ansatz 1: Die Kombination aus MIN, ABS und INDEX/MATCH

Dieser Ansatz ist einer der häufigsten und flexibelsten. Er nutzt die Funktionen MIN, ABS, INDEX und MATCH, um den Wert zu finden, der am nächsten liegt. Hier ist, wie es funktioniert:

1. ABS (Absolute Differenz): Zuerst berechnen wir den absoluten Unterschied zwischen jedem Wert in Ihrer Liste und Ihrer Zielzahl. Die Funktion ABS stellt sicher, dass wir nur positive Werte betrachten, da wir uns nur für die Nähe und nicht für die Richtung der Differenz interessieren.
2. MIN (Minimaler Wert): Als Nächstes verwenden wir die Funktion MIN, um den kleinsten (also den am wenigsten abweichenden) absoluten Unterschied zu finden.
3. MATCH (Position des minimalen Werts): Die Funktion MATCH findet die Position des minimalen absoluten Unterschieds innerhalb der Liste der absoluten Differenzen.
4. INDEX (Wert an der Position): Schließlich verwenden wir die Funktion INDEX, um den tatsächlichen Wert aus Ihrer ursprünglichen Liste abzurufen, der sich an der durch MATCH gefundenen Position befindet.

Hier ist die Formel in Excel:

=INDEX(A1:A10,MATCH(MIN(ABS(A1:A10-B1)),ABS(A1:A10-B1),0))

Erläuterung:

• A1:A10 ist der Bereich, der Ihre Liste von Zahlen enthält.
• B1 ist die Zelle, die Ihre Zielzahl enthält.

Schritt-für-Schritt-Beispiel:

1. Nehmen wir an, Ihre Zahlen befinden sich in den Zellen A1 bis A5: 10, 22, 35, 41, 50.
2. Ihre Zielzahl ist 38 und befindet sich in Zelle B1.
3. Die Formel würde zuerst die absoluten Differenzen berechnen:
   • |10 – 38| = 28
   • |22 – 38| = 16
   • |35 – 38| = 3
   • |41 – 38| = 3
   • |50 – 38| = 12
4. MIN würde den Wert 3 finden.
5. MATCH würde die Position von 3 in der Liste der absoluten Differenzen finden, was 3 und 4 ist (die Formel gibt das erste Vorkommen zurück).
6. INDEX würde den Wert an der 3. Position im Bereich A1:A5 zurückgeben, der 35 ist. In diesem Fall ist 35 genauso nah an 38 wie 41. Wenn wir wollen, dass 41 (oder das größere Zahl) zurückgegeben wird, wenn es mehrere Zahlen gibt, die gleich nah sind, müssen wir einen anderen Ansatz verwenden.

Wichtige Hinweise:

• Diese Formel gibt den ersten gefundenen Wert zurück, falls mehrere Werte gleich nah an Ihrer Zielzahl liegen.
• Denken Sie daran, die Zellbezüge (A1:A10 und B1) an Ihre spezifische Tabelle anzupassen.
• Für sehr große Datensätze kann diese Formel etwas leistungsschwach sein.

Ansatz 2: Verwendung der AGGREGATE-Funktion (Für Excel 2010 und höher)

Die AGGREGATE-Funktion bietet eine flexiblere und oft effizientere Alternative, besonders wenn Sie mit Fehlern oder versteckten Zeilen in Ihren Daten zu tun haben. Sie kann verschiedene Berechnungen durchführen, einschließlich der Suche nach dem Minimum unter Ignorierung von Fehlern.

=INDEX(A1:A10,AGGREGATE(5,6,ROW(A1:A10)-ROW(A1)+1/(ABS(A1:A10-B1)=MIN(ABS(A1:A10-B1))),1))

Erläuterung:

• A1:A10 ist der Bereich, der die Liste der Zahlen enthält.
• B1 ist die Zelle, die die Zielzahl enthält.
• AGGREGATE(5,6,...) findet das Minimum unter Ignorierung von Fehlern. 5 bedeutet MIN, und 6 bedeutet, Fehlerwerte ignorieren.
• ROW(A1:A10)-ROW(A1)+1 erstellt ein Array von Zeilennummern (1, 2, 3, …, 10).
• ABS(A1:A10-B1)=MIN(ABS(A1:A10-B1)) erstellt ein Array von TRUE/FALSE-Werten, wobei TRUE dort steht, wo der absolute Unterschied minimal ist.
• 1/(ABS(A1:A10-B1)=MIN(ABS(A1:A10-B1))) erzeugt einen Fehler (#DIV/0!), wenn der absolute Unterschied *nicht* minimal ist. Die AGGREGATE-Funktion ignoriert diese Fehler. Dort, wo der Unterschied minimal ist, ist das Ergebnis 1.
• Insgesamt findet AGGREGATE die kleinste Zeilennummer, bei der der absolute Unterschied minimal ist.

Dieser Ansatz vermeidet das Problem mehrerer minimaler Werte, indem er das erste Vorkommen zurückgibt.

Ansatz 3: Benutzerdefinierte Funktion (VBA)

Für komplexere Szenarien oder wenn Sie diese Funktionalität häufig benötigen, können Sie eine benutzerdefinierte Funktion (UDF) in VBA (Visual Basic for Applications) erstellen.

1. Öffnen Sie den VBA-Editor (Alt + F11).
2. Fügen Sie ein neues Modul ein (Einfügen > Modul).
3. Fügen Sie den folgenden Code ein:

Function NaechsterWert(Bereich As Range, Zielwert As Double) As Double
  Dim Zelle As Range
  Dim MinDiff As Double
  Dim AktuellerWert As Double
  Dim NaechsterWertGefunden As Double

  MinDiff = 999999999 'Ein sehr großer Startwert

  For Each Zelle In Bereich
    AktuellerWert = Zelle.Value
    Dim Diff As Double
    Diff = Abs(AktuellerWert - Zielwert)

    If Diff < MinDiff Then
      MinDiff = Diff
      NaechsterWertGefunden = AktuellerWert
    End If
  Next Zelle

  NaechsterWert = NaechsterWertGefunden

End Function

Erläuterung des VBA-Codes:

• Die Funktion NaechsterWert nimmt einen Bereich (Bereich) und einen Zielwert (Zielwert) als Eingabe.
• Sie durchläuft jede Zelle im angegebenen Bereich.
• Sie berechnet den absoluten Unterschied zwischen dem Zellenwert und dem Zielwert.
• Sie verfolgt den minimalen Unterschied und den entsprechenden Wert.
• Schließlich gibt sie den Wert zurück, der dem Zielwert am nächsten liegt.

Verwendung der benutzerdefinierten Funktion:

In Ihrer Excel-Zelle können Sie die Funktion wie folgt verwenden:

=NaechsterWert(A1:A10, B1)

Dabei ist A1:A10 Ihr Zahlenbereich und B1 die Zelle mit dem Zielwert.

Ansatz 4: Datensortierung und einfachere Formel

Wenn es möglich ist, die Daten im Excel-Blatt zu sortieren, kann man die Formel deutlich vereinfachen. Nehmen wir an, die Daten in Spalte A (A1:A10) sind nach Größe sortiert. Dann kann man die folgende Formel verwenden:

=WENN(ABS(INDEX(A1:A10,MATCH(B1,A1:A10,1))-B1) < ABS(INDEX(A1:A10,MATCH(B1,A1:A10,1)+1)-B1),INDEX(A1:A10,MATCH(B1,A1:A10,1)),INDEX(A1:A10,MATCH(B1,A1:A10,1)+1))

Diese Formel funktioniert folgendermaßen:

• MATCH(B1,A1:A10,1) findet die größte Zahl, die kleiner oder gleich der Zielzahl (B1) ist.
• INDEX(A1:A10,MATCH(B1,A1:A10,1)) gibt diesen Wert aus.
• INDEX(A1:A10,MATCH(B1,A1:A10,1)+1) gibt den Wert aus, der direkt nach dem eben gefundenen Wert kommt.
• Die Formel vergleicht die absoluten Differenzen dieser beiden Werte mit dem Zielwert und gibt den Wert mit der kleinsten Differenz aus.

Vorteile dieses Ansatzes: Einfacher zu verstehen, wenn die Daten sortiert werden können.

Fazit

Excel bietet verschiedene Möglichkeiten, um die Zahl zu finden, die einem bestimmten Wert am nächsten liegt. Die Wahl des Ansatzes hängt von Ihren spezifischen Anforderungen, der Größe Ihrer Daten und Ihrer Vertrautheit mit den verschiedenen Excel-Funktionen ab. Ob Sie die Kombination aus MIN, ABS, INDEX und MATCH, die leistungsstarke AGGREGATE-Funktion, eine benutzerdefinierte VBA-Funktion oder die Sortierlösung verwenden, Sie können Ihren Arbeitsablauf optimieren und die Genauigkeit Ihrer Datenanalyse verbessern. Experimentieren Sie mit diesen Techniken und entdecken Sie die Excel-Formel-Magie, die Ihnen zur Verfügung steht!