Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Listen/Datensätze - BEITRAG: lückenlose Auflistung fehlender Zahlen *


Aufgabe
in A1 bis A16 stehen unsortiert (auch mehrfach vorkommende) Zahlen. Es sollen lückenlos alle Zahlen aufgelistet werden, die NICHT zwischen 1 und 20 (inklusive) vorkommen.

       A               B               C               D       
1 2  
2 20  4  
3 5  
4 77  6  
5 20  8  
6 15  10  10  10  
7 12  12  12  
8 11  13  13  13  
9 11  14  14  14  
10 16  16  16  
11 -4  17  17  17  
12 19  19  19  
13 #ZAHL!    0  
14 773  #ZAHL!    0  
15 18  #ZAHL!    0  
16 #ZAHL!    0  
17   #ZAHL!    0  
18   #ZAHL!   

Lösung
in Zelle B1:
{=KKLEINSTE(WENN(ISTNV(VERGLEICH(ZEILE($1:$20);(A$1:A$16);));ZEILE($1:$20));ZEILE(A1))}
und runterkopieren

ohne die Fehlermeldung #ZAHL! zum Schluß in C1:
{=WENN(ZEILE(A1)>SUMME(ISTNV(VERGLEICH(ZEILE($1:$20);(A$1:A$16);))*1);"";KKLEINSTE(WENN(ISTNV(VERGLEICH(ZEILE($1:$20);(A$1:A$16);));ZEILE($1:$20));ZEILE(A1)))}

Mit Häufigkeit (Franz Pölt) in D1:
{=REST(MIN(KKLEINSTE((HÄUFIGKEIT(A$1:A$16;ZEILE($1:$20))>0)*20+ZEILE($1:$21);ZEILE(A1));21);21)}
Hier darf in A1:A16 keine Fehlermeldung vorkommen - bei obigen Formeln schon.
Die Formel darf nur bis Zeile 21 kopiert werden - danach erscheint #ZAHL!



Erläuterung
Sollen umgekehrt die vorkommenden Zahlen aufgelistet werden: die identischen Formeln mit ISTZAHL statt ISTNV bzw. =0 statt >0.

Sollen alle fehlenden Zahlen von 0 bis 20 aufgelistet werden, dann in den ersten beiden Formeln jeweils ZEILE($1:$20) ersetzen durch ZEILE($1:$21)-1
Die Formel von Franz ist dafür nicht geeignet, denn eine fehlende 0 wird nicht aufgelistet, wenn in A1 bis A16 negative Zahlen vorkommen.