Soluviittaukset

Soluviittausten avulla tehtävät laskutoimitukset ovat huomattavasti joustavampia ja monipuolisempia kuin pelkillä numeroarvoilla tehtävät laskutoimitukset. Jokaiseen laskentataulukossa olevaan soluun voidaan viitata yksikäsitteisesti solun sarake- ja rivi-indeksien avulla, kuten esimerkiksi A1. Tavallisissa kaavoissa lasketaan vakionumeroarvoilla, joiden lukujen muuttaminen on hieman työlästä kaavojen muuttuessa. Soluviittausten avulla laskettaessa ei tarvitse muuttaa itse laskentakaavaa, vaan voidaan muuttaa pelkästään niiden solujen arvoja, joista laskenta suoritetaan. Lisäksi kerran hyvin kirjoitettu kaava on kopioitavissa tai siirrettävissä myös muihin laskentataulukon soluihin. Soluarvoilla toteutettu kaava on siis paljon yleiskäyttöisempi kuin kiinteillä numeroarvoilla kirjoitettu laskutoimitus.

Soluviittauksen tekeminen onnistuu taulukkolaskentaohjelmissa helposti. Microsoft Excelissä soluun kirjoitetaan ensiksi kaavan aloitusmerkki eli yhtä suuruus merkki (=) ja välittömästi tämän jälkeen voidaan valita hiiren tai näppäimistön avulla solu, johon halutaan viitata. Kun solu on valittuna voidaan soluviittaus (=B16) hyväksyä ENTER-näppäimellä tai kaavan kirjoittamista voidaan jatkaa näppäimistön avulla. Kaikki kaavoihin tulevat solut voidaan siis valita hiirellä tai näppäimistöllä kaavan kirjoittamisen aikana.

Soluviittaukset laskennassa

Kuva 8. Soluviittaukset laskennassa

Oheisessa kuvassa lasketaan riveittäin F- ja G-sarakkeessa olevien solujen summa ja tulo. Kun kaavat on toteutettu soluviittauksilla, niin muutettaessa F- ja G-sarakkeessa olevia lukuja muuttuvat myös H-sarakkeessa olevat lopputulokset.

Suhteelliset soluviittaukset

Suhteellinen viittaus on viittaamista sen paikan suhteen, jossa sillä hetkellä ollaan. Jokapäiväisessä elämässä käytettävät sanonnat Mene tuonne! tai Tule tänne ovat täysin sanojan paikasta riippuvaisia sanontoja. Suhteelliset soluviittaukset toimivat pitkälti samalla tavoin. Soluviittauksissa suhteellisuus ei kuitenkaan tule näkyville niin selkeästi, koska aina viitataan johonkin tiettyyn soluun. Vasta kopioimisen yhteydessä soluviittauksen suhteellisuus tulee ilmi. Seuraavassa lyhyt esimerkki suhteellisesta soluviittauksen käytöstä.

Kun kirjoitetaan soluun A1 soluviittaus =D1 viitataan oikeasti A1-solun oikealla puolella D-sarakkeessa ja samalla rivillä sijaitsevaan soluun. Kun soluviittaus kopioidaan esimerkiksi solusta A1 soluun B2, niin soluviittaus muuttuu muotoon =E2. Kyseessä on siis puhtaasti paikasta riippuvainen viittaus! Soluviittauksen suhteellisuus tulee esille vasta kopioinnin yhteydessä.

Suhteellisia soluviittauksia

Kuva 9. Suhteellisia soluviittauksia

Oheisen kuvan esimerkissä käytetään hyväksi suhteellisella soluviittauksella tapahtuvaa laskentaa. Esimerkissä on hintataulukko, jossa lasketaan ostoksista kertyviä kustannuksia tuotteittain. A-sarakkeessa on näkyvillä tuotteen nimet, joita vastaavat yksikköhinnat on sijoitettu B-sarakkeeseen. C-sarakkeeseen on määritelty tuotteen kappalemäärä. D-sarakkeessa lasketaan tuotteille kokonaishintaa, joka muodostuu tuotteiden yksikköhinnan ja kappalemäärän tulona. Solussa oleva kaava on määriteltynä suhteelliseksi, jolloin se on muotoa . Kyseessä on kaava, joka laskee samalta riviltä kahden D-sarakkeen vasemmalla puolella olevan solun tulon. Kun kaavaa kopioidaan D-sarakkeessa alaspäin, niin kaava muuttuu sen mukaisesti. Esimerkiksi soluun tuleva kaava muuttuu kopioitaessa muotoon .

Suhteelliset soluviittaukset ovat erityisen hyödyllisiä erilaisten laskentataulukkojen tekemisessä. Kaavan kopioiminen on helppoa koko taulukon alueelle, joten säästytään samankaltaisten kaavojen kirjoittamiselta. Yleensä taulukkolaskennassa voi vähentää paljon työmääräänsä suunnittelemalla kaavat mahdollisimman yleiskäyttöisiksi. Kaikissa tapauksissa ei tietenkään voi käyttää suhteellisia soluviittauksia, joten tarvitaan myös absoluuttisia soluviittaukseksi, joista esitellään seuraavassa luvussa.

Absoluuttiset soluviittaukset

Absoluuttinen viittaus tarkoittaa yksikäsitteistä paikasta riippumatonta viittausta. Jokapäiväisessä elämässä käytetään absoluuttista viittausta esimerkiksi sanottaessa: Tavataan Agoran luento Auditorio 1:n edessä. Tietenkin sanontaan voisi lisätä tarkemmin kaupungin ja maan nimen, mutta idean pitäisi selvitä jo esimerkistä. Absoluuttinen viittaus kertoo paikan, jossa keskustelun osapuolien on määrä tavata, mutta keskustelijoiden olinpaikasta riippumattomasti. Absoluuttinen viittaus voidaan siis tehdä missä tahansa paikassa ilman erehtymisen vaaraa.

Soluviittauksen absoluuttisuus, kuten myös suhteellisuus, tulee ilmi myös vasta kopioinnin yhteydessä. Absoluuttinen soluviittaus saadaan esimerkiksi kirjoittamalla soluviittaus =$D$1 soluun A1. Tällöin viitataan viittaavan solun paikasta riippumattomasti aina soluun D1. Jos solun A1 sisältö kopioidaan soluun B2, niin solussa B2 oleva viittaus on edelleen muodossa =$D$1. Soluviittaus soluun D1 säilyy kopioidessakin, joten kyseessä on varmasti absoluuttinen soluviittaus. Edellisen esimerkin absoluuttisessa soluviittauksessa $D$1 on mukana absoluuttisuuden kertovat dollarimerkit ($). Dollarimerkillä voidaan kiinnittää soluviittauksen sekä sarake- että rivi-indeksi siten, ettei se muutu kaavan kopioinnin yhteydessä.

Absoluuttinen soluviittaus on oikein käytettynä yhtä tehokas työkalu kuin suhteellinen soluviittaus. Oheisessa kuvassa on tyypillinen esimerkki absoluuttisen soluviittauksen käytöstä. Kyseessä on vaatekaupan lasku, jolla on kolme tuotetta. Tuotteiden nimet ovat A-sarakkeessa ja tuotetta vastaava yksikköhinta on B-sarakkeessa. Tuotteen kappalemäärät ovat vastaavasti C-sarakkeessa. D-sarakkeessa lasketaan tuotteen alennus, joka muodostuu B1-solussa olevan alennusprosentin mukaan sekä tietysti tuotteen yksikköhinnasta ja kappalemäärästä. Kokonaishinta muodostuu vastaavasti alennuksen ja tuotteen hinnan perusteella.

Absoluuttisia soluviittauksia

Kuva 10. Absoluuttisia soluviittauksia

Esimerkiksi soluun on laskettu takeista muodostuva alennus. Muodostuva alennus lasketaan muodossa . Soluun D4 on siis kirjoitettu kaava . Solussa oleva kaava laskee kahden vasemmalla puolella olevan solun tulon ( ja ) ja kertoo sen vielä solussa B1 olevalla luvulla. Jos solun kaava kopioidaan soluun , niin saadaan uusi kaava, joka on muotoa . -solussa käytetyn kaavan soluviittaukset muuttuvat kopioidessa ainoastaan kappalemäärän ja yksikköhinnan kohdalta. Absoluuttinen soluviittaus soluun säilyy sellaisenaan.

Absoluuttinen soluviittaus on erityisen hyödyllinen tilanteissa, joissa jokin laskentakaavan osa pysyy koko ajan paikallaan, kuten edellisen esimerkin alennusprosentti. Aina ei kuitenkaan tarvita kokonaan absoluuttista tai suhteellista soluviittausta vaan jotakin siltä väliltä.

Puoliabsoluuttiset soluviittaukset

Puoliabsoluuttinen soluviittaus tarkoittaa viittausta, joissa kiinnitetään ainoastaan toinen sarake- tai rivi-indeksistä. Puoliabsoluuttinen viittaus muuttuu kopioinnin yhteydessä ainoastaan sen indeksin suhteen, jota ei ole kiinnitetty. Esimerkiksi solussa A1 oleva soluviittaus =D$1 on puoliabsoluuttinen soluviittaus. Kun kaavaa kopioidaan esimerkiksi soluun B2, niin soluviittaus muuttuu muotoon =E$1. Sarakeindeksi muuttuu D:stä E:ksi, koska kaavaa kopioitiin yksi sarake oikealle. Rivi-indeksi 1 ei muutu ollenkaan, vaikka kaavaa kopioitiin myös yksi rivi alaspäin. Tämä johtuu luonnollisestikin absoluuttisuuden kiinnittävästä dollarimerkistä ($), joka on sijoitettu rivi-indeksin eteen.

Puoliabsoluuttisia soluviittauksia

Kuva 11. Puoliabsoluuttisia soluviittauksia

Oheisessa esimerkissä on alennusprosenttitaulukko, josta näkyy tietyn hinnan ja alennusprosentin muodostamat tuotehinnat. Taulukon vasemman puoleisessa sarakkeessa (A) on tuotteiden hinnat ja niiden oikealla puolella tietyn prosentin mukaan alennetut hinnat. Alennus muodostuu toisella rivillä olevien alennusprosenttien mukaan. Soluun on tarkoitus kirjoittaa kaava, joka voidaan kopioida kaikkiin muihin alennushintataulukon soluihin. Kaava tulee olemaan muotoa .

Ensimmäinen versio kaavasta voisi olla =A3-(A3*B2), mutta pikaisella kokeilemisella kaavan heikkoudet selviävät. Kaava toimii ainoastaan solussa B3! Kaavaan pitää kiinnittää hintasarake (A-sarake) puoliabsoluuttisella soluviittauksella $A3. Kaava muuttuu tällöin muotoon =$A3-($A3*B2). Kaava näyttää toimivan hyvin, kun sitä kopioidaan pelkästään kolmannella rivillä. Neljännellä riville se ei kuitenkaan enää toimi! Kaavan korjaaminen onnistuu toisen rivin eli alennusprosenttirivin kiinnittämisellä, jonka jälkeen kaava on muotoa =$A3-($A3*B$2). Nyt kaava toimii, vaikka sitä kopioidaan mihin tahansa alennushintataulukon soluun.

Erilaiset soluviittaukset

Seuraavaan on koottu erilaiset soluviittaukset:

C3on suhteellinen soluviittaus sekä sarakkeen että rivin suhteen.

Microsoft Excelissä soluviittauksissa olevia dollarimerkkejä ei tarvitse välttämättä kirjoittaa käsin! Kun soluviittaus on aktiivinen eli kursori on soluviittauksen sisällä voidaan viittauksen vaihtaa suhteelliseksi, absoluuttiseksi tai puoliabsoluuttiseksi näppäimistön F4-näppäimen avulla. Ensimmäinen näppäimen painaminen muuttaa viittauksen kokonaan absoluuttiseksi. Toisella ja kolmannella painalluksella saadaan puoliabsoluuttinen viittaus rivin ja sarakkeen suhteen. Neljännellä painalluksella saadaan takaisin suhteellinen viittaus.

Soluviittaukset samalla laskentataulukossa

Viittaus samaan laskentataulukkoon

Kuva 12. Viittaus samaan laskentataulukkoon

Yleensä taulukkolaskennassa tarvitaan samalle laskentataulukkoon tehtäviä soluviittauksia. Edellisten lukujen esimerkeissä soluviittaukset tehtiin aina samalle laskentataulukkoon. Absoluuttisten soluviittausten yhteydessä käytettiin absoluuttista soluviittausta alennusprosentin käyttämiseksi kaavassa. Olkoon alennusprosentti -laskentataulukon solussa. Microsoft Excelissä viittaaminen solusta soluun onnistui kirjoittamalla soluun kaava . Tällöin kysymyksessä on suhteellinen soluviittaus.

Soluviittaukset toiselle laskentalaskentataulukkoon

Soluviittauksen tekeminen toiselle työkirjan toiselle laskentataulukkoon onnistuu samalla tavoin kuin samaan laskentataulukkoon. Soluviittaus voidaan tehdä aloittamalla kaavan kirjoittaminen ja siirtymällä tämän jälkeen toiselle laskentataulukkoon ja valita sieltä kaavaan haluttu solu. Kun solu on valittuna voidaan soluviittaus hyväksyä ENTER-näppäimellä tai kaavan kirjoittamista voidaan jatkaa näppäimistön avulla.

Ainoana erona soluviittauksessa on laskentataulukon nimen näkyminen soluviittauksen yhteydessä. Jokaisessa laskentataulukossa on samoilla indekseillä merkittyjä soluja, joten soluviittaukseen toiselle laskentataulukkoon täytyy ottaa mukaan laskentalaskentataulukon nimi. Jos solu on nimetty, niin laskentataulukon nimeä ei tarvitse välttämättä antaa, kun ollaan saman työkirjan sisällä. Nimetyistä solualueista kerrotaan enemmän vasta luvussa Solujen nimeäminen.

Viittaus eri laskentataulukkoon

Kuva 13. Viittaus eri laskentataulukkoon

Soluviittaus toiselle laskentataulukkoon on Microsoft Excelissä muotoa . Huutomerkki (!) toimii laskentataulukon nimen ja varsinaisen soluviittauksen erottimena. Erotin merkkinä voi toimia toisissa taulukkolaskentaohjelmissa esimerkiksi kaksoispiste (:).

Oheisen kuvan esimerkissä Yleiset-laskentataulukon B1 solussa on Tuote1-laskentataulukossa tarvittava alennusprosentti. Soluviittaus Yleiset-laskentataulukkoon Tuote1-laskentataulusta onnistuu soluviittauksella =Yleiset!B1 .

Soluviittaukset toiseen työkirjaan

Toiseen työkirjaan viittaaminen eroaa hieman toiselle laskentataulukkoon viittaamisesta, koska soluviittaukseen täytyy liittää tieto työkirjan (tiedoston) nimestä. Soluviittaus toiseen laskentataulukkoon on Microsoft Excelissä muotoa =[työkirjan_nimi.xls]Laskentataulukon_nimi!$Soluviittaus. Työkirjan nimi erotetaan laskentataulukon nimestä hakasulkein([]). Työkirjan nimeen voi tulla näkyville koko hakemistopolku. Tämä voi hankaloittaa huomattavasti taulukkolaskentatyökirjan siirtämistä toiseen hakemistorakenteeseen. Kannattaa siis tarkistaa, että viittaus tiedostoon on suhteellinen eli tiedostonimen yhteydessä ei ole koko hakemistopolkua.

Viittaus eri työkirjaan

Kuva 14. Viittaus eri työkirjaan

Microsoft Excelissä varsinainen soluviittaus toiseen työkirjaan on aina absoluuttinen. Esimerkiksi soluviittaus -nimisen työkirjan -laskentataulukosta nimisen työkirjan -laskentataulukkoon onnistuu muodossa . Esimerkin tapauksessa soluviittaus on absoluuttinen, mutta tiedostoviittaus on suhteellinen. Tällöin esimerkin työkirjojen on oltava samassa hakemistossa.

Kolmiulotteiset kaavat

Usein taulukkolaskenta ajatellaan pelkästään laskentataulukossa tehtävänä kaksiulotteisena laskentana. Taulukkolaskennassa voidaan kuitenkin ottaa käyttöön myös kolmas ulottuvuus. Esimerkiksi erilaiset laskutoimitukset voidaan tehdä suoraan koko työkirjan laskentataulukoiden läpi. Tällöin tietenkin asetetaan hyvin suuria rajoituksia laskentataulukon muodolle. Seuraavassa esimerkki työkirjan läpi tapahtuvasta laskennasta.

Kuukauden säätilatietoja eri kaupungeissa.

Kuva 15. Kuukauden säätilatietoja eri kaupungeissa.

Otetaan esimerkiksi tilanne, jossa joka kuukauden säätilatiedot on jaettu omille laskentalomakkeille. Jokaisella laskenta-taulukolle on useamman paikkakunnan säätilatiedot omissa sarakkeissaan oheisen kuvan mukaisesti. Kuvaan ei ole otettu kuin tammikuu ja helmikuu, mutta esimerkki on helposti yleistettävissä myös useammalle laskentataulukolle.

Jos säätilatiedoista halutaan esimerkiksi laskea jokaisen kuukauden ensimmäisen päivän lämpötilojen keskiarvo Yhteenveto-taulukkoon, niin tämä onnistuu laskemalla taulujen läpi muodostuvan solualueen keskiarvo. Taulujen läpi muodostuva solualue, johon otetaan kummankin taulukon B2-solu on tässä tapauksessa Tammikuu:Helmikuu!B2. Solualuetta ei tarvitse itse kirjoittaa, vaan kaavan kirjoittaminen voidaan tehdä seuraavasti:

Kaavan tekeminen aloitetaan sen solun valinnalla, johon kaava tulee eli esimerkin tapauksessa Yhteenveto-lomakkeen solu B4. Tämän jälkeen voidaan kirjoittaa tai aloittaa funktion ohjattu lisääminen. Tässä yhteydessä käytettävä funktio on AVERAGE, jolla voidaan laskea halutun solualueen keskiarvo. Kun kaavaan valitaan solualue, mennään valitsemaan ensimmäisestä laskentataulukosta solu, joka halutaan mukaan alueeseen. Esimerkin tapauksessa solu on Tammikuu-taulukon solu B2. Solun valinnan jälkeen painetaan näppäimistön SHIFT-näppäin pohjaan ja valitaan laskettavan alueen viimeinen taulukko aktiiviseksi eli esimerkin tapauksessa taulukko on Helmikuu. Aktivoinnin jälkeen kaava voidaan hyväksyä ENTER-näppäimellä. Kaavasta tulee esimerkin tapauksessa seuraavaan muotoon: =AVERAGE(Tammikuu:Helmikuu!B2).

Edellä tehty kaava on kopioitavissa kaikkiin Yhteenveto-taulukon kustannussoluihin. Kaavassa on määritelty pelkästään solualueen aloitus- ja lopetustaulukko. Tämän vuoksi laskentataulukoita voidaan lisätä Tammikuu ja Helmikuu -laskentataulukoiden väliin, jolloin ne otetaan mukaan automaattisesti laskentakaavoihin.

Kolmiulotteinen taulukkolaskenta asettaa kuitenkin suuria rajoituksia laskennassa mukana olevien taulukoiden muodolle. Taulukoiden on oltava täsmälleen saman muotoisia ja niiden on myös säilytettävä muotonsa. Kolmiulotteinen taulukkolaskenta on kuitenkin hyvin tehokas tapa tehdä laskentaa useamman laskentataulukon arvoilla.

Käyttäjien kommentit

Kommentoi tätä sivua Lisää uusi kommentti
Kurssimateriaalien käyttäminen kaupallisiin tarkoituksiin tai opetusmateriaalina ilman lupaa on ehdottomasti kielletty!
http://appro.mit.jyu.fi/doc/tiedonhallinta/taulukkolaskenta/index4.html
© Antti Ekonoja (anjoekon@jyu.fi) <http://users.jyu.fi/~anjoekon/>
Tommi Lahtonen (tommi.j.lahtonen@jyu.fi) <http://hazor.iki.fi/>
Jukka Mäntylä (jmantyla@iki.fi) <http://www.iki.fi/jmantyla/>
2003-10-08 11:24:04
Informaatioteknologia - Jyväskylän yliopiston IT-tiedekunta ja avoin yliopisto