Hazardní hryUžitečné › Makro na vytvoření rozpisu v Excelu

Makro na vytvoření rozpisu v Excelu

Excel obsahuje řadu užitečných funkcí, které nám umožňují zjistit počet kombinací, permutací či variací. Někdy však spíše než celkový výsledek potřebujeme vytvořit rozpis všech možných uspořádání, abychom s daty mohli pracovat dle potřeby. Ukážeme si excelovské makro, které dokáže rozpis všech možností vygenerovat.

Tento postup jsme s výhodou využili například u číselné loterie Kostky od společnosti Sazka, kde se mj. sází na součty šesti klasických kostek. Určit počet všech možností je poměrně jednoduché: 6= 46 656. Ale spočítat, kolika způsoby lze například vytvořit součet 21 již tak jednoduché není (možností je přes 4000). Lze to zjistit například pomocí permutací, ale řešení je to trochu kostrbaté, časově náročné. Nejjednodušším způsobem bylo nechat Excel vygenerovat rozpis všech 46 656 možných uspořádání kostek – viz ilustrace:

1 1 1 1 1 1
1 1 1 1 1 2
1 1 1 1 1 3
……………
6 6 6 6 6 6

Pak již lze snadno doplnit sedmý sloupec „Součet“ a pracovat s běžnými excelovskými funkcemi – například pomocí funkce COUNTIF zjistit četnosti, tj. kolikrát je každý součet v rozpisu zastoupen (dalo by se samozřejmě poupravit i makro, aby zrovna generovalo i součet řádku v 7. sloupci). Jak ale na to? Postup je poměrně jednoduchý a dá se shrnout do několika bodů, které si dále podrobněji popíšeme:

  1. Spusťte si Excel;
  2. Aktivujte si kartu „Vývojář“, kde je Visual Basic a makra;
  3. Vložte makro (kód);
  4. Spusťte makro (kód);
  5. Zkontrolujte vygenerovaný rozpis.

Aktivace karty Vývojář v Excelu

Spusťte si Excel. Nyní budeme potřebovat Visual Basic, který je na kartě Vývojář. Tato karta je však po instalaci Office standardně deaktivována a je třeba si ji zapnout. Běžte na Soubor / Možnosti / Přizpůsobit pás karet a napravo v menu Hlavní karty zatrhněte políčko Vývojář.

Aktivace karty Vývojář v Excelu

Spuštění Visual Basicu

Přepněte se na kartu Vývojář a klikněte na ikonu Visual Basic. Je možné použít i klávesovou zkratku Alt+F11.

Visual Basic v Excelu (karta Vývojář)

Vložení a spuštění kódu (makra) pro vygenerování rozpisu

Spustí se nám okno s Visual Basicem. Nyní můžeme provést tři kroky najednou.

  1. Nalevo v průzkumníku projektu poklepejte na List1 (List1) pod Microsoft Excel Objects.
  2. Otevře se vám bílá plocha napravo. Do ní zkopírujte a vložte kód, který najdete níže na této stránce.
  3. Spusťte kód tlačítkem „Play“ (nebo klávesovou zkratkou F5).

Vložení a spuštění makra pro vygenerování rozpisu

Kód (makro) pro vygenerování rozpisu

Označte a zkopírujte si (CTRL+C) následující kód (makro) pro vygenerování všech možných uspořádání šesti klasických kostek a vložte jej (CTRL+V) do spuštěného okna s Visual Basicem (krok 2).

Sub SestKostek()
 
Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim d As Integer
Dim e As Integer
Dim f As Integer
Dim n As Long
 
n = 1
 
For a = 1 To 6
    For b = 1 To 6
        For c = 1 To 6
            For d = 1 To 6
                For e = 1 To 6
                    For f = 1 To 6

                    Cells(n, 1).Value = a
                    Cells(n, 2).Value = b
                    Cells(n, 3).Value = c
                    Cells(n, 4).Value = d
                    Cells(n, 5).Value = e
                    Cells(n, 6).Value = f
                    
                    n = n + 1
                    
                    Next f
                Next e
            Next d
        Next c
    Next b
Next a
 
End Sub

Pokud byste chtěli, aby se vám zrovna vygenerovaly i součty šesti kostek v 7. sloupci, doplňte před výraz n = n + 1 ještě kód Cells(n, 7).Value = a + b + c + d + e + f.

Zkontrolujte výsledek

Po spuštění kódu (makra) bude Excel chvíli pracovat v závislosti na rychlosti vašeho počítače a nakonec vygeneruje rozpis všech 46 656 možností, tedy od samých jedniček po samé šestky – viz ukázka prvních 18 řádků.

Vygenerovaný rozpis šesti kostek

Jak makro pracuje a jak si jej případně upravit?

Vygenerování rozpisu je docíleno pomocí šesti cyklů (For Next). Áčko („a“) generuje hodnoty na první kostce, béčko („b“) na druhé kostce, až „f“ na šesté kostce. „n“ je počet řádků rozpisu, který se zvyšuje o jedničku v každém cyklu. Cells(n, 1) až Cells(n, 6) jsou souřadnice excelových buněk, do kterých se po řadě doplňují hodnoty af. V každém řádku se až do konce úlohy doplňují hodnoty proměnných a, b, c, d, e, f, a to od konce. Jakmile f dosáhne šestky, zvýší se e o jedničku a k nim se opět přiřadí hodnoty 1 až 6 atd.

Pokud byste chtěli vytvořit rozpis například jen pro tři kostky, odstraňte z kódu poslední tři cykly (d až f) a všechny části kódu, kde se proměnné d a f vyskytují. Jestliže potřebujete zvýšit počet generovaných čísel, například v prvním sloupci (nyní by to již nebyla klasická kostka) z šesti na dvacet, upravte cyklus a následovně For a = 1 To 20. Obdobně můžete upravit rozsah generovaných hodnot i v ostatních sloupcích.

Uvedený kód lze využít, i když potřebujete rozepsat všechny možné kombinace či variace písmen, například a a a, a a b atd. Jednoduše by to šlo ošidit hromadným nahrazením (CTRL+H) jedniček áčkem, dvojek béčkem apod., bez nutnosti psát kód, kde by na místo čísel byly textové řetězce.

Mohlo by vás také zajímat:

Kombinatorika | Variace | Permutace | Kombinace | Statistika;
Převod systému souborů FAT32 na NTFS aneb Jak na flashdisk dostat soubor větší než 4 GB;
→ Co je hazardní hra a morální hazard?
Zdanění výher v Česku.

 
Copyright © 2007–2017 Jindřich Pavelka, Hazardní-Hry.eu – O webu | Reklama | Přístupnost | Podmínky používání | Mapa stránek | EN | FB |