Funktioita, kaavioita ja työkaluja - Demo 2
Tämän demokerran tehtävissä perehdytään muutamiin uusiin funktioihin sekä kaavioiden lisäämiseen. Lisäksi demojen loppupuolella käsitellään muutamia taulukkolaskentatyökaluja.
Tehtävät on suunniteltu ensisijaisesti Microsoft Excel 2003-taulukkolaskentaohjelmaa varten, mutta tehtävät voi tehdä myös muillakin taulukkolaskentaohjelmilla, joskin tehtävissä käytetyt valikkokomennot eroavat ohjelmittain.
Mikäli teet demoja Office 2007:lla, valitse linkki Excel 2007 demotehtäviin ja ohjeisiin.
Mallivideot
- haku.wmv 1.8M
- ehdolliset.wmv 3.8M
- kaavio1.wmv 865K
- kaavio2.wmv 2.0M
- kaavio3.wmv 452K
- lajittelu.wmv 1.3M
- pikasuodatus.wmv 3.1M
- subtotals.wmv 1.8M
Hakufunktiot
Toteutaan demo 1:n tuotehintataulukkoon vaihtuva prosentuaalinen hinnanalennus loppusumman perusteella.
- Avaa tallentamasi tuotehintataulukko tai mallivastaus ja siirry Alennukset-laskentataulukolle.
- Toteuta mallikuvan mukainen alennustaulukko, jossa ensimmäisessä sarakkeessa on loppuhinta ja
toisessa alennusprosentti. Loppuhinta on pienin summa, jolla kyseisen alennuksen voi saada.
- Syötä ensin luvut ja muotoile sitten sopivilla tyyleillä (Otsikko, Currency, Percent).
- Maalaa taulukosta numeroita sisältävä alue (A4:B8) ja nimeä se aletaulu -nimellä
- Käy Tuotehinnat-laskentataulukolla ja nimeä kokonaissumman sisältävä solu D8 nimellä kokonaissumma.
- Palaa takaisin Alennukset-laskentataulukolle ja korvaa
alennusprosentin sisältävä solu B1 hakufunktiolla VLOOKUP (suom. PHAKU).
Anna seuraavat parametrit:
- Haettava tieto: kokonaissumma.
- Hakutaulu: aletaulu.
- Palautussarake: 2.
- Kokeile muuttaa Tuotehinnat-laskentataulukossa tuotteiden määriä ja hintoja. Katso muuttuiko alennusprosentti ja alennettu hinta oikein.
Ehdollisia funktioita
- Avaa käyttöösi viime demojen säätilatietojen taulukko tai mallivastaustiedosto saatila_demo1.xls, jos et ole tehnyt kaikkia tehtäviä. Seuraavissa tehtävissä jatketaan funktioiden käyttämistä lämpötilataulukkoon. Toteuta seuraavat laskennat vihjefunktioita käyttäen.
- Siirry laskentataulukolle, johon viime demoissa laskettiin muutamia tilastollisia tunnuslukuja. Toteuta laskentataulukolle seuraavat kaavat funktioiden ohjatun lisäämisen avulla (Insert | Function (suom. Lisää funktio)). Tarvittavat funktiot löytyvät Tilastollisten (engl. Statistical) tai Matemaattisten ja Trigonometristen (engl. Math & Trig) -funktioiden joukosta.
- Toteuta kaavat, joilla lasketaan lämpimimpien ja kylmien päivien (voi olla useita) lukumäärät. Vihje(countif)
-
Vihje:
Laske eri säätilatyyppisten päivien lukumäärä. Joudut siis laskemaan erikseen Puolipilvisten, Pilvisten, Aurinkoisten ja Lumisateisten päivien lukumäärät. Laskennassa kannattaa käyttää COUNTIF (suom. LASKE.JOS)-funktiota. Ehdot kannattaa laittaa omaan soluunsa.
=COUNTIF(Hakualue ; Haettava tieto)
-
Vihje:
Laske eri säätilatyyppisten päivien keskiarvolämpötila. Kaava kannattaa tehdä siten, että lasketaan summa tietyn säätilatyypin päivistä ja jaetaan se edellä lasketulla päivien lukumäärällä. Vihje (sumif/pävillä)
=SUMIF(Hakualue ; Haettava tieto ; Summausalue) -
Toteuta vastaavalla tavalla taulukko, jossa lasketaan kunkin kuukauden keskilämpötila. Tee ensin kuvan mukaiset ehtosarakkeet, jossa on
kuukausi ja kuukauden indeksi. Laske kuukauden indeksin perusteella ensin kyseisen kuukauden päivien määrä ja sitten keskiarvo.
Kuukauden indeksi löytyy luettelosta Kuukausi-sarakkeelta. Vihje (käytä countif,sumif)
Ehdolliset tilastolliset laskennat on usein helpompi tehdä tietokantafunktioilla.
Kaaviot
Kaavioilla havainnollistat helposti aineistoa. Tehdään säätilataulukon pohjalta muutama kaavio.
Keskiarvolämpötilat
- Tee oheisen kuvan mukainen kaavio kuukausittaisista lämpötiloista.
Maalaa ensin kuukausittaiset keskilämpötilat (otsikko mukaan) ja valitse sitten Insert | Chart (suom. Lisää | Kaavio).
- Valitse ensimmäisessä vaiheessa pylväsdiagrammi.
- Toisessa vaiheessa määritellään X-akseli ja sarjat. Kutakin maalattua saraketta kohden tulee kaavioon pylvässarja. Pylväät ryhmitellään
alueen rivien mukaan. Riviotsikot määräytyvät annetun X-akselin mukaan, joka on yleensä maalatun alueen ensimmäinen sarake. Nyt X-akseli
on määrättävä erikseen sillä emme maalanneet sitä. Mene Series-välilehdellä, napauta Category (X) axis labels -kohdasta
alueen valinta -painiketta
ja valitse kuukausien nimet. - Kolmannessa vaiheessa voidaan täydentää kaavion selitteitä. Kirjoita kaavion nimeksi Keskiarvolämpötilat kuukausittain ja X-akselin otsikoksi Kuukausi.
- Lopuksi voit lisätä kaavion nykyiselle laskentataulukolle tai luoda uuden. Lisää kaavio nykyiselle taulukolle.
- Muokkaa kaaviota hieman. Napauta Kuukausi-sanaa ja siirrä se ylälaitaan raahamalla tekstilaatikon reunaviivasta. Muokkaa X-akselilla olevat tekstin näkymään selvemmin valitsemalla ensin X-akseli aktiiviseksi ja valitsemalla sitten oikean hiiren napin ponnahdusvalikosta Format Axis.
Lämpötilavertailu
- Tee seuraavaksi oheisen kuvan mukainen kaavio.
- Valitse ensin säätilatietolaskentataulukosta lämpötiloja sisältävä sarake aktiiviseksi.
- Toisessa vaiheessa määritellään uudet sarjat kullekkin kuukaudelle. Poista ensin kaikki olemassa olevat sarjat.
- Lisää uusi sarja Add (suom. Lisää) -painikkeella.
- Anna sarjan nimeksi Tammikuu.
- Valitse sarjan tiedoiksi ainoastaan tammikuun lämpötilat. Lämpötilat saa valittua Values-kohdassa olevasta alueen valinta -painikkeesta .
- Määrittele muut kuukausisarjat vastaavalla tavalla.
- X-akselin tiedoiksi kannattaa ottaa erikseen lasketun päivän indeksinumero -sarakkeen tiedot.
- Määritä otsikkotiedot ja selitteiden paikka.
- Lisää kaavio uuteen laskentataulukkoon. Keksi uudelle laskentataulukolle järkevä nimi.
- Kun kaavio on valmis, muuta vielä akselit leikkaamaan toisensa kuvan mukaisesti. Leikkauspisteen muuttaminen onnistuu Y-akselin (pystyakseli) muokkaamalla Category (X) axis crosses at -ominaisuutta.
- Tutki myös muita kaavion eri osien muokkaamismahdollisuuksia. Miten saisit esimerkiksi numerot ja arvopisteet menemään samaan kohtaan X-akselin väliviivojen kanssa?
Piirakkadiagrammi
- Luodaan vielä kuvan mukainen piirakka- eli sektoridiagrammi.
Valitse Säätilalukuja-välilehdeltä alue, jossa on ensimmäisellä sarakkeella säätilatyypit ja toisella sarakkeella päivien määrä. Ota otsikot mukaan.
- Luo näiden pohjalta piirakkadiagrammi.
- Valitse kolmannessa vaiheessa Data labels -välilehdeltä kohta Percentage päälle niin prosenttiosuudet näkyviin.
- Laita kaavio uudelle laskentataulukolle.
Luettelotyökaluja
Seuraavia hyödyllisiä työkaluja voi käyttää luettelomuotoiseen tietoon, jossa ensimmäisellä rivillä on kenttien nimet ja muilla riveillä kenttien mukaisia tietueita.
- Lajittele eli järjestä säätilatietoja seuraavien ehtojen mukaisesti. Muista valita ennen lajittelua kaikki taulukon tietoja sisältävät sarakkeet. Yksittäisen sarakkeen järjestäminen sekoittaa koko taulukon, joten sitä kannattaa varoa. Lajittelemiseen liittyvät ehdot voit antaa valikkokomennolla
Data | Sort (suom. Tiedot | Lajittele). Varmista aina lajitellusta taulukosta, että tiedot ovat haluamassasi järjestyksessä.
Järjestä tiedot
- Lämpötilan ja sään suhteen nousevaan järjestykseen. Käy katsomassa mitä järjestäminen vaikutti edellä tekemiisi kaavioihin.
- Lämpötilan suhteen laskevaan järjestykseen ja päiväyksen suhteen nousevaan järjestykseen.
- Kuukauden ja lämpötilan suhteen nousevaan järjestykseen. Mitä hyötyä kyseisestä suodatuksesta olisi säätilatietojen tarkastelussa?
- Alkuperäiseen järjestykseen pelkästään päivämäärän mukaisesti.
- Tee aineistolle pikasuodatus (engl. AutoFilter).
Pikasuodatuksen saat päälle valikkokomennolla Data | Filter | AutoFilter (suom. Tiedot | Suodata | Pikasuodata).
Edellisten ehtojen vaikutuksen saat halutessasi pois valikkokomennolla Data | Filter | Show All (suom. Tiedot | Suodata | Näytä kaikki).
Suodata näkyville
- Kaikki aurinkoiset päivät.
- Kaikki tammikuun päivien tiedot. Käy katsomassa miten edellä tekemäsi lämpötilojen vertailukaavio muuttui suodatuksen yhteydessä.
- Pilvisten päivien tiedot, jolloin tuulee koillisesta.
- 10 kylmintä päivää.
- Lämpimimmät 15% päivistä.
- Päivät, jolloin lämpötila on suurempi tai yhtä suuri kuin -20 astetta ja lämpötila on pienempi tai yhtä suuri kuin -10 astetta. Huomaa, että ennen tämän suodatuksen tekoa sinun on muotoiltava lämpötilat sisältävät solut ilman yksiköitä koska muuten excelin pikasuodatus ei osaa tehdä vertailuja oikein.
- Päivät, jolloin lämpötila on suurempi tai yhtäsuuri kuin -5 astetta tai lämpötila on pienempi tai yhtä suuri kuin -30. Suodata edelleen näkyville edelliseen ehtoon täsmäävät tammikuun päivät.
- Lajittele eli järjestä säätilatiedot säätilojen ja tuulen suunnan mukaan laskevaan järjestykseen.
Lisää tämän jälkeen tietoihin välisummia seuraavan ohjeen mukaisesti.
- Välisummia pääset lisäämään valikkokomennolla Data | Subtotals (suom. Tiedot | Välisummat).
- Määritä avautuvasta ikkunasta seuraavat tiedot kuntoon.
- Välisummat lasketaan Sää-tietojen muuttuessa (engl. At each change in).
- Käytettävä funktio on keskiarvo (engl. Average).
- Keskiarvot lisätään ainoastaan Lämpötilan yhteyteen.
- Hyväksy määrittämäsi tiedot ja tutki laskettuja tietoja.
- Täydennetään välisummien laskemista hieman. Laske välisummat uudelleen täydentämään jo laskettuja tietoja seuraavien ehtojen mukaisesti.
- Uudet välisummat lasketaan tuulen Suunnan muuttuessa.
- Käytettävä funktio on edelleen keskiarvo (engl. Average).
- Keskiarvot lisätään ainoastaan Lämpötilan yhteyteen.
- Poista ikkunan alareunasta valinta Replace current subtotals, jolloin uudet keskiarvot tulevat tarkentamaan entisiä.
- Poista välisummat valikkokomennolla Data | Subtotals (suom. Tiedot | Välisummat) avautuvan ikkunan Remove All (suom. Poista kaikki) -painikkeella.
Lisätehtäviä
- Säätilatietojen keskiarvoja laskevissa ehdollisissa funktioissa on ongelma.
Jos kyseiseen säätilakategoriaan liittyvien päivien lukumäärä on nolla, niin kaava antaa virheilmoituksen.
Tee säätilatyypin mukaan keskiarvoja laskevaan soluun kaava, jossa ennen laskemista tarkistetaan ettei jakaja ei ole nolla.
Vaihtoehtoisesti voidaan Information-ryhmän funktioilla tarkistaa jälkikäteen onko laskennassa tapahtunut virhettä.
Vihje: IF-funktio (suom. JOS).
- Lisää seuraavat kelpoisuustarkistukset (oikeellisuustarkistukset) säätilatietotaulukkoon. Tarkemmin oikeellisuustarkistuksiin voit perehtyä
luentomonisteen sähköisestä versiosta.
- Päivämäärän sisältävään sarakkeeseen saa antaa ainoastaa päivämääriä. Voit lisätä vihjeen myös syötteen muodosta sekä selventävän virheviestin.
- Määrittele lämpötilalle ja tuulen jotkin sopivat rajat.
Vihje: Data |Data Validation .
- Hae säätilatiedot erillisestä listasta. Tee siis ensin säätilatietoja sisältävä lista erilliselle laskentataulukolle ja nimeä alue hyvälle nimelle. Tämän jälkeen voit määritellä tietojen lähteeksi listan (=listanimi).
- Toteuta edellisen kaltainen tietojen hakeminen myös tuulen suunnalle.
Käyttäjien kommentit