Excel: Suorat ja suhteelliset viittaukset

Tällä videolla käyn läpi tärkeän soluviittauksiin liittyvän asian. Oheisessa taulukossa on esitetty eri elinkeinotyyppien määrät. Sarakkeessa D haluaisin esittää kunkin elinkeinotyypin prosentuaalisen osuuden elinkeinotyyppien kokonaismäärästä. Taulukkosivulla on myös ympyräkaavio, joka käyttää sarakkeen D elinkeinotyyppien suhteellisia osuuksia. Kaavio päivittyy sitä mukaa kun kunkin elinkeinotyypin suhteellinen osuus on selvillä.

Siirryn soluun D4. Saan kunkin elinkeinotyypin suhteellisen osuuden jakamalla elinkeinotyypin määrää elinkeinojen kokonaistuotannolla. Prosenttimuotoilun voin asettaa jälkikäteen.

Lähden siis kirjoittamaan kaavaa. Aloitan syöttämällä yhtäsuuruusmerkin. Napsautan seuraavaksi solua C4, jossa on esitetty alkutuotannon määrä. Syötän laskuoperaattorin eli jakoviivan ja napsautan solua C12, jossa on laskettu elinkeinotyyppien kokonaismäärä. Hyväksyn kaavan CTRL Enterillä. Kaikki vaikuttaisi tässä vaiheessa hyvältä. Päätän kopioida kaavaa käyttäen täyttökahvaa. Vien hiiren täyttökahvan päälle ja kaksoisnapsautan.

Tässä vaiheessa huomaan, että jotain on pielessä. Excel näyttää kopioiduissa soluissa Jako nollalla virheviestiä. Miksi solussa D4 kaava antoi oikean lopputuloksen mutta kun kopioin kaavaa, sainkin virheitä? Kaksoisnapsautetaan solua D5, jolloin Excel näyttää värillisten kehysten avulla mitä soluja laskutoimituksessa on käytetty.

Kaava viittaa ihan oikein sinisen kehyksen ympäröimään soluun C5, jonka arvona on teollisuuselinkeinon määrä. Teollisuus-elinkeinon määrää jaetaan kuitenkin tyhjän solun C13 arvolla eli nollalla. Kun mietin asiaa tarkemmin, Excel on toiminut saman periaatteen mukaan kuin se oletuksena toimii kaavoja kopioidessaan.

Soluviittaukset eri värilliset kehykset liikkuvat samaan suuntaan kuin mihin suuntaan kopiointia tehdään. Eli koska kopioin kaavaa yhden solun verran alaspäin, molemmat kehykset putosivat yhden solun verran alaspäin.

Jotta saisin oikeat tulokset, punainen kehys ei kuitenkaan saa pudota. Sen tulee aina pysyä solussa C12. Onneksi Excel tarjoaa keinon kiinnittää kehyksiä eli soluviittauksia paikoilleen.

Painan ESCIä, kumoan kopioinnin CTRL zetalla ja napsautan kaavarivillä. Varmistan, että hiiren osoitin kaavarivillä on joko soluosoitteen alussa, keskellä tai lopussa. Painan sitten näppäimistöltä F4 painiketta. Solun saraketunnuksen ja rivinumeron eteen ilmestyy dollari-merkki.

Dollarimerkki sekä solun saraketunnuksen että rivinumeron edessä lukitsee punaisen kehyksen paikoilleen. Vaikka kopioin kaavaa alaspäin, tai johonkin muuhun suuntaan, pysyy kehys aina paikoillaan. Hyväksyn kaavan CTRL Enterillä.

Kaksoisnapsautan täyttökahvan päällä, jolloin saan oikeat tulokset. Varmistan vielä, että solumuotoiluja ei kopioida. Tämän jälkeen napsautan prosentti-muotoilu-painiketta.

Huomaan myös, että ympyräkaavio on päivittynyt ja esittää tiedot havainnollisessa muodossa.

Useimmissa kaavoissa selviän sillä, että lukitsen solukehyksen paikoilleen. On kuitenkin tilanteita, joissa kaavaa kopioidessani haluan estää solukehyksen liikkumisen ainoastaan pystysuunnassa tai ainoastaan vaakasuunnassa.

Valitsen vielä kerran solun D4 ja napsautan kaavarivillä. Kiinnitän huomioni kaavarivillä soluun C12. Sekä saraketunnuksen C:n että rivinumeron 12 edessä on nyt dollarimerkki.

Dollarimerkki saraketunnuksen edessä estää punaista kehystä liikkumasta vaakasuunnassa. Dollarimerkki rivinumeron edessä estää punaista kehystä liikkumasta pystysuunnassa. Tässä esimerkissä kahdesta dollarimerkistä ei ole mitään haittaa, mutta minulle riittäisi itseasiassa vain yksi dollarimerkki.

Mieti hetki kumman dollarimerkin voin poistaa ja silti kopioidessani kaavaa alaspäin saan samat tulokset kuin tällä hetkellä?

Voin poistaa dollarimerkin saraketunnuksen edestä. Tässä esimerkissä kaavan kopiointi tapahtuu alaspäin. Siten minun täytyy estää punaisen kehyksen liikkuminen pystysuunnassa. Koska rivinumero ilmaisee solun sijaintia pystysuunnassa, estää dollarimerkki rivinumeron edessä punaisen kehyksen pystysuuntaisen liikkeen.

Saraketunnuksen edessä oleva dollarimerkki ei sen sijaan ole tarpeellinen tässä tapauksessa. Voin poistaa dollarimerkin saraketunnuksen edestä joko backspace-painikkeella tai F4-painikkeen avulla. F4-painikkeen painaminen vaihtelee erilaisten tilojen välillä.

Hyväksyn kaavan CTRL Enterillä ja kopioin kaavaa täyttökahvalla.

Mikäli lasket Excelillä ensimmäisiä kertoja, voi dollarimerkkien paikoilleen asettelu tuntua aluksi hankalalta. Muista kuitenkin, että useimmissa tapauksissa riittää, että mietit, tuleeko kehykselle sallia vapaa liikkuminen vai tuleeko se kiinnittää kokonaan.