So spüren Sie Werte per Formel in unsortierten Listen auf

23. Mai 2017
Martin Althaus Von Martin Althaus, Excel, Office ...

In unsortierten Listen Daten über Tabellenfunktionen zu finden, ist ein Problem. Das liegt daran, dass viele Verweisfunktionen nur in sortierten Listen das gewünschte Ergebnis liefern, falls der Suchwert nicht exakt gefunden wird. Über Spezialformeln finden Sie auch in einer unsortierten Liste das nächstkleinere und nächstgrößere Ergebnis zu Ihrem Suchwert.

Diese Formeln sind beispielsweise hervorragend geeignet, um Produktionskapazitäten zuzuweisen oder auch um Rechnungs- oder Angebotsdaten zu finden. Mit dieser Formel finden Sie den nächstkleineren oder gleichen Wert: 

=KGRÖSSTE(DatBer;ZÄHLENWENN (DatBer;”>”&Suchwert)+1)

Setzen Sie diese Formel ein, um den nächstgrößeren oder gleichen Wert aufzuspüren:

=KKLEINSTE(DatBer;ZÄHLENWENN (DatBer;”<”&Suchwert)+1)

Sie übergeben den Formeln jeweils zwei Argumente: Mit DatBer übergeben Sie den Spaltenbereich, in dem Sie den nächstgrößeren oder -kleineren Wert suchen. Das Argument Suchwert legt den Wert fest, den Sie innerhalb von DatBer suchen.

Die Formeln liefern den nächstgrößeren bzw. -kleineren Wert zu dem Suchwert, sofern der Suchwert nicht exakt auftritt. Falls der Suchwert exakt auftritt, liefern die Formeln den Suchwert als Ergebnis.

In den Zellen F5 und F9 liefern die folgenden beiden Formeln in Abbildung [1] die Ergebnisse 31 und 17:

Zelle F5: =KKLEINSTE($C$4:$C$18; ZÄHLENWENN($C$4:$C$18;”<”&F3)+1)

Zelle F9: =KGRÖSSTE($C$4:$C$18; ZÄHLENWENN($C$4:$C$18;”>”&F3)+1)

Der Suchwert 25 ist im Bereich C4:C18 nicht enthalten. Der nächstgrößere Wert ist 31 und der nächstkleinere Wert 17. Die Formeln arbeiten nach folgendem Prinzip, um diese Werte zu ermitteln:

Über ZÄHLENWENN zählen Sie, wie viele Werte größer bzw. kleiner als der Suchwert sind. Indem Sie diese Anzahl plus 1 an KKLEINSTE bzw. KGRÖSSTE übergeben, wird der entsprechend kleinste bzw. größte Wert ausgegeben. Das ist dann genau der Wert, der in der Rangfolge direkt über bzw. unter dem Suchwert liegt.

Wie Sie die passenden Restdaten ermitteln

Um für die gefundenen Werte die dazugehörenden Daten zu ermitteln, setzen Sie in Zelle F6 die folgende Formel ein:

=INDEX($A$4:$A$18;VERGLEICH ($F$5;$C$4:$C$18;0))

Über VERGLEICH findet die Formel die Position des gefundenen Werts aus Zelle F5. Die INDEX-Funktion liefert dazu den passenden Wert aus dem Bereich A4:A18. [2] Für die Zellen F7, F10 und F11 passen Sie die Bezüge der Formel entsprechend an.

Werden Sie mit unseren Tipps zum Excel-Profi!

Jetzt gratis per E-Mail

DDV Ehrencodex E-Mail-Marketing
  • Wir benötigen Ihre E-Mail-Adresse für die Zusendung des Newsletters.
  • Wir geben Ihre E-Mail-Adresse garantiert nicht an Dritte weiter.
  • Jederzeit abbestellbar durch einen Link im Newsletter.

Unsere Datenschutzgarantie:

Wir benötigen Ihre E-Mail-Adresse für die Zusendung des Newsletters. Wir geben Ihre E-Mail-Adresse garantiert nicht an Dritte weiter. Jederzeit abbestellbar durch einen Link im Newsletter.

Weitere Artikel zum Thema

Newsletter

Jetzt kostenlose Excel-Hilfe anfordern!

  • die besten Tipps
  • völlig kostenlos
  • jederzeit abbestellbar
DDV Ehrencodex E-Mail-Marketing
  • Wir benötigen Ihre E-Mail-Adresse für die Zusendung des Newsletters.
  • Wir geben Ihre E-Mail-Adresse garantiert nicht an Dritte weiter.
  • Jederzeit abbestellbar durch einen Link im Newsletter.

Unsere Datenschutzgarantie:

Wir benötigen Ihre E-Mail-Adresse für die Zusendung des Newsletters. Wir geben Ihre E-Mail-Adresse garantiert nicht an Dritte weiter. Jederzeit abbestellbar durch einen Link im Newsletter.

Unsere Experten

Rudolf Ring twittert für @Computerwissen

Die von Ihnen eingegebene E-Mail Adresse ist nicht korrekt. Bitte korrgieren Sie Ihre Eingabe und klicken Sie auf "OK":

Bitte wählen Sie mindestens einen Newsletter aus und klicken Sie auf "OK"