Vytvoření vlastních funkcí v Excelu

Poznámka: Snažíme se pro vás co nejrychleji zajistit aktuální obsah nápovědy ve vašem jazyce. Tato stránka byla přeložena automaticky a může obsahovat gramatické chyby nebo nepřesnosti. Naším cílem je to, aby pro vás byl její obsah užitečný. Mohli byste nám prosím dát ve spodní části této stránky vědět, jestli vám informace v článku pomohly? Pokud byste se rádi podívali na jeho anglickou verzi, najdete ji tady.

I když Excel obsahuje spoustu předdefinovaných funkcí listu, je pravděpodobné, že nemá funkci pro každý typ výpočtu, který uděláte. Návrháři aplikace Excel nemohli předvídat potřeby výpočtu všech uživatelů. Aplikace Excel místo toho umožňuje vytvářet vlastní funkce, které jsou vysvětleny v tomto článku.

Hledáte informace o tom, jak vytvořit vlastní funkci JavaScriptu, kterou můžete spustit v Excelu pro Windows, Excelu pro Mac nebo Excelu online? V článku Přehled vlastních funkcí Excelu.

Vlastní funkce, jako třeba makra, používají programovací jazyk Visual Basic for Applications (VBA) . Liší se od maker dvěma významnými způsoby. První, používají namísto podprocesů procedury funkcí . To znamená, že začínají výrazem Function místo dílčího příkazu a končí funkcí End Sub. Za druhé provádějí výpočty namísto provádění akcí. Některé typy příkazů, například příkazy pro výběr a formátování oblastí, jsou vyloučeny z vlastních funkcí. V tomto článku se dozvíte, jak vytvořit a používat vlastní funkce. Pokud chcete vytvořit funkce a makra, pracujete s editorem jazyka Visual Basic (VBE), který se otevře v novém okně, které je odlišné od Excelu.

Předpokládejme, že vaše společnost nabízí pro prodej produktu množství 10%, pokud je objednávka pro více než 100 jednotek. V následujících odstavcích bude vypočítána funkce pro výpočet této slevy.

Následující příklad ukazuje formulář objednávky, ve kterém jsou uvedeny jednotlivé položky, množství, Cena, sleva (pokud existují) a výsledná zvýšená cena.

Příklad objednávky bez vlastní funkce

Pokud chcete v tomto sešitu vytvořit vlastní funkci slevy, postupujte takto:

  1. Stisknutím kombinace kláves ALT + F11 spusťte program Visual Basic Editor (na Macu stiskněte klávesu FN + ALT + F11) a potom klikněte na Vložit > modul. V pravé části editoru jazyka Visual Basic se zobrazí nové okno modulu.

  2. Zkopírujte a vložte následující 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: Pokud chcete, aby byl kód čitelnější, můžete k odsazení použít klávesu TAB . Odsazení je určeno pouze pro vaši výhodu a je volitelné, protože kód bude spouštěn s nebo bez něj. Když zadáte odsazenou čáru, bude v jazyce Visual Basic Editor vycházet podobně jako odsazení dalšího řádku. Pokud chcete přesunout (tedy doleva) o jeden znak tabulátoru, stiskněte SHIFT + TAB.

Nyní jste připraveni použít novou funkci slevy. Zavřete Visual Basic Editor, vyberte buňku G7 a zadejte:

= DISCOUNT (D7; E7)

Excel vypočítá 10% slevu na 200 jednotek za $47,50 za kus a vrátí $950,00.

Na prvním řádku kódu VBA, funkce sleva (množství, cena) jste vyznačili, že funkce Discount vyžaduje dva argumenty, množství a cenu. Když zavoláte funkci v buňce listu, musíte zahrnout tyto dva argumenty. Ve vzorci = sleva (D7; E7), D7 je argument množství a E7 je argument Cena . Nyní můžete vzorec slevy zkopírovat do G8: G13 a získat tak výsledky.

Podívejme se, jak Excel interpretuje tuto funkci procedury. Po stisknutí klávesy ENTERExcel vyhledá v aktuálním sešitu název slevy a zjistí, že se jedná o vlastní funkci v modulu VBA. Názvy argumentů uzavřené v závorkách, množství a Cenapředstavují zástupné symboly pro hodnoty, na kterých je založen výpočet slevy.

Příklad objednávky s vlastní funkcí

Příkaz když v následujícím bloku kódu prověří argument množství a určí, jestli je počet prodaných položek větší nebo roven 100:

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

Pokud je počet prodaných položek větší nebo roven 100, jazyk VBA provede následující příkaz, který násobí hodnotu množství hodnotou Price a potom vynásobí výsledek hodnotou 0,1:

Discount = quantity * price * 0.1

Výsledek je uložen jako variabilní sleva. Příkaz VBA, který ukládá hodnotu v proměnné, se nazývá příkaz pro zadání , protože vyhodnocuje výraz na pravé straně znaménka rovná se a přiřadí výsledek k názvu proměnné vlevo. Protože proměnná sleva má stejný název jako procedura funkce, vrátí se hodnota uložená v proměnné do vzorce listu, který se nazývá funkce discount.

Pokud je množství menší než 100, provede jazyk VBA následující příkaz:

Discount = 0

Dále následující příkaz zaokrouhlí hodnotu přiřazenou proměnné Discount na dvě desetinná místa:

Discount = Application.Round(Discount, 2)

Jazyk VBA neobsahuje funkci ZAOKROUHLit, ale Excel. Chcete-li tedy použít funkci ZAOKROUHLit v tomto příkazu, můžete v jazyku VBA vyhledat metodu Round (funkce) v objektu Application (Excel). Je to tak, že přidáte aplikaci Word před slovo zaokrouhlit. Tuto syntaxi použijte, kdykoli potřebujete získat přístup k funkci aplikace Excel z modulu VBA.

Vlastní funkce musí začínat příkazem Function a končit příkazem End Function. Kromě názvu funkce určuje příkaz funkce obvykle jeden nebo více argumentů. Můžete ale vytvořit funkci bez argumentů. Excel obsahuje několik vestavěných funkcí – NÁHČÍSLO a teď, které nepoužívají argumenty.

Při použití příkazu Function zahrnuje procedura funkce jeden nebo více příkazů jazyka VBA, které provádějí rozhodování a provádějí výpočty pomocí argumentů předaných funkci. Nakonec, když je to v proceduře Function, musíte použít příkaz, který přiřadí hodnotu proměnné se stejným názvem jako funkce. Tato hodnota se vrátí do vzorce, který funkci volá.

Počet klíčových slov jazyka VBA, která můžete použít ve vlastních funkcích, je menší než číslo, které můžete v makrech použít. U vlastních funkcí není dovoleno provádět nic jiného, než vrátit hodnotu vzorce v listu, ani použít výraz, který se používá v jiném makru nebo funkci VBA. Vlastní funkce například nemohou měnit velikost oken, upravovat vzorec v buňce nebo měnit možnosti písma, barvy a vzorku textu v buňce. Pokud do procedury funkce zahrnete kód "Action" (Tento druh), vrátí funkce #VALUE!. #ČÍSLO!.

Může se zobrazit dialogové okno s jednou akcí, kterou může postupovat funkce. Příkaz InputBox můžete ve vlastní funkci použít jako prostředek pro získání vstupu od uživatele, který funkci provádí. Příkaz OknoSeZprávou můžete použít jako prostředek pro předávání informací uživateli. Můžete také použít vlastní dialogová okna nebo UserForms, ale je to předmět nad rámec tohoto úvodu.

Dokonce i jednoduchá makra a vlastní funkce mohou být špatně čitelné. Snazší pochopení můžete usnadnit zadáváním vysvětlujícího textu ve formě komentářů. Komentáře přidáte před vysvětlující text apostrofem. Následující příklad ukazuje funkci Discount s komentáři. Když tyto komentáře přidáte, můžete s vámi nebo jiným uživatelům usnadnit správu kódu v jazyku VBA. Pokud potřebujete v budoucnu změnit kód, budete mít snazší přehled o tom, co jste původně udělali.

Příklad funkce jazyka VBA s komentáři

Apostrof informuje Excel, že bude na stejném řádku přeskakovat všechno, takže můžete vytvářet komentáře buď na řádcích, nebo na pravé straně řádků obsahujících kód jazyka VBA. Můžete začít relativně dlouhý blok kódu s komentářem, který vysvětluje jeho celkový účel, a pak použít vložené komentáře k dokumentaci jednotlivých příkazů.

Dalším způsobem, jak dokumentovat a vlastní funkce dokumentovat, je přidělit jim popisné názvy. Místo označenínázvu makra můžete třeba pojmenovat MonthLabels , který popisuje konkrétnější účel makra. Použití popisných názvů pro makra a vlastní funkce je obzvláště užitečné, když jste vytvořili mnoho postupů, zejména pokud vytváříte procedury, které mají podobné, ale ne stejné účely.

Způsob, jak dokumentovat makra a vlastní funkce, jsou osobní preference. Důležité je přijmout nějakou metodu dokumentace a používat ji konzistentně.

Pokud chcete použít vlastní funkci, musí být otevřený sešit obsahující modul, ve kterém jste funkci vytvořili. Pokud tento sešit není otevřený, dostanete #NAME? Chyba, když se pokusíte funkci použít. Pokud odkazujete na funkci v jiném sešitu, musíte před název funkce předcházet názvu sešitu, ve kterém je daná funkce umístěná. Pokud například vytvoříte funkci s názvem sleva v sešitu s názvem Personal. xlsb a zavoláte ji z jiného sešitu, musíte zadat = osobní. xlsb! sleva (), ne jednoduše = sleva ().

Když v dialogovém okně Vložit funkci vyberete vlastní funkce, můžete si uložit některé klávesové úhozy (a možná chyby). Vlastní funkce se zobrazí v kategorii definované uživatelem:

insert function dialog box

Abyste mohli vlastní funkce kdykoli být dostupné, můžete je Uložit do samostatného sešitu a potom tento sešit uložit jako doplněk. Doplněk můžete potom zpřístupnit při každém spuštění Excelu. Tady je postup:

  1. Po vytvoření požadovaných funkcí klikněte na soubor _GT_ Uložit jako.

    V Excel 2007 klikněte na tlačítko Microsoft Officea potom klikněte na Uložit jako .

  2. V dialogovém okně Uložit jako otevřete rozevírací seznam Uložit jako typ a vyberte doplněk aplikace Excel. Uložte sešit pod rozpoznatelným názvem, jako je třeba MyFunctions, do složky AddIns . Tuto složku bude navrhovat v dialogovém okně Uložit jako , takže stačí pouze přijmout výchozí umístění.

  3. Po uložení sešitu klikněte na > Možnosti aplikace Excel.

    V Excel 2007 klikněte na tlačítko Microsoft Officea potom klikněte na Možnosti aplikace Excel.

  4. V dialogovém okně Možnosti aplikace Excel klikněte na kategorii Doplňky .

  5. V rozevíracím seznamu Spravovat vyberte Doplňky aplikace Excel. Potom klikněte na tlačítko Přejít .

  6. V dialogovém okně Doplňky zaškrtněte políčko vedle jména, které jste použili k uložení sešitu, jak vidíte níže.

    add-ins dialog box

  1. Po vytvoření požadovaných funkcí klikněte na soubor _GT_ Uložit jako.

  2. V dialogovém okně Uložit jako otevřete rozevírací seznam Uložit jako typ a vyberte doplněk aplikace Excel. Uložte sešit pod rozpoznatelným názvem, například MyFunctions.

  3. Po uložení sešitu klikněte na nástroje > Excel –doplňky.

  4. V dialogovém okně Doplňky klikněte na tlačítko Procházet, abyste tento doplněk našli, klikněte na otevříta pak zaškrtněte políčko vedle doplňku v poli Doplňky k dispozici .

Po provedení těchto kroků budou vlastní funkce dostupné při každém spuštění Excelu. Pokud chcete přidat do knihovny funkcí, vraťte se do editoru jazyka Visual Basic. Pokud v aplikaci Visual Basic Explorer provedete projekt pod nadpisem VBAProject, zobrazí se modul s názvem za souborem doplňku. Váš doplněk bude mít příponu. xlam.

named module in vbe

Poklikáním na tento modul v Project Exploreru zobrazíte kód funkce v editoru jazyka Visual Basic. Chcete-li přidat novou funkci, umístěte kurzor za příkaz end, který ukončí poslední funkci v okně Code, a začněte psát. Tímto způsobem můžete vytvořit tolik funkcí, kolik potřebujete, a v kategorii definované uživatelem v dialogovém okně Vložit funkci budou vždy k dispozici.

Tento obsah byl původně vytvořen značkou zesvětlení a Craig Stinson jako součást knihy v knize Microsoft Office Excel 2007. Od té doby se aktualizovala i na novější verze Excelu.

Potřebujete další pomoc?

Kdykoli se můžete zeptat některého odborníka v technické komunitě Excelu, získat podporu v komunitě pro odpovědi, případně navrhnout novou funkci nebo vylepšení ve fóru Excel User Voice.

Rozšiřte své dovednosti s Office
Projít školení
Získejte nové funkce jako první
Připojte se k účastníkům programu Office Insiders

Byly tyto informace užitečné?

Děkujeme vám za zpětnou vazbu.

Děkujeme vám za váš názor. Vypadá to, že bude užitečné, když vás spojíme s některým z našich agentů z podpory Office.

×