Luento 8 - Tietokantafunktiot ja taulukkolaskentatyökaluja

Lyhyt kertaus edellisen luennon asioista

• Kaavioiden tekeminen numeerisesta datasta
• Laskentataulukon ja työkirjan suojaaminen
• Taulukkolaskennan perusidea

  

Tietokanta- eli luettelofunktiot

Tietokantafunktioiden nimitys on hieman harhaanjohtava, koska asialla ei ole mitään tekemistä oikeiden tietokantojen kanssa. Parempi nimitys onkin luettelofunktiot, koska funktiot on tarkoitettu ensisijaisesti luettelomuodossa olevien tietojen ehdolliseen laskemiseen. Luettelofunktiot soveltuvat hyvin erilaisiin laskutoimituksiin, joissa tarvitaan yhtä useampaa ehtoa. Tällöin ei siis voida käyttää SUMIF- tai COUNTIF-funktioita.

Tietokanta- eli luettelofunktioihin voi tutustua tarkemmin kurssin luentomonisteen esimerkkien avulla. Seuraavassa listassa luennolla tarkastellut funktiot:

• DAVERAGE - Keskiarvo
  • Pilvisyys on S
  • Pilvisyys S ja Sää R
  • Pilvisyys S ja Sää R ja Lämpötila <= -20
  • Lämpötila > -20 ja lämpötila < 2
  • Lämpötila < -20 tai lämpötila > 2
• DSUM - Summa
• DCOUNTA - Epätyhjien solujen lukumäärä
• DMAX - Maksimi
• DMIN - Minimi
• Tiedot voidaan kysyä eri soluissa - Esim. (=">"&H10), jolloin käyttäjän ei tarvitse osata kirjoittaa ehtoja.
• JA-ehtoja varten on lisättävä ehtoalueeseen yksi uusi sarake.
• TAI-ehtoja varten on lisättävä ehtoalueeseen yksi uusi rivi.

Taulukkolaskentatyökalut

• Tarkoitettu erityisesti luettelomuotoisen datan käsittelyyn, joten sovellusta suunnitellessa kyseinen rajoitus kannattaa ottaa huomioon.
  • Taulukon tiedot ovat riveittäin (tietueittain) eli yhdellä rivillä on ainoastaan yhteen asiaan liittyvät tiedot.
  • Luettelon ensimmäisenä rivinä on sarakeotsikot.
  • Yhteen sarakkeeseen (kenttään) tulee tietoja, jotka koskevat koko data-aineiston yhtä muuttujaa. Esimerkiksi säätilatietojen eri päivien lämpötilatiedot ovat kaikki samassa sarakkeessa.
  • Tietoalue, jolla työkaluilla käsiteltävät tiedot sijaitsevat kannattaa sijoittaa laskentalomakkeen ylälaitaan eli ensimmäiselle riville. Muutamien työkalujen käytössä erityisesti makrojen kanssa on vaikeuksia, jos tietoalue on sijoitettu muualle kuin ensimmäiselle riville.
• Työkaluilla on mahdollista aineistosta luoda helposti erilaisia koosteita tai suodatettuja tietotaulukoita.
• Muutamia esimerkkiratkaisuja:
  • Tulosten laskentasovellus kannattaa usein jakaa laskentataulukoiksi kilpailusarjojen mukaan, koska varsinaiset tulokset lasketaan sarjojen mukaan.
  • Tulojen ja menojen seurantasovellus kannattaa jakaa yleensä kahteen osaan, joissa kummassakin on saman muotoiset laskentataulukot.
  • Opiskelijoiden arvosanojen laskentasovellus voidaan jakaa joko tenttien mukaan tai jättää yhdeksi listaksi.

Solualueen järjestäminen

Solualueen järjestäminen eli lajittelu (engl. Sort) on käyttökelpoinen työkalu solualueen järjestämiseen jonkin tietyn sarakkeen (kentän) perusteella. Solualue voidaan järjestää kolmen eri sarakkeen perusteella joko laskevaan tai nousevaan aakkoselliseen järjestykseen.

Pikasuodatus

Pikasuodatus (engl. AutoFilter) on kätevä työkalu tietojen suodattamiseen jonkin ehdon perusteella. Työkalu soveltuu erityisesti tilanteisiin, joissa tiedoista halutaan näkyville vain jonkin ehdon täyttävät tiedot. Seuraavassa erilaisia mahdollisuuksia työkalun käyttöön:

• Suodatus tehdään taulukossa paikallaan piilottamalla ne rivit, jotka eivät ole valitun ehdon mukaisia.
• Suodatetaan näkyville jonkin sarakkeen (kentän) tietyn ehdon mukaiset rivit (tietueet). Esimerkiksi päivät, joiden lämpötila on täsmälleen -15 astetta.
• Suodatetaan näkyville tietyn ehdon mukaiset rivit (tietueet) useamman sarakkeen ehtojen perusteella. Esimerkiksi halutaan näkyville päivätä, jolloin lämpötila on -15 astetta ja päivä on ollut koko pilvinen (K).
• Suodatetaan näkyville jonkin sarakkeen (kentän) 1-10 suurinta tai pienintä arvoa sisältävät rivit (tietueet). Toinen mahdollisuus on suodattaa prosentuaalisesti suurimmat tai pienimmät arvot. Esimerkiksi näkyville halutaan kolme lämpimintä päivää. Näkyville voi tulla useampiakin päiviä, jos useampana päivänä on sama lämpötila. (TOP 10...)
• Suodatetaan näkyville jonkin sarakkeen (kentän) tietyn arvovälin sisältävät rivit (tietueet). Esimerkiksi päivät, jolloin lämpötila on suurempi kuin -23 ja pienempi kuin 5 astetta. (Custom)
• Suodatetaan näkyville jonkin sarakkeen (kentän) tiettyjen arvojen sisältävät rivit (tietueet). Esimerkiksi päivät, jolloin lämpotila on suurempi kuin 5 astetta tai pienempi kuin -23 astetta. (Custom)
• Samassa sarakkeessa (kentässä) voidaan käyttää maksimissaan kahta ehtoa Custom-valinnan avulla.
• Muutamia esimerkkiratkaisuja:
  • Tulosten laskentasovelluksessa voidaan esimerkiksi suodattaa näkyville tuloslistasta mitalistit.
  • Tulojen ja menojen seurantasovelluksessa voidaan esimerkiksi suodattaa yhden menoryhmän menot.
  • Opiskelijoiden arvosanojen laskentasovelluksessa voidaan suodattaa näkyviin hyväksytyt arvosanat.

Toinen mahdollisuus tietojen suodattamiseen on Erikoissuodatus (engl. Advanced Filter), joka on esitelty hieman myöhemmin.

Datalomake

Datalomaketta (engl. Form / DataForm) voidaan käyttää luettelomuodossa olevien tietojen lisäämiseen, selaamiseen, päivittämiseen tai poistamiseen. Datalomakkeesta tekee erityisen käyttökelpoisen automaattinen kaavojen kopioituminen lisätylle riville edelliseltä riviltä. Seuraavassa muutamia käyttökohteita ja rajoituksia:

• Ei päästä muuttamaan kaavoja sisältäviä soluja.
• Kopioi automaattisesti lisäyksen yhteydessä kaavarivit, joita tietoalueella sijaitsee.
• Ei lisätessä laajentaa nimettyä aluetta, joten nimetystä alueesta kannattaa tehdä koko sarakkeen pituinen.
• Tietoalueen on oltavat ensimmäisellä rivillä, jos työkalu käynnistetään makrosta.

Tarkemmat ohjeet työkalun käytöstä löytyy erillisestä ohjeesta.

Oikeellisuustarkistus

Oikeellisuustarkistustyökalulla (engl. Validation) voidaan tarkistaa käyttäjän soluun antama syöte.

• Voidaan tarkistaa syötteen muoto (Numeerinen, Päivämäärä, Teksti, yms.).
• Voidaan tarkistaa arvojen suuruus. Esimerkiksi päivämäärä voidaan rajoittaa ainoastaan tammikuun päivämääräksi tai lämpötila joihinkin järkeviin väleihin.
• Syöte voidaan valita olemassa olevasta alasvetovalikosta, joka muodostetaan jonkin valmiin listan perusteella. Tällöin kannattaa käyttää nimettyä aluetta (=nimetty_alue) ja huolehtia alueen laajenemisesta lisäysten yhteydessä.
• Käyttäjää voidaan informoida syötteestä jo solun aktivoinnin yhteydessä.
• Käyttäjää voidaan varoittaa väärästä syötteestä tai jopa estää väärän syötteen antaminen kokonaan.
• Muutamia esimerkkiratkaisuja:
  • Tulosten laskentasovelluksessa voidaan rajoittaa ajan syöttämistä.
  • Tulojen ja menojen seurantasovelluksessa voidaan rajoittaa meno- ja tuloerien syöttämistä.
  • Opiskelijoiden arvosanojen laskentasovelluksessa voidaan rajoittaa opiskelijoille syötettäviä pistemääriä.

Tarkemmat ohjeet työkalun käytöstä löytyy erillisestä ohjeesta.

Erikoissuodatus

Erikoissuodatusta (engl. Advanced Filter) voidaan käyttää suodatuksissa, joissa tarvitaan monipuolisia ehtoja. Erikoissuodatuksen ehdot määritellään vastaavalle ehtoalueelle kuin tietokanta- eli luettelofunktioiden ehtoalue. Seuraavassa muutamia käyttökohteita ja rajoitteita:

• Erikoissuodatuksen avulla voidaan suodattaa tiedot suoraan tietoalueeseen tai tiedot voidaan kopioida toiseen laskentataulukkoon.
• Suodatusehdot tehdään tietokanta- eli luettelofunktioiden tapaan erilliselle ehtoalueelle.
• Suodatettujen tietojen kopionti toiselle laskentataulukolle vaatii toimenpiteiden tekemistä tietyllä tavalla tietyssä järjestyksessä.
• Soveltuu esimerkiksi erilaisten kilpailusarjojen tietojen kopiointiin, mutta aiheuttaa vaikeuksia tietojen jatkokäsittelyyn.

Suodatus saadaan toimintaan valinnalla Data | Filter | Advanced Filter (suom. Tiedot | Suodatus | Erikoissuodata).

Kaikki luettelon rivit saadaan takaisin näkyville valinnalla Data | Filter | Show All (suom. Tiedot | Suodatus | Näytä kaikki)

Seuraavassa luennolla läpikäydyt ehdot:

• PVM on 10
• Pilvisyys S ja Sää R
• Pilvisyys S tai Sää R
• PVM > 2 ja PVM < 10
• PVM < 2 tai PVM > 10

Tarkemmat ohjeet työkalun käytöstä löytyy erillisestä ohjeesta.

Pivot-taulukon raportti

Pivot-taulukon raportti on erinomainen ristiintaulukointityökalu. Raporttiin voidaan luoda useammastakin taulukosta yhteenveto raportti. Soveltuu hyvin esimerkiksi tulo- ja mentotaulukon yhteenvetotaulukoksi.

• Jos tehdään kooste useammasta taulukosta, niin sarakkeet eli kentät kannattaa nimetä mahdollisimman yhden mukaisiksi.
• Pienoisia eroja eri ohjelmaversioiden toiminnassa.
• Taulukon eri osien ulkoasu on muutettavissa helposti.
• Taulukko on helposti päivitettävissä, joten alueeksi kannattaa valita kokonaiset sarakkeet.
• Muutamia esimerkkiratkaisuja:
  • Tulosten laskentasovelluksessa voidaan tehdä tuloslista kaikkien kilpailijoiden tuloksista.
  • Tulojen ja menojen seurantasovelluksessa voidaan tehdä kooste kaikista meno- ja tuloryhmittäin.
  • Opiskelijoiden arvosanojen laskentasovelluksessa voidaan tehdä "jonkinlainen" raportti kurssisuorituksista.

Raportin ohjattu tekeminen voidaan aloittaa valinnalla Tiedot | Pivot-taulun raportti (engl. Data | Pivot-table report).

Seuraavassa muutamia luentoesimerkkinä käytettyjä ristiintaulukointeja:

• Rivi: Pilvisyys - Data: Lämpötila - Funktio: Average
• Rivi: Pilvisyys - Sarake: Sää - Data: Lämpötila - Funktio: Average
• Rivi: Pilvisyys & Sää- Data: Lämpötila - Funktio: Average
• Rivi: Pilvisyys - Sarake: Sää - Data: Lämpötila - Funktio: Count
• Rivi: Lämpötila - Data: Pilvisyys - Funktio: Count - Sivu: Päivämäärä

Tarkemmat ohjeet työkalun käytöstä löytyy erillisestä ohjeesta.

Välisummat

Välisummat työkalun avulla saadaan nopeasti tehtyä luettelomuodossa olevista tiedoista välisummia, jonkin sarakkeen tietojen perusteella. Seuraavassa muutamia rajoituksia välisummien laskemiselle:

• Järjestä alue välisummat määräävän sarakkeen mukaan ennen välisummien laskemista.

Muutamia muita taulukkolaskentatyökaluja

• Tavoitteen haku (engl. Goal Seek) sopii yhden tuntemattoman muuttujan ratkaisemiseen. Joissakin tapauksissa arvo on helpompi laskea muiden lukujen perusteella.
• Ratkaisin (engl. Solver) sopii useamman tuntemattoman muuttujan ratkaisemiseen. Nopea tapa löytää useampaa soluarvoa muuttamalla optimaalinen ratkaisu reunaehtojen puitteissa.
• Skenaariot (engl. Scenarios) sopivat erilaisten tapausten vertailemiseen. Skenaarioon tallennetaan eri tapaukset ja ne voidaan haluttaessa ottaa käyttöön. Skenaarioista voidaan tehdä myös yhteenvetoraportteja.
• Data Analysis -työkaluilla analysoidaan tietoja tilastollisten menetelmien avulla.
• Consolidate -työkalulla voidaan yhdistää samanmuotoisia taulukoita ja laskea yhdistämisen yhteydessä haluttuja laskutoimituksia.

Makrojen perusteita

• Makroja käytetään tekstinkäsittelyssä, taulukkolaskennassa ja esitysgrafiikassa.
• Makro on kokoelma erilaisia tekstinkäsittelyssä tai taulukkolaskennassa tehtäviä toimenpiteitä, jotka kootaan yhdeksi kokonaisuudeksi eli makroksi.
• Esimerkiksi taulukon suodattaminen voidaan nauhoittaa makroksi.
• Makrokielenä toimii esimerkiksi VBA tai ohjelman oma makrokieli.
• Makrojen nimiin ei välilyöntejä.
• Makrovirukset ja niiden leviäminen täytyy huomioida.