Tarkkojen laskelmien tekemiseksi Excelissä on tärkeää ymmärtää, miten erilaiset soluviittaukset toimivat.
A1 vs.R1C1 Viitteet
Excel -laskentataulukot sisältävät monia soluja ja (oletusarvoisesti) jokainen solu tunnistetaan sen perusteella sarakkeen kirje jota seuraa sen rivinumero. Tätä kutsutaan A1-tyyppiseksi viittaukseksi. Esimerkkejä: A1, B4, C6
A1 -viitetyyli
Vaihtoehtoisesti voit vaihtaa tilaan R1C1 -viitetila viittaamaan solun riviin & sarakkeen numero. Sen sijaan, että viittaisit soluun A1, viittaisit R1C1: ään (rivi 1, sarake 1). Solu C4 viitataan nimellä R4C3.
R1C1 -viitetyyli
R1C1-tyyliset viittaukset ovat erittäin harvinaisia Excelissä. Ellei sinulla ole hyvää syytä, sinun on todennäköisesti pidettävä kiinni A1-tyylisestä viitetilasta. Kuitenkin, jos käytät VBA: ta, kohtaat todennäköisesti tämän viitetyylin.
Vaihda R1C1 -viitetyyliin
Voit vaihtaa viitetyyliä siirtymällä kohtaan Tiedosto> Vaihtoehto> Kaava. Valitse vieressä oleva valintaruutu R1C1 -viitetyyli.
Nimetyt alueet
Yksi Excelin alikäytetyimmistä ominaisuuksista on Nimetty alue -ominaisuus. Sen sijaan, että viittaisi soluun (tai soluryhmään) sen solun sijainnin mukaan (esim. B3 tai R3C2), voit nimetä alueen ja viitata yksinkertaisesti alueen nimeen kaavoissasi.
Alueen nimeäminen:
- Valitse solu tai solut, jotka haluat nimetä
- Napsauta Alueen nimi -ruudun sisällä
- Anna haluamasi nimi
- Paina Enter
Nyt voit viitata soluun A1 kirjoittamalla sen sijaan = alueen_nimi1. Tämä on erittäin hyödyllistä, kun käsitellään suuria työkirjoja, joissa on useita laskentataulukoita.
Solualue
Kun käytät Excelin sisäänrakennettuja toimintoja, sinun on ehkä viitattava solualueisiin. Solualueet näyttävät tältä ”A1: B3”. Tämä viittaus viittaa kaikkiin soluihin A1 ja B3 välillä: solut A1, A2, A3, B1, B2, B3.
Solualueen valitseminen kaavaa syötettäessä:
- Kirjoita alue (erota aloitus- ja lopetusalue puolipisteellä)
- Napsauta ensimmäistä soluviittausta hiirellä, pidä hiiren painiketta painettuna ja vedä haluamaasi aluetta.
- Pidä vaihtonäppäintä painettuna ja valitse alue nuolinäppäimillä
Absoluuttiset (jäädytetyt) ja suhteelliset viitteet
Kun syötät soluviittauksia kaavoihin, voit käyttää suhteellisia tai absoluuttisia (jäädytettyjä) viittauksia. Suhteelliset viitteet siirtyvät suhteessa, kun kaava kopioidaan uuteen soluun. Absoluuttiset viittaukset pysyvät ennallaan. Katsotaanpa joitain esimerkkejä:
Suhteellinen viite
Suhteellinen viittaus Excelissä näyttää tältä
= A1
Kun kopioit ja liität kaavan, jossa on suhteelliset viittaukset, suhteelliset viitteet siirtyvät suhteessa. Ellei toisin mainita, soluviittaukset ovat oletuksena suhteellisia (jäädyttämättömiä).
Esimerkki: Jos kopioit "= A1" yhden rivin alas, viittaus muuttuu muotoon "= A2".
Absoluuttiset (jäädytetyt) soluviitteet
Jos et halua, että soluviittauksesi liikkuvat, kun kopioit kaavan, voit "jäädyttää" soluviittauksesi lisäämällä dollarimerkkejä ($ s) viittauksen eteen, jonka haluat jäädyttää. Nyt kun kopioit ja liität kaavan, soluviittaus pysyy muuttumattomana. Voit jäädyttää rivi-, sarake- tai molemmat.
A1: Mikään ei ole jäätynyt
$ A1: Sarake on jäädytetty, mutta rivi ei ole jäädytetty
A $ 1: Rivi on jäädytetty, mutta sarake ei ole jäädytetty
$ A $ 1: Sekä rivi että sarake on jäädytetty
Absoluuttinen viitepikakuvake
Dollarimerkkien ($ s) lisääminen manuaalisesti kaavoihisi ei ole kovin käytännöllistä. Käytä sen sijaan kaavaa luodessasi F4 -näppäintä vaihtaaksesi absoluuttisten/suhteellisten soluviittausten välillä.
Absoluuttinen soluviittausesimerkki
Milloin sinun pitäisi todella jäädyttää soluviittaus? Yksi yleinen esimerkki on, kun sinulla on syöttösoluja, joihin viitataan usein. Alla olevassa esimerkissä haluamme laskea myyntiveron kullekin valikkokohdan määrälle. Myyntivero on vakio kaikissa kohteissa, joten viitataan liikevaihtoverosoluun toistuvasti.
Jos haluat löytää kokonaisveron, kirjoita kaava ’= (B3*C3)*$ C $ 1’ sarakkeeseen D ja kopioi kaava alas.
Sekalainen viite
Olet ehkä kuullut sekavista viittauksista. Sekoitettu viittaus on, kun joko rivi- tai sarakeviittaus on lukittu (mutta ei molempia).
Sekalainen viite
Muista, että käyttämällä F4 -näppäintä voit selata suhteellisia, absoluuttisia soluviittauksia.
Soluviitteet - Rivien/sarakkeiden lisääminen ja poistaminen
Saatat ihmetellä, mitä tapahtuu soluviitteillesi, kun lisäät tai poistat rivejä/sarakkeita?
Soluviittaus päivittyy automaattisesti viittaamaan alkuperäiseen soluun. Näin on riippumatta siitä, onko soluviittaus jäädytetty.
3D -viitteet
Joskus saatat joutua työskentelemään useiden laskentataulukoiden kanssa, joilla on identtiset tiedot. Excelin avulla voit viitata useisiin arkkeihin kerralla ilman, että sinun tarvitsee syöttää jokaista laskentataulukkoa manuaalisesti. Voit viitata taulukkoalueeseen samalla tavalla kuin viittaisi solualueeseen. Esimerkki "Taulukko1: Taulukko5! A1" viittaisi soluihin A1 kaikissa arkeissa Taulukko1 - Taulukko5.
Käydään esimerkki läpi:
Haluat laskea yhteen kunkin tuotteen myydyt yksiköt kaikissa myymälöissä. Jokaisella myymälällä on oma laskentataulukko ja kaikilla laskentataulukoilla on sama muoto. Voit luoda samanlaisen kaavan:
Tämä ei ole liian vaikeaa vain neljällä laskentataulukolla, mutta entä jos sinulla olisi 40 laskentataulukkoa? Haluatko todella lisätä manuaalisesti jokaisen soluviittauksen?
Sen sijaan voit käyttää 3D -viittausta viittaamaan helposti useisiin arkkeihin kerralla (samalla tavalla kuin voit viitata solualueeseen).
Ole varovainen! Laskentataulukoiden järjestyksellä on väliä. Jos siirrät toisen arkin viitattujen arkkien (StoreA ja StoreD) väliin, tämä arkki lisätään. Päinvastoin, jos siirrät arkin arkki -alueen ulkopuolelle (ennen StoreA: ta tai StoreD: n jälkeen), sitä ei enää sisällytetä.
Pyöreä soluviite
Pyöreä soluviittaus on, kun solu viittaa takaisin itseensä. Jos esimerkiksi solun B1 tulosta käytetään tulona solulle B1, luodaan pyöreä viittaus. Solun ei tarvitse viitata suoraan itseensä. Välivaiheita voi olla.
Esimerkki:
Tässä tapauksessa solun B2 kaava on ”A2+A3+B2”. Koska olet solussa B2, et saa käyttää yhtälöä B2. Tämä laukaisee pyöreän viittauksen ja solun "B2" arvoksi asetetaan automaattisesti "0".
Yleensä pyöreät viittaukset ovat seurausta käyttäjävirheestä, mutta joissain tilanteissa haluat ehkä käyttää pyöreää viitettä. Ensisijainen esimerkki pyöreän viitteen käytöstä on laskea arvot iteratiivisesti. Tätä varten sinun on siirryttävä osoitteeseen Tiedosto> Asetukset> Kaavat ja Otettu käyttöön iteratiivinen laskenta:
Ulkoiset viitteet
Joskus tietoja laskettaessa sinun on ehkä viitattava työkirjan ulkopuolisiin tietoihin. Tätä kutsutaan ulkoiseksi viitteeksi (linkki).
Jos haluat valita ulkoisen viitteen kaavan luomisen aikana, siirry ulkoiseen työkirjaan ja valitse viite normaalisti.
Voit siirtyä toiseen työkirjaan käyttämällä CTRL + TAB -pikakuvaketta tai siirtymällä kohtaan Näytä> Vaihda Windows.
Kun olet valinnut solun, näet ulkoisen viittauksen, joka näyttää tältä:
Huomaa, että työkirjan nimi on suluissa [].
Kun suljet viitatun työkirjan, viite näyttää tiedoston sijainnin:
Kun avaat ulkoisen linkin sisältävän työkirjan uudelleen, sinua kehotetaan ottamaan linkkien automaattinen päivitys käyttöön. Jos teet niin, Excel avaa viitearvon työkirjan nykyisen arvon kanssa. Vaikka se onkin kiinni! Ole varovainen! Haluat tai et halua tätä.
Nimetyt alueet ja ulkoiset viitteet
Mitä tapahtuu ulkoiselle soluviittaukselle, kun rivejä / sarakkeita lisätään tai poistetaan viitetyökirjasta? Jos molemmat työkirjat ovat auki, soluviitteet päivittyvät automaattisesti. Jos molemmat työkirjat eivät kuitenkaan ole auki, soluviittaukset eivät päivity eivätkä ole enää kelvollisia. Tämä on valtava huolenaihe, kun linkitetään ulkoisiin työkirjoihin. Tästä johtuu monia virheitä.
Jos linkität ulkoisiin työkirjoihin, nimeä soluviittaus nimetyllä alueella (katso lisätietoja edellisestä osiosta). Kaava viittaa nyt nimettyyn alueeseen riippumatta siitä, mitä muutoksia ulkoisessa työkirjassa tapahtuu.
