Taulukkolaskentafunktiot ja -kaaviot - Luento 9

• Luentotaltiointi
• Taulukkolaskentasovelluksen suunnittelu
• Solualueen ja työkirjan ulkoasun muokkaaminen
• Tietojen ja kaavojen uusiokäyttö
• Tyylit ja solumuotoilut
• Taulukkolaskentafunktiot ja niiden käyttö
  • SUM-funktio (suom. SUMMA)
  • AVERAGE-funktio (suom. KESKIARVO)
  • MIN-funktio (suom. MIN)
  • MAX-funktio (suom. MAKS)
• Tilastolliset funktiot (engl. Statistical)
• Päivämäärä- ja aikafunktioita (engl. Date and Time)
• Loogiset funktiot (engl. Logical)
  • IF-funktio (suom. JOS) esimerkkejä
• Sisäkkäiset funktiot
• Kaavioiden tekeminen
• Matemaattiset ja trigonometriset funktiot (engl. Math & Trig)

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.

Solualueen ja työkirjan ulkoasun muokkaaminen

• Sarakkeen leveyttä ja rivin korkeutta voidaan muuttaa rivin ja sarakkeen otsikoiden erottimien kohdalta (väliviiva) hiirellä raahaamalla.
• Jos solussa on risuaitamerkkejä (####), niin sisältö ei mahdu soluun näkyville.
• Rivejä ja sarakkeita voidaan lisätä valikkokomennolla Insert | Rows/Columns (Lisää | Rivejä/Sarakkeita).
  • Rivin lisäämisessä siirretään aktiivista riviä alaspäin.
  • Sarakkeen lisäämisessä siirretään aktiivista saraketta oikealle.
• Rivin tai sarakkeen poistaminen onnistuu valikkokomennolla Edit | Delete (Muokkaa | Poista).
  • Jos koko rivi tai sarake on valittuna, niin se poistetaan.
  • Jos yksittäinen solu(t) valittuna, niin poistamisen suunta varmistetaan.
• Laskentataulukoiden lisääminen onnistuu valikkokomennolla Insert | Sheet (Lisää | Laskentataulukko)
• Laskentataulukon poistaminen onnistuu valikkokomennolla Edit | Delete Sheet (Muokkaa | Poista laskentataulukko)
• Laskentataulukon nimeäminen onnistuu valikkokomennolla Format | Sheet | Rename (Muotoile | Laskentataulukko | Nimeä uudelleen) tai hiiren oikean painikkeen avulla.

Vihje: Hiiren kontekstivalikosta löytyvät kätevästi lisäämiseen ja poistamiseen tarvittavat komennot!

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 oikeassa alalaidassa.
• 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.

Tyylit ja solumuotoilut

• Tyyleistä saatavat hyödyt
  • Yhtenäiset ja rakenteelliset muotoilut jokaiselle laskentataulukolle.
  • Nopeat ulkoasun muutokset yhtenäisyyden vuoksi.
• Tyylien käyttäminen ja uusien tyylien tekeminen onnistuu valikkokomennolla Format | Style (Muotoile | Tyyli) avautuvasta ikkunasta.
• Tyylejä käytetään samanlaisia ominaisuuksia tarvitseviin soluihin.
• Valmiita tyylejäkin voi ja kannattaa muuttaa (esim. Normal-tyyli).
• Tyyleihin sisällytettävät ominaisuudet
  • Erilaiset solumuotoilut (engl. Number)(Yleinen, Luku, Valuutta, Laskenta, Päivämäärä, Aika, Prosentti, Murtoluku, Tieteellinen, Teksti, Erikois ja Omat)
  • Tasaus (engl. Alignment)
  • Kirjasimen ominaisuudet (engl. Font)
  • Solun rajat (engl. Border)
  • Taustakuviot ja värit (engl. Pattern)
  • Suojaus (engl. Protection)
• Tyylejä muuttaessa laskentataulukot eivät saa olla suojattuja.

Taulukkolaskentafunktiot ja niiden käyttö

• Funktioita käytetään erilaisten laskutoimitusten suorittamiseen.
• Funktio palauttaa arvon soluun, johon se on kirjoitettu. Arvo siis ilmestyy näkyville samaan soluun.
• Funktiolla ei voi suoraan muuttaa toisen solun sisältöä.
• Funktio aloitetaan yhtäsuuruus -merkillä (=), jota seuraa välittömästi funktion nimi.
• Funktion nimi on lyhenne vastaavasta toiminnosta. Esimerkiksi keskiarvon laskeva funktio on englanninkielisessä ohjelmassa AVERAGE ja suomenkielisessä ohjelmassa KESKIARVO
• Funktiolle annetaan parametreina asioita, jotka määrittelevät tarkemmin funktion toimintaa. Esimerkiksi

  =SUM(B4;B5;B6) tai =SUM(B4:B8)

• Parametrit erotetaan toisistaan puolipiste-merkeillä (;)
• Parametrien määrä, tyyppi ja järjestys määritellään funktion käyttöohjeessa.
• Funktioiden lisääminen kannattaa tehdä aluksi ohjatusti Insert|Function-valinnalla. Myöhemmin funktioiden kirjoittaminen käsin voi olla tehokkaampaa ja helpompaa.

Syntaksi

=FUNKTION_NIMI(parametri1 ; parametri2 ;...;parametriN)

Kuvitteellinen funktioesimerkki

=KAHVINKEITIN(kahvia ; vettä ; suodatinpaperia)

=MIKROAALTOUUNI(ruoka ; aika ; tehot)

SUM-funktio (suom. SUMMA)

Laskee annettujen solujen tai solualueen summan.

Syntaksi

=SUM(solualue)

Esimerkki

=SUM(E4:E8)
=SUM(kokonais)
=SUM(B22;B24;B26)

AVERAGE-funktio (suom. KESKIARVO)

Laskee keskiarvon annetulta solualueelta.

Syntaksi

=AVERAGE(solualue)

Esimerkki

=AVERAGE(lämpötila)

=AVERAGE(A1:A9)

MIN-funktio (suom. MIN)

Laskee annetun solualueen pienimmän arvon.

Syntaksi

=MIN(solualue)

Esimerkki

=MIN(lämpötila)
=MIN(C1:G1)

MAX-funktio (suom. MAKS)

Laskee annetun solualueen suurimman arvon.

Syntaksi

=MAX(solualue)

Esimerkki

=MAX(A2:A38)

=MAX(Päivämäärä)

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)

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.

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ä

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)

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

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

Jos on kahvia ja vettä ja suodatinpaperia niin laitetaan kahvinkeittimeen kahvia, vettä, suodatinpaperia muuten sanotaan että "Jotain puuttuu"

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

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

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ä")

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 myöhemmin.

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

Kaavioiden tekeminen

Kaaviot ovat hyvä työkalu

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

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.