Rakenteellisten 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 lisätä rakenteellisia viittauksia kaavaan, valitse viitattava taulukon solut sen sijaan, että kirjoitat niiden soluviittaukset kaavaan. Seuraavan esimerkkitiedon avulla kirjoitetaan kaava, joka käyttää automaattisesti rakenteellisia viittauksia myyntiprovision laskemiseen.

Myyjä	Alue	Myyntisumma	% Myyntipalkkio	Myyntipalkkion määrä
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 %

1. Kopioi edellä olevan taulukon esimerkkitiedot sarakeotsikot mukaan lukien ja liitä ne uuden Excel-laskentataulukon soluun A1.
2. Luo taulukko valitsemalla mikä tahansa tietoalueen solu ja painamalla näppäinyhdistelmää Ctrl+T.
3. Varmista, että Oma taulukko sisältää otsikot -ruutu on valittuna, ja valitse OK.
4. Kirjoita soluun E2 yhtäläisyysmerkki (=) ja valitse solu C2.
   Rakenteellinen viittaus [@[Myyntisumma]] näkyy kaavarivillä yhtäläisyysmerkin jälkeen.
5. Kirjoita tähti (*) heti oikeanpuoleisen hakasulkeen jälkeen ja valitse solu D2.
   Rakenteellinen viittaus [@[% Myyntipalkkio]] näkyy kaavarivillä tähden jälkeen.
6. 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.

1. Valitse esimerkkilaskentataulukon solu E2
2. 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.

1. Valitse mikä tahansa taulukon solu, jotta valintanauhan Taulukon rakennenäkymä -välilehti tulee näkyviin.
2. Kirjoita haluamasi nimi Taulukon nimi -ruutuun ja paina Enter-näppäintä.

Esimerkkitiedoissa käytettiin nimeä OsastoMyynti.

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

• Käytä kelvollisia merkkejä Aloita nimi aina kirjaimella, alaviivamerkillä (_) tai kenoviivalla (\). Käytä nimen jäljellä olevassa osassa kirjaimia, lukuja, pisteitä ja alaviivamerkkejä. Nimi ei voi olla "C", "c", "R" tai "r", koska 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 sama 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ä (.) sanojen erottimina. 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 suunnitelmissa on käyttää taulukoita, Pivot-taulukoita ja kaavioita, on suositeltavaa lisätä nimien etuliitteeksi objektityyppi. 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 järjestetyssä luettelossa Nimien hallinnassa.

Rakenteellisen viittauksen syntaksisäännöt

Voit myös kirjoittaa tai muuttaa kaavan rakenteellisia viittauksia manuaalisesti, mutta tällöin on hyvä ymmärtää rakenteellisen viittauksen syntaksi. 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ääritteet:[Myyntisumma]ja[Myyntipalkkion määrä] ovat sarakemääritteitä, jotka käyttävät niitä edustavien sarakkeiden nimiä. Ne viittaavat sarakkeen tietoihin ilman sarakeotsikko- tai summarivejä. Kirjoita määritteet aina hakasulkeisiin esimerkissä kuvatulla tavalla.
• Kohdemääritteet:[#Totals] ja [#Data] ovat erikoiskohteiden määritteitä, jotka viittaavat taulukon tiettyihin osiin, kuten summariville.
• 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 taulukon nimellä alkava ja sarakemääritteeseen päättyvä merkkijono.

Voit luoda tai muokata rakenteellisia viittauksia manuaalisesti näiden syntaksisääntöjen avulla:

• 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. Esimerkiksi =OsastoMyynti[[Myyjä]:[Alue]]
• Kaikki sarakeotsikot ovat tekstimerkkijonoja Ne eivät kuitenkaan edellytä lainausmerkkejä, kun niitä käytetään rakenteellisissa viittauksissa. 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, mikä tarkoittaa, että sarakemääritteessä on käytettävä kaksinkertaisia hakasulkeita. Esimerkki: =OsastoMyyntiTVYhteenveto[[Kokonaismäärä $]]

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

• Sarkain
• Rivinsiirto
• Carriage return
• Pilkku (,)
• Kaksoispiste (:)
• Piste (.)
• Vasen hakasulje ([)
• Oikea hakasulje (])
• ristikkomerkki (#)
• puolilainausmerkki (')
• Lainausmerkki (")
• Vasen aaltosulje ({)
• Oikea aaltosulje (})
• dollarimerkki ($)
• Sirkumfleksi (^)
• et-merkki (&)
• Tähti (*)
• Plusmerkki (+)
• Yhtäläisyysmerkki (=)
• Miinusmerkki (-)
• Suurempi kuin -symboli (>)
• Pienempi kuin -symboli (<)
• jakomerkki (/)
• at-merkki (@)
• Kenoviiva (\)
• Huutomerkki (!)
• Vasen sulkumerkki (()
• Oikea sulje ())
• Prosenttimerkki (%)
• Kysymysmerkki (?)
• Tarkennettu merkki (')
• Puolipiste (;)
• Aaltoviiva (~)
• Alaviiva (_)

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

Seuraavassa on luettelo erikoismerkeistä, jotka edellyttävät kaavassa ohjausmerkkiä ('):

• Vasen hakasulje ([)
• Oikea hakasulje (])
• ristikkomerkki (#)
• puolilainausmerkki (')
• at-merkki (@)

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 hakasulkeen ([) jälkeen
• ennen viimeistä oikeaa hakasuljetta (]).
• pilkun 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 #This rivimää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 haluat esimerkiksi luoda Myyntipalkkion määrä -nimisen lasketun sarakkeen, joka laskee myyntipalkkion summan euroina, 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, mutta jos 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 otsikkona tai summarivinä, palautuu #VALUE! -virhe. 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 asiat, 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.

• Luodaanko rakenteellisia viittauksia puolivalintojen taulukoille Kun luot kaavan, oletusarvoisesti solualueen valitseminen taulukosta valitsee solut puolittain ja lisää automaattisesti kaavaan rakenteellisen viittauksen solualueen sijasta. 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 Tiedoston>asetukset>-valintaikkunassa Kaavojen>käsitteleminen .

• Ulkoisia linkkejä 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 avattuna Excelissä, jotta #REF!- virheitä ei tule linkit sisältävään kohdetyökirjaan. Jos avaat kohdetyökirjan ensin ja saat #REF!- virheitä, ne ratkeavat sillä, että avaat lähdetyökirjan. Jos avaat lähdetyökirjan ensin, virhekoodeja ei pitäisi tulla näkyviin.

• Alueen muuntaminen taulukoksi ja taulukon muuntaminen alueeksi Kun muunnat taulukon alueeksi, kaikki soluviittaukset muuttuvat vastaaviksi absoluuttisiksi A1-tyyliviittauksiksi. Kun muunnat alueen taulukoksi, Excel ei muuta automaattisesti alueen soluviittauksia 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älilehden >otsikkorivillä. Jos poistat taulukon sarakeotsikot käytöstä, ne eivät vaikuta sarakenimiä käyttäviin rakenteellisiin viittauksiin, vaan voit edelleen käyttää niitä kaavoissa. Jäsennetyt viittaukset, jotka viittaavat suoraan taulukon otsikoihin (esimerkiksi =OsastoMyynti[[#Headers],[%Myyntipalkkio]]), johtavat #REF.

• Sarakkeiden ja rivien lisääminen tai poistaminen taulukosta Koska taulukon tietoalueet muuttuvat usein, rakenteellisten 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, kun taas kun täytät kaavan, täydelliset rakenteelliset viittaukset muokkaavat sarakemääritteitä sarjana, kuten seuraavassa yhteenvetotaulukossa on esitetty.

Jos täyttösuuntana on:	ja painat täytön aikana seuraavaa painiketta:	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	Nykyiset soluarvot siirretään ja sarakemääritteet lisätään korvaamatta nykyisiä soluarvoja.

Tarvitsetko lisätietoja?

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

Aiheeseen liittyvät artikkelit

Yleistä Excel-taulukoista
Taulukoiden luominen ja muotoileminen
Tietojen laskeminen yhteen Excel-taulukossa
Excel-taulukon muotoileminen
Taulukon koon muuttaminen lisäämällä tai poistamalla rivejä ja sarakkeita
Alueen tai taulukon tietojen suodattaminen
Taulukon muuntaminen alueeksi
Excel-taulukoiden yhteensopivuusongelmat
Excel-taulukon vieminen SharePointiin
Yleisiä tietoja kaavoista Excelissä