Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.04.2024 09:26:26
Median für Zeilen mit gleichen Zeitstempel
19.04.2024 08:41:23
Zwei Makros in ein Arbeitsblatt - zweite wird nicht ausführt
18.04.2024 21:09:11
4 Spalten aus smarter Tabelle in andere smarte Tab. kopieren
18.04.2024 20:45:35
Worksheet_Activate für alle Sheets
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1724to1728
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1724to1728
1724to1728
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Fälligkeitsdatum ermitteln

Fälligkeitsdatum ermitteln
22.11.2019 11:25:50
dEllE
Hallo liebe Excel-Freunde,
folgendes Problem:
Für eine Liquiditätsplanung gibt es eine Kalender-Tabelle, die sämtliche Zahlungen anhand einer Parametertabelle zu deren Fälligkeitstermin ermittelt.
Nun gibt es Zahlungen, die bspw. nur vierteljährlich getätigt werden müssen. Die Formel für die Ermittlung von vierteljährlichen Zahlung habe ich bereits.
Jedoch weiß ich nicht, wie ich in dieser Formel noch definieren kann, ab welchem Monat (oder Startdatum) diese Zahlungen immer durchzuführen sind.
In meinem hochgeladenen Beispiel berechne ich die fällige Zahlung zum 15.12.2019, jedoch wäre die Zahlung schon am 15.11.2019 fällig.
https://www.herber.de/bbs/user/133372.xlsx
Wie muss ich den "Startmonat" oder das "Startdatum" in die Formel einbeziehen?
Bekomme ich nicht hin :(((
Ich freue mich über jeden Tip und schon mal Danke!

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: mit einer Matrixfunktion(alität)sformel ...
22.11.2019 11:55:49
neopa
Hallo dEIE,
... die keines spez. Formelabschluss wie eine klassische Matrixformel benötigt.
In L2:

=WENN(SUMMENPRODUKT((ZÄHLENWENN([@Datum];EDATUM((TEXT(tab_Parameter[Erstmals fällig am];"TT.MM.") &JAHR(HEUTE()-1)+0);{3;6;9;12}))));9999;"")
Gruß Werner
.. , - ...
AW: mit einer Matrixfunktion(alität)sformel ...
22.11.2019 13:17:43
dEllE
Servus Werner,
erstmal vielen Dank. Geniale Formel. EDATUM kannte ich bisher nur aus DAX.
Soweit ich die Formel allerdings verstanden habe, ist das Array {3;6;9;12} für die vierteljährliche Verschiebung bzw. Prüfung zuständig.
Ich müsste aber die Spalte "Zahlungsturnus" in die Logik involvieren, da dort auch Zahlungen vorkommen, die bspw. monatlich oder vlt. sogar mal alle 2 Monate fällig sind.
Anzeige
AW: dafür zusätzlich liAGGREGAT() intergriert ...
22.11.2019 13:51:37
neopa
Hallo dEllE,
... und die Formel wertet danach dynamisch auch alle zugelassen (1;2;3;4;6;12) Turnusmonate aus.
Dazu ersetze die statischen Matrix {3;6;9;12} in der Formel durch:
AGGREGAT(15;6;ZEILE(A$1:A$12)/(REST(ZEILE(A$1:A$12);tab_Parameter[Zahlungsturnus])=0);ZEILE(A$1:INDEX(A:A;12/tab_Parameter[Zahlungsturnus])))
Gruß Werner
.. , - ...
AW:sorry Schreibfehler, AGGREGAT() war gemeint owT
22.11.2019 13:54:58
neopa
Gruß Werner
.. , - ...
AW: AW:sorry Schreibfehler, AGGREGAT() war gemeint owT
22.11.2019 14:16:56
dEllE
Werner,
Du bist der Hardcore Excler ;)
Die Formel funktioniert bisher bei mir nicht, bzw. erhalte ich immer nur "".
Damit ich dem Problem evtl. selbst auf schliche komme, was genau wird mit diesem Teil erzielt:
INDEX(A:A;12/tab_Parameter[Zahlungsturnus])
Für was muss die Index-Funktion verwendet werden?
Anzeige
AW: dazu erläutert ...
22.11.2019 14:32:03
neopa
Hallo,
... mit der Teilformel 12/tab_Parameter[Zahlungsturnus] wird die Anzahl der erforderlichen Zahlungen im Jahr ermittelt und damit ergibt sich für den Formelteil: ZEILE(A$1:INDEX(A:A;12/tab_Parameter[Zahlungsturnus])) ein Zahlenmatrix von 1 bis zu dieser Anzahl, welche dann wiederum als Matrix für das letzte Argument der Funktion AGGREGAT() zur Verfügung steht und damit mit dem AGGREGAT(9-Formelteil wiederum die notwendig Zahlenmatrix (die Zahlmonate) für das zweite Argument der Funktion EDATUM() bereitgestellt. Also bei D2=3 wieder {3;6;9;12} und bei Z:b: D2={6;!2}.
Als Ergebnis der gesamten Formel erhältst Du natürlich ein ="", wenn Dein Kalender den jeweilig ermittelten Zahltag nicht gelistet hat.
Gruß Werner
.. , - ...
Anzeige
AW: dazu erläutert ...
25.11.2019 09:07:30
dEllE
Guten Morgen,
ich habe die Formel jetzt so "einigermaßen" verstanden, jedoch funktioniert sie tatsächlich nicht.
Beispiel:
Erstmals fällig am 15.02.2019, Turnus 1 Monat
Dann finde ich im Kalender (Datum von akt. Monatsanfang bis Ende Folgemonat) lediglich eine Zahlung am 15.11. jedoch keine am 15.12.
Und sobald ich eine weitere Zahlung in die tab_Parameter eintrage, ist auch die o.g. Zahlung verschwunden.
AW: hierzu ...
25.11.2019 11:42:48
neopa
Hallo dEllE,
... Dein 1. Problem kann ich nicht nachvollziehen. Dazu stell doch dazu Deine aktuelle Datei ein. damit ich erkkennen kann woran es da liegen könnte.
Zu Deinem 2. Problem. Meine Formellösung war und ist (bisher) nur für einen Zahlungsturnus aufgestellt. Wie viele verschiedene Datensätze der Parameterdatei sind denn max. auszuwerten? Außerdem müsste dann in dieser Liste auch eine Spalte für den jeweilige Zahlbetrag ergänzt werden.
Wenn das dann von Dir vorliegt, würde ich mich einer entsprechenden Lösungsfindung noch mal annehmen. Dies wird aber voraussichtlich dann erst zum WE hin.
Gruß Werner
.. , - ...
Anzeige
AW: hierzu ...
25.11.2019 15:06:01
dEllE
Moin Werner,
zu 1.)
Jetzt funktionieren zwar die folgenden Zahlungstermine, jedoch nicht der "Erstmals fällig am" Termin. Beispiel 15.11.2019
zu 2.)
Ziel dieser Funktion ist eine Liquiditätsplanung.
Die Tabelle "tab_paramater" hätte ich vlt. besser als "tab_Zahlungsflüsse" benannt.
In diese Tabelle werde ich alle regelmäßigen, anfallenden Zahlungen mit deren Fälligkeiten eintragen.
Die Spalte "Betrag" habe ich nachgetragen. Letztendlich würde ich alle Betrag zum fälligen Zahlungstermin in der Kalendertabelle summieren.
Und Eile hat es natürlich nicht. Ich bin schon über Deine Formel froh, denn auf so viele notwendigen Arrays wäre ich niemals gekommen.
https://www.herber.de/bbs/user/133431.xlsx
Anzeige
AW: nun nachvollziehbar ...
26.11.2019 10:46:11
neopa
Hallo dEeie,
... und festgestellt, dass Deine Zielstellung noch etwas anders ist, als von mir angenommen. Außerdem hatte ich noch einen Fehler in der Formel, der zwar behoben werden kann aber damit Deine komplexe Zielstellung nicht realisiert. Allerdings hast auch Du meine Formel etwas "verfälscht", indem Du aus ZEILE(A$1:A$12) ein ZEILE(A$1:A$10) gemacht hast. Aber wie auch immer, auch damit ist Dein Problem noch nicht gelöst.
Aber wie bereits geschrieben, für Deine Zielstellung müsste ich mich erst intensiver damit auseinandersetzen, wozu ich momentan nicht komme.
Ich werde es aber versuchen im Auge zu behalten.
Gruß Werner
.. , - ...
Anzeige
AW: nun nachvollziehbar ...
26.11.2019 12:00:48
dEllE
Moin Werner,
die "Verfälschung" ist irgendwie beim Verschieben/Kopieren passiert. Hatte ich aber bereits gemerkt, nachdem ich verstanden hatte, welche Funktion dahinter steckt.
Wie gesagt, wenn Du Zeit findest, wäre es schön, wenn wir da eine Lösung finden. Dein Ansatz ist auf jeden schon mal interessant.
Ich werde mich auch mal mit den Arrays quälen. Es kann ja "so" schwer nicht sein ;)
Auf jeden Fall schon mal Danke für Deine Bemühungen.
Also hoffentlich bis die Tage.
Gruß dEllE
AW: nun Formel umgestellt ..
28.11.2019 13:02:16
neopa
Hallo dEIE,
... folgende neue Formel:
=WENN((TAG(tab_Vertragsdaten[Erstmals fällig am])TAG([@Datum]))+([@Datum]&lttab_Vertragsdaten[Erstmals fällig am]);"";SUMMENPRODUKT(0+(tab_Vertragsdaten[Erstmals fällig am]=((TEXT(EDATUM([@Datum];
-AGGREGAT(15;6;ZEILE(A$1:A$12)/(REST(ZEILE(A$1:A$12);tab_Vertragsdaten[Zahlungsturnus])=0);ZEILE(A$1:INDEX($A$1:$A$12;12/tab_Vertragsdaten[Zahlungsturnus]))));"T.M.") &JAHR(tab_Vertragsdaten[Erstmals fällig am]))+0)))*tab_Vertragsdaten[Betrag])
Gruß Werner
.. , - ...
Anzeige
AW: ergänzend hierz nun noch ...
28.11.2019 14:09:34
neopa
Hallo,
... hier hatte ich den der Namen "Tab_Parameter" zuvor in "tab_Vertragsdaten" geändert, wie Du sicherlich schon bemerkt hast.
Den Namen kannst Du jederzeit auch nachträglich wieder in einen von Dir gewünschten ändern. Ich bleibe nachfolgend bei "tab_Vertragsdaten".
Wenn in dieser mehr als ein Datensatz erfasst ist, würde eine einspaltige Datenauswertung zumindest zu komplex werden. Hierfür würde sich besser je Vertragsdatensatz eine neue Auswertungsspalte anbieten. Deren Ergebniswerte kannst man dann mit einer einfachen Summenformel Zeilenweise (als je Datum) zusammenfassen.
Der Zugriff auf die Vertragsdaten muss dann aber auch zwingend über zusätzliches INDEX() erfolgen. Dazu siehe unten.
Die nachfolgende Formel M7 könnte ich noch so definieren, dass sie nicht nur nach unten sondern auch nach rechts ziehend kopiert werden kann. Das hab ich mir hier erspart. Die Formel hab ich nicht ziehend nach rechts kopiert sondern direkt und anschließend darin nur SPALTE(A1) durch SPALTE(B1) bzw. SPALTE(C1) ersetzt.
Genau genommen müsste man auch diesen Zelladressbezug durch einen Bezug auf die interne formatierte Tabellenadressen beziehen, damit die Formel voll dynamisch ist und die Vertragstabelle wie die Ergebnistabellen nachträglich beliebig verschoben werden kann, ohne die Formeln anpassen zu müssen. Auch das hab ich mir hier erspart.
Arbeitsblatt mit dem Namen 'Tabelle1'
 ADFGHLMNO
1Erstmals fällig amZahlungsturnusBetrag      
215.06.20194 Monat/e-66,00 €      
301.04.20201 Monat/e33,00 €      
415.11.20143 Monat/e-77,00 € https://www.herber.de/forum/archiv/1724to1728/t1724906.htm
5         
6    DatumGesamtBetrag_1Betrag_2Betrag_3
7    01.11.2019    
21    15.11.2019-77,00 €  -77,00 €
37    01.12.2019    
51    15.12.2019    
68    01.01.2020    
82    15.01.2020    
99    01.02.2020    
113    15.02.2020-143,00 €-66,00 € -77,00 €
128    01.03.2020    
142    15.03.2020    
159    01.04.202033,00 € 33,00 € 
173    15.04.2020    
189    01.05.202033,00 € 33,00 € 
203    15.05.2020-77,00 €  -77,00 €
220    01.06.202033,00 € 33,00 € 
234    15.06.2020-66,00 €-66,00 €  
250    01.07.202033,00 € 33,00 € 

ZelleFormel
L7=SUMME(tab_kalender[@[Betrag_1]:[Betrag_3]])
M7=WENN((TAG(INDEX(tab_Vertragsdaten[Erstmals fällig am];SPALTE(A1)))>TAG([@Datum]))+([@Datum]&ltINDEX(tab_Vertragsdaten[Erstmals fällig am];SPALTE(A1)));"";SUMMENPRODUKT(0+(INDEX(tab_Vertragsdaten[Erstmals fällig am];SPALTE(A1))= ((TEXT(EDATUM([@Datum];
-AGGREGAT(15;6;ZEILE(A$1:A$13)/(REST(ZEILE(A$1:A$13);INDEX(tab_Vertragsdaten[Zahlungsturnus];SPALTE(A1)))=0);ZEILE(A$1:INDEX($A$1:$A$13;12/INDEX(tab_Vertragsdaten[Zahlungsturnus];SPALTE(A1))))));"T.M.") &JAHR(INDEX(tab_Vertragsdaten[Erstmals fällig am];SPALTE(A1))))+0 )))*INDEX(tab_Vertragsdaten[Betrag];SPALTE(A1)))
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
AW: nun Formel umgestellt ..
28.11.2019 14:22:38
dEllE
Hi Werner,
vielen Dank. Die Formel funktioniert bei nur einem Eintrag in der "tab_Vertragsdaten".
Sobald ich dort weitere Zahlungen eintrage, funktioniert die Formel nicht mehr und ich erhalte #WERT! Fehler.
PS:
Bei der Gelegenheit ist mir auch aufgefallen, dass man die Arrays für das Monatsdurchzählen "A$1:A$12" nicht über eine int. Tabelle legen sollte. Wenn ein Benutzer eine Zeile in dieser Tabelle hinzufügt, ändert sich das Array trotz der $-Sperre auf A$1:A$13 usw.
AW: zu 1. siehe meinen Beitrag oben drüber und ...
28.11.2019 14:32:42
neopa
Hallo dEIE,
... zu Deinem PS: ZEILE(A$1:A$12) kann leicht ersetzt werden durch die starre Matrix{1;2;3;4;5;6;7;8;9;10;11;12}
Aber wie bereits oben geschrieben, müssten dann in meiner letzte Formel auch alle noch vorhandenen Zelladressen durch Bezügen auf die formatierte Tabelle ersetzt werden.
Gruß Werner
.. , - ...
Anzeige
AW: zu 1. siehe meinen Beitrag oben drüber und ...
28.11.2019 15:42:42
dEllE
Hi Werner,
HEILIGER BIMBAM!!!
Erstmal vielen lieben Dank für Deine Bemühungen. TOP!
Mit der Variante die einzelnen Spalten zu summieren kann ich absolut leben.
Jetzt werde ich mich erstmal an's Verständnis dieser Formel setzen, um die Spaltenformeln danach noch dynamisch zu gestalten, dann passt das voll und ganz.
Also nochmal:
VIELEN DANK!
AW: hierzu noch ...
28.11.2019 17:07:31
neopa
Hallo,
... folgender Hinweis: zu diesem thread kann morgen zwar noch aktiv geantwortet werden, aber da er ab morgen in der Forumsliste nicht mehr erscheint, bekomme ich evtl. neue Beiträge Deinerseits in diesem nicht mehr mit.
Sollten Deinerseits also dazu noch Fragen sein, so erstelle dann einen neuen thread und schreibe in diesem im Betreff z.B. "@neopa Fortsetzungsthread ..." und im Beitrag verlinke auf diesen thread.
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige