Demo 4 - Taulukkolaskentafunktiot

Näissä demoissa jatketaan edellisissä demoissa tehdyn tuotetaulukon kehittämistä. Avaa tekemäsi tuotetaulukko tai hae tuotetaulukko osoitteesta <URL: http://appro.mit.jyu.fi/2001/kevat/ohjelmistot/demot/demo4/tuotetaulu.xls>. Ensimmäisellä kerralla voit tallentaa työkirjasi uudelle nimelle demo4.xls Muista tallentaa työkirjaasi riittävän usein!

1. Edellisten demojen lopussa lisättiin tuotetaulukkoon kolme nimettyä aluetta (kokonais, alennus ja alennettu). Nimettyjen alueiden perusteella laskettiin D-, E- ja F-sarakkeen yhteensä summat.

   Käytä nimettyjä alueita laskeaksesi E- ja F-sarakkeen tietoja. Nimettyjä alueita voi käyttää myös suhteellisesti, jolloin esimerkiksi solussa F4 oleva kaava saadaan muotoon kokonais-alennus. Tällöin lasketaan samalla rivillä nimetyn alueen sisällä olevien solujen erotus.

   
2. 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 mk, niin annetaan 10 prosentin alennus, muutoin alennuksen suuruus on 0 prosenttia.
   • Seuraavassa otetaan mukaan edelliseen kokonaan uusi alennusprosenttiraja.
     • Jos tuotteiden kokonaishinnan summa on suurempi tai yhtä suuri kuin 2000 mk, niin annetaan 15 prosentin alennus.
     • Jos tuotteiden kokonaishinnan summa on suurempi tai yhtä suuri kuin 1000 mk, mutta pienempi kuin 2000 mk, niin annetaan 10 prosentin alennus.
     • Muussa tapauksessa alennuksen suuruus on 0 prosenttia.
   • Seuraavassa edelliseen otetaan mukaan taas uusi alennusprosenttiraja.
     • Jos tuotteiden kokonaishinnan summa on suurempi tai yhtä suuri kuin 3000 mk, niin annetaan 20 prosentin alennus.
     • Jos tuotteiden kokonaishinnan summa on suurempi tai yhtä suuri kuin 2000 mk, mutta pienempi kuin 3000 mk, niin annetaan 15 prosentin alennus.
     • Jos tuotteiden kokonaishinnan summa on suurempi tai yhtä suuri kuin 1000 mk, mutta pienempi kuin 2000 mk, 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.
3. Sisäkkäisten IF-funktioiden toteuttaminen on kohtuullisen työlästä varsinkin alennusprosenttien lukumäärän kasvaessa. Seuraavaksi alennusprosenttien muodostuminen toteutetaan hieman yksinkertaisemmalla tavalla.
   • Lisää työkirjaasi uusi lomake, jolle teet oheisen kuvan mukaisen alennusprosenttitaulukon.
   • Nimeä lomake 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.
   
4. Kasvata tuotetaulukon tuotteiden kokonaishinta yli 6000 markkaan 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 mk. Testaa uuden alennusprosenttirajan toiminta huolellisesti.
5. Kuten huomasit, niin alennustaulukko täytyy olla järjestetty pienimmästä suurimpaa! Järjestämisen voit tehdä työkalupalkista löytyvällä järjestä nousevaan järjestykseen (engl. Sort Ascending)-painikkeen avulla. Ensiksi pitää kuitenkin valita järjestettävä alue.
6. 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?
7. Lisää soluun B1 päivämäärän ilmoittava TODAY (suom. TÄMÄ.PÄIVÄ)-funktio.
8. 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 lomake kertotaulua varten.
   • 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.
   
   
   
9. 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.
10. Seuraavaksi aloitetaan tekemään uusi taulukkolaskentasovellus, joka on tarkoitettu opiskelijoiden arvosanojen laskentaan.
    • Lisää työkirjaasi kaksi uutta lomaketta. Anna toiselle lomakkeelle nimi Tentti1 ja toiselle lomakkeelle nimi Arvosanat.
    • Lisää Tentti1-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 on pisterajat, joita vastaavat numeerisessa muodossa oleva arvosana on D-sarakkeessa. Tekstimuodossa oleva arvosana löytyy vastaavasti F-sarakkeesta. B-sarakkeen soluihin tulevan väliviivan eteen pitää laittaa heittomerkki('-).

      

    • Muotoile lisätyt lomakkeet oheisten kuvien mukaisiksi tyylejä käyttäen.
11. Seuraavaksi lisätään opiskelijan arvosanan laskeva kaava Tentti1-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.
12. 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 saat keskiarvon, minimin ja maksimin tekstiarvosanoista?

Lisätehtäviä

1. Oheisessa kuvassa on tilastoitu Jyväskylän säätietoja helmikuussa. Toteuta oheisen kuvan mukainen säätietojen analysointilomake. 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 lomake, 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!