24 Zahlen

Forumthread: 24 Zahlen

24 Zahlen

06.09.2008 19:46:00

Udo

Hallo !
Ich hab immer in einer Excel Spalte von 1-24 zufällige Zahlen von 0 - 36. ( Die 0 gehört als Zahl dazu )
Davon sind immer einige mehrfach eingetragen.
Nehmen wir an es sind die Zahlen
33 10 12 16 19 32 9 10 8 34 4 15 7 9 9 16 35 7 16 12 21 24 22 4
Wie kann ich denn jetzt am geschicktesten rausfinden, welche Zahlen von 0 bis 36 fehlen ?
Vielen Dank für Euer feedback.
LG Udo

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: 24 Zahlen

06.09.2008 20:09:24

Tino

Hallo,
so müsste es gehen.
Mit Formel:

	A	B
1	33	0
2	10	1
3	12	2
4	16	3
5	19
6	32	5
7	9	6
8	10
9	8
10	34
11	4
12	15	11
13	7
14	9	13
15	9	14
16	16
17	35
18	7	17
19	16	18
20	12
21	21	20
22	24
23	22
24	4	23
25
26		25
27		26
28		27
29		28
30		29
31		30
32		31
33
34
35

Formeln der Tabelle

Zelle	Formel
B1	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B2	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B3	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B4	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B5	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B6	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B7	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B8	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B9	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B10	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B11	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B12	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B13	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B14	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B15	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B16	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B17	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B18	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B19	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B20	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B21	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B22	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B23	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B24	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B25	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B26	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B27	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B28	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B29	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B30	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B31	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B32	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B33	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B34	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)
B35	=WENN(ZÄHLENWENN(A:A;ZEILE()-1)>0;"";ZEILE()-1)

Mit VBA und sortieren:

Option Explicit

Sub SucheFehlende()
Dim Bereich As Range
Set Bereich = Range("B1:B35")
    Bereich.FormulaR1C1 = "=IF(COUNTIF(C[-1],ROW()-1)>0,"""",ROW()-1)"
    Bereich.Value = Bereich.Value

    Bereich.Sort Key1:=Bereich(1), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub

Ergebnis sieht so aus:

	A	B
1	33	0
2	10	1
3	12	2
4	16	3
5	19	5
6	32	6
7	9	11
8	10	13
9	8	14
10	34	17
11	4	18
12	15	20
13	7	23
14	9	25
15	9	26
16	16	27
17	35	28
18	7	29
19	16	30
20	12	31
21	21
22	24
23	22
24	4

Gruß Tino

24 Zahlen, so gehts ...

06.09.2008 20:13:39

Matthias

Hallo
Hier 2 Beispielzeilen in der Datei
https://www.herber.de/bbs/user/55225.xls
Die Zahlen in A4:A27 sind änderbar
Ich habe das Blatt geschützt ohne PW.
In der ersten Zeile habe ich die bedingte Formatierung genutzt
In der zweiten Zeile dann Formeln
F steht für fehlt.
Hilfts?
Gruß Matthias

AW: 24 Zahlen

06.09.2008 20:43:58

Udo

Hallo Tino,
Hallo Matthias,
ich bin ja ganz begeistert. Vielen Dank Euch beiden. Ich denke ich komm damit klar.
Bin ein bisschen traurig, daß mir selbst nie solch schöne codes einfallen :-(
Schönes Wochende allerseits
und nochmals vielen Dank
Udo

LÜCKENLOSE Auflistung fehlender Zahlen

06.09.2008 21:12:00

Hi Udo,
in A1 bis A24 stehen Deine (auch mehrfach vorkommenden) Zahlen.
in B1 steht die Arrayformel:
{=KKLEINSTE(WENN(ISTFEHLER(VERGLEICH(ZEILE($1:$37)-1;(A$1:A$24);0));ZEILE($1:$37));ZEILE(A1))-1}
runterkopieren
und es werden Dir die Zahlen aufgelistet, die zwischen 0 und 36 in der Spalte A nicht vorkommen.
ARRAY-Formel {=geschweifte Klammern} nicht eingeben;
Abschluß der Formel mit gleichzeitig Strg / Shift / Enter; - das erzeugt sie.
trapp, trapp, trapp, trapp, trapp, brrrrr
WF
http://www.excelformeln.de/
die ultimative Formelseite

wieder Klasse ...

07.09.2008 08:11:12

neopa

Guten Morgen Walter,
... Deine Lösungsformel. Eben eine echte WF-Formel!
Man könnte noch ein bißchen Schnickschnack als Beiwerk zu Deiner Formell anfügen, um am unteren Ende evtl. noch störende Fehlermeldungen zu unterdrücken:


{=WENN(ZEILE()>37-SUMME(--(HÄUFIGKEIT(A:A;A:A)>0));""; KKLEINSTE(WENN(ISTFEHLER(VERGLEICH(ZEILE($1:$37)-1;(A$1:A$24);));ZEILE($1:$37));ZEILE())-1)  }

Für Mitleser: Wenn Die Formel nicht in der ersten Zeile beginnt, muss natürlich ZEILE(A1) anstelle ZEILE() geschrieben werden.
Ein schönen Sonntag wünsch ich noch.
Gruß Werner
.. , - ...

Auflistung ohne Fehlermeldungen

07.09.2008 11:35:00

Moin Werner,
Deine Lösung mit Häufigkeit funktioniert aber nur, wenn in A1 bis A24 ausschließlich Zahlen von 0 bis 24 vorkommen.
Können auch andere vorkommen, dann:
{=WENN(ZEILE(A1)>ANZAHL(WENN(ISTFEHLER(VERGLEICH(ZEILE($1:$37)-1;(A$1:A$24);0));ZEILE($1:$37))); "";KKLEINSTE(WENN(ISTFEHLER(VERGLEICH(ZEILE($1:$37)-1;(A$1:A$24);0));ZEILE($1:$37));ZEILE(A1))-1) }
Salut WF

trotzdem HÄUFIGKEIT() ...

07.09.2008 18:07:00

neopa

Hallo Walter ,
... auch wenn entgegen der Aufgabenstellung auch andere Zahlen als 0 bis 36 (was Du auch sicherlich meintest, nicht 0 bis 24) in A1:A24 enthalten sind, könnte man die Fehlermeldung mittels HÄUFIGKEIT() unterdrücken. natürlich etwas angepasst.


{=WENN(ZEILE()>37-SUMME(--(HÄUFIGKEIT(N(A1:A360));""; KKLEINSTE(WENN(ISTFEHLER(VERGLEICH(ZEILE($1:$37)-1;(A$1:A$24);));ZEILE($1:$37));ZEILE())-1) }

Ist halt etwas kürzer ;-)
Ein schönen Abend wünsch ich noch.
Gruß Werner
.. , - ...

mit HÄUFIGKEIT - aber kürzer ;-)

07.09.2008 20:46:32

Hallo Werner,
fehlende Zahlen von 0 bis 36 im Bereich A1:A24:
{=REST(MIN(KKLEINSTE((HÄUFIGKEIT(A$1:A$24;ZEILE($1:$37)-1)>0)*37+ZEILE($1:$38)-1;ZEILE(A1));37); 37) }
so einfach ist das ;-)
Servus aus dem Salzkammergut
Franz

das ist natürlich wieder ein echter FP-Hammer ...

08.09.2008 08:57:42

neopa

Guten Morgen Franz,
... und haut mich glatt um. Da wär ich wohl nie draufgekommen, selbst wenn ich nicht so abgespannt wäre wie zur Zeit (ein Glück das ich bald Urlaub hab).
Eine schöne Woche Dir
Gruß Werner
.. , - ...

AW: Bedingung mit SUMME(1/ZÄHLENWENN...)

08.09.2008 09:50:00

Erich

Hi zusammen,
jetzt noch eine etwas kürzere Abbruchbedingung zu Franz' und Walters Formel:

	A	B	C	D	E	F	G
1	2	0	0	0	0	0	0
2	15	1	1	1	1	1	1
3	7	4	4	4	4	4	4
4	15	6	6	6	6	6	6
5	10	9	9	9	9	9	9
6	32	11	11	11	11	11	11
7	31	14	14	14	14	14	14
8	26	16	16	16	16	16	16
9	5	19	19	19	19	19	19
10	33	22	22	22	22	22	22
11	18	24	24	24	24	24	24
12	27	25	25	25	25	25	25
13	30	28	28	28	28	28	28
14	20	29	29	29	29	29	29
15	17	35	35	35	35	35	35
16	23	36	36	36	36	36	36
17	3	#ZAHL!	#ZAHL!	0
18	13	#ZAHL!	#ZAHL!	0
19	12	#ZAHL!	#ZAHL!	0
20	8	#ZAHL!	#ZAHL!	0
21	34	#ZAHL!	#ZAHL!	0
22	21	#ZAHL!	#ZAHL!	0
23	17	#ZAHL!	#ZAHL!	0
24	20	#ZAHL!	#ZAHL!	0

Formeln der Tabelle

Zelle	Formel
B1	{=KKLEINSTE(WENN(ISTFEHLER(VERGLEICH(ZEILE($1:$37)-1;(A$1:A$24); 0)); ZEILE($1:$37)); ZEILE(A1))-1}
C1	{=WENN(ZEILE()>37-SUMME(--(HÄUFIGKEIT(N(A1:A36<37); {1.0})>0)); ""; KKLEINSTE(WENN(ISTFEHLER(VERGLEICH(ZEILE($1:$37)-1;(A$1:A$24); )); ZEILE($1:$37)); ZEILE())-1)}
D1	{=REST(MIN(KKLEINSTE((HÄUFIGKEIT(A$1:A$24;ZEILE($1:$37)-1)>0)*37+ZEILE($1:$38)-1;ZEILE(A1)); 37); 37)}
E1	{=WENN(ZEILE(A1)>ANZAHL(WENN(ISTFEHLER(VERGLEICH(ZEILE($1:$37)-1;(A$1:A$24); 0)); ZEILE($1:$37))); "";KKLEINSTE(WENN(ISTFEHLER(VERGLEICH(ZEILE($1:$37)-1;(A$1:A$24); 0)); ZEILE($1:$37)); ZEILE(A1))-1)}
F1	{=WENN(SUMME(1/ZÄHLENWENN($A$1:$A$24;$A$1:$A$24))<38-ZEILE(); KKLEINSTE(WENN(ISTFEHLER(VERGLEICH(ZEILE($1:$37)-1;(A$1:A$24); 0)); ZEILE($1:$37)); ZEILE(A1))-1;"")}
G1	{=WENN(SUMME(1/ZÄHLENWENN($A$1:$A$24;$A$1:$A$24))<38-ZEILE(); REST(MIN(KKLEINSTE((HÄUFIGKEIT(A$1:A$24;ZEILE($1:$37)-1)>0)*37+ZEILE($1:$38)-1;ZEILE(A1)); 37); 37); "")}

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Das funktioniert nicht bzw. nur,

08.09.2008 12:12:23

Hi Erich,
wenn in A1:A24 keine Leerzellen vorkommen.
wenn in A1:A24 keine Zahlen außerhalb des Bereiches 0:36 vorkommen.
Salut WF

AW: Das funktioniert bei der gestellten Aufgabe

08.09.2008 12:20:00

Erich

Hi Walter,
na ja, ich bin halt von der Aufgabenstellung ausgegangen:
"Ich hab immer in einer Excel Spalte von 1-24 zufällige Zahlen von 0 - 36."
Grüße von Erich aus Kamp-Lintfort

hab drei Macken entdeckt

08.09.2008 19:20:28

Hallo Franz,
2 harmlose:
Die Formel funktioniert nicht, wenn in A1:A24 eine Fehlermeldung vorkommt.
Wird die Formel weiter als 37 Zeilen kopiert, erscheint #ZAHL! (würde ne Rolle spielen, wenn man die bis-Zahl - hier 36 - durch ein Eingabefeld dynamisch gestalten will.
eine gravierende:
Die fehlende Null wird nicht gelistet, wenn in A1:A24 auch negative Zahlen vorkommen.
Hab bei uns nen Beitrag (fehlende Zahlen von 1 bis 20 - also ohne Null) mit Deiner Formel als Alternative gebastelt. Muss noch freigegeben werden.
Salut WF

Beliebteste Forumthreads (12 Monate)

	A	B
1	33	0
2	10	1
3	12	2
4	16	3
5	19
6	32	5
7	9	6
8	10
9	8
10	34
11	4
12	15	11
13	7
14	9	13
15	9	14
16	16
17	35
18	7	17
19	16	18
20	12
21	21	20
22	24
23	22
24	4	23
25
26		25
27		26
28		27
29		28
30		29
31		30
32		31
33
34
35

	A	B
1	33	0
2	10	1
3	12	2
4	16	3
5	19	5
6	32	6
7	9	11
8	10	13
9	8	14
10	34	17
11	4	18
12	15	20
13	7	23
14	9	25
15	9	26
16	16	27
17	35	28
18	7	29
19	16	30
20	12	31
21	21
22	24
23	22
24	4

	A	B
1	33	0
2	10	1
3	12	2
4	16	3
5	19
6	32	5
7	9	6
8	10
9	8
10	34
11	4
12	15	11
13	7
14	9	13
15	9	14
16	16
17	35
18	7	17
19	16	18
20	12
21	21	20
22	24
23	22
24	4	23
25
26		25
27		26
28		27
29		28
30		29
31		30
32		31
33
34
35

	A	B
1	33	0
2	10	1
3	12	2
4	16	3
5	19	5
6	32	6
7	9	11
8	10	13
9	8	14
10	34	17
11	4	18
12	15	20
13	7	23
14	9	25
15	9	26
16	16	27
17	35	28
18	7	29
19	16	30
20	12	31
21	21
22	24
23	22
24	4

Forumthread: 24 Zahlen

13 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

13
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

	A	B
1	33	0
2	10	1
3	12	2
4	16	3
5	19
6	32	5
7	9	6
8	10
9	8
10	34
11	4
12	15	11
13	7
14	9	13
15	9	14
16	16
17	35
18	7	17
19	16	18
20	12
21	21	20
22	24
23	22
24	4	23
25
26		25
27		26
28		27
29		28
30		29
31		30
32		31
33
34
35

	A	B
1	33	0
2	10	1
3	12	2
4	16	3
5	19	5
6	32	6
7	9	11
8	10	13
9	8	14
10	34	17
11	4	18
12	15	20
13	7	23
14	9	25
15	9	26
16	16	27
17	35	28
18	7	29
19	16	30
20	12	31
21	21
22	24
23	22
24	4