Kreiranje prilagođenih funkcija u programu Excel

Iako Excel uključuje mnoštvo ugrađenih funkcija radnog lista, postoji verovatnoća da nema funkciju za svaki tip izračunavanja koji obavite. Dizajneri programa Excel ne mogu da predviđaju izračunavanje svakog korisnika. Umesto toga, Excel vam omogućava da kreirate prilagođene funkcije koje su objašnjene u ovom članku.

Da li tražite informacije o tome kako da kreirate JavaScript prilagođenu funkciju koju možete da pokrećete u programu Excel za Windows, Excel for Mac ili Excel Online ? Ako jeste, pogledajte Pregled prilagođenih funkcijau članku Excel.

Prilagođene funkcije, kao što su makroi, koristite Visual Basic for Applications (VBA) programski jezik. One se razlikuju od makroa na dva značajna načina. Prvo, oni koriste procedure funkcije umesto podprocedure . To jest, počinju izjavom funkcija umesto potizjave i završavaju se krajnjim funkcijom umesto kraja podmornice. Drugo, oni obavljaju izračunavanja umesto da preduzimaju radnje. Određene vrste izjava, kao što su izjave koje biraju i prikazuju opsege, su isključene 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 (VANBE), koja se otvara u novom prozoru odvojen od programa Excel.

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

Dolenavedene primere prikazuje obrazac porudžbine koji navodi svaku stavku, količinu, cenu, popust (ako postoji) i dobijenu proširenu cenu.

Primer obrasca porudžbine bez prilagođene funkcije

Da biste kreirali prilagođenu funkciju popusta u ovoj radnoj svesci, slijedite ove korake:

  1. Pritisnite kombinaciju tastera ALT + F11 da biste otvorili Visual Basic Editor (na Mac računaru, pritisnite 5 + ALT + F11), a zatim kliknite na dugme Umetni > modul. Pojavljuje se novi prozor modula na desnoj strani Visual Basic uređivača.

  2. Kopirajte i nalepite sledeći kôd na 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 čitljiviji, možete da koristite taster Tab za uvlačenje redova. Uvlačenje je samo za vašu korist i opcionalno je, jer se kôd pokreće sa ili bez nje. Kada otkucate uvlačen red, Visual Basic Editor pretpostavlja da će sledeći red biti slično uvučen. Da biste se pomerili (to jest, nalevo) jedan tabulatorski znak, pritisnite kombinaciju tastera SHIFT + TAB.

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

= KASASKONTA (D7, E7)

Excel izračunava popust od 10 procenata u 200 jedinicama na $47,50 po jedinici i daje $950,00.

U prvom redu VBA koda, funkcija KASASKONTA (količina, cena), naznačili ste da funkcija KASASKONTA zahteva dva argumenta, količinu i cenu. Kada poziv koristite u ćeliji radnog lista, morate da dodate ta dva argumenta. U formuli = popust (D7, E7), D7 argument količine , a E7 je argument cena . Sada možete da kopirate formulu popusta na G8: G13 da biste dobili navedene rezultate ispod.

Razmotrite kako Excel tumači ovu funkciju. Kada pritisnete taster ENTER, Excel traži popust za ime u trenutnoj radnoj svesci i pronalazi da je to prilagođena funkcija u VBA modulu. Imena argumenata u zagradama, Količina i cenasu čuvari mesta za vrednosti na kojima je zasnovan Obračun popusta.

Primer obrasca porudžbine sa prilagođenom funkcijom

Izjava IF u sledećem bloku koda ispituje argument Količina i određuje da li je broj prodate stvari veći ili jednak 100:

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

Ako je broj prodate stvari veći ili jednak 100, VBA izvršava sledeću izjavu koja množi vrednost količine po ceni , a zatim rezultat množi sa 0,1:

Discount = quantity * price * 0.1

Ishod se skladišti kao popustna promenljive. VBA izraz koji skladišti vrednost u promenljivoj naziva se naredba Dodela , zato što daje izraz na desnoj strani znaka jednakosti i daje rezultat u ime promenljive sa leve strane. Pošto promenljivi popust ima isto ime kao i procedura funkcije, vrednost uskladištena u promenljivoj se vraća formuli radnog lista koja se zove funkcija kasaskonta.

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

Discount = 0

Konačno, sledeća izjava zaokružuje vrednost koja je dodeljena promenljivoj popustu na dva decimalna mesta:

Discount = Application.Round(Discount, 2)

VBA nema funkciju ROUND, ali Excel ima. Zbog toga, da biste koristili RUNDU u ovoj izjavi, možete da kažete VBA da potraži metodu Round (funkcija) u objektu aplikacije (Excel). To uradite tako što ćete dodati Word aplikaciju pre početka reči. Koristite ovu sintaksu svaki put kada treba da pristupite funkciji Excel iz VBA modula.

Prilagođena funkcija mora da počne sa izjavom funkcije i završava se izjavom funkcije end. Pored imena funkcije, naredba funkcija obično navodi neke argumente. Međutim, možete da kreirate funkciju bez argumenata. Excel uključuje nekoliko ugrađenih funkcija – RAND i sada, na primer – koje ne koriste argumente.

Posle izvoda funkcije, procedura funkcije uključuje neke VBA izjave koji donose odluke i vrše izračunavanja pomoću argumenata prosleđeni funkciji. Na kraju, negde u proceduri funkcije, morate da dodate izjavu koja dodeli vrednost promenljive sa istim imenom kao funkcija. Ova vrednost se vraća u formulu koja poziva funkciju.

Broj VBA ključnih reči koje možete da koristite u prilagođenim funkcijama je manji od broja koji možete da koristite u makroima. Prilagođenim funkcijama nije dozvoljeno da radi ništa osim da vrati vrednost u formulu na radnom listu ili na izraz koji se koristi u drugom VBA makrou ili funkciji. Na primer, prilagođene funkcije ne mogu da promene veličinu prozora, uređuju formulu u ćeliji ili menjaju opcije fonta, boje ili obrasca za tekst u ćeliji. Ako u proceduri funkcije unesete kôd "radnja", funkcija vraća #VALUE! #VALUE!.

Jedna radnja koju funkcija funkcije može da uradi (osim izračunavanja izračunavanja) prikazaće se dijalog. Možete da koristite izjavu o prijavljivanju u prilagođenoj funkciji kao sredstvo za dobijanje unosa od korisnika koji izvršava funkciju. Možete da koristite izjavu MsgBox kao sredstvo za prenošanje informacija korisniku. Možete da koristite i prilagođene dijaloge ili Userforms, ali to je tema izvan opsega ovog predstavljanja.

Čak i jednostavni makroi i prilagođene funkcije mogu da se teško čitaju. Možete ih lakše razumeti tako što ćete otkucati tekst objašnjenja u obliku komentara. Možete da dodate komentare tako što ćete ispred teksta objašnjenja sa apostrofom. Na primer, sledeći primer prikazuje funkciju KASASKONTA sa komentarima. Dodavanje komentara kao ovih olakšava vama ili drugima da održavaju VBA kôd kao da je vreme prolazilo. Ako treba da promenite kôd u budućnosti, lakše ćete razumeti ono što ste prvobitno učinili.

Primer VBA funkcije sa komentarima

Apostrof kaže programu Excel da zanemari sve sa desne strane na istom redu, tako da možete da kreirate komentare po sebi ili na desnoj strani linija koje sadrže VBA kôd. Možete da započnete relativno Dugački blok kodova sa komentarom koji objašnjava njenu ukupnu namenu, a zatim da koristi Umetanje komentara za pojedinačne izjave.

Drugi način za dokumentovanje makroa i prilagođenih funkcija jeste da im date opisna imena. Na primer, umesto da imenujete makroe , možete da ih imenujete da biste opisali preciznije namenu makroa. Korišćenje opisnih imena za makroe i prilagođene funkcije naročito su korisne kada ste kreirali mnoge procedure, naročito ako kreirate procedure koje imaju slične, a ne identične namene.

Način na koji dokumentuju makroe i prilagođene funkcije je stvar ličnog željena. Važno je da se usvoji neki metod dokumentacije i dosledno ga koristite.

Da biste koristili prilagođenu funkciju, radna sveska koja sadrži modul u koji ste kreirali funkciju mora da bude otvorena. Ako ta radna sveska nije otvorena, da li imate #NAME? Greška prilikom pokušaja upotrebe funkcije. Ako pozivaљ funkciju u drugoj radnoj svesci, morate da prethodis imenu funkcije sa imenom radne sveske u kojoj se funkcija nalazi. Na primer, ako kreirate funkciju pod imenom "popust" u radnoj svesci pod imenom personal. xlsb i nazivate je funkcijom iz druge radne sveske, morate otkucati = Personal. xlsb! kasaskonta (), ne samo = kasaskonta ().

Možete da uštedite tastere na tastaturi (i moguće greške pri kucanju) tako što ćete izabrati prilagođene funkcije iz dijaloga "Umetanje funkcije". Prilagođene funkcije se pojavljuju u kategoriji definisanom korisnikom:

insert function dialog box

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

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

    U Excel 2007, kliknite na dugme Microsoft Officei kliknite na dugme 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 u okviru prepoznatljivog imena, kao što je Myfunkcije, u fascikli AddIns . Dijalog " Čuvanje kao " predlaže tu fasciklu, tako da sve što treba da uradite je da prihvatite podrazumevanu lokaciju.

  3. Kada sačuvate radnu svesku, izaberite stavke > Excel opcije.

    U Excel 2007 kliknite na dugme Microsoft Officei izaberite stavku Excel opcije.

  4. U dijalogu Opcije programa Excel izaberite kategoriju programski dodaci.

  5. Na padajućoj listi izaberite stavku Excel programskidodaci. Zatim kliknite na dugme Idi .

  6. U dijalogu programski dodaci potvrdite izbor u polju za potvrdu pored imena koje ste koristili da biste sačuvali radnu svesku, 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 ispod prepoznatljivog imena, kao što je Myfunkcije.

  3. Kada sačuvate radnu svesku, izaberite stavku alatke > Excel programske dodatke.

  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 programskog dodatka u dostupnim programskim dodacima .

Kada izvršite ove korake, prilagođene funkcije će biti dostupne svaki put kada pokrećete Excel. Ako želite da dodate u biblioteku funkcija, vratite se u Visual Basic Editor. Ako u programu Visual Basic Editor pogledate u okviru naslova Vabaproject, videćete modul nazvan posle vaše datoteke programskog dodatka. Programski dodatak će imati oznaku za proširenje. xlam.

named module in vbe

Ako dvaput kliknete na to, modul u istraživaču projekata uzrokuje Visual Basic Editor da bi prikazao kôd funkcije. Da biste dodali novu funkciju, postavite mesto umetanja posle izvoda funkcije end koje prekida poslednju funkciju u prozoru koda i počnite da kucate. Možete da kreirate onoliko funkcija koliko vam je potrebno na ovaj način i oni će uvek biti dostupni u dijalogu "Umetanje funkcije" u dijalogu " Umetanje funkcije ".

Ovaj sadržaj je prvobitno kreirao Mark Dodge i Craig Stinson u sklopu knjige Microsoft Office Excel 2007. Otada se ažurira i primenjuje se na novije verzije programa Excel.

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.

Napomena:  Ova stranica je prevedena automatski i može da sadrži gramatičke greške ili netačnosti. Naš cilj je da ovaj sadržaj bude koristan. Možete li da nam javite da li su informacije bile korisne? Ovo je članak na engleskom jeziku za referencu.​

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 vam na povratnim informacijama!

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

×