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.

Etsitkö tietoja siitä, miten voit luoda JavaScript-mukautetun funktion, jota voit käyttää Excel for Windowsissa, Excel for Macissa tai Excel Online ? Lisä tietoja on artikkelissa Excelin mukautettujen funktioiden yleiskatsaus.

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: Jos haluat tehdä koodista helppolukuisemman, voit sisentää viivoja sarkaimella . Sisennys on vain eduksi, ja se on valinnainen, koska koodi toimii tai sitä ei ole. Kun kirjoitat sisennetyn viivan, Visual Basic-editori olettaa, että seuraava rivi on samalla tavalla sisennetty. Jos haluat siirtää yhden sarkain merkin ulos (eli vasemmalle), paina näppäin yhdistelmää 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.

Mukautetussa funktioissa käytettävissä olevien VBA-avain sanojen määrä on pienempi kuin makroissa käytettävissä oleva luku. Mukautetuilla funktioilla ei voi tehdä mitään muuta kuin palauttaa arvo laskenta taulukon kaavaan tai lausekkeessa, jota käytetään toisessa VBA-makrossa tai-funktiossa. Esimerkiksi mukautetut funktiot eivät voi muuttaa Windowsin kokoa, muokata kaavaa solussa tai muuttaa solun tekstin fonttia, väriä tai kuvion asetuksia. Jos sisällytät "Action"-koodin funktion toiminto sarjaan, 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.

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

Heitto merkki käskee Exceliä ohittamaan kaikki saman rivin oikealla puolella olevat kommentit, jotta voit luoda kommentteja joko riveittäin tai VBA-koodia sisältävien rivien oikeassa reunassa. Voit aloittaa suhteellisen pitkän koodi lohkon kommentista, joka selittää sen yleisen tarkoituksen, ja käyttää sitten tekstiin sitoutuvia kommentteja yksittäisten lauseiden asia kirjoihin.

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, funktion luoneen moduulin sisältävän työkirjan on oltava avattuna. Jos työkirja ei ole avoinna, saat #NAME? virhe, kun yrität käyttää funktiota. Jos viittaat funktioon toisessa työkirjassa, funktion nimen eteen on kirjoitettava sen työkirjan nimi, jossa funktio sijaitsee. Jos esimerkiksi luot. xlsb-nimisen funktion, jonka nimi on "henkilökohtainen", ja soitat kyseiseen funktioon jostakin muusta työkirjasta, sinun on kirjoitettava = Personal. xlsb! Discount ()eikä vain = Discount ().

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 on alun perin kirjoittanut Markus Dodge ja Craig Stinson osana kirjassaan Microsoft Office Excel 2007 Inside Out. Se on sittemmin päivitetty niin, että sitä voi käyttää myös uusissa Excel-versioissa.

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.

Huomautus:  Tämä sivu on käännetty automaation avulla, ja siinä saattaa olla kielioppivirheitä tai epätarkkuuksia. Tarkoitus on, että sisällöstä on sinulle hyötyä. Kerrotko meille, oliko tiedoista hyötyä? Tästä pääset artikkelin englanninkieliseen versioon.

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ä.

×