Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Verweisfunktionen - BEITRAG: Ermittlung der Position des nächst höheren Eintrages *


Aufgabe
Ist eine Werteliste aufsteigend sortiert, ermittelt die Formel VERGLEICH mit hinten ...;1) die Position des größten Wertes, der kleiner gleich dem Suchkriterium ist. Nun soll die Position des kleinsten Wertes ermittelt werden, der größer gleich dem Suchkriterium ist.

Beispiel: Suchwert=12
=VERGLEICH(12;A1:A99;1) liefert die Position des größten Wertes, der kleiner 12 ist (die 10 in Pos. 2)
Gesucht: Position des kleinsten Wertes, der größer gleich 12 ist: (die 15 in Pos. 3)

       A       
1 5  
2 10  
3 15  
4 20  
5 25  
6 30  
7 35  
8 40  
9 45  
10 50 

Lösung
Suchspalte: A
Suchwert: 12

=VERGLEICH(12;A:A;1)+(ZÄHLENWENN(A:A;12)=0)

Ist absteigend sortiert, wird normalerweise (VERGLEICH mit hinten ...;-1) der nächst höhere Wert ermittelt. Soll der nächst kleinere ermittelt werden, funktioniert das mit
=VERGLEICH(12;A:A;-1)+(ZÄHLENWENN(A:A;12)=0)
oder mit VERWEIS und versetzter Zählspalte (von neopa):
=VERWEIS(2;1/(A:A>12);ZEILE(X2:X99))

Ergänzung:
Liegen die Werte unsortiert vor und können auch Leerzellen vorkommen, dann:
{=VERGLEICH(MIN(WENN(A1:A99>=12;A1:A99));A:A;0)}
für den nächst höheren
bzw.
{=VERGLEICH(MAX(WENN(A1:A99<=12;A1:A99));A:A;0)}
für den nächst kleineren

Erläuterung
In der VERGLEICH-Funktion gibt es hinten 3 Vergleichstypen: ;-1 ;0 ;1
Lässt man den komplett (mit Semikolon) weg, wird Typ 3 (die ;1) angewandt.