Jäsennettyjen viittausten käyttäminen Excel-taulukoissa

Kun luot Excel-taulukon, Excel määrittää nimen taulukolle ja jokaiselle taulukon sarakeotsikolle. Kun lisäät kaavoja Excel-taulukkoon, kyseiset nimet voivat näkyä automaattisesti kirjoittaessasi kaavaa ja valitessasi soluviittauksia taulukossa eikä sinun tarvitse kirjoittaa niitä manuaalisesti. Tässä on esimerkki Excelin toimista:

Eksplisiittisten soluviittausten sijaan	Excel käyttää taulukon ja sarakkeiden nimiä
=Summa(C2:C7)	=SUMMA(OsastoMyynti[Myyntisumma])

Tällaista taulukon ja sarakkeiden nimien yhdistelmää kutsutaan rakenteelliseksi viittaukseksi. Rakenteellisten viittausten nimet muuttuvat sitä mukaa, kun lisäät tietoja taulukkoon tai poistat niitä.

Rakenteelliset viittaukset tulevat näkyviin myös silloin, kun luot Excel-taulukon ulkopuolella kaavan, jossa on viittauksia taulukon tietoihin. Viittaukset voivat helpottaa taulukoiden löytämistä suurista työkirjoista.

Jos haluat sisällyttää kaavaan rakenteellisia viittauksia, valitse taulukkosolut, joihin haluat viitata, sen sijaan, että kirjoittaisit niiden soluviittauksen kaavaan. Käytetään seuraavia esimerkkitietoja sellaisen kaavan kirjoittamiseen, joka käyttää automaattisesti rakenteellisia viittauksia myyntiprovisiomäärän laskemiseen.

Myyjä	Alue	Myyntisumma	% Myyntipalkkio
Jaakko	Pohjoinen	260	10 %
Teemu	Etelä	660	15 %
Marja	Itä	940	15 %
Elias	Länsi	410	12 %
Taina	Pohjoinen	800	15 %
Juhani	Etelä	900	15 %

Kopioi yllä olevan taulukon esimerkkitiedot, mukaan lukien sarakeotsikot, ja liitä ne uuden Excel-laskentataulukon soluun A1.
Luo taulukko valitsemalla mikä tahansa tietoalueen solu ja painamalla näppäinyhdistelmää Ctrl+T.
Varmista, että Taulukossa on otsikoita -ruutu on valittuna, ja valitse OK.
Kirjoita E2-soluun yhtäläisyysmerkki (=) ja valitse solu C2.

Rakenteellinen viittaus [@[Myyntisumma]] näkyy kaavarivillä yhtäläisyysmerkin jälkeen.
Kirjoita tähtimerkki (*) suoraan sulkeen perään ja valitse solu D2.

Rakenteellinen viittaus [@[% Myyntipalkkio]] näkyy kaavarivillä tähden jälkeen.
Paina sitten Enter-näppäintä.

Excel luo automaattisesti lasketun sarakkeen ja kopioi kaavan koko sarakkeeseen mukauttaen sitä rivikohtaisesti.

Eksplisiittisten soluviittausten käyttäminen

Jos kirjoitat eksplisiittisiä soluviittauksia laskettuun sarakkeeseen, voi olla vaikea nähdä, mitä kaava laskee.

Valitse esimerkkilaskentataulukossa solu E2
Kirjoita kaavariville =C2*D2 ja paina Enter-näppäintä.

Huomaa, että vaikka Excel kopioi kaavan koko sarakkeeseen, se ei käytä rakenteellisia viittauksia. Jos haluat esimerkiksi lisätä uuden sarakkeen C- ja D-sarakkeiden väliin, kaavaa täytyy muokata.

Taulukon nimen muuttaminen

Kun luot Excel-taulukon, Excel luo sille oletusnimen (Taul1, Taul2 ja niin edelleen), mutta voit muuttaa taulukon nimen paremmin kuvaavaksi.

Valitse mikä tahansa taulukon solu, jos haluat näyttää valintanauhan Taulukon rakennenäkymä -välilehden.
Kirjoita haluamasi nimi Taulukon nimi - ruutuun ja paina Enter-näppäintä.

Esimerkkitiedoissa on käytetty nimeä OsastoMyynti.

Noudata taulukkojen nimissä seuraavia sääntöjä:

Nimessä voi käyttää vain kelvollisia merkkejä Nimen ensimmäisen merkin on oltava kirjain, alaviiva (_) tai kenoviiva (\). Nimen muut merkit voivat olla kirjaimia, numeroita, pisteitä tai alaviivoja. Nimi ei voi olla "C", "c", "R" tai "r", sillä näitä kirjaimia käytetään jo pikanäppäiminä aktiivisen solun sarakkeen tai rivin valitsemiseen. Kirjaimet kirjoitetaan tällöin Nimi- tai Siirry-ruutuun.

Älä käytä soluviittauksia Nimet eivät saa olla samoja kuin soluviittaus, esimerkiksi Z$100 tai R1C1.
Älä erota sanoja välilyönnillä Nimessä ei voi käyttää välilyöntejä. Voit käyttää alaviivaa (_) ja pistettä (.) sanan erottimena. Esimerkiksi DeptSales, Sales_Tax tai First.Quarter.
Älä käytä enempää kuin 255 merkkiä Taulukon nimessä voi olla enintään 255 merkkiä.
Käytä yksilöiviä taulukon nimiä Samat nimet eivät ole sallittuja. Excel ei tee eroa nimien isojen ja pienten kirjainten välillä, joten jos kirjoitat nimeksi ”Myynti” mutta samassa työkirjassa on jo toinen ”MYYNTI”-nimi, sinun on valittava yksilöivä nimi.
Objektitunnisteen käyttäminen Jos aiot käyttää taulukoiden, Pivot-taulukoiden ja kaavioiden yhdistelmää, nimi kannattaa merkitä etuliitteeksi objektityypillä. Esimerkki: tbl_Sales myyntitaulukolle, pt_Sales myynnin Pivot-taulukolle ja chrt_Sales myyntikaaviolle tai ptchrt_Sales myynnin Pivot-kaaviolle. Tämä säilyttää kaikki nimesi nimien hallinnassa järjestetyssä luettelossa.

Rakenteellisen viittauksen syntaksisäännöt

Voit myös kirjoittaa tai muuttaa rakenteellisia viittauksia manuaalisesti kaavaan, mutta se auttaa ymmärtämään jäsennetyntaksia. Katsotaan seuraavaa kaavaesimerkkiä:

=SUMMA(OsastoMyynti[[#Yhteensä],[Myyntisumma]],OsastoMyynti[[#Tiedot],[Myyntipalkkion määrä]])

Tässä kaavassa on seuraavat rakenteellisen viittauksen osat:

Taulukon nimi: OsastoMyynti on mukautettu taulukon nimi. Se viittaa taulukon tietoihin ilman otsikko- tai summarivejä. Voit käyttää taulukon oletusnimeä, kuten Taul1, tai antaa sille mukautetun nimen.
Sarakemäärite: [Myyntisumma] ja [Myyntipalkkion summa] ovat sarakemäärite, joka käyttää niiden edustamien sarakkeiden nimiä. Ne viittaavat sarakkeen tietoihin ilman sarakeotsikko- tai summarivejä. Kirjoita määritteet aina hakasulkeisiin esimerkissä kuvatulla tavalla.
Kohteen määritteet: [#Totals] ja [#Data] ovat erikoiskohteiden määritteitä, jotka viittaavat taulukon tiettyihin osiin, kuten kokonaisriviin.
Taulukkomäärite: [[#Yhteensä],[Myyntisumma]] sekä [[#Tiedot],[Myyntipalkkion määrä]] ovat taulukkomääritteitä, jotka edustavat rakenteellisen viittauksen ulompia osia. Ulkoiset viittaukset noudattavat taulukon nimeä, ja ne kirjoitetaan hakasulkeisiin.
Rakenteellinen viittaus: (OsastoMyynti[[#Totals],[Myyntisumma]] ja OsastoMyynti[[#Data],[Myyntipalkkion määrä]] ovat rakenteellisia viittauksia, joita edustaa merkkijono, joka alkaa taulukon nimestä ja päättyy sarakemäärittimeen.

Jos haluat luoda tai muokata rakenteellisia viittauksia manuaalisesti, käytä näitä syntaksisääntöjä:

Käytä määritteiden ympärillä hakasulkeita Kaikki taulukoiden, sarakkeiden ja erikoiskohteiden määritteet on sijoitettava vastaaviin hakasulkeisiin ([ ]). Määrite, joka sisältää muita määritteitä, edellyttää vastaavia ulompia hakasulkeita, jotka ympäröivät muiden määritteiden sisempiä hakasulkeita. Esimerkki: =OsastoMyynti[[Myyjä]:[Alue]]
Kaikki sarakeotsikot ovat tekstimerkkijonoja Ne eivät kuitenkaan vaadi lainausmerkkejä, kun niitä käytetään rakenteellisessa viittauksessa. Numerot ja päivämäärät, kuten 2014 tai 1.1.2014, katsotaan myös tekstimerkkijonoiksi. Et voi käyttää sarakeotsikoita sisältäviä lausekkeita. Esimerkiksi lauseke OsastoMyyntiTVYhteenveto[[2014]:[2012]] ei toimi.

Käytä hakasulkeita erikoismerkkejä sisältävien sarakeotsikoiden ympärillä Koko erikoismerkkejä sisältävä sarakeotsikko on laitettava hakasulkeisiin, eli sarakemääritteessä on siis käytettävä kaksinkertaisia hakasulkeita. Esimerkki: =OsastoMyyntiTVYhteenveto[[Kokonaismäärä $]]

Seuraavassa on luettelo erikoismerkeistä, jotka edellyttävät kaavassa ylimääräisiä hakasulkeita.

Sarkain
Rivisyöte
Rivinvaihto
Pilkku (,)
Kaksoispiste (:)
Piste (.)
Vasen hakasulje ([)
Oikea hakasulje (])
Ristikkomerkki (#)
Puolilainausmerkki (')
Lainausmerkki (")
Vasen aaltosulje ({)
Oikea aaltosulje (})
Dollarimerkki ($)
Näppäimistö (^)
Et-merkki (&)
Tähti (*)
Plusmerkki (+)
Yhtäläisyysmerkki (=)
Miinusmerkki (-)
Suurempi kuin -merkki (>)
Pienempi kuin -merkki (<)
Jakomerkki (/)
At sign (@)
Kenoviiva (\)
Huutomerkki (!)
Vasen sulkeet (()
Oikea sulkeet ())
Prosenttimerkki (%)
Kysymysmerkki (?)
Taustan haku (')
Puolipiste (;)
Tilde (~)
Alaviiva (_)

Käytä ohjausmerkkiä sarakeotsikoiden joidenkin erikoismerkkien yhteydessä Joillakin merkeillä on erityinen merkitys, ja niiden kanssa on käytettävä ohjausmerkkinä puolilainausmerkkiä ('). Esimerkki: =OsastoMyyntiTVYhteenveto[Kohteiden'#]

Seuraavassa on luettelo erikoismerkeistä, jotka tarvitsevat kaavassa pakomerkin (').

Vasen hakasulje ([)
Oikea hakasulje (])
Ristikkomerkki (#)
Puolilainausmerkki (')
At sign (@)

Käytä välilyöntiä rakenteellisten viittausten luettavuuden parantamiseksi Voit parantaa rakenteellisen viittauksen luettavuutta käyttämällä välilyöntejä. Esimerkki: =OsastoMyynti[ [Myyjä]:[Alue] ] tai =OsastoMyynti[[#Otsikot], [#Tiedot], [% Myyntipalkkio]]

Yhden välilyönnin käyttäminen on suositeltavaa

Ensimmäisen vasemman hakasuljeen jälkeen ([)
Ennen viimeistä oikeaa hakasuljetta (]).
Pilkkujen jälkeen.

Viittausoperaattorit

Jos haluat lisätä solualueiden määrityksen joustavuutta, voit yhdistää sarakemääritteitä seuraavien viittausoperaattorien avulla.

Tämä rakenteellinen viittaus:	Viittaa kohteeseen:	Käyttämällä merkkiä:	Mikä on solualue:
=OsastoMyynti[[Myyjä]:[Alue]]	Kahden tai useamman vierekkäisen sarakkeen kaikki solut	: (kaksoispiste) alueoperaattori	A2:B7
=OsastoMyynti[Myyntisumma],OsastoMyynti[Myyntipalkkion määrä]	Kahden tai useamman sarakkeen yhdistelmä	, (pilkku) yhdistämisoperaattori	C2:C7, E2:E7
=OsastoMyynti[[Myyjä]:[Myyntisumma]] OsastoMyynti[[Alue]:[% Myyntipalkkio]]	Kahden tai useamman sarakkeen leikkauskohta	(välilyönti) leikkausoperaattori	B2:C7

Erikoiskohteiden määritteet

Voit viitata taulukon tiettyihin osiin, kuten ainoastaan summariviin, käyttämällä rakenteellisessa viittauksessa joitain seuraavista erikoiskohteiden määritteistä.

Tämä erikoiskohteen määrite:	Viittaa kohteeseen:
#Kaikki	Koko taulukko, mukaan lukien sarakeotsikot, tiedot ja summat (jos saatavilla).
#Tiedot	Vain tietorivit.
#Otsikot	Vain otsikkorivi.
#Yhteensä	Vain summarivi. Jos summariviä ei ole, arvoksi palautuu nolla.
#Tämä rivi tai @ tai @[Sarakkeen nimi]	Vain kaavan kanssa samalla rivillä olevat solut. Näitä määritteitä ei voida yhdistää mihinkään muuhun erikoiskohteen määritteeseen. Niiden avulla voit pakottaa viittauksen noudattamaan epäsuoria leikkauksia tai ohittaa epäsuorat leikkaukset ja viitata sarakkeen yksittäisiin arvoihin. Excel muuttaa automaattisesti #Tämä rivi -määritteet lyhyemmäksi @-määritteeksi taulukoissa, joissa on enemmän kuin yksi tietorivi. Jos taulukossa on vain yksi rivi, Excel ei korvaa #Tämä rivi -määritettä, mikä voi aiheuttaa odottamattomia laskentatuloksia, kun lisäät rivejä. Voit välttää laskentaongelmat varmistamalla, että olet lisännyt taulukkoon useita rivejä, ennen kuin ryhdyt lisäämään rakenteellisten viittausten kaavoja.

Täydelliset ja ei-täydelliset rakenteelliset viittaukset lasketuissa sarakkeissa

Kun luot lasketun sarakkeen, luot kaavan yleensä rakenteellisen viittauksen avulla. Tämä rakenteellinen viittaus voi olla hyväksymätön tai täysin hyväksytty. Jos esimerkiksi haluat luoda lasketun sarakkeen nimeltä Myyntipalkkion summa, joka laskee myyntipalkkion summan dollareina, voit käyttää seuraavia kaavoja:

Rakenteellisen viittauksen tyyppi	Esimerkki	Kommentti
Ei-täydellinen	=[Myyntisumma]*[% Myyntipalkkio]	Kertoo nykyisen rivin vastaavat arvot.
Täydellinen	=OsastoMyynti[Myyntisumma]*OsastoMyynti[% Myyntipalkkio]	Kertoo molempien sarakkeiden kaikkien rivien vastaavat arvot.

Noudata seuraavaa yleissääntöä: Jos käytät taulukon sisäisiä rakenteellisia viittauksia esimerkiksi luodessasi lasketun sarakkeen, voit käyttää hyväksymätöntä rakenteellista viittausta. Jos taas käytät taulukon ulkopuolista rakenteellista viittausta, sinun on käytettävä täydellistä rakenteellista viittausta.

Esimerkkejä rakenteellisten viittausten käytöstä

Tässä on joitakin tapoja, joilla voit käyttää rakenteellisia viittauksia.

Tämä rakenteellinen viittaus:	Viittaa kohteeseen:	Mikä on solualue:
=OsastoMyynti[[#Kaikki],[Myyntisumma]]	MyyntiSumma-sarakkeen kaikki solut.	C1:C8
=OsastoMyynti[[#Otsikot],[% Myyntipalkkio]]	% Myyntipalkkio -sarakkeen otsikko.	D1
=OsastoMyynti[[#Yhteensä],[Alue]]	Alue-sarakkeen summa. Jos summariviä ei ole, arvoksi palautuu nolla.	B8
=OsastoMyynti[[#Kaikki],[MyyntiSumma]:[% Myyntipalkkio]]	MyyntiSumma- ja % Myyntipalkkio -sarakkeen kaikki solut.	C1:D8
=OsastoMyynti[[#Tiedot],[% Myyntipalkkio]:[Myyntipalkkion määrä]]	Vain % Myyntipalkkio- ja Myyntipalkkion määrä -sarakkeiden tiedot.	D2:E7
=OsastoMyynti[[#Otsikot],[Alue]:[Myyntipalkkion määrä]]	Vain sarakkeiden Alue ja Myyntipalkkion määrä välisten sarakkeiden otsikot.	B1:E1
=OsastoMyynti[[#Yhteensä],[MyyntiSumma]:[Myyntipalkkion määrä]]	MyyntiSumma- ja Myyntipalkkion määrä -sarakkeiden summarivit. Jos summariviä ei ole, arvoksi palautuu nolla.	C8:E8
=OsastoMyynti[[#Otsikot],[#Tiedot],[% Myyntipalkkio]]	Vain % Myyntipalkkio -sarakkeen otsikot ja tiedot.	D1:D7
=OsastoMyynti[[#Tämä rivi], [Myyntipalkkion määrä]] tai =OsastoMyynti[@Myyntipalkkion määrä]	Nykyisen rivin ja Myyntipalkkion määrä -sarakkeen leikkauskohdassa oleva solu. Jos sitä käytetään samalla rivillä otsikko- tai summarivin kanssa, tämä palauttaa #VALUE! -virheen. Jos kirjoitat tämän rakenteellisen viittauksen pidemmän muodon (#Tämä rivi) useita tietorivejä sisältävään taulukkoon, Excel korvaa sen automaattisesti lyhyemmällä muodolla (@). Molemmat toimivat samalla tavalla.	E5 (jos nykyinen rivi on 5)

Strategioita rakenteellisten viittausten käyttöön

Ota huomioon seuraavat seikat, kun käsittelet rakenteellisia viittauksia.

Kaavan automaattisen täydennyksen käyttäminen Kaavan automaattisen täydennyksen käyttäminen saattaa olla erittäin hyödyllistä, kun määrität rakenteellisia viittauksia ja haluat varmistaa syntaksin oikeellisuuden. Lisätietoja on artikkelissa Kaavan automaattisen täydennyksen käyttäminen.
Valitse, luodaanko puolivalintojen taulukoille rakenteellisia viittauksia Kun luot kaavan, taulukon solualueen valitseminen valitsee oletusarvoisesti solut ja lisää automaattisesti rakenteellisen viittauksen kaavan solualueen sijaan. Tämä puolivalinta helpottaa huomattavasti rakenteellisen viittauksen syöttämistä. Voit ottaa tämän toiminnon käyttöön tai poistaa sen käytöstä valitsemalla Käytä taulukoiden nimiä kaavoissa -valintaruudun Tai poistamalla sen valinnan Tiedosto -> Asetukset > Kaavat > Kaavojen käsitteleminen -valintaikkunassa.
Ulkoisten linkkien sisältävien työkirjojen käyttäminen muiden työkirjojen Excel-taulukoihin Jos työkirja sisältää ulkoisen linkin toisen työkirjan Excel-taulukkoon, linkitetyn lähdetyökirjan on oltava avoinna Excelissä, jotta et #REF! -virheitä linkit sisältävässä kohdetyökirjassa. Jos avaat kohdetyökirjan ensin ja #REF! -virheet tulevat näkyviin, ne ratkeavat, jos avaat lähdetyökirjan. Jos avaat lähdetyökirjan ensin, virhekoodeja ei pitäisi näkyä.
Alueen muuntaminen taulukoksi ja taulukon muuntaminen alueeksi Kun muunnat taulukon alueeksi, kaikki soluviittaukset muuttuvat vastaaviksi suoriksi A1-tyyliviittauksiksi. Kun muunnat alueen taulukoksi, Excel ei muuta tämän alueen soluviittauksia automaattisesti vastaaviksi rakenteellisiksi viittauksiksi.
Sarakeotsikoiden poistaminen käytöstä Voit ottaa taulukon sarakeotsikot käyttöön tai poistaa ne käytöstä Taulukon rakennenäkymä -välilehdessä > Otsikkorivi. Jos poistat taulukon sarakeotsikot käytöstä, tämä ei vaikuta sarakkeiden nimiä käyttäviin jäsennettyihin viittauksiin, ja voit silti käyttää niitä kaavoissa. Rakenteelliset viittaukset, jotka viittaavat suoraan taulukon otsikoihin (esim. =OsastoMyynti[[#Headers],[%Komissio]]), johtavat #REF.
Sarakkeiden ja rivien lisääminen tai poistaminen taulukosta Koska taulukon tietoalueet muuttuvat usein, jäsennettyjen viittausten soluviittaukset muuttuvat automaattisesti. Esimerkiksi jos käytät taulukon nimeä kaavassa, joka laskee kaikki tietoja sisältävät taulukon solut, ja lisäät sitten tietorivin, soluviittaus muuttuu automaattisesti.
Taulukon tai sarakkeen nimeäminen uudelleen Jos nimeät sarakkeen tai taulukon uudelleen, Excel muuttaa automaattisesti kyseisen taulukon ja sarakeotsikon käyttöä työkirjan kaikissa rakenteellisissa viittauksissa.
Rakenteellisten viittausten siirtäminen, kopioiminen ja täyttäminen Kaikki rakenteelliset viittaukset pysyvät ennallaan, kun kopioit tai siirrät rakenteellista viittausta käyttävän kaavan.

Huomautus: Rakenteellisen viittauksen kopioiminen ja rakenteellisen viittauksen täyttäminen eivät ole sama asia. Kun kopioit, kaikki rakenteelliset viittaukset pysyvät samoina, mutta kun täytät kaavan, täydelliset rakenteelliset viittaukset säätävät sarakemääritelmät seuraavan taulukon yhteenvedon mukaisesti.

Jos täyttösuuntana on:	Täytön aikana painat:	tulos on seuraava:
Ylös tai alas	Ei mitään	Sarakemääritteitä ei muuteta.
Ylös tai alas	Ctrl	Sarakemääritteet muuttuvat sarjana.
Oikea tai vasen	Ei mitään	Sarakemääritteet muuttuvat sarjana.
Ylös, alas, oikea tai vasen	Vaihtonäppäin	Nykyisten solujen arvojen korvaamisen sijaan nykyiset soluarvot siirretään ja sarakkeen määrittimet lisätään.

Tarvitsetko lisätietoja?

Voit aina kysyä neuvoa Excel Tech Community -yhteisön asiantuntijalta tai saada tukea tukiyhteisöltä.

Aiheeseen liittyvät artikkelit

Excel-taulukoiden yleiskatsausVideo: Excel-taulukon luominen ja muotoileminenExcel-taulukon tietojen summaExcel-taulukon muotoileminenTaulukon koon muuttaminen lisäämällä tai poistamalla rivejä ja sarakkeita Alueen tai taulukon tietojen suodattaminenTaulukon muuntaminen alueeksi Excel-taulukon yhteensopivuusongelmat Excel-taulukon vieminen SharePointiin Excelin kaavojen yleiskatsaukset