Office: (Office 365) Hilfe bei Formel-Vereinfachung/-Kürzung

Hallo zusammen,

ich stehe gerade auf dem Schlauch bei der Vereinfachung/Kürzung einer Formel.
Mein aktuelles Ziel ist es in Excel die Umsätze bei der Einstellung neuer Vertriebsmitarbeiter zu simulieren. Hierfür gibt es natürlich ein paar Vorgaben, was das ganze etwas komplexer macht - aber ich werde mein bestes geben das ganze im Folgenden möglichst verständlich darzustellen.
Für die Erläuterung beziehe ich mich auf die beigefügte Datei.
In B6:D20 wird eingetragen, wie viel Prozent des Zielumsatzes (siehe C26) neue Mitarbeiter je Quartal erzielen müssen. Die "Einarbeitungszeit beträgt in dem Beispiel 3 Jahre - danach fällt der Mitarbeiter aus der Kategorie "Neuer Mitarbeiter" und sein Umsatz wird nicht mehr berücksichtigt (daher in C20 die 0%, aber mehr dazu gleich).
Die Simulation der Umsätze soll auf Monatsbasis erfolgen, die Angabe der %-Sätze des Zielumsatzes quartalsweise. Für die Umrechnung von Monat in Quartal dienen die in Zeile 23 eingetragenen Zahlen.
In B23:CH29 findet die Simulation statt. Hier wird manuell im Bereich I26:T26 die vergangenen Umsätze aus dem Jahr 2022 eingetragen. Zelle H27 gibt die Mitarbeiteranzahl Ende 2021 an. In Zeile 28 werden nun die Anzahl neu eingestellter Mitarbeiter je Monat eingepflegt bzw. für die Zukunft simuliert (was passiert, wenn wir X Mitarbeiter in Monat Y einstellen?). Die eingetragene Formel soll nun schauen, wann ein neuer Mitarbeiter eingestellt wurde und je nachdem den Umsatz berechnen, den dieser Mitarbeiter zu leisten hat bzw. voraussichtlich erbringen wird - basierend auf dem Zeitraum seit der Einstellung und dem Zielumsatz. Wenn bspw. im Jan 22 ein neuer Mitarbeiter eingestellt wird, muss dieser im Jan 23 (d.h. im Y2 Q1 seit Einstellung) 7% vom Zielumsatz (1.000.000€) erzielen, also 70.000€. Im Jan 24 dann entsprechend 23,50% von 1.000.000€, also 235.000€. Im Januar 25 sind seine 3 Jahre "Einarbeitungszeit" vorbei, also fällt er aus der Betrachtung heraus (=0). In den ersten Spalten der Zeile 28 ist die Formel noch ganz gut überschaubar, aber mit zunehmender Spaltenzahl, vergrößert sich auch die Formel, da ich aktuell jeden Monat vor dem betrachteten Monat einzeln auf eine Neu-Einstellung überprüfe. Da die Formeln aber immer die selben bleiben und jeweils nur eine weitere Formelzeile ergänzt wird (und einige Bezüge geändert bzw. nach rechts verschoben werden), müsste es doch eine Möglichkeit geben, das ganze zu vereinfachen/kürzen, oder nicht?

Ich hoffe ich habe es einigermaßen verständlich erklärt und bedanke mich bereits im Voraus für eure Antworten!

VG André

 

Hallo André,

eine wesentlich kürzere Formel als Deine, kann ich Dir aufzeigen. Meine Formel ermittelt noch dazu neben Deinen Monatsvorgabewerten auch gleich noch die Jahressumme mit (Du hast nur die Summe der ersten 4 Monate berechnet). Doch sind Deine Ergebniswertvorgaben mE falsch. Denn danach würde bereits im 2. Jahr nicht 56% sondern 168% des Zielumsatzes gefordert (im 1. Jahr 39% und im 3. Jahr 300%!). Das ist doch sicherlich nicht gewollt?

Außerdem ist doch wahrscheinlich der Einstellungstermin nicht immer der 1.1. eines Jahres, oder? Deine "Quartalsvorgaben" gelten dann doch sicherlich für den Einstellungstermin und nicht für das Kalenderjahr, oder? Sinnvoll wäre es dann z.B. in Spalte B der Auswertungszeile das Einstellungsdatum vorzugeben. Dann kann ich Dir auch eine Formel aufzeigen.

 

Hallo Werner, vielen Dank für deine schnelle Antwort! Ja da hast du vollkommen Recht! Ich hatte die Simulation zuvor auf Quartalsebene und vergessen die Formeln entsprechend anzupassen, d.h.:
- Die Jahressumme muss natürlich alle 12 Monate mit einbeziehen
- Die Berechnung der Monatsumsätze muss jeweils durch 3 dividiert werden (da ein Quartal aus 3 Monaten besteht).
Mit den beiden Anpassungen sollte es dann passen - sofern ich nicht noch einen weiteren Fehler übersehen habe :)

Einstellungstermin ist nicht immer der 1.1., da hast du ebenfalls Recht. Die Quartalsvorgaben sollten aber dennoch passen, diese beziehen sich immer auf den jeweiligen Einstellungstermin. Dazu dienen wiederum die Werte in Zeile 23. Also in Zeile 27 wird eingetragen, in welchem Monat wie viele neue Mitarbeiter eingestellt werden. Hierauf basiert dann auch die Berechnung. Kleines Beispiel: Wir haben im Q4 2021 (Zelle H26) 10 Mitarbeiter. Ein weiterer Mitarbeiter wird nun im Juni 2023 eingestellt. D.h. in Zelle AA27 wird eine 1 eingetragen. Die Formel in Zelle AA28 schaut nun, wann in Zeile 26 zum ersten Mal eine 11 steht - also dieselbe Zahl wie in Zelle AA26. (Kleine Anmerkung hierzu: Die beiden "Formelblöcke" machen grundsätzlich das gleiche, nur dass dies beim zweiten "Block" mit XVerweis geschieht. Die "einfachere" Variante ohne XVerweis wird nur für das Jahr 2022 angewendet, aber da wir in dem genannten Beispiel keine Einstellungen in dem Jahr haben, überspringe ich den ersten "Formelblock" bei der Erläuterung). Wurde die Zahl gefunden - in unserem Beispiel in Zelle AA26, da wir uns ja im Monat der Einstellung befinden, schaut die Formel in die Zeile 23. Nun wird von der Zahl vom aktuell betrachteten Monat (in unserem Beispiel 6,3 -> Zelle AA23), die Zahl der Spalte subtrahiert, in der der zuvor gesuchte Wert gefunden wurde (in unserem Beispiel ebenfalls 6,3 in Zelle AA23).

Hier fällt mir gerade auf, dass ich die Abrunden-Funktion vergessen habe. Also es sollte die Subtraktion auf 0 stellen abgerundet werden. Sonst funktioniert das ganze mit den Monaten nicht -- sorry für die vielen Fehler). Also nach dem Prinzip:
WENNNV(SVERWEIS(WENN(ABRUNDEN(AA$23-XVERWEIS($AA26;$I26:$CH26;$I$23:$CH$23;;0;1);0)>12; 12;ABRUNDEN(AA$23-XVERWEIS($AA26;$I26:$CH26;$I$23:$CH$23;;0;1);0));$A$8:$D$20;3;FALSCH);0)*$C26*$AA27​

Zurück zur Funktionsweise der Formel: Nachdem die beiden Zahlen voneinander subtrahiert und abgerundet wurden, wird das Ergebnis in der Matrix mit den % Zielangaben (A8:D20) gesucht. In unserem Fall sucht die Formel also nach dem Wert 0 und gibt entsprechend 2,00% zurück. Dieser Prozentsatz wird nun mit dem Target Revenue von 1 Mio€ und der Anzahl neu eingestellter Mitarbeiter im entsprechenden Monat (hier 1) multipliziert und durch 3 dividiert (vgl. vorherige Anmerkung). Alle weiteren Formel-Zeilen ergeben 0, da in den vorigen Monaten ja niemand eingestellt wurde und das ganze damit immer mit 0 multipliziert wird.
Für die folgenden Monate funktioniert das ganze gleich. Nur wird jeweils eine weitere Zeile in der Formel für die entsprechende Spalte ergänzt. Also für Juli 23 ergeben alle Formelzeilen, bis auf die zweite Zeile des zweiten "Formelblockes" ebenfalls 0. Der Wert, den die Formel somit in Zeile 2 des zweiten Formelblockes in der Matrix (A8:D20) sucht, ist somit: 7,1 (AB23) minus 6,3 (AA23) = 0,8 --> ABGERUNDET 0 --> 2,00%. Somit ist das Ergebnis dasselbe wie im Jun 23. Ein anderes Ergebnis ergibt sich dann erstmals im September 23, also im Jahr 1, Quartal 2 seit der Einstellung. Hier sucht die Formel in der Matrix nach dem Wert 7,3-6,3 = 1 --> ABGERUNDET 1 --> 2,50%. Also die Matrix im Bereich A8:D20 bezieht sich immer auf den Monat der Einstellung. Wenn bspw. im März 25 ein/mehrere neue Mitarbeiter eingestellt werden, ist 03.25-05.26 das Y1Q1, 06.23-08.23 das Y1Q2, etc.

Ich hoffe ich konnte meinen Gedankengang bei der Formelerstellung einigermaßen verständlich erklären.
VG André

 

Hallo André,

ehrlich: ich hab mir Deine Formel(n) nicht näher angeschaut und auch Dein Beitrag nach dem 1. Absatz nur flüchtig überflogen.
Nachfolgend habe ich meine Formel auch gänzlich ohne Beachtung Deiner Daten in Zeile 23 und 26 (natürlich mit der Ausnahme C26) aufgebaut.

Aber wie schon geschrieben setzt meine Formel in B28:B## die Eingabe des jeweiligen Einstellungsdatums voraus.
Dafür nun folgende Formel in I28:

=WENN(LINKS(I$25;5)="Total";SUMME(INDEX(28:28;SPALTE()-12):H28);WENNFEHLER(INDEX($C$8:$C19;(KÜRZEN(DATEDIF($B28;I$25;"M")/3;)+1));0)*$C$26/3)
und diese ziehend weit genug nach rechts und unten kopieren.

Sollte ein Einstellung nach dem 1. des jeweiligen Monats erfolgen, dann beginnt die Zuweisung erst im Folgemonat.

 

Hallo Werner,

ich habe deine Formel gerade getestet und die grundsätzliche Funktionsweise ist schon ganz gut. Danke dafür!

Wäre es möglich:
1. Das Einstellungsdatum nicht als extra Eintrag in Spalte B angeben zu müssen, sondern aus der Zeile 27 herauszulesen? Also diese Zeile ist dafür da, in die verschiedenen Monate einzutragen, wie viele Mitarbeiter jeweils eingestellt wurden. Das entsprechende Einstellungsdatum steht in Zeile 25. Wenn ich bspw. im März 22 zwei Mitarbeiter einstelle, trage ich in Zelle K27 eine 2 ein.
2. Die Werte direkt in Zeile 28 aufsummiert darzustellen? Der gezeigte Ausschnitt ist nur ein Teil der Tabelle und wird schnell unübersichtlich wenn je eine Zeile für eine Neueinstellung dargestellt wird.
Hier wird es dann nämlich kompliziert für mich. Die Formel muss einerseits die Einstellungen im aktuellen, als auch in den vorangegangenen Monaten betrachten, abhängig vom jeweiligen Einstellungsdatum mit den Prozentsätzen in C8:C19 multiplizieren und alles aufsummieren. Hier wiederholt sich dann immer meine Formel.
In der Datei habe ich deine Formel in den ersten 4 Monaten einmal übernommen und entsprechend den Anmerkungen abgeändert. Es funktioniert so, aber wie gesagt, mit jeder Spalte wird die Formel immer länger und länger und ich muss immer eine Formelzeile manuell hinzufügen. Gibt es hier eine Möglichkeit das zu kürzen? (Wenn es mit den "normalen" Excel-Formeln nicht geht, vllt. mit einer eigens erstellten in VBA?)

VG André

 

Hallo André,

möglich wäre es schon, aber einerseits wird dann die Formel wesentlich komplizierter und anderseits würde ich Dir insofern widersprechen, das die Tabelle übersichtlicher wird. Denn mE ist eine zeilenweise Erfassung der Einstellungen wesentlich aussagefähiger.

Ich würde sogar außer dem Einstellungstermin in Spalte B der jeweiligen Zeile auch noch den (anteiligen? oder soll der Wert in C26 wirklich für jede Person gelten?) Umsatz je Person in der Spalte C der jeweiligen Zeile eintragen.

In der Zeile 23 oder 27 kannst Du dann die Summen der jeweiligen Spaltenwerte ab Zeile28 ermitteln. Die ermittelten Werte je Person kannst Du dann weiterhin mit der von mir aufgezeigten Formel berechnen lassen.

Diese Ergebniszeilen kannst Du ja gruppieren und darüber bei Bedarf aus- und einblenden.

 

Alles klar, dann werde ich versuchen das auf die simplere Weise umzusetzen.
Eine letzte Frage hätte ich da allerdings noch:
Wenn ich weiterhin die Eingabe in Zeile 27 belassen möchte (also je Monat die Anzahl Einstellungen eintragen), gibt es eine Funktion, die mir dann automatisch in Spalte B das Einstellungsdatum schreibt (also das Datum aus der Zeile 25), wenn in Zeile 27 eine 1 oder größer steht? (wenn in einem Monat eine 2 steht, müsste das Datum nach der Logik dann ja 2 mal erscheinen etc.). Im Besten Fall würde die Funktion noch jeweils eine neue Zeile in die Tabelle einfügen, wenn ein Mitarbeiter hinzugefügt wird (wie bereits gesagt geht die Tabelle nach unten weiter. Sonst müsste ich manuell immer eine neue Zelle einfügen)

 

Hallo André,

ja kann man. In Deiner XL-Version (die ich nicht habe) gibt es die Funktion FILTER(), damit kannst Du die Einstellungstermine, die Du mit einer Zahl in Zeile27 kennzeichnest, in die Spalte B übernehmen. In meiner Version würde ich es einfach mit AGGREGAT() ermitteln (wenn Du möchtest, kann ich diese natürlich aufzeigen).

Ich würde allerdings einen Eintragung in Zeile 27 als Termin in Spalte B stets nur einmal listen (egal wie viele Einstellungen am Termin erfasst wurden). Dafür würde ich in Spalte C den Vorgabewert (bei Dir in C26, wenn dieser dort für eine Person gültig sein soll; was Du bisher noch nicht eindeutig angegeben hast) mit der Anzahl der Einstellungen multiplizieren und meine Formel nicht mehr auf $C$26 sondern auf $C## der jeweiligen Datenzeile beziehen lassen.

 

Hey Werner, danke für die Antwort.
Ja der Wert in C26 bezieht sich auf den Zielumsatz je Mitarbeiter. Also wenn in einem Monat 2 neue MA eingestellt werden, beläuft sich das Ziel auf 2 Mio €. Ich ergänze in deiner Formel dann einfach *I$27 am Ende (vor der Klammer zu), dann habe ich die Anzahl ja mit drin :).

Ich habe die Filter-Funktion gerade getestet. Genau das habe ich gesucht, vielen Dank! Allerdings sollte die Auflistung vertikal und nicht horizontal erfolgen. Lässt sich das irgendwie umstellen?

 

Hallo André,

wie geschrieben ich hab XL365 nicht. Kann mir aber nicht vorstellen, dass die FILTER()-Funktion nicht so definiert werden kann, wie notwendig. Evtl. musst Du noch MTRANS() einbinden. Allerdings wäre dann eine Listung mit AGGREGAT() wieder einfacher. Jedoch wrd diese dann wahrscheinlich nicht "spillen."

 

Ah super, nach etwas rumprobieren funktioniert das ganze mit MTRANS().
Sieht dann wie folgt aus: =MTRANS(FILTER(I25:CH25;I27:CH27>=1)) - als Array bestätigt.
Das wäre es vorerst mit meinen Fragen. Ich versuche das ganze nun umzusetzen und bei eventuell auftretenden Problemen melde ich mich nochmals. Wirklich vielen lieben Dank dir!
VG André

 

Bitteschön

 

Das sollte eigentlich nicht nötig sein...

 

Hallo Helmut,

ich kann es nicht prüfen, da ich kein XL365 habe. Allerdings erzwingt in älteren XL-Versionen der zusätzliche Einsatz von MTRANS() generell den Matrixformelabschluss. "Wundern" tut mich auch schon etwas, das FILTER() überhaupt noch MTRANS() benötigt.

 

Nun ja, hier filtert FILTER() ja Spalten (weil nur eine Zeile vorgegeben ist). André hätte aber das Filtrat gerne in Zeilen. Dann muss man halt Transponieren. Das hat nichts mit FILTER zu tun.