Taulukkolaskentatyökaluja - Demo 7 (Office 2007)

Seuraavissa tehtävissä tutustutaan muutamiin uusiin taulukkolaskentatyökaluihin. Lajittelun, suodatuksen ja välisummien käyttöön tutustuttiin jo edellisissä demoissa. Tehtäviin saat apua kurssin luentomonisteen sähköisestä versiosta.

Excel 2003 -versio demotehtävistä

Mallivideot

Mallivideot on tehty vanhemmalla Excelillä.

Ongelmia videon katselussa?

Ristiintaulukointi Pivot-työkalulla

Avaa tehtävien pohjaksi edellisten demojen mallivastaustyökirja saatila_demo2.xls.

Tee Pivot-taulukon raportti seuraavien lyhyiden ohjeiden mukaisesti.

  1. Aloita Pivot-taulukon raportin tekeminen valikkokomennolla Insert-välilehdeltä kohta "PivotTable".
  2. Valitse tietoalueeksi kaikki tietoja sisältävät sarakkeet (A:H) ja taulukon sijoituspaikaksi uusi laskentataulukko.
    Valitse alueeksi sarakkeet A:H
  3. Seuraavaksi raporttiin tulee raahata tietoja sarake-, rivi- ja datakentiksi.
    Pivot
  4. Raahaa hiirellä Sää-sarake rivikentäksi (engl. Row Field).
    Sää-kenttä raahattuna rivikentäksi
  5. Raahaa hiirellä Lämpötila-sarake tieto-osaksi (engl. Data Items).
    Lämpötila tietoosaan raahattuna
  6. Oletuksena lasketaan säätilatietoja vastaavien päivien lämpötilojen summa ("Sum of Lämpötila"). Vaihtoehtoisesti Excel voi laskea oletuksena myös päivien lukumäärän ("Count of Lämpötila").
  7. Tuplanapauta hiirellä Sum of Lämpötila -solua ja muuta avautuvasta ikkunasta käytettäväksi funktioksi keskiarvon laskenta (engl. Average). Tarkista pitääkö edellisissä demoissa lasketut tiedot edelleen paikkaansa.
    Laske keskiarvo (AVG)

Pivot-raportin muokkaus

Muuta edellä tekemääsi raporttia seuraavien vaatimuksien mukaisiksi:

  1. Mieti miten saisit lisättyä samaan raporttiin sekä keskiarvolämpötilat että päivien lukumäärät? Kokeile toteuttaa ne samaan raporttiin! Voit suorittaa vapaasti erilaisia kokeiluja, koska kentät voit aina raahata pois Pivot-taulukosta.
  2. Lisää vielä samaan raporttiin minini- ja maksimilämpötilojen laskenta. Raporttiin tulevien kenttien laskutoimituksia pystyt muuttamaan napsauttamalla kenttäotsikkoa (esim. Max of Lämpötila) hiiren oikealla ja valitsemalla Value Field Settings -komennon. Pääset muuttamaan laskutoimituksia myös käyttämällä työkalurivin nappia "Field Settings".
  3. Kokeile muuttaa kaavion englannin- tai suomenkielisiä tietoja havainnollisemmiksi.
  4. Muuta myös laskettujen lukujen esitys kaksidesimaaliseksi. Kokeile mistä kohtaa napauttamalla saat aktivoitua kaikki data-alueen tiedot. Muuttaminen onnistuu hiiren oikean painikkeen avulla!
  5. Piilota näkyvistä tyhjien solujen laskentaan liittyvät tiedot muuttamalla sää-kentässä näytettäviä tietoja. Jos otit alussa vain tietoja sisältävät alueet, niin sinulla ei välttämättä ole tyhjiä soluja laskennassa.
  6. Tallenna tiedosto nimellä saatilademo3.xls

Pivot-harjoituksia

  1. Tee uusi Pivot-raportti uudelle laskentataulukolle. Käytä pohjana samoja tietoja kuin edellisessäkin Pivot-raportissa.
  2. Tee raportti kuukausien keskilämpötiloista. Kuukausitiedoista kannattaa poistaa Blank-vaihtoehto.
  3. Kokeile muokata raportin ulkoasua Design -välilehdeltä löytyvällä Report Layout -painikkeella.
  4. Lisää sarakekenttiin (Column Fields) vielä Sää-kenttä. Nyt saat ristiintaulukoidun raportin keskilämpötiloista ryhmiteltynä eri kuukausien ja säätilatyyppien (esim. Aurinkoinen) suhteen.
  5. Tee uusi ristiintaulukoitu raportti päivien jakautumisesta erilaisten tuulensuuntien (esim. Pohjoinen) ja säätilatyyppien mukaan.
  6. Määrittele tämän jälkeen laskentataulukon yläreunasta löytyväksi sivukentäksi (engl. Page Field) kuukausitieto. Miten saat nyt näkyville ainoastaan tammikuun päivien tuulen suunnat ja säätilatiedot?
  7. Mitä tapahtuu kun lisäät vielä rivikentiksi tuulennopeuden?

Pivot-kaavio

Pivot-kaavio

  1. Tehdään Pivot-kaavio, jossa näkyy yllä olevan kuvan mukaisesti palkkeina kunkin kuukauden suhteelliset säätyyppiosuudet. Pivot-kaavion voit luoda kuten edellä, mutta paina Insert-välilehdellä PivotTable-napin alla olevaa pientä kolmiota (pudotusvalikko) ja valitse siitä PivotChart.
  2. Valitse Säätilataulukon sarakkeet (A:H). Älä pohjaa raporttia edellisiin Pivot-taulukkoihin sillä kaavion muokkaaminen muuttaa edellisiä raportteja.
  3. Luo raportti uudelle välilehdelle.
  4. Tietokenttien raahaaminen on helppoa tehdä oikean reunan paneelissa. Raahaa tietokenttälistasta Sää-kenttä alas Legend Fields (Series) -ikkunaan ja Kuukausi-kenttä Axis Fields (Categories) -ikkunaan. Values-ikkunaan voit raahata esimerkiksi Päiväys-kentän. Kentällä ei ole kuitenkin merkitystä sillä laskemme ainoastaan lukumääriä (Count of).
    Kenttien raahaaminen kaavioon
  5. Sulje Pivot-kaaviotyökalu klikkaamalla laskentataulukolle. Pivot-kaavion vieressä on taulukko, jonka pohjalta kaavio syntyy. Poista taulukosta kuukausien kohdalta "Blank", jotta se ei näkyisi kaaviossa. Saat sen poistettua klikkaamalla taulukosta Kuukausi-solun pudotusvalikkoa ja ottamalla rastin pois kohdan "Blank" edestä.
  6. Nyt meillä on erilaiset säätyypit kuukausittain palkkeina. Haluamme kuitenkin suhteelliset osuudet, koska päiviä voi olla kuukaudessa eri määrä. Tuplanapauta taulukon yläkulman laskentatoimitusta (Count of Päiväys). Mene Show values as -välilehdelle ja valitse tietojen näyttötavaksi % of row.
    Arvot prosenteiksi
  7. Muokkaa vielä Y-akselia siten, että 100% on isoin arvo. Klikkaa kaavion Y-akselia hiiren oikealla napilla ja valitse ponnahdusvalikosta valinta Format Axis. Valitse Axis options -kohdasta Maximum arvoksi Fixed: 1. Määritä vielä Number-kohdasta desimaalien määräksi 0.
  8. Tallenna tiedosto.

Erikoissuodatus

  1. Lisää työkirjaan uusi laskentataulukko (engl. Sheet) ja anna laskentataulukolle nimeksi Ehtoalueet. Laskentatulukolle tullaan seuraavissa tehtävissä määrittelemään erikoisuodatuksen (engl. Advanced Filter) tarvitsemia ehtoalueita.
  2. Kopioi Säätilataulukon otsikkorivin ja yhden tietorivin tiedot Ehtoalueet-laskentataulukon yläreunaan.
  3. Tyhjennä (tuhoa) säätilatietoja sisältävän rivin tiedot siten, että näkyville jäävät vain otsikkotiedot. Otsikoiden alapuolisiin soluihin jää ainoastaan kelpoisuustarkistukset, joten meillä on edelleen käytössä kätevät listat säätilatietojen käyttöön. Tietoja täyttäessäsi kelpoisuustarkistuksista aiheutuu ylimääräisiä kyselyjä, jotka eivät kuitenkaan estä tietojen lisäämistä.
    ehtoalue
  4. Valitse (tai kirjoita) ehtoalueelle eli Ehtoalueet-laskentataulukon "toiselle riville" säätilatiedoksi Sää-sarakkeeseen Aurinkoinen ja suodata Säätilataulukon tietoja seuraavan ohjeen mukaisesti:
    • Aktivoi Säätilataulukko eli valitse jokin taulukon solu aktiiviseksi.
    • Aloita suodattaminen valitsemalla Data-välilehdeltä kohdasta "Sort & Filter" valinta Advanced.
      Advanced filter
    • Valitse suodatettavaksi listaksi (engl. List range) Säätilataulukon kaikki tietosarakkeet (A:H).
    • Valitse ehtoalueeksi (engl. Criteria Range) edellä määrittämäsi ehtoalue (otsikkorivi ja sen alla oleva rivi).
    • Hyväksy suodatusehdot OK-painikkeella. Näkyville pitäisi tulla kaikki aurinkoisten päivien säätilatiedot.
    • Kaikki tiedot saat näkyville valikkokomennolla Data-välilehdeltä samasta kohdasta valinta Clear.

Erikoissuodatustehtäviä

Määrittele seuraavat suodatusehdot ehtoalueelle ja varmista suodatuksen toiminta tutkimalla suodatuksesta tulleita vastauksia. Voit halutessasi nimetä ehtoalueen ja säätilasarakkeet, jolloin alueita ei tarvitse erikseen valita työkalua käytettäessä.

  1. Suodata näkyville päivät, jolloin tuulee pohjoisesta ja on aurinkoista.
  2. Suodata näkyville päivät, joiden lämpötila on suurempi tai yhtäsuuri kuin -10 astetta.
  3. Suodata näkyville päivät, joiden lämpötila on suurempi tai yhtäsuuri kuin -10 astetta ja sää on lumisateinen.
  4. Suodata näkyville kaikki tammikuun päivien tiedot eli päivät, joiden päivämäärä on suurempi tai yhtäsuuri kuin 1.1.2003 ja joiden päivämäärä pienempi tai yhtäsuuri kuin 31.1.2003 Tätä varten joudut lisäämään ehtoalueelle uuden sarakkeet, johon kopioit Päiväys-otsikon.
  5. Suodata näkyville kaikki päivät, joiden säätila on lumisade tai pilvinen. Nyt ehtoalueeseen on "lisättävä" ehtoja varten yksi rivi lisää, koska TAI-ehdot on määriteltävä eri riveille.
  6. Suodata näkyville ne tammikuun päivät, joiden lämpötila on suurempi tai yhtäsuuri kuin -10 astetta tai joiden lämpötila on pienempi tai yhtäsuuri kuin -20 astetta. Tarkista, että suodatus onnistui.

Tietokantafunktiot (luettelofunktiot)

Tietokantafunktioilla (engl. Database) lasketaan luettelomuodossa olevista tiedoista erilaisia tilastollisia tunnuslukuja ehtojen perusteella. Ehdot annetaan erikoissuodatuksen mukaisilla ehtoalueilla. Tarkemmin tietokantafunktioiden toimintaan voit perehtyä luentomonisteen sähkoisen version avulla.

Kaikki seuraavat asiat on hyvin helppoa laskea myös SQL:n avulla.

  1. Laske aurinkoisten päivien keskiarvolämpötila DAVERAGE (suom. TKESKIARVO) -funktiolla.
  2. Laske sellaisten päivien keskiarvolämpötila, jolloin tuulee pohjoisesta ja on aurinkoista.
  3. Laske sellaisten päivien lukumäärä, joiden lämpötila on suurempi tai yhtäsuuri kuin -10 astetta (DCOUNT (suom. TLASKE)).
  4. Laske sellaisten päivien lukumäärän, joiden lämpötila on suurempi tai yhtäsuuri kuin -10 astetta ja sää on lumisateinen.
  5. Laske kaikkien tammikuun päivien keskiarvolämpötila.
  6. Laske maksimilämpötila sellaisilta päiviltä, joiden säätila on lumisateinen tai aurinkoinen (DMAX (suom. TMAKS)).
  7. Laske kaikkien niiden tammikuun päivien lukumäärä, joiden lämpötila on suurempi tai yhtäsuuri kuin -10 astetta tai joiden lämpötila on pienempi tai yhtäsuuri kuin -20 astetta.

Lisätehtäviä

Makrot

Nauhoita erikoissuodatuksen tekevä makro seuraavan ohjeen mukaisesti.

  1. Valmistele erikoissuodatuksen ehtoalue sellaiseksi, että haluat käyttää sitä makrossa. Voit ottaa esimerkiksi käyttöösi yhdellä rivillä olevat ehdot aluksi.
  2. Aloita makron nauhoittaminen avaamalla View-välilehdellä oleva Macros-pudotusvalikko (pieni kolmio) ja valitsemalla sieltä vaihtoehto Record Macro.
  3. Määrittele makrolle hyvä nimi ja määrittele halutessasi muutkin makron yleiset tiedot.
  4. Nyt nauhoitus on käynnissä, joten tee säätilatiedoille erikoissuodatus samalla tavoin kuin edellisissä tehtävissä.
  5. Kun suodatus on valmis, niin lopeta makron nauhoitus samasta paikasta kuin nauhoittamisen aloituskin painamalla Macro-välilehdeltä Stop recording.

Lisää makron käytön helpottamiseksi laskentataulukolle painike seuraavien ohjeiden mukaisesti.

  1. Painikkeen saat lisättyä painamalla Insert -välilehdeltä valintaa Shapes. Tästä voit valita painikkeesi ulkomuodon.
  2. Painikkeen lisääminen onnistuu valitsemalla haluamasi muodon, jolloin hiiren kohdistin muuttuu ja voit piirtää kuvan laskentataulukolle haluamaasi kohtaan.
  3. Makron lisääminen onnistuu painamalla kuviota hiiren oikealla napilla ja valitsemalla valikosta Assign Macro.
  4. Näkyville avautuu ikkuna, josta voit valita painikkeeseen yhdistettävän makron. Valitse makroksi edellä nauhoittamasi erikoissuodatusmakro.
  5. Lisää painikkeelle jokin teksti painamalla hiiren oikealla napilla ja valitsemalla Add text.
  6. Muokkaa hiiren oikean napin avulla painikkeen ominaisuuksia valitsemalla Size and properties ja määrittämällä Properties-välilehdellä painikkeen sijainti kiinteäksi. (engl. Don't move or size with cells). Tämän ominaisuuden avulla painike ei "häviä" suodatuksen yhteydessä.

Kokeile makron toimintaa muuttamalla suodatusehtoja. Jos makro ei toimi, niin tallenna työkirjasi ja valitse makrojen suojaustasoksi Disable all macros with notification. Makrojen suojaustason muuttaminen onnistuu Developer-välilehdeltä valinnalla Macro Security. Developer-välilehti ei välttämättä näy oletuksena, mutta saat sen kytkettyä käyttöön painamalla Office-nappia ja valitsemalla Excel options. Tämän jälkeen Popular-välilehdeltä löytyy vaihtoehto Show Developer tab in the Ribbon. Valitse se, jolloin välilehti ilmestyy palkkiin.

Avaa tämän jälkeen työkirjasi uudelleen käyttöön, jolloin ruudun yläreunaan pitäisi tulla ilmoitus, että makrot on poistettu käytöstä tämän dokumentin kohdalla. Valitse Options ja avautuvasta ikkunasta vaihtoehto Enable this content. Kokeile tämän jälkeen makron toimintaa!

Nauhoita makro myös kaikkien tietojen näyttämistä varten ja yhdistä se omaan painikkeeseen. Kokeile kummankin makron toimintaa muuttamalla ehtoalueen suodatusehtoja.

Tallenna tiedosto lopuksi.

Käyttäjien kommentit

Kommentoi tätä sivua Lisää uusi kommentti
Kurssimateriaalien käyttäminen kaupallisiin tarkoituksiin tai opetusmateriaalina ilman lupaa on ehdottomasti kielletty!
http://appro.mit.jyu.fi/tiedonhallinta/demot/demo7/
© Antti Ekonoja (antti.j.ekonoja@jyu.fi) <http://users.jyu.fi/~anjoekon/>
Tommi Lahtonen (tommi.j.lahtonen@jyu.fi) <http://hazor.iki.fi/>
Jukka Mäntylä (jmantyla@iki.fi) <http://www.iki.fi/jmantyla/>
2011-02-24 11:21:18
Informaatioteknologia - Jyväskylän yliopiston informaatioteknologian tiedekunta