Samouczek programu Excel: jak losowo przypisywać osoby do zespołów

W tym filmie przyjrzymy się podstawowym formułom losowego przydzielania zespołów ludzi.

Tutaj mamy listę 36 osób.

Powiedzmy, że chcemy losowo przypisać każdą osobę do zespołu składającego się z 4 osób, tak aby w sumie było 9, po 4 osoby w każdej.

Mam zamiar rozwiązać ten problem małymi krokami, za pomocą kolumn pomocniczych, a na końcu zebrać wszystko razem. To świetny sposób na rozwiązanie bardziej skomplikowanych problemów w programie Excel.

Zacznę od tabeli Excela, aby bardzo szybko wprowadzić formuły.

Następnie dodam kolumny dla Rand, rangi, grupowania i numeru zespołu. Cel każdej kolumny stanie się jasny w miarę postępów.

Następnie użyję funkcji RAND, aby przypisać losową liczbę każdej osobie. RAND generuje małe liczby od zera do 1.

RAND()

RAND jest funkcją zmienną, więc będzie obliczana ponownie przy każdej zmianie arkusza. Nie chcemy takiego zachowania, więc użyję polecenia paste special, aby przekonwertować formuły na wartości.

Następnie użyję funkcji RANK, aby uszeregować każdą osobę według ich losowej liczby. RANK wymaga liczby i listy liczb, według których ma być klasyfikowana.

RANK((@rand),(rand))

Wynikiem jest lista liczb od 1 do 36, gdzie 1 reprezentuje największą wartość, a 36 oznacza najmniejszą.

Zbliżamy się.

Potrzebujemy tylko sposobu na grupowanie według rangi.

Zrobię to, dzieląc rangę przez wielkość zespołu, która wynosi 4.

RANK((@rand),(rand))/4

Daje to trochę bałaganu, ale teraz mamy to, czego potrzebujemy.

Jeśli zaokrąglimy te liczby w górę, uzyskamy numery zespołów od 1 do 9. To idealna praca dla funkcji CEILING, która zaokrągla w górę do podanej wielokrotności.

Muszę podać liczbę CEILING i określić wielokrotność 1 i mamy nasze zespoły.

=CEILING((@grouping),1)

Teraz, aby upewnić się, że to działa poprawnie, użyję funkcji LICZ.JEŻELI do zliczania członków zespołu.

Następnie zamienię zakodowany rozmiar zespołu na odniesienie.

RANK((@rand),(rand))/$F$5

Teraz, kiedy zmieniam wielkość zespołu, wszystko nadal działa.

Na koniec skonsoliduję formuły.

Najpierw skopiuję formułę grupowania.

=CEILING(@rank)/$F$5,1)

Następnie skopiuję wzór rangi.

=CEILING(RANK((@rand),(rand))/$F$5,1)

Teraz mogę usunąć dwie kolumny pomocnika.

Aby w dowolnym momencie wygenerować nowe zespoły, mogę ponownie użyć funkcji LOS.

Kierunek

Podstawowa formuła

Powiązane skróty

Wstaw tabelę Ctrl + T ⌃ + T Usuń kolumny Ctrl + - ⌘ + -