☰

Den nächsthöheren Wert in einer Spalte finden

tag-icon • tag-icon microsoft-excel worksheet-function lookup
Den nächsthöheren Wert in einer Spalte finden

Ich habe zwei Spalten, Aund B. Spalte Bhat Werte, die ich in Spalte nachschlagen muss A. Allerdings muss ich nicht den exakt entsprechenden Wert finden, sondern den nächsthöheren Wert.

Zum Beispiel:

Column A    Column B   

   2           3
   4           4
   5           5
   7           6
   8           8
   9           9

Ich möchte also für den Wert 5in Spalte den Wert aus Spalte Bzurückgeben .7A

Ich schätze, ich brauche wahrscheinlich eine Art Nachschlage-/Indexübereinstimmungsfunktion, aber ich konnte die Formel nicht selbst schreiben.

Antwort1

Sortiert

Die einfachste Formel gilt für den Fall, dass die Spalte Ain aufsteigender Reihenfolge sortiert ist:

Screenshot des Arbeitsblatts

Geben Sie die folgende Formel ein C1und verwenden Sie Strg-Eingabe/Kopieren-Einfügen/Nach unten ausfüllen/Automatisch ausfüllen für die restlichen Spalten der Tabelle:

=INDEX(A:A,1+MATCH(B1,A:A,1))

Erläuterung:

Das 1als drittes Argument bedeutet MATCH(), dass der größte Wert gefunden wird, der kleiner oder gleich dem ersten Argument ist. Das Hinzufügen 1zu diesem Index ergibt den Index der nächsthöheren Zahl. Die INDEX()Funktion extrahiert dann die Zahl.

Beachten Sie, dass ich am Ende der Spalte einen zusätzlichen Wert hinzugefügt habe A. Dies gilt für den Sonderfall, in dem es keinen nächsthöheren Wert gibt.

Unsortiert

Für den Fall, dass die Spalte Aunsortiert ist (funktioniert auch, wenn sie sortiert ist), ist die Formel ein wenig komplizierter:

Geben Sie in das Array ( Ctrl+ Shift+ Enter) die folgende Formel ein C1und kopieren Sie sie in die restliche Tabellenspalte, fügen Sie sie ein bzw. füllen Sie sie aus (vergessen Sie nicht, das {und zu entfernen }):

{=SMALL(IF($A$1:$A$6>B1,$A$1:$A$6),1)}

Erläuterung:

Die Funktion gibt den n-ten kleinsten Wert des Arrays zurück,SMALL(array,n)Boolesche Werte ignorierenDa der Standardwert für das dritte Argument der IF()Funktion ist FALSE, werden nur Werte überprüft, die größer als der Wert in der Spalte sind B, was zum nächsthöheren Wert führt.

Beachten Sie, dass kein spezieller Abschlusswert für die Spalte Aerforderlich ist, da ein #NUM!Fehler auftritt, wenn in der Spalte keine Werte vorhanden sind, Adie größer sind als der Wert in der Spalte B.

Schließlich gibt es, wie Aventurin hervorgehoben hat, eine alternative, ähnliche Formel, die unabhängig von der Sortierung funktioniert (allerdings mit einem wichtigen Vorbehalt).

Für Excel 2016+:

=MINIFS($A$1:$A$6,$A$1:$A$6,">"&B1)

Dies funktioniert, weil die MINIFS()Funktion die Werte herausfiltert, die nicht den Kriterien entsprechen, bevor der Mindestwert extrahiert wird.

Für frühere Versionen von Excel:

{=MIN(IF($A$1:$A$6>B1,$A$1:$A$6))}

Dies funktioniert aus demselben Grund wie die SMALL()Funktion – es ignoriert boolesche Werte, die von der IF()Funktion generiert werden.

Vorbehalt:

Die Formeln =MINIFS()und {=MIN(IF())}funktionieren nicht korrekt, wenn eine Null der korrekte nächsthöhere Wert sein kann, da auch dann eine Null zurückgegeben wird, wennIstkein nächsthöherer Wert. (Dies ist der gleiche Grund, warum Abei der ersten Formel ein zusätzlicher Wert am Ende der Spalte hinzugefügt wird – diese Formel gibt ebenfalls eine Null zurück, wenn es keine höheren Werte gibt.)

Antwort2

Sie können beispielsweise die Array-Funktion {=MIN(IF(A1:A6 > B1; A1:A6))}oder {=MIN(IF(A1:A6 > B1; A1:A6; 1000))}(mit 1000 als Fallback-Wert) verwenden.

Es wird das Minimum aller Werte aus Spalte A genommen, die größer sind als der Wert aus der aktuellen Zelle aus Spalte B (hier B1). Daher muss keine der Spalten sortiert sein.

Mit Excel >= 2016 können Sie die Funktion auch nutzen MINIFS.

Beachten Sie, dass Array-Funktionen durch Drücken von eingefügt werden müssen Ctrl+Shift+Enter.

verwandte Informationen