Korzystanie z tabeli przestawnej programu Excel do przypisywania liczb losowych
Okresowo może zaistnieć potrzeba przypisania losowych numerów do listy pracowników, np. w przypadku losowania lub testowania leków. W tym artykule pokażę, w jaki sposób można wykorzystać funkcję RANDBETWEEN wraz z tabelą przestawną programu Excel do ciągnięcia losowego asortymentu pracowników.
Funkcja RANDBETWEEN została wprowadzona w programie Excel 2007 i ma dwa argumenty:
Dół – Najmniejsza liczba losowa, jaką można przypisać
Góra – Największa liczba losowa, jaką można przypisać
Tak więc wzór taki jak =RANDBETWEEN(1,1000) wygenerowałby liczby losowe od 1 do 1,000. Możesz być zaznajomiony z funkcją RAND, która generuje liczby losowe z przedziału od 0 do 1. Tak więc historycznie użytkownicy Excela stworzyliby formułę taką jak =RAND()*1000 do generowania liczb losowych.
Zarówno RANDBETWEEN, jak i RAND znane są w programie Excel jako funkcje zmiennego arkusza, co oznacza, że wynik zmienia się za każdym razem, gdy dokonujesz jakichkolwiek zmian w arkuszu Excel. Aby zobaczyć to z pierwszej ręki, można zebrać kilka przykładowych danych, jak pokazano na Rysunku 1:
W pustym arkuszu wpisz słowo Pracownik w komórce A1.
Wpisz słowo Random w komórce B1.
Wpisz słowo Pracownik1 w komórce A2.
Za pomocą uchwytu do wypełniania w komórce A2 przeciągnij zawartość komórki w dół do A21, co powinno spowodować przejście serii Pracownik1 do Pracownik20.
Wybierz komórki B2 do B21, typ =RANDBETWEEN(1,1000), a następnie naciśnij Ctrl-Enter, aby wypełnić wszystkie komórki jednocześnie.
Liczby te będą się zmieniać za każdym razem, gdy naciśniesz klawisz F9, który ponownie obliczy arkusz, lub gdy będziesz edytować dowolną komórkę w arkuszu. Aby zatrzymać liczby, należy nacisnąć klawisz Ctrl-C.
Kliknij prawym przyciskiem myszy na komórkę B2.
Kliknij ikonę Wklej wartości w programie Excel 2010 i później. W programie Excel 2007 wybierz opcję Wklej wartości specjalne, a następnie kliknij dwukrotnie na wartości.
W tym momencie losowe numery są przydzielane na stałe. Ze względu na ich zmienną naturę liczby te mogą się jeszcze raz zmienić po wklejeniu ich jako wartości.
Kliknij polecenie Cofnij lub naciśnij Ctrl-Z, aby przywrócić formuły, dzięki czemu nie będziesz musiał ich ponownie odtwarzać.
Rysunek 1: Funkcja RANDBETWEEN przypisuje losowe liczby w arkuszu kalkulacyjnym.
Praca z liczbami losowymi może sprawiać wrażenie próby trafienia w ruchomy cel, ale możemy użyć tabeli przestawnej w Excelu, aby uzyskać przerwę od akcji, jak pokazano na rysunku 2:
Kliknij dowolną komórkę z listy pracowników.
Aktywuj menu Wstaw programu Excel.
Kliknij polecenie Tabela.
Kliknij OK, aby przekształcić listę w tabelę.
Kliknij przycisk Podsumuj z PivotTable w menu Projektowanie.
Kliknij OK, aby utworzyć tabelę przestawną.
Kliknij pole wyboru Pracownik na liście pól PivotTable.
Kliknij pole wyboru Random (Losowo) na liście pól PivotTable.
Rysunek 2: Tabela przestawna umożliwia zamrożenie liczb losowych przy jednoczesnym zachowaniu wzorów.
W odróżnieniu od formuł w arkuszu roboczym, wyniki w tabeli przestawnej zmieniają się tylko wtedy, gdy odświeżasz tabelę przestawną. Dzięki temu możemy «zamrozić» nasze losowe liczby do czasu, aż będziemy chcieli je ponownie przydzielić. Rysunek 3 pokazuje, jak wybrać grupę 5 pracowników z listy, a także jak wybrać inny losowy zestaw 5 w dowolnym momencie:
Kliknij przycisk Filter w polu Row Labels.
Wybierz opcję Filtry wartości.
Chose Top 10.
Ustaw liczbę pracowników, których chcesz oglądać, np. 5.
Kliknij OK.
Tabela obrotowa pokazuje teraz 5 pracowników, którym przydzielono najwyższe liczby losowe.
Kliknij prawym przyciskiem myszy dowolną komórkę w tabeli przestawnej.
Wybierz opcję Refresh.
W tabeli obrotowej przedstawiono nowy, losowy zestaw 5 pracowników.
Rysunek 3: Filtr Top 10 w tabeli przestawnej automatycznie wybiera losowy zestaw pracowników przy każdym odświeżeniu tabeli przestawnej.
Biorąc pod uwagę, że użyliśmy funkcji Tabela z naszą listą, idąc dalej można po prostu dodać nowe nazwiska na dole listy pracowników. Funkcja Tabela automatycznie skopiuje formułę RANDBETWEEN w dół. Po kliknięciu prawym przyciskiem myszy i odświeżeniu tabeli obrotowej, wszyscy nowi pracownicy zostaną włączeni do puli losowej.
