19  Funktiot

Lähde

Olen hakenut funktioiden määritelmät ja käyttökuvaukset Excelin dokumentaatiosta. Dokumentaatiosta löytyy enemmän funktioita; kuvailen tässä ne, jotka voivat olla eniten hyödyllisiä työssäsi. Löydät kaikki Excelin funktiot luokan mukaan täältä: Excelin funktiot (luokittain)

19.1 Sivun käyttöopas

Oikealla laidalla on sivun kaikkien otsikoiden listaus, josta voit etsiä sinua kiinnostavan funktion. Voit myös käyttää verkkokirjan hakutoimintoa, joka löytyy sivun yläoikeasta laidasta.

Yleistä tietoa funktioiden käytöstä Excelissä löytyy alaluvusta 14.8.

HUOM: Jos luet mobiililaitteella, et luultavasti näe otsikkolistausta oikealla liian pienen näytön takia.

19.2 Sanastoa

Funktio: Toiminto, joka ottaa jonkun syötetyn tiedon (input), tekee sille jotain (esim. laskee keskiarvon), ja sylkäisee ulos tuloksen (output). Funktio voi ottaa avainsanoja.

Funktiokutsu: Kun käytät funktiota, esim. solussa.

Palautus: Kun funktio kutsutaan, se tekee laskutoimintonsa ja palauttaa jonkun arvon (output).

Parametri: Ennalta määritelty input, joka pitää syöttää funktioon. Esim. funktiolla KESKIARVO on 1-127 parametriä, riippuen siitä, kuinka monta solua syötät funktioon. Parametrit voivat olla pakollisia tai valintaisia. Funktioilla voi myös olla oletusarvoja parametreille, joita käytetään jos käyttäjä ei itse määrittele parametriä funktiokutsussa. Parametrin synonyymi on avainsana.

Datatyyppi: Excel tulkitsee solujen tiedot eri datatyyppeinä. Näitä ovat mm. luvut, tekstijonot, päivämäärät, funktiot ja virhearvot.

Virhearvo: Ennalta määritelty datatyyppi, joka ilmaisee jonkun virheen. Näitä ovat mm. #PUUTTUU!, #ARVO!, #JAKO/0!

19.3 Tietofunktiot

19.3.1 ON-funktiot

=FUNKTIO(arvo)

ON-funktiot palauttavat totuusarvon TOSI, jos niiden vaatimus solulle täyttyy, ja EPÄTOSI jos vaatimus ei täyty.

Funktiot ja niiden vaatimukset:

  • ONTYHJÄ: Onko soluarvo tyhjä?
  • ONVIRH: Onko soluarvo jotain muuta, kuin #PUUTTUU!? (Eli ONPUUTTUU-funktion vastakohta)
  • ONVIRHE: Onko soluarvo jokin virhearvo? (#PUUTTUU!, #ARVO!, #VIITTAUS!, #JAKO/0!, #LUKU!, #NIMI?, #TYHJÄ!)
  • ONTOTUUS: Onko soluarvo totuusarvo? (TOSI tai EPÄTOSI)
  • ONPUUTTUU: Onko soluarvo virhearvo #PUUTTUU!? (Eli ONVIRH-funktion vastakohta)
  • ONEI_TEKSTI: Onko soluarvo jotain muuta kun tekstiä? Palauttaa TOSI, jos soluarvo on tyhjä solu.
  • ONLUKU: Onko soluarvo luku?
  • ONVIITT: Onko soluarvo viittaus toiseen paikkaan?
  • ONTEKSTI: Onko soluarvo tekstiä?
  • ONPARILLINEN: Onko soluarvo parillinen? (Eli ONPARITON-funktion vastakohta)
  • ONPARITON: Onko soluarvo pariton? (Eli ONPARILLINEN-funktion vastakohta)
  • ONKAAVA: Onko soluarvo kaava?

19.3.2 PUUTTUU

=PUUTTUU()

Palauttaa virhearvon #PUUTTUU!.

19.3.3 SOLU

=SOLU(kuvaus_laji; viittaus)

Palauttaa tietoa soluviittauksesta.

Viittausalue määritellään viittaus-avainsanalla. Tietopyyntö määritellään kuvaus_laji-avainsanalla.

Avainsanojen komentojen eri määritelmät:

SOLU-funktion kuvaus_laji-avainsanan komennot.
Komento Palautus
osoite Soluviittauksen osoite tekstinä (esim. A2)
sarake Solun sarake numerona (esim. 2 sarakkeelle B)
väri Arvo 1, jos solun negatiiviset arvot näkyvät värillisinä. Muuten arvo 0.
sisältö Solun arvon (esim. 1000 tai "Hei")
tiedostonimi Solun sisältävän tiedoston nimen. Palauttaa tyhjää "" jos tiedostoa ei ole tallennettu.
muoto Solun lukumuotoilun erityisenä merkkijonona. Katso kaikki merkkijonot funktion apusivuilta.
sulkeet Arvo 1, jos solun positiiviset/kaikki arvot esitetään sulkeissa. Muuten arvo 0.
etuliite Solun tasausmuotoilun erityisenä merkkijonona. Katso kaikki merkkijonot funktion apusivuilta.
suojaus Arvo 1, jos solu on lukittu, tai arvo 0, jos solua ei ole lukittu.
rivi Solun rivi numerona (esim. 2 riville 2)
tyyppi Solun sisällön tyyppi erityisenä merkkijonona. Palauttaa "h" jos solu on tyhjä, "o" jos solussa on tekstivakio, ja muuten "r".
leveys Palauttaa kaksisoluisen matriisin. Ensimmäisessä solussa on pyydetyn solun sarakeleveys. Toisessa solussa on totuusarvo TOSI, jos kyseinen leveys on oletusarvo, ja EPÄTOSI jos leveyttä on muutettu käyttäjän toimesta.

19.4 Loogiset funktiot

19.4.1 TOSI ja EPÄTOSI

=TOSI(), =EPÄTOSI()

Palauttavat totuusarvot TOSI tai EPÄTOSI, nimensä mukaan. Voit vaihtoehtoisesti käyttää sanoja TOSI tai EPÄTOSI funktioiden sijaan.

19.4.2 JA

=JA(arg1; arg2; ...)

Palauttaa totuusarvon TOSI, jos kaikki funktion avainsanojen arvot ovat TOSI. Palauttaa EPÄTOSI, jos vähintään yksi funktion avainsanoista on EPÄTOSI.

TAI-funktion vastakohta (ks. Alaluku 19.4.5).

Avainsanojen tulee olla totuusarvoja tai solu-/matriisiviittauksia totuusarvoihin.

Funktio ei ota huomioon tekstiä, lukuja tai tyhjiä soluja.

Funktiota käytetään usein yhdistelmässä muiden loogisten funktioiden kanssa.

19.4.3 JOS-funktiot

=JOS(arvioitava; jostosi; josepätosi)

=JOS.JOUKKO(arvioitava1; jostosi1; arvioitava2; jostosi2; ...)

=JOSVIRHE(arvioitava; josvirhe)

=JOSPUUTTUU(arvioitava; jospuuttuu)

Funktioperhe arvioi jonkun loogisen funktion ja palauttavat ennalta määriteltyjä tuloksia loogisen testin pohjalta.

JOS-funktio arvioi loogisen funktion avainsanassa arvioitava. Käyttäjä määrittelee mitä palautetaan, jos arvioitava-funktion tulos on TOSI (jostosi), sekä jos tulos on EPÄTOSI (josepätosi).

JOS.JOUKKO-funktio arvioi yhden tai useamman (korkeintaan 127) loogisen funktion ja palauttaa sitä vastaavan totuusfunktion jostosiN, jos arvioitavaN-funktion tulos on TOSI.

Jokaiselle arvioitavalle loogiselle funktiolle tulee syöttää yksi TOSI-ehdon alla tuotettava arvo, eli avainsanat syötetään pareissa. Yksi ehto arvioidaan kahdella avainsanalla (arvioitava1, jostosi1); kaksi ehtoa arvioidaan neljällä avainsanalla (arvioitava1, jostosi1, arvioitava2, jostosi2); ja niin edelleen.

Jos mikään avainsanoista ei johda palautukseen, funktio ei palauta mitään.

Voit asettaa oletusarvon palautukselle (eli mitä palautetaan, jos mikään muu testi ei ole TOSI) lisäämällä viimeisen avainsanaparin, jossa arvioitava avainsana on TOSI. Määrittele sitten jostosi-avainsanalle, mitä pitäisi palauttaa. Näiden avainsanojen tulee olla viimeiset listauksessa, muuten ne palautuvat ennen kuin kaikki muut ehdot on käyty läpi!

JOSVIRHE-funktio arvioi loogisen funktion avainsanassa arvioitava. Käyttäjä määrittelee mitä palautetaan, jos funktion tulos palauttaa minkä tahansa virhearvon (josvirhe).

JOSPUUTTUU-funktio arvioi loogisen funktion avainsanassa arvioitava. Käyttäjä määrittelee mitä palautetaan, jos funktion tulos palauttaa virhearvon #PUUTTUU! (jospuuttuu).

19.4.4 EI

=EI(arvioitava)

Palauttaa avainsanan arvioitava totuustuloksen vastakohdan, eli EPÄTOSI jos avainsanan tulos on TOSI tai toisinpäin.

19.4.5 TAI ja EHDOTON.TAI

=TAI(arg1; arg2; ...)

Palauttaa totuusarvon TOSI, jos vähintään yksi funktion avainsanoista ovat TOSI. Palauttaa EPÄTOSI, jos kaikki funktion avainsanojen totuusarvot on EPÄTOSI.

JA-funktion vastakohta (ks. Alaluku 19.4.2).

Avainsanojen tulee olla totuusarvoja tai solu- tai matriisiviittauksia totuusarvoihin.

Funktio ei ota huomioon tekstiä, lukuja tai tyhjiä soluja.

Käytetään usein yhdistelmässä muiden loogisten funktioiden kanssa.

=EHDOTON.TAI(arg1; arg2; ...)

Palauttaa totuusarvon TOSI, jos vähintään yksi mutta ei kaikki funktion avainsanoista ovat TOSI. Palauttaa EPÄTOSI, jos kaikki funktion avainsanojen totuusarvot ovat joko TOSI tai EPÄTOSI.

Ehdoton versio TAI-funktiosta. Toimii samalla tavalla.

19.4.6 LAMBDA

=LAMBDA(par1; par2; ...; parN; laskutoimitus)

LAMBDA-toiminnolla voit laatia omia funktioitasi. Voit määritellä enintään 253 avainsanaa (vähintään nolla) joita funktioisi käyttää. Voit sitten määritellä mitä funktiosi tekee avainsanalla laskutoimitus.

Toimivan LAMBDA-funktion toteuttamisen jälkeen voit rekisteröidä funktion nimen Nimien hallinta -tilassa, jolloin funktiota pystyy käyttämään koko työkirjassa (tai työlehtiössä, jos olet rajannut käytön. Ks. Luku 16).

LAMBDA-toiminto kannattaa tehdä kolmessa askeleessa:

  1. Testaa, että laskutoimitus-kaavasi toimii.
  2. Testaa, että koko LAMBDA-funktio toimii. Kirjoita se kokonaisuudessaan soluun, ja lisää loppuun testiarvot omaan sulkuun: =LAMBDA(x; y; x+y+1)(1;2) määrittelee funktion ja ajaa sen testiarvoilla x = 1 ja y = 2.
  3. Lisää funktio nimien hallintaan. Muista määritellä oikea käyttöalue (työkirja tai yksittäiset taulukot) sekä antaa kuvaileva nimi ja kommentti. Katso lisää nimien hallinnasta luvusta 16).

19.4.7 BYCOL, BYROW ja MAP

=BYCOL(matriisi; LAMBDA(sarake)), =BYROW(matriisi; LAMBDA(rivi)), =MAP(matriisi; LAMBDA(solu)

Funktioilla voit suorittaa LAMBDA-funktion määritetyn matriisialueen jokaiselle riville (BYROW), sarakkeelle (BYCOL) tai yksittäiselle solulle (MAP).

Avainsanalla matriisi määrittelet data-alueen, jonka datalla haluat toimittaa LAMBDA-funktion.

Avainsanalla LAMBDA() määrittelet LAMBDA-funktion, mikä tulee ajaa haluamasi data-alueella.

Määrittele LAMBDA-funktio niin kuin tekisit oman funktion, mutta älä rekisteröi sen nimeä - funktiota käytetään vain tässä kontekstissa.

Funktiopari BYCOL ja BYROW palauttaa matriisin kooltaan 1*N, jossa N on kuinka monta kertaa funktio ajettiin. Funktio MAP taas palauttaa matriisin, jonka koko vastaa alkuperäistä data-aluetta (koska funktion LAMBDA-funktio lasketaan jokaiselle solulle).

Jos määritelty data-alue sisältäisi neljä saraketta, BYCOL palauttaisi 1*4-matriisin. Jos määritelty data-alue taas sisältäisi kolme riviä, BYROW palauttaisi 1*3-matriisin. Jos määritelty data-alue sisältää neljä saraketta ja kolme riviä, MAP palauttaisi 4*3-matriisin.

MAP-funktio osaa myös yhdistää monia solujonoja yhteen. Tällöin funktio palauttaa 1*N-matriisin, jossa N on yhdistettävien solujonojen määrä. Esimerkiksi =MAP(A1:A5; B1:B5; C1:C5; LAMBDA() palauttaa 1*3-matriisin, jossa jokainen solu on LAMBDA-funktion lopputulos kullekin solujonolle (ensin A1:A5, sitten B1:B5, ja viimeiseksi C1:C5).

Ole tarkkana kun syötät MAP-funktiolle dataa! Jos yhdistät solujonoja, funktiot ajetaan per solujono, mutta jos syötät kokonaisen matriisin, funktiot ajetaan per solu.

Esimerkki:

=BYCOL(A1:C2; LAMBDA(sarake; MAKS(sarake)) palauttaa 1*3-matriisin (koska data-alueella on kolme saraketta, A, B ja C). Jokaisessa solussa on LAMBDA-funktion lopputulos sille sarakkeelle (ensimmäisessä solussa tulos A-sarakkeelle, toisessa B-sarakkeelle ja kolmannessa C-sarakkeelle). Laskettava funktio on MAKS(sarake), mikä palauttaa sarakkeen suurimman arvon.

19.5 Haku- ja viitefunktiot

19.5.1 VALITSE-funktiot

=VALITSESARAKKEET(matriisi; sarake1; ...), =VALITSERIVIT(matriisi; rivi1; ...)

Funktio palauttaa valitut sarakkeet tai rivit matriisi-alueelta. Funktiolle annetaan sarake- tai rivinumero1, sekä alue, josta tiedot haetaan. Funktioille voi antaa useamman sarake- tai rivinumeron.

1 Ei siis kirjainta sarakkeiden kohdalla! Sarake A:n numero on 1, sarake D:n numero on 4, jne.

Negatiivisilla rivi- tai sarakenumeroilla voi hakea tietoja matriisin lopusta. Arvo -1 on sama kuin viimeinen sarake/rivi, -2 toiseksi viimeinen, ja niin eteenpäin. Näin voit käyttää funktiota vaikka et tietäisi kuinka monta riviä/saraketta alkuperäisessä matriisissa olisi.

Rivi- tai sarakenumerot eivät tarvitse olla alkuperäisessä järjestyksessään, vaan ne palautetaan siinä järjestyksessä, miten ne määritellään funktiossa. Esimerkiksi sarakenumerot 1, 2, 5, 4 palauttaa vastaavat sarakkeet (A, B, E, D) tässä järjestyksessä.

Esimerkki:

=VALITSESARAKKEET(A1:C5; 2) palauttaa sarakkeen B (sarakenumero 2) kaikki arvot. Koska alueella on viisi riviä, funktio palauttaa seuraavat solut: B1, B2, B3, B4, B5.

=VALITSERIVIT(A1:C5, 2, 4) palauttaa rivien 2 ja 4 kaikki arvot. Koska alueella on kolme saraketta ja valittiin kaksi riviä palautukseen, funktio palauttaa matriisin jossa ensimmäisellä rivillä on solut A2, B2, C2 ja toisella rivillä solut A4, B4, C4.

19.5.2 SARAKE ja RIVI

=SARAKE(solu), =RIVI(solu)

Funktiot palauttavat solun sarake- tai rivinumeron. Numerointi on juoksevassa järjestyksessä: riveillä numero on sama kuin rivin viittausnumero yleisesti (rivi 1 on 1, 2 on 2, jne.), ja sarakkeille numerointi alkaa yhdestä (sarake A on 1, B on 2, jne.).

19.5.3 HYLKÄÄ

=HYLKÄÄ(matriisi; rivit; sarakkeet)

Funktio poistaa määritetyn määrän rivejä ja/tai sarakkeita alkuperäisestä matriisista, ja palauttaa tuloksen uutena matriisina. Poisto koskee vain palautettua matriisia - funktio ei siis poista tietoja alkuperäisestä matriisista. Avainsana rivit on pakollinen, sarakkeet on valinnainen.

Negatiivisilla arvoilla voit poista rivejä/sarakkeita matriisin lopusta alkupään sijaan: rivit = -1 poistaa viimeisen rivin, sarakkeet = -3 poistaa kolme viimeistä saraketta, ja niin eteenpäin.

19.5.4 SUODATA

=SUODATA(matriisi; sisällytä; jostyhjä)

Funktio palauttaa suodatetun version alkuperäisestä matriisista. Avainsanalla sisällytä määrittelet hakualueen, jonka pohjalta suodatetaan, sekä loogisen säännön jonka pohjalta rivejä sisällytetään uuteen matriisiin. Avainsanalla jostyhjä määrittelet mitä palautetaan, jos palautettava matriisi olisi tyhjä (eli suodatin ei löytäisi yhtään sääntöä vastaavaa tulosta).

Esimerkki:

=SUODATA(A1:C5; B1:B5 = 2) hakee matriisin A1:C5 (kolme saraketta, viisi riviä), ja palauttaa kaikki rivit, joiden B-sarakkeen arvo on 2. Jos näitä ei ole, funktio palauttaa virhearvon #TYHJÄ!.

=SUODATA(A1:C5; var1 = E1; "Ei löydy!") hakee matriisin A1:C5 (kolme saraketta, viisi riviä), ja palauttaa kaikki rivit, joiden nimetystä sarakkeesta var1 löytyy arvo, joka itsessään on määritelty solussa E1. Jos solussa E1 lukee esimerkiksi 2, funktio palauttaa säännön var1 = 2 mukaan; jos siinä lukee "b", funktio palauttaa säännön var1 = "b" mukaan, ja niin eteenpäin. Jos palautettavia arvoja ei ole ollenkaan, funktio palauttaa tekstijonon "Ei löydy!". HUOM: Jos nimeä var1 ei olla määritelty, funktio palauttaa virheen.

19.5.5 XHAKU

=XHAKU(hakuarvo; hakumatriisi; palautusmatriisi; joseilöydy; vastaavuustila; hakutila)

Funktiolla suoritat hakuja alkuperäisestä matriisista ja palautat yhden tai useamman solun. Funktio on hyvin joustava siinä, mitä se palauttaa ja missä muodossa. Avainsanat ovat:

  • hakuarvo (pakollinen): Mitä haetaan? Voi olla mikä tahansa Excelissä pätevä datamuoto (esim. luku, päivämäärä, merkkijono, jne.)
  • hakumatriisi (pakollinen): Mistä haetaan? Määrittele joko rivi tai sarake kokonaisuudessaan. Esim. A1:A5 hakee sarakkeesta A, var1 hakee nimetyltä alueelta “var1”, A1:C1 hakee riviltä 1. Tämä on siis avainsana, jolla määrittelet, mitä loogista sääntöä yrität täyttää (“hae kaikki rivit, joiden sukupuoli on 2; hae kaikki rivit, joiden bruttovuositulot ovat 60 000 euroa”…)
  • palautusmatriisi (pakollinen): Miltä alueelta palautetaan? Määrittele joko rivi tai sarake kokonaisuudessaan. Tämä avainsana määrittelee siis, mikä arvo palautetaan, kun olet löytänyt hakuarvon määritetystä hakumatriisista (“kun olet löytänyt kaikki rivit, joiden sukupuoli on 2, palauta niiden bruttovuositulot”).
  • joseilöydy (valinnainen): Mitä palautetaan, jos mitään ei löydy? Jos ei määritellä tätä avainsanaa, funktio palauttaa virhearvon #PUUTTUU! jos palautettavaa ei löydy.
  • vastaavuustila (valinnainen): Miten tarkasti haetaan? Oletusasetuksena funktio hakee tarkan vastaavuuden, ja palauttaa #PUUTTUU! jos sitä ei löydy. Voit myös määritellä yhden seuraavista: -1 palauttaa tarkan vastaavuuden tai seuraavaksi pienimmän kohteen, 1 palauttaa tarkan vastaavuuden tai seuraavaksi suuremman kohteen, 2 hakee laajalla vastaavuudella, jolloin voit käyttää villikorttimerkkejä *, ?, ~.2
  • hakutila (valinnainen): Miten ja missä järjestyksessä haku suoritetaan? Oletuksena haku suoritetaan ensimmäisestä kohteesta alkaen (1), voit myös määritellä sen suoritettavan viimeisestä kohteesta (-1), binäärihaku nousevassa järjestyksessä (2) tai binäärihaku laskevassa järjestyksessä (-2). Binäärihaut ovat nopeampia, mutta vaativat, että matriisi on valmiiksi järjestetty kyseiseen järjestykseen - muutoin ne palauttavat virheellisiä tuloksia.

2 * edustaa mitä tahansa merkkimäärää (nollasta ylöspäin) missä tahansa kohtaa sanaa. ? edustaa yksittäistä merkkiä missä tahansa kohtaa sanaa. ~ on poistumismerkki (engl. escape character), joka kertoo Excelille, että seuraava tähti, kysymysmerkki tai tilde ei ole villikorttimerkki vaan osa tekstiä. Lue lisää: Yleismerkkien käyttäminen hauissa - Microsoft-tuki.

19.5.6 Lajittelufunktiot

=LAJITTELE(matriisi; lajitteluindeksi; lajittelujärjestys; sarmukaan), =LAJITTELE.ARVOJEN.PERUSTEELLA(matriisi; mukaan1; järjestys1; mukaan2; järjestys2; ...)

Funktiot hakevat alkuperäisen matriisin, lajittelevat sen tietyn säännön mukaan, ja palauttavat lajiteltun matriisin.

LAJITTELE-funktiossa määrittelet ensin mikä matriisi-alue lajitellaan. Jos valitset useamman rivin/sarakkeen, voit myös määritellä, minkä rivin/sarakkeen mukaan lajitellaan avainsanalla lajitteluindeksi. Avainsana lajittelujärjestys määrittelee, onko lajittelu nousevassa (1, oletus) vai laskevassa (-1) järjestyksessä. Avainsanalla sarmukaan voit määritellä, lajitellaanko rivin (EPÄTOSI, oletus) vai sarakkeen (TOSI) mukaan.

LAJITTELE.ARVOJEN.PERUSTEELLA-funktio tekee melkein saman, mutta sillä voit määritellä useamman lajittelualueen ja -järjestyksen. Funktio lajittelee ensin alueen mukaan1 mukaan, järjestyksessä järjestys1 (samat arvot ja oletukset kuin lajittelujärjestys yllä), sitten alueen mukaan2 ja järjestyksen järjestys2 mukaan, ja niin eteenpäin.

Yhdistelemällä LAJITTELE- ja SUODATA-funktioita voit rakentaa joustavia ja (suht) nopeasti käytettävissä olevia ehdollisia hakuja tiedossasi. Haluatko esimerkiksi kaikki vastaajat, joiden bruttotulot ovat vähintään 50 000 euroa, järjestettynä koulutustason mukaan? Onnistuu: =LAJITTELE.ARVOJEN.PERUSTEELLA(SUODATA(datamatriisi; bruttotulot >= 50000); koulutustaso).

19.5.7 OTA

=OTA(matriisi; rivit; sarakkeet)

Funktio palauttaa matriisista määritetyn määrän rivejä (pakollinen) ja sarakkeita (valinnainen). Rivit ja sarakkeet määritetään määrässä (esim. 2 tai 5 riviä). Negatiiviset numerot hakevat matriisin lopusta.

19.5.8 TRANSPONOI

=TRANSPONOI(matriisi)

Funktio kääntää alkuperäisen matriisin siten, että riveistä tulee sarakkeita ja sarakkeista rivejä.

Alueen, johon funktio palauttaa käännetyn matriisin, voi määritellä joko dynaamisesti (lisäämällä funktio vasempaan yläkulmaan ja painamalla Enter) tai eksplisiittisesti (maalaamalla ensin koko alue, ja kirjoittamalla funktio suoraan alueeseen. Lopussa painetaan Ctrl + Shift + Enter, jotta funktio täyttää koko alueen eksplisiittisesti eikä dynaamisesti). Lue lisää dynaamisista matriiseista alaluvusta 14.9.

19.5.9 AINUTKERTAISET.ARVOT

=AINUTKERTAISET.ARVOT(matriisi; sarmukaan; vainkerran)

Funktio palauttaa matriisin kaikki uniikit (ainutkertaiset) arvot. Valinnaisella loogisella avainsanalla sarmukaan funktio palauttaa uniikit sarakkeet (TOSI) rivien sijaan (EPÄTOSI tai jätetty pois). Valinnaisella loogisella avainsanalla vainkerran voit määritellä, että funktio palauttaa vain ne uniikit arvot, jotka esiintyvät vain yhden kerran matriisissa (useamman kerran sijaan).

19.6 Matemaattiset ja trigonometriset funktiot

Koska matemaattisia ja trigonometrisia funktioita löytyy hyvin suuri määrä Excelissä, avaan tässä luvussa vain yleisimmät, tilastotieteen käytössä olevat funktiot. Tarkista koko luettelo Excelin opassivuilla.

19.6.1 Trigonometriset funktiot

Trigonometrisia funktioita on suuri määrä Excelissä. Jokaiselle funktiolle annetaan alkuperäinen arvo avainsanana (=FUNKTIO(arvo)). Excelissä löytyy yleisimmät identiteettifunktiot sekä niiden hyperboliset versiot (aina jälkiliitteellä H funktion nimessä). Katso Excelin opassivut kaikista funktioista (linkki tämän luvun alussa).

19.6.2 ITSEISARVO

=ITSEISARVO(arvo)

Palauttaa arvon itseisarvon, eli absoluuttisen arvon.

19.6.3 KOOSTE ja VÄLISUMMA

=KOOSTE(funktio; asetukset; matriisi; k)

=VÄLISUMMA(funktio; viittaus)

KOOSTE-funktio palauttaa yleisiä koostefunktioita määrätylle matriisiviittaukselle. Funktio toimii siten niin sanottuna wrapper-funktiona, eli sille syötetään, mikä koostefunktio halutaan ajaa matriisille.

VÄLISUMMA-funktio tekee saman, mutta on yksinkertaisempi. Funktio ei ota asetuksia.

Pakollisella avainsanalla funktio määrittelet, minkä luvun haluat laskea. Vaihtoehtoja on yhteensä 19, numeroituna 1-19:

  1. KESKIARVO
  2. LASKE
  3. LASKE.A
  4. MAKS
  5. MIN
  6. TULO
  7. KESKIHAJONTA.S
  8. KESKIHAJONTA.P
  9. SUMMA
  10. VAR.S
  11. VAR.P
  12. MEDIAANI
  13. MOODI.YKSI
  14. SUURI
  15. PIENI
  16. PROSENTTIPISTE.SIS
  17. NELJÄNNES.SIS
  18. PROSENTTIPISTE.ULK
  19. NELJÄNNES.ULK

Tämä luettelo näkyy myös, kun kirjoitat funktiota Excelissä, joten sinun ei tarvitse osata sitä ulkoa.

VÄLISUMMA-funktiossa voit käyttää vain ensimmäistä yhtätoista funktiota (eli KESKIARVO:sta VAR.P:hen).

Pakollisella avainsanalla asetukset määrittelet, mitkä eri arvot ohitetaan laskussa. Jos funktio löytää näitä arvoja matriisin sisällä, se seuraa tässä määriteltyä logiikkaa. Asetuksia on kahdeksan:

  1. Ohittaa VÄLISUMMA- ja KOOSTE-funktiot.
  2. Ohittaa piiloitetut rivit, VÄLISUMMA- ja KOOSTE-funktiot.
  3. Ohittaa virhearvot, VÄLISUMMA- ja KOOSTE-funktiot.
  4. Ohittaa piiloitetut rivit, virhearvot, VÄLISUMMA- ja KOOSTE-funktiot.
  5. Ei ohita mitään.
  6. Ohittaa piiloitetut rivit.
  7. Ohittaa virhearvot.
  8. Ohittaa piiloitetut rivit ja virhearvot.

HUOM: Funktio ei ohita asioita, jos funktion sisällä tehdään laskutoimitus! Tällöin ohitus pitää tehdä laskutoimituksen sisällä, esim. käyttäen JOS- ja ONVIRH-rakenteita.

VÄLISUMMA-funktio ei ota asetuksia, mutta muokkaamalla funktionumeroa voit ohittaa piiloitetut rivit. Funktiot 1-9 määritellään ohittamaan piiloitetut rivit lisäämällä 10 niiden etuliitteeksi: 101, 102, 103, jne. Funktiot 10 ja 11 ottavat numeron 1 etuliitteeksi: 110 on VAR ja 111 on VAR.P.

Pakollisella avainsanalla matriisi määrittelet alueen, jolle haluat laskea kyseisen laskufunktion.

Viimeinen avainsana, k, on pakollinen tietyillä laskufunktioilla. Funktioilla SUURI ja PIENI määrittelet, minkä k:n suurimman/pienimmän arvon haluat. PROSENTTIPISTE-funktioilla k määrittelee, minkä k:nnen prosenttipisteen arvon haluat. NELJÄNNES-funktoilla k määrittelee, minkä neljänneksen haluat.

19.6.4 PARILLINEN ja PARITON

=PARILLINEN(arvo), =PARITON(arvo)

Funktiot pyöristävät luvun ylöspäin lähimpään parilliseen tai parittomaan kokonaislukuun.

19.6.5 EKSPONENTTI

=EKSPONENTTI(arvo)

Palauttaa arvon \(e^\text{arvo}\).

19.6.6 KERTOMA

=KERTOMA(arvo)

Palauttaa arvon kertoman, eli faktoriaalin. Luvun faktoriaali on yhtä kuin sarja

\[\prod_{i=1}^{n}{i}\]

Jos luku on 5, niin KERTOMA(5) palauttaa \(5*4*3*2*1=120\).

19.6.7 Pyöristysfunktiot

=PYÖRISTÄ.KERR(arvo; tarkkuus)

=PYÖRISTÄ.KERR.ALAS(arvo; tarkkuus)

=PYÖRISTÄ.KERR.YLÖS(arvo; tarkkuus)

=PYÖRISTÄ.KERR.ALAS.TARKKA(arvo; tarkkuus)

=PYÖRISTÄ(arvo; desimaalit)

=PYÖRISTÄ.DES.ALAS(arvo; desimaalit)

=PYÖRISTÄ.DES.YLÖS(arvo; desimaalit)

Kaikki pyöristysfunktiot toimivat samalla tavalla ja näyttävät samoilta, mutta pienillä eroilla.

Tavallinen pyöristys hoituu funktiolla PYÖRISTÄ, joka ottaa arvon joka pyöristetään, sekä desimaalitarkkuuden numeroissa. Esim. =PYÖRISTÄ(2,673; 1) pyöristää arvon yhden desimaalin tarkkuudelle, eli 2.7. Suunta määräytyy niin kuin matematiikassa yleensä, eli >= 0.5 pyöristetään ylös ja < 0.5 alas.

Jos haluat pyöristää tiettyyn suuntaan, voit käyttää PYÖRISTÄ.DES.ALAS tai PYÖRISTÄ.DES.YLÖS.

Funktioperhe PYÖRISTÄ.KERR pyöristää tiettyä kerrannaisnumeroa kohtaan. Funktio hakee siis lähimmän numeron, jonka voi tasajakaa tarkkuus-arvolla. Pääteliitteet .ALAS ja .YLÖS määrittelevät pyöristyksen suunnan. Yleisfunktiossa PYÖRISTÄ.KERR suunta määräytyy jakojäännöksen perusteella: jos jaosta jää >= puolet kerrannaisen arvosta, se pyöristää ylöspäin, ja muuten alaspäin. Esimerkkinä =PYÖRISTÄ.KERR(10;3) laskee, että kymmenen jako kolmella jättää yhden jäljelle, mikä on vähemmän kuin puolet kolmesta (<= 1.5), joten se pyöristää alaspäin. Lähin tasajakonumero alaspäin on 9, joten tulos on 9.

19.6.8 SUURIN.YHT.TEKIJÄ ja PIENIN.YHT.JAETTAVA

=SUURIN.YHT.TEKIJÄ(luku1; luku2; ...), =PIENIN.YHT.JAETTAVA(luku1; luku2; ...)

Funktiot hakevat suurimman tai pienimmän yhteisen tekijän kahdelle tai useammalle arvolle. Nolla on tässä jaettava kaikilla luonnollisilla luvuilla, ja negatiiviset luvut eivät toimi.

19.6.9 Logaritmit

=LOG(arvo; kanta), =LOG10(arvo), =LUONNLOG(arvo)

Funktioperhe LOG palauttaa arvon logaritmin. Erikoisfunktiot LOG10 ja LUONNLOG palauttavat arvon kymmenkantaisen logaritmin \(10^{x}\) ja luonnollisen logaritmin \(e^{x}\). Muut logaritmikannat määritellään LOG-funktion kanta-avainsanalla.

19.6.10 JAKOJ

=JAKOJ(luku; jakaja)

Funktio palauttaa jäännöksen jakolaskulle luku/jakaja. Jakojäännöksellä on aina sama etumerkki (positiivinen tai negatiivinen) kuin jakajalla.

19.6.11 PII

=PII()

Palauttaa piin arvon 15:lla desimaalilla.

19.6.12 Satunnaisluvut

=SATUNNAISLUKU(), =SATUNNAISLUKU.VÄLILTÄ(alku; loppu)

Funktiot generoivat satunnaislukuja. Perusfunktio SATUNNAISLUKU tuottaa yhden luvun väliltä 0-1, ja funktio SATUNNAISLUKU.VÄLILTÄ tuottaa luvun väliltä alku-loppu. Luvut generoidaan Mersenne Twister -algoritmilla, ja tuotetaan uudelleen joka kerta, kun kaavat lasketaan uudelleen.

19.6.13 Neliöjuuret

=NELIÖJUURI(arvo), =NELIÖJUURI.PII(arvo)

Funktiot laskevat arvon neliöjuuren. Perusfunktio laskee tavallisen neliöjuuren (positiivisen), NELIÖJUURI.PII kertoo ensin arvon piillä ja laskee sitten neliöjuuren.

19.6.14 Summat

=SUMMA(alue)

=SUMMA.JOS(alue; ehto)

=SUMMA.JOS.JOUKKO(alue; ehtoalue1; ehto1; ehtoalue2; ehto2; ...)

Summafunktiolla lasket yhteen määrätyn alueen kaikki arvot. Voit asettaa ehdon summaukselle käyttämällä SUMMA.JOS-funktiota, jolloin arvot summataan yhteen vain silloin, kun ne täyttävät loogisen ehdon ehto-avainsanassa.

Jos haluat määritellä useita ehtoja, tai jos ehdot riippuvat jonkun toisen alueen arvoista, voit käyttää SUMMA.JOS.JOUKKO-komentoa. Jokainen ehto määritellään alue-ehto-parina, jossa ehtoalue määrittelee, millä alueella ehto tarkistetaan, ja ehto määrittelee itse ehdon. Alue-ehto-pareja voi olla 127 kappaletta.

19.6.15 Neliösummat

=NELIÖSUMMA(alue)

=NELIÖSUMMIEN.EROTUS(alue1; alue2)

=NELIÖSUMMIEN.SUMMA(alue1; alue2)

=EROTUSTEN.NELIÖSUMMA(alue1; alue2)

Tämä funktioperhe laskee neliösummia, jolloin ensin otetaan jokaisen arvon neliö (\(x^2\)) ja sitten summataan ne yhteen.

Perusfunktio NELIÖSUMMA tekee tämän arvosarjalle, esim. kokonaiselle sarakkeelle tai riville. Kaava tälle on: \(\sum{x^2}\)

Kolme seuraavaa toimivat kahdella alueella, ja tekevät eri asioita riippuen funktiosta:

  • NELIÖSUMMIEN.EROTUS laskee kahden alueen jokaisen arvon neliön, vähentää ne toisistaan (alue2 - alue1) ja summaa lopputuloksen yhteen. Kaava on: \(\sum{x^2-y^2}\)
  • NELIÖSUMMIEN.SUMMA laskee kahden alueen jokaisen arvon neliön ja summaa ne yhteen. Kaava on: \(\sum{x^2 + y^2}\)
  • EROTUSTEN.NELIÖSUMMA laskee ensin kahden alueen jokaisen arvon eron (alue2 - alue1), ottaa tuloksen neliön ja summaa kaikki nämä yhteen. Kaava on: \(\sum{(x-y)^2}\)

19.6.16 KATKAISE

=KATKAISE(luku; numerot)

Funktio pudottaa desimaalit annetulta luvulta. Oletusarvona funktio pudottaa kaikki desimaalit, mutta voit myös määritellä kuinka monta jätetään valinnaisella avainsanalla numerot. Huomaa, että funktio ei pyöristä, vaan tiputtaa vain pois desimaalit!

19.7 Tilastolliset funktiot

19.7.1 Sijaintiluvut

19.7.1.1 LASKE-funktiot

=LASKE(alue)

=LASKE.A(alue)

=LASKE.TYHJÄT(alue)

=LASKE.JOS(alue; ehto)

=LASKE.JOS.JOUKKO(alue; ehtoalue1; ehto1; ehtoalue2; ehto2; ...)

Jos haluat laskea, kuinka monta arvoa tietyllä alueella on (esim. laskeaksesi analyysissa käytetyn N:n tietylle laskutoiminnolle), voit käyttää funktiota LASKE. Funktio laskee vain luvut, päivämäärät ja tekstimuotoiset luvut jotka Excel pystyy muuntamaan luvuiksi.

Jos haluat laskea myös totuusarvoja, tekstiä tai virhearvoja, käytä funktiota LASKE.A. Funktio laskee kaikkien arvojen määrän, lukuunottamatta tyhjiä soluja.

Jos haluat laskea nimenomaan tyhjät solut, käytä funktiota LASKE.TYHJÄT. Funktio laskee tyhjät solut sekä solut, joissa on tyhjä teksti "". Funktio ei laske nollaa!

Voit ehdollistaa laskutoiminnon käyttämällä funktiota LASKE.JOS. Funktio laskee vain ne solut, jotka täyttävät ehdon. Huomaa, että funktio laskee samat arvot kuin perusfunktio LASKE, eli tyhjiä, tekstiä, totuusarvoja tai virheitä ei lasketa mukaan.

Voit myös kirjoittaa useamman ehdon sekä käyttää eri ehtoalueita funktiolla LASKE.JOS.JOUKKO. Ehdot annetaan alue-ehto-pareina, jossa alue määrittelee, millä alueella ehdon on pädettävä alkuperäisen luvun ollakseen mukana laskussa, ja ehto määrittelee itse ehdon. Laskettava alue määritellään alue-avainsanalla, eli laskettava alue voi olla eri kuin ehtoalueet. Tyhjät solut kohdellaan arvona 0, sekä laskussa että ehdoissa.

19.7.1.2 TAAJUUS

=TAAJUUS(alue; lokerot)

Funktio palauttaa frekvenssitaulukon. Määrittele ensin alue, josta haluat laskea kaikkien arvojen frekvenssit. Voit määritellä mitkä arvot lasketaan lokerot-alueella, mutta voit myös jättää sen pois, jolloin funktio automaattisesti laskee kaikki ainutlaatuiset arvot ja niiden esiintymiset.

Huomaa, että lokerot tulee määritellä lukujanana eli yksiulotteisena matriisina! Voit joko kirjoittaa lokeroarvot vierekkäisiin soluihin jonnekin työlehteesi, tai kirjoittaa ne käsin funktioon käyttäen {}-syntaksia.

Jos määrittelet lokerot, voit pudottaa muuttujan tasoa suhde- tai välimatka-asteikolta järjestysasteikolle. Jokainen lokero jonka määrittelet on raja-arvo; ensimmäinen lokeroarvo on kaikki arvot alle tai yhtä suuria kuin lokeroarvo, toinen on kaikki arvot ensimmäisen ja toisen arvon välillä, jne. Jos siis määrittelet lokeroarvoina matriisialueen jossa on arvot {70, 79, 89}, niin funktio laskee neljä lokeroa: Arvot 70 tai vähemmän, arvot 71-79, arvot 80-89, ja arvot 90 tai enemmän.

TAAJUUS palauttaa aina yhden luvun enemmän, kuin lokeroja on. Tämä luku edustaa kaikkia arvoja, jotka ovat suurempia kuin suurimman arvon lokero. Jos lokerot esim. määritellään yhdestä viiteen, TAAJUUS palauttaa kuudennen lokeron joka edustaa kaikkia arvoja yli viiden.

19.7.1.3 Moodi

=MOODI.YKSI(alue), =MOODI.USEA(alue)

Funktiot hakevat lukusarjan moodin, eli useimmiten esiintyvän arvon. Jos odotat vain yhtä moodia, käytä funktiota MOODI.YKSI, mutta huomaa, että sen käyttäytyminen jos se löytää useamman moodin ei ole määritelty! Multimodalisuuden kohdalla käytä funktiota MOODI.USEA, joka palauttaa pystysuuntaisen matriisin. Tämä matriisi sisältää kaikki esiintyvät moodit.

19.7.1.4 Mediaani

=MEDIAANI(alue)

Funktio hakee lukusarjan mediaanin. Jos mediaaneja on kaksi (eli sarja on parillinen), funktio palauttaa kahden keskimmäisen arvon keskiarvon. Funktio laskee mukaan totuusarvot ja tekstimuotoiset luvut (“1”). Muut arvot antavat joko virheen tai ohitetaan, riippuen siitä, ovatko ne matriisiargumentissa vai suoraan viitattuna.

19.7.1.5 Keskiarvo

=KESKIARVO(alue)

=KESKIARVO.TASATTU(alue; tasaus)

=KESKIARVO.JOS(alue; ehto; keskiarvoalue)

=KESKIARVO.JOS.JOUKKO(keskiarvoalue; ehtoalue1; ehto1; ehtoalue2; ehto2; ...)

Yleisen aritmeettisen keskiarvon voit laskea funktiolla KESKIARVO.

Jos haluat laskea tasatun keskiarvon, käytä funktiota KESKIARVO.TASATTU. Määrittele alue jolle haluat laskea keskiarvon, sekä tasaus-prosentti desimaalilukuna (esim. 0.05 jos haluat jättää pois 5 prosenttia datan ääripäistä).

Jos haluat ehdollistaa mitkä luvut valitaan keskiarvoon, voit käyttää funktiota KESKIARVO.JOS. Lisää ehto avainsanaan ehto. Ehto päteää alueeseen joka määriteltiin avainsanalla alue; jos haluat laskea keskiarvon jollekin muulle alueelle (esim. toiselle muuttujalle kuin ehtomuuttuja), määrittele se avainsanalla keskiarvoalue.

Jos haluat laskea keskiarvon käyttäen useampaa eri ehtoa, käytä funktiota KESKIARVO.JOS.JOUKKO. Ehdot syötetään aina alue-ehto-parina, jossa alue määrittelee miltä alueelta ehto tarkastetaan, ja ehto määrittelee itse ehdon.

19.7.2 Hajontaluvut

19.7.2.1 Pienin, suurin, ja raja-arvot

=MIN(alue), =MAKS(alue)

=MIN.JOS.JOUKKO(alue; ehtoalue1; ehto1; ehtoalue2; ehto2; ...)

=MAKS.JOS.JOUKKO(alue; ehtoalue1; ehto1; ehtoalue2; ehto2; ...)

=PIENI(alue; k), =SUURI(alue; k)

=PROSENTTIPISTE.SIS(alue; k), =PROSENTTIPISTE.ULK(alue; k)

=NELJÄNNES.SIS(alue; neljännes), =NELJÄNNES.ULK(alue; neljännes)

Voit hakea alueen pienimmän ja suurimman arvon funktioilla MIN (minimi) ja MAKS (maksimi).

Voit myös ehdollistaa minimi- ja maksimihaun funktioilla MIN.JOS.JOUKKO ja MAKS.JOS.JOUKKO. Jokainen ehto määritellään alue-ehto-parina, jossa alue määrittelee, missä ehdon tulee pädetä, ja ehto määrittelee itse ehdon. Muista myös määritellä itse laskenta-alue avainsanalla alue!

Jos haluat hakea alueen k:nnen pienimmän tai suurimman arvon, käytä funktioita PIENI ja SUURI. Raja-arvo määritellään omalla avainsanallaan, k.

Jos haluat hakea tietyn fraktiilin persentiilinä, käytä funktioita PROSENTTIPISTE.SIS tai PROSENTTIPISTE.ULK. Molemmat funktiot ottavat alueen sekä arvon k, joka on persentiili ilmaistuna desimaalilukuna. Loppupääte SIS/ULK määrittelee kuuluuko raja-arvo itsessään fraktiilin sisälle (SIS) vai ei (ULK).

Neljännekset, eli kvartiilit, voi hakea myös funktioilla =NELJÄNNES.SIS ja =NELJÄNNES.ULK. Kvartiilin numero ilmaistaan numeroina 0-4: Pienin arvo (0), ensimmäinen neljännes (1), toinen neljännes eli mediaani (2), kolmas neljännes (3), tai suurin arvo (4). Kvartiilinumerot 0, 2 ja 4 vastaavat tuloksia funktioista MIN, MEDIAANI ja MAKS vain jos käytät funktiota NELJÄNNES.SIS. Loppupääte määrittelee kuuluuko raja-arvo neljänneksen sisälle (SIS) vai ei (ULK).

19.7.2.2 Kurtoosi ja vinouma

=KURT(alue), =JAKAUMAN.VINOUS(alue)

Funktiot laskevat alueen kurtoosin tai vinouman.

Excelissä kurtoosi annetaan keskitettynä nollan ympärille, jolloin positiivinen arvo tarkoittaa terävää jakaumaa ja negatiivinen arvo tarkoittaa leveää jakaumaa.

19.7.2.3 Keskipoikkeama

=KESKIPOIKKEAMA(alue)

Funktio laskee alueen keskipoikkeaman. Keskipoikkeama lasketaan kaavalla:

\[\frac{\sum{x-\bar{x}}}{n} \tag{19.1}\]

19.7.2.4 Oikaistu neliösumma

=OIKAISTU.NELIÖSUMMA(alue)

Funktio laskee alueen oikaistun neliösumman (engl. deviance). Oikaistu neliösumma lasketaan kaavalla:

\[\sum{(x-\bar{x})^2} \tag{19.2}\]

19.7.2.5 Varianssi

=VAR.S(alue)

Funktio palauttaa alueen varianssin. Varianssi lasketaan kaavalla:

\[ s^2 = \frac{\sum{(x-\bar{x})^2}} {n-1} \tag{19.3}\]

19.7.2.6 Keskihajonta

=KESKIHAJONTA.S(alue)

Funktio laskee alueen keskihajonnan (engl. standard deviation). Keskihajonta lasketaan kaavalla:

\[ \sigma = \sqrt{\frac{\sum{(x-\bar{x})^2}} {n-1}} \tag{19.4}\]

19.7.2.7 Z-arvo

=NORMITA(x; keskiarvo; keskihajonta)

Palauttaa arvon normitetun z-arvon. Funktiolle pitää antaa arvo, joka halutaan normittaa (x) sekä jakauman keskiarvo ja keskihajonta.

Z-arvo lasketaan kaavalla:

\[ z = \frac{x - \bar{x}} {\sigma} \tag{19.5}\]

19.7.3 Yksimuuttujatestit

19.7.3.1 Z-testi

=Z.TESTI(alue; x; sigma)

Funktio laskee z-testin yksisuuntaisen p-arvon.

Määrittele testattava arvo avainsanalla x, ja alue, jota vastaan testaat avainsanalla alue. Testi testaa tuolloin, eroaako arvo x alueen keskiarvosta merkitsevästi. Toisin sanoin: kuinka suurella todennäköisyydellä saisit arvon x tai isomman, jos sen todellinen arvo onkin alueen keskiarvo eikä x.

Valinnaisella avainsanalla sigma voit antaa populaation tunnetun keskihajonnan. Jos jätät sen pois, keskihajonta estimoidaan otoksesta. Useimmiten sinun tulee jättää se pois.

Jos haluat laskea kaksisuuntaisen z-testin yksisuuntaisen sijaan (eli jos x:n ero keskiarvoon saa mennä kumpaan tahansa suuntaan), voit käyttää seuraavaa kaavaa:

= 2 * MIN( Z.TESTI(); 1 - Z.TESTI() )

Funktio ei laske testisuuretta! Jotta voit raportoida koko testin, sinun tulee myös laskea testisuure käsin. Voit käyttää NORMITA-funktiota laskeaksesi x-arvon z-normitetun arvon, jonka raportoit yhteydessä z-testin palauttaman p-arvon kanssa.

19.7.4 Kaksimuuttujatestit

19.7.4.1 Kovarianssi

=KOVARIANSSI.S(matriisi1; matriisi2)

Funktio laskee kahden matriisin yhteisen kovarianssin. Funktio olettaa, että matriisit ovat yhtä suuria otoksia kokonaispopulaatiosta. Funktio ohittaa tekstit, totuusasvot ja tyhjät solut, mutta ottaa huomioon nollan.

19.7.4.2 Korrelaatiokerroin

=PEARSON(matriisi1; matriisi2)

=PEARSON.NELIÖ(matriisi1; matriisi2)

Funktio PEARSON palauttaa kahden matriisin Pearsonin tulomomenttikorrelaatiokertoimen, r:n. Pearsonin korrelaatio lasketaan kaavalla:

\[ r = \frac{\sum{(x-\bar{x})(y-\bar{y})}} {\sqrt{\sum{(x-\bar{x})^2}\sum{(y-\bar{y})^2}}} \tag{19.6}\]

Funktio PEARSON.NELIÖ laskee ensin r:n, ja palauttaa sitten sen neliön, \(r^2\). Voit tehdä saman ottamalla PEARSON-funktion tulokseen neliön.

19.7.4.3 Khiin neliön testi

=CHINELIÖ.TESTI(todellinen; odotettu)3

3 Excelin kääntäjät eivät taida tietää, että kreikan kirjain \(\chi\) kirjoitetaan suomeksi khii eikä chi.

Funktio laskee khiin neliö -testin p-arvon.

Ensimmäinen avainsana, todellinen, määrittelee todellisten havaintojen matriisin. Toinen avainsana, odotettu, määrittelee odotettujen soluarvojen matriisin.

Jotta voit käyttää khiin neliön testiä, sinun tulee ensin rakentaa kaksi ristiintaulukointia: todelliset arvot ja odotetut arvot. Katso ohjeet alaluvusta 13.3.

Funktio ei laske testisuuretta! Joudut laskemaan testisuureen käsin.

Suureen vapausasteet lasketaan kaavalla \((k_{\text{rivit}}-1) * (k_{\text{sarakkeet}}-1)\).

Voit myös laskea testin yksisuuntaisen p-arvon käyttämällä funktiota CHINELIÖ.JAKAUMA.OH(suure; vapausasteet). Tätä varten tarvitset suureen ja vapausasteet.

19.7.4.4 T-testi

=T.TESTI(matriisi1; matriisi2; suunta; laji)

Funktio laskee t-testin p-arvon.

Ensimmäiset kaksi avainsanaa määrittelevät kaksi matriisia, joita halutaan verrata toisiinsa. Avainsanalla suunta määrittelet, käytätkö yksisuuntaista (1) vai kaksisuuntaista (2) jakaumaa. Avainsanalla laji määrittelet t-testin lajin seuraavista:

  1. Parittainen t-testi samasta otoksesta (ns. within-group)
  2. Kahden otoksen t-testi, saman varianssin oletus (homoskedastinen; ns. between-groups)
  3. Kahden otoksen t-testi, erisuuren varianssin oletus (heteroskedastinen; ns. between-groups; vastaa Welchin t-testiä)

Huomaa, että laji 1 ei toimi, jos matriiseissa on eri määrät arvoja.

Funktio ei laske testisuuretta! Joudut laskemaan testisuureen käsin. Kaavat riippuvat siitä, lasketko parittaisen vai kahden otoksen t-testin. Lue lisää luvusta 11.

19.7.4.5 Järjestyslukujen laskeminen

=ARVON.MUKAAN.TASAN(luku; alue; järjestys), =ARVON.MUKAAN.KESKIARVO(luku; alue; järjestys)

Funktioilla lasket järjestysluvut jollekin lukusarjalle. Funktioiden erona on miten ne käsittelevät tasajärjestystä: .TASAN palauttaa saman järjestysluvun kaikille tasajärjestysluvuille, ja .KESKIARVO palauttaa tasajärjestyslukujen keskiarvon järjestyslukuna.

Avainsana luku määrittelee, minkä luvun järjestysluvun haluat laskea. Avainsana alue määrittelee alueen, jossa haluat laskea järjestysluvut (eli kaikki luvut, joiden mukaan järjestysluku lasketaan). Valinnainen avainsana järjestys määrittelee nousevan 1 tai laskevan 0 järjestyksen; oletuksena on laskeva järjestys.

19.7.5 Merkitsevyystestit

19.7.5.1 Binomijakauman todennäköisyysarvo

=BINOMI.JAKAUMA(luku; yritykset; todennäköisyys; kumulatiivinen)

Funktio hakee p-arvon määritetylle binomijakaumalle. Funktiota käytetään mm. merkkitestin toteuttamisessa. Avainsanalla luku määrittelet merkkimäärän, jota haluat testata. Avainsanalla yritykset määrittelet otoskoon. Avainsanalla todennäköisyys määrittelet todennäköisyysrajan - merkkitestissä aina 0,5. Avainsanalla kumulatiivinen määrittelet, haluatko kumulatiivisen jakaumafunktion EPÄTOSI vai tiheysfunktion TOSI - merkkitestissä käytetään kumulatiivista jakaumaa.

19.7.5.2 Normaalijakauman todennäköisyysarvo

=NORM_JAKAUMA.NORMITA(z; kumulatiivinen)

Funktio hakee p-arvon määritetylle normaalijakauman z-arvolle.

Avainsanalla z määrittelet z-arvon. Avainsanalla kumulatiivinen määrittelet, haetko kumulatiivisen jakaumafunktion TOSI vai todennäköisyystiheysfunktion EPÄTOSI. Yleensä haluat tällä kurssilla tiheysfunktion.

19.8 Tekstifunktiot

19.8.1 SIIVOA

=SIIVOA(teksti)

Poistaa tekstistä tulostumattomat merkit. Nämä ovat merkkejä, jotka eivät näy visuaalisesti, mutta jotka kuitenkin voivat löytyä tekstistä ja voivat aiheuttaa ongelmia myöhemmin.

19.8.2 YHDISTÄ ja &-merkki

=YHDISTÄ(teksti1; teksti2; ...)

Funktiolla voit yhdistää useita tekstijonoja yhdeksi. Tekstijonot yhdistetään sellaisinaan, eli funktio ei lisää välilyöntejä tai muita erotusmerkkejä. Lisää ne itse lisäämällä välilyöntijonon " " kahden tekstijonon väliin.

Funktion sijaan voit käyttää &-merkkiä. Komento A1 & B2 on sama kuin =YHDISTÄ(A1; B2).

19.8.3 TEKSTI

=TEKSTI(arvo; muotokoodi)

Funktiolla voit muotoilla lukuja erinäisiin muotoihin. Luku annetaan avainsanalla arvo, ja halutun muodon muotokoodi annetaan avainsanalla muotokoodi. Muotoilukoodeja on monenlaisia ja ne ovat jokseenkin vaikeasti ymmärrettävissä, suosittelen tarkistaa tämän funktion apusivut: TEKSTI-funktio (Microsoft).

19.8.4 VERTAA

=VERTAA(teksti1; teksti2)

Funktio arvioi, ovatko kaksi merkkijonoa samoja (TOSI) vai erilaisia (EPÄTOSI). Funktio vertaa täsmällisesti, eli isot ja pienet kirjaimet eroavat, kuin myös välilyönnit ja näkymättömät merkit. Funktio ohittaa solun muotoiluerot, esim. lihavoinnin tai kursiivin.

19.8.5 ETSI ja KÄY.LÄPI

=ETSI(hakumerkit; teksti; aloitus)

=KÄY.LÄPI(hakumerkit; teksti)

ETSI-funktio toimii tekstihakuna, ja hakee määrätystä tekstistä jotain merkkijonoa. Teksti, josta haetaan, määritellään avainsanalla teksti, ja merkkijono määritellään avainsanalla hakumerkit. Valinnaisella avainsanalla aloitus voit määritellä indeksin, mistä merkistä haku aloitetaan.

Funktio palauttaa indeksiluvun, missä se ensimmäisenä löysi määritetyn merkkijonon tekstistä. Indeksiluku vastaa merkkijonon ensimmäisen merkin sijaintia tekstissä: jos haetaan esim. sanaa ja tekstistä Ajanjaksot, funktio palauttaa luvun 2. Tämä merkitsee, että haettava teksti löytyy tekstistä alkaen kohdasta 2. Funktio ei siis huomaa ja-sanan toistumista myöhemmin sanassa, koska se löysi ensimmäisen esiintymisen ja lopettaa siihen.

ETSI on tarkka ja erottelee ison ja pienen kirjaimen välillä, sekä ottaa huomioon näkymättömät merkit.

Jos haluat hakea sekä pieniä että isoja kirjaimia, käytä KÄY.LÄPI-funktiota. Huomioi kuitenkin, että KÄY.LÄPI-funktiossa ei voi määritellä aloituskohtaa.

19.8.6 PITUUS

=PITUUS(teksti)

Palauttaa teksti-avainsanassa määritetyn tekstin merkkien määrän.

19.8.7 PIENET ja ISOT

=PIENET(teksti), =ISOT(teksti)

Muuntaa tekstin kaikki merkit joko pieniksi tai isoiksi kirjaimiksi. Ei vaikuta muihin merkkeihin tekstissä, esim. pisteihin tai välilyönteihin.

19.8.8 TEKSTI.JÄLKEEN ja TEKSTI.ENNEN

=TEKSTI.JÄLKEEN(teksti; erotin; aloitus; vastaavuus; loppu_täsmäys; joseilöydy)

=TEKSTI.ENNEN(teksti; erotin; aloitus; vastaavuus; loppu_täsmäys; joseilöydy)

Funktiopari hakee kaiken tekstin joko ennen (.ENNEN) tai jälkeen (.JÄLKEEN) erotinkohdan. Avainsanalla teksti määrittelet, mistä haluat hakea tekstiä. Avainsanalla erotin määrittelet, minkä hakukohdan jälkeen haluat poimia kaiken tekstin. Muut avainsanat ovat valinnaisia:

  • aloitus: Mistä kohdasta tekstiä haku aloitetaan? Ilmaistaan indeksilukuna.

  • vastaavuus: Onko kirjainkoolla merkitystä (1) vai ei (0)? Oletusarvolta kirjainkoolla on merkitystä.

  • loppu_täsmäys: Lasketaanko tekstin loppuminen erottimena (1) vai ei (0)? Oletusarvolta lasketaan.

  • joseilöydy: Mitä tehdään, jos tekstiä ei löydy ollenkaan? Oletusarvolta palauttaa virhearvon #PUUTTUU!.

19.8.9 POISTA.VÄLIT

=POISTA.VÄLIT(teksti)

Poistaa välilyönnit tekstin alusta ja lopusta. Funktio ei poista sanojen välisiä välilyöntejä.

19.8.10 ERISNIMI

=ERISNIMI(teksti)

Muuttaa tekstin ensimmäisen kirjaimen, sekä kaikki erikoismerkkien jälkeiset kirjaimet isoiksi kirjaimiksi.

19.8.11 POIMI.TEKSTI

=POIMI.TEKSTI(teksti; aloitus; määrä)

Funktio poimii tekstistä tietyn määrän merkkejä aloituskohdan jälkeen. Määrittele teksti avainsanalla teksti, aloituskohdan indeksiluvun avainsanalla aloitus, ja poimittavien merkkien määrän avainsanalla määrä.

Jos aloitusluku on suurempi kuin tekstin merkkimäärä, funktio palauttaa tyhjän tekstijonon "". Jos aloitusluku on pienempi kuin merkkimäärä, mutta poimittavien merkkien määrä ylittää tekstin pituuden, funktio palauttaa kaiken tekstin, jonka se voi hakea alkuperäisestä tekstistä.