Oppikerta 4.

Oppikerta 4. 1

Kaavojen käyttäminen. 1

Aikaisemmin luodun työkirjan etsiminen ja avaaminen tiedoston ominaisuuksien avulla. 2

Aiemmin luodun työkirjan avaaminen. 2

Työkirjan tallentaminen toisella nimellä. 4

Solujen ja solualueiden nimeäminen. 4

Solun tai solualueen nimeäminen vieressä olevilla soluilla. 4

Solualueen nimeäminen määrittämällä. 5

Solun tai solualueen nimeäminen nimiruudun avulla. 5

Laskentakaavojen kirjoittaminen. 6

Kaavan kirjoittaminen. 6

Kaavan kopioiminen muihin soluihin. 8

Lisätään kaava, joka laskee lukujen summan SUMMA-funktiolla. 8

Lisätään kaava, joka laskee arvonlisäveron. 9

Tekstiviestien näyttäminen laskulla. 13

Kirjoitetaan kaava, joka lisää laskuun tekstiviestin. 13

4. oppikerta: pikakertaus. 15

 

Kaavojen käyttäminen

• Aikaisemmin tehdyn työkirjan etsiminen tiedoston ominaisuuksien avulla.
• Solun tai solualueen nimeäminen
• Laskutoimituksessa käytettävän kaavan lisääminen laskentataulukkoon.
• Kaavan kopioiminen kaavasta toiseen.
• Yhteissumman laskeminen SUMMA-funktiolla.
• Tekstiviestien näyttäminen laskulla.

 

Mainostoimisto Naubiksen laskulomakkeen työstäminen jatkuu, Nyt on vuorossa laskun välisumma, arvonlisävero ja loppusumma. Jotta näitä lukuja ei olisi pakko laskea manuaalisesti laskimella, lomakkeeseen lisätään kaavoja, jotka hoitavat laskutoimitukset nopeasti.

 

Kaava määrittelee laskutoimituksen tai toimintosarjan, jonka ohjelma suorittaa laskeakseen tuloksen laskentataulukon johonkin soluun taulukon muihin soluihin kirjoitettujen tietojen perusteella. Excel sisältää myös joukon valmiiksi määritettyjä laskutoimituksia eli funktioita, jotka on nimetty niin, että niitä on helppo käyttää.

 

Kaikki Excelin kaavat alkavat yhtäsuurusmerkillä (=). Jos merkkiä ei kirjoiteta, kaavan teksti tulkitaan tavalliseksi tekstiksi, luvuiksi tai niiden yhdistelmäksi. Kaava voi sisältää soluviittauksen, joka yksilöi jonkin laskutoimituksessa käytettävän solun sen sarakkeen nimen ja rivinumeron avulla kuten esimerkiksi A1.

 

Kaavassa käytetty soluviittaus voi olla joko suhteellinen tai absoluuttinen. Suhteellinen soluviittaus muuttuu kaavan sisältävän solun mukaan. Toisin sanoen, jos kaava, joka sisältää suhteellisen soluviittauksen, kopioidaan tai siirretään toiseen soluun kaavassa oleva soluviittaus muuttuu automaattisesti uuden solun paikkaa vastaavaksi. Jos esimerkiksi solussa A3 on kaava, joka sisältää suhteellisen viittauksen soluun A1 ja tuo kaava kopioidaan soluun D3, soluviittaus muuttuu automaattisesti viittaamaan soluun D1.

 

Absoluuttinen soluviittaus ei muutu, kun kaava kopioidaan tai siirretään toiseen soluun. Toisin sanoen, jos solussa A3 on kaava, joka sisältää absoluuttisen viittauksen soluun A1 ja tuo kaava kopioidaan soluun D3, alkuperäinen viittaus soluun A1 säilyy.

 

Kaavojen käyttäminen tulee vielä helpommaksi ja nopeammaksi, jos soluille tai solualueille annetaan nimet. Nimiä käytettäessä ei kaavoja kirjoitettaessa tarvitse muistaa solujen sarakenimiä ja rivinumeroita. Toinen etu nimien käyttämisestä on, että solun nimeä käyttäen voidaan kaavasta viitata työkirjan missä tahansa laskentataulukossa olevaan soluun esimerkiksi taulukossa Taul1 olevan soluun voidaan viitata taulukossa Taul6 olevassa kaavasta.

 

Aikaisemmin luodun työkirjan etsiminen ja avaaminen tiedoston ominaisuuksien avulla

Nyt on aika avata työkirja, joka sisältää aiemmin tehdyn laskulomakkeen, mutta sen tiedostonimi on päässyt unohtumaan. Tiedossa on kuitenkin, että työkirjan ominaisuuksiin tallennettiin avainsana lasku, joten tiedosto voidaan etsiä ja avata tuota avainsanaa käyttämällä. Kun työkirja on avattu, sille voidaan antaa uusi nimi ja tallentaa se haluttuun paikkaan.

Aiemmin luodun työkirjan avaaminen

1. Napauta näytön alareunassa olevan Windows-tehtäväpalkin Käynnistä-painiketta. Käynnistä-valikko tulee näkyviin.
2. Valitse Ohjelmat ja sitten Microsoft Excel tai valitse suoraan Uusi Office-tiedosto ja sieltä Tyhjä työkirja.
3. Napauta vakiotyökalurivillä olevaa Avaa-painiketta. Avaa valintaikkuna tulee näkyviin.
4. Napauta Työkalut-painiketta ja sitten Etsi-painiketta. Etsi-valintaikkuna tulee näkyviin.

 

 

 

Valitse ikkunan oikeassa laidassa oleva Työkalut-painike ja sieltä komento Etsi.

 

5. Valitse Ominaisuudet-luetteloruudusta vaihtoehto Avainsanat.
6. Varmista, että Ehto-luetteloruudussa on valittuna vaihtoehto sisältää sanat.
7. Kirjoita Arvo-muokkausruutuun Lasku ja napauta Lisää luetteloon-painiketta.
8. Valitse kovalevy Kohde-luetteloruudusta.
9. Valitse Etsi alikansioista-valintaruutu ja napauta Etsi-painiketta. Excel etsii kaikista kovalevyllä olevista tiedostoista avainsanaa Lasku. Haun tulokset näkyvät Avaa-valintaikkunaan.

 

Näytön tulisi olla seuraavan kuvan kaltainen.

Työkirjan tallentaminen toisella nimellä

Tässä harjoituksessa harjoitustiedosto Naubis.harj_lasku tallennetaan nimellä Naubis_lasku.

 

1. Valitse tiedosto-valikosta komento Tallenna nimellä. Tallenna nimellä-valintaikkuna tulee näkyviin ja Tiedoston nimi-muokkausruudussa näkyy alkuperäinen tiedostonimi Naubis.harj_lasku.
2. Kirjoita Tiedoston nimi-muokkausruutuun Naubis_Lasku ja napauta Tallenna-painiketta. Uusi nimi Naubis_Lasku on nyt työkirjan otsikkorivillä.

 

Kaavojen tekeminen helpommin käytettäväksi ja ymmärrettäväksi

Seuraavaksi annetaan nimet kaavoissa käytettäville soluille ja solualueilla. Näin kaavojen kirjoittaminen tulee huomattavasti helpommaksi.

Solut voidaan nimetä monella tavalla, joten kannattaa valita menetelmä, joka parhaiten sopii käsiteltävään tehtävään. Voidaan esimerkiksi käyttää sarake- ja riviotsikoita, jotka ilmentävät parhaiten vierekkäisissä soluissa olevia tietoja. Voidaan myös antaa kuvaavia nimiä, joiden ansioita kaava, jossa nimeä käytetään, tulee helpoimmin ymmärrettäväksi.

 

Solujen ja solualueiden nimeäminen

Seuraavassa muutamia asioita, jotka kannattaa pitää mielessä, kun soluja tai solualueita nimetään:

 

• Nimen tulee alkaa kirjaimella.
• Nimi voi sisältää numeroita kunhan ne ovat kirjainten tai alleviivan perässä. Huomaa, että jos ensimmäinen merkki on numero, Excel lisää automaattisesti sen eteen alleviivamerkin.
• Sanojen tai numeroiden välissä olevat välilyöntimerkit korvataan alleviivalla (_) kuten Laskun_loppusumma.
• Nimi ei saa olla soluviite kuten esimerkiksi A2.
• Nimi voi olla vain yksi kirjain lukuun ottamatta R ja C.

Solun tai solualueen nimeäminen vieressä olevilla soluilla

Tässä harjoituksessa annetaan nimet soluille, jotka sisältävät välisumman, arvonlisäveron ja laskun loppusumman.

 

1. Valitse solut D24-D26 jaa E24:E26.
2. Valitse Lisää-valikosta komento Nimi ja sitten Luo. Luo nimet –valinta ikkuna tulee näkyviin.

 

 

3. Vasemmasta sarakkeesta –valintaruutu ja napauta OK-painiketta.
4. Napauta solua E24. Kuten huomaat, nimi Välisumma näkyy kaavarivin vasemmassa reunassa olevassa nimiruudussa.

 

 

5. Naputa solua E25 ja sitten solua E26. Jokaisen solun nimi on sama kuin nimisarakkeessa oleva otsikko.

 

Solualueen nimeäminen määrittämällä

 

Tässä harjoituksessa annetaan solualueelle sen käyttötarkoitusta kuvaava nimi.

 

1. Valitse solut D11:D23.
2. Valitse Lisää-valikosta komento Nimi ja sitten Määritä. Määritä -valintaikkuna tulee näkyviin.
3. Kirjoita Yksikköhinta ja napauta OK-painiketta. Nimi Yksikköhinta tulee näkyviin vasemmassa reunassa olevaan nimiruutuun.

 

 

Solun tai solualueen nimeäminen nimiruudun avulla

Tässä harjoituksessa nimetään solualue nimiruudun avulla.

 

1. Napauta solua E11 ja vedä hiiren osoitin soluun E23. Suorakaiteen muotoinen alue on nyt valittuna.
2. Napauta nimiruutua. Ensimmäinen valittuna olevan solun E11 soluviite näkyy nimiruudussa.
3. Kirjoita nimiruutuun Rivisumma ja paina Enter-näppäintä. Solualueen nimi on nyt Rivisumma.
4. Napauta nimiruudun oikeassa reunassa olevaa nuolenpäätä, jolloin näkyviin tulee kaikki edellä määritetyt nimet sisältävä luettelo. Luettelon tulisi nyt olla seuraavan kuvan kaltainen.

 

 

 

 

5. Valitse luettelosta nimi Yhteensä. Solu nimeltä Yhteensä tulee aktiiviseksi.

 

 

Laskentakaavojen kirjoittaminen

Excel-laskentakaavioilla voidaan suorittaa soluissa olevilla arvoilla muun muassa sellaisia laskutoimituksia kuten yhteenlasku, vähennyslasku, kertolasku ja Jakolasku. Laskutoimitukset määritetään kirjoittamalla kaavoihin matemaattisia operaattoreita kuten plusmerkki (+), miinusmerkki (-), kertomerkki (*) tai jakomerkki (/).

Kaavassa voidaan käyttää myös funktiota, valmiiksi määritettyjä toimintasarjoja, jotka säästävät aikaa kaavoja kirjoitettaessa. Funktio helpottaa käsillä olevan tehtävän kuvaamista ja siihen tavallisesti liittyy yksi tai useampi argumentti. Argumenttien avulla funktiolle annetaan tiedot. Esimerkiksi SUMMA-funktio, laskee yhteen kaikki solualueen sisältämät luvut. Seuraavissa harjoituksissa kirjoitetaan yksinkertainen kaava, joka suorittaa kertolaskun laskussa olevilla luvuilla. Sitten kaava kopioidaan muihin soluihin ja lopuksi lasketaan solun yhteissumma SUMMA-funktiota käyttäen.

 

Kaavan kirjoittaminen

Tässä harjoituksessa kirjoitetaan yksinkertainen kaava, jolla lasketaan työstä laskutettava summa kertomalla työtunnit tuntihinnalla.

 

1. Napauta solua A11, kirjoita Web-sivujen ylläpito ja paina sarkainnäppäintä.
2. Kirjoita soluun B11 Huppari-mainoksen tekeminen ja paina sarkanäppäintä.
3. Kirjoita soluun C11 luku 5 ja paina sarkainnäppäintä.
4. Kirjoita soluun D11 luku 26 ja paina sarkainnäppäintä. Soluun D11 tulee luku 26,00.
5. Kirjoita soluun E11 yhtäsuuruusmerkki (=) ja ryhdy kirjoittamaan kaavaa.
6. Napauta solua C11 ja katso, mitä näkyy solussa E11. Soluun E11 tulee näkyviin soluviittaus yhtäsuuruusmerkin perään. Näytön tulisi nyt olla seuraavan kuvan kaltainen.

 

 

 

7. Kohdistimen ollessa edelleen solussa E11, kirjoita *-merkki, joka tarkoittaa kertolaskun matemaattista operaattoria.
8. Napauta solua D11 ja kuten huomaat, soluviittaus D11 tulee näkyviin solun E11 kertolaskumerkin perään. Paina Enter-näppäintä. Soluun E11 tulee näkyviin kertolaskun tulos 130,00.
9. Napauta solua E11 ja katso kaavariville. Kaava näkyy nyt kaavarivillä, mutta solussa näkyy edelleen luku 130. Näytön tulisi olla seuraavan kuvan kaltainen.

 

 

 

 

Kaavan kopioiminen muihin soluihin

Tässä harjoituksessa kopioidaan edellisessä harjoituksessa kirjoitettu kaava laskun muihin soluihin.

1. Varmista, että solu E11 on aktiivisena.
2. Aseta hiiren osoitin solun E 11 oikeaan alanurkkaan ja odota , kunnes automaattisen täyttökahvan päälle tulee musta pieni plusmerkki.
3. Vedä automaattista täyttökahvaa alaspäin soluun E23 asti. Solussa E11 oleva kaava kopioidaan valitun alueen kaikkiin soluihin. Näytön tulisi nyt olla seuraavan kuvan kaltainen.

 

 

4. Napauta solua E12 ja katso, mitä tapahtuu kaavarivillä. Kaavassa olevat soluviittaukset koskevat nyt riviä 12 eikä riviä 11 kuten solussa E11.

 

Jos halutaan kopioida jossakin solussa oleva kaava toiseen paikkaan laskentataulukossa, asetetaan hiiren osoitin kaavan sisältävän solun reunaviivan päälle, painetaan Ctrl-näppäin alas ja vedetään kaava toiseen soluun.

 

Lisätään kaava, joka laskee lukujen summan SUMMA-funktiolla

Tässä harjoituksessa lisätään laskuun kaava Summa-painiketta käyttämällä.

 

1. Valitse nimiruudun luetteloruudusta nimi Välisumma. Solu E24, joka sijaitsee otsikon Välisumma oikealla puolella, tulee aktiiviseksi.

 

         

 

 

2. Napauta vakiotyökalurivillä olevaa Summa-painiketta. Liikkuvan katkoviivan muodostama kehys ilmestyy kaavassa käytettävien solujen ympärille. Solu E24 sisältää kaavan SUMMA. Kaavassa käytetty argumentti sisältää automaattisesti solualueelle annetun nimen Rivisumma soluviittauksen E11:E23 asemasta. Näytön tulisi olla seuraavan kuvan kaltainen.

 

 

 

3. Paina Enter-näppäintä. Soluun E24 tulee näkyviin laskutoimituksen tulos kaavan sijaan.

 

Lisätään kaava, joka laskee arvonlisäveron

 

Tässä harjoituksessa kirjoitetaan kaava, joka laskee välisummalle arvonlisäveron 22%. Sen jälkeen kirjoitetaan kaava loppusummalle.

 

1. Napauta solua B25, kirjoita ALV-% ja paina sarkainnäppäintä.
2. Kirjoita soluun C25 0,22 ja paina Enter-näppäintä.
3. Valitse nimiruudun luettelosta nimi Alv. Solu E25, jonka nimi on Alv tulee aktiiviseksi.
4. Kirjoita yhtäsuuruusmerkki (=) ja napauta solu E24. Nimi Välisumma lisätään kaavaan. Näytön tulisi nyt olla seuraavan kuvan kaltainen.

 

 

5. Kirjoita kertomerkki (*) ja napauta solua C25. Solussa C25 oleva veroprosentti lisätään kaavaan. Näytön tulisi nyt olla seuraavan kuvan kaltainen.
6. Paina Enter-näppäintä Excel laskee arvonlisäverson ja tulos 28,60 tulee näkyviin kaavan tilalle.
7. Kirjoita yhtäsuurimerkki (=) ja napauta solua E24. Kaavaan lisätään nimi Välisumma.
8. Kirjoita plusmerkki (+) ja napauta solu aE25. Solun nimi Alv lisätään kaavaan plusmerkin perään. Näytön tulisi nyt olla seuraavan kuvan kaltainen.
9. Paina Enter-näppäintä. Tulos 158,60 tulee näkyviin soluun kaavan tilalle. Näytön tulisi nyt olla seuraavan kuvan kaltainen.

 

 

 

 

Tekstiviestien näyttäminen laskulla

Mainostoimisto Naubiksen tapana on ollut antaa 2% alennus yli 5000 mk laskuista asiakkaille, jotka maksavat tietyn ajan kuluessa ja sen vuoksi laskuun halutaan lisätä teksti., joka muistuttaa asiakkaita tästä. Seuraavassa harjoituksessa kirjoitetaan kaava, joka lisää muistutuksen vain niihin laskuihin, joiden loppusumma on yli 5000 mk.

 

Kirjoitetaan kaava, joka lisää laskuun tekstiviestin

Tässä harjoituksessa kirjoitetaan kaava, joka JOS-funktiota käyttäen lisää laskuun muistutuksen, jos laskun loppusumma on yli 5000 mk.

 

1. Valitse solut A30:E30.
2. Napauta Muotoilu-työkalurivillä oleva Yhdistä ja keskitä –painiketta. Solut yhdistetään ja tasataan keskelle.
3. Napauta kaavarivillä olevaa Muokkaa kaavaa –painiketta. Kaavaikkuna tulee näkyviin. Kaavarivin vasemmassa reunassa näkyy luetteloruutu, joka sisältää funktioiden nimet.
4. Valitse luettelosta nimi JOS. Kaavaikkunaan tulee näkyviin muokkausruutuja, joiden avulla määritetään JOS-funktion argumentti.

 

 

Kaavaikkuna

Kaavaikkunan avauspainike

Kaavarivi

Kaavapaletti

 

5. Napauta solua E26. Totuus_testi-muokkausruutuun tulee näkyviin nimi Yhteensä.
6. Kirjoita >5000 ja paina sarkainnäppäintä.
7. Kirjoita Arvo_jos_tosi-muokkausruutuun ”Yli 5000 mk:n laskuista myönnetään 2% alennus, jos ne maksetaan 30 päivän aikana.” Muista kirjoittaa lainausmerkit. Paina sarkainnäppäintä.
8. Kirjoita Arvo_jos_epätosi-muokkausruutuun kaksi lainausmerkkiä (””) ilman välilyöntiä. Näytön tulisi nyt olla seuraavan kuvan kaltainen.

 

 

 

9. Napauta OK-painiketta. Jos tämän jälkeen loppusumma on yli 5000 mk, yllä kirjoitettu viesti kirjoitetaan automaattisesti riville 30.