Kreiranje prilagođenih funkcija u programu Excel

Iako Excel uključuje mnogo ugrađenih funkcija radnog lista, verovatno nema funkciju za svaki tip izračunavanja koje izvršite. Dizajneri Excel ni moguće predvideti potrebe izračunavanja svakog korisnika. Umesto Excel da kreirate prilagođene funkcije, koje su objašnjene u ovom članku.

Prilagođene funkcije, kao što su makroi, koriste programski Visual Basic za aplikacije (VBA). Oni se razlikuju od makroa na dva značajna načina. Prvo koriste procedure funkcije umesto Sub procedura. To jesti, oni počinju izrazom Funkcije umesto izjave Sub, a završavaju se sa Funkcijom End umesto sa End Sub. 2. Oni obavljaju izračunavanja umesto da preduzimaju radnje. Određene vrste izraza, kao što su izjave koje biraju 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, koristite program Visual Basic Editor (VBE)koji se otvara u novom prozoru odvojeno od Excel.

Recimo 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 demonstriraćemo funkciju za izračunavanje ovog popusta.

Primer ispod prikazuje obrazac porudžbine koji navodi svaku stavku, količinu, cenu, popust (ako ga ima) i rezultirajući ukupnu cenu.

Example order form without a custom function

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

  1. Pritisnite kombinaciju tastera Alt+F11 da biste otvorili Visual Basic uređivaču (na Mac uređaju pritisnite FN+ALT+F11), a zatim izaberite stavku Umetni >modulu. Novi prozor modula se pojavljuje sa desne strane prozora 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
    

Napomena: Da bi kôd bio čitanjeniji, možete da koristite taster Tab za uvlačenje redova. Uvlačenje je samo za vašu korist i opcionalno je jer će se kôd pokrenuti sa kodom ili bez njega. Kada otkucate uvučeni red, Visual Basic uređivač pretpostavlja da će sledeći red biti slično uvučen. Da biste se premestili sa jednog tabulatora nalevo (to jest, nalevo), pritisnite kombinaciju tastera Shift+Tab.

Sada ste spremni da koristite novu funkciju DISCOUNT. Zatvorite Visual Basic uređivača, izaberite ćeliju G7 i otkucajte sledeće:

=DISCOUNT(D7,E7)

Excel izračunava 10 procenata popusta na 200 jedinica od 47,50 USD po jedinici i daje 950,00 USD.

U prvom redu VBA koda funkcija DISCOUNT(količina, cena), naznačeno je da funkcija DISCOUNT zahteva dva argumenta, količinu i cenu. Kada pozovete funkciju u ćeliju radnog lista, morate da uključite ta dva argumenta. U formuli =DISCOUNT(D7,E7), D7 je argument količine, a E7 argument cene. Sada možete da kopirate formulu DISCOUNT u G8:G13 da biste prikazali rezultate prikazane ispod.

Hajde da razmislimo o tome kako Excel tumači proceduru ove funkcije. Kada pritisnete taster Enter,Excel traži ime DISCOUNT u trenutnoj radnoj svesci i pronalazi da je to prilagođena funkcija u VBA modulu. Imena argumenata u zagradama, količini i ceni sučuvari mesta za vrednosti na kojima je zasnovano izračunavanje popusta.

Example order form with a custom function

Izjava If u sledećem bloku koda ispituje argument količine i određuje da li je broj prodatih artikala veći od ili jednak 100:

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

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

Discount = quantity * price * 0.1

Rezultat je uskladišten kao promenljivi popust. VBA izjava koja skladišti vrednost u promenljivoj naziva se izjava o dodeli, jer procenjuje izraz sa desne strane znaka jednakosti i dodeljuje rezultat promenljivom imenu 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 se zove funkcija DISCOUNT.

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

Discount = 0

Na kraju, sledeći izraz zaokružava vrednost dodeljenu promenljivoj Popust na dva decimalna mesta:

Discount = Application.Round(Discount, 2)

VBA nema funkciju ROUND, ali Excel funkcija ROUND. Stoga, da biste koristili funkciju ROUND u ovoj izjavi, govorite VBA da potraži metod Round (funkcija) u objektu Aplikacija (Excel). To možete da uradite tako što ćete dodati reč Aplikacija pre reči Zaokruži. Koristite ovu sintaksu kad god je potrebno da pristupite Excel iz VBA modula.

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

Nakon izjave Funkcije, procedura funkcije uključuje jednu ili više VBA izjava koje donose odluke i obavljaju izračunavanja pomoću argumenata prosleđivanih funkciji. Na kraju, negde u proceduri funkcije morate da uključite izjavu koja dodeljuje vrednost promenljivoj sa istim imenom kao i funkcija. Ova vrednost se vraća formuli koja poziva funkciju.

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 ništa osim da daju vrednost formuli u radnom listu ili izrazu 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 promene opcije fonta, boje ili šare teksta u ćeliji. Ako u proceduru funkcije uključite kôd "radnje", funkcija vraća #VALUE! grešku.

Jedna radnja koju procedura funkcije može da obavi (osim izvršavanja izračunavanja) jeste prikaz dijaloga. InputBox izjavu u prilagođenoj funkciji možete da koristite kao način da korisniku koji izvršava funkciju pristupi unosu. MsgBox izjavu možete da koristite kao način da korisniku prenesete informacije. Možete da koristite i prilagođene dijalozi ili korisničke oblike,ali to je tema izvan okvira ovog uvoda.

Čak može biti teško čitati čak i jednostavne makroe i prilagođene funkcije. Možete da ih učinite lakšim za razumevanje tako što ćete kucati tekst detalja u obliku komentara. Komentare dodajete tako što pre teksta detalja dodajete apostrof. Na primer, sledeći primer prikazuje funkciju DISCOUNT sa komentarima. Dodavanje ovakvih komentara olakšava vama ili drugima da održavate VBA kôd kada vreme prođe. Ako ubuduće treba da promenite kôd, biće vam lakše da razumete šta ste prvobitno uradili.

Primer VBA funkcije sa komentarima

Apostrof vam govori Excel da zanemarite sve sa desne strane u istom redu, tako da možete da kreirate komentare u redovima sami ili sa desne strane redova koji sadrže VBA kôd. Možete da započnete relativno dugačak blok koda sa komentarom koji objašnjava njegovu ukupnu svrhu, a zatim da koristite komentare u samom dokumentu za pojedinačne izjave.

Drugi način za dokumentovanje makroa i prilagođenih funkcija jeste da im date opisna imena. Na primer, umesto da imenuju oznake makroa ,možete da je napišete "MonthLabels" da biste opisali tačnije svrhu zbog koje makro služi. Korišćenje opisnih imena za makroe i prilagođene funkcije naročito je korisno kada kreirate mnogo procedura, naročito ako kreirate procedure koje imaju slične, ali ne i identične svrhe.

Način na koji dokumentirate makroe i prilagođene funkcije je samo lične željene postavke. Važno je da usvojite neki metod dokumentacije i da ga dosledno koristite.

Da biste koristili prilagođenu funkciju, radna sveska koja sadrži modul u kojem ste kreirali funkciju mora da bude otvorena. Ako ta radna sveska nije otvorena, dobijate #NAME? grešku prilikom pokušaja korišćenja funkcije. Ako upuć koristite funkciju u različitoj radnoj svesci, imenu funkcije morate pre imena radne sveske u kojoj se funkcija čuva. Na primer, ako u radnoj svesci koja se zove POPUSTI kreirate funkciju pod ikonim Personal.xlsb i pozovete tu funkciju iz druge radne sveske, morate da otkucate =personal.xlsb!discount(), a ne samo =discount().

Možete da sačuvate neke tastere (i moguće greške u kucanju) tako što ćete izabrati prilagođene funkcije iz dijaloga Umetanje funkcije. Prilagođene funkcije se pojavljuju u kategoriji Korisnički definisane:

insert function dialog box

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

  1. Kada kreirate funkcije koje su vam potrebne, izaberite stavku Datoteka > Sačuvaj kao.

    U Excel 2007 kliknite na dugme Microsoft kancelarija , azatim izaberite stavku Sačuvaj kao

  2. U dijalogu Sačuvaj kao otvorite padajuću listu Sačuvaj kao tip i izaberite stavku Excel Programski dodatak. Sačuvajte radnu svesku pod prepoznatljivim imenom, kao što je MojaFunctions, u fascikli Dodaci. Dijalog Sačuvaj kao će predložiti tu fasciklu, tako da sve što treba da uradite jeste da prihvatite podrazumevanu lokaciju.

  3. Kada sačuvate radnu svesku, izaberite stavku Datoteka > Excel Opcije.

    U Excel 2007 kliknite na dugme Microsoft kancelarija , azatim izaberite stavku Excel Opcije.

  4. U dijalogu Excel Opcije izaberite kategoriju Programski dodaci.

  5. Sa padajuće liste Upravljanje izaberite stavku Excel Programski dodaci. Zatim kliknite na dugme Idi.

  6. U dijalogu Programski dodaci potvrdite izbor u polju za potvrdu pored imena koje ste koristili za čuvanje radne sveske, kao što je prikazano ispod.

    add-ins dialog box

  1. Kada kreirate funkcije koje su vam potrebne, izaberite stavku Datoteka > Sačuvaj kao.

  2. U dijalogu Sačuvaj kao otvorite padajuću listu Sačuvaj kao tip i izaberite stavku Excel Programski dodatak. Sačuvajte radnu svesku pod prepoznatljivim imenom, kao što je MojaFunctions.

  3. Kada sačuvate radnu svesku, izaberite stavku Alatke > Excel stavku Programski dodaci.

  4. U dijalogu Programski dodaci kliknite na dugme Potraži da biste pronašli programski dodatak, kliknite na dugme Otvori,a zatim potvrdite izbor u polju za potvrdu pored Add-In u polju Dostupni programski dodaci.

Kada ispratite ove korake, prilagođene funkcije će biti dostupne svaki put kada Excel. Ako želite da dodate u biblioteku funkcija, vratite se u Visual Basic Editor. Ako pogledate u Visual Basic Editor Project u okviru VBA naslovaProject, videćete modul nazvan po datoteci programske dodatke. Programski dodatak će imati oznaku tipa datoteke .xlam.

named module in vbe

Dvostrukim klikom na taj modul u istraživaču Project ćete Visual Basic uređivaču prikazati kôd funkcije. Da biste dodali novu funkciju, postavite mesto umetanja posle izjave Završi funkciju koja obustavlja poslednju funkciju u prozoru "Kôd" 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 kategoriji Korisnički definisane u dijalogu Umetanje funkcije.

Ovaj sadržaj prvobitno su autori Mark Dodge i Toma Bešović u sklopu knjige na Microsoft kancelarija Excel 2007 Inside Out. Ona je posle toga ažurirana tako da se primenjuje i na novije verzije Excel verzije.

Potrebna vam je dodatna pomoć?

Možete uvek da postavite pitanje stručnjaku u zajednici Excel Tech Community, dobijete podršku u zajednici Answers community ili predložite novu funkciju ili poboljšanje na sajtu Excel User Voice.

Potrebna vam je dodatna pomoć?

Razvijte Office veštine
Istražite obuku
Prvi nabavite nove funkcije
Pridružite se Office Insider korisnicima

Da li su vam ove informacije koristile?

Hvala za povratne informacije! Izgleda da će biti od pomoći ako vas povežemo sa našim agentima Office podrške.

×