Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Tabelle automatisch erstellen
#1
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
Antworten Top
#2
Hallöchen,

im Prinzip so:
Arbeitsblatt mit dem Namen 'Tabelle1'
 AB
1Jahre5
2Periode4
3  
4  
511
612
713
814
921
1022
1123
1224
1331

ZelleFormel
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.
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
[-] Folgende(r) 1 Nutzer sagt Danke an schauan für diesen Beitrag:
  • Tarkrai
Antworten Top
#3
Oder mit Hilfsspalten.
Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCDE
1Jahre5   
2Perioden pro Jahr4   
3     
4JahrPeriode   
511 1WAHR
612 2WAHR
713 3WAHR
814 4WAHR
921 5WAHR
1022 6WAHR
1123 7WAHR
1224 8WAHR
1331 9WAHR
1432 10WAHR
1533 11WAHR
1634 12WAHR
1741 13WAHR
1842 14WAHR
1943 15WAHR
2044 16WAHR
2151 17WAHR
2252 18WAHR
2353 19WAHR
2454 20WAHR
25   21FALSCH

ZelleFormel
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.
Wir sehen uns!
... Detlef

Meine Beiträge können Ironie oder Sarkasmus enthalten.

[-] Folgende(r) 1 Nutzer sagt Danke an shift-del für diesen Beitrag:
  • Tarkrai
Antworten Top
#4
Hallo auch von mir!
Formeln aus Zeile 5 bis Zeile 65 herunterziehen:
(die Formel aus Spalte B habe ich von André "geklaut") Angel

AB
1Jahre3
2Perioden/Jahr5
3JahrPeriode
4
511
612
713
814
915
1021
1122
1223
1324
1425
1531
1632
1733
1834
1935
20
21
22
Formeln der Tabelle
ZelleFormel
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
Gib einem Mann einen Fisch und du ernährst ihn für einen Tag. 
Lehre einen Mann zu fischen und du ernährst ihn für sein Leben. (Konfuzius)
[-] Folgende(r) 1 Nutzer sagt Danke an RPP63 für diesen Beitrag:
  • Tarkrai
Antworten Top
#5
Die Formel in B5 nochmal gekürzt:
=WENN(ZEILE(A1)>B$1*B$2;"";REST(ZEILE(A1)-1;$B$2)+1)

Gruß Ralf
Gib einem Mann einen Fisch und du ernährst ihn für einen Tag. 
Lehre einen Mann zu fischen und du ernährst ihn für sein Leben. (Konfuzius)
[-] Folgende(r) 1 Nutzer sagt Danke an RPP63 für diesen Beitrag:
  • Tarkrai
Antworten Top
#6
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   Blush



Tabelle1

ABC
1
2
3Laufzeit in Jahren1
4Zahlungen pro Jahr60
5
6
7JahrPeriode
8Jahr31
9Jahr42
10Jahr53
11Jahr64
12Jahr75
13Jahr86
14Jahr97
15Jahr108
16Jahr119
17Jahr1210
18Jahr1311
19Jahr1412
20Jahr1513
21Jahr1614
22Jahr1715
23Jahr1816
24Jahr1917
25Jahr2018
26Jahr2119
27Jahr2220
28Jahr2321
29Jahr2422
30Jahr2523
31Jahr2624
32Jahr2725
33Jahr2826
34Jahr2927
35Jahr3028
36Jahr3129
37Jahr3230
38Jahr3331
39Jahr3432
40Jahr3533
41Jahr3634
42Jahr3735
43Jahr3836
44Jahr3937
45Jahr4038
46Jahr4139
47Jahr4240
48Jahr4341
49Jahr4442
50Jahr4543
51Jahr4644
52Jahr4745
53Jahr4846
54Jahr4947
55Jahr5048
56Jahr5149
57Jahr5250
58Jahr5351
59Jahr5452
60Jahr5553
61Jahr5654
62Jahr5755
63Jahr5856
64Jahr5957
65Jahr6058
6659
6760

Formeln der Tabelle
ZelleFormel
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

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

B
35
412
Datengültigkeit der Tabelle
ZelleZulassenDatenEingabe 1Eingabe 2
B3Benutzerdefiniert=UND(B$3>=0;B$4>=0;B$3*B$4<=60)
B4Benutzerdefiniert=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
Gib einem Mann einen Fisch und du ernährst ihn für einen Tag. 
Lehre einen Mann zu fischen und du ernährst ihn für sein Leben. (Konfuzius)
[-] Folgende(r) 1 Nutzer sagt Danke an RPP63 für diesen Beitrag:
  • Tarkrai
Antworten Top
#8
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:

AB
1
2
3LFZ3
4Zahlg/Jahr5
5
6
7JahrPeriode
8
911
1012
1113
1214
1315
1421
1522
Formeln der Tabelle
ZelleFormel
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
Gib einem Mann einen Fisch und du ernährst ihn für einen Tag. 
Lehre einen Mann zu fischen und du ernährst ihn für sein Leben. (Konfuzius)
[-] Folgende(r) 1 Nutzer sagt Danke an RPP63 für diesen Beitrag:
  • Tarkrai
Antworten Top
#9
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 Blush
Antworten Top
#10
Hi!
Zitat: 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!)

AB
3LFZ5
4Zahlg/Jahr1
5
6
7JahrPeriode
911
1021
1131
1241
1351
Formeln der Tabelle
ZelleFormel
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
Gib einem Mann einen Fisch und du ernährst ihn für einen Tag. 
Lehre einen Mann zu fischen und du ernährst ihn für sein Leben. (Konfuzius)
[-] Folgende(r) 1 Nutzer sagt Danke an RPP63 für diesen Beitrag:
  • Tarkrai
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste