Excelis kohandatud funktsiooni loomine

Kuigi Excelis on palju töölehefunktsioone, pole sellel tõenäoliselt funktsiooni igat tüüpi arvutuste jaoks. Exceli kujundajad ei osanud prognoosida kõigi kasutajate arvutusvajadusi. Excel võimaldab luua kohandatud funktsioone, mida on selles artiklis kirjeldatud.

Lihtsa kohandatud funktsiooni loomine

Kohandatud funktsioonid (nt makrod) kasutavad programmeerimiskeelt Visual Basic for Applications (VBA). Need erinevad makrodest kahel viisil. Esmalt kasutatakse neid Sub-toimingute asemel Function-toiminguid. Ehk siis need algavad lausega Function, mitte lausega Sub, ja lõppevad lausega End Function, mitte lausega End Sub. Teiseks need mitte ei käivita toiminguid, vaid teevad arvutusi. Teatud tüüpi laused (nt vahemike valimiseks ja vormindamiseks) pole kohandatud funktsioonide raames saadaval. Sellest artiklist saate teada, kuidas luua ja kasutada kohandatud funktsioone. Funktsioone ja makrosid saab luua Visual Basic Editoris (VBE), mis avaneb Excelist eraldi uues aknas.

Oletame, et teie ettevõte pakub üle 100 toote ostmisel allahindlust toote hinnast 10% ulatuses. Järgmistes lõikudes on kirjeldatud selle allahindluse arvutamiseks kasutatavat funktsiooni.

Allpool toodud näites on tellimisvorm, kus on kirjas tooted, kogused, hinnad, allahindlused (kui neid on) ja lõplik laiendatud hind.

Selles töövihikus kohandatud DISCOUNT-funktsiooni loomiseks tehke järgmist.

1. Visual Basic Editori avamiseks vajutage klahvikombinatsiooni Alt+F11 (Mac-arvutis vajutage klahvikombinatsiooni FN +ALT+F11) ja seejärel klõpsake nuppu Lisa>moodul. Visual Basic Editori paremas servas kuvatakse uus mooduliaken.

2. Kopeerige ja kleepige järgmine kood uude moodulisse.

   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
   
   

Kohandatud funktsioonide kasutamine

Nüüd oletegi valmis kasutama uut funktsiooni DISCOUNT. Sulgege Visual Basic Editor, valige lahter G7 ja tippige järgmine rida:

=DISCOUNT(D7,E7)

Excel arvutab 200 toote kohta 10%-se allahindluse 47,50 dollarit ja tagastab hinna 950,00 dollarit.

VBA-koodi esimesel real on funktsioonile DISCOUNT(kogus, hind) määratud kaks argumenti: kogus ja hind. Töölehe lahtris funktsiooni kasutades tuleb lisada need kaks argumenti. Valemis =DISCOUNT(D7,E7) on D7 kogus ja E7 on hind. Nüüd saate valemi DISCOUNT kopeerida lahtrisse G8:G13, et saada allpool näidatud tulem.

Vaatame, kuidas Excel seda funktsioonitoimingut tõlgendab. Sisestusklahvi (Enter) vajutamisel otsib Excel praegusest töövihikust nime DISCOUNT ja avastab, et tegu on kohandatud funktsiooniga VBA moodulis. Sulgudega ümbritsetud argumendinimed (kogus ja hind) on nende väärtuste kohatäited, mille põhjal allahindlus arvutatakse.

If-lause järgmises koodiplokis uurib koguse argumenti ja määratleb, kas müüdud kaupade arv on 100 või suurem:

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

Kui müüdud toodete arv on 100 või suurem, käivitab VBA järgmise lause, mis korrutab koguse väärtus hinna väärtusega ja jagab tulemi 0,1-ga.

Discount = quantity * price * 0.1

Tulem salvestatakse muutujana Discount. Sellist VBA-lauset, mis salvestab väärtuse muutujana, nimetatakse määramislauseks, sest see leiab paremal oleva avaldise väärtuse ja määrab tulemi vasakul oleva muutuja väärtuseks. Kuna muutuja Discount nimi on sama nagu funktsioonitoimingul, tagastatakse muutujas talletatav väärtus sellele töölehe valemile, mis funktsiooni DISCOUNT algselt kutsus.

Kui kogus on alla 100, käivitab VBA järgmise lause:

Discount = 0

Lõpuks ümardab järgmine lause muutujale Discount määratud väärtuse kahe kümnendkohani:

Discount = Application.Round(Discount, 2)

VBA-s pole funktsiooni ROUND, aga Excelis on. Seega selles lauses funktsiooni ROUND kasutamiseks tuleb VBA-l lasta otsida meetodit (funktsiooni) Round objektist Application (Excel). Selleks tuleb sõna „Round“ ette lisada sõna Application. Kasutage seda süntaksit alati VBA-mooduli kaudu Exceli funktsioonile juurdepääsemiseks.

Kohandatud funktsiooni reeglid

Kohandatud funktsiooni alguses peab olema lause Function ja lõpus lause End Function. Lisaks funktsiooni nimele määratakse lauses Function tavaliselt ka mõni argument. Kuid saate luua funktsiooni ka ilma argumentideta. Excelis on mitu sisseehitatud funktsiooni– näiteks RAND ja NOW – mis ei kasuta argumente.

Lausele Function järgnev funktsioonitoiming sisaldab ühte või mitut VBA-lauset, mis teevad otsuseid ja arvutusi funktsioonile edastatud argumentide põhjal. Samuti tuleb funktsioonitoimingusse lisada lause, mis määrab funktsiooniga sama nime kandva muutuja väärtuse. See väärtus tagastatakse valemile, mis selle funktsiooni kutsus.

Funktsioonides VBA märksõnade kasutamine

Kohandatud funktsioonides kasutatavate VBA-märksõnade arv on väiksem kui makrodes kasutatav arv. Kohandatud funktsioonid ei tohi teha muud peale töölehel oleva valemi väärtuse tagastamise või mõnes muus VBA makros või funktsioonis kasutatava avaldise. Näiteks ei saa kohandatud funktsioonid muuta akende suurust, redigeerida lahtris valemit ega muuta lahtri teksti fonti, värvi ega mustri suvandeid. Kui lisate funktsiooniprotseduuri sedalaadi toimingukoodi, tagastab funktsioon #VALUE! #VALUE!.

Ainuke toiming, mille funktsioonitoimingu raames saab esile kutsuda (peale arvutamise), on dialoogiboksi kuvamine. Kohandatud funktsioonis saab lause InputBox abil lasta funktsiooni käivitanud kasutajal määrata sisendväärtus. Lause MsgBox abil saate kasutajale teavet edastada. Saate kasutada ka kohandatud dialoogibokse või kasutajavorme, kuid see pole selle sissejuhatuse teema.

Makrode ja kohandatud funktsioonide dokumenteerimine

Ka lihtsaid makrosid ja kohandatud funktsioone võib olla keeruline lugeda. Nende lugemise hõlbustamiseks saate lisada selgitavad tekstid kommentaaridena. Kommentaari sisestamiseks lisage teksti ette ülakoma. Järgmises näites on funktsiooni DISCOUNT koos kommentaaridega. Selliste kommentaaride lisamine aitab teil või teistel VBA-koodi ka edaspidi hõlpsalt hallata. Kui teil on vaja koodi tulevikus muuta, on teil lihtsam mõista, mida te algselt tegite.

Ülakoma käsib Excelil ignoreerida kõike, mis on samal real paremal, nii et saate kommentaare luua nii ridade kaupa kui ka VBA-koodi sisaldavate ridade paremas servas. Võite näiteks pikema koodiploki algusesse lisada kommentaari, mis selgitab selle otstarvet, ja kasutada reasiseseid kommentaare, et kirjeldada üksikuid lauseid.

Teine võimalus makrode ja kohandatud funktsioonide dokumenteerimiseks on panna neile kirjeldavad nimed. Näiteks selle asemel, et panna makro nimeks Sildid, võite nimeks panna KuuSildid, et kirjeldada makro eesmärki veel paremini. Makrode ja kohandatud funktsioonide kirjeldavate nimede kasutamine on eriti kasulik siis, kui olete loonud palju toiminguid, eriti kui olete loonud toiminguid, millel on sarnased, kuid mitte identsed eesmärgid.

Makrode ja kohandatud funktsioonide dokumenteerimise viis sõltub isiklikest eelistustest. Oluline on kasutada mõnda dokumentatsioonimeetodit ja kasutada seda järjekindlalt.

Kohandatud funktsioonide kõikjal kättesaadavaks tegemine

Kohandatud funktsiooni kasutamiseks peab teie loodud moodulit sisaldav töövihik olema avatud. Kui see töövihik pole avatud, saate #NAME? kui proovite funktsiooni kasutada. Kui viitate funktsioonile mõnes muus töövihikus, peate funktsiooni nime ette märkima selle töövihiku nime, kus funktsioon asub. Näiteks kui loote töövihikus Nimega Personal.xlsb funktsiooni DISCOUNT ja kutsute seda funktsiooni mõnest muust töövihikust, peate tippima =personal.xlsb!discount(), mitte lihtsalt =discount().

Klahvivajutuste säästmiseks (ja kirjavigade vältimiseks) saate valida oma kohandatud funktsiooni dialoogiboksis Funktsiooni lisamine. Teie kohandatud funktsioon kuvatakse kategoorias Kasutaja määratletud:

Hõlpsam viis kohandatud funktsioonide igal ajal saadavaks muutmiseks saate need salvestada eraldi töövihikusse ja seejärel salvestada selle töövihiku lisandmoodulina. Selle töövihiku saate Excelis alati kättesaadavaks muuta. Selleks tehke järgmist.

Pärast nende juhiste järgimist on kohandatud funktsioonid iga kord Exceli käitamisel kättesaadavad. Oma funktsiooniteegi täiendamiseks naaske Visual Basic Editori. Visual Basic Editori Project Exploreris on pealkirja VBAProject all teie lisandmooduli järgi nimetatud mooduli nimi. Teie lisandmoodulil on laiend .xlam.

Kui topeltklõpsate seda moodulit Project Exploreris, kuvab Visual Basic Editor teie funktsioonikoodi. Uue funktsiooni lisamiseks viige järjepunkt koodiakna viimast lauset sisaldava lause End Function lõppu, ja hakake tippima. Sel viisil saate luua nii palju funktsioone kui soovite ning need on alati saadaval dialoogiboksi Funktsiooni lisamine kategoorias Kasutaja määratletud.

Autorite kohta

See sisu oli algselt osa Mark Dodge‘i ja Craig Stinsoni raamatust Microsoft Office Excel 2007 Inside Out (Microsoft Office Excel 2007 – põhjalik ülevaade). Sellesse on hiljem lisatud teave Exceli uuemate versioonide kohta.

Kas vajate rohkem abi?

Võite alati küsida Exceli tehnikakogukonna eksperdilt või kogukonnafoorumites tuge.