Ustvarjanje funkcij po meri v Excelu

Funkcije po meri, kot so makri, uporabljajo programski jezik Visual Basic for Applications (VBA). 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 lahko ustvarite in uporabite 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, upoštevajte te korake:

1. Pritisnite tipki Alt+F11, da odprete urejevalnik za Visual Basic (v računalniku s sistemom Mac pritisnite tipke FN+ALT+F11), nato pa 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
   

Zdaj ste pripravljeni na uporabo nove funkcije DISCOUNT. 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 Function DISCOUNT(količina, cena) ste navedli, da funkcija DISCOUNT zahteva dva argumenta, količino inceno. Ko priklicate funkcijo v celico delovnega lista, morate vključiti ta dva argumenta. V formuli =DISCOUNT(D7,E7) je D7 argument količine , E7 pa argument cena . Zdaj lahko kopirate formulo DISCOUNT v G8:G13, da dobite rezultate, prikazane spodaj.

Razmislimo o tem, kako Excel interpretira ta funkcijska procedura. Ko pritisnete tipko Enter, Excel poišče ime DISCOUNT v trenutnem delovnem zvezku in ugotovi, da je to funkcija po meri v modulu VBA. Imena argumentov, obdana z oklepaji , količino in ceno, so ograde za vrednosti, na katerih temelji izračun popusta.

Izjava »If« v spodnjem bloku kode pregleda argument količine in določi, ali je število prodanih elementov 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.

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 ima na voljo 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.

Š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 storiti ničesar drugega kot vrniti vrednost formuli na delovnem listu ali izrazu, ki se uporablja v drugem makru ali funkciji VBA. Funkcije po meri na primer ne morejo spreminjati velikosti oken, urejati formule v celici ali spreminjati možnosti pisave, barve ali vzorca za besedilo v celici. Če v funkcijsko proceduro vključite to vrsto kode 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 UserForms, vendar je to tema, ki ni opisana v teh navodilih.

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 morate kodo v prihodnosti spremeniti, boste lažje razumeli vaša pretekla dejanja.

Opuščaj sporoči Excelu, naj prezre vse elemente, ki se nahajajo na desni strani v isti vrstici, tako da lahko ustvarite samostojne komentarje v vrsticah 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 več procedur, zlasti procedur s podobnimi (a ne enakimi) nameni.

Izbrani način dokumentiranja makrov in funkcij po meri temelji izključno na vaši osebni izbiri. Pomembno je, da izbrani način dokumentiranja nato dosledno uporabljate.

Č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? ko poskusite uporabiti funkcijo. Če se sklicujete na funkcijo v drugem delovnem zvezku, morate pred ime funkcije vnesti ime delovnega zvezka, v katerem je funkcija. Če na primer ustvarite funkcijo DISCOUNT v delovnem zvezku, ki se imenuje Personal.xlsb, in to funkcijo pokličete iz drugega delovnega zvezka, morate vnesti =personal.xlsb!discount(), ne le =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 v raziskovalcu projekta ta modul dvokliknete, urejevalnik za Visual Basic prikaže vašo 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.

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.