Eindeutige Zahlen mit Name & Datum & Summe mit Berücksichtigung des Endedatums

[MarcusK]

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

Hier wäre eine Beispieldatei:

  Beispieldatei.xlsx (Größe: 17,96 KB / Downloads: 17)

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. :19:

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!

Liebe Grüße
Marcus

[schauan]

Hallo Marcus,

eine einfache Zusammenfassung wäre als Formellösung machbar. Man könnte für die Kunden-ID die Formellösung für den Spezialfilter ohne Duplikate anwenden und den Rest dann mit weiteren Formeln erzeugen bzw. berechnen.

Ich denke, wegen Deinem letzten Ansinnen lässt sich die Aufgabe allerdings nur mit einer oder einigen Hilfsspalten oder per Makro lösen.

Für eine Hilfsspaltenlösung müsstest Du in der oberen Tabelle die zusammenhängenden Datensätze kennzeichnen - dazu sollten die wie in Deiner Tabelle sortiert vorliegen. Man müsste die Kennzeichnung nach unten übernehmen und anhand der dann die ID anlegen und die Summen bilden.

Bin allerdings jetzt erst mal ein paar Stunden offline.

[steve1da]

Hola,

verlinkst du bitte deine Fragen in den verschiedenen Foren gegenseitig?
Danke.

Gruß,
steve1da

[Raoul21]

Hi

als schnelle Lösung, zwar nicht genau das Ergebnis, das Du wolltest, aber in die Richtung:

Tabelle, In Berech konvertieren
Daten Teilergebnis, OK

Damit kannast Du dann weiter arbeiten.

Oder Makro schreiben. Geht auf Knopfdruck.

Gruss, Raoul

[MarcusK]

Hallo Schauan!

Vielen Dank für den Lösungsvorschlag! So wie Du sagst, die Zusammenfassung geht an sich.
Nur die Einbindung des händischen Ende-Datums  erweist sich als echtes Problem. Hilfsspalten sind eigentlich kein Problem, die kann man ja ausblenden.
Komme aber dennoch nicht drauf. :20: 

Liebe Grüße
Marcus

---

Hallo Steve1da!

Gerne, ähm wie mach ich das...?

Liebe Grüße
Marcus

---

Hallo Raoul21!

Vielen Dank für Deine Nachricht! Ich hab jedoch keine Ahnung was Du meinst.  

Liebe Grüße
Marcus

[schauan]

Hallöchen,

Gerne, ähm wie mach ich das...?

einfach den Link aus der Adresszeile kopieren und hier posten.

[schauan]

Hallöchen,

hier mal ein Ansatz, wie Du die "Vorgänge" ermitteln kannst.

Arbeitsblatt mit dem Namen 'Tabelle1'
	F	G	H	I	J	K	L
2					Daten der Buchhaltung Zahlungseingänge
3	Vorgang	Kundennummer	Vorname	Nachname	Leistungszeitraum von	Leistungszeitraum bis	Betrag
4	1	1234	Max	Mustermann	01.01.2020	31.01.2020	100,00 €
5	1	1234	Max	Mustermann	01.02.2020	29.02.2020	100,00 €
6	2	1357	Paul	Platzhalter	01.01.2020	31.01.2020	100,00 €
7	2	1357	Paul	Platzhalter	01.01.2020	31.01.2020	100,00 €
8	3	2468	Bernd	Beispiel	01.01.2020	31.01.2020	100,00 €
9	3	2468	Bernd	Beispiel	01.02.2020	29.02.2020	100,00 €
10	3	2468	Bernd	Beispiel	01.02.2020	29.02.2020	100,00 €
11	4	5678	Maria	Musterfrau	01.03.2020	31.03.2020	100,00 €
12	4	5678	Maria	Musterfrau	01.04.2020	30.04.2020	100,00 €
13	4	5678	Maria	Musterfrau	01.05.2020	31.05.2020	100,00 €
14	5	5678	Maria	Musterfrau	01.07.2020	31.07.2020	100,00 €

Zelle	Formel
F4	=WENN(ISTZAHL(K3);WENN(ODER(H3&I3<>[@Vorname]&[@Nachname];MONAT(K3)<MONAT([@[Leistungszeitraum bis]])-1);MAX($F$3:F3)+1;MAX($F$3:F3));1)

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg

In der zweiten Tabelle arbeitest Du dann mit der Vorgangsnummer, hier mal für die ID. Damit kannst Du dann auch die Summen bilden oder ein Maximum beim Datum ermitteln ...

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H	I
18								händische Eingabe	händische Eingabe
19	Vorgang	Daten der Buchhaltung Zahlungseingänge						geplante Std	Eingabe des Bearbeiters
20	1	1234	Max	Mustermann	200,00 €	01.01.2020	29.02.2020		29.02.2020
21	2	1357	Paul	Platzhalter	200,00 €	01.01.2020	31.01.2020		31.05.2020
22	3	2468	Bernd	Beispiel	300,00 €	01.01.2020	29.02.2020		29.02.2020
23	4	5678	Maria	Musterfrau	300,00 €	01.03.2020	31.05.2020		31.05.2020
24	5	5678	Maria	Musterfrau	100,00 €	01.07.2020	31.07.2020		30.09.2020

Zelle	Formel
A20	=WENN(ZÄHLENWENN(F4:F14;ZEILE(A1))>0;ZEILE(A1);"")
B20	=SVERWEIS(A20;Datentabelle;2;FALSCH)
C20	=SVERWEIS($B20;Datentabelle[[Kundennummer]:[Nachname]];2;FALSCH)
D20	=SVERWEIS($B20;Datentabelle[[Kundennummer]:[Nachname]];3;FALSCH)

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg

[Rudi'S]

Hallo Marcus,
 zu der vorgeschlagenen ID-Nummer in einer Hilfsspalte (damit schon mal zuordenbar) solltest du in einer 2. Hilfsspalte die Anzahl der erfassten Monate bzw. Bezugsmonate (immer mit 1, dann hast du bei Addition zugehörig zur UD-Nummer die Anzahl) aufführen, damit die Beträge in der Zusammenfassung mit denen der Buchhaltung übereinstimmen. ZB. bei der Musterfrau ist es der Auftrag 1, aber durch die Pause sind es dann nicht 5 sondern in der Addition 4 Leistungsmonate.

Auf was soll sich die händische Eingabe beziehen: als Nachweis wie viele Leistungen dort verrechnet wurden (Überprüfung der vollständigen Zahlung - kann es eigentlich nicht sein, da meines Wissens meistens/immer Adhoc Zahlung erfolgen muss), Oder soll das der Hinweis für weitere noch zu erfolgende Leistungen sein? 

Gruß Rudi

[Raoul21]

Marcus, Du schreibst, Du hast keine Ahnung, was ich meine. Weiss nicht, was da so schwer zu verstehen ist. Hier nochmals mit andere Worten.

Geh in deiner Beíspieldatei in die Tabelle mit den Zahlungsdaten aus dem Buchhaltungsprogramm.

Gehe dann ins Menü Tabellentools, und dort "In Bereich konvertieren"
Dann geh ins Menü Daten, dort in Teilergebnis. Ein Fenster geht auf, bestätige mit OK. (Siehe Screenshot)

Damit erscheint eine Tabelle mit Teilergebnissen, ähnlich, wie du sie wolltest.

Das geht sehr schnell und liefert eine gute Grundlage für weitere Arbeiten.

Verstehst Du das jetzt besser?

[schauan]

Hallöchen,

hier nochmal die Auswertung mit ein paar mehr Formeln. Du müsstest lediglich z.B. mit WENNFEHLER dem #NV vorbauen wenn die Vorgänge beendet sind. Ich weiß allerdings nicht, wie man den 30.09. berechnen sollte ...

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H	I	J
19	Vorgang	Daten der Buchhaltung Zahlungseingänge						geplante Std	Eingabe des Bearbeiters	Excel2016
20	1	1234	Max	Mustermann	200,00 €	01.01.2020	29.02.2020		29.02.2020	29.02.2020
21	2	1357	Paul	Platzhalter	200,00 €	01.01.2020	31.01.2020		31.05.2020	31.01.2020
22	3	2468	Bernd	Beispiel	300,00 €	01.01.2020	29.02.2020		29.02.2020	29.02.2020
23	4	5678	Maria	Musterfrau	300,00 €	01.03.2020	31.05.2020		31.05.2020	31.05.2020
24	5	5678	Maria	Musterfrau	100,00 €	01.07.2020	31.07.2020		30.09.2020	31.07.2020
25		#NV
26		#NV

Zelle	Formel
A20	=WENN(ZÄHLENWENN(Datentabelle[Vorgang];ZEILE(A1))>0;ZEILE(A1);"")
B20	=SVERWEIS(A20;Datentabelle;2;FALSCH)
C20	=SVERWEIS($B20;Datentabelle[[Kundennummer]:[Nachname]];2;FALSCH)
D20	=SVERWEIS($B20;Datentabelle[[Kundennummer]:[Nachname]];3;FALSCH)
E20	=SUMMEWENN(Datentabelle[Vorgang];A20;Datentabelle[Betrag])
G20	=MAXWENNS(Datentabelle[Leistungszeitraum bis];Datentabelle[Vorgang];A20)
J20	{=MAX(WENN(Datentabelle[Vorgang]=A20;Datentabelle[Leistungszeitraum bis]))}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg