Vytváranie vlastných funkcií v Exceli

Hoci Excel obsahuje množstvo vstavaných funkcií hárka, je pravdepodobné, že nemá funkciu pre každý typ výpočtu, ktorý vykonávate. Návrhári Excelu nedokázali predvídať potreby výpočtov každého používateľa. Excel vám namiesto toho poskytuje možnosť vytvárať vlastné funkcie, ktoré sú vysvetlené v tomto článku.

Hľadáte informácie o tom, ako vytvoriť vlastnú funkciu jazyka JavaScript, ktorú môžete spustiť v Exceli pre Windows, Excel pre Mac alebo Excel pre web ? Ak sa nachádzate, prečítajte si článok Excel vlastné funkcie – Preh3/4ad.

Vlastné funkcie, podobne ako makrá, využí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.

Príklad formulára objednávky bez vlastnej funkcie

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 kliknite na položky 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
    

Poznámka: Ak chcete, aby bol váš kód čitateľnejší, môžete na zarážky čiar použiť kláves Tab . Odsadenie je len pre váš prospech a je voliteľné, pretože kód sa spustí s alebo bez neho. Po zadaní zarážky sa v editore jazyka Visual Basic predpokladá, že váš ďalší príkaz bude podobne odsadený. Stlačením kombinácie klávesov SHIFT + TABsa presuniete (čiže naľavo) na jeden znak tabulátora.

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 riadkoch kódu VBA, funkcia DISCOUNT (množstvo, cena), ste uviedli, že funkcia DISCOUNT vyžaduje dva argumenty, množstvo a cena. Keď zavoláte na funkciu v bunke hárka, musíte tieto dva argumenty zahrnúť. V poli vzorec = DISCOUNT (D7; E7) je D7 argument množstvo a E7 je argument cena . Teraz môžete skopírovať vzorec zliav do skupiny G8: G13, aby sa zobrazili nižšie uvedené výsledky.

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žstvocena, sú zástupnými objektmi pre hodnoty, ktoré sú potrebné na výpočet zľavy.

Príklad formulára objednávky s vlastnou funkciou

Príkaz If v nasledujúcom bloku kódu slúži na preskúmanie argumentu množstvo. 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.

Vlastná funkcia musí začať s príkazom funkcie a skončiť s príkazom ukončiť funkciu. Okrem názvu funkcie sa v príkaze funkcia zvyčajne špecifikuje jeden alebo viacero argumentov. Môžete však vytvoriť funkciu bez argumentov. Excel obsahuje viacero vstavaných funkcií – RAND a teraz, napríklad – ktoré nepoužívajú argumenty.

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.

Počet kľúčových slov jazyka VBA, ktoré môžete použiť vo vlastných funkciách, je menší než číslo, ktoré môžete použiť v makrách. Vlastné funkcie nie sú povolené na nič iné ako na vrátenie hodnoty do vzorca v hárku alebo na výraz použitý v inom makre alebo funkcii VBA. Vlastné funkcie napríklad nemôžu zmeniť veľkosť okien, upraviť vzorec v bunke alebo zmeniť možnosti písma, farby alebo vzorky textu v bunke. Ak zahrniete kód akcie tohto druhu do funkčnej procedúry, funkcia vráti #VALUE. .

V dialógovom okne je možné vykonať funkciu jedna akcia (okrem vykonávania výpočtov). Príkaz InputBox môžete použiť vo vlastnej funkcii ako prostriedok na získanie vstupu od používateľa vykonávajúceho funkciu. Príkaz MsgBox môžete použiť ako prostriedok na prenos informácií používateľovi. Môžete tiež použiť vlastné dialógové okná alebo programovací, ale je to predmet mimo rozsahu tohto úvodu.

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.

Príklad funkcie VBA s komentármi

Apostrof informuje Excel o všetkom, čo sa nachádza napravo od toho istého riadka, takže môžete vytvoriť komentáre v riadkoch sami alebo na pravej strane čiar obsahujúcich kód VBA. Môžete začať relatívne dlhý blok kódu s komentárom, ktorý vysvetľuje jeho celkový účel a potom pomocou vnorených komentárov dokumentovať jednotlivé výkazy.

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ť.

Ak chcete použiť vlastnú funkciu, musí byť zošit s modulom, v ktorom ste vytvorili funkciu, otvorený. Ak tento zošit nie je otvorený, dostanete #NAME? pri pokuse o použitie funkcie sa vyskytla chyba. Ak chcete odkazovať na funkciu v inom zošite, musí byť pred názvom funkcie názov zošita, v ktorom sa funkcia nachádza. Ak napríklad vytvoríte funkciu s názvom zľava v zošite s názvom personal. xlsb a zavoláte túto funkciu z iného zošita, musíte zadať výraz = Personal. xlsb! Discount (), nie jednoducho = 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:

Dialógové okno Vloženie funkcie

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:

  1. Po vytvorení potrebných funkcií kliknite na položky Súbor > Uložiť ako.

    V programe Excel 2007 kliknite na tlačidlo Microsoft Office a potom na položku Uložiť ako.

  2. V dialógovom okne Uložiť ako otvorte rozbaľovací zoznam Uložiť vo formáte a vyberte možnosť Doplnok programu Excel. Zošit uložte pod rozpoznateľným názvom, napríklad MojeFunkcie, do priečinka Doplnky. Dialógové okno Uložiť ako navrhne tento priečinok, takže stačí prijať predvolené umiestnenie.

  3. Po uložení zošita kliknite na položky Súbor > Možnosti programu Excel.

    V programe Excel 2007 kliknite na tlačidlo Microsoft Office a potom na položku Možnosti programu Excel.

  4. V dialógovom okne Program Excel – možnosti kliknite na kategóriu Doplnky.

  5. V rozbaľovacom zozname Správa vyberte položku Doplnky programu Excel. Potom kliknite na tlačidlo Spustiť.

  6. V dialógovom okne Doplnky začiarknite políčko vedľa názvu, pod ktorým ste uložili zošit, ako je znázornené nižšie.

    Dialógové okno Doplnky

  1. Po vytvorení potrebných funkcií kliknite na položky Súbor > Uložiť ako.

  2. V dialógovom okne Uložiť ako otvorte rozbaľovací zoznam Uložiť vo formáte a vyberte možnosť Doplnok programu Excel. Zošit uložte pod rozpoznateľným názvom, napríklad MojeFunkcie.

  3. Po uložení zošita kliknite na položky Nástroje > Doplnky pre Excel.

  4. V dialógovom okne Doplnky vyberte tlačidlo Prehľadávať a nájdite svoj doplnok. Kliknite na položku Otvoriť a potom začiarknite políčko vedľa svojho doplnku v poli Dostupné doplnky.

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.

Pomenovaný modul vo vbe

Po dvojitom kliknutí na tento modul v programe Project Explorer sa v programe Visual Basic Editor zobrazí kód funkcie. Ak chcete pridať novú funkciu, umiestnite kurzor za príkaz ukončiť funkciu, ktorý ukončí poslednú funkciu v okne kód, a začnite písať. Môžete vytvoriť toľko funkcií, koľko potrebujete týmto spôsobom, a vždy budú k dispozícii v kategórii definované používateľom v dialógovom okne Vloženie funkcie .

Tento obsah bol pôvodne napísaný Markom Dodge a Craigom Stinson ako súčasť svojej knihy Microsoft Office Excel 2007 Inside Out. Od tohto roku bola aktualizovaná, aby sa používala aj na novšie verzie Excelu.

Potrebujete ďalšiu pomoc?

Vždy sa môžete opýtať odborníka v komunite technikov pre Excel, získať podporu v rámci komunity lokality Answers alebo navrhnúť novú funkciu či vylepšenie na lokalite Excel User Voice.

Poznámka:  Táto stránka bola preložená automaticky a môže obsahovať gramatické chyby alebo nepresnosti. Naším cieľom je, aby bol tento obsah pre vás užitočný. Môžete nám dať vedieť, či boli tieto informácie pre vás užitočné? Tu nájdete anglický článok ako referenciu.

Rozšírte svoje zručnosti práce s balíkom Office
Preskúmať školenie
Buďte medzi prvými, ktorí získajú nové funkcie
Pridajte sa k insiderom pre Office

Boli tieto informácie užitočné?

Ďakujeme za vaše pripomienky!

Ďakujeme vám za pripomienky. Pravdepodobne vám pomôže, ak vás spojíme s pracovníkom podpory pre Office.

×