Taitajatehtävä 2009:Taulukko Ohje
Koe 1 Koe 2
Excel-tehtävät
1) Avaa taulukko Alennus.xls.
Valitse solu A2 ja sitten Kaavat, Määritä nimi.
Valitse solu F10 ja sitten Kaavat, Määritä nimi.
Valitse alue A4:F9 ja sitten Kaavat, Luo valinnasta.
Kokeile alueen valintaa nimen perusteella painamalla F5.
Kirjoita kaavat käyttäen aluenimiä. Valitse sopiva alue,
kirjoita kaava nimiä käyttäen ja paina Ctrl-Enter.
Valitse solut A5:D9 ja A2 (Ctrl-valinta).
Poista valittujen solujen lukitus Muotoile solut -valintaikkunan Suojaus -välilehdeltä.
Suojaa taulukko.
Tyhjennä tuotetiedot.
Tallenna mallina.
Palauta malli osoitteeseen heikkilaakso@yahoo.com. Muista lisätä tehtävän numero!
2) Hae työkirja maat.xls. Valitse kaikki solut komennolla Ctrl-Vaihto-End. Tee aluenimet komennolla Kaavat, Luo valinnasta. Valitse Yläriviltä ja paina Ok. Valitse alue Asukastiheys painamalla ensin F5. Kirjoita kaava =Väkiluku / Ala ja paina Ctrl-Enter.
Siirry taulukkoon Taul2. Lisää 1. riville sarakeotsikot Maanosa Valtioita Pinta-ala Asukasluku. Kirjoita A2-sarakkeesta allekkain maanosien nimet Aasia, Afrikka, Etelä-amerikka, Euraasia, Eurooppa, Oseania ja Pohjois-amerikka.
Siirry soluun B2 ja kirjoita
kaava =LASKE.JOS(Maanosa;A2).
Siirry soluun C2 ja kirjoita kaava =SUMMA.JOS(Maanosa;A2;Ala).
Siirry soluun D2 ja kirjoita kaava =SUMMA.JOS(Maanosa;A2;Väkiluku).
Valitse solut B2:D2 ja kopioi ne täyttökahvalla alaspäin kaikkiin maanosiin. Valitse solut B9:D9 ja lisää automaattinen summa. Mitä luvut kertovat?
Lajittele Taul1 maanosan perusteella. Lisää välisummat, jotka laskevat alat ja väliluvut Tiedot, Jäsennä, Välisumma. Lisää välisumma, joka laskee valtioiden määrän. Tulosten pitäisi vastata Taul2:n tuloksia.
Tehdään sama Pivot-taulukolla. Poista ensin välisummat. Vie soluosoitin luettelon sisälle ja valitse Lisää, Taulukot, Pivot-taulukko. Napsauta Maanosa-ruutua. Napsauta sitten Pinta-ala ja Väkiluku-ruutuja. Vedä Valtio arvokenttään.
Palauta työkirja osoitteeseen heikkilaakso@yahoo.com. Muista lisätä tehtävän numero!
3) Hae taulukko Kumulatiivinen pohja.xlsx Tee kaavat mahdollisimman tehokkaasti.
Palauta työkirja osoitteeseen heikkilaakso@yahoo.com. Muista lisätä tehtävän numero!
4) Tee allaoleva taulukko. Miten tekisit kursivoituihin soluihin kaavat mahdollisimman tehokkaasti?
| Alennusprosentit | |||||
| Hinta | 10 % | 20 % | 30 % | 40 % | 50 % |
| 100 | 90 | 80 | 70 | 60 | 50 |
| 200 | 180 | 160 | 140 | 120 | 100 |
| 300 | 270 | 240 | 210 | 180 | 150 |
| 400 | 360 | 320 | 280 | 240 | 200 |
| 500 | 450 | 400 | 350 | 300 | 250 |
| 600 | 540 | 480 | 420 | 360 | 300 |
| 700 | 630 | 560 | 490 | 420 | 350 |
| 800 | 720 | 640 | 560 | 480 | 400 |
| 900 | 810 | 720 | 630 | 540 | 450 |
| 1000 | 900 | 800 | 700 | 600 | 500 |
Palauta työkirja osoitteeseen heikkilaakso@yahoo.com. Muista lisätä tehtävän numero!
5) Hae työkirja koulukone.xls. Taulukko on purjelento-opetuksessa käytetyn kaksipaikkaisen ASK-21 koulukoneen lentopäiväkirja. Kaikki lennot pitää tilastoida ja raportoida ilmailuviranomaisille vuosittain . Anna ensin sarakkeille nimet. Sen jälkeen alueet voi valita F5-näppäimellä kaavoja ja muotoiluja varten.
Lisää sarakkeeseen G kaava joka laskee ilmassaoloajan. Solussa H1 on koneen kokonaisilmassaoloaika edellisen vuoden lopussa.
Lisää sarakkeeseen H kaava jossa kokonaisilmassaoloaika lisääntyy kumulatiivisesti.
Sarakkeiden G ja H muotoilu on oma ja sen muoto on [t]:mm. Tämä muotoilu ei nollaa tunteja 23 jälkeen.
Lisää sitten yhteenvetokaavat alla olevan mallin mukaan. Käytä funktioita SUMMA.JOS ja LASKE.JOS.
| Lennot | Määrä/kpl | Tunnit |
| KOE | 1 | 0:18 |
| KOU | 410 | 133:06 |
| HAR | 75 | 22:49 |
| TAR | 10 | 3:56 |
| TAI | 1 | 0:15 |
| YLE | 21 | 10:48 |
| Yhteensä | 518 | 171:12 |
Kopioi lentopäiväkirja uuteen taulukkoon ja laske pivot-taulukolla päälliköiden lentotunnit ja lentojen määrät. Laske myös lentotunnit ja lentojen määrä kunakin päivänä.
Palauta työkirja osoitteeseen heikkilaakso@yahoo.com. Muista lisätä tehtävän numero! Tärkeä tehtävä.
6) Tee Alejos.xlt kaltainen malli. Käytä kaavoissa aluenimiä ja suojaa solut ilman
salasanaa.
Palauta malli osoitteeseen heikkilaakso@yahoo.com. Muista
lisätä tehtävän numero!
7) Tee Alephaku-1.xlt kaltainen malli. Käytä kaavoissa aluenimiä ja suojaa solut ilman
salasanaa.
Palauta malli osoitteeseen heikkilaakso@yahoo.com. Muista
lisätä tehtävän numero!
8) Tee Alephaku-2.xlt kaltainen malli. Käytä kaavoissa aluenimiä ja suojaa solut ilman
salasanaa.
Palauta malli osoitteeseen heikkilaakso@yahoo.com. Muista
lisätä tehtävän numero!
9) Hae työkirja viikko.xls. Valitse ensin taulukko ma ja sitten vaihto-näppäin pohjassa viikko. Kirjoita keskiarvokaavat soluihin F2:F11 ja B12:E12. Lisää soluun F12 keskiarvokaava alueelta B2:E11. Napsauta viikko-taulukkoa ja lisää kolmiulotteiset kaavat alueelle B2:E11. Kaavan muoto solussa B2 on =KESKIARVO(ma:pe!B2).Kaavan voi tietysti kirjoittaa kaikkiin soluihin kerralla.
Palauta työkirja osoitteeseen heikkilaakso@yahoo.com. Muista
lisätä tehtävän numero!
10) Hae työkirja vuosi.xls. Kirjoita taulukoihin Tammi:Joulu alla olevat testitiedot ja summat. Lisää Yhteensä-taulukkoon koko vuoden myynnit kolmiulotteisilla kaavoilla. Suojaa otsikot ja kaavat, tyhjennä tiedot ja tallenna mallina.
| Nuottakauppa Oy:n myynti | Helsinki | Tampere | Turku | Oulu | Summa |
|---|---|---|---|---|---|
| Tietokoneet | 100000 | 75000 | 75000 | 50000 | |
| Kamerat | 50000 | 37500 | 37500 | 25000 | |
| Kodinkoneet | 100000 | 50000 | 50000 | 50000 | |
| Summa |
Palauta malli osoitteeseen heikkilaakso@yahoo.com. Muista lisätä tehtävän numero!
Bonustehtävät
11) Tee lainanhoitomalli, jossa on mahdollisuus laskea 12 erää 20 vuoden aikana tasaeränä ja tasalyhenteisenä. Esimerkki.
| Alkupääoma | Korko yhteensä | Korko yhteensä | ||||
| Korkokanta | ||||||
| Vuodet | ||||||
| Erät | ||||||
| Tasaerä | Tasalyhennys | |||||
| Erä | Korko | Lyhennys | Pääoma | Korko | Lyhennys | Pääoma |
| 1 | ||||||
| 2 |
Palauta malli osoitteeseen heikkilaakso@yahoo.com. Muista lisätä tehtävän numero!
12) Tee lainanhoitomalli, jossa on mahdollisuus laskea 12 erää 20 vuoden aikana tasalyhenteisenä. Korkokanta voi muuttua ja tarvittaessa voi pitää lyhennysvapaita jaksoja. Esimerkki.
Access-tehtävät
13) Tee Yritys-niminen tietokanta, jossa on kolme taulukkoa, yhteydet ja taulukoiden käyttöön liittyvät lomakkeet. Lomakkeet käynnistetään päävalikosta. Lisää raportti, joka tulostaa tehdyt kaupat. Pohja.
14) Tee tietokanta, johon lisäät ulkoiset tiedot asiakkaista ja tuotteista. Lisää tietokantaan taulukot Tilaukset ja Tilausrivit ja lisää hakusarakkeet. Tilaukset-taulukko mahdollistaa monen tuotteen tilaamisen samalla kertaa. Yhteydet ovat alla olevan esimerkin näköiset. Toolshop.mdb.
15) Lisää edelliseen tietokantaan lomakkeet ja tee siitä sovellus. Palauta tietokanta osoitteeseen heikkilaakso@yahoo.com. Muista lisätä tehtävän numero! Tärkeä tehtävä.
16) Toteuta alla olevan rakenteen mukainen tietokanta. Suunnittele taulukot, yhteydet, lomakkeet ja päävalikko. Lisää kaksi tuoteryhmää, neljä tuotetta, neljä asiakasta, kolme toimittajaa sekä viisi ostoa ja myyntiä. Palauta.
VBA
VBA-Tehtävät
17) Aki Taanilan materiaalin harjoitukset.
18) Tee Ratolin tehtävät VBA-kielellä.
19) Harjoittele makrojen nauhoittamista Asta Havaskarin taulukkolaskentaharjoituksilla 1, 6, 11 ja 15. Tutustu koodiin ja siivoa siitä tarpeettomat osat.
20a) Tutki ja kokeile koodit työkirjassa nimet.xls. Miten estät tietojen katoamisen?
20b) Hae Asiakkaat.xls. Tee makro, joka lisää rivin ja siirtää osoitetiedot tyhjälle riville. Muokkaa koodia niin, että loput tiedot siirretään silmukalla. Tee sitten sama temppu toisin päin ja palauta koodi sähtöpostiin.
21) Tee lomake, jossa syötetään pituus, leveys ja korkeus. Painikkeella lasketaan tilavuus.
22a) Tee sovellus, joka laskee auton polttoaineen kulutuksen l/100 km ja polttoainekustannukset/km. Syöttötietona lomakkeessa on kilometrit, litrat ja litrahinta.
22b) Tee lomakkeesta automaattisesti käynnistyvä.
22c) Lisää sovellukseen ominaisuus, joka lisää annetut tiedot taulukkoon.
22d) Muuta ohjelma sellaiseksi, että laskenta tehdään soluissa.
23a) Tee pankkisimulaattori. Lomakkeella on painikkeet saldo ja otto. Saldo luetaan taulukosta ja otto lisää taulukkoon rivin, jossa on uusi saldo.
23b) Lisää pankkisimulaattoriin tunnusluvun tarkistus.
24a) Tee makro, joka aktivoi 20 x 20 solun alueen suurimman solun.
24b) Tee makro, joka värittää 20 x 20 solun alueen suurimman solun siniseksi ja pienimmän punaiseksi.
24c) Tee ohjelma, joka arpoo 100 satunnaislukua väliltä 1-10 ja tallentaa taulukkoon, kuinka monta kertaa kukin luku esiintyy.
24d) Tee makro, joka värittää valitun alueen suurimman solun siniseksi ja pienimmän punaiseksi.
25a) Tee Ventti -peli. Satunnaisluvun saa funktiolla rnd(). Satunnaisluvut 1-13 saa: luku = int(rnd()*13+1)
25b) Tee kivi-paperi-sakset -peli.
25c) Tee jatsipeli, miinaharava, oma sovellus tai kaikki.
26) Tee ajanottojärjestelmä. Taulukkoon tarvitaan osallistujan nimi, lähtöaika ja saapumisaika. Ajat saadaan funktiolla now(). Tee lopuksi tulostaulu.
27) Muokkaa lainataulukkoa siten, että Lainan tiedot syötetään lomakkeella ja maksutapa valitaan painikkeilla.
28) Tee kortistotyyppinen sovellus, jossa on tietueen lisäys, muokkaus ja poisto. Lisäksi tietueet voidaan lajitella eri kenttien perusteella. Lisää myös haku. Käytä apuna Excelin luettelo-ominaisuuksia.
29) Tee sovellus, joka kysyy kaksi IP-osoitetta, aliverkon peitteen ja ilmoittaa, ovatko IP:t samassa aliverkossa. Tarkista IP-osoitteiden laillisuus. Lisätietoa.
30a) Tee sovellus, joka saa verkon osoitteen ja aliverkon peitteen, ilmoittaa aliverkkojen määrän ja niiden IP-osoitteet sekä verkossa olevien koneiden määrän.
30b) Muuta sovellusta siten, että se hallitsee myös yliverkotuksen. Lisätietoa.
VBA-ratkaisut
18/1)
Sub testi()
pituus = Val(InputBox("Kirjoita pituus"))
Do Until pituus > 0
pituus = Val(InputBox("Kirjoita pituus"))
Loop
Leveys = Val(InputBox("Kirjoita leveys"))
Do Until Leveys > 0
Leveys = Val(InputBox("Kirjoita leveys"))
Loop
Korkeus = Val(InputBox("Kirjoita korkeus"))
Do Until Korkeus > 0
pituus = Val(InputBox("Kirjoita korkeus"))
Loop
MsgBox ("Tilavuus on: " & pituus * Leveys * Korkeus)
End Sub
18/2 a)
Sub kymppi()
Dim Luvut(1 To 10) As Integer
summa = 0
For i = 1 To 10
Luvut(i) = InputBox
("Kirjoita " & i & ". luku:")
summa = summa + Luvut(i)
Next i
MsgBox (summa / 10)
End Sub
18/2 b)
Sub kymppi()
Dim Luvut(1 To 10) As Integer
summa = 0
Luvut(1) = InputBox("Kirjoita " & 1 & ". luku:")
summa = summa + Luvut(1)
pienin = summa
suurin = summa
For i = 2 To 10
Luvut(i) = InputBox("Kirjoita " & i & ". luku:")
summa = summa + Luvut(i)
If Luvut(i) > suurin Then suurin = Luvut(i)
If Luvut(i) < pienin Then pienin = Luvut(i)
Next i
MsgBox ("Keskiarvo: " & summa / 10 & ", pienin: " & pienin & ", suurin: " & suurin)
End Sub
18/2 c)
Sub kymppi()
Dim Luvut(1 To 10) As Integer
For i = 1 To 10
Cells(i, 1).Value = InputBox(i & ". luku:")
Next i
MsgBox ("ka: " & Cells(11, 1).Value & ", Pi: " & Cells(12, 1).Value & ", Su: " &Cells(13, 1).Value)
End Sub
18/3 a)
Sub TunnusluvunTarkastus()
Tunnusluku = "1234"
Arvaus = InputBox("Anna tunnusluku")
For i = 1 To 2
If Tunnusluku = Arvaus Then
MsgBox ("Oikein")
Exit For
Else
Arvaus = InputBox("Väärin,anna tunnusluku")
End If
Next i
End Sub