Clever-Excel-Forum › Office-Anwendungen › Excel v
Eindeutige Zahlen mit Name & Datum & Summe mit Berücksichtigung des Endedatums

Thema geschlossen 
Baumstrukturmodus
Eindeutige Zahlen mit Name & Datum & Summe mit Berücksichtigung des Endedatums
MarcusK Offline
pausiert
Registriert seit: 13.08.2020
Version(en): MS Office 2016
#1
27.08.2020, 08:43 (Dieser Beitrag wurde zuletzt bearbeitet: 27.08.2020, 09:06 von schauan. Bearbeitungsgrund: seltsamer "Abspann" )
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 Angel

Hier wäre eine Beispieldatei:

.xlsx   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
Suchen
Top
schauan Offline
Einer von hier
Registriert seit: 10.04.2014
Version(en): 97-2019 (32) + 365 (64)
#2
27.08.2020, 09:42
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.
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
[-] Folgende(r) 1 Nutzer sagt Danke an schauan für diesen Beitrag:
  • MarcusK
Homepage Suchen
Top
steve1da Offline
Benutzer
Registriert seit: 16.04.2014
Version(en): xl2016/365
#3
27.08.2020, 10:00
Hola,

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

Gruß,
steve1da
[-] Folgende(r) 1 Nutzer sagt Danke an steve1da für diesen Beitrag:
  • MarcusK
Suchen
Top
Raoul21 Offline
pausiert
Registriert seit: 01.04.2020
Version(en): 2007
#4
27.08.2020, 10:57
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
[-] Folgende(r) 1 Nutzer sagt Danke an Raoul21 für diesen Beitrag:
  • MarcusK
Suchen
Top
MarcusK Offline
pausiert
ThreadStarter
Registriert seit: 13.08.2020
Version(en): MS Office 2016
#5
27.08.2020, 12:42 (Dieser Beitrag wurde zuletzt bearbeitet: 27.08.2020, 12:46 von 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...? Blush

Liebe Grüße
Marcus
Hallo Raoul21!

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

Liebe Grüße
Marcus
Suchen
Top
schauan Offline
Einer von hier
Registriert seit: 10.04.2014
Version(en): 97-2019 (32) + 365 (64)
#6
27.08.2020, 13:21
Hallöchen,

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

einfach den Link aus der Adresszeile kopieren und hier posten.
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
Homepage Suchen
Top
schauan Offline
Einer von hier
Registriert seit: 10.04.2014
Version(en): 97-2019 (32) + 365 (64)
#7
27.08.2020, 13:52
Hallöchen,

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

Arbeitsblatt mit dem Namen 'Tabelle1'
FGHIJKL
2Daten der Buchhaltung Zahlungseingänge
3VorgangKundennummerVornameNachnameLeistungszeitraum vonLeistungszeitraum bisBetrag
411234MaxMustermann01.01.202031.01.2020100,00 €
511234MaxMustermann01.02.202029.02.2020100,00 €
621357PaulPlatzhalter01.01.202031.01.2020100,00 €
721357PaulPlatzhalter01.01.202031.01.2020100,00 €
832468BerndBeispiel01.01.202031.01.2020100,00 €
932468BerndBeispiel01.02.202029.02.2020100,00 €
1032468BerndBeispiel01.02.202029.02.2020100,00 €
1145678MariaMusterfrau01.03.202031.03.2020100,00 €
1245678MariaMusterfrau01.04.202030.04.2020100,00 €
1345678MariaMusterfrau01.05.202031.05.2020100,00 €
1455678MariaMusterfrau01.07.202031.07.2020100,00 €

ZelleFormel
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'
ABCDEFGHI
18händische Eingabehändische Eingabe
19VorgangDaten der Buchhaltung Zahlungseingängegeplante StdEingabe des Bearbeiters
2011234MaxMustermann200,00 €01.01.202029.02.202029.02.2020
2121357PaulPlatzhalter200,00 €01.01.202031.01.202031.05.2020
2232468BerndBeispiel300,00 €01.01.202029.02.202029.02.2020
2345678MariaMusterfrau300,00 €01.03.202031.05.202031.05.2020
2455678MariaMusterfrau100,00 €01.07.202031.07.202030.09.2020

ZelleFormel
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
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
Homepage Suchen
Top
Rudi'S Offline
Benutzer
Registriert seit: 18.06.2017
Version(en): 2021
#8
27.08.2020, 13:58
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
Suchen
Top
Raoul21 Offline
pausiert
Registriert seit: 01.04.2020
Version(en): 2007
#9
27.08.2020, 19:37
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?


Angehängte Dateien Thumbnail(s)
   
[-] Folgende(r) 1 Nutzer sagt Danke an Raoul21 für diesen Beitrag:
  • MarcusK
Suchen
Top
schauan Offline
Einer von hier
Registriert seit: 10.04.2014
Version(en): 97-2019 (32) + 365 (64)
#10
28.08.2020, 06:52
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'
ABCDEFGHIJ
19VorgangDaten der Buchhaltung Zahlungseingängegeplante StdEingabe des BearbeitersExcel2016
2011234MaxMustermann200,00 €01.01.202029.02.202029.02.202029.02.2020
2121357PaulPlatzhalter200,00 €01.01.202031.01.202031.05.202031.01.2020
2232468BerndBeispiel300,00 €01.01.202029.02.202029.02.202029.02.2020
2345678MariaMusterfrau300,00 €01.03.202031.05.202031.05.202031.05.2020
2455678MariaMusterfrau100,00 €01.07.202031.07.202030.09.202031.07.2020
25#NV
26#NV

ZelleFormel
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
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
[-] Folgende(r) 1 Nutzer sagt Danke an schauan für diesen Beitrag:
  • MarcusK
Homepage Suchen
Top
Thema geschlossen 


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste