Tabelle automatisch erstellen

[Tarkrai]

Hallo,
ich würde gern eine Tabelle erstellt bekommen je nach Angaben in den jeweiligen Feldern, dabei soll die Anzahl an Zeilen die erstellt werden 60 nicht überschreiten. Dabei spielt es keine Rolle ob 60 Jahre je eine Periode oder 2 Jahre je 30 Perioden. Wenn 60 überschritten wird oder die Multiplikation der beiden Werte 0 ergeben soll nichts angezeigt werden. Gibt es eine Möglichkeit um so eine Tabelle zu erstellen oder muss ich mit verschachtelten WENN-Funktionen arbeiten.

[img]
Dateiupload bitte im Forum! So geht es: Klick mich!
]

Grüße und danke im vorraus Tarkrai

[schauan]

Hallöchen,

im Prinzip so:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B
1	Jahre	5
2	Periode	4
3
4
5	1	1
6	1	2
7	1	3
8	1	4
9	2	1
10	2	2
11	2	3
12	2	4
13	3	1

Zelle	Formel
A5	=(ZEILE()-4-B5)/$B$2+1
B5	=WENNFEHLER(REST(ZEILE()-4;$B$2)/REST(ZEILE()-4;$B$2)*REST(ZEILE()-4;$B$2);$B$2)
A6	=(ZEILE()-4-B6)/$B$2+1
B6	=WENNFEHLER(REST(ZEILE()-4;$B$2)/REST(ZEILE()-4;$B$2)*REST(ZEILE()-4;$B$2);$B$2)
A7	=(ZEILE()-4-B7)/$B$2+1
B7	=WENNFEHLER(REST(ZEILE()-4;$B$2)/REST(ZEILE()-4;$B$2)*REST(ZEILE()-4;$B$2);$B$2)
A8	=(ZEILE()-4-B8)/$B$2+1
B8	=WENNFEHLER(REST(ZEILE()-4;$B$2)/REST(ZEILE()-4;$B$2)*REST(ZEILE()-4;$B$2);$B$2)
A9	=(ZEILE()-4-B9)/$B$2+1
B9	=WENNFEHLER(REST(ZEILE()-4;$B$2)/REST(ZEILE()-4;$B$2)*REST(ZEILE()-4;$B$2);$B$2)
A10	=(ZEILE()-4-B10)/$B$2+1
B10	=WENNFEHLER(REST(ZEILE()-4;$B$2)/REST(ZEILE()-4;$B$2)*REST(ZEILE()-4;$B$2);$B$2)
A11	=(ZEILE()-4-B11)/$B$2+1
B11	=WENNFEHLER(REST(ZEILE()-4;$B$2)/REST(ZEILE()-4;$B$2)*REST(ZEILE()-4;$B$2);$B$2)
A12	=(ZEILE()-4-B12)/$B$2+1
B12	=WENNFEHLER(REST(ZEILE()-4;$B$2)/REST(ZEILE()-4;$B$2)*REST(ZEILE()-4;$B$2);$B$2)
A13	=(ZEILE()-4-B13)/$B$2+1
B13	=WENNFEHLER(REST(ZEILE()-4;$B$2)/REST(ZEILE()-4;$B$2)*REST(ZEILE()-4;$B$2);$B$2)

Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg

Allerdings kann die Formel nicht wissen, wie viel Zeilen 5 Jahre sind ... Meinst Du damit, dass sich die Perioden entsprechend oft wiederholen? Bei 4 Perioden und 5 Jahren also 20 Zeilen?

Die Formel würde sich auch nicht ohne Hilfe bei mehr Jahren auf mehr Zeilen verteilen. Entweder müsste man die Formel bis ganz unten ausfüllen und per WENN und entsprechender Berechnung B1*B2+Startzeile nach der n. Zeile nur noch "" ausgeben, oder per VBA die Formeln bei Eingabe der Jahre und Perioden über den nötigen Bereich verteilen.

[shift-del]

Oder mit Hilfsspalten.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E
1	Jahre	5
2	Perioden pro Jahr	4
3
4	Jahr	Periode
5	1	1		1	WAHR
6	1	2		2	WAHR
7	1	3		3	WAHR
8	1	4		4	WAHR
9	2	1		5	WAHR
10	2	2		6	WAHR
11	2	3		7	WAHR
12	2	4		8	WAHR
13	3	1		9	WAHR
14	3	2		10	WAHR
15	3	3		11	WAHR
16	3	4		12	WAHR
17	4	1		13	WAHR
18	4	2		14	WAHR
19	4	3		15	WAHR
20	4	4		16	WAHR
21	5	1		17	WAHR
22	5	2		18	WAHR
23	5	3		19	WAHR
24	5	4		20	WAHR
25				21	FALSCH

Zelle	Formel
A5	=WENN(E5;QUOTIENT(D5-1;$B$2)+1;"")
B5	=WENN(E5;REST(D5-1;$B$2)+1;"")
D5	=ZEILEN(A$5:A5)
E5	=D5<=$B$1*$B$2

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Die Grenze von 60 legt man dadurch fest dass die Formeln nur 59x nach unten kopiert werden - also bis Zeile 64.

[RPP63]

Hallo auch von mir!
Formeln aus Zeile 5 bis Zeile 65 herunterziehen:
(die Formel aus Spalte B habe ich von André "geklaut")

	A	B
1	Jahre	3
2	Perioden/Jahr	5
3	Jahr	Periode
4
5	1	1
6	1	2
7	1	3
8	1	4
9	1	5
10	2	1
11	2	2
12	2	3
13	2	4
14	2	5
15	3	1
16	3	2
17	3	3
18	3	4
19	3	5
20
21
22

Formeln der Tabelle

Zelle Formel A5 =WENN(ZEILE(A1)>B$1*B$2;"";WENN(REST(ZEILE(A1);B$2)=1;A4+1;A4)) B5 =WENN(ZEILE(A1)>B$1*B$2;"";WENNFEHLER(REST(ZEILE(A1);B$2)/REST(ZEILE(A1);$B$2)*REST(ZEILE(A1);B$2);B$2))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

[RPP63]

Die Formel in B5 nochmal gekürzt:
=WENN(ZEILE(A1)>B$1*B$2;"";REST(ZEILE(A1)-1;$B$2)+1)

Gruß Ralf

[Tarkrai]

Erstmal vielen Dank für die ganzen Antworten, habe anhand dieser ein wenig rumprobiert scheiter jedoch an was auch immer, an sich funktionieren die Formel aber irgendwie nicht richtig oder ich setz sie einfach falsch ein   



Tabelle1

	A	B	C
1
2
3	Laufzeit in Jahren	1
4	Zahlungen pro Jahr	60
5
6
7	Jahr	Periode
8	Jahr	3	1
9	Jahr	4	2
10	Jahr	5	3
11	Jahr	6	4
12	Jahr	7	5
13	Jahr	8	6
14	Jahr	9	7
15	Jahr	10	8
16	Jahr	11	9
17	Jahr	12	10
18	Jahr	13	11
19	Jahr	14	12
20	Jahr	15	13
21	Jahr	16	14
22	Jahr	17	15
23	Jahr	18	16
24	Jahr	19	17
25	Jahr	20	18
26	Jahr	21	19
27	Jahr	22	20
28	Jahr	23	21
29	Jahr	24	22
30	Jahr	25	23
31	Jahr	26	24
32	Jahr	27	25
33	Jahr	28	26
34	Jahr	29	27
35	Jahr	30	28
36	Jahr	31	29
37	Jahr	32	30
38	Jahr	33	31
39	Jahr	34	32
40	Jahr	35	33
41	Jahr	36	34
42	Jahr	37	35
43	Jahr	38	36
44	Jahr	39	37
45	Jahr	40	38
46	Jahr	41	39
47	Jahr	42	40
48	Jahr	43	41
49	Jahr	44	42
50	Jahr	45	43
51	Jahr	46	44
52	Jahr	47	45
53	Jahr	48	46
54	Jahr	49	47
55	Jahr	50	48
56	Jahr	51	49
57	Jahr	52	50
58	Jahr	53	51
59	Jahr	54	52
60	Jahr	55	53
61	Jahr	56	54
62	Jahr	57	55
63	Jahr	58	56
64	Jahr	59	57
65	Jahr	60	58
66			59
67			60

Formeln der Tabelle

Zelle Formel B5 =WENN($B$3*$B$4<=0;"Maximal 60 Perioden";WENN($B$4*$B$3>60;"Maximal 60 Perioden";WENN(ZEILE(A3)>B$3*B$4;"";""))) A8 =WENN($B$3*$B$4<=0;" ";WENN($B$4*$B$3>60;" ";WENN(ZEILE(A3)>B$3*B$4;"";WENN(REST(ZEILE(A3);B$4)=1;A7+1;A7)))) B8 =WENN($B$3*$B$4<=0;" ";WENN($B$4*$B$3>60;" ";WENN(ZEILE(A3)>B$3*B$4;"";WENNFEHLER(REST(ZEILE(A3);B$4)/REST(ZEILE(A3);$B$4)*REST(ZEILE(A3);B$4);B$4))))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

[RPP63]

Moin!
1. Deine Formel in B5 ist seltsam. Sowas würde ich mit Daten, Datenüberprüfung, Zulassen, benutzerdefiniert lösen:

	B
3	5
4	12

Datengültigkeit der Tabelle

Zelle Zulassen Daten Eingabe 1 Eingabe 2 B3 Benutzerdefiniert =UND(B$3>=0;B$4>=0;B$3*B$4<=60) B4 Benutzerdefiniert =UND(B$3>=0;B$4>=0;B$3*B$4<=60)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

2. Du darfst in den Formeln Zeile(A1) nicht verändern! Es handelt sich hier um eine Zählvariable, die mit 1 beginnt und beim kopieren der Formel hochzählt.

3. Warum "verschlimmbesserst" Du die funktionierenden Formeln?

Gruß Ralf

[RPP63]

4. Im übrigen ist die Leerzeile (hier Zeile 8) über der Auflistung unbedingt notwendig, Du kannst sie ausblenden, wenn sie Dich stört.
Ich habe die Formeln mal an Dein obiges Beispiel angepasst:

	A	B
1
2
3	LFZ	3
4	Zahlg/Jahr	5
5
6
7	Jahr	Periode
8
9	1	1
10	1	2
11	1	3
12	1	4
13	1	5
14	2	1
15	2	2

Formeln der Tabelle

Zelle Formel A9 =WENN(ZEILE(A1)>B$3*B$4;"";WENN(REST(ZEILE(A1);B$4)=1;A8+1;A8)) B9 =WENN(ZEILE(A1)>B$3*B$4;"";REST(ZEILE(A1)-1;$B$4)+1)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8


Gruß Ralf

[Tarkrai]

Danke für die Erklärung, so wirklich habe ich die Funktion ehrlich gesagt noch nicht Verstanden, ich habe immer noch den "Fehler" wenn ich nur 1 Periode habe das die Jahre nicht mehr gezählt werden sondern 0 angezeigt wird. Ich hab jetzt wieder rumprobiert verstehe die Funktion aber leider immer noch nicht, wäre nett wenn mir das jemand erklären könnte danke schonmal 

[RPP63]

Hi!

 ich habe immer noch den "Fehler" wenn ich nur 1 Periode habe das die Jahre nicht mehr gezählt werden sondern 0 angezeigt wird. 

Gut, der Sonderfall eine Periode im Jahr war noch nicht in der Formel abgebildet:
(Zeile 8 ist hier ausgeblendet!)

	A	B
3	LFZ	5
4	Zahlg/Jahr	1
5
6
7	Jahr	Periode
9	1	1
10	2	1
11	3	1
12	4	1
13	5	1

Formeln der Tabelle

Zelle Formel A9 =WENN(ZEILE(A1)>B$3*B$4;"";WENN(B$4=1;ZEILE(A1);WENN(REST(ZEILE(A1);B$4)=1;A8+1;A8))) B9 =WENN(ZEILE(A1)>B$3*B$4;"";REST(ZEILE(A1)-1;$B$4)+1)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Zur "Erklärung":
1. Nutze zum Verständnis der Formeln die Excel-Hilfe zu Rest(), was Zeile(A1) bedeutet habe ich Dir oben bereits geschrieben.
2. Im Register Formeln gibt es die Formelauswertung, die solltest Du mal durchsteppen, um die Auswertung nachvollziehen zu können.

Gruß Ralf