Mukautettujen funktioiden luominen Excelissä

Vaikka Excel sisältää monia valmiita taulukkolaskennan toimintoja, siinä ei silti välttämättä ole toimintoa kaikille laskutoimituksille, joita haluat tehdä. Excelin suunnittelijat eivät pysty ottamaan huomioon kaikkia mahdollisia laskentatarpeita. Sen sijaan Excelissä voit luoda mukautettuja funktioita, jotka selitetään tässä artikkelissa.

Yksinkertaisen mukautetun funktion luominen

Mukautetut funktiot, kuten makrot, käyttävät Visual Basic for Applications (VBA) -ohjelmointikieltä. Ne eroavat makroista kahdella merkittävällä tavalla. Ensinnäkin niissä käytetään Funktio-toimintosarjoja Sub-toimintosarjojen sijaan. Tämän johdosta ne alkavat Function-lausekkeella Sub-lausekkeen sijaan ja päättyvät End Function -lausekkeeseen End Sub -lausekkeen sijaan. Lisäksi ne suorittavat laskutoimituksia toimintojen sijaan. Tietynlaiset lausekkeet, kuten alueita valitsevat ja muotoilevat lausekkeet, eivät kuulu mukautettuihin funktioihin. Tässä artikkelissa opit luomaan ja käyttämään mukautettuja funktioita. Voit luoda funktioita ja makroja Visual Basic Editorissa (VBE), joka avautuu uuteen Excelistä erilliseen ikkunaan.

Oletetaan, että yrityksesi tarjoaa 10 prosentin määräalennusta tuotteen myynnistä, jos tilausmäärä on yli 100 kappaletta. Seuraavissa kappaleissa esitetään funktio, joka laskee tämän alennuksen.

Alla olevassa esimerkissä näkyy tilauslomake, joka sisältää kunkin nimikkeen määrän, hinnan, mahdollisen alennuksen ja tuloksena saatavan kokonaishinnan.

Voit luoda mukautetun DISCOUNT-funktion tähän työkirjaan seuraavasti:

1. Avaa Visual Basic Editor painamalla näppäinyhdistelmää Alt+F11 (paina Macissa FN+ALT+F11) ja valitse sitten Lisää>moduuli. Uusi moduuli-ikkuna tulee näkyviin Visual Basic Editorin oikealle puolelle.

2. Kopioi ja liitä seuraava koodi uuteen moduuliin.

   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
   
   

Mukautettujen funktioiden käyttäminen

Pääset nyt käyttämään uutta DISCOUNT-funktiota. Sulje Visual Basic Editor, valitse solu G7 ja kirjoita seuraava:

=DISCOUNT(D7,E7)

Excel laskee 10 prosentin alennuksen 200 kappaleesta 47,50 dollarin kappalehinnalla ja palauttaa summan 950,00 dollaria.

VBA-koodisi ensimmäisellä rivillä Function DISCOUNT(quantity, price) ilmaisit, että DISCOUNT-funktio edellyttää kahta argumenttia: quantity ja price. Kun kutsut funktiota laskentataulukon solusta, sinun on sisällytettävä kutsuun nuo kaksi argumenttia. Kaavassa =DISCOUNT(D7,E7), D7 on quantity-argumentti ja E7 on price-argumentti. Voit nyt kopioida DISCOUNT-kaavan alueelle G8:G13, jotta saat alla näkyvät tulokset.

Tarkastellaan seuraavaksi sitä, miten Excel tulkitsee tämän funktiotoimintosarjan. Kun painat Enter-näppäintä, Excel etsii nimeä DISCOUNT nykyisestä työkirjasta ja havaitsee, että se on mukautettu funktio VBA-moduulissa. Sulkuihin merkityt argumenttien nimet quantity ja price, ovat paikkamerkkejä arvoille, joihin alennuksen laskenta perustuu.

Seuraavan koodilohkon Jos-lauseke tutkii määrä-argumenttia ja määrittää, onko myytyjen nimikkeiden määrä suurempi tai yhtä suuri kuin 100:

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

Jos myytyjen nimikkeiden määrä on suurempi tai yhtä suuri kuin 100, VBA suorittaa seuraavan lausekkeen, joka kertoo quantity-arvon price-arvolla ja kertoo sitten tuloksen 0,1:llä:

Discount = quantity * price * 0.1

Tulos tallennetaan muuttujaan Alennus. VBA-lauseketta, joka tallentaa arvon muuttujaan, kutsutaan määrityslauseeksi, koska se arvioi yhtäläisyysmerkin oikealla puolella olevan lausekkeen ja määrittää tuloksen vasemmalla olevan muuttujan arvoksi. Koska Alennus-muuttujalla on sama nimi kuin funktiotoimintosarjalla, muuttujaan tallennettu arvo palautetaan laskentataulukon kaavaan, joka kutsui DISCOUNT-funktiota.

Jos quantity on pienempi kuin 100, VBA suorittaa seuraavan lausekkeen:

Discount = 0

Lopuksi seuraava lauseke pyöristää Discount-muuttujalle määritetyn arvon kahden desimaalin tarkkuuteen:

Discount = Application.Round(Discount, 2)

VBA:ssa ei ole PYÖRISTÄ-funktiota, mutta Excelissä on. Jos haluat PYÖRISTÄ-funktiota tässä lausekkeessa, sinun tulee pyytää VBA:ta etsimään Pyöristä-menetelmä (funktio) Sovellus-objektista (Excel). Tämä tapahtuu lisäämällä Sovellus-sana Pyöristä-sanan eteen. Käytä tätä syntaksia, kun haluat käyttää Excel-funktiota VBA-moduulissa.

Tietoja mukautettuja funktioiden säännöistä

Mukautetun funktion tulee alkaa Function-lausekkeella ja päättyä End Function -lausekkeeseen. Funktion nimen lisäksi Function-lauseke yleensä määrittää yhden tai useampia argumentteja. Voit kuitenkin luoda funktion myös ilman argumentteja. Excel sisältää useita valmiita toimintoja kuten SATUNNAISLUKU ja NYT, joissa ei käytetä argumentteja.

Function-lausekkeen jälkeen funktiotoimintosarjassa tulee yksi tai useita VBA-lausekkeita, jotka tekevät päätöksiä ja suorittavat laskutoimituksia, joissa käytetään funktiolle lähetettyjä argumentteja. Sinun tulee myös lisätä funktiotoimintosarjan johonkin kohtaan lauseke, jossa funktion kanssa samannimiselle muuttujalle määritetään arvo. Tämän arvo palautetaan kaavalle, joka funktiota kutsuu.

VBA-avainsanojen käyttäminen mukautetuissa funktioissa

Mukautetuissa funktioissa voi käyttää pienempää määrää VBA-avainsanoja kuin makroissa. Mukautetut funktiot eivät voi tehdä muuta kuin palauttaa arvon laskentataulukon kaavaan tai toisessa VBA-makrossa tai -funktiossa käytettyyn lausekkeeseen. Mukautetut funktiot eivät esimerkiksi voi muuttaa ikkunoiden kokoa, muokata solussa olevaa kaavaa tai muuttaa solun tekstin fontti-, väri- tai kuvioasetuksia. Jos sisällytät tämän tyyppisen "toimintokoodin" funktiotoimintosarjaan, funktio palauttaa #VALUE! -virheen.

Funktiotoimintosarjalla voidaan suorittaa ainoastaan (lukuun ottamatta laskutoimitusten suorittamista) valintaikkunan näyttäminen. Mukautetussa funktiossa voit InputBox-lausekkeen avulla kerätä syötettä funktion suorittavalta käyttäjältä. MsgBox-lausekkeella voit esittää tietoja käyttäjälle. Voit käyttää myös mukautettuja valintaikkunoita tai käyttäjälomakkeita, mutta tämä aihe ei sisälly tähän esittelyyn.

Makrojen ja mukautettujen funktioiden dokumentointi

Jopa yksinkertaiset makrot ja mukautetut funktiot voivat olla vaikealukuisia. Voit muuntaa ne helpommin ymmärrettävään muotoon kirjoittamalla tekstiä kommentteina. Kommentti lisätään kirjoittamalla heittomerkki selittävän tekstin eteen. Esimerkiksi seuraavassa esimerkissä DISCOUNT-funktiolle on lisätty kommentteja. Tällaiset kommentit helpottavat VBA-koodin myöhempää ylläpitämistä. Jos haluat muokata koodia myöhemmin, sinun on helpompi ymmärtää, mitä teit aiemmin.

Heittomerkki kertoo Excelille, että kaikki heittomerkin oikealla puolella samalla rivillä oleva teksti tulee ohittaa, joten voit lisätä kommentteja riveille, joilla ei ole mitään muuta, tai VBA-koodia sisältävien rivien oikealle puolelle. Voit aloittaa pitkän koodilohkon kommentilla, jossa selitetään sen yleisen tarkoituksen, ja lisätä sitten yksittäisiä lausekkeita rivikommentteihin.

Toinen tapa dokumentoida makroja ja mukautettuja funktioita on antaa niille kuvaavia nimiä. Sen sijaan, että antaisit makrolle nimen Otsikot voisit esimerkiksi antaa nimeksi KuukausienOtsikot, joka kuvaa makroa paremmin. Makrojen ja mukautettujen funktioiden kuvaavat nimet ovat hyödyllisiä erityisesti silloin, kun olet luonut monia toimintosarjoja, joilla on saman tyyppiset mutta kuitenkin erilaiset tarkoitukset.

Makrojen ja mukautettujen funktioiden dokumentointitapa on makuasia. Tärkeintä on ylipäätään dokumentoida jollakin tavalla ja noudattaa sitä johdonmukaisesti.

Mukautettujen funktioiden saatavuus missä tahansa

Jos haluat käyttää mukautettua funktiota, sen moduulin sisältävän työkirjan, jossa funktio luotiin, on oltava avoinna. Jos työkirja ei ole avoinna, näyttöön tulee #NAME? -virheen, kun yrität käyttää funktiota. Jos viittaat funktioon toisessa työkirjassa, kirjoita funktion nimen eteen sen työkirjan nimi, jossa funktio sijaitsee. Jos esimerkiksi luot funktion nimeltä ALENNUS työkirjaan nimeltä Oma.xlsb ja kutsut tätä funktiota toisesta työkirjasta, sinun on kirjoitettava =oma.xlsb!alennus() eikä vain =alennus().

Voit säästyä muutamilta näppäinpainalluksilta (ja mahdollisilta kirjoitusvirheiltä) valitsemalla mukautetut funktiot Lisää funktio -valintaikkunasta. Mukautetut funktiot näkyvät Käyttäjän määrittämät -luokassa:

Mukautetut funktiot on helpompi tuoda saataville tallentamalla ne erilliseen työkirjaan ja tallentamalla työkirjan sitten apuohjelmana. Voit sitten määrittää apuohjelman saatavilla olevaksi aina, kun Excel suoritetaan. Voit tehdä tämän seuraavasti:

Kun olet suorittanut nämä toimet, mukautetut funktiosi ovat käytettävissä aina, kun suoritat Excelin. Jos haluat lisätä funktiokirjastoon, palaa Visual Basic Editoriin. Kun katsot Visual Basic Editorin Project Exploreria VBAProject-otsikon alla, näet apuohjelmatiedostosi nimen mukaisesti nimetyn moduulin. Apuohjelmallasi on tunniste .xlam.

Jos kaksoisnapsautat moduulia Project Explorerissa, Visual Basic Editor näyttää funktiokoodin. Jos haluat lisätä uuden funktion, aseta kohdistin sen End Function -lausekkeen perään, joka päättää koodi-ikkunassa viimeisen funktion, ja aloita kirjoittaminen. Voit luoda tähän tapaan niin monta funktiota kuin haluat, ja ne ovat aina käytettävissä Käyttäjän määrittämät -luokassa Lisää funktio -valintaikkunassa.

Tietoa tekijöistä

Tämän sisällön alkuperäiset tekijät ovat Mark Dodge ja Craig Stinson. Sisältö julkaistiin alunperin kirjan Microsoft Office Excel 2007 Inside Out -osana. Se on myöhemmin päivitetty koskemaan myös Excelin uudempia versioita.

Tarvitsetko lisätietoja?

Voit aina kysyä neuvoa Excel Tech Community -yhteisön asiantuntijalta tai saada tukea yhteisöistä.