Demo 4 - Taulukkolaskentafunktiot

Näissä demoissa jatketaan edellisissä demoissa tehdyn tuotteiden hintataulukon kehittämistä. Avaa edellisissä demoissa tekemäsi tuotetaulukko tai käytä pohjana valmista tuotetaulukkoa hintataulukko.xls.

1. Tallenna avaamasi työkirjasi uudelle nimelle demo4.xls Muista tallentaa työkirjaasi riittävän usein!
2. Nimeä laskentataulukko eli välilehti, jossa on tuotteiden hintatiedot, nimelle tuotetaulu.
3. Kehitetään seuraavaksi hieman alennusprosentin määräytymistä. Kauppias haluaa antaa asiakkailleen alennusta ostosten kokonaishintakertymän mukaan. Toteuta seuraavat alennusprosentin määräävät kaavat soluun E1 käyttäen IF(suom. JOS)-funktiota. Funktion lisääminen onnistuu valinnalla Lisää | Funktio (engl. Insert | Function).

   

   • Jos tuotteiden kokonaishinnan summa on suurempi tai yhtä suuri kuin 1000 €, niin annetaan 10 prosentin alennus, muutoin alennuksen suuruus on 0 prosenttia.
   • Seuraavassa otetaan mukaan edelliseen kokonaan uusi alennusprosenttiraja. Jos IF-funktion käyttö tuntuu vaikealle, niin sisäkkäisiä ehtoja kannattaa hahmotella ensin paperilla.
     • Jos tuotteiden kokonaishinnan summa on suurempi tai yhtä suuri kuin 2000 €, niin annetaan 15 prosentin alennus.
     • Jos tuotteiden kokonaishinnan summa on suurempi tai yhtä suuri kuin 1000 €, mutta pienempi kuin 2000 €, niin annetaan 10 prosentin alennus.
     • Muussa tapauksessa alennuksen suuruus on 0 prosenttia.
   • Seuraavassa edelliseen otetaan mukaan taas uusi alennusprosenttiraja. Jos IF-funktion käyttö tuntuu vaikealle, niin sisäkkäisiä ehtoja kannattaa hahmotella ensin paperilla.
     • Jos tuotteiden kokonaishinnan summa on suurempi tai yhtä suuri kuin 3000 €, niin annetaan 20 prosentin alennus.
     • Jos tuotteiden kokonaishinnan summa on suurempi tai yhtä suuri kuin 2000 €, mutta pienempi kuin 3000 €, niin annetaan 15 prosentin alennus.
     • Jos tuotteiden kokonaishinnan summa on suurempi tai yhtä suuri kuin 1000 €, mutta pienempi kuin 2000 €, niin annetaan 10 prosentin alennus.
     • Muussa tapauksessa alennuksen suuruus on 0 prosenttia.
   • Edellisestä kaavasta tuli jo kohtuullisen monimutkainen ja pitkä. Mieti millainen kehittämästäsi funktiosta tulisi, jos siihen lisättäisiin vielä seuraavia ehtoja. Kaavaa ei tarvitse kuitenkaan toteuttaa.
     • Kaavaan lisätään uusia alennusprosenttirajoja.
     • Kaavaan lisättäisiin alennusprosenttirajoja myös tuotteiden lukumäärän suhteen.
4. Sisäkkäisten IF-funktioiden toteuttaminen on kohtuullisen työlästä varsinkin alennusprosenttien lukumäärän kasvaessa. Seuraavaksi alennusprosenttien muodostuminen toteutetaan hieman helpommalla tavalla, mutta aina ei voida välttyä sisäkkäisten IF-funktioiden käytöltä.

   

   • Lisää työkirjaasi uusi laskentataulukko eli välilehti, jolle teet oheisen kuvan mukaisen alennusprosenttitaulukon.
   • Nimeä laskentataulukko eli välilehti nimelle alennus.
   • Nimeä myös taulukosta solualue A2:B7 nimelle alennustaulu.
   • Tarkoituksena on toteuttaa alennusprosentin määräytyminen siten, että alennusprosentti käydään hakemassa alennustaulusta. Alennusprosentin noutaminen onnistuu VLOOKUP (suom. PHAKU)-funktion avulla. Suunnittele ja toteuta tuotetaulun soluun E1-kaava, jonka avulla alennusprosentti saadaan haettua.
   • Tarkista tekemäsi kaavan toimivuus tuotteiden lukumääriä muuttamalla.
5. Kasvata tuotetaulukon tuotteiden kokonaishinta yli 6000 euroa tuotteiden määriä muuttamalla. Lisää alennustauluun uusi rivi siten, että se tulee nimetyn alueen sisälle. Tee uusi prosenttiraja, jonka mukaan annetaan alennusta 35 prosenttia, jos tuotteiden kokonaishinta on suurempi tai yhtä suuri kuin 6000 €. Testaa uuden alennusprosenttirajan toiminta huolellisesti.
6. Kuten ehkä jo huomasit, niin alennustaulukko täytyy olla järjestetty pienimmästä suurimpaa, jotta VLOOKUP-funktio toimii oikein! Järjestämisen voit tehdä valikkokomennon Data | Sort (suom. Tiedot | Järjestä) kautta avautuvalla työkalulla. Valitse ensiksi järjestettävä alue!
7. Lisää tuotetauluun uusi tuote kenkien ja housujen väliin.
   • Tuotteen lisääminen onnistuu rivin lisäämisellä, jonka jälkeen tuotteelle voidaan kirjoittaa nimi- ja hintatiedot.
   • Kopioi tuotteen kokonaishinnan, alennuksen ja alennetun hinnan laskevat kaavat housujen vastaavista sarakkeista lisätylle riville.
   • Toimivatko kaavat sellaisenaan? Testaa kaavojen toimivuutta muuttamalla uuden tuotteen kappalemääriä. Miten yhteensä tiedot muuttuivat? Toimivatko yhteensä tietojen kaavat edelleen oikein?
8. Lisää soluun B1 päivämäärän ilmoittava TODAY (suom. TÄMÄ.PÄIVÄ)-funktio.
9. Seuraavaksi muistellaan hieman soluviittauksia oheisen kuvan kertotaululla. Kertotaulusta on tultava sellainen, että sillä saadaan näkyviin mikä tahansa 5*5-kertotaulu.

   

   • Lisää työkirjaan yksi uusi laskentataulukko eli välilehti kertotaulua varten ja nimeä laskentataulukko jollekin nimelle.
   • Tee kertotaulun otsikkorivi(1) ja sarake(A) siten, että muut rivin ja sarakkeen arvot lasketaan solujen B1 ja A2 arvojen perusteella (Esim. lisätään edellisen solun arvoon yksi).
   • Toteuta varsinaisen kertolaskukaava soluun B2 siten, että voit kopioida kaavan muihinkin kertotaulusoluihin.
   • Kokeile kertotaulun toimintaa muuttamalla solujen B1 ja A2 arvoja.
10. Nimeä kertotaulusta ensimmäisen rivin solut nimelle kerroin1. Nimeä kertotaulusta A-sarakkeen solut nimelle kerroin2. Muuta kertotaulun kaava sellaiseksi, että se käyttää hyväkseen nimeämiäsi alueita.
11. Seuraavaksi aloitetaan tekemään uutta pientä taulukkolaskentasovellusta, joka on tarkoitettu opiskelijoiden arvosanojen laskentaan.

    

    

    • Lisää työkirjaasi kaksi uutta laskentataulukkoa eli välilehteä. Anna toiselle laskentataulukolle eli välilehdelle nimi Tentti ja toiselle laskentalomakkeelle nimi Arvosanat.
    • Lisää Tentti-lomakkeelle opiskelijoiden nimet ja tenttitehtävien pistemäärät B-, C-, D-, E-, F- ja G-sarakkeisiin. Sarakkeiden H, I ja J arvot lasketaan hieman myöhemmin.
    • Tee Arvosanat-lomakkeelle oheisen kuvan mukainen arvosanataulukko. Vasemman puolimmaisessa sarakkeessa (A) on pisterajat, joita vastaavat numeerisessa muodossa oleva arvosana on B-sarakkeessa. Tekstimuodossa oleva arvosana löytyy vastaavasti C-sarakkeesta.
    • Muotoile lisätyt lomakkeet oheisten kuvien mukaisiksi tyylejä käyttäen. Tekstiarvosanoja varten on jouduttu luomaan uusi tyyli.
12. Seuraavaksi lisätään opiskelijan arvosanan laskeva kaava Tentti-lomakkeelle.
    • Lisää H-sarakkeeseen kaava, jolla saadaan laskettua tenttitehtävien kokonaispistemäärä. Laskennassa joudut käyttämään SUM (suom. SUMMA)-funktiota. Tee kaavasta sellainen, että sitä voidaan kopioida saman sarakkeen muihin soluihin.
      
    • Lisää I-sarakkeeseen kaava, jonka avulla saadaan määriteltyä pistemäärää vastaava numeerisessa muodossa oleva arvosana opiskelijalle.
      • Pistemäärää vastaava arvosana on tarkoitus hakea Arvosanat-lomakkeelta, joten sinun kannattaa käydä määrittämässä Arvosanat-lomakkeelle nimetty alue. Jos olet vahingossa määritellyt nimetyn alueen väärin, niin sen muuttaminen tai poistaminen onnistuu valinnalla Lisää | Nimi | Määrittele (engl. Insert | Name | Define)
      • Tämän jälkeen arvosanojen hakeminen I-sarakkeeseen onnistuu VLOOKUP (suom. PHAKU)-funktion avulla. Tee kaavasta sellainen, että sitä voidaan kopioida saman sarakkeen muihin soluihin.
    • Lisää J-sarakkeeseen kaava, jonka avulla saadaan määriteltyä pistemäärää vastaava tekstimuodossa oleva arvosana opiskelijalle.
13. Kehitetään opiskelijoiden arvosanataulukkoa hieman eteenpäin.

    

    • Lisää tenttipistemäärien perään yksi uusi sarake. Sarakkeeseen tulee opiskelijoiden harjoitustyöstä saamat bonuspisteet.
    • Muokkaa kokonaispistemäärän laskevaa kaavaa sellaiseksi, että bonuspistemäärä lasketaan mukaan kokonaispistemäärään ainoastaan, jos opiskelijan kokonaispistemäärä ilman bonuspistettä on vähintään 12 pistettä. Opiskelijan tenttitulos on siis oltava hyväksytty ilman bonuspisteitä. Joudut käyttämään laskennassa sekä SUM (suom. SUMMA)-funktiota että IF (suom. JOS)-funktiota.
    • Jos toteutat kaavan Excel 2000:ssa, niin huomaat kaavan toimivan hieman väärin! Tämä johtuu Excel 2000:n "älykkyydestä" :) Käy poistamassa kohdasta Tools | Options | Edit (suom. Työkalut | Asetukset | Muokkaa) rasti kohdasta Extend list formats and formulas ja kopioi tekemäsi kaava uudelleen soluihin.
    • Lisää opiskelijataulukkoon kaksi uutta saraketta, joihin merkitään harjoitustyön kummankin osan hyväksymispäivämäärä.
    • Muuta opiskelijan arvosanan määräävä kaavaa sellaiseksi, että hyväksytty arvosana annetaan vasta, kun opiskelijan kumpikin harjoitustyö on tehty. Joudut käyttämään arvosanan laskemisessa OR(suom. TAI)-, VLOOKUP (suom. PHAKU)-, ISBLANK (suom. ONTYHJÄ) ja IF (suom. JOS)-funktiota. Seuraavassa pieni vihje funktioiden järjestyksen ajattelemiseen.

      VIHJE:  IF(OR(ISBLANK();ISBLANK()); ;VLOOKUP())
      

      Kaavan voi toki toteuttaa käyttämällä muitakin funktioita!
    • Opiskelijataulukosta voi olla myös mielenkiintoista saada informaatiota kokeen tuloksista. Lisää oheisen kuvan mukaisesti opiskelijataulukkoon keskiarvo, minimi- ja maksimiarvojen laskenta. Tarvitset laskemiseen MIN (suom. MIN)-, MAX (suom. MAKS)-, AVERAGE (suom. KESKIARVO)-funktioita.
    • Miten keskiarvo, minimi ja maksimi saadaan tekstiarvosanoista?

Lisätehtäviä

1. Oheisessa kuvassa on tilastoitu Jyväskylän säätietoja helmikuussa. Toteuta oheisen kuvan mukainen säätietojen analysointilaskentataulukko. Säätiedot voit keksiä itse.
   • Tiedot on laitettu neljään sarakkeeseen, joista näkyy päivämäärät, lämpötilat, pilvisyys ja säätila.
   • Taulukon oikealle puolelle on laskettu muutamia tilastollisia tietoja säätiloista. Laskentaan on käytetty COUNTIF (suom. LASKE.JOS)-, SUMIF (suom. SUMMA.JOS)-, AVERAGE (suom. KESKIARVO)-, MAX (suom. MAKS)- ja MIN (suom. MIN)-funktioita.
   • Mikä ongelma tulee esimerkiksi koko pilvisten päivien keskilämpötilojen ilmoittamisessa, jos pilvisiä päiviä ei ole ollenkaan? Miten voisit ratkaista ongelman?

   

2. Tee vastaava taulukko Tampereen säätietojen analysointiin.
3. Tee myös yksi laskentataulukko, jolle voit laskea yhteenvedon sekä Jyväskylän, että Tampereen tiedoista. Yhteenvedon tekemiseen kannattaa käyttää "kolmiulotteista" laskentaa, jossa tiedot lasketaan suoraan laskentataulukon läpi. Miten koostetaulukon kentät kannattaa sijoittaa!