Napriek tomu, že Excel už má stovky vstavaných funkcií, ako napríklad SUMA, VLOOKUP, VĽAVO a podobne, dostupné vstavané funkcie zvyčajne nepostačujú na vykonávanie pomerne zložitých úloh. Nebojte sa však, pretože požadované funkcie musíte vytvoriť iba sami.
Krok
Krok 1. Vytvorte nový zošit alebo otvorte zošit, ktorý chcete spracovať pomocou funkcií definovaných používateľom (UDF)
Krok 2. V programe Microsoft Excel otvorte editor jazyka Visual Basic pomocou Nástroje-> Makro-> Editor jazyka Visual Basic (alebo stlačte klávesovú skratku Alt+F11)
Krok 3. Kliknutím na tlačidlo Modul pridáte do pracovného hárka nový modul
UDF môžete vytvoriť v zošite bez pridania nového modulu, ale funkcia nebude fungovať v iných pracovných hárkoch toho istého zošita.
Krok 4. Vytvorte „hlavu“alebo „prototyp“svojej funkcie
Prototyp funkcie musí mať nasledujúcu štruktúru:
verejná funkcia "Názov funkcie" (parameter1 ako typ1, parameter2 ako typ2) ako typ výsledku.
Prototypy môžu mať čo najviac funkcií a ich typmi môžu byť všetky základné typy údajov alebo typy objektov programu Excel vo forme rozsahu. Parametre si môžete predstaviť ako „operanty“(operátory), na ktorých bude funkcia pôsobiť. Keď napríklad napíšete SIN (45) na výpočet sínusu 45 stupňov, ako parameter sa bude brať číslo 45. Potom kód funkcie použije tieto hodnoty na vykonanie výpočtov a zobrazenie výsledkov.
Krok 5. Pridajte kód funkcie, aby ste zaistili, že: 1) použijete hodnotu danú parametrom; 2) pošlite výsledok do názvu funkcie; a 3) zatvorte funkciu vetou „koncová funkcia“. Naučiť sa programovať vo VBA alebo v inom jazyku vyžaduje veľa času a podrobné vedenie. Našťastie tieto funkcie majú spravidla malé bloky kódu a málo využívajú funkcie programovacieho jazyka. Tu je niekoľko prvkov jazyka VBA, ktoré je možné použiť:
- Blok If (if), ktorý vám umožňuje vykonať časť kódu iba vtedy, ak je splnená podmienka. Ako príklad:
- . Kľúčové slovo Else spolu s druhou časťou kódu môžete vynechať, pretože je voliteľné.
- Blok Do (do), ktorý vykonáva časť kódu Kým alebo Do, keď alebo kým nie je splnená podmienka. Ako príklad:
- . Všimnite si tiež druhý riadok, ktorý „deklaruje“premennú. Do svojho kódu môžete pridať premenné na neskoršie použitie. Premenné pôsobia v kóde ako dočasné hodnoty. Nakoniec zvážte deklaráciu funkcie ako BOOLEAN, čo je typ údajov, ktorý povoľuje iba PRAVÉ alebo NEPRAVDIVÉ hodnoty. Tento spôsob určovania prvočísel nie je ani zďaleka optimálny, ale kód bol napísaný tak, aby bol ľahko čitateľný.
- Pre blok (to), ktorý vykoná určité množstvo kódu. Ako príklad:
- Konštantná hodnota, ktorá sa zadáva priamo do vzorca bunky. V takom prípade je potrebné citovať text (reťazec).
- Odkaz na bunku, napríklad B6 alebo podobný rozsah A1: C3 (parameter musí byť dátový typ „Rozsah“)
-
Ďalšia funkcia, ktorá je uzavretá vo vašej funkcii (vaša funkcia môže byť uzavretá aj v inej funkcii), napríklad: = faktoriál (MAX (D6: D8))
Krok 7: Vytvorte funkciu definovanú používateľom v programe Microsoft Excel Krok 7. Uistite sa, že výsledky sú správne
Použite ho niekoľkokrát, aby ste sa presvedčili, že funkcia je schopná správne spracovať rôzne hodnoty parametrov:
Tipy
- Pri písaní blokov kódu v riadiacich štruktúrach, ako sú If, For, Do, atď., Uistite sa, že odsadíte (vložíte okraj ľavého riadka mierne dovnútra) bloku kódu tým, že niekoľkokrát stlačíte medzerník alebo tabulátor. Vďaka tomu bude kód zrozumiteľnejší a chyby bude oveľa jednoduchšie nájsť. Navyše zvýšenie funkčnosti je teraz jednoduchšie.
- Ak neviete, ako napísať kód pre funkcie, prečítajte si článok Ako napísať jednoduché makro v programe Microsoft Excel.
- Niekedy funkcie nepotrebujú na výpočet výsledku všetky parametre. V takom prípade môžete pred názvom parametra v hlavičke funkcie použiť kľúčové slovo Voliteľné. Na určenie, či je parametru priradená hodnota alebo nie, môžete vo svojom kóde použiť funkciu IsMissing (parameter_name).
- Nepoužívané názvy používajte ako funkcie v programe Excel, aby sa žiadne funkcie neprepisovali a neodstraňovali.
- Excel má mnoho vstavaných funkcií a väčšinu výpočtov je možné vykonať pomocou týchto vstavaných funkcií, jednotlivo alebo naraz. Predtým, ako začnete sami kódovať, pozrite sa na zoznam dostupných funkcií. Vykonanie je možné rýchlejšie, ak používate vstavané funkcie.
Pozor
- Z bezpečnostných dôvodov veľa ľudí vypína makrá. Nezabudnite príjemcov zošita upozorniť, že odoslaný zošit obsahuje makrá a že tieto makrá nepoškodia ich počítače.
- Funkcia použitá v tomto článku nie je najlepším spôsobom, ako vyriešiť príslušný problém. Tento príklad sa používa na vysvetlenie použitia štruktúr riadenia jazykov.
- VBA, podobne ako ostatné jazyky, má okrem funkcií Do, If a For niekoľko ďalších riadiacich štruktúr. Tu diskutovaná štruktúra iba opisuje, čo je možné vykonať v zdrojovom kóde funkcie. Na internete je veľa sprievodcov, ktoré vám môžu pomôcť naučiť sa VBA.
Výsledok kurzu verejnej funkcie (ako celočíselná hodnota) ako reťazec
Ak je hodnota> = 5 Potom
Výsledky kurzu = „Prijaté“
Inak
Výsledky kurzu = „Odmietnuté“
Koniec Ak
Koncová funkcia
Všimnite si prvkov v bloku kódu If:
IF podmienka POTOM kód INÝ kód END IF
Verejná funkcia BilPrima (hodnota ako celé číslo) ako booleovský
Dim i As Integer
i = 2
BilPrima = Pravda
Urob
Ak hodnota / i = Int (hodnota / i) Potom
BilPrima = nepravda
Koniec Ak
i = i + 1
Slučka Kým i <hodnota A ČísloPrima = Pravda
Koncová funkcia
Znova sa pozrite na prvky:
KÓD ZMYKLITE, KEĎ/DO podmienky
Faktor verejnej funkcie (hodnota ako celé číslo) ako dlhý
Stmaviť výsledky ako dlhé
Dim i As Integer
Ak je hodnota = 0 Potom
výsledok = 1
Hodnota inak = 1 Potom
výsledok = 1
Inak
výsledok = 1
Pre i = 1 Na hodnotu
výsledok = výsledok * i
Ďalšie
Koniec Ak
Faktoriál = výsledok
Koncová funkcia
Znova sa pozrite na prvky:
PRE premennú = dolná hranica AŽ horná hranica kódu ĎALEJ
. Všimnite si tiež dodatočného prvku ElseIf v príkaze If, ktorý vám umožňuje pridať ďalšie možnosti do vykonávaného kódu. Nakoniec zvážte funkciu „výsledok“a premennú deklarovanú ako Long. Dátový typ Long umožňuje oveľa väčšie hodnoty ako Integer.
Nasleduje kód funkcie, ktorá prevádza malé čísla na slová.
Krok 6. Vráťte sa do zošita a použite funkciu tak, že do bunky napíšete symbol „rovná sa“(=) a za ním názov funkcie
Úvodné zátvorky („(“) napíšte za názov funkcie pomocou znamienka kóma na oddelenie parametrov a na konci so zátvorkami („)“). Ako príklad:
= NumberToLetter (A4)
. Môžete tiež použiť domáce formuly ich vyhľadaním v kategóriách Definované užívateľom vnútri možnosti Vložiť vzorec. Stačí kliknúť na tlačidlo Fx naľavo od riadka vzorcov. Vo funkciách existujú tri typy foriem parametrov:
