Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Listen/Datensätze - BEITRAG: Zahlen >= -10 und <= 50 auflisten wahlweise sortiert/unsortiert


Aufgabe
Aus einer Zahlenreihe sollen alle Zahlen innerhalb einer Unter- und Obergrenze aufgelistet werden.

       A               B               C               D               E               F               G       
1 25  -10  -5  -5  25  25  1  
2 66  50  -5  20  25  -5   
3 25    -5  25  -5  40   
4 -5    20  31  -5  20  2  
5 -5    25  40  40  44   
6 40    25  44  25  31  3  
7 25    25    -5     
8 111    25    25     
9 -5    31    20     
10 55    40    44     
11 25    44    31     
12 20            4  
13 -25             
14 44            5  
15 31            6  
16     sortiert mit  sortiert ohne  unsortiert mit  unsortiert ohne   
17     Duplikaten  Duplikate  Duplikaten  Duplikate  Hilfsspalte für F 


Lösung
Die Zahlen stehen in Spalte A; in Zelle B1 steht die Untergrenze - in B2 die Obergrenze.

Spalte C: sortiert mit Duplikaten
in C1 steht:
{=KKLEINSTE(WENN(A$1:A$100<>"";WENN(A$1:A$100>=B$1;WENN(A$1:A$100<=B$2;A$1:A$100)));ZEILEN($1:1))}
und runterkopieren

Spalte D: sortiert ohne Duplikate
in D1 steht die identische Formel wie in C1 oder etwas kürzer:
{=MIN(WENN(A1:A100<>"";WENN(A1:A100>=B1;WENN(A1:A100<=B2;A1:A100))))}
in D2 steht:
{=WENN(ODER(D1="";D1=MAX(A:A);MIN(WENN((A$1:A$100>D1);A$1:A$100))>B$2);"";MIN(WENN(A$1:A$100<>"";WENN(A$1:A$100>D1;A$1:A$100))))}
und runterkopieren

Spalte E: unsortiert mit Duplikaten (wie beim Auto- und Spezialfilter)
in E1 steht:
{=WENN(ZEILE()>SUMME((A$1:A$100>=B$1)*(A$1:A$100<>"")*(A$1:A$100<=B$2));"";INDEX(A:A;KKLEINSTE(WENN((A$1:A$100>=B$1)*(A$1:A$100<=B$2);ZEILE($1:$100));ZEILE())))}
und runterkopieren

Spalte F: unsortiert ohne Duplikate
in F1 steht:
=WENN(ZEILE()>MAX(G:G);"";INDEX(A:A;VERGLEICH(ZEILE();G:G;0)))
und runterkopieren
in G1 (Hilfsspalte) steht:
=WENN(ODER(A1="";A1 < B$1;A1>B$2);"";1)
in G2 steht:
=WENN(ODER(A2="";A2 < B$1;A2>B$2;ZÄHLENWENN($A$1:A2;A2)>1);"";MAX(G$1:G1)+1)
und runterkopieren