HERBERS Excel-Forum - das Archiv

Thema: Automatische Aufteilung anteilige Umsätze nach Mitarbeitern

Automatische Aufteilung anteilige Umsätze nach Mitarbeitern
Selfy
Hallo zusammen. Wir führen in unserer Kanzlei ein Rechnungsausgangsbuch in Excel, in der jede von uns fakturierte Rechnung an Mandanten aufgelistet wird. In einer Spalte befindet sich der Nettoumsatz, in einer anderen wird als normales Textfeld eingetragen "MA1 20,5/MA2 35,6/MA3 43,9". "MA" steht hierbei für das Kürzel des jeweiligen Mitarbeiters. Die Prozente bedeuten, wie viel am Umsatz er/sie beteiligt ist. Am Ende des Monats wird dann händisch für jeden einzelnen Mitarbeiter berechnet, wie viel Gesamtumsatz in dem Monat erwirtschaftet wurde.

Gibt es eine Möglichkeit, das in irgendeiner Form zu automatisieren, möglichst einfach gehalten? Wie viele Mitarbeiter an einem Auftrag mitgearbeitet haben, ist sehr unterschiedlich und nach oben hin quasi offen - oft einer allein, manchmal auch 6 oder 7.

Ich hoffe, mein Anliegen ist verständlich.

Grüße
Selfy
AW: Automatische Aufteilung anteilige Umsätze nach Mitarbeitern
{Boris}
Hi,

zeig mal bitte eine kleine Beispieldatei.
Als Tipp vorab:
Mitarbeiter und Umsatzbeteiligung sind z.B. zwei getrennte Infos und gehören in verschiedene Spalten.
Dabei kann man den Umsatzanteil z.B. aus einer Matrix entnehmen (links: Mitarbeiter, rechts: Umsatzanteil).

Das nur vorab als Anstoß.

Kannst aber gerne auch einfach mal Deine bestehende Datenstruktur in der Datei zeigen.

VG, Boris
AW: Automatische Aufteilung anteilige Umsätze nach Mitarbeitern
cysu11
Hallo Selfy,

wie ist denn deine Tabelle aufgebaut, lade doch bitte deine Beispieldatei hoch! :)

LG, Alexandra
AW: Automatische Aufteilung anteilige Umsätze nach Mitarbeitern
Onur
Poste mal eine Datei dazu.
AW: Automatische Aufteilung anteilige Umsätze nach Mitarbeitern
daniel
Hi
einfach wird schwierig, vor allem mit deiner Excelversion.
Ein Excel 365 würde es einfacher machen, so wirst du wahrscheinlich mit Hilfsspalten arbeiten müssen.

ein möglicher Weg:
da du es nicht genauer beschrieben hast, nehme ich für meine Lösungsbeschreibung an, dass die Nettoumsätze in Spalte A stehen und die Texte mit den Anteilen in Spalte B. Zeile 1 ist immer Überschrift, Daten ab Zeile 2. Sollte es bei euch anders sein, kannst du das ja anpassen (jemand, der in einer Kanzlei arbeitet, sollte das hinbekommen)

1. schreibe in die Zeile 1 neben die Tabelle (also ab Spalte C) die einzelnen Mitarbeiterkürzel, so wie sie in den Texten verwendet werden (C1: MA1, D1 MA2, E1 MA3 usw), du solltest für jeden Mitarbeiter eine Spalte haben.
2. in die Zelle C2 kommt dann diese Formel, ,mit welcher du den für diese Zeile passenden Betrag des jeweiligen Mitarbeiters ausrechnest: 
=$A2*0,01*LINKS(TEIL($B2;FINDEN(C$1;$B2)+LÄNGE(C$1);9);FINDEN("/";TEIL($B2;FINDEN(C$1;$B2)+LÄNGE(C$1);9)&"/")-1)


unterhalb der Gesamtliste kannst du dann einfach aufsummieren und hast für jeden Mitarbeiter die Summe.

Gruß Daniel
AW: Automatische Aufteilung anteilige Umsätze nach Mitarbeitern
Selfy
https://www.herber.de/bbs/user/177154.xlsx

Hier ist die Beispieldatei zu finden.
AW: Automatische Aufteilung anteilige Umsätze nach Mitarbeitern
Daniel
Könntest du denn mit meiner Idee was anfangen?
AW: Automatische Aufteilung anteilige Umsätze nach Mitarbeitern
Onur
Gibt es eine Möglichkeit, das in irgendeiner Form zu automatisieren, möglichst einfach gehalten?
WAS willst du denn genau automatisieren? Deine Datei hilft da auch nicht weiter...
AW: Automatische Aufteilung anteilige Umsätze nach Mitarbeitern
daniel
Hallo

hier eine "einfache" Formellösung, passend für deine Beispieldatei.
Mitarbeitername und Auswertemonat habe ich als Text fest eingestellt, aber das kannst du ja einfach durch einen Zellbezug ersetzen.
Ermittelt wird der Anteil am Umsatz. 

=SUMME(WENNFEHLER($E$2:$E$4*0,01*(TEXT($C$2:$C$4;"JJJJ-MM")="2025-04")*LINKS(TEIL($D$2:$D$4;FINDEN("MA1";$D$2:$D$4)+LÄNGE("MA1");9);FINDEN("/";TEIL($D$2:$D$4;FINDEN("MA1";$D$2:$D$4)+LÄNGE("MA1");9)&"/")-1);0))/SUMME($E$2:$E$4*(TEXT($C$2:$C$4;"JJJJ-MM")="2025-04"))


solltest du die Möglichkeit haben, Excel 2024, Excel 365 oder Excel Web zu verwenden, ließe sich die Formel deutlich kürzer und einfacher gestalten.
Gruß Daniel
AW: Automatische Aufteilung anteilige Umsätze nach Mitarbeitern
Piet
Hallo

ich war bisher nicht am Thread beteiligt, biete aber eine VBA Lösung an. PQ dürfte sicher die bessere Option sein.
Langfristig dürfte PQ die bessere Option sein, aber da musst du dich erst mal reinarbeiten.

Mein Beispiel ist so simpel aufgebaut, das es auch mit der derzeitigen Datenstruktur zurecht kommt!
Voraussetzung ist, das die Mitarbeiter in Spalte H-J mit dem Text der Spalte D (Verteilung) übereinstimmen.
Die Anzahl der Mitarbeiter ist unbegrenzt!! - Ich durchsuche die Spalte D nach diesen Überschriften.
Zum Schluß fügt das Makro noch am Ende der Liste Summenformel ein. So sieht man das Ergebnis.
https://www.herber.de/bbs/user/177279.xlsm

mfg Piet
AW: Automatische Aufteilung anteilige Umsätze nach Mitarbeitern
Selfy
Ich will automatisieren, dass ich per Formel je Mitarbeiter den prozentualen Monatsumsatz ausgeben kann.
AW: Automatische Aufteilung anteilige Umsätze nach Mitarbeitern
Sigi.21
Hallo Selfy,

damit man da irgendwas verbessern kann, musst du die Struktur und Eingabe ändern. (Bsp. s. Mappe)
https://www.herber.de/bbs/user/177272.xlsx

ruß Sigi
AW: Automatische Aufteilung anteilige Umsätze nach Mitarbeitern
Selfy
Hallo Sigi. Danke für den Input! Dass die Formatierung von Mitarbeiterkürzel + Prozentsatz fließend in einer Zelle problematisch ist, dachte ich mir schon. Deine Idee, wie du sie in deiner Tabelle dargestellt hast, ist schon echt gut. Der Problematische hierbei ist nur leider, dass wir eine Anzahl an monatlichen Rechnungen im dreistelligen Bereich schreiben, weshalb die Struktur, wie du sie aufgebaut ist, zu unübersichtlich werden würde, auch wenn mir die die grundsätzliche Idee sehr gut gefällt.

Grüße
Selfy
AW: Automatische Aufteilung anteilige Umsätze nach Mitarbeitern
Sigi.21
Hallo,

du hast doch über 16.000 Spalten und über 1 Mio. Zeilen. Wenn das sooo nicht reicht, dann dreh die Tabelle halt um. Hauptsache "MA und Prozent" stehen nicht in einer (1) Zelle!
(s. noch ein Bsp.)
https://www.herber.de/bbs/user/177276.xlsx

Gruß Sigi
Das würde mit...
Case
Moin, :-)

... Power Query gehen. Kommt das für dich in Frage? ;-)

Servus
Case
AW: Das würde mit...
Selfy
Hm, Power Query wäre komplettes Neuland für mich und nach dem Sichten von einigen Seiten im Netz sieht es erstmal so aus, als wäre das für einen (diesbezüglichen) Laien sehr aufwendig. Wie schätzt du das ein?
Also mit...
Case
Moin :-)

... Power Query arbeiten ist natürlich nicht in ein paar Minuten zu lernen. ;-)

Aber es bietet eine Fülle an Möglichkeiten: ;-)
https://excelhero.de/power-query/power-query-ganz-einfach-erklaert/

Um deine Vorgaben zu erfüllen, kann man die Schritte so aufteilen: ;-)
Daten laden.
Monat hinzufügen.
MA von Prozent trennen.
Spalte MA hinzufügen.
Spalte Prozente hinzufügen.
Spalte Umsatzbeteiligung hinzufügen.
Gruppieren.
Pivotieren.

In einer Beispieldatei sieht das dann so aus: ;-)
https://www.herber.de/bbs/user/177274.xlsx

Die einzelnen Schritte kannst du dir im PQ-Editor ansehen. ;-)

Und der Vorteil - du musst an deiner Datenstruktur nichts ändern und kannst auch andere Auswertungen vornehmen. ;-)

Servus
Case
AW: Also mit...
Yal
... ich würde das Gruppieren und Pivotieren nicht in Power Query selbst sondern durch die Ausgabe als Pivottable machen.
Dann sind die Auswertungsmöglichkeit noch grösser:

Userbild
Nicht sichtbar: die Monat sind in der Pivottable direkt aus der Datum aggregiert, ohne vorige Extraktion der Monat. Die Pivottable kann genauso daraus eine Quartalsumme machen, oder einzelne Tage darstellen/filtern. Ideal um die Ergebnisse zu prüfen.

VG
Yal
AW: Automatische Aufteilung anteilige Umsätze nach Mitarbeitern
Onur
Poste mal eine Datei dazu.