Office
Logi sisse

Excelis kohandatud funktsiooni loomine

Kuigi Excelis on palju töölehefunktsioone, võib siiski mõni teie arvutuste jaoks vajalik funktsioon puududa. Exceli loojatel polnud võimalik luua absoluutselt kõigi kasutajate vajadustele vastavat rakendust. Excel võimaldab luua kohandatud funktsioone, mida on selles artiklis kirjeldatud.

Sarnaselt makrodele salvestatakse kohandatud funktsioonid programmeerimiskeeles Microsoft 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. Selles artiklis antakse ülevaade selle kohta, 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.

Ilma kohandatud funktsioonita tellimisvormi näide

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

  1. Vajutage Visual Basic Editori avamiseks klahvikombinatsiooni Alt+F11 (Mac-arvutis vajutage klahvikombinatsiooni FN+ALT+F11) ja seejärel valige 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

Märkus.: Koodi loetavamaks muutmiseks saate tabeldusklahvi (Tab) abil ridu taandada. Taande on ainult teie huvides ja on valikuline, sest kood töötab ka ilma selleta. Pärast taandrea lisamist eeldab Visual Basic Editor, et ka järgmine rida on samamoodi taandatud. Ühe tabeldusmärgi võrra väljapoole ehk vasakule liikumiseks vajutage klahvikombinatsiooni Shift+Tab.

Uus funktsioon DISCOUNT on nüüd kasutamiseks valmis. 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.

Kohandatud funktsiooniga tellimisvormi näide

Järgmises koodiplokis olev If-lause analüüsib koguse argumenti ja selgitab välja, kas tooteid on müüdud vähemalt 100 tükki.

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 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, mis ei kasuta argumente (nt RAND ja NOW).

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 saab kasutada vähem VBA märksõnu kui makrodes. Kohandatud funktsioonid ei saa teha midagi peale väärtuse tagastamisele töölehel olevale valemile või mõnes muus VBA makros või funktsioonis kasutatavale avaldisele. Näiteks ei saa kohandatud funktsioonid akna suurust muuta, lahtris olevat valemit redigeerida ega muuta lahtriteksti fonti, värvi ega mustrit. Kui funktsioonitoimingusse lisatakse seda tüüpi „toimingu“ kood, tagastab funktsioon tõrke #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. Lisaks saate kasutada kohandatud dialoogibokse ja kasutajavorme (UserForms), aga see pole enam selle sissejuhatuse teema.

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 tarvis tulevikus koodi muuta, saate paremini aru, mida te algselt tegite.

Kommentaaridega VBA-funktsiooni näide

Ülakoma ütleb Excelile, et kõike, mis on ülakoma järel samal real, tuleb ignoreerida, nii et saate kommentaare lisada otse ridadele või VBA-koodi sisaldavatest ridadest paremale. 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 kirjeldavad nimed on eriti kasulikud siis, kui olete loonud palju toiminguid ja eriti, kui olete loonud toiminguid, mis on sarnased, kuid mitte identsed.

Makrode ja kohandatud funktsioonide dokumenteerimise viis sõltub isiklikest eelistustest. Oluline on seda teha konkreetset meetodit järgides ja järjekindlalt.

Kohandatud funktsiooni kasutamiseks peab olema avatud see töövihik, mis sisaldab moodulit, milles te funktsiooni lõite. Kui töövihik pole avatud, kuvatakse funktsiooni kasutamisel tõrketeade #NAME?. Kui viitate funktsioonile mõnes muus töövihikus, tuleb funktsiooni nime ette lisada selle töövihiku nimi, kust funktsioon pärineb. Näiteks kui loote funktsiooni DISCOUNT töövihikus Isiklik.xlsb ja kutsute selle funktsiooni mõnes muus töövihikus, peate kirjutama =isiklik.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:

Dialoogiboks Funktsiooni lisamine

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.

  1. Pärast soovitud funktsioonide loomist valige Fail > Salvesta nimega.

    Rakenduses Excel 2007 klõpsake Microsoft Office’i nuppu ja siis käsku Salvesta nimega.

  2. Dialoogiboksis Nimega salvestamine avage ripploend Salvestustüüp ja valige Exceli lisandmoodul. Salvestage töövihik äratuntava nimega (nt MinuFunktsioonid) kausta Lisandmoodulid. Dialoogiboksis Nimega salvestamine pakutakse välja see kaust, nii et teil tuleb lihtsalt vaikeasukoht aktsepteerida.

  3. Pärast töövihiku salvestamist valige Fail > Exceli suvandid.

    Rakenduses Excel 2007 klõpsake Microsoft Office’i nuppu ja siis nuppu Exceli suvandid.

  4. Klõpsake dialoogiboksis Exceli suvandid kategooriat Lisandmoodulid.

  5. Valige ripploendis Halda väärtus Exceli lisandmoodulid. Seejärel klõpsake nuppu Mine.

  6. Dialoogiboksis Lisandmoodulid märkige oma salvestatud töövihiku ruut, nagu on näidatud allpool.

    lisandmoodulite dialoogiboks

  1. Pärast soovitud funktsioonide loomist valige File (Fail) > Save As (Salvesta nimega).

  2. Dialoogiboksis Save As (Nimega salvestamine) avage ripploend Save As Type (Save As Type) ja valige Excel Add-In (Exceli lisandmoodul). Salvestage töövihik äratuntava nimega (nt MinuFunktsioonid).

  3. Pärast töövihiku salvestamist valige Tools (Tööriistad) > Excel Add-Ins (Exceli lisandmoodulid).

  4. Dialoogiboksis Add-Ins valige nupp Browse (Sirvi), et oma lisandmoodul üles otsida, klõpsake nuppu Open (Ava) ja märkige oma lisandmooduli ruut jaotises Add-Ins Available (Saadaolevad lisandmoodulid).

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.

nimega moodul vbe-s

Project Exploreris selle mooduli topeltklõpsamisel kuvab Visual Basic Editor teie funktsiooni koodi. 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.

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 oma küsimuse alati esitada mõnele Exceli tehnikakogukonna eksperdile, otsida abi vastustefoorumist või soovitada mõnd uut funktsiooni või täiustust Exceli User Voice’i lehel.

Täiendage Office'i kasutamise oskusi
Tutvuge koolitusmaterjalidega
Kasutage uusi funktsioone enne teisi
Liituge Office Insideri programmiga

Kas sellest teabest oli abi?

Täname tagasiside eest!

Täname tagasiside eest! Tundub, et võiksime teid kokku viia ühega meie Office'i tugiagentidest, kes aitab teil probleemi lahendada.

×