Jak vytvářet funkce definované uživatelem v aplikaci Microsoft Excel

2024 Autor: Jason Gerald | [email protected]. Naposledy změněno: 2023-12-16 11:01

Přestože Excel již má stovky předdefinovaných funkcí, jako jsou SUMA, VLOOKUP, VLEVO atd., Dostupné vestavěné funkce obvykle nestačí k provádění poměrně složitých úkolů. Nebojte se však, protože požadované funkce musíte vytvořit pouze sami.

Krok

Krok 1. Vytvořte nový sešit nebo otevřete sešit, který chcete zpracovat pomocí funkcí definovaných uživatelem (UDF)

Krok 2. Otevřete Editor jazyka v aplikaci Microsoft Excel pomocí Nástroje-> Makro-> Editor jazyka (nebo stiskněte klávesovou zkratku Alt+F11)

Krok 3. Kliknutím na tlačítko Modul přidáte do listu nový modul

UDF můžete vytvořit v sešitu bez přidání nového modulu, ale funkce nebude fungovat v jiných listech ve stejném sešitu.

Krok 4. Vytvořte „hlavu“nebo „prototyp“své funkce

Prototyp funkce musí dodržovat následující strukturu:

veřejná funkce "Název funkce" (parametr1 jako typ1, parametr2 jako typ2) jako typ výsledku.

Prototypy mohou mít co nejvíce funkcí a jejich typy mohou být všechny základní datové typy nebo typy objektů aplikace Excel ve formě Range. Parametry můžete považovat za „operanty“(operátory), podle kterých bude funkce fungovat. Když například napíšete SIN (45) pro výpočet sinusu 45 stupňů, bude jako parametr bráno číslo 45. Poté kód funkce použije tyto hodnoty k provedení výpočtů a zobrazení výsledků.

Krok 5. Přidejte kód funkce, abyste zajistili, že: 1) použijete hodnotu danou parametrem; 2) předejte výsledek názvu funkce; a 3) zavřete funkci větou „koncová funkce“. Naučit se programovat ve VBA nebo v jakémkoli jiném jazyce zabere hodně času a podrobné vedení. Naštěstí mají tyto funkce obvykle malé bloky kódu a funkce programovacího jazyka příliš nevyužívají. Zde jsou některé prvky jazyka VBA, které lze použít:

1. Blok If (if), který vám umožňuje spustit část kódu, pouze pokud je splněna podmínka. Jako příklad:

Výsledek kurzu veřejné funkce (jako celočíselná hodnota) jako řetězec

Pokud je hodnota> = 5 Pak

Výsledky kurzu = "Přijato"

Jiný

Výsledky kurzu = "Odmítnuto"

Konec If

Koncová funkce

Všimněte si prvků v bloku kódu If:

IF podmínka POTOM kód JINÝ kód KONEC IF

2. . Klíčové slovo Else a druhou část kódu můžete vynechat, protože je nepovinné.
3. Blok Do (do), který spouští část kódu While nebo Before, když nebo dokud není splněna podmínka. Jako příklad:

Veřejná funkce BilPrima (hodnota jako celé číslo) jako booleovský

Dim i As Integer

i = 2

BilPrima = Pravda

Dělat

Pokud hodnota / i = Int (hodnota / i) Pak

BilPrima = Falešný

Konec If

i = i + 1

Smyčka While i <hodnota A NumberPrima = True

Koncová funkce

Podívejte se znovu na prvky:

DO kód SMYČKÁVEJTE, dokud podmínka

4. . Všimněte si také druhého řádku, který „deklaruje“proměnnou. Do kódu můžete přidat proměnné pro pozdější použití. Proměnné fungují jako dočasné hodnoty v kódu. Nakonec zvažte deklaraci funkce jako BOOLEAN, což je datový typ, který povoluje pouze hodnoty PRAVDA nebo NEPRAVDA. Tento způsob určování prvočísel není zdaleka optimální, ale kód byl napsán tak, aby byl snadno čitelný.
5. Pro blok (to), který spouští určité množství kódu. Jako příklad:

Faktor faktoru veřejné funkce (hodnota jako celé číslo) jako dlouhý

Ztlumit výsledky jako dlouhé

Dim i As Integer

Pokud je hodnota = 0 Pak

výsledek = 1

Hodnota ElseIf = 1 Potom

výsledek = 1

Jiný

výsledek = 1

Pro i = 1 Na hodnotu

výsledek = výsledek * i

další

Konec If

Faktoriál = výsledek

Koncová funkce

Podívejte se znovu na prvky:

Proměnná FOR = dolní limit TO horní limit kódu DALŠÍ

. Všimněte si také dalšího prvku ElseIf v příkazu If, který vám umožní přidat další možnosti do prováděného kódu. Nakonec zvažte funkci „result“a proměnnou deklarovanou jako Long. Datový typ Long umožňuje mnohem větší hodnoty než Integer.

Níže je uveden kód funkce, která převádí malá čísla na slova.



Krok 6. Vraťte se do sešitu a použijte funkci tak, že do buňky napíšete symbol „rovná se“(=) a za ním název funkce

Za znak funkce napište úvodní závorky („(“) za název funkce kóma oddělte parametry a ukončete uzavíracími závorkami („)“). Jako příklad:

= NumberToLetter (A4)

. Můžete také použít domácí vzorce vyhledáním v kategoriích Definováno uživatelem uvnitř možnosti Vložit vzorec. Stačí kliknout na tlačítko Fx nalevo od řádku vzorců. Ve funkcích existují tři typy forem parametrů:

1. Konstantní hodnota, která se zadává přímo do vzorce buňky. V tomto případě musí být text (řetězec) citován.
2. Například odkazy na buňky B6 nebo podobný rozsah A1: C3 (parametr musí být datový typ „Rozsah“)

3. Další funkce, která je uzavřena ve vaší funkci (vaši funkci lze také uzavřít do jiné funkce), například: = faktoriál (MAX (D6: D8))

   

   Krok 7. Zkontrolujte, zda jsou výsledky správné

   Použijte jej několikrát, abyste se ujistili, že funkce dokáže správně zpracovávat různé hodnoty parametrů:

   Tipy

   • Při psaní bloků kódu v řídicích strukturách, jako jsou If, For, Do atd., Ujistěte se, že odsadíte (vložíte ohraničení levého řádku mírně dovnitř) bloku kódu několikrát stisknutím mezerníku nebo tabulátoru. Díky tomu bude kód srozumitelnější a chyby bude mnohem snazší najít. Zvýšení funkčnosti je navíc snazší.
   • Pokud nevíte, jak psát kód pro funkce, přečtěte si článek Jak napsat jednoduché makro v aplikaci Microsoft Excel.
   • Někdy funkce pro výpočet výsledku nepotřebují všechny parametry. V tomto případě můžete použít klíčové slovo Optional před názvem parametru v záhlaví funkce. Pomocí funkce IsMissing (název_parametru) v kódu můžete určit, zda je parametru přiřazena hodnota nebo ne.
   • Nepoužívané názvy používejte jako funkce v Excelu, aby nebyly přepsány a odstraněny žádné funkce.
   • Excel má mnoho předdefinovaných funkcí a většinu výpočtů lze provádět pomocí těchto předdefinovaných funkcí, buď jednotlivě, nebo všechny najednou. Než začnete sami kódovat, podívejte se na seznam dostupných funkcí. Provedení lze provést rychleji, pokud používáte vestavěné funkce.

   Varování

   • Z bezpečnostních důvodů mnoho lidí deaktivuje makra. Ujistěte se, že upozorníte příjemce sešitu, že odeslaný sešit má makra a že tato makra nepoškodí jejich počítače.
   • Funkce použitá v tomto článku není nejlepší způsob, jak vyřešit související problém. Tento příklad slouží k vysvětlení použití struktur řízení jazyků.
   • VBA, stejně jako jiné jazyky, má kromě Do, If a For několik dalších řídicích struktur. Zde diskutovaná struktura pouze popisuje, co lze provést ve zdrojovém kódu funkce. Na internetu je mnoho průvodců, které vám mohou pomoci naučit se VBA.