Frage von Vital123, 36

Hat jemand eine Excel Formel für eine Personenvertretung?

Hallo Leute, ich habe da ein kleines Problem. Ich muss eine Tabelle für die Arbeit erstellen und weiß nicht richtig wie. Mir fehlt eine Formel.

Folgende Situation:

Paul = Arbeitsanteil 8 Std. täglich = 100 % (bearbeitet 10 Endnummern = 1, 2, 3, ... 8, 9, 10) Emily = Arbeitsanteil 7 Std. täglich = 87,5 % (bearbeitet 10 Endnummern = 1, 2, 3, ... 8, 9, 10) Ronald = Arbeitsanteil 6 Std. täglich = 75 % (bearbeitet 10 Endnummern = 1, 2, 3, ... 8, 9, 10) Melanie = Arbeitsanteil 6 Std. täglich = 75 % (bearbeitet 10 Endnummern = 1, 2, 3, ... 8, 9, 10) Sina = Arbeitsanteil 6 Std. täglich = 75 % (bearbeitet 10 Endnummern = 1, 2, 3, ... 8, 9, 10) Claudia= Arbeitsanteil 5 Std. täglich = 62,5 % (bearbeitet 10 Endnummern = 1, 2, 3, ... 8, 9, 10) Nina = Arbeitsanteil 4 Std. täglich = 50 % (bearbeitet 10 Endnummern = 1, 2, 3, ... 8, 9, 10) Tom = Arbeitsanteil 4 Std. täglich = 50 % (bearbeitet 10 Endnummern = 1, 2, 3, ... 8, 9, 10)

Wenn jetzt zum Beispiel Tom krankheitsbedingt ausfällt, müssen seine Nummern von den anderen vertreten werden. Und zwar jeder nach seinem entsprechenden Arbeitsanteil. So dass Paul mit 8 Std. täglich mehr Nummern vertreten muss als zum Beispiel die Nina mit nur 4 Std. täglich.

D. h. Die 10 ganze Nummern von Tom (insgesamt also 100 Ziffern 00, 10, 20, ...90, 01, 11, 21, ...91, 02, 12, 22, ... 99) müssen verteilt werden.

Arbeitsanteil von Paul, Emily ,Ronald, Melanie, Sina, Claudia und Nina zusammen ergibt 42 Std. täglich. 100 Ziffern/42 Std. = 2,38 pro Std. 2,388 (Stundenzahl von Paul) = 19,04 aufgerundet auf 19 für Paul. Bedeutet, dass Paul eine ganze Nummer und 9/10tel Ziffern vertritt 2,387 (Stundenzahl von Emily) = 16,6 aufgerundet auf 17 für Emily. Bedeutet, dass Emily eine ganze Nummer und 7/10tel Ziffern vertritt. 2,38*6 (Stundenzahl von Ronald) =14,2 aufgerundet auf 14 für Ronald. Bedeutet, dass Ronald eine ganze Nummer und 4/10tel vertritt. usw.

Das heißt: Paul würde z. B. die ganze "0" und "07, 17, 27, 37, 47, 57, 67, 77, 87" vertreten müssen. Emily würde z. B. die ganze "1" und "97, 08, 18, 28, 38, 48, 58" vertreten müssen. Ronald würde z. B. die ganze "2" und "68, 78, 88, 98 " vertreten müssen. Melanie würde z. B. die ganze "3" und "09, 19, 29, 39" vertreten müssen Sina würde z. B. die ganze "4" und "49, 59, 69, 79" vertreten müssen Claudia würde z. B. die ganze "5" und "89, 99" vertreten müssen Nina würde z. B. die ganze "6" vertreten müssen.

Meine Frage also: Wie kann ich eine Formel schreiben, dass je nachdem wer ausfällt, mir das Programm selbständig ausrechnet, wer welche Nummern und Ziffern zu vertreten hat entsprechend dem jeweiligen Arbeitskraftanteil.

Expertenantwort
von Ninombre, Community-Experte für Excel, 15

Eigentlich wollte ich schon schreiben, dass das mit Formeln nix wird. Dann hab ich es allerdings als Herausforderung gesehen.

Trotzdem ist es nicht die eine Formel, daher ein Beispielsheet
http://workupload.com/file/4YZGRAwv

Wenn es nicht die 8 Mitarbeiter sind, dann solltest Du die Formeln näher anschauen, um es erweitern zu können. Wenn es unklar ist bitte fragen. 

Es gibt zwei Tabellen:

1. Übersicht der Nummern je Mitarbeiter

2. Die Verteillogik: Zunächst Ermittlung wie viele Nummern wg. Ausfall verteilt werden müssen (zwar standardmäßig 10 je Mitarbeiter, kann aber auch geändert werden) und wer gem. Arbeitsanteil wie viele übernehmen muss. Ich hab aus der Frage entnommen, dass jede Zahl aus 10 Unterpunkten besteht. 10 Nummern entsprechen also 100 Unterpunkte, die zu verteilen sind.

Danach wird die Liste der Nummern des Kranken auf die verbleibenden verteilt. Hinsichtlich Vergabe werden die 100 Nummern je Mitarbeiter einfach der Reihe nach gem. Arbeitsanteil an die verbleibenden Mitarbeiter verteilt. Da eine Logik reinzubauen, dass noch schöne Päckchen geschnürt werden, übersteigt dann meine Fähigkeiten mit Formeln

Es funktioniert auch nur, wenn nur 1 MA krank ist. Wenn mehr als einer ausfallen soll, muss eine andere Logik her. (Als Workaround: Die Nummern des zweiten kranken Mitarbeiters temporär an die Liste des ersten kranken anhängen)

Bei bestimmten Zahlenverhältnissen kommt der letzte Mitarbeiter besser davon als die Kollegen, das ist meinem Verständnis nach aber beim Runden nicht zu vermeiden.

Zumindest die eine Formel sollte man erklären:

=WENN(UND(C$5="";ZEILE(B16)-15<=C$7);INDEX('Nummer je MA'!$A:$H;ZEILE(A16)-14+SUMMENPRODUKT(($B$7:B$7)*($B$4:B$5=""));VERGLEICH("x";$B$5:$I$5;0));"")

Ich geh über die Zeilenzahl, um die Nummern aufzulisten, die der Mitarbeiter in Vertretung übernehmen muss. Da die Liste in Zeile 16 beginnt, müssen 15 abgezogen werden, um den ersten Eintrag darzustellen.

Bedingung dafür, dass ein Eintrag erzeugt wird, ist also, dass der Mitarbeiter nicht abwesen ist (C5="") und die laufende Zeile kleiner ist als die Anzahl der Nummern, die der Mitarbeiter übernehmen muss. D.h.: Wenn die Zeilenanzahl die Zahl der zu übernehmenden Nummern überschreitet wird kein weiterer Eintrag mehr erzeugt. 

Es wird dann aus dem anderen Tabellenblatt die Nummern des kranken Mitarbeiters übernommen. Das ist etwas umständlicher: Index verweist auf die Matrix in der zweiten Tabelle.
Zeile:  Laufende Zeile der Zuordnung+ die Anzahl der Einträge, die den anderen Mitarbeitern bereits zugeordnet wurden. Das wird mit dem Summenprodukt ermittelt.
Spalte: Eintrag x suchen

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten