Otázky a odpovědi kolem spreadsheetů

Slyšel jsem, že si v MS Excelu mohu definovat vlastní výpočetní funkce. Můžete o tom sdělit něco bližšího? Ano, ...


Slyšel jsem, že si v MS Excelu mohu definovat vlastní výpočetní funkce. Můžete
o tom sdělit něco bližšího?
Ano, funkce definovat lze. K tomu je ale zapotřebí znát trochu programování ve
Visual Basicu, pomocí kterého můžeme funkci vytvořit. Zde je třeba se zmínit,
že je poněkud rozdílné prostředí pro psaní funkcí a subprocedur ve verzích
Excelu 5.0 a Excelu 95 na jedné straně a Excelu 97 na straně druhé. Princip je
však stejný. Pro Excel 5.0 a Excel 95 je potřeba vložit do souboru list modulu
pomocí nabídky Vložit, Makro, Modul. V Excelu 97 nejsou již příkazy psány na
samostatném listu. Editor Microsoft Visual Basicu se zobrazí nabídkou Nástroje,
Makro, Editor jazyka Visual Basic. Logika a postup práce jsou však obdobné. Pro
zjednodušení budeme předpokládat, že máme k dispozici Excel 5.0 nebo Office 95,
neboť jsou více rozšířeny. Po vložení listu modulu můžeme začít s definicí
funkce.
Do listu modulu můžeme zapsat velké množství definic funkcí, podprocedur,
procedur, vlastností, deklarací a instrukcí na úrovni modulu. Do každého sešitu
můžeme vložit více modulových listů. Každá podprocedura modulu může volat jinou
podproceduru na jiném modulu. Musí-me to však správně zapsat a dále musíme umět
nastavit v nabídce odkaz na externí soubor. Pokud toto zvládneme, můžeme volat
podprocedury z jiných sešitů, které nejsou třeba přímo otevřeny. Dále
upozorňuji, že funkce bude dostupná pouze tehdy, pokud bude soubor s touto
funkcí otevřen nebo odkazován.
Funkce
Nyní k samotné definici funkce. Každá funkce musí začínat příkazem Function
následovaným jménem a seznamem proměnných, který je uzavřen v závorkách. Každá
funkce musí končit příkazem End Function. Pokud používáme Excel 97, příkaz End
Function se zapíše sám. Dále bude zřejmě nejlépe vysvětlit definici funkce
názorným způsobem na příkladu.
Předpokládejme, že chceme používat funkci, která na základě odkazované
reference na nějakou buňku počítá či zobrazuje různé výsledky. Do Excelu je již
integrována známá podmínková logická funkce =KDYŽ (podmínka; pravda; nepravda)
(v anglické verzi =IF()). V dialogovém okně vyvolaném nabídkou Vložit, Funkce
nebo tuto funkci najdeme pomocí tlačítka Vložit funkci na standardním panelu
nástrojů ve skupině "Logické". Představme si, že do buňky A1 stávajícího listu
a souboru budeme zadávat pořadové číslo zaměstnance a na jiném místě (třeba v
buňce A5) budeme chtít zobrazit jeho jméno a příjmení. Je to příklad celkem
banální, navíc by bylo v níže uvedeném případě vhodnější použít integrované
vyhledávací funkce, jako je třeba funkce INDEX nebo funkce skupiny VYHLEDAT.
Pro pochopení našeho problému je však tento příklad poměrně vhodný. Pokud
použijeme integrovanou funkci KDYŽ, může zápis v buňce A5 vypadat třeba
takto:
=KDYŽ(A1=1; "Karel Nenadál"; "Petr Brousek"),
případně
=KDYŽ(A1=1;B1;B2),
pokud se jména nacházejí v příslušných buňkách sloupce B.
V tomto případě, pokud zadáme do buňky A1 číslo "1", objeví se v buňce A5
"Karel Nenadál", v ostatních případech "Petr Brousek". Představme si ale, že
chceme používat tři jména. Můžeme to vyřešit vložením další funkce KDYŽ jako
parametru do stávající funkce. Zápis potom bude vypadat takto:
=KDYŽ(A1=1; "Karel Nenadál"; KDYŽ(A1=2; "Petr Brousek"; "Michal Marek")),j
případně
=KDYŽ(A1=1;B1; KDYŽ(A1= 2;B2;B3)),
pokud se jména nacházejí v příslušných buňkách sloupce "B".
Takto bychom mohli postupovat dále, ale je zřejmé, že už i výše uvedený příklad
se jeví méně přehledným. Pokud bychom výše uvedenou funkci definovali v modulu,
zápis by vypadal následujícím způsobem:
Z výše uvedeného zápisu je jednak zřejmé, že zápis takto definované funkce bude
podstatně jednodušší, dále ze zápisu plyne, že podmínek tam může být zapsáno
více: Je však zapotřebí znát v tomto případě i zápis pro podmíněné větvení.
Pakliže např. zadáme příslušné číslo do buňky A1, můžeme do libovolné buňky
otevřených sešitů zapsat:
=PRACOVNÍK(A1) a získáme výsledek.
Z výše uvedených příkladů vyplývají pravidla zápisu (syntaxe) funkce:
Syntax zápisu pro podmíněné větvení je tato:
Příklad: Pro lepší přiblížení procesu definování vlastních funkcí uvedeme ještě
příklad čtyřparametrové funkce pro výpočet složitého výrazu. Syntaxe funkce na
listu modulu je:
Function Komplikované_počítání (x, y, z, a)
Komplikované_počítání = ((x + y + z)/ x * y * z) ^ (1/a^x)
End Function
Do buňky zadáme:
=KOMPLIKOVANÉ_POČÍTÁNÍ(A1; A2; A3; A4),
pokud jsme příslušné hodnoty x, y, z, a zadali do buněk A1, A2, A3, A4.
Závěrem
Na závěr je vhodné poznamenat, že v průvodci funkcemi vyvolaném nabídkou
Vložit, Funkce nebo pomocí tlačítka Vložit funkci na standardním panelu
nástrojů, najdeme námi definovanou funkci ve skupině Vlastní, ale pouze tehdy,
je-li soubor s definicí funkce otevřen nebo připojen odkazem.
Pokud chceme zahrnout funkci do jiné skupiny, než je skupina Vlastní,
postupujeme následovně:
lVyvoláme dialogové okno pro úpravu maker pomocí nabídky Nástroje, Makro.
lNahoru do pole Název makra zapíšeme název funkce.
lV dolní části dialogového okna vybereme příslušnou kategorii funkcí, do které
chceme naši funkci zahrnout.
lPotvrdíme a uložíme soubor.
8 0289 / Maf









Komentáře
K tomuto článku není připojena žádná diskuze, nebo byla zakázána.