Excel Problem: Zufällige Gruppierung mit gewissen Anteilen pro Gruppe?

Hallo,

ich habe eine Mitarbeiterliste mit 432 Datensätzen. In Spalte A die Namen und in Spalte B jeweils die entsprechende Abteilung, in die der Mitarbeiter arbeitet (Sales, Controlling, Marketing, Backoffice, Warehouse, Service).

Die Teilnehmer sollten jetzt in jeweils 5er Gruppen eingeteilt werden und das jeden Monat (zufällig) aufs Neue. Zufällig in Klammer, da geschaut werden sollte, dass die Verteilung innerhalb der Gruppen stimmt, anteilig ihrer Größe.

In Controlling, Marketing und Backoffice arbeiten jeweils ca.10 % (~43 Mitarbeiter), im Backoffice und Warehouse jeweils ~20 % (~86 MA) und im Sales ~30 % (~ 130 MA).

Ich habe es bereits hinbekommen, eine zufällige Verteilung zu machen.

1. In der Ursprungsliste (Name auf A und Abteilung auf B) hab ich in Spalte C eine =RAND() z.dt. =ZUFALLSZAHL() generieren lassen
2. In einem anderen Register habe ich die Zufallszahlen sortieren lassen nach Größe und hab damit eine erste zufällige Aufteilung: =INDEX(Ursprungsliste!$A:$A;MATCH(SMALL(Ursprungsliste!C:C;ROW()-3);Ursprungsliste!C:C;0);1) z.dt. MATCH=VERGLEICH & SMALL=KKLEINSTE

Jetzt habe ich manche Gruppen die rein aus Sales-Mitarbeitern bestehen und andere die keine Sales-Mitarbeiter beinhalten. Macht ja auch Sinn, weil zufällig, aber gibt es da irgendwie eine Möglichkeit?

Bestenfalls ohne VBA, wenn das geht.

Comments

[PWolff]

D. h. die Verteilung der Abteilungen in jeder Gruppe soll so nahe wie möglich an der Verteilung der Abteilungen in der Gesamtheit der Mitarbeiter sein?

[Simoo21]

Ja, richtig :-) Sodass wir gut durchmischte Gruppen erhalten.

Answer 1

[PWolff]

Man könnte die Liste aller Mitarbeiter erst einmal nach Abteilungen sortieren und dann jede Abteilung für sich auf die 5 Gruppen aufteilen.

Andererseits muss man irgendwie herausfinden, wie viele Mitarbeiter aus jeder Abteilung in jede der Gruppen gehören - diese Werte können ja auch um ±1 schwanken. Hier eine brauchbare Zufallsverteilung zu finden halte ich für die größte Schwierigkeit hier. (Hier müssen wir auch noch entscheiden, ob wir lieber um 1 größere Gruppen oder um 1 kleinere Gruppen haben wollen.)

Der Rest sind dann Dinge, die zwar Konzentration und ein paar Hilfsspalten erfordern, aber (für mich) ziemlich Standard sind.