Luento 6 - Taulukkolaskentafunktioita ja kolmiulotteinen laskenta

• Ohjelmistot0602.mp3 3.3M
• Ohjelmistot0603.mp3 3.1M
• Ohjelmistot0605.mp3 3.3M
• Ohjelmistot0606.mp3 3.7M
• Ohjelmistot0607.mp3 3.5M
• Ohjelmistot0608.mp3 2.7M

Harjoitustyöohjeet

Tarkat ohjeet harjoitustyön tekemiseen löytyy osoitteesta: <URL: http://appro.mit.jyu.fi/ohjelmistot/harkka/>

Lyhyt kertaus edellisen luennon asioista

• IF (suom. JOS)-funktion käyttäminen.
• Sisäkkäisten funktioiden käyttäminen.
• VLOOKUP (suom. PHAKU)-funktion käyttäminen (esim. valuuttalaskin)

Erilaisia funktioita

Seuraavassa on esitelty lyhyesti erilaisia funktioita funktioluokittain. Mukaan ei ole otettu kaikkia mahdollisia funktioita tai niiden ominaisuuksia, joten funktioita kannattaa tutkia tarkemmin ohjelman opasteista.

Päivämäärä- ja aikafunktiot (engl. Date and Time)

Päivämäärä- ja aikafunktiot palauttavat erilaisia ajanmääreitä, joiden muoto riippuu solumuotoilusta.

• Luku 1 vastaa päivämäärää 01.01.1900.
• Luku 37291 vastaa päivämäärää 04.02.2002.
• Luku 1/24 vastaa aikaa1:00:00 eli yhtä tuntia.
• Luku 0,5 vastaa aikaa12:00:00 eli kahtatoista tuntia.
• Laskutoimitus =1/(60*60*24) vastaa aikaa00:00:01 eli yhtä sekuntia.

Seuraavassa muutamia hyödyllisiä päivämäärä- ja aikafunktioita:

• DATE(year,month,day) - muodostaa päivämäärän annetuista luvuista.
• DATEDIF(start_date,end_date,unit) - vuosien, kuukausien tai päivien lukumäärä kahden päivämäärän välillä.
• DAY(serial_number), MONTH, YEAR, HOUR, MINUTE, SECOND - poimitaan päivä, kuukausi tai vuosi annetusta luvusta tai päivämäärästä.
• NOW() - antaa järjestelmän tämän hetkisen ajan ja päivämäärän.
• TODAY() - antaa järjestelmän tämän hetkisen päivän päivämäärän.
• WEEKDAY(serial_number,return_type) - antaa päivämäärän järjestysnumeron. Oletuksena sunnuntai on 1.

  =WEEKDAY(DATEVALUE("04.02.2002"))

Erikoisfunktiot (engl. Information)

Erikoisfunktioilla voidaan tutkia funktion palauttamia arvoja.

• CELL(info_type,reference) - palauttaa solun sisältämiä tietoja.
• COUNTBLANK(range) - lasketaan tyhjien solujen lukumäärät.
• INFO(type_text) - palauttaa järjestelmään liittyviä tietoja.
• ISBLANK(value) - voidaan testata solujen tyhjyyttä.
• Muiden IS-funktioiden avulla voidaan testata solujen sisällön muotoa, parillisuutta, totuusarvoja ja virhearvoja.
• N(value) - palauttaa annetun arvon konvertoituna numeeriseksi arvoksi.
• TYPE(value) - voidaan tutkia esimerkiksi solun sisältöä ja toimia sisällön mukaan.

Seuraavassa muutama esimerkki erikoisfunktioiden käytöstä:

=IF( ISNA(VLOOKUP(B4;valuutat;3;FALSE) );"Väärä valuuttatunnus";VLOOKUP(B4;valuutat;3;FALSE))

=IF( ISERROR(Paljonko/Mista*Mihin);"-";Paljonko/Mista*Mihin)

Matemaattiset ja trigonometriset funktiot (engl. Math & Trig)

Matemaattiset ja trigonometriset funktiot sopivat erilaisen matemaattisten laskutoimitusten tekemiseen.

• ABS(number) - palauttaa luvun itseisarvon.
• SIN(number), COS, TAN, ASIN, ACOS, ATAN - käytetään erilaisiin trigonometrisiin laskutoimituksiin.
• CEILING(number,significance) - palauttaa ylöspäin pyöristetyn numeron.
• COUNTIF(range,criteria) - mahdollistaa ehdollisen solujen laskemisen (tilastollinen).
• FACT(number) - palauttaa luvun kertoman.
• FLOOR(number,significance) - palauttaa alapäin pyöristetyn luvun.
• INT(number) - palauttaa kokonaisluvuksi alaspäin pyöristetyn luvun.
• MOD(number,divisor) - palauttaa lukujen jakolaskujen jakojäännöksen.
• PI() - palauttaa piin 15 desimaalisen likiarvon.
• POWER(number,power) - palauttaa lukujen potenssiin korotuksen.
• PRODUCT(number1,number2, ...) - palauttaa annettujen lukujen tulon.
• RAND( ) - palautaa satunnaisluvun.
• ROUND(number,num_digits) - pyöristetään luku haluttuun tarkkuuteen.
• SQRT(number) - palauttaa luvun neliöjuuren.
• SUBTOTAL(function_num,ref1,ref2,...) - palauttaa taulukon välisummat. Käytetään SUBTOTAL-työkalun kanssa.
• SUM(number1,number2, ...) - palauttaa annettujen lukujen summan.
• SUMIF(range,criteria,sum_range) - palauttaa annettujen lukujen ehdollisen summan. Ei sovellu useamman ehdon mukaan tehtävään laskentaa.
• SUMPRODUCT(array1,array2,array3, ...) - palauttaa useamman taulukon vastinsolujen tulon summan.
• TRUNC(number,num_digits) - palauttaa katkaistun kokonaisluvun.

=SUMIF(Tutkittava_alue; Etsittävä_arvo; Summattava_alue)

=SUMIF(Pilvisyys;E5;Lämpötila)

=IF(G6>0;SUMIF(Pilvisyys;E6;Lämpötila)/G6;0)

=COUNTIF(Etsittävä_alue; Etsittävä_arvo)

=COUNTIF(Pilvisyys;E5)

Tilastolliset funktiot (engl. Statistical)

Tilastollisilla funktioilla saadaan datasta tilastollisia tunnuslukuja. Tilastolliset funktiot ovat tarkoitettu erityisesti tilastollisten ongelmien ratkaisemiseen, joten seuraavaan on koottu ainoastaan tavallisen käyttäjän käyttöön soveltuvia funktioita.

• AVERAGE(number1,number2, ...) - palauttaa annettujen lukujen keskiarvon.
• COUNT(value1,value2, ...) - palauttaa lukuja sisältävien solujen lukumäärän.
• COUNTA(value1,value2, ...) - palauttaa epätyhjien solujen lukumäärän.
• MAX(number1,number2,...) - palauttaa annetuista arvoista suurimman.
• MIN(number1,number2,...) - palauttaa annetuista arvoista pienimmän.
• RANK(number,ref,order) - palauttaa luvun järjestyksen verrattuna muihin listan lukuihin.
• COUNTIF - ks. matemaattiset ja trigonometriset funktiot.

=AVERAGE(Lämpötila)

=MAX(Lämpötila)

=MIN(Lämpötila)

=RANK(Vertailtava_arvo; Koko_alue; Nouseva=1 tai Laskeva=0)

=RANK(B5;$B$3:$B$30;1)

Loogiset funktiot (engl. Logical)

Loogisilla funktioilla voidaan toteuttaa erilaisia ehtoja ja vertailla niiden toteutumista.

• AND(logical1,logical2, ...) - palauttaa arvon tosi, jos kaikki ehdot ovat tosia.
• FALSE( ) - palauttaa totuusarvon FALSE.
• IF(logical_test,value_if_true,value_if_false) - voidaan testata ehdon toteutumista.
• NOT(logical) - palauttaa totuusarvon vasta-arvon.
• OR(logical1,logical2,...) - palautaa arvon tosi, jos jokin ehdoista on tosi.
• TRUE( ) - palauttaa totuusarvon TRUE.

Molempien seuraavista ehdoista oltava tosia, niin sitten saadaan tosi.

=AND(Ehto1; Ehto2) 

Jos jokin seuraavista ehdoista on tosi, niin sitten saadaan tosi.

=OR(Ehto1; Ehto2)

=IF(AND(Suodatinpaperia; kahvia; vettä);KAHVINKEITIN(kahvia ; vettä ; suodatinpaperia);"Jotain puuttuu")
=IF(OR(mehua ; kaljaa ; maitoa ; vettä); "Jano sammuu"; "Janottaa edelleen")

Seuraavassa esimerkki opiskelijoiden harjoitustyömerkinnän tarkistamiseen.

=IF(OR(ISBLANK(J8);ISBLANK(K8));"Hylätty";VLOOKUP(I8;arvosanat;2;TRUE))

Hakufunktiot (engl. Lookup and Reference)

Hakufunktioilla voidaan hakea solualueesta osoitetta tai sisältöä.

• ADDRESS(row_num,column_num,abs_num,a1,sheet_text) - palauttaa soluosoitteen, joka luodaan numeerisista arvoista.
• AREAS(reference) - palauttaa solualueiden lukumäärän viittauksessaan.
• COLUMN(reference) - palauttaa sarakkeen järjestysnumeron.
• COLUMNS(array) - palauttaa alueen solujen lukumäärän.
• HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) - voidaan hakea erillisestä taulukosta hakuarvoa vastaava asia. Vertaillaan ensimmäisen rivin tietoihin.
• INDEX(array,row_num,column_num) - palauttaa taulukosta halutun kohdan.
• INDIRECT(ref_text,a1) - palauttaa soluviittauksen osoittaman solun sisällön. Voidaan siis muodostaa soluviittaus erillisten tietojen perusteella.
• MATCH(lookup_value,lookup_array,match_type) - palauttaa halutun arvon sijainnin taulukossa.
• OFFSET(reference,rows,cols,height,width) - palauttaa arvoja (esim. summan) solualueen suhteen määritellyltä alueelta.
• ROW(reference) - palauttaa viittauksen rivinumeron.
• ROWS(array) - palauttaa viittauksen rivien lukumäärän.
• VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) - voidaan hakea erillisestä taulukosta hakuarvoa vastaava asia. Vertaillaan ensimmäisen sarakkeen tietoihin.

=INDIRECT("a"& C16)

Muita esimerkkejä VLOOKUP-funktion käytöstä voi katsoa edellisen luennon esimerkeistä.

Rahoitusfunktiot (engl. Financial)

Rahoitusfunktiot on tarkoitettu erilaisen lainojen ja sijoitusten arvojen laskemiseen. Seuraavassa on esitelty ainoastaan muutamia normaalin käyttäjän tarvitsemia rahoitusfunktioita.

• FV(rate,nper,pmt,pv,type) - palauttaa sijoituksen tulevaisuuden arvon.
• IPMT(rate,per,nper,pv,fv,type) - palauttaa lainasta tai sijoituksesta kertyvän kokonaiskoron.
• NPER(rate, pmt, pv, fv, type) - palauttaa lainan maksuerien lukumäärän.
• PMT(rate,nper,pv,fv,type) - palauttaa tasaeräisen lainan maksuerän suuruuden.

Tekstifunktiot (engl. Text)

Tekstifunktiot on tarkoitettu erilaisiin merkkijono operaatioihin.

• CHAR(number) - palauttaa lukua vastaavaa ASCII-taulukon arvon.
• CLEAN(text) - poistaa kaikki tulostumattomat merkit tekstistä.
• CODE(text) - palauttaa merkkiä vastaavan ASCII-taulukon numeerisen arvon.
• CONCATENATE (text1,text2,...) - liittää yhteen merkkijonoja (&-merkki).
• EXACT(text1,text2) - tarkistaa onko kaksi tekstiä samoja.
• FIND(find_text,within_text,start_num) - palauttaa merkkijonon esiintymän paikan toisessa merkkijonossa.
• LEFT(text,num_chars) - palauttaa tietyn määrän merkkejä merkkijonon alusta.
• LEN(text) - palauttaa merkkijonon merkkien lukumäärän.
• LOWER(text) - muuttaa tekstin pieniksi kirjaimiksi.
• MID(text,start_num,num_chars) - palauttaa merkkijonosta halutun määrän merkkejä halutusta kohdasta.
• REPLACE(old_text,start_num,num_chars,new_text) - korvaa merkkijonon toisella.
• RIGHT(text,num_chars) - palauttaa halutun määrän merkkejä merkkijonon lopusta.
• SEARCH(find_text,within_text,start_num - palauttaa merkkijonon esiintymän toisessa merkkijonossa. (Isoilla ja pienillä kirjaimilla ei ole väliä.)
• TEXT(value,format_text) - voidaan muotoilla numeerinen arvo tekstiksi.
• TRIM(text) - poistaa ylimääräisen tyhjän tilan merkkijonosta.
• UPPER(text) - palauttaa annetun tekstin isoina kirjaimina.
• VALUE(text) - muuttaa tekstiarvon numeeriseksi.

Tietokantafunktiot (engl. Database)

Tietokantafunktioita tutkitaan tarkemmin vasta luennolla 8.

Kolmiulotteinen taulukkolaskenta

Kolmiulotteinen taulukkolaskenta mahdollistaa laskemisen lomakkeiden läpi.

• Laskentakaava voidaan toteuttaa porautumaan lomakkeiden läpi.
• Voidaan laskea esimerkiksi kaikkien laskentataulujen solun B3 summa (=SUM(Sheet1:Sheet3!D1)).
• Erilaisten yhteenvetojen tekeminen helpottuu (esimerkiksi säätilataulukko).

Ryhmätila

Ryhmätilaksi kutsutaan tilaa, jossa useammalle lomakkeelle voidaan tehdä samanaikaisesti toimenpiteitä. Toiminto on erityisen hyödyllinen, jos halutaan tehdä useampia laskentataulukoita saman muotoisiksi. Seuraavassa lyhyt esimerkki ryhmätilan käytöstä:

1. CTRL- tai SHIFT-näppäimen avulla voidaan aktivoida useampia taulukkolaskentalomakkeita yhtä aikaa.
2. Yhteen laskentataulukkoon kirjoitettavat tiedot tai muotoilut menevät kaikille lomakkeille yhtä aikaa.
3. Ryhmätilan purkaminen tapahtuu napauttamalla ryhmätilan ulkopuolista laskentataulukkoa.
4. Jos kaikki laskentataulukot on valittu, niin minkä tahansa taulukon napauttaminen purkaa ryhmätilan.