Vytváranie vlastných funkcií v Exceli

Hoci Excel ponúka množstvo vstavaných funkcií hárka, môže sa stať, že nemá funkciu pre typ výpočtu, ktorý vykonávate. Návrhári Excelu nemohli predvídať potreby každého používateľa týkajúce sa výpočtov. Namiesto toho vám Excel poskytuje možnosť vytvárať vlastné funkcie. Viac sa o nich dozviete v tomto článku.

Vytvorenie jednoduchej vlastnej funkcie

Vlastné funkcie, ako napríklad makrá, používajú programovací jazyk Visual Basic for Applications (VBA ). Od makier sa odlišujú v dvoch zásadných vlastnostiach. V prvom rade využívajú procedúry Function namiesto procedúr Sub. Znamená to, že sa začínajú príkazom Function namiesto príkazu Sub a končia sa príkazom End Function namiesto príkazu End Sub. Druhou odlišnosťou je, že namiesto akcií vykonávajú výpočty. Niektoré druhy príkazov, napríklad tie, ktoré vyberajú a formátujú rozsahy, nie je možné vo vlastných funkciách využívať. V tomto článku sa dozviete, ako môžete vytvárať a používať vlastné funkcie. Funkcie a makrá budete vytvárať v editore jazyka Visual Basic (VBE), ktorý sa otvorí v samostatnom okne mimo Excelu.

Predpokladajme, že vaša spoločnosť ponúka množstvovú zľavu vo výške 10 % na určitý výrobok pri objednaní viac ako 100 jednotiek. V nasledujúcich odsekoch nájdete popis funkcie na výpočet tejto zľavy.

V príklade nižšie je zobrazený objednávkový formulár, v ktorom sú uvedené jednotlivé položky, množstvo, cena, zľava (ak sa uplatňuje) a výsledná navýšená cena.

Ak chcete v tomto zošite vytvoriť vlastnú funkciu DISCOUNT, postupujte takto:

1. Stlačením kombinácie klávesov Alt + F11 otvorte editor jazyka Visual Basic (v Macu stlačte kombináciu klávesov FN + ALT + F11) a potom kliknite na položku Vložiť>modul. Na pravej strane editora jazyka Visual Basic sa zobrazí okno nového modulu.

2. Skopírujte a prilepte nasledujúci kód do nového modulu.

   Function DISCOUNT(quantity, price)
    If quantity >=100 Then
    DISCOUNT = quantity * price * 0.1
    Else
    DISCOUNT = 0
    End If
   
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   
   

Používanie vlastných funkcií

Teraz ste pripravení na používanie novej funkcie DISCOUNT. Zavrite editor jazyka Visual Basic, vyberte bunku G7 a zadajte nasledujúci vzorec:

=DISCOUNT(D7;E7)

Excel vypočíta zľavu vo výške 10 % na 200 jednotiek pri cene 47,50 EUR za jednotku a vráti sumu 950,00 EUR.

V prvom riadku kódu VBA, funkcia DISCOUNT(množstvo; cena), ste uviedli, že funkcia DISCOUNT si vyžaduje dva argumenty, množstvo a cenu. Keď v bunke hárka voláte funkciu, musíte zadať obidva argumenty. Vo vzorci =DISCOUNT(D7;E7) predstavuje D7 argument množstvo a E7 argument ceny . Teraz môžete skopírovať vzorec DISCOUNT do buniek G8 až G13. Získate výsledky zobrazené nižšie.

Poďme sa pozrieť na to, ako Excel interpretuje túto funkčnú procedúru. Keď stlačíte kláves Enter, Excel začne v aktuálnom zošite hľadať názov DISCOUNT a zistí, že ide o vlastnú funkciu v module VBA. Názvy argumentov v zátvorkách, množstvo a cena, sú zástupnými objektmi pre hodnoty, ktoré sú potrebné na výpočet zľavy.

Príkaz If v nasledujúcom bloku kódu slúži na preskúmanie argumentu quantity a určuje, či je počet predaných položiek väčší alebo rovný 100:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Ak je počet predaných položiek väčší alebo rovný 100, jazyk VBA spustí nasledujúci príkaz, ktorý vynásobí hodnotu množstvo hodnotou cena a výsledok vynásobí hodnotou 0,1:

Discount = quantity * price * 0.1

Výsledok sa uloží ako premenná Discount. Príkaz v jazyku VBA, ktorý ukladá hodnotu do premennej, sa nazýva príkaz priradenia, pretože jeho úlohou je vyhodnotiť výraz na pravej strane rovnosti a výsledok priradiť k názvu premennej na ľavej strane. Keďže premenná Discount má rovnaký názov ako funkčná procedúra, hodnota uložená v premennej sa vráti do vzorca hárka, ktorý spustil funkciu DISCOUNT.

Ak je množstvo menšie ako 100, jazyk VBA spustí nasledujúci príkaz:

Discount = 0

Nakoniec nasledujúci príkaz zaokrúhli hodnotu priradenú premennej Discount na dve desatinné miesta:

Discount = Application.Round(Discount, 2)

Jazyk VBA neponúka funkciu ROUND, ale Excel ju má. Ak teda chcete v tomto príkaze použiť funkciu ROUND, musíte jazyku VBA prikázať vyhľadať metódu zaokrúhlenia (funkciu) v objekte Application (Excel). Môžete to urobiť pridaním slova Application pred slovo Round. Túto syntax použite vždy, keď potrebujete získať prístup k funkcii Excelu z modulu VBA.

Informácie o pravidlách vlastných funkcií

Vlastná funkcia sa musí začínať príkazom Function a končiť príkazom End Function. Príkaz Function okrem názvu funkcie zvyčajne špecifikuje aj jeden alebo dva argumenty. Môžete však vytvoriť funkciu bez argumentov. Excel obsahuje niekoľko vstavaných funkcií, ktoré nevyužívajú argumenty. Ide napríklad o funkcie RAND a NOW.

Po príkaze Function nasleduje vo funkčnej procedúre jeden alebo viac príkazy VBA, ktoré robia rozhodnutia a vykonávajú výpočty pomocou argumentov použitých vo funkcii. Nakoniec musíte niekde do funkcie zadať príkaz, ktorý priradí hodnotu k premennej s rovnakým názvom ako funkcia. Táto hodnota sa vráti do vzorca, ktorý vyvoláva funkciu.

Používanie kľúčových slov VBA vo vlastných funkciách

Vo vlastných funkciách môžete použiť menej kľúčových slov VBA ako v makrách. Vlastné funkcie môžu len vracať hodnotu do vzorca v hárku alebo do výrazu použitého v inom makre alebo funkcii jazyka VBA. Vlastné funkcie nemôžu napríklad meniť veľkosť okien, upravovať vzorec v bunke ani meniť písmo, farbu či možnosti vzoru textu v bunke. Ak vo funkčnej procedúre zahrniete kód akcie tohto typu, funkcia vráti #VALUE! Ak je zadané umiestnenie pred prvou alebo za poslednou položkou v poli, výsledkom vzorca bude chybová hodnota #ODKAZ!.

Jedinou akciou, ktorú funkčná procedúra dokáže vykonávať (okrem vykonávania výpočtov), je zobrazovanie dialógového okna. Príkaz InputBox môžete použiť vo vlastnej funkcii ako prostriedok na získanie vstupu od používateľa, ktorý spúšťa funkciu. Príkaz MsgBox môžete použiť ako prostriedok na odovzdanie informácií používateľovi. Môžete použiť aj vlastné dialógové okná alebo používateľské formuláre, to však už nie je predmetom tohto článku.

Dokumentácia makier a vlastných funkcií

Niekedy môžu byť aj jednoduché makrá a vlastné funkcie ťažko čitateľné. Ak chcete, aby boli zrozumiteľnejšie, môžete použiť popisný text vo forme komentárov. Komentáre môžete pridávať zadaním apostrofu pred popisný text. Nasledujúci príklad zobrazuje funkciu DISCOUNT s komentármi. Pridaním takýchto komentárov uľahčite sebe aj ostatným dlhodobé udržiavanie kódu VBA. Ak niekedy budete potrebovať niečo v kóde zmeniť, komentáre vám pomôžu pochopiť pôvodný kód.

Excel považuje apostrof za príkaz ignorovať všetko, čo sa v tom istom riadku nachádza napravo od apostrofu. Komentáre preto môžete zadávať do samostatných riadkov alebo na pravú stranu riadkov obsahujúcich kód VBA. Pomerne dlhý blok kódu môžete začať komentárom, ktorý vysvetľuje jeho celkový účel, a potom jednotlivé príkazy dokumentovať pomocou vnorených komentárov.

Makrá a vlastné funkcie môžete dokumentovať aj tak, že im priradíte popisné názvy. Makru napríklad môžete namiesto názvu Označenia priradiť názov OznačeniaMesiacov, čím presnejšie popíšete účel, na ktorý toto makro slúži. Používanie popisných názvov makier a vlastných funkcií je užitočné najmä v prípade, že ste vytvorili veľa procedúr, obzvlášť takých, ktoré slúžia na podobné, ale nie rovnaké účely.

Spôsob dokumentácie makier a vlastných funkcií je otázkou osobnej voľby. Dôležité je zvoliť si určitú formu dokumentácie a konzistentne ju používať.

Zaistenie prístupu k vlastným funkciám odkiaľkoľvek

Ak chcete použiť vlastnú funkciu, musí byť otvorený zošit obsahujúci modul, v ktorom ste túto funkciu vytvorili. Ak zošit nie je otvorený, zobrazí sa #NAME? pri pokuse o použitie funkcie. Ak na funkciu odkazujete v inom zošite, pred názvom funkcie musíte uviesť názov zošita, v ktorom sa funkcia nachádza. Ak napríklad vytvoríte funkciu s názvom DISCOUNT v zošite s názvom Osobné.xlsb a túto funkciu voláte z iného zošita, musíte zadať vzorec =osobne.xlsb!discount(), nie iba =discount().

Zbytočnému písaniu (a prípadným chybám) sa môžete vyhnúť tak, že si vlastné funkcie vyberiete z dialógového okna Vloženie funkcie. Vaše vlastné funkcie sa zobrazia v kategórii Definované používateľom:

Jednoduchším spôsobom, ako získať prístup k svojim vlastným funkciám kedykoľvek, je uložiť ich v samostatnom zošite a tento zošit uložiť ako doplnok. Doplnok budete mať k dispozícii pri každom spustení Excelu. Postupujte takto:

Po vykonaní týchto krokov budete mať svoje vlastné funkcie k dispozícii pri každom spustení Excelu. Ak chcete pridať ďalšie funkcie do svojej knižnice funkcií, vráťte sa do editora jazyka Visual Basic. Keď sa v editore jazyka Visual Basic pozriete do Prieskumníka projektu pod nadpisom projektu VBA, uvidíte modul nazvaný podľa súboru vášho doplnku. Váš doplnok bude mať príponu .xlam.

Keď dvakrát kliknete na modul v Prieskumníkovi projektu, editor jazyka Visual Basic zobrazí kód funkcie. Ak chcete pridať novú funkciu, umiestnite kurzor za príkaz End Function, ktorým sa končí posledná funkcia v okne kódu, a začnite písať. Týmto spôsobom môžete vytvoriť ľubovoľný počet funkcií. Funkcie budú vždy k dispozícii v kategórii Definované používateľom v dialógovom okne Vloženie funkcie .

Informácie o autoroch

Autormi pôvodnej verzie tohto obsahu sú Mark Dodge a Craig Stinson. Vytvorili ju ako súčasť svojej knihy Microsoft Office Excel 2007 Inside Out. Pôvodná verzia bola aktualizovaná aj pre novšie verzie Excelu.

Potrebujete ďalšiu pomoc?

Vždy sa môžete opýtať odborníka v komunite Excel Tech Community alebo získať podporu v komunitách.