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é: 66 = 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 4 000). 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:
- Spusťte si Excel;
- Aktivujte si kartu „Vývojář“, kde je Visual Basic a makra;
- Vložte makro (kód);
- Spusťte makro (kód);
- 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ář.
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.
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.
- Nalevo v průzkumníku projektu poklepejte na List1 (List1) pod Microsoft Excel Objects.
- 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.
- Spusťte kód tlačítkem „Play“ (nebo klávesovou zkratkou F5).
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 SubPokud 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ů.
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 a až f. 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.