lückenlose Auflistung fehlender Zahlen *

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	7	2	2	2
2	20	4	4	4
3	1	5	5	5
4	77	6	6	6
5	20	8	8	8
6	15	10	10	10
7	9	12	12	12
8	11	13	13	13
9	11	14	14	14
10	1	16	16	16
11	-4	17	17	17
12	7	19	19	19
13	7	#ZAHL!		0
14	773	#ZAHL!		0
15	18	#ZAHL!		0
16	3	#ZAHL!		0
17		#ZAHL!		0
18		#ZAHL!		0

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.

Das Portal zum Thema Excel-Formeln