Vytvoření vlastních funkcí v Excel

Přestože Excel obsahuje množství předdefinových funkcí listu, je pravděpodobné, že funkce nemá funkci pro každý typ výpočtu, který provádíte. Návrháři Excel nemohli odhadnout potřeby výpočtů všech uživatelů. Místo toho Excel umožňuje vytvářet vlastní funkce, které jsou vysvětleny v tomto článku.

Vlastní funkce, jako jsou makra, používají programovací jazyk Visual Basic for Application (VBA). Od maker se liší dvěma významnými způsoby. Nejdřív místo procedur Sub používají procedury Funkce. To znamená, že začínají příkazem Function místo příkazu Sub a končí koncovou funkcí místo end sub. Za druhé, místo provádění akcí provádějí výpočty. Některé typy příkazů, jako jsou příkazy, které vyberou a formátují oblasti, jsou vyloučené z vlastních funkcí. V tomto článku se dozvíte, jak vytvářet a používat vlastní funkce. Při vytváření funkcí a maker pracujete s editorem Visual Basic (VBE),který se otevře v novém okně odděleném od Excel.

Předpokládejme, že vaše společnost nabízí množstevní slevu ve výši 10 procent z prodeje produktu za předpokladu, že objednávka je pro více než 100 jednotek. V následujících odstavcích ukážeme funkci pro výpočet této slevy.

V následujícím příkladu je uveden formulář objednávky, který obsahuje každou položku, množství, cenu, slevu (pokud je nějaká) a výslednou rozšířenou cenu.

Příklad objednávek bez vlastní funkce

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

  1. Stisknutím kombinace kláves Alt+F11 otevřete Editor Visual Basic (na Macu stiskněte FN+ALT+F11) a potom klikněte na Vložit > Modul. Na pravé straně editoru nástrojů se zobrazí nové okno Visual Basic 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 váš kód čitelný, můžete řádky odsadit pomocí klávesy Tab. Odsazení je jenom pro vaši výhodu a je nepovinné, protože kód bude běžet s kódem nebo bez něj. Po zadání odsazené čáry bude Visual Basic Editor předpokládat, že další řádek bude odsazený podobně. Pokud chcete jeden znak tabulátoru přesunout (to znamená doleva), stiskněte kombinaci kláves Shift+Tab.

Teď můžete použít novou funkci SLEVA. Zavřete editor Visual Basic, vyberte buňku G7 a zadejte následující příkaz:

=SLEVA(D7;E7)

Excel vypočítá 10procentní slevu na 200 jednotek za 47,50 Kč na jednotku a vrátí 950,00 Kč.

V prvním řádku kódu jazyka VBA funkce SLEVA(množství, cena) jste uvedli, že funkce SLEVA vyžaduje dva argumenty, množství a cenu. Když zavoláte funkci do buňky listu, musíte zahrnout tyto dva argumenty. Ve vzorci =SLEVA(D7;E7) je argument množství D7 a argument cena je E7. Teď můžete vzorec SLEVA zkopírovat do G8:G13 a zobrazit výsledky níže.

Podívejme se, jak Excel interpretuje tento postup funkce. Když stisknete Enter,Excel vyhledá název DISCOUNT v aktuálním sešitu a zjistí, že se jedná o vlastní funkci v modulu VBA. Názvy argumentů uzavřené v závorkách, množství a ceně jsouzástupné symboly hodnot, na kterých je výpočet slevy založen.

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

Příkaz If v následujícím bloku kódu zkontroluje 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ý vynásobí hodnotu množství cenovou hodnotou a výsledek vynásobí hodnotou 0,1:

Discount = quantity * price * 0.1

Výsledek se uloží jako proměnná Sleva. Příkaz jazyka VBA, který ukládá hodnotu v proměnné, se nazývá příkaz přiřazení, protože vyhodnotí výraz na pravé straně symbolu rovná se a přiřadí výsledek 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 SLEVA.

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

Discount = 0

Následující příkaz zaokrouhlí hodnotu přiřazenou proměnné Sleva na dvě desetinná místa:

Discount = Application.Round(Discount, 2)

Jazyk VBA nemá žádnou funkci ROUND, ale Excel ano. Pokud tedy chcete v tomto příkazu použít funkci ROUND, řekněte jazyku VBA, aby v objektu Application (Excel) hledat metodu Round (funkce). To můžete udělat přidáním slova Aplikace před slovo Zaokrouhlit. Tuto syntaxi použijte pokaždé, když potřebujete získat přístup Excel funkci z modulu VBA.

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

Po příkazu Function zahrnuje procedura funkce jeden nebo více příkazů jazyka VBA, které se rozhodují a provádějí výpočty pomocí argumentů předátých funkci. A konečně, někde v postupu funkce musíte zahrnovat příkaz, který přiřadí hodnotu proměnné se stejným názvem jako funkce. Tato hodnota se vrátí do vzorce, který volá funkci.

Počet klíčových slov jazyka VBA, která můžete použít ve vlastních funkcích, je menší než počet, který můžete použít v makrech. Vlastní funkce nesmí dělat nic jiného než vrátit hodnotu do vzorce v listu nebo výrazu použitému v jiném makru nebo funkci jazyka VBA. Vlastní funkce například nemohou měnit velikost oken, upravovat vzorec v buňce nebo měnit možnosti písma, barvy nebo vzorku textu v buňce. Pokud do procedury funkce zahrníte kód akce tohoto druhu, vrátí funkce #VALUE! .

Jedna akce, která může procedura funkce udělat (kromě provádění výpočtů), je zobrazení dialogového okna. Příkaz InputBox můžete použít ve vlastní funkci jako prostředek pro získání vstupu od uživatele, který funkci provádí. Příkaz MsgBox můžete použít jako prostředek pro předávání informací uživateli. Můžete taky použít vlastní dialogová okna nebo userforms, ale to je předmět nad rámec tohoto úvodu.

Čitelnost i jednoduchých maker a vlastních funkcí může být obtížná. Srozumitelnost můžete usnadnit zadáním vysvětlujícího textu ve formě komentářů. Komentáře přidáte tak, že před vysvětlující text přidáte apostrof. Například následující příklad ukazuje funkci SLEVA s komentáři. Přidáním komentářů, jako je tento, vám nebo ostatním usnadníte správu kódu jazyka VBA, jakmile čas projde. Pokud budete v budoucnu potřebovat kód změnit, budete mít snazší pochopení toho, co jste původně udělali.

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

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

Další způsob, jak zdokumentovat makra a vlastní funkce, je dát jim popisné názvy. Například místo názvu popisky maker bysteho mohli pojmenovát MonthLabels, abyste přesněji popsali účel, pro který makro slouží. Použití popisných názvů maker a vlastních funkcí je užitečné zejména v případě, že jste vytvořili mnoho postupů, zejména pokud vytváříte postupy, které mají podobné, ale ne identické účely.

Způsob zdokumentování maker a vlastních funkcí je otázkou 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 sešit obsahující modul, ve kterém jste funkci vytvořili, otevřený. Pokud tento sešit není otevřený, dostanete #NAME? při pokusu o použití funkce. Pokud odkazujete na funkci v jiném sešitu, musíte před názvem funkce předcházet názvu sešitu, ve kterém je funkce umístěna. Pokud například vytvoříte funkci s názvem SLEVA v sešitu s názvem Personal.xlsb a tuto funkci zavoláte z jiného sešitu, musíte zadat =personal.xlsb!discount(), ne jednoduše =sleva().

Některé stisknutí kláves (a možné chyby při psaní) si můžete uložit tak, že v dialogovém okně Vložit funkci vyberete vlastní funkce. Vaše vlastní funkce se zobrazí v kategorii Definované uživatelem:

insert function dialog box

Jednodušším způsobem, jak mít vaše vlastní funkce k dispozici, je ukládat je do samostatného sešitu a pak sešit uložit jako doplněk. Doplněk pak můžete nastavit k dispozici při každém spuštění Excel. Tady je postup:

  1. Po vytvoření funkcí, které potřebujete, klikněte na > Uložit jako.

    V Excel 2007 klikněte na tlačítko Microsoft Office apotom 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 Excel doplněk. Uložte sešit pod rozpoznatelným názvem, například MyFunctions, do složky AddIns. Dialogové okno Uložit jako navrhne tuto složku, takže jediné, co musíte udělat, je přijmout výchozí umístění.

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

    V Excel 2007 klikněte na tlačítko Microsoft Office apotom klikněte na Excel Možnosti.

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

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

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

    add-ins dialog box

  1. Po vytvoření funkcí, které potřebujete, 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 Excel doplněk. Uložte sešit pod rozpoznatelným názvem, například Mojefunkce.

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

  4. V dialogovém okně Doplňky vyberte tlačítko Procházet, abyste našli doplněk, klikněte na Otevřít a potom zaškrtněte políčko vedle Add-In v poli Doplňky k dispozici.

Po provedení těchto kroků budou vaše vlastní funkce k dispozici při každém spuštění Excel. Pokud chcete přidat do knihovny funkcí, vraťte se do editoru Visual Basic funkcí. Pokud se podíváte do Visual Basic Editoru Project pod nadpisem VBAProject, zobrazí se modul pojmenovaný po souboru doplňku. Doplněk bude mít příponu .xlam.

named module in vbe

Poklikáním na tento modul v průzkumníku Project způsobí, že Visual Basic Editor zobrazí kód funkce. Pokud chcete přidat novou funkci, umístěte kurzor za příkaz End Function , který ukončí poslední funkci v okně Kód, a začněte psát. Tímto způsobem můžete vytvořit tolik funkcí, kolik potřebujete, a vždycky budou dostupné v kategorii Definované uživatelem v dialogovém okně Vložit funkci.

Tento obsah původně napsali Mark Dodge a Craig Stinson jako součást Microsoft Office Excel 2007 Inside Out. Od té doby se aktualizuje tak, aby se na novější verze Excel také.

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.

Potřebujete další pomoc?

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! Pravděpodobně bude užitečné, když vás spojíme s některým z našich agentů podpory Office.

×