Formulace problému
Předpokládejme, že potřebujeme vytvořit množinu celočíselných náhodných čísel bez opakování v daném rozsahu hodnot. Příklady na cestách:
- generování jedinečných náhodných kódů pro produkty nebo uživatele
- přiřazování lidí k úkolům (každý náhodně ze seznamu)
- permutace slov ve vyhledávacím dotazu (ahoj seo-shnikam)
- hrát loto atd.
Metoda 1. Jednoduchá
Pro začátek zvažme jednoduchou možnost: potřebujeme získat náhodnou sadu 10 celých čísel od 1 do 10. Pomocí funkce zabudované v Excelu MEZI PŘÍPADEM (HRANA MEZI) jedinečnost není zaručena. Pokud jej zadáte do buňky listu a zkopírujete o 10 buněk, může se snadno opakovat:
Proto půjdeme jinou cestou.
Všechny verze Excelu mají funkci HODNOST (RANG), určený k řazení nebo jinými slovy určení nejvyšší pozice čísla v sadě. Největší číslo v seznamu má rank=1, druhé nahoře má rank=2 a tak dále.
Zadáme funkci do buňky A2 SLCHIS (RAND) bez argumentů a zkopírujte vzorec o 10 buněk dolů. Tato funkce nám vygeneruje sadu 10 náhodných zlomkových čísel od 0 do 1:
V dalším sloupci představíme funkci HODNOSTk určení pozice v žebříčku pro každé obdržené náhodné číslo:
Dostaneme do sloupce B to, co jsme chtěli – libovolný počet neopakujících se náhodných celých čísel od 1 do 10.
Čistě teoreticky může nastat situace, kdy SLCHIS nám dá dvě stejná náhodná čísla ve sloupci A, jejich pořadí se bude shodovat a dostaneme opakování ve sloupci B. Pravděpodobnost takového scénáře je však extrémně malá, vzhledem k tomu, že přesnost je na 15 desetinných míst.
Metoda 2. Složitá
Tato metoda je o něco složitější, ale používá pouze jeden maticový vzorec. Řekněme, že potřebujeme vytvořit seznam 9 neopakujících se náhodných celých čísel v rozsahu od 1 do 50 na listu.
Do buňky A2 zadejte následující vzorec, klikněte na konec Ctrl + Shift + Enter (chcete-li jej zadat jako maticový vzorec!) a zkopírujte vzorec dolů na požadovaný počet buněk:
Metoda 3. Makro
A problém samozřejmě můžete vyřešit pomocí programování ve Visual Basicu. V jednom ze starých článků o náhodném vzorkování jsem již citoval funkci makra pole Lotto, která produkuje požadovaný počet náhodných neopakujících se čísel z daného intervalu.
- Jak spočítat počet jedinečných hodnot v rozsahu
- Náhodný výběr prvků ze seznamu