Funktioita ja työkaluja - Luento 11

• Luentotaltiointi
• Pivot-taulukon raportti
• Tietojen lajitteleminen
• Välisummat
• Tietojen suodatus
• Kaavioiden ja työkalujen yhteiskäyttö
• Tietojen suojaaminen
• Oikeellisuustarkistus
• Tietokantafunktiot
• Erikoissuodatus
• Makrot
  • Makrojen käyttäminen
  • Makroviruksilta suojaantuminen

Tällä luennolla jatkamme excelin työkalujen parissa ja perehdymme lisäksi tietokantafunktioiden käyttöön. Luennolla käyty esimerkki löytyy luento10.xls-tiedostosta.

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

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

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

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

Tietokantafunktiot

Tietokantafunktioita olisi parempi nimittää luettelofunktioiksi. Tietokantafunktiot on tarkoitettu tehokkaaseen luettelomuotoisen datan laskemiseen. Tietokantafunktiot eroavat vastaavista tavallisista funktioista siten, että niille voidaan antaa erilaisia ehtoja laskennan tueksi. Seuraavassa muutamia funktioiden käytössä huomioitavia asioita.

Malli

• Ensimmäisenä parametrina määritellään tietokanta eli luettelo, jonka tietojen perusteella suoritetaan laskutoimituksia.
• Toisena parametrina annetaan sarakeotsikko, jonka määräämässä sarakkeessa laskutoimitus tehdään. Sarakeotsikko voidaan antaa tekstinä, sarakkeen järjestysnumerona (luettelon sisällä) tai soluviittauksena sarakeotsikkoon.
• Kolmantena parametrina annetaan ehtoalue, jonka ehtojen perusteella varsinainen laskenta suoritetaan. Ehtoalueelle asetetaan seuraavia rajoituksia:
  • Ehtoalueen ensimmäisellä rivillä on oltava samat otsikot kuin luettelossa, josta tietoja haetaan.
  • Kaikkia otsikoita ei tarvitse olla ehtoalueella, vaan ainoastaan niiden sarakkeiden otsikot, joiden mukaan laskentaa halutaan rajoittaa.
  • Samalla ehtorivillä olevien ehtojen on oltava voimassa yhtäaikaa samalla luettelon rivillä eli ehto on JA-tyyppinen.
  • Eri riveillä ja samaa saraketta rajoittavien ehtojen voimassa olo on TAI-tyyppinen.
  • Alueessa ei saa olla yhtään kokonaan tyhjää ehtoriviä.

Lisää tietokantafunktioista voit lukea luentomonisteen sähköisestä versiosta.

Erikoissuodatus

Erikoissuodatuksella saadaan annettua monipuolisia suodatusehtoja alueelle. Lisäksi pikasuodatuksesta poiketen tiedot voidaan myös kopioida toiseen laskentataulukkoon. Erikoissuodatuksesta kannattaa huomata muutama asia.

• Voidaan käyttää täsmälleen samanlaisia ehtoalueita kuin tietokantafunktioiden yhteydessä.
• Voidaan suodattaa paikallaan tai voidaan kopioida suodatetut tiedot jollekin toiselle laskentataulukolle.
• TAI-ehto määritellään useammalla rivillä.
• JA-ehto määritellään samalla rivillä, jolloin saman sarakkeen otsikoita voi olla useampiakin.
• Tietojen kopioinnin yhteydessä täytyy ensin mennä laskentataulukkoon, jonne tiedot tuodaan. Kopiointi onnistuu vain aktiiviselle laskentataulukolle.
• Tiedoista voidaan kopioida myös yksittäisiä sarakkeita. Tällöin määritellään haluttujen sarakkeiden otsikot kopioinnin kohteeseen.
• Erikoissuodatus sopii hyvin käytettäväksi esimerkiksi Pivot-taulukkoon tai muun työkalun käyttöön haluttavien tietojen "alkusuodattamisessa".

Makrot

Makrot ovat käteviä työkaluja erilaisten toistuvien toimenpiteiden automatisointiin. Makrojen avulla usein toistuva ja useita toimintoja vaativa toimenpide saadaan yhden napin painalluksen taakse. Seuraavassa muutama makrojen huomion arvoinen piirre.

• Makroja käytetään sekä tekstinkäsittelyssä, taulukkolaskennassa että esitysgrafiikassa.
• Makro on kokoelma erilaisia toimenpiteitä, jotka on koottu yhteen.
• Makrot koostuvat ohjelmointikielisistä (esimerkiksi VBA) komennoista.
• Makroja voidaan tehdä nauhoittamalla tai käsin kirjoittamalla.
  • Nauhoitetaessa makroon tallentuvat tiedot suorittamistamme toimenpiteistä, kuten esimerkiksi solujen valinnasta.
  • Yksinkertaisten makrojen tekemiseen ei siis tarvitse osata ohjelmoida.
  • Esimerkiksi taulukon suodattaminen voidaan nauhoittaa makroksi.
  • Monimutkaisemmat makrot joutuu kirjoittamaan käsin ainakin osittain.
  • Makroja käsin korjailemalla saadaan tehokkaita ja yleiskäyttöisiä työkaluja.
• Nauhoittaminen aloitetaan valikkokomennolla Tools | Macro | Record New Macro (suom. Työkalut | Makro | Nauhoita uusi makro), jonka jälkeen tehdään makroon tulevat toimenpiteet rauhassa.
• Nauhoittaminen loppuu Macro-työkalurivin Stop Recording (Lopeta nauhoitus)-painikkeella tai Tools | Macro (suom. Työkalut | Makro) valikon vastaavalla valinnalla.
• Makron nauhoitusnopeus on eri nopeus kuin ajonopeus.
• Makroilla voidaan myös tehdä omia funktioita.
• Makrojen nimiin ei saa laittaa välilyöntejä.

Makrojen käyttäminen

Makroja voidaan käyttää (suorittaa) kätevästi esimerkiksi laskentataulukolle lisätyn painikkeen avulla. Painikkeen lisääminen onnistuu seuraavasti:

• Välitse näkyville Forms- (suom. Lomakkeet) -työkalurivi valikosta View | Toolbars (suom. Näytä | Työkalurivit).
• Valitse työkalurivin työkaluista Button (suom. Painike). Oikeassa reunassa toinen ylhäältä.
• Raahaa hiiren vasen pohjassa laskentataulukkoon haluamasi painikkeen kokoinen alue.
• Valitse avautuvasta Assign Marco (suom. Yhdistä makro) -ikkunasta painikkeeseen haluttu makro.
• Anna painikkeelle kuvaava nimi hiiren oikean alta löytyvällä Edit Text (suom. Muokkaa tekstiä) -komennolla.
• Makroa voit nyt käyttää painiketta napsauttamalla.

Makroja voidaan käyttää myös valikkokomennolla Tools | Macro | Macros (suom. Työkalut | Makro | Makrot) avatuvasta ikkunasta. Ensin ajettava makro täytyy valita aktiiviseksi ja sen jälkeen sen voi ajaa Run-komennolla. Makroihin voi myös yhdistää pikanäppäimiä.

Makroviruksilta suojaantuminen

Vaikka nykyisellään makroviruksia on liikkeellä vähän, niin niiden kanssa kannattaa edelleen olla varovainen. Jos työkirja sisältää makroja, mutta niitä ei tiedä tarvitsevansa, niin niitä ei kannata ottaa käyttöön.

Makrosuojauksista johtuu, ettei edes hyödyllisiä makroja päästä ajamaan oletusasetuksilla. Tällöin kannattaa tarkistaa makrojen suojaustaso valikkokomennolla Tools | Macro | Security (suom. Työkalulut | Makro | Suojaus). Avautuvasta ikkunasta suojaustason saa näkyville Security Level (suom. Suojaustaso) -välilehdeltä. Makrojen ajamiseksi suojaustaso kannattaa laskea Medium (suom. Normaali)-tasolle, jolloin makrojen käyttö varmistetaan käyttäjältä asiakirjan avaamisen yhteydessä. Kun asetukset on hyväksytty, niin työkirja täytyy avata uudelleen, jolloin näkyville pitäisi tulla makroista aiheutuva kysely.