Kreirajte prilagođene funkcije u programu Excel

Iako Excel sadrži mnoštvo ugrađenih funkcija radnog lista, postoji šansa da nema funkciju za svaki tip izračunavanja koji izvršite. Dizajneri programa Excel nisu mogli da predvide potrebe svakog korisnika u pogledu izračunavanja. Umesto toga, Excel vam pruža mogućnost da kreirate prilagođene funkcije koje su objašnjene u ovom članku.

Kreiranje jednostavne prilagođene funkcije

Prilagođene funkcije, poput makroa, koriste programski jezik Visual Basic for Applications (VBA). Oni se razlikuju od makroa na dva značajna načina. Prvo koriste procedure funkcije umesto podprocedura . To jest, one počinju izjavom Function umesto izjavom Sub i završavaju se sa End Function umesto sa End Sub. Drugo, oni vrše izračunavanja umesto da preduzimaju radnje. Određene vrste izraza, kao što su izjave koje izdvajaju i oblikuju opsege, izuzete su iz prilagođenih funkcija. U ovom članku ćete saznati kako da kreirate i koristite prilagođene funkcije. Da biste kreirali funkcije i makroe, radite sa programom Visual Basic Editor (VBE) koji se otvara u novom prozoru odvojenom od programa Excel.

Pretpostavimo da vaše preduzeće nudi količinski popust od 10 procenata na prodaju proizvoda, pod uslovom da je porudžbina za više od 100 jedinica. U sledećim pasusima pokazaćemo funkciju za izračunavanje ovog popusta.

Dolenavedeni primer prikazuje narudžbenicu koja navodi svaku stavku, količinu, cenu, popust (ako postoji) i dobijenu proširenu cenu.

Da biste kreirali prilagođenu funkciju DISCOUNT u ovoj radnoj svesci, pratite ove korake:

1. Pritisnite kombinaciju tastera Alt+F11 da biste otvorili Visual Basic Editor (na Mac računaru pritisnite FN+ALT+F11), a zatim izaberite stavku "Umetni>modul". Novi prozor modula se pojavljuje sa desne strane programa Visual Basic Editor.

2. Kopirajte i nalepite sledeći kôd u novi modul.

   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
   
   

Korišćenje prilagođenih funkcija

Sada ste spremni da koristite novu funkciju POPUST. Zatvorite Visual Basic Editor, izaberite ćeliju G7 i otkucajte sledeće:

=DISCOUNT(D7,E7)

Excel izračunava 10 posto popusta na 200 jedinica po ceni od 47,50 USD po jedinici i vraća 950,00 USD.

U prvom redu VBA koda, funkcije DISCOUNT(količina, cena), naveli ste da funkcija DISCOUNT zahteva dva argumenta, količinu i cenu. Kada pozovete funkciju u ćeliji radnog lista, morate da uključite ta dva argumenta. U formuli =DISCOUNT(D7,E7), D7 je argument količina , a E7 je argument cena . Sada možete da kopirate formulu POPUST u G8:G13 da biste dobili dolenavedene rezultate.

Hajde da razmotrimo kako Excel tumači ovu proceduru funkcije. Kada pritisnete taster ENTER, Excel traži ime POPUST u trenutnoj radnoj svesci i pronalazi da je to prilagođena funkcija u VBA modulu. Imena argumenata navedena u zagradama, količina i cena, predstavljaju čuvare mesta za vrednosti na kojima se zasniva izračunavanje popusta.

Izjava If u sledećem bloku kôda ispituje argument količine i određuje da li je broj prodatih stavki veći od 100 ili jednak tom broju:

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

Ako je broj prodatih stavki veći od 100 ili jednak tom broju, VBA izvršava sledeću izjavu koja množi vrednost količine vrednošću cene , a zatim rezultat množi sa 0,1:

Discount = quantity * price * 0.1

Rezultat se skladišti kao promenljiva "Popust". VBA izraz koji skladišti vrednost u promenljivoj zove se izraz dodele zato što procenjuje izraz koji se nalazi sa desne strane znaka jednakosti i dodeljuje rezultat imenu promenljive sa leve strane. Pošto promenljiva "Popust" ima isto ime kao procedura funkcije, vrednost uskladištena u promenljivoj vraća se u formulu radnog lista koja poziva funkciju DISCOUNT.

Ako je količina manja od 100, VBA izvršava sledeću izjavu:

Discount = 0

Na kraju, sledeća izjava zaokružuje vrednost dodeljenu promenljivoj "Popust" na dva decimalna mesta:

Discount = Application.Round(Discount, 2)

VBA nema funkciju ROUND, ali Excel ima. Stoga, da biste koristili ROUND u ovoj izjavi, govorite VBA da traži metod (funkciju) Round u objektu aplikacije (Excel). To radite dodavanjem reči "Aplikacija" pre reči "Round". Koristite ovu sintaksu svaki put kada treba da pristupite Excel funkciji iz VBA modula.

Razumevanje pravila prilagođenih funkcija

Prilagođena funkcija mora da počinje izrazom funkcije Function i da se završava izrazom funkcije End. Pored imena funkcije, izjava funkcije obično navodi jedan ili više argumenata. Međutim, možete da kreirate funkciju bez argumenata. Excel sadrži nekoliko ugrađenih funkcija – RAND i NOW, na primer – koje ne koriste argumente.

Iza izjave funkcije, procedura funkcije uključuje neke VBA izjave koje donose odluke i vrše izračunavanja pomoću argumenata prosleđenih funkciji. Na kraju, negde u proceduri funkcije morate da uključite izjavu koja dodeljuje vrednost promenljivoj koja ima isto ime kao i funkcija. Ova vrednost se vraća formuli koja poziva funkciju.

Korišćenje VBA ključnih reči u prilagođenim funkcijama

Broj VBA ključnih reči koje možete da koristite u prilagođenim funkcijama manji je od broja koji možete da koristite u makroima. Prilagođenim funkcijama nije dozvoljeno da rade bilo šta drugo osim da vrate vrednost u formulu na radnom listu ili izraz koji se koristi u drugom VBA makrou ili funkciji. Na primer, prilagođene funkcije ne mogu da promene veličinu prozora, urede formulu u ćeliji ili da promene opcije fonta, boje ili šare za tekst u ćeliji. Ako u proceduru funkcije uključite kôd "radnje" ove vrste, funkcija vraća #VALUE! grešku.

Jedina radnja koju procedura funkcije može da izvrši (osim proračuna) jeste prikazivanje dijaloga. Izraz InputBox možete da koristite u prilagođenoj funkciji kao sredstvo za dobijanje unosa od korisnika koji izvršava funkciju. Izraz MsgBox možete da koristite kao sredstvo za prenošenje informacija korisniku. Možete da koristite i prilagođene dijaloge ili korisničke obrasce, ali to je tema koja prevazilazi opseg ovog uvoda.

Dokumentovanje makroa i prilagođenih funkcija

Čak i jednostavni makroi i prilagođene funkcije mogu da budu teški za čitanje. Možete ih lakše razumeti tako što ćete otkucati tekst objašnjenja u obliku komentara. Komentare dodajete tako što ćete dodati apostrof ispred teksta objašnjenja. Na primer, sledeći primer prikazuje funkciju DISCOUNT sa komentarima. Dodavanje ovakvih komentara olakšava vama ili drugima da održavate VBA kôd kako vreme prolazi. Ako ubuduće treba da promenite kôd, lakše ćete razumeti šta ste prvobitno uradili.

Apostrof govori programu Excel da zanemari sve što se nalazi nadesno u istom redu, tako da možete da kreirate komentare u samostalnim redovima ili na desnoj strani redova koji sadrže VBA kôd. Relativno dugačak blok koda možete da započnete komentarom koji objašnjava njegovu celokupnu namenu, a zatim da koristite umetnute komentare da biste dokumentovali pojedinačne izjave.

Drugi način da dokumentujete makroe i prilagođene funkcije jeste da im date opisna imena. Na primer, umesto da date ime makrou "Oznake", možete mu dati ime "OznakeMeseci" da biste preciznije opisali svrhu koju makro služi. Korišćenje opisnih imena za makroe i prilagođene funkcije naročito je korisno kada imate mnogo procedura, naročito ako kreirate procedure koje imaju slične, ali ne identične svrhe.

Način na koji dokumentujete svoje makroe i prilagođene funkcije zavisi od ličnih preferencija. Ono što je važno jeste da usvojite neki metod dokumentacije i dosledno ga koristite.

Omogućavanje dostupnosti prilagođenih funkcija sa bilo kog mesta

Da biste koristili prilagođenu funkciju, radna sveska koja sadrži modul u kojem ste kreirali funkciju mora biti otvorena. Ako ta radna sveska nije otvorena, dobijate #NAME? greške prilikom pokušaja korišćenja funkcije. Ako na funkciju uputite u drugoj radnoj svesci, ispred imena funkcije morate dodati ime radne sveske u kojoj se funkcija nalazi. Na primer, ako kreirate funkciju DISCOUNT u radnoj svesci koja se zove Personal.xlsb i pozovete tu funkciju iz druge radne sveske, morate da otkucate =personal.xlsb!discount(), a ne samo =discount().

Možete sebi da uštedite neke pritiske na taster (i moguće greške u kucanju) tako što ćete izabrati prilagođene funkcije u dijalogu "Umetanje funkcije". Prilagođene funkcije se pojavljuju u kategoriji "Korisnički definisane":

Lakši način da prilagođene funkcije učinite dostupnim u svakom trenutku jeste da ih uskladištite u zasebnoj radnoj svesci, a zatim sačuvate tu radnu svesku kao programski dodatak. Zatim možete da ga učinite dostupnim svaki put kada pokrenete Excel. Evo kako to da uradite:

Pošto izvršite ove korake, prilagođene funkcije će biti dostupne svaki put kada pokrenete Excel. Ako želite da dodate sadržaj u biblioteku funkcija, vratite se u Visual Basic Editor. Ako pogledate Visual Basic Editor istraživač projekta pod naslovom VBA projekta, videćete modul nazvan po datoteci programskog dodatka. Programski dodatak će imati oznaku tipa datoteke .xlam.

named Ako kliknete dvaput na taj modul u istraživaču projekata, Visual Basic Editor prikazuje kôd funkcije. Da biste dodali novu funkciju, postavite mesto umetanja iza izjave funkcije End koja završava poslednju funkciju u prozoru Koda i počnite da kucate. Na ovaj način možete da kreirate koliko god vam je funkcija potrebno i one će uvek biti dostupne u korisnički definisanoj kategoriji u dijalogu "Umetanje funkcije ".

Više informacija o autorima

Autori ovog sadržaja su prvobitno kreirali Mark Dođ i Krejg Stinson u sklopu knjige Microsoft Office Excel 2007 Inside Out. Od tada je ažuriran tako da se primenjuje i na novije verzije programa Excel.

Potrebna vam je dodatna pomoć?

Možete uvek da postavite pitanje stručnjaku u Excel Tech zajednici ili da potražite pomoć u zajednicama.