Office: (Office 2016) Eindeutige Zahlen mit Name & Datum & Summe mit Berücksichtigung des Endedatums

Hallo liebes Forum!

Ich wende mich mit einem recht anspruchsvollem Problem an euch und hoffe einen Profi zu finden der mir hier weiterhelfen kann und möchte ;)

Zur Erklärung:
Ich habe eine Tabelle welche Zahlungsdaten aus dem Buchhaltungsprogramm heraus beinhaltet. Das ist die obere Tabelle in der Datei.
Diese Tabelle ist nach den Kundennummern sortiert und hat für jeden Geldeingang eines Kunden eine Zeile z.B. Kundennummer 1234, Max Mustermann,
Zeitraum 01.01.2020 - 31-01-2020, 100,-- €.
Da diese Tabelle automatisch generiert wird kann es vorkommen dass Max im selben Zeitraum 2x 100,-- € überweist, daher also 2 Zeilen mit jeweils 100,-- €
oder eben auch bei längeren Zeitraumen auch 01.01.2020 - 31.01.2020 -> 100,-- €, 01.02.2020 - 29.02.2020 -> 100,-- €, 01.03.2020 - 31.03.2020 -> 100,-- €, usw.


Soviel dazu, ich hoffe das ist einigermaßen verständlich. :)

Da dies ser unübersichtlich ist, hätte ich gerne eine Zusammenfassung von dieser Buchungstabelle. Eine PiVot-Lösung kann ich leider nicht verwenden, da ich in der
Zusammenfassung auch gerne einige Zellen einbauen würde, welche händisch befüllt werden und - jetzt kommts - auch auswirkung darauf haben wie die Zeilen aus
der Buchungstabelle zusammengefasst werden solln. Das wäre dann die untere Tabelle.

Soll heißen, Max zählt im Jänner und Februar jeweils 100,00 €. Diese beiden Zeilen sollen in der unteren Tabelle aufscheinen. Gibt der zuständige Bearbeiter in einer
Zelle der Jännerzeile ein Endedatum ein, also wie lange er für die Bearbeitung des Kunden brauchen wird z.B. 31.03.2020.
Soll die untere Tabelle die beiden Zeilen von Jänner und Februar zusammenfassen und auch die zukünftige Zahlung für den März dieser Zeile zuordnen können.
Nimmt Max jedoch im Juni wieder eine Leistung in anspruch, soll diese dann entsprechend in einer eigenen Zeile ausgewiesen werden.
Dies ist anhand von Marie Musterfrau in der Datei angedeutet.

Die Abgleichung mit der händischen Datumseingabe ist desswegen notwendig, weil es durchaus sein kann, dass Jänner und Februar bezahlt wird, der März aussetzt und
erst im April wieder eine Zahlung erfolgt, es sich aber um einen Auftrag handelt.

Eine ziemlich harte Nuss das ganze, ich hoffe dass es sich so realisieren lässt.


Vielen Dank vorab für eure Mühen! :D


Liebe Grüße

Marcus

 

Hallo Marcus,

ich habe deine Vorgabedaten in ein 2. Arbeitsblatt "Tabelle1 (2)" kopiert und dort zwei Buttons eingefügt:

1. Mit dem 1. Button ("Summationsbereich entfernen") kann man die untere Tabelle (=Summationsbereich/Zusammenfassung) entfernen, um eventuell noch weiteren Platz für die obere Tabelle (Buchhaltungstabelle) zu schaffen.
   -
2. Mit dem 2. Button ("Summation für händische Eingabe") wird dann die untere Tabelle wieder erstellt anhand der Daten der oberen (Buchhaltungs)Tabelle.
   Pro Kunde wird dabei eine Summenzeile erstellt aufgrund der Daten der oberen Tabelle. Das sind im vorliegenden Demodatenbestand 4 Summenzeilen (weil 4 Kunden).
   -
3. Man kann dann eine Aufsplittung dieser Summenzeilen vornehmen, indem man in der Spalte "Eingabe des Bearbeiters" ein Interimsdatum einträgt.
   Z.B. steht in der 1. Summenzeile (Kunde "Max Mustermann") in der Spalte "Eingabe des Bearbeiters" das höchste Datum aus der Spalte "Leistungsdatum bis" der oberen Tabelle, das für diesen Kunden infrage kommt (=29.02.2020).
   Gibt man jedoch für diesen Kunden in der Spalte "Eingabe des Bearbeiters" den 31.01.2020 ein, wird die Zeile in 2 Zeilen aufgeteilt: die 1.Zeile enthält dann die Werte der oberen Tabelle bis zum 31.01.2020 und die 2.Zeile die Werte der oberen Tabelle bis zum 29.02.2020.
   
   Ähnliches kann man auch zB. mit der Kundin "Maria Musterfrau" machen (letzte Summenzeile), indem man statt des 31.07.2020 den 31.05.2020 eingibt: Das Resultat sind dann 2 Zeilen mit den Werten bis zum 31.05.2020 und mit den Werten vom 01.06.2020 bis zum 31.07.2020.
   
   Diese Aufsplittung kann man nach Belieben fortsetzen. Ist man jedoch eines schönen Tages mit der so erfolgten Aufsplittung nicht zufrieden, müsste man wieder den 2. Button (Summation) anklicken - dann wird die untere Tabelle gelöscht und es erfolgt eine Neusummation.

Ich hoffe, dass diese Makrolösung deinen Wünschen entspricht.

 

Zur info....
https://www.clever-excel-forum.de/T...um-Summe-mit-Beruecksichtigung-des-Endedatums

 

Hallo Anton!

Bitte entschuldige die späte Antwort!

Wow, Deine Lösung ist der Hammer!!! Ein großes Dankeschön!

Nur einen kleinen Hacken hat die Sache, wenn ich auf den unteren Button "Summation für händische Eingabe" klicke sind meine händischen Eingaben alle zurückgesetzt.
Das hätte zur Folge dass die Bearbeiter höllisch sauer sind und es nie wieder verwenden :-)

Warum ich diese Anwendung brauche ist, eine Übersicht aus den Buchungen zu schaffen und gleichzeitig die Auslastung der Bearbeiter zu sehen sowie die Zahlungen zu den jeweiligen Aufträgen zuzuweisen. Sprich der Bearbeiter betreut einen Kunden, sobald eine Zahlung erfolgt, scheint eine Buchungszeile auf. Der Bearbeiter sieht dann die Zeile in der unteren Tabelle und gibt mit dem "bis Datum" das voraussichtliche Ende ein. Alle Zahlungen des Kunden sollen dann bis zum "bis Datum" in eine Zeile zusammengefasst werden.

Ich weiß das ist eine sehr anspruchsvolle Angelegenheit :-)

Liebe Grüße
Marcus

 

Hallo Steve1da!

Danke für den Link, aber das bin auch ich :-)

Liebe Grüße
Marcus

 

Hallo Marcus,

ich habe mir jetzt mein Hirn zermartert, wie dein Posting #1 nun denn wirklich zu verstehen ist. Kann es sein, dass du in Wirklichkeit eine OP-Bearbeitung haben möchtest?
Das würde dann in Wahrheit Folgendes bedeuten:
.

1. Die obere Tabelle ("aus der Buchhaltung") ist gar nicht die Tabelle der Zahlungseingänge, sondern die Tabelle der Forderungen, die aufgrund der Fakturierung (=Rechnungslegung) von Leistungen, die in den einzelnen Leistungszeiträumen erbracht werden, entstehen. Das würde nämlich auch erklären, warum in dieser Tabelle Spalten für den "Leistungszeitraum von/bis" aufscheinen und die Bewegungen in dieser fein säuberlich nach diesen erstellt sind.
   .
2. Davon zu unterscheiden wäre dann die Tabelle der Zahlungseingänge (untere Tabelle), für die eine Offene-Posten-Bearbeitung durch die Bearbeiter erfolgen soll.
   Zahlt/überweist z.B. Max Mustermann die beiden Forderungspositionen für Jänner bzw. Februar nicht in 2 Beträgen von je € 100,-, sondern in einem Betrag (€ 200,-), dann wäre dieser Betrag aufzuteilen und mit den aufgeteilten Beträgen die Forderungen aus Jänner bzw. Februar auszugleichen.
   .
3. Falls dem so wäre, müsste man sowohl an die obere Tabelle (Forderungen) als auch an die untere Tabelle (Zahlungen) eine weitere Spalte anfügen. In dieser ist eine eindeutige Zahlungsnummer einzufügen, anhand der man z.B. die Zahlung von Max Mustermann (untere Tabelle) den beiden Forderungspositioen von Max Mustermann Jänner+Februar (obere Tabelle) zuordnen kann.
   Die eigentlichen Probleme entstehen dann, wenn Max Mustermann nicht exakt den passenden Betrag bezahlt. Oder aber auch, wenn nicht klar ist, welchen Forderungspositionen welche Zahlung zuzuordnen ist.

Die Programmierung einer solchen Lösung erreicht einen gewissen Umfang, der um einiges über den eines Hilfeforums hinausgeht.
Da muss ich leider aufgeben.

 

Hallo Anton!

Nein nein, es passt alles so wie Du es gemacht hast perfekt. Die untere Tabelle dient einfach der Übersicht wieviel jeder Kunde bereits bezahlt hat damit der Bearbeiter bei Abschluss weiß wieviel bezahlt wurde und dann eine Endrechnung stellen kann.

Das einzige Problem an Deinem Werk ist, dass wenn man auf den Button klickt alle EIngeben der Bearbeiter weg sind und neue Zahlungseingänge erst nach klicken auf besagten Button aufscheinen.

Ist kein Problem, ich möchte mich trotzdem herzlich für Deine Hilfe bedankenT

Liebe Grüße
Marcus

 

Hallo,

ich habe jetzt die Zelle D11 benannt in "SummationVon". In dieser Zelle ist das Datum einzugeben, ab dem eine Summation erfolgt, wenn der Button "Summation für händische Eingabe" geklickt wird. Dabei werden in beiden Tabellen die Zeilen übergangen, die in der Spalte "Leistungszeitraum von" ein Datum enthalten, das kleiner (=früher) ist als das in Zelle "SummationVon".

Im momentanen Beispiel werden, da in der Zelle "SummationVon" der 01.07.2020 als Datum eingegeben ist, beim Klick auf Button "Summation für händische Eingabe" die letzten beiden Zeilen der Kundin Maria Musterfrau als Summenzeile an die 2. Tabelle angefügt mit Betrag € 200,-. Die übrigen Zeilen der beiden Tabellen bleiben unbehelligt.

Wird die Zelle "SummationVon" geleert (oder auf den 01.01.2020 gesetzt), so ist nach Klick auf vorgenannten Button die gleiche Situation wie vor der Makro-Änderung: Alle Zeilen werden neu aufsummiert.

 

Schönen guten Morgen Anton!

Vielen Dank, es funtioniert spitze. nur sind nach wie vor die Änderungen der Bearbeiter "weg" wenn ich den unteren Button drücke.

Ich habe jetzt Maria auf 2 Zeilen gesplitten - funktioniert einwandfrei!
Jetzt hab ich oben eine Zeile hinzugefügt, diese scheint unten nicht auf, erst wenn ich auf den Button klicke. Dann sind aber wieder alle oberen Änderungen der Bearbeiter zurückgesetzt.

Liebe Grüße
Marcus

 

Hallo Marcus,

da die Vorgabe einer Summationsgrenze mittels "SummationVon" offenbar zu Problemen der unterschiedlichsten Art führt, habe ich das Summationsmakro wie folgt geändert:
1) Es wird die Betragssumme der unteren Tabelle (Bearbeitungsbereich) gebildet.
2) Es wird die Betragssumme der oberen Tabelle (Buchhalt.Tabelle) gebildet.
3) Es werden am Ende der Buchhalt.Tabelle solange Zeilen aus der Betragssumme von 2) ausgeschieden, bis die beiden Summen aus 1) und 2) übereinstimmen.
4) Der in 3) ermittelte, noch nicht übertragene Zeilenbereich der Buchhalt.Tabelle wird mittels neuer Summenzeilen an die bestehenden Summenzeilen angefügt.

Im beiliegenden Beispiel ergeben sich folgende Verhältnisse:
1) Die Betragssumme in der unteren Tabelle (Bearbeitungsbereich) ist € 1.200,-
2) Die Betragssumme in der oberen Tabelle (Buchhalt.Tabelle) ist € 1.350,-
3) Die Differenz zwischen beiden Summen ergibt sich, weil die beiden letzten Zeilen der oberen Tabelle (Musterfrau € 50,- / Synonym € 100,-) noch nicht aufsummiert wurden.
4) Die beiden noch nicht übertragenen Kunden werden als neue/zusätzliche Summensätze über € 50,- bzw. € 100,- an die bestehenden (und unverändert belassenen) 4 Summensätze im Bearbeitungsbereich der unteren Tabelle angefügt, sodass sich schlussendlich 6 Zeilen im Bearbeitungsbereich ergeben.

 

Hallo Anton!

Vielen Dank für die neuerliche Überarbeitung!
Leider funktioniert es immer noch nicht so richtig, denn denn in der unteren Tabelle sollten die beiden gelb und grün hinterlegten Zeilen eigentlich zusammengefasst werden. Ich bin mir langsam nicht mehr sicher ob sich das überhaupt realisieren lässt und möchte Dir auch nicht allzu viel Deiner Zeit stehlen!

Liebe Grüße
Marcus

 

Hallo Marcus,

dadurch dass ich das Summationsmakro geändert habe, ergibt sich bei der Prozedur, die zum 1. Button ("Summationsbereich entfernen") ein Problem, sodass sich Makros gegenseitig aufrufen und so sich gegenseitig blockieren. Das kann leicht behoben werden, indem du in der SUB SummenbereichEntfernen die 4 rot gekennzeichneten Codezeilen einfügst:

Code:

'
'Summenbereich/Bearbeit.bereich wird gelöscht (Button "Summationsbereich entfernen")
'
Sub SummenbereichEntfernen()
  Dim Zl1 As Long  'Zeilennummer der 1.Kopfzeile
[COLOR="#FF0000"]  On Error GoTo Ende_Summenb_Entf
  Application.EnableEvents = False
[/COLOR]  With ActiveSheet
     With .ListObjects(1)
       Zl1 = .HeaderRowRange.Row + .ListRows.Count + 4
     End With
     .Cells(Zl1, 2).Resize(2, 8).CurrentRegion.Clear
  End With 'ActiveSheet
[COLOR="#FF0000"]Ende_Summenb_Entf:
  Application.EnableEvents = True[/COLOR]
End Sub

Dadurch wird während des Löschvorgangs die Ereignisbehandlung ausgeschaltet und hinterher wieder zuverlässig eingeschaltet. Da nämlich durch die ".Clear"-Methode der Inhalt des Arbeitsblattes geändert wird, würde ansonsten das Makro "Workbook_SheetChange(...)", das auf das Änderungsereignis reagieren würde, in Aktion treten.

 

Hallo Marcus,

Sie werden auch tatsächlich zusammengefasst, wie du meiner nachfolgenden Beispieldatei im Arbeitsblatt "Tabelle1 (4)" entnehmen kannst.
1) In dieses Arbeitsblatt habe ich dein Arbeitsblatt "Tabelle1 (3)" kopiert,
2) dann habe ich die 4 farbigen Zeilen der unteren Tabelle entfernt,
3) den 2.Button "Summation für händische Eingabe" geklickt
4) sodass aus den 4 angefügten Zeilen der oberen Tabelle 2 Summenzeilen in der unteren Tabelle erzeugt wurden.

In der angefügten Exceldatei habe ich auch das Problem mit dem ersten Button (Summationsbereich entfernen) behoben.

 

Hallo Anton!

Dankeschön für die neuerliche Änderung!!

Ich weiß nicht woran es liegt, bei mir funktioniert es einfach nicht... :-(

Neue Einträge in der oberen Tabelle werden immer als neue Zeile in die untere Tabelle eingefügt, egal welche Datumsangaben ich mache. Wird ein Datum in der unteren Tabelle geändert wird der Wert zur oberen Zeile hinzugezählt, und eine Zeile mit dem minusbetrag erstellt und eine Zeile mit dem positiven Wert erstellt.

Liebe Grüße
Marcus

 

Hallo,

wenn ich von folgender oberen Tabelle ausgehe (siehe folgendes Bildschirm-Foto)

und von folgender unteren Tabelle:


klicke ich dann auf den Button "Summation für händische Eingabe", erhalte ich folgende untere Tabelle:


Gebe ich in der unteren Tabelle in die erste angefügte Zeile für "Musterfrau" (Zeile 29 - Spalte "Eingabe des Bearbeiters") als Datum den 30.09.2020 ein, erhalte ich folgende untere Tabelle

Gebe ich anschließend in der unteren Tabelle in die erste Zeile für "Synonym" (Zeile 31 - Spalte "Eingabe des Bearbeiters") als Datum den 30.04.2020 ein, erhalte ich weiters folgende untere Tabelle.


Ich meine, das müsste das von dir gewünschte Verhalten des VBA-Makros sein - falls ich mich nicht irre.