Ustvarjanje funkcij po meri v Excelu

Čeprav Excel vključuje številne vgrajene funkcije delovnega lista, verjetno ni na voljo funkcij za vse vrste vaših izračunov. Oblikovalci Excela ne morejo predvideti vseh potreb izvajanja izračunov posameznega uporabnika. V Excelu je zato na voljo možnost ustvarjanja funkcij po meri, ki je opisana v tem članku.

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.

Primer obrazca naročila brez funkcije po meri

Č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

Opomba: Če želite zagotoviti boljšo berljivost kode, lahko s tabulatorko zamaknete vrstice. zamik je namenjen le boljši preglednosti in je izbiren, saj se koda izvede tudi brez njega. Ko vnesete zamaknjeno vrstico, urejevalnik za Visual Basic Editor predvideva, da bo enako zamaknjena tudi naslednja vrstica. Če se želite premakniti za en tabulator (tj. v levo), pritisnite Shift+tabulatorka.

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(quantity, price)« ste označili, da funkcija DISCOUNT zahteva dva argumenta, tj. količino in ceno. Ko prikličete funkcijo v celici delovnega lista, morate vključiti ta dva argumenta. V formuli »=DISCOUNT(D7,E7)« je D7 argument količine, E7 pa je argument cene. Zdaj lahko kopirate formulo DISCOUNT v G8:G13, da pridobite spodnje rezultate.

Oglejmo si, kako Excel obravnava to funkcijsko proceduro. Ko pritisnete tipko Enter, Excel poišče ime DISCOUNT v trenutnem delovnem zvezku in ugotovi, da je funkcija po meri v modulu VBA. Imeni argumenta v oklepaju, tj. quantity (količina) in price (cena), sta označbi mesta za vrednosti, na katerih temelji izračun popusta.

Primer obrazca naročila s funkcijo po meri

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 v makrih. Funkcije po meri lahko le vrnejo vrednost formuli na delovnem listu ali izrazu, uporabljenem v drugem makru ali funkciji VBA. Funkcije po meri ne smejo na primer spreminjati velikosti oken, urejati formule v celici ali spreminjati pisavo, barvo ali možnosti vzorcev za besedilo v celici. Če v funkcijsko proceduro vključite takšno kodo »dejanja«, funkcija vrne napako »#VALUE!«.

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.

Primer funkcije VBA s pripombami

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, se prikaže napaka »#NAME?«, ko poskušate 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 z imenom DISCOUNT v delovnem zvezku z imenom Personal.xlsb in prikličete to funkcijo iz drugega delovnega zvezka, morate vnesti =personal.xlsb!discount() in 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«:

pogovorno okno »Vstavljanje funkcije«

Č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:

  1. Ko ustvarite želeno funkcijo, kliknite Datoteka > Shrani kot.

    V programu Excel 2007 kliknite gumb Microsoft Office, nato pa kliknite Shrani kot

  2. V pogovornem oknu Shrani kot odprite spustni seznam Shrani kot vrsto, nato pa izberite Excelov dodatek. Shranite delovni zvezek s prepoznavnim imenom, kot je Moje funkcije v mapo Dodatki. Pogovorno okno Shrani kot predlaga to mapo, tako da morate le sprejeti privzeto mesto.

  3. Ko shranite delovni zvezek kliknite Datoteka > Excelove možnosti.

    V programu Excel 2007 kliknite gumb Microsoft Office, nato pa kliknite Excelove možnosti.

  4. V pogovornem oknu Excelove možnosti kliknite kategorijo Dodatki.

  5. Na spustnem seznamu Upravljaj izberite Excelovi dodatki. Nato kliknite gumb Pojdi.

  6. V pogovornem oknu Dodatki potrdite polje ob imenu, s katerim ste shranili delovni zvezek, kot je prikazano spodaj.

    pogovorno okno »Dodatki«

  1. Ko ustvarite želeno funkcijo, kliknite Datoteka > Shrani kot.

  2. V pogovornem oknu Shrani kot odprite spustni seznam Shrani kot vrsto, nato pa izberite Excelov dodatek. Shranite delovni zvezek s prepoznavnim imenom, kot je Moje funkcije.

  3. Ko shranite delovni zvezek kliknite Orodja > Excelovi dodatki.

  4. V pogovornem oknu Dodatki izberite gumb »Prebrskaj«, da poiščete dodatek, nato kliknite Odpri, potrdite polje ob dodatku v polju Dodatki, ki so na voljo.

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.

poimenovani modul v vbe

Č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.

Potrebujete dodatno pomoč?

Kadar koli lahko zastavite vprašanje strokovnjaku v skupnosti tehničnih strokovnjakov za Excel, pridobite podporo skupnosti Answers ali predlagate novo funkcijo oziroma izboljšavo na spletnem mestu Excel User Voice.

Razširite poznavanje Officea
Oglejte si izobraževanje
Prvi dobite nove funkcije
Pridružite se programu Office Insider

Vam je bila informacija v pomoč?

Zahvaljujemo se vam za povratne informacije.

Zahvaljujemo se vam za povratne informacije. Videti je, da bi vam prišla prav pomoč enega od naših Officeovih agentov za podporo.

×