Demo 6 - Taulukkolaskentatyökaluja ja tietokantafunktioita

Seuraavissa demotehtävissä opetellaan käyttämään erilaisia taulukkolaskentatyökaluja.

1. Käy hakemassa kuukauden säätilatiedot seuraavasta osoitteesta: <URL: http://appro.mit.jyu.fi/2001/kevat/ohjelmistot/demot/demo6/saatila.xls>. Tallenna tiedosto U:-levyasemalle ja avaa se Excelissä.
2. Seuraavissa tehtävissä kokeillaan muutaman tietokantafunktion toimintaa. Tietokantafunktioista ei löydy asiaa kurssin luentomonisteesta, joten sinun kannattaa käyttää apuna tehtävien tekemisessä seuraavaa artikkelia: <URL: http://appro.mit.jyu.fi/materiaalia/tietokantafunktiot.html>. Seuraavaksi valmistellaan hieman ehtoaluetta sekä säätilataulukkoa:
   • Tee ensin oheisen kuvan mukainen ehtoalue, jolle voit kopioida säätilatietojen otsikot. Nimeä alue (2 riviä, 4 saraketta) nimelle ehto.

     

   • Nimeä säätilatietoalue otsikoineen nimelle saatila.

   Laske tietokantafunktioilla seuraavien ehtojen mukaiset tulokset:

   • Laske keskilämpötila (DAVERAGE (suom. TKESKIARVO)) päivistä, joiden lämpötila on suurempi kuin -20 astetta. Älä tee funktiota ehtoalueeseen, vaan johonkin muuhun soluun! (Vastaus on -5 astetta.)
   • Laske keskilämpötila päivistä, joiden lämpötila on suurempi kuin -20 astetta ja pilvisyys on S. (Vastaus on -8,5 astetta.)
   • Laske sellaisten päivien lukumäärä (DCOUNTA (suom. TLASKEA)), jolloin pilvisyys on K. (Vastaus on 5 kappaletta.)
   • Laske sellaisten päivien lukumäärä, jolloin pilvisyys on K tai S. Huomaa, että joudut laajentamaan ehtoaluetta yhdellä rivillä, joten sinun kannattaa nimetä käyttöösi uusi ehtoalue. (Vastaus on 21 kappaletta.)
   • Laske sellaisten päivien lukumäärä, jolloin lämpötila on suurempi kuin -2 astetta ja pienempi kuin 10 astetta. Huomaa, että joudut laajentamaan alkuperäistä ehtoaluetta yhdellä sarakkeella (1 rivi, 5 saraketta), joten kannatta nimetä uusi ehtoalue. (Vastaus on 2 kappaletta.)
   • Laske sellaisten päivien lukumäärä, jolloin lämpötila on suurempi kuin -2 astetta tai pienempi kuin -15 astetta. Millainen ehtoalueen pitää olla nyt? (Vastaus on 21 kappaletta.)
   • Laske sellaisten päivien maksimilämpötila (DMAX (suom. TMAKS)), jolloin pilvisyys on K ja sää on V tai pilvisyys S ja sää on V. (Vastaus on -23 astetta.)
3. Seuraavissa tehtävissä käytetään säätilataulukkoon järjestämiseen tarkoitettua työkalua. Säätilataulukon järjestäminen aloitetaan valitsemalla aktiiviseksi koko säätilataulukko otsikoineen ja tämän jälkeen valinnalla Tiedot | Lajittele (engl. Data | Sort) päästään muokkaamaan järjestysehtoja. Tee järjestäminen seuraavien kriteerien mukaisesti:
   • Järjestä säätilataulukko lämpötilan mukaan nousevaan järjestykseen.
   • Järjestä säätilataulukko ensisijaisesti lämpötilan mukaan nousevaan järjestykseen ja toissijaisesti pilvisyyden mukaan laskevaan järjestykseen.
   • Järjestä säätilataulukko ensisijaisesti sään mukaan nousevaan järjestykseen ja toissijaisesti lämpötilan mukaan laskevaan järjestykseen.
   • Miten saat järjestettyä säätilatiedot alkuperäiseen järjestykseen järjestystyökalun avulla? Järjestä säätilataulukko alkuperäiseen järjestykseensä.
4. Taulukkolaskennassa on olemassa valmis lomake luettelomuotoisen datan käsittelyyn. Valitse säätilatiedoista jokin yksittäinen solu aktiiviseksi. Lomakkeen saat käyttöön valinnalla Tiedot | Lomake (engl. Data | Form). Kokeile lomakkeen toimintaa seuraavien tehtävien opastuksella.
   • Muuta lomakkeen avulla kymmenennen päivän säätilatietoja. Tarkista, että tiedot muuttuivat säätilataulukkoon.
   • Lisää lomakkeen avulla Uusi (engl. New) 29. päivä ja sille säätilatiedot. Tarkista, että lisäämäsi päivä tuli säätilatietojen loppuun.
   • Lomakkeen käytön voit lopettaa valinnalla Sulje (engl. Close).
5. Seuraavissa tehtävissä suodatetaan säätilatietoja pikasuodatuksen avulla. Pikasuodatuksen saat päälle valitsemalla ensin suodatettavan alueen otsikkoriveineen ja valitsemalla tämän jälkeen pikasuodatuksen päälle valinnalla Tiedot | Suodata | Pikasuodata (engl. Data | Filter | Auto Filter). Suodatusehtoja voit muuttaa otsikkoriville ilmaantuvien valikoiden avulla. Tee säätilalle suodatuksia seuraavien ehtojen mukaan:
   1. Suodata näkyviin kaikki sellaiset päivät, joina on pakkasta -30 astetta.
   2. Suodata näkyviin kaikki sellaiset päivät, joina on pakkasta -30 astetta ja pilvisyys on selkeä (S).
   3. Suodata näkyviin kaikki sellaiset päivät, joina on pakkasta -30 astetta ja pilvisyys on selkeä (S) ja sää on lumisateinen (L).
   4. Poista kaikista kohdista kaikki suodatusehdot eli valitse näkyville kaikki (engl. All). Suodatusehtojen poistaminen tulee kysymykseen aina, kun lisäät kokonaan uusia suodatusehtoja.
   5. Suodata näkyviin 4 kylmintä päivää. Vihje: (Top 10...)
   6. Suodata näkyviin 5 lämpimintä päivää.
   7. Suodata näkyviin kaikki sellaiset päivät, joina lämpötila on ollut suurempi kuin -23 astetta ja joina lämpötila on ollut pienempi kuin 5 astetta. (Vihje: Custom...)
   8. Suodata näkyviin kaikki sellaiset päivät, joina lämpötila on ollut suurempi kuin -23 astetta ja joina lämpötila on ollut pienempi kuin 5 astetta ja joina pilvisyys on ollut puolipilvistä (P).
   9. Suodata näkyviin kaikki sellaiset päivät, joina lämpötila on ollut pienempi kuin -23 astetta tai joina lämpötila on ollut suurempi kuin 5 astetta.

   Lopeta pikasuodatus valinnalla Tiedot | Suodata | Pikasuodata (engl. Data | Filter | Auto Filter).

6. Tee seuraavaksi muutama raportti säätilataulukosta. Ohjeessa olevat vaiheet eroavat hieman sisällön ja järjestyksen puolesta ohjelman eri versioiden kesken.
   • Raportin tekeminen aloitetaan valinnalla Tiedot | Pivot-taulukon raportti (engl. Data | PivotTable Report)
   • Ensimmäisessä vaiheessa valitaan paikka, josta raporttiin tuleva data otetaan. Tehtävän tapauksessa valitaan dataksi Microsoft Excelin luettelo tai tietokanta (engl. Microsoft Excel List or database). Samalla valitaan millainen raportti luodaan. Tässä tapauksessa tehdään pelkkä Pivot-taulu (engl. Pivot Table).
   • Seuraavassa (engl. Next) kohdassa määritellään data-alue (engl. Range), jolta raportti halutaan tehdä. Valitse säätilataulukko otsikoineen data-alueeksi.
   • Seuraavassa (engl. Next) vaiheessa voidaan valita tehdäänkö raportti olemassa olevalle taulukkolaskentalomakkeelle vai tehdäänkö se uudelle taulukkolaskentalomakkeelle. Tehtävän tapauksessa raportti kannattaa sijoittaa uudelle taulukkolaskentalomakkeelle (engl. New worksheet).
   • Lopuksi (engl. Finish) valitaan raportille tuleva data. Nyt pystytään määrittelemään raportin riveille ja sarakkeisiin tulevat otsikot sekä mahdollisesti niiden leikkauspisteisiin tuleva data.
     • Raahaa hiirellä rivikentiksi (engl. Row field) PVM-painike PivotTable-työkalupalkista.
     • Raahaa hiirellä tiedoiksi (engl. Data)Lämpötilat-painike PivotTable-työkalupalkista.
     • Mitä raportista nähdään?
     • Raahaa hiirellä pois raportista PVM-painike ja Sum of Lämpötila -painike.

     Tee seuraavaksi uusi raportti seuraavien ohjeiden mukaisesti.

     • Raahaa hiirellä rivikentiksi (engl. Row field) Pilvisyys-painike PivotTable-työkalupalkista.
     • Raahaa hiirellä tiedoiksi (engl. Data)Lämpötilat-painike PivotTable-työkalupalkista.
     • Mitä raportista nähdään?
     • Muuta raporttia kaksoisnapauttamalla Sum of Lämpötila -painiketta ja muuta laskettavaksi arvoksi keskiarvo (engl. Average)
     • Muuta raportin rivejä Pilvisyys-painikkeesta löytyvän alasvetovalikon avulla. Poista jokin valikon valinnoista ja hyväksy muutokset.

     Poista edellisen raportin tiedot ja tee vielä yksi uusi raportti.

     • Raahaa hiirellä rivikentiksi (engl. Row field) Pilvisyys-painike PivotTable-työkalupalkista.
     • Raahaa hiirellä sarakekentiksi (engl. Column field) Sää-painike PivotTable-työkalupalkista.
     • Raahaa hiirellä tiedoiksi (engl. Data)Lämpötilat-painike PivotTable-työkalupalkista.
     • Muuta raporttia tuplanapauttamalla Sum of Lämpötila -painiketta ja muuta laskettavaksi arvoksi lukumäärä (engl. Count)
     • Mitä raportista nähdään?
     • Jos raportin oikeassa alakulmassa ei ole lukua 28, niin muokkaa raportissa näkyviä pilvisyyskenttiä siten, että kaikki pilvisyys-vaihtoehdot tuleva näkyviin. Kyseessä on koko kuukaudessa olevien päivien lukumäärä.

     Pivot-taulukon ominaisuuksia pääset muokkaamaan hiiren oikean painikkeen avulla. Kokeile poistaa rivien ja sarakkeiden Grand Total -kentät.

     Halutessasi voit kokeilla toteuttaa muitakin Pivot-tauluja säätiladatasta.

7. Tässä tehtävässä käytetään uudelleen ensimmäisessä tehtävässä tehtyä ehtoaluetta. Seuraavassa suodatetaan säätilataulukon tietoja ehtoalueen ehtojen avulla. Seuraavassa tarkemmat ohjeet erikoissuodatuksen käytöstä:
   • Merkitse ehtoalueeseen ehto, jonka perusteella suodatetaan näkyviin sellaiset päivät, joiden lämpötila on suurempi kuin -2 astetta.
   • Valitse aktiiviseksi koko säätilataulukko otsikkotietoineen.
   • Tämän jälkeen aloitetaan erikoissuodatus valinnalla Tiedot | Suodata | Erikoissuodata (engl. Data | Filter | Advanced Filter)
   • Valinnalla avautuvasta ikkunasta voidaan valita siirretäänkö suodatettu data kokonaan toiseen paikkaan vai suodatetaanko suoraan valittuja tietoja. Tehtävän tapauksessa voidaan suodattaa luettelo paikallaan (engl. Filter the list, in-place).
   • Luetteloalue (List Range) on koko data-alue, jonka tietoja suodatetaan. Tässä tapauksessa koko säätilatietotaulukko otsikoineen eli nimettyalue saatila.
   • Ehtoalue (Criteria range) on alue, jossa suodatuksen ehdot ovat. Valitse alueeksi ehtoalue, johon olet edellä merkinnyt lämpötilaehdon.
   • Ikkunan tietojen hyväksymisen jälkeen säätilataulukon tiedot suodatetaan ehtoalueen ehtojen mukaisesti.
   • Kaiken datan saat takaisin näkyviin valinnalla Tiedot | Suodata | Näytä kaikki (engl. Data | Filter | Show All)

   

   Suodata säätilataulukon dataa seuraavien ehtojen mukaisesti:

   • Suodata näkyviin sellaiset päivät, joiden pilvisyys on K tai S. Millainen ehtoalueen pitää olla nyt?
   • Suodata näkyviin sellaiset päivät, joiden lämpötila on suurempi kuin -2 astetta ja pienempi kuin 10 astetta. Millainen ehtoalueen pitää olla nyt?
   • Suodata näkyviin sellaiset päivät, joiden lämpötila on suurempi kuin -2 astetta tai pienempi kuin -15 astetta. Millainen ehtoalueen pitää olla nyt?
   • Kokeile myös datan kopioimista toiseen paikkaa suodattamisen yhteydessä.

Lisätehtäviä

Lisätehtävissä harjoitellaan datan tuomista Exceliin ulkopuolelta.

1. Tallenna ensin seuraavat tiedostot U:-levyasemalle:
   • Tekstitiedosto: valuutat.txt
   • Tietokanta: tietokanta.mdb
2. Sarakkeisiin järjestetyn tekstitiedoston tuominen Exceliin onnistuu seuraavan ohjeen avulla.
   • Valinnalla Data | Get External Data | Import Text File voidaan valita hakemistorakenteesta tekstitiedosto, jonka sisältö halutaan tuoda Exceliin. Valitse tiedostoksi valuutat.txt
   • Seuraavassa vaiheessa näkyviin tulee ikkuna, josta voidaan valita tuotavan tiedoston muoto. Tiedoston muodoksi voidaan valita joko erotinmerkillä rajattu tieto (engl. Delimited) tai kiinteästi sarakkeissa oleva tieto (engl. Fixed width). Valitse vaihtoehdoista jälkimmäinen.
   • Seuraavassa vaiheessa valitaan hiiren avulla datasarakkeiden paikat. Valitse sarake-erottimien paikaksi sellaiset, että kaikki data tulee jaetuksi sarakkeisiin järkevällä tavalla.
   • Seuraavassa vaiheessa voidaan haluttaessa valita sarakkeisiin tulevien tietojen tyyppi. Tiedon tyypin valintaa ei ole pakko tehdä, koska oletuksena on Yleinen (engl. General)-tyyppi
   • Lopuksi valitaan laitetaanko tieto uudelle vai olemassa olevalle taulukkolaskentalomakkeelle. Voit määritellä paikaksi kokonaan uuden lomakkeen.
3. Exceliin voidaan tuoda myös Microsoft Access -tietokannan tauluja. Lue Excelin opasteesta kohta: "Set up a data source that uses the Microsoft Access driver" ja tuo dataa Exceliin sen ohjeiden mukaisesti dataa.mdb-tietokannasta. Mitään ohjeen erikoisemmista määrittelyistä ei tarvitse tehdä, joten toimenpide on huomattavasti yksikertaisempi kuin ohjeesta voisi päätellä.
   • Voit asentaa Microsoft Queryn, jos ohjelma kysyy jotakin asentamisesta.
   • Kyselyn tekemisessä tulee vaihe, jossa voidaan määrittää mitä tietoja tietokannasta otetaan. Valitse mukaan kyselyyn kaikki tiedot.
   • Halutessasi voit suodattaa dataa kyselyn teon yhteydessä.
   • Halutessasi voit järjestää dataa kyselyn teon yhteydessä.
   • Lopuksi valitse datan tuominen Exceliin kokonaan uudelle taulukkolaskentalomakkeelle.