Taulukkolaskentafunktiot ja -kaaviot - Luento 2

• Luentotaltiointi
• Taulukkolaskentasovelluksen suunnittelu
• Tilastolliset funktiot (engl. Statistical)
• Päivämäärä- ja aikafunktioita (engl. Date and Time)
• Loogiset funktiot (engl. Logical)
  • IF-funktio (suom. JOS) esimerkkejä
• Matemaattiset ja trigonometriset funktiot (engl. Math & Trig)
• Sisäkkäiset funktiot
• Tietojen ja kaavojen uusiokäyttö
• Kaavioiden tekeminen
  • Kaavion lisääminen
  • Kaavion muokkaaminen

Luennolla perehdytään muutamiin taulukkolaskennassa hyödyllisiin ominaisuuksiin, taulukkolaskentafunktioihin sekä kaavioihin. Lisää taulukkolaskentafunktioita tulee myöhemmillä luennoilla.

Taulukkolaskentasovelluksen suunnittelu

• Sovellus kannattaa yleensä jakaa vähintään kahteen pääosaan: Käsiteltävä tieto ja käsittely.
  • Tiedot ovat perusmuotoista tietoa yhdessä laskentataulukossa.
  • Käsittelyssä käytetään tietoja hyväksi, mutta lopputulokset "tallennetaan" tiedoista erilleen.
• Tiedot kerralla oikeaan muotoon.
  • Tiedot kannattaa (tuoda) sijoittaa riveittäin. Samalla rivillä kaikki samaan tapahtumaan liittyvä tiedot (esim. säätilasovellus).
  • Samassa sarakkeessa oltavat tiedot on oltava "samaa tyyppiä" (esim. lämpötiloja, päivämääriä) eli samassa järjestyksessä.
  • Hyvin riveittäin ja sarakkeittain järjestetty tieto on helppo jatkojalostaa työkalujen avulla.
• Taulukkolaskentaohjelmaan tuotavat tiedot oltava sisällöltään oikeaa tietoa oikeassa muodossa.
  • Esimerkiksi tietokantojen taulujen välisiä viiteindeksejä ei kannata pitää enää taulukkolaskennassa mukana.
  • Numerot numeroina ja teksti tekstinä. Laskennallisista kentistä kannattaa pudottaa mittayksiköt pois.
• Muokkaa koko sovelluksen ulkoasu tyylien avulla.
• Nimetyt alueet mahdollisimman laajoina käytössä
  • Helpotetaan muistamista ja kaavoja.
• Toteuta laskenta mahdollisimman helpolla tavalla.
  • Valmiita ja tehokkaita funktioita on olemassa todella paljon.
  • Käytä laskennassa hyväksesi kerran laskettuja arvoja.
  • Sijoita kaavojen vakioarvot soluihin eikä suoraan kaavoihin.
  • Käytä ylimääräisiä laskentasoluja oikeaan muotoon pääsemiseksi.
• Käytä työkaluja ongelmien ratkaisemiseksi
  • Järjestäminen ja suodattaminen.
  • Syöttäminen.
  • Havainnollistaminen.
  • Tarkistaminen.
  • Ristiintaulukointi ja raportointi.
  • Matemaattiset ongelmat.

Tilastolliset funktiot (engl. Statistical)

Tilastollisilla funktioilla saadaan datasta tilastollisia tunnuslukuja. Tilastolliset funktiot ovat tarkoitettu erityisesti tilastollisten ongelmien ratkaisemiseen. Seuraavaan on koottu ainoastaan muutamia yleisimpiä tilastollisia tuloslukuja laskevia funktioita.

• AVEDEV(solualue) - (suom. KESKIPOIKKEAMA) - laskee annetun solualueen hajontojen itseisarvojen keskiarvon (aineiston vaihtelun mittaaminen).
• AVERAGE(solualue) - (suom. KESKIARVO) - laskee annetun solualueen lukujen keskiarvon.
• AVERAGEA(solualue) - (suom. KESKIARVOA) - laskee annetun solualueen kaikkien arvojen keskiarvon (myös ei numeeriset).
• COUNT(solualue) - (suom. LASKE) - laskee lukuja sisältävien solujen lukumäärän.
• COUNTA(solualue) - (suom. LASKE.A) - laskee epätyhjien solujen lukumäärän.
• COUNTIF(solualue;ehto) - (suom. LASKE.JOS) - mahdollistaa ehdollisen solujen laskemisen (tilastollinen funktio).
• MAX(solualue) - (suom. MAKS) - laskee annetuista arvoista suurimman.
• MEDIAN(solualue) - (suom. MEDIAANI) - laskee annetun solualueen arvojen mediaanin eli keskimmäisen luvun (puolet pienempi ja puolet isompia).
• MIN(solualue) - (suom. MIN) - laskee annetuista arvoista pienimmän.
• MODE(solualue) - (suom. MOODI) - laskee annetulla solualueella useimmin esiintyvän arvon.
• RANK(solualue) - (suom. ARVON.MUKAAN) - laskee luvun järjestyksen verrattuna muihin listan lukuihin.
• STDEV(solualue) - (suom. KESKIHAJONTA) - laskee annetun solualueen arvojen keskihajonnan eli arvojen poikkeaman keskiarvosta.
• VAR(solualue) - (suom. VAR) - laskee annetun solualueen arvojen varianssin eli arvojen poikkeaman keskiarvosta.

=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)

Tarkemmat esimerkit löytyvät erillisestä luento2_malli.xls tiedostosta.

Päivämäärä- ja aikafunktioita (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 38811 vastaa päivämäärää 04.04.2006.
• Luku 1/24 vastaa aikaa 1:00:00 eli yhtä tuntia.
• Luku 0,5 vastaa aikaa 12: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(vuosi;kuukausi;päivä) - (suom. PÄIVÄYS) - muodostaa päivämäärän annetuista luvuista.
• DAY(numero), MONTH, YEAR, HOUR, MINUTE, SECOND - (suom. PÄIVÄ, KUUKAUSI, VUOSI, TUNNIT, MINUUTIT, SEKUNNIT ) - poimitaan päivä, kuukausi tai vuosi annetusta luvusta tai päivämäärästä.
• NOW() - (suom. NYT) - antaa järjestelmän tämän hetkisen ajan ja päivämäärän.
• TODAY() - (suom. TÄMÄ.PÄIVÄ) - antaa järjestelmän tämän hetkisen päivän päivämäärän.
• WEEKDAY(numero;tyyppi) - (suom. VIIKONPÄIVÄ) - antaa päivämäärän järjestysnumeron. Oletuksena sunnuntai on 1.

Tarkemmat esimerkit löytyvät erillisestä luento2_malli.xls tiedostosta.

Loogiset funktiot (engl. Logical)

Loogisilla funktioilla voidaan toteuttaa erilaisia ehtoja ja vertailla niiden toteutumista.

• AND(Ehto1; Ehto2; ...) - (suom. JA) - palauttaa arvon TOSI (TRUE), jos kaikki ehdot ovat TOSIa (TRUE).
• IF(Ehto; Jos_Ehto_Tosi; Jos_Ehto_Epätosi) - (suom. JOS) - voidaan testata ehdon toteutumista. Jos ensimmäisen parametrin Ehto on TOSI, niin tehdään toisen parametrin määrittämät asiat. Muussa tapauksessa tehdään kolmannen parametrin asiat.
• NOT(Ehto) - (suom. EI) - palauttaa totuusarvon vasta-arvon.
• OR(Ehto1; Ehto2; ...) - (suom. TAI) - palautaa arvon TOSI (TRUE), jos jokin ehdoista on TOSI (TRUE).

IF-funktio (suom. JOS) esimerkkejä

Kuvitteellinen funtioesimerkki, jossa mukana myös sisäkkäisiä funktioita.

=IF(AND(Suodatinpaperia; kahvia; vettä);
  KAHVINKEITIN(kahvia ; vettä ; suodatinpaperia);
	"Jotain puuttuu")

=IF(OR(mehua ; kaljaa ; maitoa ; vettä); "Jano sammuu"; "Janottaa edelleen")

Esimerkissä testataan onko A1 solussa oleva arvo suurempi kuin 10. Jos arvo on suurempi kuin 10, niin soluun kirjoitetaan teksti "suurempi kuin 10". Muussa tapauksessa soluun kirjoitetaan teksti "pienempi tai yhtäsuuri kuin 10".

=IF(A1>10 ; "suurempi kuin 10" ; "pienempi tai yhtäsuuri kuin 10")

Esimerkiksi jos kokonaisostokset ovat 500 EUR tai enemmän, niin annetaan 10 prosentin alennus (kirjoitetaan soluun 10%) , mutta muuten ei alennusta (kirjoitetaan soluun 0%).

=IF(E10>=500 ; 0,1 ; 0)

Matemaattiset ja trigonometriset funktiot (engl. Math & Trig)

Matemaattiset ja trigonometriset funktiot sopivat erilaisen matemaattisten laskutoimitusten tekemiseen.

• ABS(numero) - (suom. ITSEISARVO) - palauttaa luvun itseisarvon.
• SIN(numero), COS, TAN, ASIN, ACOS, ATAN - käytetään erilaisiin trigonometrisiin laskutoimituksiin.
• FACT(numero) - (suom. KERTOMA) - laskee luvun kertoman.
• INT(numero) - (suom. KOKONAISLUKU) - laskee kokonaisluvuksi alaspäin pyöristetyn luvun.
• MOD(numero;jakaja) - (suom. JAKOJ) - laskee lukujen jakolaskujen jakojäännöksen.
• PI() - (suom. PII) -palauttaa piin 15 desimaalisen likiarvon.
• POWER(numero;potenssi) - (suom. POTENSSI) - laskee lukujen potenssiin korotuksen.
• PRODUCT(solualue) - (suom. TULO) - laskee annettujen lukujen tulon.
• RAND() - (suom. SATUNNAISLUKU) - antaa satunnaisluvun.
• ROUND(numero;tarkkuus) - (suom. PYÖRISTÄ) - pyöristää luvun haluttuun tarkkuuteen.
• SQRT(numero) - (suom. NELIÖJUURI) - laskee luvun neliöjuuren.
• SUBTOTAL(funktio_nro;viittaus1;viittaus2;...) - (suom. VÄLISUMMA) - laskee taulukon välisummat. Käytetään SUBTOTAL-työkalun kanssa.
• SUM(solualue) - (suom. SUMMA) - laskee annettujen lukujen summan.
• SUMIF(ehtoalue;ehto;summa-alue) - (suom. SUMMA.JOS) - laskee annettujen lukujen ehdollisen summan. Ei sovellu useamman ehdon mukaan tehtävään laskentaa.
• TRUNC(numero;tarkkuus) - (suom. KATKAISE) - 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)

Muutamia käyttöesimerkkejä COUNTIF ja SUMIF-funktion käytöstä löytyy luento2_malli.xls -tiedostosta.

Sisäkkäiset funktiot

Sisäkkäiset funktiot mahdollistavat monipuolisen laskennan. Sisemmän funktion palauttamaa arvoa käytetään ulomman funktion parametrina. Peruslaskutoimitusten yhteydessä sisäkkäisiä funktioita tarvitaan harvemmin, koska laskutoimitukset saadaan tehtyä suoraan yhdellä funktiolla. Hieman monimutkaisemmissa tarpeissa ja erityisesti ehdollisessa laskennassa tarvitaan varmasti sisäkkäisiä funktioita.

Esimerkissä sisemmän MIKROAALTOUUNI-funktio palauttaa ruoka-annoksen. ATERIA-funktio vastaavasti koostaa aterian kyseisestä ruoka-annoksesta, ruokajuomasta ja leivästä.

=ATERIA( MIKROAALTOUUNI(ruoka ; aika ; tehot) ; maito ; leipä)

Seuraavassa esimerkissä käytetään IF-funktiota poistamaan mahdollinen nollalla jakamisyritys, josta luonnollisesti aiheutuu virhe. Funktion keskimmäisessä osassa tehdään laskutoimitus, jossa SUMIF-funktion saama arvo jaetaan solulla H14. Solussa ei siis saa olla nollaa. Ulommalla funktiolla testataan ensin solun sisältö ja vasta sen jälkeen suoritetaan mahdollinen laskenta.

=IF(H14>0;SUMIF(sää;G14;lämpötila)/H14; "Ei päiviä")

Esimerkin toteutusta voi katsoa tarkemmin erillisestä luento2_malli.xls tiedostosta.

Esimerkki muutamista sisäkkäisistä IF-funktioista. Jos kokonaishinta on suurempi tai yhtä suuri kuin 1000 mk, niin alennusta tulee 20 %, muutoin (jos kokonaisnaishinta on suurempi tai yhtä suuri kuin 500 mk niin alennus 10 %, muutoin (alennus on 0%)). Tieto alennusprosentista kirjoitetaan soluun, jossa kaava sijaitsee.

=IF(E10>1000 ; 0,2 ; IF(E10>500 ; 0,1 ; 0))

Sisäkkäisten funktioiden käyttöesimerkki, jossa testataan hakufunktioiden antamaan virheilmoitusta. Tarkempia esimerkkejä hakufunktioista tulee vasta myöhemmillä luennoilla.

=IF(ISNA(VLOOKUP($B$4;valuutat;3;FALSE));"väärä valuutta";VLOOKUP($B$4;valuutat;3;FALSE))

Tietojen ja kaavojen uusiokäyttö

Taulukkolaskennassa on muutamia perusominaisuuksia, joiden avulla vältytään ylimääräiseltä kirjoittamiselta.

• Pelkkien soluarvojen tai muotoilujen kopioiminen on mahdollista Paste Special (suom. Liitä määräten) -toiminnon avulla.
• Solualue voidaan nopeasti täyttää täyttötyökalujen avulla.
  • Täyttökahva solun vasemmassa alalaidassa.
  • Täyttötoiminto - Edit | Fill (suom. Muokkaa | Täytä).
  • Sarjatäyttö - Edit | Fill | Series (suom. Muokkaa | Täytä | Sarjat) .
• Solun tietojen puhdistaminen - Edit | Clear (suom. Muokkaa | Tyhjennä)).
• Ryhmätilan käyttö eli useampien laskentataulukoiden yhtäaikainen täyttö.
  • Useampia laskentataulukoita saadaan aktivoitua SHIFT- ja CTRL-näppäimen avulla.
  • Yhteen syötetyt tiedot menevät myös muille laskentataulukoille.
• Kolmiulotteinen laskenta mahdollistaa laskutoimitukset työkirjan laskentataulukoiden vastinsoluilla.

Kaavioiden tekeminen

Kaaviot ovat hyvä työkalu

• havainnollistamaan numeerista dataa.
• näyttämään numerisesta aineistosta poikkeamat.

Kaavion lisääminen

Kaavion lisääminen koostuu neljästä erilaisesta vaiheesta:

1. Kaavion tyyppi. Tyyppejä on valittavissa hyvin monenlaisia ja tyyppiä pystyy muuttamaan myös jälkikäteen.
2. Kaavion data-alue ja sarjat.
   • Data riveittäin tai sarakkeittain
   • Sarjat voidaan määrittää kokonaan käsin - Nimi, data-alue ja x-akselin arvoalue
3. Kaavion lisäominaisuuksien sekä erilaisten tekstien määrittäminen.
4. Kaavion paikka.
   • Uudelle lomakkeelle.
   • Objektina olemassa olevalle lomakkeelle.

Kaavion muokkaaminen

Jokaista kaavion ominaisuutta voidaan muuttaa jälkikäteen:

• Tietoihin tehtävät muutokset heijastuvat suoraan kaavioon.
• Uuden datasarjan lisääminen ei onnistu automaattisesti, mutta uusien "datarivien" lisääminen onnistuu.
• Lisäyksen yhteydessä määriteltyjä ominaisuuksia voidaan muuttaa Chart (suom. Kaavio)-valikon tai hiiren oikean avulla.
• Akseleiden, sarjojen, selitteen ulkoasua ja asetuksia voidaan muuttaa hiiren oikean painikkeen avulla. Ominaisuuksista voidaan muuttaa esimerkiksi seuraavia:
  • Kaavion akseleiden skaalausta.
  • Akseleiden arvojen muotoilut.