Taulukkolaskentatyökaluja - Demo 3

• Mallivideot
• Ristiintaulukointi Pivot-työkalulla
  • Pivot-raportin muokkaus
  • Pivot-harjoituksia
  • Pivot-kaavio
• Erikoissuodatus
  • Erikoissuodatustehtäviä
• Tietokantafunktiot (luettelofunktiot)
• Lisätehtäviä
  • Makrot

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. Mikäli käytät Office 2007 -versiota, siirry Excel 2007 -tehtäviin.

Ristiintaulukointi Pivot-työkalulla

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

Tee Pivot-taulukon raportti seuraavien lyhyiden ohjeiden mukaisesti. Tarkempia ohjeita löydät tarvitessasi luentomonisteen sähköisestä versiosta.

1. Aloita Pivot-taulukon raportin tekeminen valikkokomennolla Data | Pivot table and Pivot chart report (suom. Tiedot | Pivot-taulukko ja kaavio-raportti).
2. Valitse ensimmäisessä vaiheessa tietolähteeksi Excelin luettelo tai tietokanta ja tehtäväksi raportiksi Pivot-taulukko.
   
3. Toisessa vaiheessa kannattaa valita tietoalueeksi kaikki tietoja sisältävät sarakkeet (A:H).
   
4. Valitse kolmannessa vaiheessa raportin sijoituspaikaksi uusi laskentataulukko ja hyväksy raportti Finish (suom. Valmis)-painikkeella.
   
5. Viimeissä vaiheessa raporttiin pitää raahata tietoja sarake-, rivi- ja datakentiksi.
   
6. Raahaa hiirellä Sää-sarake rivikentäksi (engl. Row Field).
   
7. Raahaa hiirellä Lämpötila-sarake tieto-osaksi (engl. Data Items).
   
8. Oletuksena lasketaan säätilatietoja vastaavien päivien lukumäärä (tai lämpötilojen summa). Tarkista onko laskettujen päivien lukumäärät samoja kuin säätilalaskenta-laskentataulukossa (eli välilehdellä), joka toteutettiin edellisissä demoissa.
9. Tuplanapauta hiirellä Count of Lämpötila (suom. Laske: 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.
   

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 muuuttamaan napsauttamalla kenttäotsikkoa (esim. Max of Lämpötila) hiiren oikealla ja valitsemalla Field Settings (suom. Kentän asetukset) -komennon.
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 PivotTable (suom. Pivot-taulukko) -työkaluriviltä löytyvällä Format Report (suom. Muotoile raportti) -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

1. Tehdään Pivot-kaavio, jossa näkyy palkkina kunkin kuukauden suhteelliset säätyyppiosuudet. Valitse Pivot-kaavion luonti kuten edellä, mutta valitse nyt ensimmäisessä vaiheessa PivotChart Report.
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. Raahaa X-akselille (Category Fields) Kuukausi-kenttä ja Y-akselin sarjoiksi (Series Fields) Sää-kenttä. Data-kenttään voit laittaa esimerkiksi Päiväys-kentän. Kentällä ei ole kuitenkin merkitystä sillä laskemme ainoastaan summia (Count of).
5. Poista kuukausista Blank-vaihtoehto.
6. Nyt meillä on erilaiset säätyypit kuukausittain palkkeina. Haluamme kuitenkin suhteelliset osuudet, koska päiviä voi olla kuukaudessa eri määrä. Tuplanapauta vasemman yläkulman data-kenttien laskentatoimitusta ja ota lisävaihtoehdot esille.

   

   Valitse laskettavaksi tiedoksi % of row.
7. Muokkaa vielä Y-akselia siten, että 100% on isoin arvo. Valitse Y-akseli oikealla hiiren napilla ja valitse ponnahdusikkunasta valinta Format Axis. Laita välilehden Scale kohtaan Maximum arvo 1,0 ja Number-välilehdeltä 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ä.
   
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 valikkokomennolla Data | Filter | Advanced Filter (suom. Tiedot | Suodata | Erikoissuodata).
     
   • 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 | Filter | Show All (suom. Tiedot | Suodata | Näytä kaikki).

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 räntäsateinen (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 valikkokomennolla Tools | Macro | Record New Macro (suom. Työkalut | Makro | Nauhoita uusi makro).
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 Macro (suom. Makro)-työkalupalkin Stop recording (suom. Lopeta nauhoitus) -painikkeella.

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

1. Painikkeen (engl. Button) saat lisättyä Forms (engl. Lomakkeet) -työkaluriviltä, jonka saat näkyville valikkokomennolla View | Toolbars | Forms (suom. Näytä | Työkalurivit | Lomakkeet).
2. Lisääminen onnistuu napsauttamalla Painiketta (engl. Button) -työkaluriviltä ja napsauttamalla sen jälkeen laskentataulukkoa hiirellä.
3. Näkyville avautuu ikkuna, josta voit valita painikkeeseen yhdistettävän makron. Valitse makroksi edellä nauhoittamasi erikoissuodatusmakro.
4. Muokkaan painikkeen nimi sopivaksi.
5. Muokkaa hiiren oikean avulla painikkeen ominaisuuksia (engl. Format Control | Properties) sellaisiksi, ettei painikkeen kokoa muuteta solujen mukaisesti (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 suojaustasoksi Medium (suom. Normaali) valikkokomennolla Tools | Macro | Security (suom. Työkalut | Makro | Suojaus) avautuvasta ikkunasta. Avaa tämän jälkeen työkirjasi uudelleen käyttöön, jolloin näkyville pitäisi tulla varmistuskysely makrojen ottamiseksi käyttöön. Makrot saadaan käyttöön Enable Macros (suom. Ota makrot käyttöön) -painikkeella. 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.