Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1704to1708
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Stufenplan einrichten

Stufenplan einrichten
12.08.2019 16:53:28
F.
Hallo Zusammen,
für eine Tabelle (so eine Art Stufenplan) bräuchte ich Euere Hilfe. Wenn ich, wie jetzt eingestellt (10 Jahre) eine geringere Laufzeit z. B. nur 5 Jahre habe, dann bleiben ja die restlichen Zeilen angefüllt mit Werten stehen, so, dass es nicht schön aussieht und aber auch ein zusätzliches Durcheinander ergibt. Die übrigen Zeilen sollten eben mit einem Makro ausgeblendet werden. Sollte die Laufzeit aber 20 oder gar 30 Jahre betragen, dann sollten die Zeilen dementsprechend ergänzt werden. Die Schlusszeile mit den aufgelaufenen Werten sollte natürlich immer mit eingeblendet werden. Schon mal vielen Dank im voraus.
https://www.herber.de/bbs/user/131399.xlsm
Viele Grüße
Frieder

26
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Stufenplan einrichten
14.08.2019 16:10:49
F.
Hallo Zusammen,
seit ein paar Tagen warte ich auf eine Antwort von meinem Thread. Leider hat sich vom Forum noch niemand an diese Aufgabe herangetraut. Wenn Ihr der Meinung seid, dass das eine Auftragsprogrammierung ist, soll man mir das doch auch Bitteschön dementsprechend mitteilen. Ich bin gerne bereit, dafür auch Geld auszugeben.
Viele Grüße
Frieder
AW: Stufenplan einrichten
14.08.2019 19:40:25
ChrisL
Hi
Ich lasse offen.
In der Freizeit Finanzplanung zu studieren, ist nix. Da habe ich besser Hobbys wie z.B. Excel :)
Man müsste sich mit der Materie beschäftigen, um deine Frage zu verstehen.
Den Zusammenhang zu erkennen ist mir persönlich wichtig, aber manchmal hilft es auch einfach eine auf das Problem reduzierte Excel-Frage zu stellen.
cu
Chris
Anzeige
AW: Stufenplan einrichten
14.08.2019 20:03:02
F.
Hallo Chris,
es ist schön zu sehen, dass sich doch noch jemand aus dem Forum meldet. Ja Chris, du brauchst deswegen kein Studium über Finanzmathematik anfangen. Denn das Problem um das es hier geht ist im Prinzip ganz einfach, wenn man weiß wie es geht und das setze ich ja von einem Excel Experten voraus. Hier geht es ja ganz einfach darum, eine Tabelle "Dynamisch" zu gestalten. Ist die Laufzeit Z. B. 5 Jahre, werden für die Auswertung der Ergebnisse (Guthaben, Zinsen etc.) 5 Zeilen + die Ergebniszeile wo die aufgelaufenen Werte summiert werden. Ist aber die Laufzeit höher, sollten dann natürlich die Zeilen der Laufzeit nach angepasst werden. Wird die Laufzeit bei der nächsten Berechnung wieder reduziert, sollten sich natürlich die Ausgabezeilen, auch wieder auf die Laufzeit Länge reduzieren.
https://www.herber.de/bbs/user/131399.xlsm
Viele Grüße
Frieder
Anzeige
mir juckts in den Fingern :-), aber....
17.08.2019 09:15:05
Oberschlumpf
Hi Frieder,
...leider habe ich z Zt kein Excel zur Hand.
Mein PC ist geschrottet, und ich hab "nur" ein Tablet ohne Excel.
Anhand deiner Beschreibungen würde ich dir zustimmen, dass man nix studieren muss, um eine Lösung für dich zu erarbeiten.
Wenn in Zelle A1 die Laufzeit (5, 10, 20, 40, usw Jahre) eingegeben wird, muss man eben nur Zelle A1 bei, bzw nach der Eingabe überwachen
Dies geschieht im Worksheet_Change-Ereignis.

If Target.Address = "$A$1" Then
sbAusEinblenden Target.Value 'es wird ein Unterprogramm aufgerufen + der Wert aus A1 ü _
bergeben
End If
Im Unterprogramm sbAusEinblenden wird nun der Wert aus A1 ausgewertet, und entweder werden nun einfach Zellen aus- oder eingeblendet.

Sub sbAusEinblenden(ByVal WertA1 As Integer)
'in meiner gedachten Tabelle enthält Zeile 3 die Überschriften.
'die Daten beginnen in Zeile 4.
'es werden die Spalten A bis D genutzt
'jedes mal, wenn der Wert in A1 geändert wird, werden alle Inhalte in den Datenzeilen gelöscht
'die letzte, benutzte Zeile MUSS die Ergebniszeile sein!
Range("A4:D" & Cells(Rows.Count, 1).End(xlUp).Row - 1).Value = ""
'alle Zeilen ab Zeile 4 werden eingeblendet
Rows("4:" & Cells(Rows.Count, 1).End(xlUp).Row).EntireRow.Hidden = False
'jetzt werden, abhängig von Wert in A1, die übrigen Zeilen ausgeblendet.
Rows("4" + WertA1 & ":" & Cells(Rows.Count, 1).End(xlUp).Row).EntireRow.Hidden = True
End Sub
So, zum vielleicht besseren Verständis hier ein Bild, wie die zu meinem Code passende Testtabelle aussehen muss:
Userbild
Uih uihh :-) ich bin neugierig!
Das ist das erste mal, das ich einen Code erstellt habe ganz ohne Excel!
Wenn es funktioniert (mit der Testtabelle!) freut es mich.
Du müsstest den Code dann nur an deine Tabelle anpassen.
Dabei kann ich dir dann aber leider nicht helfen, da ich kein Excel habe.
Hilfts denn?
Ciao
Thorsten
Anzeige
AW: mir juckts in den Fingern :-), aber....
17.08.2019 12:32:25
F.
Hallo Thorsten,
schön, dass Du dir soviel Mühe machst. Ich habe mal versuchsweise diese Daten in eine ganz leere Tabelle eingebaut und beim Aufruf der Sub gab es mehrere Fehlermeldungen von "400" bis "Laufzeitfehler 424 Objekt erforderlich". Da mir ja in VBA auch der dementsprechende "Durchblick" fehlt und ich übrigens bereits den Inhalt der Zeilen mit einer Formel ausblenden kann, gäbe es nur noch ein Problem und das wäre die Ergebniszeile immer an die letzte gefüllte Zeile heran zu bringen. Mit einer Wenn Abfrage in meinem Sheet müsste das doch irgendwie möglich sein. Dieses muss wohl mit VBA aus dem Programm heraus geschehen, in dem man über eine "Wenn Abfrage" (If Laufzeit = ?) die benötigten und bereits bekannten Formeln in die Zellen einträgt, je nach Laufzeit mittels For / Next Schleife das ganze anpasst. Schwieriger wird es wohl werden, wenn sich die Laufzeit verringert und die Zeilen von der Berechnung vorher, wieder gelöscht werden müssen. Die Auswertungszeile am Ende muss natürlich auch immer wieder dementsprechend angepasst werden.
Viele Grüße
Frieder
Anzeige
hätte ja funktionieren können,...
17.08.2019 13:59:03
Oberschlumpf
Hi Frieder,
aber wie ja geschrieben habe, kann ich dir leider nicht weiter helfen.
Trotzdem weiter viel Erfolg!
Ich weiß, dass es funktioniert. Nur ohne Ecxel kann ich nicht testen-.
Ciao
Thorsten
AW: hätte ja funktionieren können,...
17.08.2019 14:56:02
F.
Hallo Thorsten,
schade, aber vielleicht kannst Du mir mit einem Hinweis weiter helfen. Wie bringe ich VBA dazu, _ die Formeln mittels VBA in die Excel Zellen zu kopieren. Wenn ich z. B. diese Formel:

Range("d20") = "=Wenn(B21>$B$16;0;J20)"
in die Tabelle kopieren will, bringt er mir die Fehlermeldung: "Laufzeitfehler 1004 - Anwendungs oder Objektdefinierter Fehler". Natürlich ist es schwierig ohne Excel da Fehler zu finden. Aber vom Wissen her, könnte man vielleicht doch einiges vielleicht ausschließen!
Viele Grüße
Frieder
Anzeige
AW: hätte ja funktionieren können,...
17.08.2019 15:15:55
Werner
Hallo Frieder,
so:
Range("d20").FormulaLocal = "=Wenn(B21>$B$16;0;J20)"
Gruß Werner
AW: hätte ja funktionieren können,...
17.08.2019 15:57:14
F.
Hallo Werner,
Danke, so funktioniert die Formel. Wenn ich Dich gerade dran habe würde es Dir etwas ausmachen, eine "Wenn Abfrage" so zu gestalten, dass die Formel nicht nur für eine Zelle bzw. Zeile rechnet, sondern abhängig von einer Zahl die in der "Textbox4" steht, für die Anzahl der Zeilen per For/Next Schleife, oder was auch immer, die Formeln dann automatisch in die nächsten Zellen kopiert. Eine Frage hätte ich noch, ob das überhaupt in VBA geht, dass man mehrere Formeln wo für verschiedene Zellen in einer Zeile gebraucht werden, jeweils in einem "Rutsch" zusammen kopieren kann.(Formeln sind alle ähnlicher Art wie diese).
Viele Grüße
Frieder
Anzeige
AW: hätte ja funktionieren können,...
17.08.2019 16:07:44
Werner
Hallo Frieder,
so:
'Blattname anpassen
With Worksheets("Tabelle1")
Range("D20:D" & .TextBox4).FormulaLocal = "=Wenn(B21>$B$16;0;J20)"
End With
Verschiedene Formeln kannst du nicht auf einmal in verschiedene Bereiche kopieren/schreiben.
Gruß Werner
AW: hätte ja funktionieren können,...
17.08.2019 16:39:02
F.
Hallo Werner,
bei der Formel stimmt irgend etwas nicht. Die trägt die Formeln nach Oben ein. Bei der 2. geänderten Formel gab es auch eine Zirkelbezugswarnung, vielleicht hängt das auch mit dem zusammen. Ich muss nur jetzt schnell weg und melde mich später wieder. Blöde Frage, wo trägt man denn Deine Formel genau ein? Ich habe sie über dem 1. Optionsfeld eingetragen!!!
Viele Grüße
Frieder
Anzeige
stop, was wichtiges vergessen...
17.08.2019 16:09:19
Werner
Hallo Frieder,
...vor der Range

muß
noch ein Punkt hin.
With Worksheets("Tabelle1")
.Range("D20:D" & .TextBox4).FormulaLocal = "=Wenn(B21>$B$16;0;J20)"
End With
Gruß Werner
und noch was falsch....
17.08.2019 16:12:51
Werner
Hallo Frieder,
...scheint heute nicht mein Tag.
'Blattname anpassen
With Worksheets("Tabelle1")
.Range("D20:D" & 20 + .TextBox1).FormulaLocal = "=Wenn(B21>$B$16;0;J20)"
End With
Gruß Werner
AW: und noch was falsch....
17.08.2019 16:22:45
Daniel
HI
ich würde ja empfehlen, die Formel nicht in deutsch und mit A1-Adressen zu schreiben, sondern in englisch und mit R1C1 bzw Z1S1-Adressen.
ersters hat den Vorteil, dass der Code dann unabhängig von der jeweiligen Spracheversion funktioniert, zweiters hat den Vorteil, dass sich relative Zellbezüge besser, dh auch wirklich relativ beschreiben lassen, so dass der Formeltext nicht geändert werden muss, wenn sich die Zielzelle verschiebt.
außerdem wird dann deutlicher sichtbar, wann eine Formel in alle Zellen gleichzeitig geschrieben wird, nämlich dann wenn in R1C1 der Formeltext gleich ist.
Klar ist das mit R1C1 ungewohnt, aber es erleichtert in den meisten Fällen die Programmierung und wenn man ".Range("D20:D" & 20 + .TextBox1)" versteht, dann versteht man auch R1C1-Addressierung.
dh ich würde das ganze so schreiben:
Worksheets("Tabelle1").Range("D20").Resize(CLng(Textbox4.Text), 1).FormulaR1C1 = "=IF(R[1]C2>R16C2;0;RC10)"
Gruß Daniel
Anzeige
AW: und noch was falsch....
17.08.2019 22:26:35
F.
Hallo Daniel,
versuchsweise habe ich auch mal Deine Formel ausprobiert, die bringt aber auch Fehlermeldungen "400" und "Laufzeitfehler 1004 - Anwendungs- oder objektdefinierter Fehler". Problem kann natürlich auch bei mir liegen, falls ich die Formel falsch eingebaut habe. Aber egal wo ich sie platziert hatte, die "1004" war immer dabei. Trotzdem Danke.
Viele Grüße
Frieder
AW: und noch was falsch....
18.08.2019 00:03:18
Daniel
sorry, ich hatte vergessen, dass du in der englischen version natürlich auch die englischen Parametertrennzeichen verwenden musst, dh Komma statt semikolon.
Allerdings kannst du dir die .FormulaR1C1-Schreibweise auch selber einfach mit Hilfe des Recorders ermitteln oder indem du die Formel normal von Hand in die Zelle schreibst und dann im Direktfenster die Formel in der gewünschten Schreibweise anzeigen lässt mit:
?Selection.FormulaLocal
?Selection.FormulaR1C1

das sollte man eigentlich immer tun und nicht einfach alles ungeprüft kopieren, was man so im Internet findet.
Gruß Daniel
Anzeige
AW: Formel Korrektur
18.08.2019 12:06:05
F.
Hallo Daniel,
probiere gerade nochmal Deine Formel aus und nachdem ich die Korrekturen vorgenommen habe, _ funktioniert die Formel auch. Nur beschreibt sie die Formel falsch. Anstatt

=Wenn($B21>$B$16;0;$J$20)
trägt sie die Formel so ein

=WENN($B22>$B$16;0;$J$20)
. Also, von einer Zeile tiefer. Kann man das irgendwie bzw. irgendwo einstellen? Habe mehrere Möglichkeiten ausprobiert, keine hat aber zum Erfolg geführt.
Viele Grüße
Frieder
AW: Formel Korrektur
18.08.2019 12:28:25
Daniel
Hi
was hast du denn ausprobiert?
in der R1C1-Addressierungsart zwischen absoluten und relativen Zellbezügen unterschieden.
bei Absoluten Zellbezügen(die mit den $) kommt direkt nach dem R bzw C die Zeilen- oder Spaltennummer der Zelle, dh R1C1 entspricht $A$1
um in R1C1 einen relativen Zellbezug zu beschreiben, setzt man den Versatzwert (ausgehend von der Zelle, in welcher die Formel steht) in Klammern.
Dabei bedeutet ein negativer Wert einen Versatz nach oben (R) oder Links (C) und ein positiver Wert einen Versatz nach unten oder rechts.
dh wenn du einen Bezug auf die Zeile oberhalb haben willst, wäre
R(-1) ein Bezug auf die Zeile oberhalb
R(1) ein Bezug auf die Zeile unterhalb
R(0) ein Bezug auf die gleiche Zeile
gleiches für den Spaltenbezug:
C(-1) ein Bezug auf die Spalte links der Zelle mit der Formel
C(1) ein Bezug auf die Spalte rechts der Zelle mit der Formel
C(0) ein Bezug auf die gleiche Spalte
wobei man das (0) auch weglassen kann und nur R oder C schreibt
und so setzt du dann deinen Zellbezug aus einer R- einer C-Komponente zusammen.
wenn du das verstanden hast, solltest du deine Formel auch selbstständig zusammenstellen können, ohne dass du sie bei mir guttenbergen musst.
ansonsten kannst du dir auch immer die Formel wie du sie haben willst erstmal von Hand in die Zelle schreiben und dir dann im Direktfenster den Formeltext in der R1C1-Schreibweise anzeigen lassen, von wo du ihn dann in den Code kopierst (im Direktfenster mit dem Befehl: ?Selection.FormulaR1C1 )
wobei ich dir dann emfpfehlen würde, soviele Zellbezüge wie möglich absolut zu setzen und nur die relativ zu machen, die unbedingt relativ sein müssen. Dann wird die Formel in R1C1 kürzer und übersichtlicher.
Gruß Daniel
AW: Formel Korrektur
18.08.2019 13:06:57
F.
Hallo Daniel,
zuerst Danke für Deine schnelle Antwort. In der kurzen Zeit soviel schreiben!!! Ja, soweit habe ich es schon verstanden. Aber eines ist mir immer noch nicht ganz klar, warum wird die Formel mit =IF(R[1]C2 eingetragen, wenn ich z. B. die Formel in "B21" haben will? Mit "=Wenn(B21)" weiß ich aber gleich, in welche Zelle die Formel hingehört. Das verwirrende ist eben die Aussage, dass "R1C1 $A$1" entspricht. Aber ich werde schon noch dahinter kommen, Dank Deiner guten Erklärung wird mir irgendwann auch das berühmte Lichtlein aufgehen.
Viele Grüße
Frieder
AW: Formel Korrektur
18.08.2019 13:56:10
Daniel
Hi
wenn du Fragen zu Formeln hast, solltest du immer zu drei Dingen Angaben machen:
1. in welche Zelle soll die Formel geschrieben werden
2. auf welche Zellen sollen die Zellbezüge in der Formel laufen
3. Soll die Formel auch noch in weitere Zellen übernommen werden und wenn ja, wie sollen sich dann die Zellbezüge verändern, sollen diese gleich bleiben oder sollen diese dann "mitwandern"
wenn du diese drei Fragen beantwortest, kann ich dir auch sagen, wie die Formel korrekt lauten muss.
Gruß Daniel
AW: und noch was falsch....
17.08.2019 21:34:08
F.
Hallo Werner,
Formel funktioniert, Zirkelbezug ist auch weg, musste eine kleine Korrektur bei der Range machen und schon war alles - was die Formel betrifft - in Ordnung. Nur bin ich halt wieder bei dem Stand angelangt, wo ich vorher ja auch schon war, dass die Tabelle sich halt nicht Dynamisch entwickeln kann. Das heißt, dass immer alle Zeilen stehen bleiben, ob die Berechnung nur für ein Jahr oder gar für 30 Jahre erfolgt. Bei https://www.zinsen-berechnen.de/sparrechner.php kann man das gut verfolgen, wenn man die passenden Daten eingibt. Das verändern der Laufzeit reicht dafür schon aus. Auf jeden Fall mal Danke für die Vorschläge.
Viele Grüße
Frieder
AW: und noch was falsch....
18.08.2019 07:50:43
Werner
Hallo Frieder,
ich denke ich habe eine Lösung für dein Problem. Ich kann im Moment aber leider nichts hochladen. Aus diesem Grund muß ich dich auf Morgen vertrösten.
Der Beitrag ist jetzt aber schon einige Tage alt. Kann sein, dass er demnächst ins Archiv verschoben wird.
In diesem Fall würde ich einen neuen Beitrag eröffnen mit dem Betreff @Frieder
Dann mußt du halt im Forum danach schauen.
Gruß Werner
AW: Fundsache!
19.08.2019 13:07:50
F.
Hallo Werner,
bist Du schon fündig geworden?
Viele Grüße
Frieder
AW: Stufenplan einrichten
17.08.2019 11:27:40
Peter
Hallo Frieder,
ich habe mir erlaubt, Deine Datei etwas zu verändern: Den Makro habe ich ergänzt, damit der Inhalt von Textfeld4 in die Zelle B17 geschrieben wird. Die Umwandlung in einen Wert steht in B16. Jetzt mussten nur noch einige Formeln ergänzt werden, und schon ist hier Dein Stufenplan.
https://www.herber.de/bbs/user/131468.xlsm
Wenn die Einträge in B16/B17 stören, kannst Du sie mit Formatbefehl ";;;" ausblenden. Bitte mal ausprobieren, Rückmeldung wäre schön.
Mit freundlichem Gruß
Peter Kloßek
AW: Stufenplan einrichten
17.08.2019 12:15:22
F.
Hallo Peter,
Danke für Deine Hilfe. Habe das Ausblenden der nicht relevanten Zeilen bereits selber hinbekommen, nach dem selben Muster wie Du das gemacht hast. Nur sind alle Zellen mit "0" ausgeblendet. Was an und für sich mein Wunsch wäre, dass alle nicht benötigten Zeilen immer leer sind und nur die Ergebnis- und Auswertungszeile mit den auf summierten Werten angezeigt werden. Dieses muss wohl mit VBA aus dem Programm heraus geschehen, in dem man über eine "Wenn Abfrage" (If Laufzeit = ?) die benötigten und bereits bekannten Formeln in die Zellen einträgt, je nach Laufzeit mittels For / Next Schleife das ganze anpasst. Schwieriger wird es wohl werden, wenn sich die Laufzeit verringert und die Zeilen von der Berechnung vorher, wieder gelöscht werden müssen. Die Auswertungszeile am Ende muss natürlich auch immer wieder dementsprechend angepasst werden.
Viele Grüße
Frieder
AW: Stufenplan einrichten
17.08.2019 16:09:46
Peter
Hallo Frieder,
das komplette Ausblenden ist möglich, indem Du in den "Wenn"-Formeln anstelle von '=0' '=""' eingibst. Dabei müssen auch diejenigen Formeln berücksichtigt werden, die eine Berechnung auslösen (bisher ergibt sich hier der "0-Wert" von selbst).
Mit freundlichem Gruß
Peter Kloßek

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige