Frage von Cappa90, 60

Kann mir jemand beim Excelmakro helfen?

Hallo ich bin leider nicht sonderlich fit, wenn es heißt ein Makro in Excel einzubinden. Aber vielleicht gibt es ja einige, die mir mit wenig Fleiß bei meinem Problem helfen können. Und zwar habe ich folgendes Problem, welches ich mit einem Makro lösen will:

Ich habe eine Liste, die aktuelle Mindestlaufzeiten angibt (Vorläufiges Vertragsende), welche jedoch in der Regel mit Automatischen Vertragsverlängerungen versehen sind sowie über verschiedene Kündigungsfristen verfügen. Die Parameter sind auch alle in Excel angegeben. Nun möchte ich, dass sich diese Liste eigenständig aktualisiert.

Beispielweise: Ein Vertrag läuft am 30.6.2017(A) aus, hat eine Kündigungsfrist von 6 Monaten(B), bei einer automatischen Verlängerung von einem Jahr(C). Dann wäre das letzte Kündigungsdatum der 31.12.2016(D). Nun soll es sich am 1.1.2017 aktualisieren und mir anzeigen, dass der Vertrag nun bis zum 30.6.2018 geht und das letztmögliche Datum der Kündigung der 31.12.2017 ist.

Ich hoffe so weit Verständlich.

Ist das möglich? Und wenn ja : kann mir wer dabei behilflich sein?

Hilfreichste Antwort - ausgezeichnet vom Fragesteller
von Ninombre, Community-Experte für Excel, 17

Ich sehe es wie suboptimierer - ich würde (s. Bild) in einer Spalte anzeigen lassen, ob es Vertragsverlängerungen gibt. Diese dann als Wert in Spalte A wieder einfügen.

Per Makro wäre es im Grund ein Dreizeiler

Sub verlaengerung()
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row 'ab Zeile 2 bis zur letzten benutzten Zeile in Spalte 1 = Spalte A
If Date > Range("D" & i).Value Then Range("a" & i).Value = WorksheetFunction.EDate(Range("A" & i), Range("C" & i))
Next
End Sub

Neben der Berechnung, ob es schon zu einer Vertragsverlängerung gekommen ist, ist bei deinem Fall nur ein weiterer Wert zu berechnen, der Kündigungstermin. Diesen könnte man unabhängig davon, ob die Vertragsverlängerung per Formel oder Makro ermittelt wird, mit edatum(A2;-B2) ermitteln oder hast Du das schon abgebildet?

Kommentar von Cappa90 ,

Okay Makro macht also keinerlei Sinn.

So in etwa wie auf dem Bild dargestellt habe ich es mir vorgestellt ; Allerdings dass es keine Neue Spalte für das Vertragsende gibt, sondern, dass sich die eigentlich Spalte Vertragsende einfach aktualisiert.

Kommentar von Ninombre ,

Ich habe nicht gesagt, dass das Makro keinen Sinn macht.

Formeln in Excel haben eine klare Einschränkung: Sie wirken immer nur auf die Zelle, in der sie stehen. Man kann damit keine Werte in anderen Zellen überschreiben. Das genau aber schwebt Dir vor: Du willst einen festen Anfangswert für den Vertragsablauf haben und diesen dann mit einem neuen Wert überschreiben. Wenn es genau in dieser Form sein soll, geht es nur per Makro. Das Makro habe ich oben auch genannt - mehr ist es nicht.

Mein Einwand: Wenn Du ohnehin manuell in der Tabelle arbeitest, um die nicht mehr benötigten Verträge zu löschen, dann könntest Du auch einfach die per Formel berechneten neuen Vertragslaufzeiten kopieren und überschreiben. Makros haben schlichtweg den Nachteil, dass man prüfen muss, ob sie richtig funktionieren, sonst kommt nur Chaos dabei raus.

Antwort
von Jackie251, 21

Dafür brauchst du doch kein Makro, das lässt sich doch alles per Formel in der Tabelle erledigen.
Wenn du noch ein wenig Infos gibst könnte man mit konkreten Formeln helfen.

Ansonten musst du dir auch überlegen wie du das Ganze nachvollziehbar dokumentierst, Excel ist keine Datenbank.
So kann eine einfache Formel zwar automatisch ab dem 1.1.2017 das Vertragsende 30.06.2018 anzeigen, sofern bei Kündigung nicht "ja" steht.
Aber was passiert wenn du 2020 dann die Kündigung auf "ja" setzt, dann würde diese Formel plötzlich zurückspingen und Mitte 2017 als Enddatum errechnen.
Statt "ja" muss also auch klar hinterlegt sind gekündigt zum Datum....
Und man sollte sich überlegen ob man einfach das Enddatum aktulisiert oder ob ein Datenblatt nicht automatisch jedes Vertragsjahr neu und explizit auflistet (in der Form 3. automatische Verlängerung galt bis 30.06.2016, die 4. bis 30.06.2017 usw usw.) in der Schnellübersicht wird dann nur der Maximalwert des Endes aufgerufen und ist somit aktuell - aber man kann die genaue Laufzeit bei Bedarf noch schnell einsehen.

Kommentar von Cappa90 ,

Hallo,
Die Liste wird lediglich der Übersichtshalber benutzt. Gekündigte Verträge werden händisch daraus entfernt.
Deswegen ist letzteres schonmal nicht nötig.

Zu 1.
Es ist nicht wichtig obs dann zurückspringt.Dafür gäbe es dann andere Programme. Der Reiter Kündigung soll auch nicht Auslöser des "Wenns sein ", sondern der Reiter "Verlängerungsoptionen" in denen mit Ja oder nein geantwortet wird.

Was  für Daten werden denn noch benötigt? Ist es denn auch möglich die Formel für die Ganze Spalte zu kontruieren oder muss z.b.  in der ersten immer A1...B1 in der 5. dann A5 ect. pp reingeschrieben werden?

Aktuelles Vertragsende ist Spalte I

Kündigungsfrist in Monaten Spalte L

Datum Kündigung Spalze M

VerlängerungsOption Spalte N

Verlängerungszeitraum in Monaten Spalte O

Antwort
von radihalle, 14

Eigentlich brauchst Du kein Makro dafür.

Eine Formel setzt Dir alle Ergebnisse pro Zeile automatisch ein; abhängig von der Datum-Eingabe in (A).

So nun zu Deinem Problem:

Ich würde die jeweiligen Datum-Felder in etwa 2 Spalten teilen A= TT.MM und B= Jahreszahl oder A=TT, B=MM, C=JJ. Diese drei Spalten würde ich dann mit einer gemeinsamen Farbe hinterlegen, dass Du leicht ein Datum erkennst.

Dann brauchst Du am 1.1. jeden Jahres nur die Jahres-Spalte (hier im Muster C) in allen Zeilen +1 erhöhen und die Formeln pro Zeile ändern ja sofort alle anderen Jahreszahlen der Zeile mit.

Ich hätte das auch per Makro gelöst; mich stört allerdings das Schaltjahr. So kann ich also nicht 365 Tage auf ein normales Datum addieren. Wäre die einfachste Lösung. Mit Makro könnte man dann zeilenweise hochrechnen.

Versuchs mal. Ich bekomme Antworten von Dir mit E-Mail gesendet.

toi, toi, toi 

Kommentar von Cappa90 ,

Hallo undd anke für die Antwort,

Ich habe mich leider glaube ich leicht missverständlich ausgedrückt. Die 12 Monate Verlängerung waren lediglich ein Beispiel. von 60 Monaten bis 3 Monaten oder auch einem Monat ist so ziemlich alles dabei.

Sorry für die Mühe die du dir gemacht hast auf Grund meiner nicht eindeutigen Frage. War aber ne coole Idee.

Gruß

Kommentar von radihalle ,

Ja, die anderen Spalten sind ja pro Zeile als Rechenvarianten weiterhin notwendig, dass Du nicht an ein festes Mass gebunden bist.  - ist klar -

Kommentar von Iamiam ,

Das Schaltjahr bekommst Du in den Griff mit zB

=Monatsende("1.1.2016";11)+1 ergibt den 1.1.2017

Das Datum in Gänsefüßchen muss man in anderen Formeln häufig erst mal einer Rechenoperation unterwerfen, zB *1 oder +0. xl interpretiert alle integrierten Datumsformate entsprechend, auch amerikanische (nicht aber Benutzerdefinierte),

was manchmal zu unerwünschten Stilblüten führt: gib mal 08/15 ein! Der Listenpunkt 17.1 wird zum 17.Jan(heuer), der 3.5 zum 3.Mai, auch 7-12 wird zum 7.Dez. Lässt sich alles nicht mehr rückwärts auflösen, d.h. xl ändert eingegebene Daten unwiderruflich ab! (eine Regelverletzung in der DV!)

Es kommt noch schlimmer: o.g. 08/15 wird zu 1.Aug 2015, während 7/12 zum 7.Dez heuer wird, also wenn du das nächstes Jahr eingibst,  was ganz anderes!

Expertenantwort
von Suboptimierer, Community-Experte für Excel, 36

Wenn ich mir dein Sheet im geistigen Auge vorstelle, sehe ich keine Notwendigkeit, auf Makros zurück zu greifen. Die Dynamik enthält das Sheet durch die Funktion HEUTE(). 

Mittels EDATUM kannst du Monate aufaddieren, mit JAHR, MONAT und TAG ein Datum zerpflücken und mit DATUM zusammensetzen. Mit WENN kannst du Fallunterscheidungen realisieren, die z. B. auf das Jahr einen drauf rechnen, wenn das heutige Datum + Kündigungsfrist das Kündigungsdatum dieses Jahrs überschreiten würde.

Wo genau hapert es? Hast du einen Fehler? Was genau meinst du nur, mittels eines Makros lösen zu können?

Kommentar von Cappa90 ,

Ich bin wirklich ein totaler Anfänger was das betrifft. Ich fürchte Makro war die falsche Begrifflichkeit, die ich dafür gewählt habe.

Ich brauche lediglich eine Funktion, die das oben dagestellte löst.Das was du geschrieben hast, hört sich schonmal vielversprechend an; Allerdings haperts bei mir bereits dadrin, wo und wie ich das von dir geschriebene umsetze.

Kommentar von Suboptimierer ,

Du musst dir überlegen, was deine Stammdaten sind und welche Daten du errechnen willst. Wie ich das sehe, sind "Datum Vertragsabschluss", "Kündigungsfrist" und "Kündigungsverlängerung".

Wenn Vertragsabschluss z. B. in A2 steht, Kündigungsfrist in B2 steht und die Verlängerung in C2 könntest du so beginnen

Dann ist der nächste Kündigungstermin

D2: =MIN(WENN(EDATUM(A2:A2;C2*ZEILE($1:$100))>EDATUM(HEUTE();B2);EDATUM(A2:A2;C2*ZEILE($1:$100));EDATUM(A2:A2;12*101)))
  • Nur ein Ansatz
  • Matrixformel → Die Eingabe muss mit Strg+Shift+Enter abgeschlossen werden
  • Es werden in einer Matrix intern alle Datumswerte dargestellt, die um die Verlängerung iterativ erhöht vom Vertragsdatum an größer sind als das heutige Datum + Kündigungsfrist und davon wird das Minimum genommen.
______________

Tipp: Wenn eine Formel zu umfangreich wird, splitte sie auf mehrere Zellen auf.

PS: Wenn du Anfängerin bist, wäre es dann nicht klüger, mit einem einfacheren Beispiel zu beginnen? -.-

Kommentar von Iamiam ,

@ Subopt: ich nehme an, sie hat auf Arbeit das als Aufgabe bekommen, sozusagen schaung mer mal, wie die damit zrechtkommt! Wahrscheinlich haben sich andere daran schon die Zähne ausgebissen mit der rekursiven Funktion der Verlängerung der Verlängerung. Auch ich muss Deine Lösung erst mal nachvollziehen, um sie zu verstehen (zumindest das erstere empfehle ich der Fragestellerin auch, ich machs aber erst morgen (bzw ist ja schon, also heute viel später...)

Kommentar von Suboptimierer ,

Ich suchte nach einer Funktion wie VRUNDEN, weil VRUNDEN nicht mit EDATUM funktioniert. Kann sein, dass ich es mir zu kompliziert gemacht habe.

Kommentar von Cappa90 ,

Erstmal danke für die Antworten.
Auch wenn mein Nickname auf A endet, bin ich männlicher Natur ;)
Es ist ähnlich, wie Iamiam vermutet hat. Es geht um die Arbeit. Allerdings isses nicht so, dass mir diese Aufgabe gestellt wurde. Es ist lediglich eine sehr stumpfe Aufgabe, die zwischendurch der Übersichtshalber passieren muss und somit einfach Zeit frisst, welches ich umgehen möchte. Allerdings weisen sämtliche Kollegen/Kolleginnen ähnliches Geschick mit Excel auf, sodass ich da keine Hilfe erwarten kann.Somit konnte ich mir nicht einfach nen "einfacheres Beispiel" suchen, weil dies mir nicht helfen würde ;)

Aus der Formel, werde ich leider nicht so richtig schlau..

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten