Funktiot

• Funktioiden lisääminen ohjatusti
• Funktioiden kirjoittaminen
• Sisäkkäiset funktiot
• Funktiot tyypeittäin
  • Information
  • Financial
  • Date & Time
  • Math & Trig
  • Statistical
  • Lookup and Reference
  • Logical
  • Database
  • Text

Taulukkolaskentaohjelmista löytyy valmiina useita hyödyllisiä funktioita, jotka soveltuvat myös vaativan käyttäjän tarpeisiin. Funktiot antavat taulukkolaskentaan valmiit työkalut, joiden avulla voidaan käsitellä helposti suurta numeerista aineistoa. Funktiot helpottavat myös tarpeellisia perusoperaatioita, kuten esimerkiksi summan laskemista.

Seuraavissa luvuissa perehdytään normaalikäyttäjän kannalta hyödyllisiin funktioihin, joten mukaan ei ole luonnollisestikaan otettu kaikkia taulukkolaskentaohjelmista löytyviä funktioita. Funktioita erilaisiin käyttötarkoituksiin voi etsiä lisäesimerkkejä taulukkolaskentaohjelman opasteesta. Seuraavissa luvuissa käsiteltävät funktiot löytyvät ainoastaan Microsoftin Excel-ohjelmasta, mutta muistakin taulukkolaskentaohjelmista löytyy varmasti vastaavat funktiot. Erityisesti funktioiden parametrisoinneissa on varmasti ohjelmakohtaisia eroja, vaikka funktiot löytyisivät samalla nimellä toisista taulukkolaskentaohjelmista.

Funktioiden lisääminen ohjatusti

Kuva 30. Funktioiden ohjattu lisääminen

Funktioiden kirjoittaminen käsin on usein tuskastuttavaa, koska kirjoitusvirheitä tulee todella helposti. Monimutkainen funktio saattaa olla toimimaton, jonkin pienen kirjoitusvirheen vuoksi. Tämän vuoksi taulukkolaskenta-ohjelmissa on ohjattu funktioiden lisäämistoiminto, joka opastaa funktion tekemisessä ja pienentää käsin kirjoittamisen osuutta, joten virheiden mahdollisuus pienenee huomattavasti. Funktion ohjattu lisäämiseen päästään valikko-komennolla Avautuvan ikkunan vasemmassa reunassa on taulukkolaskentaohjelmassa olevat funktioryhmät ja oikealla puolella on näkyvillä aktiivisen ryhmän funktiot. Ikkunan alaosassa näkyy lyhyt selitys funktion toiminnasta ja sen parametrisoinnista.

Esimerkkikuvassa on vaateostosten laskemista varten tehty taulukko. Taulukosta näkyvät selkeästi tuotteen nimi, yksikköhinta ja kappalemäärä ja näistä muodostuva kokonaishinta. Taulukkoon pitää kuitenkin vielä laskea Yhteensä summa, josta nähdään vaateostosten kokonaishinta. Summa on tarkoitus laskea soluun E7. Summan voi tietenkin laskea myös muodossa =E4+E5+E6, mutta tuotemäärän kasvaessa summan laskeminen ei ole enää mielekästä kyseisellä tavalla. Tämän vuoksi summan laskemiseen käytetään SUM-funktiota.

Kuva 31. SUM-funktion käyttäminen laskennassa

Otetaan SUM-funktio käyttöön funktioiden lisäämistoiminnon avulla. Aluksi valitaan aktiiviseksi se solu, johon ollaan lisäämässä funktiota. Solun valinnan jälkeen valitaan funktion lisäämistoiminto. Tarvittava funktio löytyy matemaattis-trigonometristen (engl. ) funktioiden ryhmästä, joka voidaan valita aktiiviseksi vasemman puoleisesta ikkunasta. Funktion nimi on (suom. ) ja funktio voidaan valita aktiiviseksi oikeanpuoleisesta ikkunasta funktioryhmän valinnan jälkeen. Kun funktion on valittu, voidaan funktion lisääminen hyväksyä OK-painikkeella.

Kuva 32. SUM-funktion määrittäminen

Hyväksymisen jälkeen näkyviin avautuu ikkuna, jolla voidaan määritellä tarkemmin funktion ominaisuuksia. Ikkunan avulla voidaan -funktion tapauksessa valita alue, jolta summa lasketaan. -kentän lopussa on painike, jota painamalla päästään valitsemaan laskentataulusta haluttu alue. Painiketta painettaessa ikkuna pienenee ja laskentataulukko tulee aktiiviseksi, joten päästään haluttu solualue aktiiviseksi. Alueen valinnan lopuksi painetaan näppäimistön -näppäintä, jolloin funktion lisäämisikkuna avautuu uudelleen näkyviin. Kun funktiolle on määritelty halutut ominaisuudet, hyväksytään funktio -painikkeella. Funktion hyväksymisen jälkeen funktio liitetään valittuun soluun ja funktion suorittaman laskutoimituksen arvo tulee näkyville soluun. Funktio koostuu funktion aloittavasta merkistä (), suomen- tai englanninkielisestä lyhenteestä eli funktion nimestä ja funktiolle välitettävistä parametreista. Esimerkiksi edellisen esimerkin summan laskeva funktio on seuraavaa muotoa: .

Funktioiden kirjoittaminen

Funktioita voidaan kirjoittaa myös käsin. Funktion ohjattu lisääminen on käyttökelpoinen väline funktioiden opettelussa, mutta jo lyhyen käytön jälkeen tuttuja funktioita haluaa kirjoittaa käsin. Funktioiden kirjoittaminen suoraan näppäimistöltä lisää työskentelyn nopeutta kohtuullisen paljon, varsinkin yksinkertaisten funktioiden kohdalla.

Funktioiden kirjoittaminen on hyvin pitkälle samanlaista kuin normaalien kaavojen kirjoittaminen. Funktioiden kirjoittaminen aloitetaan merkillä, josta ohjelma tunnistaa funktion tai kaavan alun. Microsoft Excelissä funktioiden kirjoittaminen aloitetaan, kuten kaavojenkin kirjoittaminen eli yhtä suuruus merkillä (=). Varsinainen funktio koostuu suomen- tai englanninkielisestä lyhenteestä eli funktion nimestä ja funktiolle välitettävistä parametreista. Funktion nimi kuvaa yleensä hyvin funktion toimintaa, mutta joidenkin funktioiden niminä käytetään hieman epäselvää kirjainlyhennettä. Kirjainlyhenne on kuitenkin pääteltävissä funktion toiminnasta. Funktion nimi täytyy kirjoittaa oikein ja funktiolle täytyy antaa täsmälleen oikea määrä oikeassa järjestyksessä olevia parametreja. Kirjoitusvirheet funktion nimessä tai parametrien väärä järjestys tekevät funktiosta toimimattoman. Jos on epävarma funktion toiminnasta, niin funktio on varmempi tehdä ohjatun lisäämisen avulla.

Funktioille joudutaan usein antamaan parametreja, joiden avulla määritellään funktion toimintaa tarkemmin. Parametrit annetaan välittömästi funktion nimen jälkeen sulkujen sisällä (()). Parametreina funktiolle voidaan esimerkiksi antaa solualue tai jokin arvo, joka määrittelee tarkemmin funktion suorittaman toimenpiteen. Eri parametrit erotetaan toisistaan yleensä puolipisteen (;) avulla, mutta käytettävä erotinmerkki määräytyy esimerkiksi Microsoft Excelissä Windowsin maa-asetusten mukaan. Toinen yleisesti käytetty erotinmerkki on kaksoispiste (:).

Funktion muoto on yleensä seuraava:

=FUNKTION_NIMI(PARAMETRI1; PARAMETRI2; ..; PARAMETRIN)

Seuraavassa on esimerkki IF (suom. JOS) -funktion käytöstä. IF-funktiolla on kolme parametria, jotka on erotettu toisistaan puolipisteen avulla.

=IF(C5>5 ; "Suurempi kuin 5" ; "Pienempi tai yhtäsuuri kuin 5")

Sisäkkäiset funktiot

Funktioita voidaan laittaa myös sisäkkäin, jolloin saadaan suoritettua monimutkaisia toimintoja. Funktioille annetaan lisätietoa parametrien avulla, joten parametrina voidaan antaa esimerkiksi solualue tai numeerisia arvoja. Funktiot yleensä palauttavat arvoja soluun, johon ne on sijoitettu, joten funktion palauttama arvo voi toimia myös toisen funktion parametrina. Sisäkkäiset funktiot rakennetaan siten, että toinen funktioista annetaan toiselle funktiolle parametrina.

Yksinkertainen esimerkki sisäkkäisten funktioiden käytöstä on alennuksen antaminen ostosten kokonaissumman kasvaessa tarpeeksi suureksi. Laskentataulukossa on solu, jossa testataan kokonaissumman suuruutta ja tehdään sen perusteella päätös alennuksesta. Tarvittavan funktion toiminta on sanallisessa muodossa seuraava:

• Jos summa on suurempi tai yhtä suuri kuin 500 mk, niin annetaan vaatteista 10%:n alennus.

• Jos taas vaatteiden hinta on pienempi kuin 500 mk, niin ostoksista ei anneta minkäänlaista alennusta. Tuotteiden hinnat lasketaan KokonaisHinta (E4:E6) nimisen nimetyn alueen summana.

Seuraavassa sanallinen muoto on muutettu kaavaksi, joka voidaan sijoittaa esimerkiksi soluun E7. IF-funktiolla voidaan testata kokonaishinnan suuruutta. Kokonaishinta lasketaan SUM-funktiolla IF-funktion sisällä.

=IF(SUM(KokonaisHinta)>=500 ; SUM(KokonaisHinta)*0,9 ; SUM(KokonaisHinta))

Aiemmin esiintyneen (luku Funktioiden lisääminen ohjatusti) esimerkin mukaan vaatteiden kokonaishinnaksi tuli SUM-funktiolla laskettaessa 661,22. Edellinen IF-funktio testaa itse asiassa SUM-funktion palauttaman luvun suuruutta eli onko 661,22 suurempi kuin 500. Tässä yhteydessä ehto toteutuisi, joten tuotteista lasketaan 10% alennus ja kokonaishinnaksi tulisi 5950,98.

Microsoft Excelissä sisäkkäisten funktioiden tekeminen onnistuu funktioiden lisäämistoiminnon avulla. Otetaan esimerkiksi edellisen esimerkin funktion lisääminen laskentataulukon soluun E7. Ohjatussa funktion lisäämisessä valitaan ensin ulompana oleva funktio eli esimerkin tapauksessa IF-funktio lisättäväksi. IF-funktion ehto-osaan eli ensimmäiseen osaan saadaan lisättyä SUM-funktio kaavariviltä löytyvästä alasvetovalikosta. Valikosta löytyy viimeisimmäksi käytetyt funktiot sekä mahdollisuus muiden funktioiden etsimiseen (engl. More Functions). Kun valikosta valitaan haluttu funktio, saadaan näkyville valitun funktion lisäämisikkuna. Kannattaa kuitenkin huomata, että sisempää funktiota (SUM) ei voida hyväksyä OK-painikkeella, koska painikkeen painaminen lopettaa koko ohjatun funktion lisäämistoiminnon. Jos halutaan palata ulompaan funktioon, niin se onnistuu aktivoimalla ulompi funktio (IF) suoraan kaavariviltä hiiren avulla. Kun koko funktio on saatu kuntoon, niin se voidaan hyväksyä OK-painikkeella.

Funktiot tyypeittäin

Seuraavissa luvuissa on lueteltu muutamia normaalikäyttäjälle hyödyllisiä funktioita funktiotyypeittäin. Funktiot on koottu funktioryhmiin ja ryhmät on järjestetty englanninkielisten nimien mukaisesti. Funktiot on vastaavasti järjestetty ryhmiin englanninkielisen nimen mukaan. Funktioryhmän funktioista ja niiden käyttötarkoituksesta löytyy lyhyt kuvaus jokaisen funktioryhmän yhteydestä. Seuraavissa luvuissa käsiteltävä funktiot ja funktioryhmät löytyvä Microsoft Excelistä, mutta suuri osa funktioista löytyy myös muista taulukkolaskentaohjelmista. Funktioiden parametrisoinneissa voi olla eroja, mutta erot selviävät viimekädessä ohjelman opasteiden. Tästä luvusta ei löydy kaikkia mahdollisia funktioita, vaan listauksesta löytyvät ainoastaan normaalikäyttäjälle hyödyllisimmät funktiot.

Funktioiden yhteydessä esitetään ensin funktion englanninkielinen nimi, josta käy ilmi myös funktion parametrisointi. Funktiosta kerrotaan myös suomenkielisessä ohjelmassa esiintyvä funktio suomenkielinen funktion nimi, mutta nimen yhteydessä ei enää mainita funktion parametrisointia. Funktion parametrisointi on täsmälleen sama suomen- ja englanninkielisillä funktioilla. Funktioiden yhteydessä annetaan myös lyhyt käyttöesimerkki funktion toiminnasta ja sen parametreista.

Information

Erikoisfunktiot (engl. Information) ovat funktioita, joita käytetään datan erilaisessa testaamisessa. Funktioista löytyy sekä numeerisen että merkkitiedon tunnistavia funktiota. Erikoisfunktioiden avulla voidaan myös testata erilaisia virhetilanteita ja suorittaa toimia niiden mukaan.

Isblank(Parametri)(suom. Ontyhjä)

Funktio testaa onko parametrina annettu solu tyhjä. Parametrina voidaan antaa soluviittaus, virhearvo, kaava tai nimetty solu. Funktio saa arvon TRUE (suom. TOSI), jos parametrina annettu arvo on tyhjä. Muussa tapauksessa funktio saa arvon FALSE (suom. EPäTOSI).

Seuraavassa esimerkissä C1-solun arvo on tyhjä.

=ISBLANK(C1) saa arvon TRUE.

Seuraavassa esimerkissä C1-solussa on välilyönti.

=ISBLANK(C1) saa arvon FALSE.

Iserror(Parametri) (suom. Onvirhe)

Funktio testaa onko parametrina annettu arvo jokin virhearvoista. Parametrina voidaan antaa soluviittaus, virhearvo, kaava tai nimetty solu. Funktio saa arvon TRUE (suom. TOSI), jos parametrina annettu arvo on jokin virhearvoista tapauksessa funktio saa arvon FALSE (suom. EPäTOSI).

Seuraavassa esimerkissä C2-solussa on virhearvo #N/A!.

=ISERROR(C2) saa arvon TRUE.

Seuraavassa esimerkissä C2-solu on tyhjä.

=ISERROR(C2) saa arvon FALSE.

Seuraavassa esimerkissä virhearvo annetaan suoraan parametrina.

=ISERROR(#NUM!) saa arvon TRUE.

Iserr(Parametri) (suom. Onvirh)

Funktio testaa onko parametrina annettu arvo jokin virhearvo, mutta ei ole kuitenkaan #N/A!. Parametrina voidaan antaa soluviittaus, virhearvo, kaava tai nimetty solu. Funktio saa arvon TRUE (suom. TOSI), jos parametrina viety arvo on jokin virhearvoista #VALUE!, #REF!, #DIV|0!, #NUM!, #NAME? tai #NULL!. Muussa tapauksessa funktio saa arvon FALSE (suom. EPäTOSI).

Seuraavassa esimerkissä C2-solussa on virhearvo #N/A!.

=ISERROR(C2) saa arvon FALSE.

Seuraavassa esimerkissä C2-solus on tyhjä

=ISERROR(C2) saa arvon FALSE.

Seuraavassa esimerkissä C2-solussa on virhearvo #VALUE!.

=ISERROR(C2) saa arvon TRUE.

Islogical(Parametri) (suom. Ontotuus)

Funktio testaa onko parametrina annettu arvo totuusarvo eli TRUE (suom. TOSI) tai FALSE (suom. EPäTOSI). Parametrina voidaan antaa soluviittaus, virhearvo, kaava tai nimetty solu. Funktio saa arvon TRUE (suom. TOSI), jos parametrina annettu arvo on totuusarvo eli TRUE (suom. TOSI) tai FALSE (suom. EPäTOSI). Muussa tapauksessa funktio saa arvon FALSE (suom. EPäTOSI).

Seuraavassa esimerkissä parametrina annetaan funktiolle totuusarvo.

=ISLOGICAL(TRUE) saa arvon TRUE.

Seuraavassa esimerkissä C1-solussa on totuusarvo TRUE.

=ISLOGICAL(C1) saa arvon TRUE.

Isna(Parametri) (suom. Onpuuttuu)

Funktio testaa onko parametrina annettu arvo virhearvo #NA! (suom. #PUUTTUU!). Parametrina voidaan antaa soluviittaus, virhearvo, kaava tai nimetty solu. Funktio saa arvon TRUE (suom. TOSI), jos parametrin annettu arvo on virhearvo #NA! (suom. #PUUTTUU!). Muussa tapauksessa funktio saa arvon FALSE (suom. EPäTOSI).

Seuraavassa esimerkissä C5-solussa on virhearvo #N/A!.

=ISNA(C5) saa arvon TRUE.

Seuraavassa esimerkissä annetaan funktiolle parametrina virhearvo #N/A!.

=ISNA(#N/A) saa arvon TRUE.

Isnontext(Parametri) (suom. Onei_teksti)

Funktio testaa onko parametrina annettu arvo tekstiä. Funktiolle voidaan antaa parametrina soluviittaus, virhearvo, kaava tai nimetty solu. Funktio saa arvon TRUE (suom. TOSI), jos arvo EI ole tekstiä. Tyhjä solu ei sisällä tekstiä, mutta välilyönti lasketaan tekstiksi. Muussa tapauksessa funktio saa arvon FALSE (suom. EPäTOSI).

Seuraavassa esimerkissä C1-solussa on numeroarvo kaksi (2).

=ISNONTEXT(C1) saa arvon TRUE.

Seuraavassa esimerkissä C1-solussa on lukee sana kukka.

=ISNONTEXT(C1) saa arvon FALSE.

Seuraavassa esimerkissä C1-solussa on numeerisen arvon palauttava kaava (=A1+B1).

=ISNONTEXT(C1) saa arvon TRUE.

Istext(Parametri) (suom. Onteksti)

Funktio testaa onko parametrina annettu arvo tekstiä. Funktiolle voidaan antaa parametrina soluviittaus, virhearvo, kaava tai nimetty solu. Funktio saa arvon TRUE (suom. TOSI), jos arvo on tekstiä. Tyhjä solu ei sisällä tekstiä, mutta välilyönti lasketaan tekstiksi. Muussa tapauksessa funktio saa arvon FALSE (suom. EPäTOSI).

Seuraavassa esimerkissä C1-solussa on numeroarvo neljätoista (14).

=ISTEXT(C1) saa arvon FALSE.

Seuraavassa esimerkissä C1-solussa on lukee sana kirjasto.

=ISTEXT(C1) saa arvon TRUE.

Seuraavassa esimerkissä C1-solussa on numeerisen arvon palauttava funktio (=SUM(A1:A5)).

=ISTEXT(C1) saa arvon FALSE.

Isnumber(Parametri) (suom. Onluku)

Funktio testaa onko parametrina annettu arvo numeerinen eli jokin luku. Esimerkiksi päivämäärä käsitellään numeerisena lukuna. Funktiolle voidaan antaa parametrina soluviittaus, virhearvo, kaava tai nimetty solu. Funktio saa arvon TRUE (suom. TOSI), jos parametrina annettu arvo on numeerinen. Muussa tapauksessa funktio saa arvon FALSE (suom. EPäTOSI).

Seuraavassa esimerkissä C1-solussa on numeroarvo neljätoista (14).

=ISNUMBER(C1) saa arvon TRUE.

Seuraavassa esimerkissä C1-solussa on lukee sana kirjasto.

=ISNUMBER(C1) saa arvon FALSE.

Seuraavassa esimerkissä C1-solussa on numeerisen arvon palauttava funktio (=SUM(A1:A5)).

=ISNUMBER(C1) saa arvon TRUE.

Isref(Arvo) (suom. Onviitt)

Funktio testaa onko parametrina annettu arvo soluviittaus. Funktiolle voidaan antaa parametrina soluviittaus, virhearvo, kaava tai nimetty solu. Funktio saa arvon TRUE (suom. TOSI), jos parametrina annettu arvo on soluviittaus. Muussa tapauksessa funktio saa arvon FALSE (suom. EPäTOSI).

Seuraavassa esimerkissä funktion palauttama arvo ei riipu D3-solun sisällöstä.

=ISREF(D3) saa arvon TRUE.

Seuraavassa esimerkissä ISNUMBER-funktio palauttaa arvonaan totuusarvon eikä soluviittausta.

=ISREF(ISNUMBER(D3)) saa arvon FALSE.

NA() (suom. Puuttuu)

Funktio saa arvokseen virhearvon #NA! (suom. #PUUTTUU!). Funktiolle ei anneta ollenkaan parametreja.

Type(Arvo) (suom. Tyyppi)

Funktio testaa parametrina annetun arvon tyypin. Funktiolle voidaan antaa parametrina mikä tahansa arvo. Funktio saa seuraavat arvot parametrina annetun arvon perusteella:

Seuraavassa esimerkissä C1-solun arvo on tyhjä.

=TYPE(C1) saa arvon 1.

Seuraavassa esimerkissä C1-solussa on tekstiä.

=TYPE(C1) saa arvon 2.

Seuraavassa esimerkissä C1-solussa on numeerisen arvon palauttava SUM-funktio.

=TYPE(C1) saa arvon 1.

Seuraavassa esimerkissä C1-solussa on totuusarvon palauttava ISTEXT-funktio.

=TYPE(C1) saa arvon 4.

Financial

Rahoitusfunktioita (engl. Financial) käytetään erilaisten lainojen ja muiden rahoituserien kustannusten seuraamiseen. Rahoitusfunktiot ovat todella tehokas keino korkojen ja lainan erien laskentaan!

FV(korko; kaudet_yht; erä; nykyarvo; laji)(suom. Tuleva.Arvo)

Funktiolla lasketaan sijoituksen tai lainan tuleva arvo. Funktiolle annetaan parametreina koko laina- tai sijoitusajan korkoprosentti (korko), vakiomaksuerien kokonaiskestoaika (kaudet_yht), jokaisella kaudella maksettava vakioerä (erä) sekä mahdollisesti sijoituksen tai lainan nykyinen arvo (nykyarvo) ja lainan erääntymisajankohta (laji). Laji on nolla (0), jos laina erääntyy maksukauden lopussa. Jos laina erääntyy maksukauden alussa, niin laji on yksi (1). Funktio saa lainan tulevan kokonaisarvon, joka perustuu vakiomaksueriin ja vakiokorkoon.

Seuraavassa esimerkissä laina tai sijoitus maksetaan 1000 mk:n erissä, joita on 10 kappaletta. Koko lainan tai sijoituksen ajalle lasketaan 10%:n korkoa. Laina erääntyy maksukauden lopussa.

=FV(10%;10;-1000) saa arvon 15 937,42 mk

Seuraavassa esimerkissä laina tai sijoitus maksetaan 1000 mk:n erissä, joita on yhteensä 120 kappaletta (kuukausittain 10 vuoden ajan). Koko lainan tai sijoituksen maksuajalle lasketaan 10% vuosittainen korko. Laina erääntyy maksukauden lopussa.

=FV(10%|12;10*12;-1000) saa arvon 204 844,98 mk

IPMT(korko;kausi; kaudet_yht; nykyarvo: ta)(suom. IPMT)

Funktiolla voidaan laskea lainasta tai sijoituksesta kokonaisuudessaan kertyvä korko. Funktiolle annetaan parametreina koko laina- tai sijoitusajan korkoprosentti (korko), vakiomaksuerä, jolta korko halutaan selvittää (kausi), vakiomaksuerien kokonaiskestoaika (kaudet_yht), sijoituksen tai lainan nykyinen arvo (nykyarvo) ja lainan tai sijoituksen tulevaisuus arvo (ta). Funktio saa arvokseen lainankorosta kertyvän arvon, joka perustuu vakiomaksueriin ja vakiokorkoon.

Esimerkissä 100 000 mk:n laina tai sijoitus maksetaan 12 vakiosuuruisessa erässä. Esimerkissä ollaan kiinnostuneita ensimmäisenä vakiomaksueränä kertyneestä lainan korosta. Lainan korko on 10%. Tuleva-arvo on oletuksena 0 mk.

=IPMT(10%;1;12;-100000) saa arvon 10000mk.

Esimerkissä 100 000 mk:n laina tai sijoitus maksetaan 12 vakiosuuruisessa erässä. Esimerkissä ollaan kiinnostuneita viimeisenä vakiomaksueränä kertyneestä lainan korosta. Lainan korko on 10%. Tuleva arvo on oletuksena 0 mk.

=IPMT(10%;12;12;-100000) saa arvon 1 334,21 mk.

NPER(korko; erä; nykyarvo; ta; laji)(suom. Njakso)

Funktiolla lasketaan lainan maksukausien lukumäärä. Funktiolle annetaan parametrina koron suuruus prosentteina (korko), vakiosuuruisen maksuerän suuruus (erä), joka sisältää pääoman ja korko-osuuden, lainan tämän hetkisen arvon (nykyarvon), lainan tulevaisuudessa oleva arvo (ta), joka halutaan saavuttaa ja lainan erääntymisajankohta (laji) Laji on nolla (0), jos laina erääntyy maksukauden lopussa. Jos laina erääntyy maksukauden alussa, niin laji on yksi (1). Funktio saa arvokseen tasasuuruisten maksuerien lukumäärän, joka määräytyy vakio maksuerän ja lainan koron perusteella.

Esimerkissä on otettu 100000 mk laina maksetaan tasasuuruisissa 16274 mk suuruisissa erissä. Lainan tuleva-arvo on nolla ja erä maksetaan maksukauden lopussa.

=NPER(10%;-16274;100000) saa arvon 10,00055434

PMT(korko; kaudet_yht; nykyarvo; ta; laji)(suom. Maksu)

Funktiolla lasketaan tasaeräisen lainan maksuerän suuruus. Funktiolle annetaan parametreinä lainan koron suuruus prosentteina (korko), maksukausien lukumäärä yhteensä (kaudet_yht), lainan nykyinen arvo (nykyarvo) ja lainan erääntymisajankohta (laji). Laji on nolla (0), jos laina erääntyy maksukauden lopussa. Jos laina erääntyy maksukauden alussa, niin laji on yksi (1). Funktio saa arvokseen tasaeräisen maksuerän suuruuden vakiokorolla annetulla ajanjaksolla.

Seuraavassa esimerkissä lasketaan vuosittaisen maksuerän suuruus 10000 mk:n lainasta 10% korolla kymmenen vuoden aikana.

=PMT(10%;10;-10000) saa arvon 1 627,45 mk

Seuraavassa esimerkissä lasketaan kuukausittaisen maksuerän suuruus 10000 mk:n lainasta 10% vuosittaisella korolla ja kymmenen vuoden aikana.

=PMT(10%|12;10*12;-10000) saa arvon 132,15 mk

Seuraavassa on esimerkki erilaisten rahoitusfunktioiden käyttämisestä. Esimerkissä korkoprosentti , maksuerien määrä ja lainan suuruus ovat vakioita. Esimerkistä on nähtävillä miten samasta lainasta voidaan laskea erilaisia tarvittavia arvoja.

Vuosittaisen koron laskemiseen on B5 solussa käytetty funktiota:

=IPMT($A$2;A5;10;-$B$2)

Vakiosuuruinen maksuerä on laskettu C5 soluun funktiolla:

=PMT($A$2;10;-$B$2)

Maksuerien lukumäärän laskemiseen on käytetty C2 solussa funktiota:

=NPER($A$2;-$C$5;$B$2)

Date & Time

Kalenterifunktioiden (engl. Date & Time) saamat arvot riippuvat järjestelmän asetuksista. Järjestelmän asetuksissa määritellään maa-asetukset (engl. Regional settings), joiden perusteella määritellään esimerkiksi päivämääräerotin ja aikaerotin. Samojen asetusten perusteella määritellään myös muidenkin erotinmerkkien käyttöä. Esimerkiksi desimaalierotin on Suomessa pilkku, kun taas USA:ssa käytetään pistettä. Asetuksia pääsee muokkaamaan Windowsissa valikkokomennolla Start | Settings | Control Panel | Regional Settings (suom. Käynnistä | Asetukset | Ohjauspaneeli | Maa-asetukset). Kalenterifunktioita joutuu usein käyttämään erilaisten päiväysten automatisoinnissa dokumenteissa tai päivämäärällä tapahtuvan laskennan apuna.

Date(vuosi; kuukausi; päivä)(suom. Päiväys)

Funktiolla saadaan muodostettua päivämäärä parametrina annetuista tiedoista. Funktiolle viedään parametrina vuosiluku, kuukauden järjestysnumero ja päivämäärä. Parametreja ei rajoita normaalit päivämäärien kuukausirajat! Funktio saa arvokseen päivämäärän, joka muodostuu parametrien perusteella. Funktiolla voidaan esimerkiksi kerätä tietoja soluista, ja muodostaa näiden perusteella päivämäärä.

Seuraavassa esimerkissä funktiolle annetaan arvot suoraan parametrina. Kuukausi parametri ylittää kuukausien lukumäärän, joten siirrytään seuraavalle vuodelle ja päivä parametri ylittää päivien määrän kuukaudessa, joten siirrytään seuraaviin kuukausiin.

=DATE(2000;13;45) saa arvon 14.2.2001.

Seuraavassa esimerkissä jokaisessa solussa B3, C3 ja D3 on numeroarvo yksi (1).

=DATE(B3;C3;D3)saa arvon 1.1.1901.

Seuraavassa esimerkissä solussa B3 on arvo 1999 ja solussa C3 on arvo 12 sekä solussa D3 on arvo 15.

=DATE(B3;C3;D3) saa arvon 15.12.1999.

Datevalue(päivämäärä_teksti)(suom. Päiväysarvo)

Funktiolla saadaan merkkijonona annetusta päivämäärästä muodostettua kokonaisluku. Kokonaisluku lasketaan päivämäärästä 1.1.1900 lähtien, joka vastaa lukua yksi (1). Funktiolle viedään parametrina merkkijono, joka on jossakin päivämäärä formaatissa. Funktio saa arvokseen erilaisissa päivämäärämuodoissa annetusta merkkijonosta muodostetun kokonaisluvun. Seuraavassa muutamia esimerkkejä funktion käytöstä.

Seuraavassa esimerkissä päivämäärän erottimena käytetään pistettä (.).

=DATEVALUE("1.1.2000") saa arvon 36526.

Seuraavassa esimerkissä päivämäärän erottimena käytetään väliviivaa (-).

=DATEVALUE("1-1-1990") saa arvon 32874.

Seuraavassa esimerkissä päivämäärän erottimena käytetään kauttaviivaa (/).

=DATEVALUE("1/1/1900") saa arvon 1.

Now()(suom. Nyt)

Funktiolla saadaan tietokoneen järjestelmän päivämäärä ja kellonaika. Saatua päivämäärää ja kellonaikaa päivitetään ainoastaan dokumentin avaamisen yhteydessä. Aikaa voidaan myös päivittää painamalla näppäintä F9. Seuraavassa esimerkki funktion käytöstä.

=NOW() voi saada esimerkiksi arvon 8.1.2002 16:12.

Today() (suom. Tämä.Päivä)

Funktiolla saadaan tietokoneen järjestelmän päivämäärä. Seuraavassa esimerkki funktion käytöstä.

=TODAY() voi esimerkiksi saada arvon 1.2.2000

Math & Trig

Matemaattiset ja trigonometriset funktiot (engl. Math & Trig) sisältävät erilaisiin matemaattisiin toimenpiteisiin käytettäviä funktioita. Funktiot ovat todella tehokkaita solujen arvoilla tapahtuvassa laskennassa. Seuraavassa on esitelty ainoastaan tavallisimpien matemaattisten laskutoimitusten tekemiseen tarvittavia funktioita.

Sum(Parametrit)(suom. Summa)

Funktiolla lasketaan parametrina annettujen lukujen summa. Parametrina voidaan antaa numeerinen arvo, kaava, solualue tai nimetty solualue.

Seuraavassa esimerkissä lasketaan yksinkertainen parametrina annettujen lukujen summa.

=SUM(1;2;3;4;5) saa arvon 15.

Seuraavassa esimerkissä lasketaan solualueen B9:F9 summa. Alueella on luvut 2,3,4,5 ja 6.

=SUM(B9:F9) saa arvon 20.

Seuraavassa esimerkissä lasketaan summa nimetyltä solualueelta Kokonainen. Solualue muodostuu soluista B9:F9ja soluissa on luvut 2,3,4,5 ja 6.

=SUM(Kokonainen) saa arvon 20.

SumIf(Alue, Ehdot, Summa_Alue)(suom. Summa.Jos)

Funktiolla lasketaan kolmantena parametrina annettavasta summa_alueesta summa niiltä riveiltä, joissa toisena parametrina annettavat ehdot toteutuvat ensimmäisenä parametrina annettavalla alueella. Funktiolle siis annetaan ensimmäisenä parametrina alue, jolla tarkastellaan toisen parametrin ehtojen toteutumista. Jos ehdot toteutuvat joillakin alueen riveillä, niin funktio laskee summa_alueella olevien vastaavien rivien lukujen summan. Funktiolla voidaan laskea ehdollistettuja summia suurestakin datamäärästä. Parametrina voidaan antaa numeerinen arvo, kaava, solualue tai nimetty solualue.

Esimerkissä on oheisen kuvan mukainen ostosten kirjanpitotaulukko. A-sarakkeessa on ostosten päivämäärä ja B-sarakkeessa on ostosten suuruus. SUMIF-funktiolla saadaan laskettua yhtenä päivänä tehtyjen ostosten summa seuraavalla kaavalla, joka on sijoitettu soluun B9.

Kuva 33. SUMIF-funktioesimerkki

=SUMIF(A2:A6;A9;B2:B6)

Vaihtamalla solussa A9 olevaa päivämäärää saadaan laskettua summat eri päiviltä.

Abs(Luku) (suom. Itseisarvo)

Funktiolla lasketaan parametrina annetun luvun itseisarvo. Parametrina voidaan antaa numeerinen arvo, kaava, solualue tai nimetty solualue.

Seuraavassa esimerkissä solussa B10 voi olla 12 tai luku 12.

=ABS(B10) saa arvon 12.

Seuraavassa esimerkissä otetaan itseisarvo suoraan parametrina annettavasta luvusta.

=ABS(3,14) saa arvon 3,14.

Exp(Luku)(suom. Eksponentti)

Funktiolla lasketaan luonnollisen logaritmin kantaluvun (2,71828182845905) potenssiin korotus parametrina annetulla reaaliluvulla. Parametrina voidaan antaa numeerinen arvo, kaava, solualue tai nimetty solualue.

Seuraavassa esimerkissä funktiolle annetaan parametrina luku yksi (1).

=EXP(1) saa arvon 2,718281828 yhdeksän desimaalin
tarkkuudella.

Seuraavassa esimerkissä solussa B12 on luku kolme (3).

=EXP(B12) saa arvon 20,08553692 yhdeksän desimaalin
tarkkuudella.

Fact(Luku) (suom. Kertoma)

Funktiolla lasketaan parametrina annettavan luvun kertoma tai nolla. Parametrina voidaan antaa reaaliluku tai soluviittaus soluun, jossa on reaaliluku.

Seuraavassa esimerkissä otetaan luvun 5 kertoma.

=FACT(5) saa arvon 120.

Seuraavassa esimerkissä otetaan solussa B12 sijaitsevan luvun kertoma. Solussa B12 on luku 3.

=FACT(B12) saa arvon 6.

Int(Luku) (suom. Kokonaisluku)

Funktiolla saadaan kokonaisluku, joka on pyöristetty alaspäin parametrina annetusta reaaliluvusta. Parametrina voidaan antaa numeerinen arvo, kaava, solualue tai nimetty solualue.

Seuraavassa esimerkissä funktiolle annetaan parametrinä luku 12,6.

=INT(12,6) saa arvon 12.

Seuraavassa esimerkissä solussa on funktio =EXP(3), joka palauttaa lukuarvon 20,08553692.

=INT(A2) saa arvon 20.

Mod(Luku; Jakaja)(suom. Jakoj)

Funktiolla lasketaan jakojäännös, joka muodostuu jaettaessa luku jakajalla. Ensimmäisenä parametrina viedään reaaliluku luku, joka jaetaan toisena parametrina vietävällä jakajalla. Parametrina voidaan antaa numeerinen arvo, kaava, solualue tai nimetty solualue.

Seuraavassa esimerkissä otetaan kahden kokonaisluvun jakojäännös.

=MOD(13;6) saa arvoksi luvun 1.

Seuraavassa esimerkissä saadaan jakojäännös, joka muodostuu kun kokonaisluku jaetaan desimaaliluvulla.

=MOD(20;3,7) saa arvoksi luvun 1,5.

Seuraavassa esimerkissä solussa B2 on luku 12 ja solussa C2 on luku 5.

=MOD(B2;C2) saa arvoksi luvun 2.

Pi( ) (suom. Pii)

Funktiolla saadaan piin () neljätoista desimaalinen likiarvo 3,14159265358979.

Product(Luku1; Luku2; ...)(suom. Tulo)

Funktiolla voidaan laskea parametrina annettujen reaalilukujen tulo. Parametrina voidaan antaa numeerinen arvo, kaava, solualue tai nimetty solualue.

Seuraavassa esimerkissä lasketaan parametrina annetun solualueen lukujen tulo.

=PRODUCT(B1:B4) saa arvon 250.

Seuraavassa esimerkissä muodostetaan parametrina annettujen lukujen tulo.

=PRODUCT(2;3;4;5;6) saa arvon 720.

Rand( ) (suom. Satunnaisluku)

Funktiolla saadaan satunnaisluku väliltä [0,1).

Seuraavassa esimerkissä otetaan satunnainen kokonaislukuarvon väliltä [0, 99]. INT-funktiolla hoidetaan satunnaisluvun pyöristäminen ylöspäin.

=INT(RAND()*100).

Seuraavassa esimerkissä otetaan satunnainen lottonumero väliltä [1,39]. INT-funktiolla hoidetaan satunnaisluvun pyöristäminen ylöspäin.

=INT(RAND()*39+1)

Round(Luku; Desimaalit)(suom. Pyöristä)

Funktiolla voidaan pyöristää ensimmäisenä parametrina annettu luku toisena parametrina annettavien desimaalien lukumäärään. Parametrina voidaan antaa numeerinen arvo, kaava, solualue tai nimetty solualue.

Seuraavassa esimerkissä luku pyöristetään kahden desimaalin tarkkuuteen.

=ROUND(7,555; 2) saa arvon 7,56.

Seuraavassa esimerkissä piin arvo pyöristetään kahden desimaalin tarkkuuteen. PI-funktio palauttaa neljätoistadesimaalisen piin likiarvon.

=ROUND(PI(); 2) saa arvon 3,14.

Seuraavan esimerkin funktio palauttaa satunnaislukuarvon välille [0, 99], joka on pyöristetty lähimpään kokonaislukuun.

=ROUND(RAND()*100;0)

Degrees(Radiaani)(suom. Asteet)

Funktio muuttaa sille parametrina annetun astekulman radiaaneiksi.

=RADIANS(90) saa arvon 1,570796327 (likimain PI()|2).

Radians(Asteet)(suom. Radiaanit)

Funktio muuttaa sille parametrina annetun radiaanikulman asteiksi.

=DEGREES(PI()|2) saa arvon 90.

Sin(Radiaani) (suom. Sin)

Funktiosta saadaan parametrina annetun radiaanikulman sini.

=SIN(RADIANS(90)) saa arvon 1.

Cos(Radiaani) (suom. Cos)

Funktiosta saadaan parametrina annetun radiaanikulman cosini.

=COS(RADIANS(180)) saa arvon 1.

Tan(Radiaani) (suom. Tan)

Funktiosta saadaan parametrina annetun radiaanikulman tangentti.

=TAN(RADIANS(45)) saa arvon 1.

Statistical

Tilastolliset (engl. Statistical) funktiot on tarkoitettu erilaisten tilastollisten tunnuslukujen laskentaan. Taulukkolaskentaohjelmissa on tilastollisiin tarpeisiin hyvin tehokkaita funktioita, joskin tilastollisen aineiston käsittelyyn käytetään yleensä erillisiä tilastollisia ohjelmia. Funktioiden avulla pystytään kuitenkin suorittamaan kohtuullisen vaativiakin tilastollisia laskutoimituksia. Tilastollisten funktioiden käyttö vaatii kuitenkin tilastotieteen käsitteiden hyvää tuntemusta, joten seuraavassa on esiteltynä ainoastaan muutama usein käytetty tilastollinen funktio. Muihin tilastollisiin funktioihin voi tutustua ohjelman opasteiden kautta.

Average(Luku1; luku2; ...)(suom. Keskiarvo)

Funktiolla lasketaan annettujen lukujen keskiarvo. Parametrina voidaan antaa numeerinen arvo, kaava, solualue tai nimetty solualue. Funktio saa arvokseen parametrina annettujen arvojen keskiarvon.

Seuraavassa esimerkissä lasketaan parametrina annettujen lukujen keskiarvo.

=AVERAGE(1;2;3;4;5) saa lukuarvon 3.

Seuraavassa esimerkissä lasketaan solualueella A1:A7 olevien lukujen keskiarvo.

=AVERAGE(A1:A7)

Seuraavassa esimerkissä solualueella A1:A7 ei ole yhtään lukua, vaan siellä on pelkästään tekstitietoa.

=AVERAGE(A1:A7) saa arvon #DIV/0!.

Count(Arvo1; Arvo2; ...) (suom. Laske)

Funktiolla lasketaan parametrina annettujen numeeristen arvojen lukumäärä. Parametrina annettavat arvot voivat olla minkä tyyppistä tietoa tahansa. Funktio saa arvokseen numeeristen parametrien lukumäärän tai parametrien osoittamalla solualueella olevien numeeristen solujen lukumäärän.

Seuraavassa esimerkissä lasketaan solualueella D1:D8 olevien solujen lukumäärän, joissa on luku.

=COUNT(D1:D8) saa arvon 3.

Seuraavassa esimerkissä viedään funktiolle parametrina kaksi kirjainta ja kolme lukua.

=COUNT(q;t;1;2;3) saa arvon.

CountA(Arvo1; Arvo2; ...)(suom. Laske.A)

Funktiolla lasketaan parametrina annettavien tyhjistä eroavien arvojen lukumäärän. Parametrina annettavat arvot voivat olla minkä tyyppistä tietoa tahansa. Funktio saa arvokseen sellaisen parametrien lukumäärän tai parametrien osoittaman solualueen solujen lukumäärän, jotka eivät ole tyhjiä.

Seuraavassa esimerkissä funktiolle viedään parametrina kolme kirjainta ja kolme lukua.

=COUNTA(q;t;e;1;2;3) saa arvokseen 6.

Seuraavassa esimerkissä lasketaan solualueen D1:D8 epätyhjät solut. Solualueella D1:D8 on yksi kokonaan tyhjä solu.

=COUNTA(D1:D8) saa arvokseen 7.

Max(Luku1; Luku2; ...) (suom. Maks)

Funktiolla voidaan tutkia annettujen arvojen maksimiarvoa. Parametrina voidaan antaa numeerinen arvo, kaava, solualue tai nimetty solualue. Funktio saa arvokseen parametrien tai parametrin viittaaman solualueen suurimman arvon.

Seuraavassa esimerkissä palautetaan parametrinä annetuista luvuista suurin.

=MAX(1;2;4;6;4) saa arvokseen 6.

Seuraavassa esimerkissä solualueen D1:D8 suurin luku on 34.

=MAX(D1:D8) saa arvokseen 34.

Min(Luku1; Luku2; ...) (suom. Min)

Funktiolla voidaan tutkia annettujen arvojen minimiarvoa. Parametrina voidaan antaa numeerinen arvo, kaava, solualue tai nimetty solualue. Funktio saa arvokseen parametrien tai parametrin viittaaman solualueen pienimmän arvon.

Seuraavassa esimerkissä palautetaan parametrina annetuista luvuista pienin.

=MIN(1;2;4;6;4) saa arvokseen 1.

Seuraavassa esimerkissä solualueen D1:D8 pienin luku on 5.

=MIN(D1:D8) saa arvokseen 5.

Lookup and Reference

Hakufunktiot (engl. Lookup and Reference) ovat hyvin tehokkaita erilaisissa taulukosta tehtävissä hauissa. Seuraavassa muutamia yleisimmin käytettyjä hakufunktioita. Hakufunktioita joudutaan usein käyttämään erilaisten ehtolausekkeiden tilalla, koska ne yksinkertaistavat suoritettavien ehtojen mukaan toimimista.

Indirect(VIITTAUSTEKSTI)(suom. Epäsuora)

Funktiolla saadaan parametrina välitetyn merkkijonon määrittelemä soluviittaus. Parametrina voidaan antaa soluviittaus, joka on esimerkiksi muodossa A1. Funktio saa arvokseen parametrina annetussa solussa olevan soluviittauksen päässä olevan arvon. Funktiota joudutaan esimerkiksi käyttämään silloin, kun soluviittaukset muodostetaan jollakin toisella funktiolla.

Seuraavassa esimerkissä funktio saa arvon solussa A1 olevan soluviittauksen mukaan. Jos solussa A1 lukee tekstinä B2, niin funktio saa sen arvon, joka sijaitsee solussa B2. Esimerkin tapauksessa solussa B2 on luku 5. Kyseessä on siis epäsuora viittaaminen soluun B2.

=INDIRECT(A1) saa arvon 5.

Columns(Taulukko) (suom. Sarakkeet)

Funktiolla saadaan parametrina välitetyn taulukon sarakkeiden lukumäärä. Parametrina voidaan antaa numeerinen arvo, kaava, solualue tai nimetty solualue. Funktio saa arvokseen parametrien tai parametrin viittaaman solualueen sarakkeiden lukumäärän.

Seuraavassa esimerkissä halutaan tietää solualueelle D1:F5 olevien sarakkeiden lukumäärä. Solualueella D1:F5 on D-, E- ja F-sarakkeet.

=COLUMNS(D1:F5) saa arvon 3.

Rows(TAULUKKO)(suom. Rivit)

Funktiolla saadaan parametrina välitetyn taulukon rivien lukumäärä. Parametrina voidaan antaa numeerinen arvo, kaava, solualue tai nimetty solualue. Funktio saa arvokseen parametrien tai parametrin viittaaman solualueen rivien lukumäärän.

Seuraavassa esimerkissä halutaan tietää solualueelle D1:F5 olevien rivien lukumäärä. Solualueella D1:F5 on rivit 1, 2, 3, 4 ja 5.

=ROWS(D1:F5) saa arvon 5.

Index(viittaus; rivinro; sarakenro)(suom. Indeksi)

Funktio saa arvokseen rivi- ja sarakeindeksien muodostamassa risteyksessä olevan arvon. Parametrina voidaan antaa numeerinen arvo, kaava, solualue tai nimetty solualue. Funktiolla saadaan parametrina annetusta solualueesta (viittaus) solu, joka on rivin rivinro ja sarakkeen sarakenro risteyksessä.

Seuraavassa esimerkissä haetaan solualueen A2:C8 toisella rivillä ja toisessa sarakkeessa eli solussa B3 oleva arvo. Solussa B3 on luku 6.

=INDEX(A2:C8;2;2) saa arvon 6.

Hlookup(hakuarvo; taulukko; rivi_indeksi_nro; alue_haku)(suom. Vhaku)

Funktiolla haetaan parametrina välitetystä taulukosta hakuarvoa vastaava arvo. Haettava arvo on samassa sarakkeessa kuin taulukon ylimmältä riviltä löytyvä hakuarvo, mutta se on rivi_indeksi_nro:n ilmoittamalla rivillä. Toisin sanoen taulukon ylimmmän rivin arvoja vertaillaan hakuarvoon.Jos rivin jostakin sarakkeesta löytyy yhtä suuri arvo kuin hakuarvo, niin funktio palauttaa arvon, joka löytyy taulukon samasta sarakkeesta rivi_indeksi_nro:n osoittamalta riviltä.

Alue_haku parametrillä voidaan määritellä täytyykö hakuarvon löytyä taulukosta. Jos alue_haku on FALSE (suom. EPäTOSI), niin hakuarvon täytyy löytyä taulukon ylimmältä riviltä. Muussa tapauksessa palautetaan arvo #N/A! (suom. #PUUTTUU!). Jos alue_haku on TRUE (suom. TOSI), niin hakuarvon ei tarvitse löytyä taulukosta. Tällöin funktio palauttaa arvonsa sarakkeesta, josta löytyy suurin hakuarvoa pienemmistä luvuista. Parametreinä voidaan antaa numeerinen arvo, kaava, solualue tai nimetty solualue.

Seuraavassa muutamia esimerkkejä HLOOKUP-funktion käytöstä. Kaikki esimerkin haut tehdään oheiden kuvan mukaiseen taulukkoon.

=HLOOKUP(2;B1:H2;2;TRUE) saa arvokseen Ti.=HLOOKUP(6;B1:H2;2;TRUE) saa arvokseen La.
=HLOOKUP(9;B1:H2;2;TRUE) saa arvokseen Su.

Kuva 34. HLOOKUP-funktion hakutaulu

=HLOOKUP(9;B1:H2;2;FALSE) saa arvokseen #N/A!.

Vlookup(hakuarvo; taulukko; sar_indeksi_nro; alue_haku)(suom. Phaku)

Funktiolla haetaan parametrina välitetystä taulukosta hakuarvoa vastaava arvo. Haettava arvo on samalla rivillä kuin taulukon vasemmanpuoleisesta sarakkeesta löytyvä hakuarvo, mutta se on sar_indeksi_nro:n ilmoittamassa sarakkeessa. Toisin sanoena taulukon vasemman puoleista saraketta vertaillaan hakuarvoon. Jos sarakkeesta löytyy yhtä suuri arvo kuin hakuarvo, niin funktio palauttaa arvon samalta riviltä sar_indeksi_nro:n osoittamasta sarakkeesta.

Alue_haku parametrillä voidaan määritellä pitääkö hakuarvon löytyä taulukosta. Jos alue_haku on FALSE (suom. EPäTOSI), niin hakuarvon on löydyttävä taulukon vasemmasta sarakkeesta. Muussa tapauksessa palautetaan arvo #N/A! (suom. #PUUTTUU!). Jos alue_haku on TRUE (suom. TOSI), niin hakuarvon ei tarvitse löytyä taulukosta. Tällöin funktio palauttaa arvon riviltä, josta löytyy suurin hakuarvoa pienemmistä luvuista. Parametreinä voidaan antaa numeerinen arvo, kaava, solualue tai nimetty solualue.

Kuva 35. VLOOKUP-funktion hakutaulu

Seuraavassa muutamia esimerkkejä -funktion käytöstä. Kaikki esimerkin haut tehdään oheisen kuvan mukaiseen taulukkoon.

=VLOOKUP(2;A2:B8;2;TRUE) saa arvokseen Ti.=VLOOKUP(4;A2:B8;2;TRUE) saa arvokseen To.
=VLOOKUP(10;A2:B8;2;TRUE) saa arvokseen Su.
=VLOOKUP(10;A2:B8;2;FALSE) saa arvokseen #N/A!.

Logical

Loogisten (engl. Logical) funktioiden avulla voidaan testata muun muassa erilaisten ehtojen täyttymistä. Seuraavassa esiteltynä muutamia hyödyllisiä loogisia funktioita.

And(totuus1; totuus2; ...)(suom. Ja)

Funktiolla voidaan testata parametrina vietävien lausekkeiden totuusarvoja. Jos kaikki parametrina vietävät lausekkeet ovat tosia eli niiden totuusarvo on TRUE (suom. TOSI), niin funktio saa arvon TRUE (suom. TOSI). Jos jokin parametrina vietävistä lausekkeista saa totuusarvon FALSE (suom. EPäTOSI), niin funktio saa arvon FALSE (suom. EPäTOSI). Parametrina voidaan välittää totuusarvoja ja aritmeettisia lausekkeita, joille voidaan antaa totuusarvo.

Seuraavassa esimerkissä solussa B17 oleva luku on 3 eli se on välillä [1,10].

=AND(B17>=1; B17<=10) saa arvon TRUE.

Seuraavassa esimerkissä soluissa B17 ja C17 on merkkijono OK.

=AND(B17="OK"; C17="OK") saa arvon TRUE.

Or(totuus1; totuus2; ...)(suom. Tai)

Funktiolla voidaan testata parametrina vietävien lausekkeiden totuusarvoja. Jos jonkin parametrina vietävän lausekkeen jokin arvo on tosi eli sen totuusarvo on TRUE (suom. TOSI), niin funktio saa arvon TRUE (suom. TOSI). Jos kaikki parametrina vietävät lausekkeet saavat totuusarvon FALSE (suom. EPäTOSI), niin funktio saa arvon FALSE (suom. EPäTOSI). Parametrina voidaan välittää totuusarvoja ja aritmeettisia lausekkeita, joille voidaan antaa totuusarvo.

Seuraavassa esimerkissä solussa B17 oleva luku on 13, joten se ei ole pienempi kuin 1, mutta se on suurempi kuin 10.

=OR(B17<1; B17>10) saa arvon TRUE.

Seuraavassa esimerkissä solussa C17 on merkkijono OK, mutta solussa B17 on merkkijono EI.

=OR(B17="OK"; C17="OK") saa arvon TRUE.

False( )(suom. Epätosi)

Funktio saa arvon FALSE (suom. EPäTOSI).

True( )(suom. Tosi)

Funktio saa arvon TRUE (suom. TOSI).

IF(lauseke; arvo_jos_TOSI; arvo_jos_EPäTOSI)(suom. Jos)

Funktiolla voidaan testata ensimmäisenä parametrina välitettävän lausekkeen totuusarvoa. Jos ensimmäisenä parametrina vietävä lauseke saa arvon TRUE (suom. TOSI), niin funktio saa toisen parametrin määräämän arvon arvo_jos_tosi. Jos taas ensimmäisenä parametrina vietävä lauseke saa arvon FALSE (suom. EPäTOSI), niin funktio saa kolmannen parametrin määräämän arvon arvo_jos_epätosi. Funktion palauttama arvo sijoitetaan siihen soluun, johon funktio on kirjoitettu. Funktion palauttamaa arvoa ei voida sijoittaa mihinkään muuhun soluun.

Seuraavassa esimerkissä testaan onko solussa A2 oleva luku on suurempi kuin 100. Solussa A2 on luku 205, joten se on suurempi kuin 100. Tällöin funktio palauttaa arvon yli sata. Jos solussa A2 oleva luku ei olisi ollut suurempi kuin 100 eli luku olisi pienempi tai yhtä suuri kuin 100, niin funktio palauttaisi arvon "pienempi tai yhtä suuri kuin sata".

=IF(A2>100;"yli sata";"pienempi tai yhtä suuri kuin
sata") saa arvon yli sata.

Database

Tietokantafunktioita (engl. Database) käytetään laskentataulukossa olevien tietojen analysointiin tiettyjen ehtojen avulla. Nimitys tietokantafunktio on hieman harhaanjohtava, koska funktioilla ei ole oikeasti mitään tekemistä oikeiden tietokantojen kanssa. Tietokantafunktiot on tarkoitettu luetteloissa olevien tietojen analysointiin. Tietokantafunktioiden avulla voidaan suorittaa erilaisia laskutoimituksia, kuten esimerkiksi keskiarvo tai summa, luetteloissa olevasta tiedosta useamman rajoittavan ehdon perusteella. Tietokantafunktioiden parametrisointi ja toiminta on samantapaista, joten seuraavassa käydään kootusti läpi tietokantafunktioiden ominaisuuksia. Ymmärtääkseen tietokantafunktioiden toiminnan kunnolla kannattaa myös katsoa DAVERAGE-funktion yhteydessä esitettävät esimerkit.

Tietokantafunktiolla on kolme parametria, joiden avulla voidaan muokata funktion toimintaa.

Seuraavassa tietokantafunktioiden yleinen syntaksi:

dfunktio(LUETTELO ; SARAKEOTSIKKO ; EHTOALUE)

• Ensimmäisenä parametrina määritellään tietokanta eli luettelo, jonka tietojen perusteella suoritetaan laskutoimituksia.

• Toisena parametrina annetaan sarakeotsikko, jonka määräämässä sarakkeessa laskutoimitus tehdään. Sarakeotsikko voidaan antaa tekstinä, sarakkeen järjestysnumerona (luettelon sisällä) tai soluviittauksena sarakeotsikkoon.

• Kolmantena parametrina annetaan ehtoalue, jonka ehtojen perusteella varsinainen laskenta suoritetaan. Ehtoalueelle asetetaan seuraavia rajoituksia:

• Ehtoalueen ensimmäisellä rivillä on oltava samat otsikot kuin luettelossa, josta tietoja haetaan.

• Kaikkia otsikoita ei tarvitse olla ehtoalueella, vaan ainoastaan niiden sarakkeiden otsikot, joiden mukaan laskentaa halutaan rajoittaa.

• Samalla ehtorivillä olevien ehtojen on oltava voimassa yhtäaikaa samalla luettelon rivillä eli ehto on JA-tyyppinen. Oheisessa kuvassa on seuraava rajoitus:

  

  Kuva 36. Samalla rivillä olevat ehdot

• Eri riveillä ja samaa saraketta rajoittavien ehtojen voimassa olo on TAI-tyyppinen. Oheisessa kuvassa on seuraava rajoitus: Haluttaessa rajoittaa lämpötilaa puolipilvisinä päivinä on lämpötilaehto lisättävä myös toiselle ehtoalueen riville (rivi 3).

  

  Kuva 37. Eri rivillä olevat ehdot

• Alueessa ei saa olla yhtään kokonaan tyhjää ehtoriviä.

Seuraavassa kuvassa on esimerkeissä käytettävä säätilatietoja sisältävä luettelo. Säätilatiedoista on tehty nimettyalue saatila, johon sisältyy koko säätilataulukko otsikoineen. Luettelon ensimmäisellä rivillä on luettelon otsikkotiedot.

Kuva 38. Esimerkeissä käytettävä säätilataulukko

DAVERAGE(LUETTELO ; SARAKEOTSIKKO ; EHTOALUE)

Funktiolla voidaan laskea yksittäisestä sarakkeesta ehtoalueen ehdot täyttävien rivien keskiarvo.Funktion ensimmäisessä parametrissa määritellään luettelo, jonka alueelta tietoja etsitään. Toisena parametrina määritellään sarakeotsikko, josta keskiarvo lasketaan. Kolmantena parametrina määritellään ehtoalue, jonka ehdot täyttäviltä riveiltä keskiarvo lasketaan.

Seuraavan esimerkin funktio palauttaa sijaintisoluunsa tuloksen -25,18. Tulos on saatu aikaan edellä esitellyn laskentataulukon (Kuva 38) tietojen perusteella ja ehtoalueena on käytetty seuraava kuvan (Kuva 39) mukaista ehtoaluetta. Esimerkissä lasketaan keskiarvo lasketaan sellaisilta riveiltä, joiden lämpötila on pienempi kuin -10 astetta.

=DAVERAGE(saatila;"Lämpötila";A1:D2)

Kuva 39. Lämpötila on pienempi kuin -10 astetta.

Seuraavan esimerkin funktio palauttaa sijaintisoluunsa tuloksen -20,62. Tulos on saatu aikaan edellä esitellyn laskentataulukon (Kuva 38)tietojen perusteella ja ehtoalueena on käytetty seuraava kuvan (Kuva 40) mukaista ehtoaluetta. Keskiarvo lasketaan sellaisilta riveiltä, joiden ( lämpotila on suurempi tai yhtä suuri kuin -23 astetta ja joissa pilvisyys on joko S ) tai sellaisilta riveiltä, joissa pilvisyys on K.

=DAVERAGE(saatila;"Lämpötila";A1:D3)

Kuva 40. (Lämpötila on suurempi tai yhtä suuri kuin -23 astetta ja pilvisyys on joko S) tai pilvisyys on K.

Seuraavan esimerkin funktio palauttaa sijaintisoluunsa tuloksen -17,36. Tulos on saatu aikaan edellä esitellyn laskentataulukon (Kuva 38) tietojen perusteella ja ehtoalueena on käytetty seuraava kuvan (Kuva 41) mukaista ehtoaluetta. Keskiarvo laskettavaksi sellaisilta riveiltä, joiden lämpötila on suurempi tai yhtä suuri kuin -23 astetta ja pienempi kuin 2 astetta.

=DAVERAGE(saatila;"Lämpötila";A1:D2)

Kuva 41. Lämpötila on suurempi tai yhtä suuri kuin -23 astetta ja pienempi kuin 2 astetta.

Seuraavan esimerkin funktio palauttaa sijaintisoluunsa tuloksen -22,70. Tulos on saatu aikaan edellä esitellyn laskentataulukon (Kuva 38) tietojen perusteella ja ehtoalueena on käytetty seuraava kuvan (Kuva 42) mukaista ehtoaluetta. Keskiarvo lasketaan sellaisilta riveiltä, joiden lämpötila on suurempi kuin 2 astetta tai pienempi tai yhtä suuri kuin -23 astetta.

=DAVERAGE(saatila;"Lämpötila";A1:D3)

Kuva 42. Lämpötila on suurempi kuin 2 astetta tai pienempi tai yhtä suuri kuin -23 astetta.

DCOUNT(LUETTELO ; SARAKEOTSIKKO ; EHTOALUE)

Funktiolla voidaan laskea yksittäisestä sarakkeesta ehtoalueen ehdot täyttävien solujen lukumäärä, joissa on numeerista tietoa. Funktion ensimmäisessä parametrissa määritellään luettelo, jonka alueelta tietoja etsitään. Toisena parametrina määritellään sarakeotsikko, josta lasketaan numeerista tietoja sisältävien rivien määrä. Kolmantena parametrina määritellään ehtoalue, jonka ehdot täyttävien rivien lukumäärä lasketaan.

Seuraavan esimerkin funktio palauttaa sijaintisoluunsa tuloksen 22. Tulos on saatu aikaan edellä esitellyn laskentataulukon (Kuva 38) tietojen perusteella ja ehtoalueena on käytetty seuraava kuvan (Kuva 43) mukaista ehtoaluetta. Lasketaan sellaisten numeerista tietoa sisältävien rivien lukumäärä, joilla lämpötila on pienempi kuin -10 astetta.

=DCOUNT(saatila;"Lämpötila";A1:D2)

Kuva 43. Lämpötila on pienempi kuin -10 astetta.

DCOUNTA(LUETTELO ; SARAKEOTSIKKO ; EHTOALUE)

Funktiolla voidaan laskea yksittäisestä sarakkeesta ehtoalueen ehdot täyttäviltä solujen lukumäärä, joissa on jotakin tietoa eli solut eivät ole tyhjiä. Funktion ensimmäisessä parametrissa määritellään luettelo, jonka alueelta tietoja etsitään. Toisena parametrina määritellään sarakeotsikko, josta lasketaan epätyhjien solujen määrä. Kolmantena parametrina määritellään ehtoalue , jonka ehdot täyttäviltä rivien lukumäärä lasketaan.

Seuraavan esimerkin funktio palauttaa sijaintisoluunsa tuloksen 7. Tulos on saatu aikaan edellä esitellyn laskentataulukon (Kuva 38) tietojen perusteella ja ehtoalueena on käytetty seuraava kuvan (Kuva 44) mukaista ehtoaluetta. Esimerkissä lasketaan sellaisten epätyhjien solujen lukumäärä, joilla pilvisyys on S ja sää on R.

=DCOUNTA(saatila;"Pilvisyys";A1:E2)

Kuva 44. Pilvisyys on S ja sää on R.

DMAX(LUETTELO ; SARAKEOTSIKKO ; EHTOALUE)

Funktiolla voidaan hakea yksittäisestä sarakkeesta ehtoalueen ehdot täyttävien solujen suurin arvo. Funktion ensimmäisessä parametrissa määritellään luettelo, jonka alueelta tietoja etsitään. Toisena parametrina määritellään sarakeotsikko, josta haetaan maksimiarvoa. Kolmantena parametrina määritellään ehtoalue, jonka ehdot täyttäviltä riveiltä maksimiarvo otetaan.

Seuraavan esimerkin funktio palauttaa sijaintisoluunsa tuloksen -2. Tulos on saatu aikaan edellä esitellyn laskentataulukon (Kuva 38) tietojen perusteella ja ehtoalueena on käytetty seuraava kuvan (Kuva 45) mukaista ehtoaluetta. Haetaan sellaisten rivien lämpötilan suurin arvo, joilla pilvisyys on S.

=DMAX(saatila;B5;A1:D2)

Kuva 45. Pilvisyys on S.

DMIN(LUETTELO ; SARAKEOTSIKKO ; EHTOALUE)

Funktiolla voidaan hakea yksittäisestä sarakkeesta ehtoalueen ehdot täyttäviltä solujen pienin arvo. Funktion ensimmäisessä parametrissa määritellään luettelo, jonka alueelta tietoja etsitään. Toisena parametrina määritellään sarakeotsikko, josta haetaan pienintä arvoa. Kolmantena parametrina määritellään ehtoalue, jonka ehdot täyttäviltä riveiltä miniarvo otetaan.

Seuraavan esimerkin funktio palauttaa sijaintisoluunsa tuloksen -33. Tulos on saatu aikaan edellä esitellyn laskentataulukon (Kuva 38) tietojen perusteella ja ehtoalueena on käytetty seuraava kuvan (Kuva 46) mukaista ehtoaluetta. Esimerkissä haetaan sellaisten rivien lämpötilan pienin arvo, joilla pilvisyys on S ja sää on R.

=DMIN(saatila;B5;A1:D2)

Kuva 46. Pilvisyys on S ja sää on R.

DSUM(LUETTELO ; SARAKEOTSIKKO ; EHTOALUE)

Funktiolla voidaan laskea yksittäisestä sarakkeesta ehtoalueen ehdot täyttävien solujen summa. Funktion ensimmäisessä parametrissa määritellään luettelo, jonka alueelta tietoja etsitään. Toisena parametrina määritellään sarakeotsikko, josta summa lasketaan. Kolmantena parametrina määritellään ehtoalue, jonka ehdot täyttäviltä riveiltä summa lasketaan.

Seuraavan esimerkin funktio palauttaa sijaintisoluunsa tuloksen -118. Tulos on saatu aikaan edellä esitellyn laskentataulukon (Kuva 38) tietojen perusteella ja ehtoalueena on käytetty seuraava kuvan (Kuva 47) mukaista ehtoaluetta. Esimerkissä lasketaan summa sellaisista soluista, joiden päivämäärä on pienempi tai yhtä suuri kuin 10 ja joiden sää on R.

=DSUM(saatila;"lämpötila";A1:D2)

Kuva 46. Päivämäärä on pienempi tai yhtä suuri kuin 10 ja sää on R.

DPRODUCT(LUETTELO ; SARAKEOTSIKKO ; EHTOALUE)

Funktiolla voidaan laskea yksittäisestä sarakkeesta ehtoalueen ehdot täyttävien solujen tulo. Funktion ensimmäisessä parametrissa määritellään luettelo, jonka alueelta tietoja etsitään. Toisena parametrina määritellään sarakeotsikko, josta summa lasketaan. Kolmantena parametrina määritellään ehtoalue, jonka ehdot täyttäviltä riveiltä tulo lasketaan.

DSTDEV(LUETTELO ; SARAKEOTSIKKO ; EHTOALUE)

Funktiolla voidaan laskea yksittäisestä sarakkeesta ehtoalueen ehdot täyttävien solujen keskihajonta otoksen perusteella. Funktion ensimmäisessä parametrissa määritellään luettelo, jonka alueelta tietoja etsitään. Toisena parametrina määritellään sarakeotsikko, josta summa lasketaan. Kolmantena parametrina määritellään ehtoalue, jonka ehdot täyttäviltä riveiltä keskihajonta lasketaan otoksen perusteella.

DSTDEVP(LUETTELO ; SARAKEOTSIKKO ; EHTOALUE)

Funktiolla voidaan laskea yksittäisestä sarakkeesta ehtoalueen ehdot täyttävien solujen keskihajonta kaikkien tietojen perusteella. Funktion ensimmäisessä parametrissa määritellään luettelo, jonka alueelta tietoja etsitään. Toisena parametrina määritellään sarakeotsikko, josta summa lasketaan. Kolmantena parametrina määritellään ehtoalue, jonka ehdot täyttäviltä riveiltä keskihajonta lasketaan kaikkien tietojen perusteella.

DVAR(luettelo ; sarakeotsikko ; ehtoalue

Funktiolla voidaan laskea yksittäisestä sarakkeesta ehtoalueen ehdot täyttävien solujen varianssi otoksen perusteella. Funktion ensimmäisessä parametrissa määritellään luettelo, jonka alueelta tietoja etsitään. Toisena parametrina määritellään sarakeotsikko, josta summa lasketaan. Kolmantena parametrina määritellään ehtoalue, jonka ehdot täyttäviltä riveiltä varianssi lasketaan otoksen perusteella.

DVARP(LUETTELO ; SARAKEOTSIKKO ; EHTOALUE)

Funktiolla voidaan laskea yksittäisestä sarakkeesta ehtoalueen ehdot täyttävien solujen varianssi kaikkien tietojen perusteella. Funktion ensimmäisessä parametrissa määritellään luettelo, jonka alueelta tietoja etsitään. Toisena parametrina määritellään sarakeotsikko, josta summa lasketaan. Kolmantena parametrina määritellään ehtoalue, jonka ehdot täyttäviltä riveiltä varianssi lasketaan kaikkien tietojen perusteella.

DGET(LUETTELO ; SARAKEOTSIKKO ; EHTOALUE)

Funktiolla voidaan poimia yksittäisestä sarakkeesta ehtoalueen ehdot täyttävien solujen arvoja. Funktion ensimmäisessä parametrissa määritellään luettelo, jonka alueelta tietoja etsitään. Toisena parametrina määritellään sarakeotsikko, josta summa lasketaan. Kolmantena parametrina määritellään ehtoalue, jonka ehdot täyttäviltä riveiltä solujen arvot poimitaan.

Text

Tekstifunktioiden (engl. Text) avulla voidaan käsitellä ja tutkia erilaisia tekstijonoja. Funktioiden avulla voidaan määrittää merkkijonojen pituuksia, lisätä tai poistaa merkkijonosta merkkejä ja vaihtaa merkkijonon kirjainten kokoa. Funktiot ovat hyödyllisiä ja käyttökelpoisia, mutta eivät välttämättömiä tavallisessa taulukkolaskennassa, joten niitä ei käsitellä tämän enempää tässä yhteydessä.