Demo 6 - Taulukkolaskentatyökaluja ja tietokantafunktioita

Seuraavissa demotehtävissä opetellaan käyttämään erilaisia taulukkolaskentatyökaluja ja tietokanta- eli luettelofunktioita. Jos jonkin työkalun käyttäminen tuntuu vaikealta, niin voit tutustua työkalun periaatteisiin demojen mallitiedoston saatila6.xls avulla. Tiedostoon on tallennettu tietokantafunktioissa käytetyt ehtoalueet ja kaavat, tehtävät pivot-taulut sekä erikoissuodatuksen ehtoalueet ja suodattamalla saatavat datat. Lisäksi työkalujen käyttöön voit perehtyä kurssin luentomonisteesta tai erillisestä ohjeesta.

1. Tallenna kuukauden säätilatiedot sisältävä työkirja saatilapohja.xls U-levyasemalle jollakin nimellä. Avaa tallentamasi tiedosto Excelissä.
2. Seuraavissa tehtävissä kokeillaan muutaman tietokanta- eli luettelofunktion toimintaa. Tietokantafunktioihin liittyviä ohjeita löydät kurssin luentomonisteesta.
   • Tee ensin oheisen kuvan mukainen ehtoalue, jolle voit kopioida säätilatietojen otsikot. Ehtoaluetta käytetään funkioiden laskentaehtojen määrittämiseen. Nimeä solualue (2 riviä, 4 saraketta) nimelle ehto. Alueeseen täytyy siis ottaa mukaan myös otsikkorivi.

     

   • Nimeä säätilatietoalue otsikoineen (A1:D29) nimelle saatila.

   Laske tietokantafunktioilla seuraavien ehtojen mukaiset tulokset:

   1. 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! Ehtoalueelle kirjoitetaan yksinkertaisesti ehto Lämpötila-sarakkeeseen. (Vastaus on -5 astetta.)
   2. Laske keskilämpötila päivistä, joiden lämpötila on suurempi kuin -20 astetta ja pilvisyys on S. (Vastaus on -8,5 astetta.)
   3. Laske sellaisten päivien lukumäärä (DCOUNTA (suom. TLASKEA)), jolloin pilvisyys on K. (Vastaus on 5 kappaletta.)
   4. 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.)
   5. 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 (koko on siis 2 riviä ja 5 saraketta), joten kannatta nimetä uusi ehtoalue. (Vastaus on 2 kappaletta.)
   6. 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.)
   7. 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. Säätilatietotaulukossa on käytetty B, C ja D-sarakkeissa oikeellisuustarkistustyökalua, joka saadaan käyttöön valikkokomennolla Data | Validation. Tutki miten työkalu oikein toimii. Voit muuttaa taulukon syötteitä ja oikeellisuustarkistukseen liittyviä ehtoja. Tarvittaessa voit tutustua myös työkalun lyhyiin ohjeisiin.
4. Taulukkolaskennassa on olemassa valmis tietolomake luettelomuotoisen tietojen käsittelyyn. Valitse säätilatiedoista jokin yksittäinen solu aktiiviseksi. Tietolomakkeen saat käyttöön valinnalla Data | Form (suom. Tiedot | Lomake). Kokeile lomakkeen toimintaa seuraavien tehtävien opastuksella. Tarvittaessa voit tutustua myös työkalun lyhyiin ohjeisiin
   • 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).
   • Kokeile toimiiko oikeellisuustarkistukset lisäämälläsi rivillä?
   • Millainen on edellä nimetty alue saatila?
   • Asian korjaamiseksi kannattaisi muuttaa nimettyalue koskemaan kaikkien sarakkeiden soluja ja oikeellisuustarkistukset olisi myös kannattanut tehdä koko sarakkeen soluihin! Tässä yhteydessä noita ei kuitenkaan kannata alkaa muuttamaan.
5. 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 Data | Sort (suom. Tiedot | Lajittele) päästään muokkaamaan järjestysehtoja. Tarkemmat ohjeet järjestämistyökalun käytöstä selviävät kurssin luentomonisteesta. 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ä.
6. 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 Data | Filter | Auto Filter (suom. Tiedot | Suodata | Pikasuodata). Suodatusehtoja voit muuttaa otsikkoriville ilmaantuvien valikoiden avulla. Tarkemmat ohjeet pikasuodatuksen käytöstä selviävät kurssin luentomonisteesta. 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).

7. Tee seuraavaksi muutama raportti säätilataulukosta. Ohjeessa olevat vaiheet eroavat hieman sisällön ja järjestyksen puolesta ohjelman eri versioiden kesken. Tarvittaessa voit tutustua myös työkalun lyhyiin ohjeisiin.
   • 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) 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 Count 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 raahaamalla ne ulos raportin alueelta ja tee uusi raportti seuraavien ohjeiden mukaiseksi.

     • 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.
     • Mitä raportista nähdään?
     • Jos raportin oikeassa alakulmassa ei ole vähintään 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ä. Jos olet lisännyt aiemmin kuukauteen päiviä, niin luku saattaa olla suurempi.
   • 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.
8. Tässä tehtävässä käytetään uudelleen ensimmäisissä tehtävissä tehtyä ehtoaluetta. Seuraavassa suodatetaan erikoissuodatuksella säätilataulukon tietoja seuraavassa määriteltyjen ehtojen avulla. Tarvittaessa voit tutustua myös työkalun lyhyiin ohjeisiin.
   • Merkitse ehtoalueeseen ehto, jonka perusteella suodatetaan näkyviin sellaiset päivät, joiden lämpötila on suurempi kuin -2 astetta. Ehdon toteuttaminen onnistuu samalla tavoin kuin tietokantafunktioiden yhteydessä.
   • Valitse aktiiviseksi koko säätilataulukko otsikkotietoineen.
   • Tämän jälkeen aloitetaan erikoissuodatus valikkokomennolla Data | Filter | Advanced Filter (suom. Tiedot | Suodata | Erikoissuodata)
   • 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 (engl. List Range) on koko data-alue, jonka tietoja suodatetaan. Tässä tapauksessa koko säätilatietotaulukko otsikoineen eli nimettyalue saatila.
   • Ehtoalue (engl. 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 valikkokomennolla Data | Filter | Show All (suom. Tiedot | Suodata | Näytä kaikki)

   

   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.