Taulukkolaskentatyökalut - Luento 3

• Luentotaltiointi
• Hakufunktiot (engl. Lookup and Reference)
• Erikoisfunktiot (engl. Information)
• Tekstifunktiot (engl. Text)
• Kaaviot
• Pivot-taulukon raportti
• Kaavioiden ja työkalujen yhteiskäyttö
• Tietojen suojaaminen
• Oikeellisuustarkistus
• Tietojen lajitteleminen
• Välisummat
• Tietojen suodatus

Tällä luennolla tarkastellaan muutamaa erilaista funktioluokkaa sekä tehokkaita taulukkolaskentatyökaluja.

Esimerkkejä löytyy tiedostoista:

• luento3.xls - luentoesimerkit
• funktioesim.xls - funktioesimerkkejä

Hakufunktiot (engl. Lookup and Reference)

Hakufunktioilla voidaan hakea solualueesta solujen osoitetta tai sisältöä. Hakufunktiot ovat varsin toimivia, mutta yleensä niiden käyttöä kannattaa vältellä.

• ADDRESS(rivinro;sarakenro;viittauslaji;a1;taulukko_teksti) - (suom. OSOITE) - palauttaa soluosoitteen, joka luodaan numeerisista arvoista.
• AREAS(viittaus) - (suom. ALUEET) - palauttaa solualueiden lukumäärän viittauksessaan.
• CHOOSE(indeksi, vaihtoehto1, vaihtoehto2,...) - palauttaa halutun vaihtoehdon.
• COLUMN(viittaus) - (suom. SARAKE) - palauttaa sarakkeen järjestysnumeron.
• COLUMNS(solualu) - (suom. SARAKKEET) - palauttaa alueen solujen lukumäärän.
• HLOOKUP(hakuarvo;taulukko_matriisi;rivi_indeksi_nro;alue_haku) - (suom. VHAKU) voidaan hakea erillisestä taulukosta hakuarvoa vastaava asia. Vertaillaan ensimmäisen rivin tietoihin.
• INDEX(matriisi;rivinro;sarakenro) - (suom. INDEKSI) - palauttaa taulukosta halutun kohdan.
• INDIRECT(viittaus_teksti;totuusarvo) - (suom. EPÄSUORA) - palauttaa soluviittauksen osoittaman solun sisällön. Voidaan siis muodostaa soluviittaus erillisten tietojen perusteella.
• MATCH(hakuarvo;haku_matriisi;vastine_laji) - (suom. VASTINE) - palauttaa halutun arvon sijainnin taulukossa.
• ROW(viittaus) - (suom. RIVI) - palauttaa viittauksen rivinumeron.
• ROWS(solualue) - (suom. RIVIT) - palauttaa viittauksen rivien lukumäärän.
• VLOOKUP(hakuarvo;taulukko_matriisi;sar_indeksi_nro;alue_haku) - (suom. PHAKU) - voidaan hakea erillisestä taulukosta hakuarvoa vastaava asia. Vertaillaan ensimmäisen sarakkeen tietoihin.

=INDIRECT("a"& C16)
=VLOOKUP(B4;valuutat;3;FALSE)

Erikoisfunktiot (engl. Information)

Erikoisfunktioilla voidaan tutkia funktion palauttamia arvoja. Funktioilla on helppo testata erilaisia laskennassa tehtäviä virheitä.

• COUNTBLANK(solualue) - (suom. LASKE.TYHJÄ) -lasketaan tyhjien solujen lukumäärät.
• INFO(lajiteksti) - (suom. KUVAUS) - palauttaa järjestelmään liittyviä tietoja.
• ISBLANK(arvo) - (suom. ONTYHJÄ) - voidaan testata solujen tyhjyyttä.
• Muiden IS???? (suom. ON???) -funktioiden avulla voidaan testata solujen sisällön muotoa, parillisuutta, totuusarvoja ja virhearvoja.
• N(arvo) - palauttaa annetun arvon konvertoituna numeeriseksi arvoksi.
• TYPE(arvo) - (suom. TYYPPI) - 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)

Tekstifunktiot (engl. Text)

Tekstifunktiot on tarkoitettu erilaisiin merkkijono-operaatioihin. Jos tekstifunktioita joutuu käyttämään taulukkolaskennassa, niin kannattaa harkita työkalun tai tietojen järkevyyttä :-)

• CHAR(numero) - (suom. MERKKI) - palauttaa lukua vastaavaa ASCII-taulukon arvon.
• CLEAN(teksti) - (suom. SIIVOA) -poistaa kaikki tulostumattomat merkit tekstistä.
• CODE(teksti) - (suom. KOODI) - palauttaa merkkiä vastaavan ASCII-taulukon numeerisen arvon.
• CONCATENATE (teksti1;teksti2;...) - (suom. KETJUTA) - liittää yhteen merkkijonoja (&-merkki).
• EXACT(teksti1;teksti2) - (suom. VERTAA) -tarkistaa onko kaksi tekstiä samoja.
• FIND(etsittävä_teksti;tekstissä;aloitusnro) - (suom. ETSI) - palauttaa merkkijonon esiintymän paikan toisessa merkkijonossa.
• LEFT(teksti;merkit_luku) - (suom. VASEN) - palauttaa tietyn määrän merkkejä merkkijonon alusta.
• LEN(teksti) - (suom. PITUUS) - palauttaa merkkijonon merkkien lukumäärän.
• LOWER(teksti) - (suom. PIENET) - muuttaa tekstin pieniksi kirjaimiksi.
• MID(teksti;aloitusnro;merkit_luku) - (suom. POIMI.TEKSTIT) - palauttaa merkkijonosta halutun määrän merkkejä halutusta kohdasta.
• REPLACE(vanha_teksti;aloitusnro;merkit_luku;uusi_teksti) - (suom. KORVAA) - korvaa merkkijonon toisella.
• RIGHT(teksti;merkit_luku) - (suom. OIKEA) - palauttaa halutun määrän merkkejä merkkijonon lopusta.
• SEARCH(etsittävä_teksti;tekstissä;aloitusnro) - (suom. KÄY.LÄPI) - palauttaa merkkijonon esiintymän toisessa merkkijonossa. (Isoilla ja pienillä kirjaimilla ei ole väliä.)
• TEXT(arvo;muoto_teksti) - (suom. TEKSTI) - voidaan muotoilla numeerinen arvo tekstiksi.
• TRIM(teksti) - poistaa ylimääräisen tyhjän tilan merkkijonosta.
• UPPER(teksti) - palauttaa annetun tekstin isoina kirjaimina.
• VALUE(teksti) - muuttaa tekstiarvon numeeriseksi.

Kaaviot

• X-akseli kuvaa kaavioissa (useimmiten) diskreettejä luokkia
  • Jos X-akseli ei ole tasavälinen, on käytettävä hajontadiagrammia (XY Scatter)
• Sarja kuvaa Y-arvoja.
  • Yhdestä sarjasta saadaan (useimmiten) kuhunkin luokkaan yksi Y-arvo.
  • Sarjoja voi olla useita, jolloin luokkiin tulee useita Y-arvoja (esim. useita palkkeja).

Erilaisia diagrammeja:

• Piirakka (sektoridiagrammi)
• Palkki
• Summapalkki
• Viivadiagrammi
• Hajonta

Pivot-taulukon raportti

Pivot-taulukon raportti (engl. Pivot-table report) on helppo työkalu erilaisten koosteraporttien ja ristiintaulukointien tekemiseen. Alussa raporttien tekeminen voi tuntua hieman hankalalle, mutta muutamilla (yksinkertaisten tietojen) kokeiluilla pääsee jo helposti sisään työkalun toimintaan. Seuraavaan on koottu tarkemmin raporttityökalun käyttöön liittyviä asioita.

• Työkalulla voidaan tehdä kooste useamman taulukon tiedoista.
  • Tietojen täytyy olla järkevästi yhdistettävissä.
  • Kenttien nimeäminen (otsikointi) oltava samanlainen.
• Taulukon eri osien ulkoasu on muutettavissa helposti.
• Alueeksi kannattaa valita kokonaiset sarakkeet.
  • Tietoja voidaan kätevästi lisätä taulukkoon.
  • Muutokset on helppo päivittää raporttiin.

Raportin ohjattu tekeminen voidaan aloittaa valinnalla Data | Pivot-table report (suom. Tiedot | Pivot-taulun raportti). Raportin tekeminen koostuu neljästä eri vaiheesta, joiden järjestyksessä on pienoisia eroja eri ohjelmaversioissa.

1. Valitaan tietojen lähde (esim. Excelin taulukko tai useammat taulukot) sekä tehdäänkö Pivot-taulukko vai -kaavio.
2. Lähdetietojen valinta, johon kannattaa ottaa kaikki tietosarakkeet.
3. Valitaan luodaanko taulu uudelle laskentataulukolle vai olemassa olevaan laskentataulukkoon.
4. Määritellään raporttiin tulevat kentät ja suoritettava laskenta.

Muutamia esimerkkejä säätilatiedoista tehtävistä raporteista.

• Lasketaan päivittäiset keskiarvot lämpötilatiedoista. Voidaan myös rajoittaa tarkastelu yhteen kaupunkiin kerrallaan.
• Lasketaan kaupungittain eri säätyyppiset päivät. Raportista on mahdollisuus tehdä "aliryhmitelty" tai ristiintaulukoitu versio.
• Kooste kaikkien kaupunkien kaikkien kuukauden päivien lämpötiloista.

Lisätietoja Pivot-taulukon raportin käytöstä voit lukea luentomonisteen alaluvusta "Pivot-taulukon raportti".

Kaavioiden ja työkalujen yhteiskäyttö

Kaavioiden automaattinen päivittyminen tietojen lisäyksien ja poistojen yhteydessä toimii hyvin. Jos taulukkoon lisättävä tieto ei ole datasarja vaan datarivi, niin lisäys näkyy välittömästi taulukossa. Myös taulukkoon määritellyt tietojen piilottamiset heijastuvat suoraan kaavioon. Tällöin esimerkiksi suodattaminen jättää kaavioon ainoastaan ne tiedot, jotka täsmäävät suodatusehtoihin. Tällä tavoin saa käteviä graafisia työkaluja esimerkiksi tietyn aikajakson lämpötilojen tarkasteluun.

• Suodatuksessa piilotettavat rivit piilotetaan myös kaaviosta.
• Pivot-taulukon raportista luotu kaavio tarjoaa "käyttöliittymän" kaavion tietojen rajoittamiseen. Kaavion tekeminen onnistuu yhdellä napinpainalluksella, mutta kaavion asetuksia joutuu usein muuttamaan.

Tietojen suojaaminen

Taulukkolaskennassa voidaan tehdä kolmen eritasoisia suojauksia.

1. Yksittäisten solujen suojaaminen. Oletuksena solut ovat suojattuja, mutta koska laskentataulukkoa ei ole suojattu pääsee soluja muuttamaan.
2. Laskentataulukon suojaaminen, jolloin suojattuja soluja ei pääse muuttamaan. Ainoastaan suojaamattomia soluja (otettu suojaus pois) päästään tällöin muuttamaan.
   • Laskentataulukosta kannattaa suojata kaavat ja muut sellaiset solut, joita käyttäjä ei saa päästä muuttamaan.
   • Laskentataulukon suojaaminen estää suojattujen solujen sisällön ja muotoilujen muuttamisen.
   • Suojaus on solujen oletusominaisuus. Soluista, joihin käyttäjä saa kirjoittaa, otetaan suojaus pois.
   Suojaus määritellään valikkokomennolla Tools | Protection | Protect Sheet (Työkalut | Suojaus | Suojaa laskentataulukko) avautuvasta ikkunasta.
3. Työkirjan suojaaminen, jolla voidaan estää rakenteen (laskentataulukoiden järjestys yms.) tai ikkunan asetusten muuttaminen. Suojaus estää laskentataulukoiden
   • nimeämisen,
   • järjestyksen vaihtamisen ja
   • tuhoamisen.
   Suojaus määritellään valikkokomennolla Tools | Protection | Protect Workbook (Työkalut | Suojaus | Suojaa työkirja) avautuvasta ikkunasta.

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.
  • Toisesta laskentataulukosta ei voi käsin valita aluetta.
  • Tällöin kannattaa käyttää nimettyä aluetta.
  • Alueen nimen voi suoraan antaa kaavamuodossa (=nimetty_alue)
  • Päästään eroon kirjoitusvirheitä.
• 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:
  • Lämpötilatiejen tarkistus esimerkiksi välille -50 ja 50 astetta.
  • Kaupunkitietojen hakeminen ennalta määrätystä listasta.
  • Tuulen suuntaan liittyvien tietojen hakeminen ennalta määrätystä listasta.
  • Rajoituksien asettaminen tuulen nopeudelle.
  • Päivämäärä kenttää voi syöttää ainoastaan päivämääriä.

Lisätietoja oikeellisuustarkistuksien käytöstä voit lukea luentomonisteen alaluvusta "Oikeellisuustarkistukset".

Tietojen lajitteleminen

Lajittelutyökalun saa käyttöön Data | Sort (Tiedot | Lajittele) -valikkokomennolla. Työkalulla voidaan kätevästi lajitella (engl. Sort) eli järjestää

• valittu solualue kolmen eri sarakkeen mukaiseen järjestykseen.
• jokaisen sarakkeen tiedot nousevaan (eng. ascending) eli pienimmästä suurimaan tai laskevaan (engl. descending) eli suurimmasta pienimpään järjestykseen.

Lisätietoja lajittelemisesta voit lukea luentomonisteen alaluvusta "Solualueen järjestäminen".

Välisummat

Välisummatyökalun (engl. Subtotals) avulla järjestetyistä tiedoista voidaan laskea yhteenvetotietoja muutamien perusfunktioiden avulla. Työkalulla voidaan laskea ryhmitellyistä tiedoista yhteenvetotietoja. Ennen työkalun käyttöä on olennaista järjestää (lajitella) tiedot järjestykseen sen sarakkeen mukaan, jonka perusteella ryhmittely tehdään. Esimerkiksi haluttaessa eri paikkakuntien päivän lämpötilasta saada "koko maan" keskiarvolämpötila kyseiselle päivälle, niin järjestäminen täytyy tällöin määritellä päivämäärän mukaan.

Työkalu saadaan käyntiin valikkokomennolla Data | Subtotals (suom. Tiedot | Välisummat) Työkalulla saadaan järjestetystä aineistosta seuraavia tietoja.

• Ryhmään kuuluvien tietojen keskiarvoja, summia, lukumääriä, minimejä, maksimeja sekä erilaisia hajontalukuja.
• Vasemman reunan painikkeilla voidaan rajoittaa tietojen näkymistä hieman tai tarkastella jotakin ryhmää erikseen.
• Samaan aineistoon voidaan määritellä useampia ryhmittelyjä ja useampia välisummalaskutoimituksia. Jokainen erilainen laskutoimitus on tällöin lisättävä erikseen. Ensin kannattaa lisätä ulompi (suurempi) välisumma ja sen jälkeen vasta pienempi kokonaisuus.

Tietojen suodatus

Tietoja voidaan suodattaa erilaisten ehtojen. Tällöin näkyville jätetään ainoastaan niitä tietoja, joihin annetut ehdot täsmäävät. Suodatus voidaan tehdä kahdelle eri työkalulla. Pikasuodatus (engl. AutoFilter) on yksinkertainen työkalu, mutta jossa voidaan antaa maksimissaan kaksi ehtoa saraketta kohden.

Erikoissuodatus (engl. Advanced Filter) on monipuolisempi ja hieman hankalampi työkalu käyttää, mutta antaa huomattavasti laajemmat suodatusmahdollisuudet. Erikoissuodatustyökalua käsitellään tarkemmin vasta seuraavalla luennolla.

Seuraavassa esitellään muutamia pikasuodatuksen tarjoamia mahdollisuuksia. Suodatustyökalun saa päälle valitsemalla ensin suodatettavan alueen aktiiviseksi ja käynnistämällä työkalun Data | Filter | AutoFilter (suom. Tiedot | Suodata | Pikasuodata) -valikkokomennolla. Suodattaminen tapahtuu sarakeotsikoissa olevien alasvetovalikoiden avulla. Kun kyseisen sarakkeen suodatus halutaan poistaa, niin alasvetovalikosta valitaan näkyville kaikki tiedot (engl. All).

• Suodattaminen yksittäisen sarakkeen arvon mukaan.
  • Esimerkiksi ainoastaan yhden päivän tiedot näkyville.
• Suodattaminen useamman sarakkeen mukaan, jolloin useammalle sarakkeelle annetaan jokin ehto. Toisella suodatusehdolla rajoitetaan ensimmäisellä ehdolla löydettyjä rivejä, joten ehtojen on oltava yhtä aikaa voimassa samalla rivillä (AND).
  • Esimerkiksi yhden kaupungin kaikki aurinkoiset päivät.
• Parhaiden tai huonoimpien (esim. 10 kappaletta) tietojen suodattaminen.
  • Parhaimpien tai huonoimpien lukumäärä voidaan valita vapaasti.
  • Esimerkiksi kolme lämpimintä tai kylmintä päivää.
  • Mahdollisuus myös prosentuaalisen paremmuuden suodattamiseen.
• Kahden samassa sarakkeessa olevan ehdon mukaan suodattaminen.
  • Mahdollistaa välien tai välien ulkopuolisten arvojen suodattamisen.
  • Mahdollistaa AND- ja OR-tyyppisen ehtojen sitomisen.
  • Esimerkiksi voidaan suodattaa näkyville -20 astetta suuremmat ja -10 astetta pienemmät lämpötilat.
  • Välien suodatuksessa voi tulla ongelmia, jos solut on muotoiltu poikkeuksellisesti. Esimerkiksi "10.0 C" tulkitaan suodatuksessa merkkijonona, vaikka "C"-kirjain olisi lisätty solumuotouilujen avulla luvun perään.
• Yhdistämällä erilaisia suodatusehtoja saadaan suurestakin taulukosta kätevästi näkyville haluttuja tietorivejä.

Lisätietoja suodattamisesta voit lukea luentomonisteen alaluvusta "Pikasuodatus".