Office
Kirjaudu sisään

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.

Mukautetuissa funktioissa, kuten makroissa, käytetään 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.

Esimerkki tilauslomakkeesta ilman mukautettua funktiota

Jos haluat luoda mukautetun DISCOUNT-funktion tähän työkirjaan, toimi seuraavasti:

  1. Avaa Visual Basic Editor painamalla 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

Huomautus: Jotta koodi olisi helpommin luettavaa, voit sisentää rivejä Sarkain-näppäimellä. Sisennyksestä on vain visuaalista hyötyä, ja ne ovat vapaaehtoisia. Koodi suoritetaan samalla tavalla sisennyksistä riippumatta. Kun olet tehnyt sisennetyn rivin, Visual Basic Editor olettaa, että haluat seuraavankin rivin olevan sisennetty vastaavasti. Jos haluat palata sisennyksessä yhden sarkainmerkin verran vasemmalle, paina näppäinyhdistelmää Vaihto+Sarkain.

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.

Esimerkki tilauslomakkeesta mukautetun funktion kanssa

Seuraavan koodilohkon If-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.

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.

Mukautetuissa funktioissa voi käyttää pienempää määrää VBA-avainsanoja kuin makroissa. Mukautetut funktiot eivät voi tehdä muuta kuin palauttaa arvon laskentataulukon kaavalle tai toisessa VBA-makrossa tai -funktiossa käytettyyn lausekkeeseen. Mukautetut funktiot eivät esimerkiksi voi muuttaa ikkunoiden kokoa, muokata solussa olevaa kaavaa tai muuttaa solussa olevan tekstin fontti-, väri- tai muotoasetuksia. Jos sisällytät tämän tyyppisen ”toimintokoodin” funktiotoimintosarjaan, funktio palauttaa #ARVO!-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.

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 kommentin ansiosta helpompi muistaa, mitä teit aiemmin.

Esimerkki VBA-funktiosta kommenttien kanssa

Heittomerkki kertoo Excelille, että kaikki heittomerkin oikealla puolella samalla rivillä oleva teksti tulee ohittaa. Voit siten lisätä kommentteja riveille, joilla ei ole mitään muuta, tai VBA-koodia sisältävillä riveillä koodin oikealle puolelle. Voit esimerkiksi aloittaa pitkän koodilohkon kommentilla, jossa annetaan koodin yleiskuvaus, ja lisätä alemmille riveille koodin yksittäisiä lausekkeita koskevia kommentteja.

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 tätä tapaa johdonmukaisesti.

Jos haluat käyttää mukautettua funktiota, niin sen moduulin sisältävän työkirjan, jossa funktio luotiin, on oltava avoinna. Jos työkirja ei ole avoinna, näkyviin tulee #NIMI?-virhe, kun yrität käyttää funktiota. Jos viittaat funktioon toisessa työkirjassa, sinun tulee lisätä 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(), ei pelkästään =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:

Lisää funktio -valintaikkuna

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:

  1. Kun olet luonut tarvitsemasi funktiot, valitse Tiedosto > Tallenna nimellä.

    Napsauta Excel 2007issa Microsoft Office ‑painiketta ja valitse sitten Tallenna nimellä.

  2. Avaa Tallenna nimellä -valintaikkunassa avattava Tallennusmuoto -luettelo ja valitse Excel-apuohjelma. Tallenna työkirja tunnistettavalla nimellä, kuten OmatFunktiot, Apuohjelmat-kansioon. Tallenna nimellä -valintaikkuna ehdottaa tuota kansiota, joten sinun tarvitsee vain hyväksyä oletussijainti.

  3. Kun olet tallentanut työkirjan, valitse Tiedosto > Excelin asetukset.

    Napsauta Excel 2007issa Microsoft Office -painiketta ja valitse Excelin asetukset.

  4. Valitse Excelin asetukset -valintaikkunassa Apuohjelmat-luokka.

  5. Valitse avattavasta Hallinta-luettelosta Excel-apuohjelmat. Napsauta sitten Siirry-painiketta.

  6. Valitse Apuohjelmat-valintaikkunassa valintaruutu sen nimen vierestä, jolla tallensit työkirjasi, kuten alla.

    Apuohjelmat-valintaikkuna

  1. Kun olet luonut tarvitsemasi funktiot, valitse Tiedosto > Tallenna nimellä.

  2. Avaa Tallenna nimellä -valintaikkunassa avattava Tallennusmuoto -luettelo ja valitse Excel-apuohjelma. Tallenna työkirja tunnistettavalla nimellä, kuten OmatFunktiot.

  3. Kun olet tallentanut työkirjan, valitse Työkalut > Excel-apuohjelmat.

  4. Valitse Apuohjelmat-valintaikkunassa Selaa-painike, jolloin voit etsiä apuohjelman, valitse Avaa ja valitse sitten ruutu, joka on apuohjelman vieressä Käytettävissä olevat apuohjelmat -ruudussa.

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.

Nimetty moduuli vbe:ssä

Jos kaksoisnapsautat moduulia Project Explorerissa, Visual Basic Editor näyttää funktiosi koodin. 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.

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, saada tukea yhteisön vastauksista tai ehdottaa uutta ominaisuutta tai parannusta Excel User Voice -sivustolla.

Kehitä Office-taitojasi
Tutustu koulutusmateriaaliin
Saat uudet ominaisuudet ensimmäisten joukossa
Liity Office Insider -käyttäjiin

Oliko näistä tiedoista hyötyä?

Kiitos palautteesta!

Kiitos palautteestasi! Näyttää siltä, että Office-tukiedustajamme avusta voi olla sinulle hyötyä.

×