Stvaranje prilagođenih funkcija u programu Excel

Iako Excel sadrži mnoštvo ugrađenih funkcija radnog lista, vjerojatno nema funkciju za svaku vrstu izračuna koji izvodite. Dizajneri programa Excel ne mogu predvidjeti potrebe svakog korisnika za izračun. Excel će vam umjesto toga pružiti mogućnost stvaranja prilagođenih funkcija, koje su objašnjene u ovom članku.

Tražite li informacije o tome kako stvoriti prilagođenu funkciju JavaScripta koju možete pokrenuti u programu Excel za Windows, Excel za Mac ili Excel Online ? Ako jeste, pročitajte članak Pregled prilagođenih funkcija programa Excel.

Prilagođene funkcije, kao što su makronaredbe, koriste programski jezik za Visual Basic for Applications (VBA) . Razlikuju se od makronaredbi na dva značajna načina. Prvo, oni koriste postupke funkcije umjesto subprocedure . To jest, oni počinju s izjavom funkcije umjesto podizvatkom i završavaju s funkcijom end , a ne na kraju sub. Drugo, oni obavljaju proračune umjesto da poduzimaju akcije. Određene vrste izjava, kao što su izjave koje odabiru i oblikujete raspone, isključeni su iz prilagođenih funkcija. U ovom ćete članku naučiti stvarati i koristiti prilagođene funkcije. Da biste stvorili funkcije i makronaredbe, radite s programom Visual Basic Editor (VBE), koji se otvara u novom prozoru odvojen od programa Excel.

Pretpostavimo da vaša tvrtka nudi popust na količinu od 10 posto na prodaju proizvoda, pod uvjetom da je narudžba za više od 100 jedinica. U sljedećim odlomcima prikazat će se funkcija koja će izračunati ovaj popust.

U primjeru u nastavku prikazan je obrazac narudžbe koji navodi svaku stavku, količinu, cijenu, popust (ako ih ima) i ostvarenu proširenu cijenu.

Primjer obrasca narudžbe bez prilagođene funkcije

Da biste stvorili prilagođenu funkciju popusta u ovoj radnoj knjizi, slijedite ove korake:

  1. Pritisnite ALT + F11 da biste otvorili Visual Basic Editor (na Macu, pritisnite FN + ALT + F11), a zatim kliknite Umetni > module. Na desnoj strani uređivača programa Visual Basic prikazat će se prozor novog modula.

  2. Kopirajte i zalijepite sljedeći kod 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
    

Napomena: Da bi vaš kod bio čitljiviji, pomoću tipke tabulatora možete uvući retke. Uvlake je samo za vašu dobrobit i nije obavezno, budući da će se kod pokretati sa ili bez nje. Kada upišete uvučeni redak, Visual Basic Editor pretpostavlja da će sljedeći redak biti slično uvučen. Da biste se pomaknuli (to jest, na lijevoj strani) jedan znak tabulatora, pritisnite SHIFT + TAB.

Sada ste spremni za korištenje nove funkcije popusta. Zatvori Visual Basic Editor, odaberite Cell G7, a zatim upišite sljedeće:

= POPUST (D7; E7)

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

U prvom retku VBA koda, funkcijski popust (količina, cijena), naznačili ste da je funkcija DISCOUNT potrebna dva argumenta, Količina i Cijena. Kada nazivate funkciju u ćeliji radnog lista, morate uvrstiti ta dva argumenta. U formuli = DISCOUNT (D7, E7), D7 je argument Količina , a E7 je argument Price . Sada možete kopirati formulu popusta na G8: G13 da biste dobili prikazane rezultate u nastavku.

Razmotrimo način na koji Excel interpretira ovu funkciju postupka. Kada pritisnete Enter, Excel traži popust na naziv u aktualnoj radnoj knjizi i pronalazi da je to Prilagođena funkcija u VBA modulu. Nazivi argumenata koji su zatvoreni u zagradama, Količina i Cijenarezervirana su mjesta za vrijednosti na kojima se temelji izračun popusta.

Primjer obrasca narudžbe s prilagođenom funkcijom

Naredba if u sljedećem bloku koda ispituje argument Količina i određuje je li broj prodane stavke veći ili jednak 100:

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

Ako je broj prodane prodaje veći od ili jednak 100, VBA izvršava sljedeću naredbu koja množi vrijednost količine prema vrijednosti Cijena , a zatim rezultat množi s 0,1:

Discount = quantity * price * 0.1

Rezultat se pohranjuje kao varijabilni popust. VBA izjava koja pohranjuje vrijednost u varijablu naziva se naredba Dodjela , jer ona procjenjuje izraz na desnoj strani znaka jednakosti i dodjeljuje rezultat nazivu varijable na lijevom kutu. Budući da varijabilni popust ima isti naziv kao i procedura funkcije, vrijednost pohranjena u varijabli vraća se u formulu radnog lista koja se zove funkcija Discount.

Ako je Količina manja od 100, VBA izvršava sljedeću naredbu:

Discount = 0

Na kraju, sljedeća naredba zaokružuje vrijednost dodijeljenu varijablu popusta na dva decimalna mjesta:

Discount = Application.Round(Discount, 2)

VBA nema funkciju ROUND, no Excel radi. Prema tome, da biste u ovoj izjavi koristili krug, recite VBA da potraži okrugli način (funkcija) u objektu aplikacije (Excel). To možete učiniti dodavanjem aplikacije programa Word prije kruga riječi. Ovu sintaksu možete koristiti kad god vam je potrebno za pristup funkciji programa Excel u VBA modulu.

Prilagođena funkcija mora početi s izjavom funkcije i završiti pomoću naredbe end (funkcija). Osim naziva funkcije, naredba funkcija obično određuje jedan ili više argumenata. Međutim, možete stvoriti funkciju bez argumenata. Excel sadrži nekoliko ugrađenih funkcija – RAND i NOW, na primjer – koji ne koriste argumente.

Prateći naredbu funkcija, postupak funkcije sadrži jednu ili više VBA izjava koje donose odluke i obavljaju izračuni pomoću argumenata proslijeđen u funkciju. Na kraju, negdje u proceduri funkcije, morate uvrstiti izjavu koja će dodijeliti vrijednost varijabli istog naziva kao i funkcija. Ta se vrijednost vraća u formulu koja naziva funkciju.

Broj VBA ključnih riječi koje možete koristiti u prilagođenim funkcijama manji je od broja koji možete koristiti u makronaredbama. Prilagođene funkcije ne smiju raditi ništa drugo osim povratka vrijednosti u formulu na radnom listu ili na izraz koji se koristi u drugoj VBA makronaredbi ili funkciji. Primjerice, prilagođene funkcije ne mogu promijeniti veličinu prozora, uređivati formulu u ćeliji ili promijeniti font, boju ili mogućnosti uzorka teksta u ćeliji. Ako u proceduri funkcije uvrstite "akcijski" kod ove vrste, funkcija vraća #VALUE! .

Jedna akcija procedure koju funkcija može učiniti (osim izvođenja izračuna) prikazuje dijaloški okvir. Naredbu InputBox možete koristiti u prilagođenoj funkciji kao sredstvo dobivanja unosa od korisnika koji izvršava funkciju. Možete koristiti naredbu MsgBox kao sredstvo prijenosa informacija korisniku. Možete koristiti i prilagođene dijaloške okvire ili korisničke obrasce, ali to je predmet izvan dosega ovog uvođenja.

Čak i jednostavne makronaredbe i prilagođene funkcije mogu biti teške za čitanje. Možete ih jednostavnije razumjeti unosom teksta koji objašnjava u obliku komentara. Možete dodavati komentare tako da prethodni tekst s objašnjenjem sadrži apostrof. Primjerice, u sljedećem se primjeru prikazuje funkcija DISCOUNT s komentarima. Dodavanjem komentara kao što je ovo olakšava vas ili druge da održavate VBA kod dok prolazi vrijeme. Ako ubuduće morate unijeti promjenu u kod, bit će vam lakše shvatiti što ste prvobitno napravili.

Primjer VBA funkcije s komentarima

Apostrof programu Excel kaže da zanemari sve desno u istom retku, tako da možete stvarati komentare u recima sami ili na desnoj strani redaka koji sadrže VBA kod. Možete započeti relativno dugi blok koda s komentarom koji objašnjava njegovu ukupnu namjenu, a zatim pomoću umetnutih komentara dokumentirati pojedinačne izjave.

Na neki drugi način dokumentirati makronaredbe i prilagođene funkcije jest dati im opisne nazive. Ako, primjerice, ne Imenujte naljepnicemakronaredbi, možete ga imenovati da biste preciznije opisali svrhu koju makronaredba služi. Korištenje opisnih naziva za makronaredbe i prilagođene funkcije posebno je korisno kada ste stvorili mnoge procedure, osobito ako stvarate postupke koji imaju slične, ali ne i identične svrhe.

Način dokumenta makronaredbi i prilagođenih funkcija pitanje je osobnog preferenci. Važno je usvojiti neku metodu dokumentacije i dosljedno je koristiti.

Da bi se koristila Prilagođena funkcija, radna knjiga koja sadrži modul u kojem ste stvorili funkciju mora biti otvorena. Ako ta radna knjiga nije otvorena, dobivate #NAME? Pogreška prilikom pokušaja korištenja funkcije. Ako referencirate funkciju u drugoj radnoj knjizi, morate prethoditi nazivu funkcije uz naziv radne knjige u kojoj se funkcija nalazi. Ako, primjerice, stvorite funkciju koja se zove popust u radnoj knjizi koja se zove Personal. xlsb, a tu funkciju nazivate iz druge radne knjige, morate upisati = Personal. xlsb! popust (), ne samo = DISCOUNT ().

Možete sebi uštedjeti neke tipke (i moguće pogreške prilikom pisanja) odabirom prilagođenih funkcija u dijaloškom okviru Umetanje funkcije. Prilagođene funkcije prikazat će se u kategoriji korisnički definirana:

Odabir mogućnosti izraza u oknu Grupiranje, sortiranje i ukupni zbroj

Jednostavnije je redovito omogućiti prilagođene funkcije da biste ih pohranili u zasebnu radnu knjigu, a zatim spremite radnu knjigu kao dodatak. Dodatak možete učiniti dostupnom prilikom svakog pokretanja programa Excel. Evo kako to učiniti:

  1. Kada stvorite funkcije koje su vam potrebne, kliknite datoteka > Spremi kao.

    U Excel 2007 kliknite gumb Microsoft Office, a zatim Spremi kao

  2. U dijaloškom okviru Spremanje u obliku otvorite padajući popis Spremi u obliku , a zatim odaberite dodatak programa Excel. Spremite radnu knjigu u obliku prepoznatljivog naziva, kao što je Myfunctions, u mapi Addins . U dijaloškom okviru Spremanje u obliku predložit će se ta mapa, pa sve što trebate učiniti jest prihvatiti zadano mjesto.

  3. Kada spremite radnu knjigu, kliknite datoteka > Mogućnosti programa Excel.

    U Excel 2007 kliknite gumb Microsoft Office, a zatim Mogućnosti programa Excel.

  4. U dijaloškom okviru Mogućnosti programa Excel kliknite kategoriju Dodaci .

  5. Na padajućem popisu Upravljanje odaberite Dodaci programa Excel. Zatim kliknite gumb Kreni .

  6. U dijaloškom okviru Dodaci potvrdite okvir pokraj naziva koji ste koristili za spremanje radne knjige, kao što je prikazano u nastavku.

    add-ins dialog box

  1. Kada stvorite funkcije koje su vam potrebne, kliknite datoteka > Spremi kao.

  2. U dijaloškom okviru Spremanje u obliku otvorite padajući popis Spremi u obliku , a zatim odaberite dodatak programa Excel. Spremite radnu knjigu ispod prepoznatljivog naziva, kao što je Myfunctions.

  3. Kada spremite radnu knjigu, kliknite alati > Dodaci programa Excel.

  4. U dijaloškom okviru Dodaci odaberite gumb Pregledaj da biste pronašli dodatak, kliknite Otvori, a zatim potvrdite okvir uz dodatak u okviru Dostupni dodaci .

Kada slijedite ove korake, prilagođene će vam funkcije biti dostupne prilikom svakog pokretanja programa Excel. Ako želite dodati u biblioteku funkcija, vratite se u Visual Basic Editor. Ako u eksploreru za Visual Basic Editor pogledate u naslovu VBAProject, prikazat će se modul nazvan po datoteci dodatka. Dodatak će imati ekstenziju. xlam.

Otvaranje datoteke u isključivom načinu

Dvostrukim klikom na taj modul u eksploreru za projekt Visual Basic Editor prikazuje vaš kod funkcije. Da biste dodali novu funkciju, postavite točku umetanja iza naredbe end funkcija koja prestaje s posljednjim funkcijom u prozoru kod i počnite tipkati. Na taj način možete stvoriti onoliko funkcija koliko vam je potrebno, a one će uvijek biti dostupne u kategoriji korisnički definirana u dijaloškom okviru Umetanje funkcije .

Taj je sadržaj izvorno autor oznake Dodge i Craig Stinson u sklopu njihove knjige Microsoft Office Excel 2007. Već je ažuriran da bi se primijenio i na novije verzije programa Excel.

Je li vam potrebna dodatna pomoć?

Postavite pitanje stručnjaku u tehničkoj zajednici za Excel, zatražite podršku u zajednici za odgovore ili predložite novu značajku ili poboljšanje na forumu za Excel User Voice.

Napomena:  Ova je stranica strojno prevedena te može sadržavati gramatičke pogreške ili netočnosti. Naša je namjera da vam ovaj sadržaj bude koristan. Jesu li vam te informacije bile korisne? Kao referencu možete pogledati i članak na engleskom jeziku.​

Proširite svoje vještine korištenja sustava Office
Istražite osposobljavanje

Jesu li vam ove informacije bile korisne?

Hvala vam na povratnim informacijama!

Hvala vam na povratnim informacijama! Čini se da bi vam pomoglo kad bismo vas povezali s nekim od naših agenata podrške za Office.

×