Ustvarjanje funkcij po meri v Excelu

Čeprav Excel vključuje številne vgrajene funkcije delovnega lista, verjetno nima funkcije za vsako vrsto izračuna, ki ga izvedete. Oblikovalci Excela nikakor niso mogli predvideti potreb vsakega uporabnika po izračunu. V Excelu je zato na voljo možnost ustvarjanja funkcij po meri, ki je opisana v tem članku.

Ustvarjanje preproste funkcije po meri

Funkcije po meri, kot so makri, uporabljajo programski jezik VBA (Visual Basic for Applications). Od makrov se razlikujejo v dveh pomembnih pogledih. Kot prvo, uporabljajo funkcijske procedure namesto procedur vrste Sub. To pomeni, da se začnejo z izjavo Function namesto Sub in končajo z izjavo End Function namesto End Sub. Kot drugo, izvajajo izračune namesto dejanj. Nekatere vrste izjav, kot so na primer izjave, ki izberejo in oblikujejo obsege, so izključene iz funkcij po meri. V tem članku boste izvedeli, kako ustvarite in uporabljate funkcije po meri. Če želite ustvariti funkcije in makre, delate z urejevalnikom za Visual Basic(VBE), ki odpre novo okno, ločeno od Excela.

Vaše podjetje na primer ponuja 10-odstotni količinski popust za prodajo določenega izdelka, če je naročilo izdano za več kot 100 enot. V spodnjih odstavkih je prikazana funkcija za izračun tega popusta.

V spodnjem primeru je prikazan obrazec za naročilo, kjer so navedeni posamezni elementi, količina, cena, popust (če obstaja) in izračunana skupna cena.

Če želite v tem delovnem zvezku ustvariti funkcijo DISCOUNT po meri, sledite tem korakom:

1. Pritisnite Alt+F11, da odprete urejevalnik Visual Basic (v računalniku Mac pritisnite FN+ALT+F11) in nato kliknite Vstavi>modul. Na desni strani urejevalnika za Visual Basic se prikaže novo okno modula.

2. V nov modul kopirajte in prilepite to kodo.

   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
   
   

Uporaba funkcij po meri

Zdaj ste pripravljeni na uporabo nove funkcije POPUST. Zaprite urejevalnik za Visual Basic, izberite celico G7 in vnesite to:

=DISCOUNT(D7,E7)

Excel izračuna 10-odstotni popust za 200 enot po ceni 47,50 € na enoto in vrne znesek 950,00 €.

V prvi vrstici kode VBA, Funkcija DISCOUNT(količina, cena), ste navedli, da funkcija DISCOUNT zahteva dva argumenta, količino in ceno. Ko pokličete funkcijo v celici delovnega lista, morate vključiti ta dva argumenta. V formuli = POPUST (D7, E7) je argument D7 količina , E7 pa argument cene . Zdaj lahko kopirate formulo POPUST v G8: G13, da dobite spodaj prikazane rezultate.

Poglejmo, kako Excel razlaga ta postopek funkcije. Ko pritisnete tipko Enter, Excel v trenutnem delovnem zvezku poišče ime POPUST in ugotovi, da je to funkcija po meri v modulu VBA. Imena argumentov v oklepajih, količina in cena, so označbe mesta za vrednosti, na katerih temelji izračun popusta.

Izjava If v naslednjem bloku kode preuči argument količine in določi, ali je število prodanih izdelkov večje ali enako 100:

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

Če je število prodanih elementov večje ali enako 100, VBA izvede to izjavo, ki pomnoži vrednost količine z vrednostjo cene, nato pa pomnoži rezultat z 0.1:

Discount = quantity * price * 0.1

Rezultat je shranjen kot spremenljivka Popust. Izjava VBA, ki shrani vrednost kot spremenljivko, se imenuje dodelitvena izjava, ker ovrednoti izraz na levi strani enačaja in dodeli rezultat imenu spremenljivke na levi strani. Ker ima spremenljivka Popust enako ime kot funkcijska procedura, je vrednost, shranjena v spremenljivki, vrnjena formuli delovnega lista, ki je priklicala funkcijo DISCOUNT.

Če je količina manj kot 100, VBA izvede to izjavo:

Discount = 0

Na koncu spodnja izjava zaokroži vrednost, dodeljeno spremenljivki Popust, na dve decimalni mesti:

Discount = Application.Round(Discount, 2)

VBA nima funkcije ROUND, Excel pa jo ima. Če želite uporabiti funkcijo ROUND v tej izjavi, zahtevate, da modul VBA poišče metodo (funkcijo) »Round« v predmetu »Application« (Excel). To storite tako, da pred besedo »Round« vnesete besedo Application. To sintakso uporabite vsakič, ko želite dostopati do Excelove funkcije v modulu VBA.

Razumevanje pravil funkcije po meri

Funkcija po meri se mora začeti z izjavo »Function« in končati z izjavo »End Function«. Poleg imena funkcije izjava »Function« običajno določa enega ali več argumentov. Ustvarite pa lahko tudi funkcijo brez argumentov. Excel vključuje več vgrajenih funkcij – na primer RAND in NOW –, ki ne uporabljajo argumentov.

Poleg izjave »Function« funkcijska procedura vključuje tudi eno ali več izjav VBA, ki izvajajo odločitve in izračune z uporabo argumentov, podanih v funkciji. Na koncu morate na poljubnem mestu funkcijske procedure vključiti še izjavo, ki dodeli vrednost spremenljivki z enakim imenom, kot ga ima funkcija. Ta vrednost je vrnjena v formulo, ki prikliče funkcijo.

Uporaba ključnih besed VBA v funkcijah po meri

Število ključnih besed VBA, ki jih lahko uporabite v funkcijah po meri, je manjše od števila, ki ga lahko uporabite v makrih. Funkcije po meri ne smejo narediti ničesar drugega, kot da vrnejo vrednost formuli na delovnem listu ali izrazu, uporabljenemu v drugem makru ali funkciji VBA. Funkcije po meri na primer ne morejo spremeniti velikosti oken, urediti formule v celici ali spremeniti možnosti pisave, barve ali vzorca za besedilo v celici. Če v funkcijski postopek vključite tovrstno kodo »dejanja«, funkcija vrne #VALUE! napaka #REF!.

Edino dejanje, ki ga funkcijska procedura lahko izvede (poleg izvajanja izračunov ), je prikaz pogovornega okna. V funkciji po meri lahko uporabite izjavo InputBox kot način za pridobivanje vnosa od uporabnika, ki izvaja funkcijo. Izjavo MsgBox lahko uporabite za sporočanje informacij uporabniku. Uporabite lahko tudi pogovorna okna po meri ali uporabniške obrazce, vendar je to tema, ki presega obseg tega uvoda.

Dokumentiranje makrov in funkcij po meri

Včasih je težko prebrati celo preproste makre in funkcije po meri. Lažje jih boste razumeli tako, da vnesete pojasnjevalno besedilo v obliki komentarjev. Komentarje dodate tako, da pred razlagalno besedilo vnesete opuščaj. V spodnjem primeru je prikazana funkcija DISCOUNT s komentarji. Če dodate takšne komentarje, lahko vi in drugi uporabniki lažje ohranjate kodo VBA. Če boste morali v prihodnosti spremeniti kodo, boste lažje razumeli, kaj ste naredili prvotno.

Apostrof sporoča Excelu, da prezre vse na desni strani v isti vrstici, tako da lahko ustvarite komentarje v vrsticah samih ali na desni strani vrstic, ki vsebujejo kodo VBA. Daljši blok kode lahko tako na primer začnete s komentarjem, v katerem je razložen njegov splošni namen, nato pa s komentarji v vrstici dokumentirate posamezne izjave.

Makre in funkcije po meri lahko dokumentirate tudi tako, da jim dodelite opisna imena. Namesto da makro na primer poimenujete Oznake, ga lahko poimenujete Mesečne oznake, da podrobneje opišete namen makra. Uporaba opisnih imen za makre in funkcije po meri je še posebej uporabna, če ste ustvarili veliko postopkov, še posebej, če ustvarite postopke, ki imajo podobne, vendar ne enake namene.

Izbrani način dokumentiranja makrov in funkcij po meri temelji izključno na vaši osebni izbiri. Pomembno je, da sprejmete neko metodo dokumentacije in jo dosledno uporabljate.

Zagotavljanje polne razpoložljivosti funkcij po meri

Če želite uporabiti funkcijo po meri, mora biti delovni zvezek z modulom, v katerem ste ustvarili funkcijo, odprt. Če ta delovni zvezek ni odprt, dobite #NAME? Napaka, ko poskušate uporabiti funkcijo. Če se sklicujete na funkcijo v drugem delovnem zvezku, morate pred imenom funkcije navesti ime delovnega zvezka, v katerem je funkcija. Če na primer ustvarite funkcijo, imenovano POPUST, v delovnem zvezku z imenom Osebno.xlsb in to funkcijo pokličete iz drugega delovnega zvezka, morate vnesti =personal.xlsb!discount() in ne preprosto =discount().

Izgubljanje časa s tipkanjem (in morebitne tipkarske napake ) lahko prihranite tako, da funkcije po meri izberete v pogovornem oknu »Vstavi funkcijo«. Funkcije po meri so prikazane v kategoriji »Uporabniško določeno«:

Če želite, da so funkcije po meri ves čas na voljo, jih shranite v ločen delovni zvezek in nato ta delovni zvezek shranite kot dodatek. Ta dodatek je nato na voljo vsakič, ko zaženete Excel. To naredite tako:

Ko izvedete te korake, so vaše funkcije po meri na voljo vsakič, ko zaženete Excel. Če jih želite dodati v knjižnico funkcij, se vrnite v urejevalnik za Visual Basic. V raziskovalcu projekta urejevalnika za Visual Basic v naslovu »VBAProject« je prikazan modul z imenom vaše datoteke dodatka. Vaš dodatek ima pripono .xlam.

Če dvokliknete ta modul v raziskovalcu projektov, urejevalnik Visual Basic prikaže kodo funkcije. Če želite dodati novo funkcijo, postavite točko vstavljanja za izjavo »End Function«, ki prekine zadnjo funkcijo v oknu kode, in začnite tipkati. Na ta način lahko ustvarite poljubno število funkcij, ki bodo vedno na voljo v kategoriji »Uporabniško določeno« v pogovornem oknu Vstavi funkcijo.

O avtorjih

Izvirna avtorja te vsebine sta Mark Dodge in Craig Stinson in je del njune knjige Microsoft Office Excel 2007 Inside Out. Ta vsebina je bila posodobljena tako, da velja tudi za novejše različice Excela.

Potrebujete dodatno pomoč?

Kadar koli se lahko obrnete na strokovnjaka v Excelovi tehnični skupnosti ali pridobite podporo v skupnostih.