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

Tärkeää: Tämä on artikkeli on käännetty koneellisesti. Lue vastuuvapauslauseke. Tämän artikkelin englanninkielinen versio on täällä .

Kun Excel-taulukon luominen, Excel liittää nimi taulukko ja taulukon kunkin sarakkeen otsikkoa. Kun lisäät Excel-taulukon kaavat, ne nimet, voit näkyvät automaattisesti, kirjoittamalla kaavan ja valitse soluviittaukset taulukon sijaan kirjoittaa ne manuaalisesti. Tässä on esimerkki siitä, mitä Excel tekee:

Eksplisiittisten soluviittausten sijaan

Excel käyttää taulukon ja sarakkeiden nimiä

=Summa(C2:C7)

=SUMMA(OsastoMyynti[Myyntisumma])

Taulukon ja sarakkeiden nimien yhdistelmää kutsutaan jäsennetyksi viittaukseksi. Jäsennettyjen viittausten nimet muuttuvat sitä mukaa, kun lisäät tietoja taulukkoon tai poistat niitä.

Jäsennetyt viittaukset näkyvät myös, kun luot Excel-taulukon ulkopuolella kaavan, jossa on viittauksia taulukon tietoihin. Viittaukset voivat helpottaa taulukoiden löytämistä suurista työkirjoista.

Voit lisätä jäsennettyjä viittauksia kaavaan napsauttamalla viitattavia taulukon soluja sen sijaan, että kirjoitat niiden soluviittauksen kaavaan. Seuraavia esimerkkitietoja käyttämällä voit kirjoittaa kaavan, joka käyttää automaattisesti jäsennettyjä viittauksia myyntiprovision laskemiseen.

Myynti Henkilö

Alue

Myyntisumma

% Myyntipalkkio

Myyntipalkkionmää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 esimerkkitiedot yllä olevaan taulukkoon, mukaan lukien sarakeotsikot, ja liitä se uuteen Excel-laskentataulukon soluun A1.

  2. Jos haluat luoda taulukon, valitse mikä tahansa solu tietoalueen ja painamalla Ctrl + T.

  3. Varmista, että taulukossa on otsikot -valintaruutu on valittuna, ja valitse OK.

  4. Kirjoita soluun E2 yhtäläisyysmerkki (=) ja napsauta solua C2.

    Rakenteellinen viittaus [@[Myyntisumma]] näkyy kaavarivillä yhtäläisyysmerkin jälkeen.

  5. Kirjoita tähti (*) heti oikeanpuoleisen hakasulkeen jälkeen ja napsauta solua D2.

    Rakenteellinen viittaus [@[% Myyntipalkkio]] näkyy kaavarivillä tähden jälkeen.

  6. Paina 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. Napsauta-mallitaulukossa solua 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 solu taulukon kohtaa Näytä Taulukkotyökalut > rakenne -välilehden valintanauhasta.

  2. Kirjoita Taulukkonimi -ruutuun haluamasi nimi ja paina Enter-näppäintä.

Esimerkkitiedoissa on käytetty nimeä OsastoMyynti.

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

  • Käytä Kelvollisia merkkejä  Käynnistä aina kirjaimella, nimi alaviivaa (_) tai kenoviiva (\). Käytä kirjaimia, numeroita, pisteitä ja alaviivoja muiden nimi. Et voi käyttää ”C”, ”c”, ”R” tai ”r” nimen, koska ne on jo määritetty pikakuvakkeen valitsemalla sarakkeen tai rivin aktiivisen solun, kun kirjoitat ne nimi tai Siirry -ruutuun.

  • Älä käytä soluviittauksia  Nimet eivät saa olla sama kuin soluviittaus, esimerkiksi Z$ 100 tai R1C1.

  • Älä käytä välilyönnillä sanat  Nimessä ei voi käyttää välilyöntejä. Voit käyttää alaviivaa (_) ja piste (.) Wordin erottimiksi. Esimerkiksi OsastoMyynti, Yrityksen_Verotus tai ensimmäinen.Vuosineljännes.

  • Älä käytä yli 255 merkkiä Taulukon nimi voi olla enintään 255 merkkiä pitkä.

  • 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.

  • Käytä objektitunnus  Jos aio ottaa taulukot, pivot-taulukoiden sekä kaavioita, on hyvä etuliite nimet objektityypin kanssa. Esimerkki: tbl_Sales sales-taulukon, pt_Sales myynnin pivot-taulukko ja chrt_Sales myynnin kaavioon tai myynnin pivot-kaavion ptchrt_Sales. Tämä säilyttää kaikki nimet järjestetyn luettelon Nimien hallinta.

Rakenteellisen viittauksen syntaksisäännöt

Voit myös kirjoittaa tai muuttaa jäsennettyjä viittauksia manuaalisesti kaavassa, mutta voit tehdä sen se auttaa ymmärtämään jäsennetyn viittauksen syntaksista. Mennään päälle kaavan seuraavassa esimerkissä:

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

Tässä kaavassa on seuraavat rakenteellisen viittauksen osat:

  • Taulukkonimi:    Mukautetun taulukkonimi on OsastoMyynti . Se viittaa taulukkotiedot ilman rivien ylä- tai summa. Voit käyttää oletusarvon taulukon nimen, kuten Taulukko1, tai muuta sen asetukseksi Määritä mukautettu nimi.

  • Sarakemääritteeseen:   [MyyntiSumma]ja[Myyntipalkkionmäärä] ovat sarakemääritteitä, jotka käyttävät ne edustavat sarakkeiden nimet. Ne viittaavat sarakkeen tiedot ilman minkä tahansa sarakkeen ylä- tai Summa-rivi. Kirjoita määritteet aina hakasulkeissa, kuten.

  • Kohteen määrite:   [#Totals] ja [#Data] ovat erikoiskohteen määritteitä, jotka viittaavat taulukon, kuten summariviin tiettyihin osiin.

  • 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.

  • Jäsennetty viittaus:   (OsastoMyynti [[#Totals], [MyyntiSumma]] ja OsastoMyynti [[#Data], [Myyntipalkkionmäärä]] ovat rakenteellisia viittauksia, joita edustaa taulukon nimellä alkava ja sarakemääritteeseen päättyvä merkkijono.

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

  • Käytä hakasulkeita ympärillä hakasulkeita    Kaikki taulukon sarakkeen ja erikoiskohteiden määritteet on oltava lainausmerkkien hakasulkeita ([]). Määrite, joka sisältää muihin määritteet edellyttää ulompi vastaavat sulkeet kehystettävä muiden määritteet hakasulkeita. Esimerkki: = OsastoMyynti [[Myyjä]: [alue]]

  • Sarakeotsikot ovat tekstimerkkijonoja    Mutta ne eivät tarvitse lainausmerkkejä, kun niitä käytetään jäsennetyssä viittauksessa. Numeroita tai päivämääriä, kuten 2014 tai 1/1/2014 pidetään myös tekstimerkkijonoja. Et voi käyttää lausekkeiden sarakeotsikoiden. 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

  • Rivin lisäys

  • Rivinvaihto

  • Pilkku (,)

  • Kaksoispiste (:)

  • Piste (.)

  • Vasen hakasulje ([)

  • Oikea hakasulje (])

  • Ristikkomerkki (#)

  • Puolilainausmerkki (')

  • Lainausmerkki (")

  • Vasen aaltosulje ({)

  • Oikea aaltosulje (})

  • Dollarimerkki ($)

  • Sirkumfleksi (^)

  • Et-merkki (&)

  • Tähtimerkki (*)

  • Plusmerkki (+)

  • Yhtäläisyysmerkki (=)

  • Miinusmerkki (-)

  • Suurempi kuin -merkki (>)

  • Pienempi kuin -merkki (<)

  • Jakomerkki (/)

  • 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 edellyttävät kaavassa ohjausmerkkiä ('):

  • Vasen hakasulje ([)

  • Oikea hakasulje (])

  • Ristikkomerkki (#)

  • Puolilainausmerkki (')

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 #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ä jäsennetyn viittauksen avulla. Tämä jäsennetty viittaus voi olla hyväksymätön tai täysin hyväksytty. Voit luoda esimerkiksi Myyntipalkkion määrä -nimisen lasketun sarakkeen, joka laskee myyntipalkkion summan euroina käyttämällä 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ä]

Solun nykyisen rivin ja Myyntipalkkionmäärä-sarakkeen leikkauskohdassa. Jos ylä-tai summarivi samalla rivillä, tämä palauttaa #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 näkökohdat, kun käsittelet jäsennettyjä viittauksia.

  • Kaavan automaattisen täydennyksen käyttäminen    Saatat huomata, että kaavan automaattisen täydennyksen käyttäminen on erittäin hyödyllinen, kun syötät jäsennettyjä viittauksia ja haluat varmistaa syntaksin oikeellisuuden. Lisätietoja on artikkelissa Kaavan automaattisen täydennyksen käyttäminen.

  • Luodaanko jäsennettyjä viittauksia puolipisteillä valinnat taulukoista    Kun luot oletusarvoisesti kaavaan napsauttamalla solua alueen sisällä taulukon puolipisteillä valitsee solut ja lisää automaattisesti kaavan jäsennetyn viittauksen sen sijaan, että solualue. Ongelman puolivalinnan on helpompi antamaan jäsennetyn viittauksen. Voit ottaa tämän toiminnon käyttöön tai poistaminen käytöstä tai poistamalla Käytä taulukoiden nimiä kaavoissa -valintaruutu, jos Tiedosto > asetukset > kaavat > käsiteltäessä kaavoja -valintaikkuna.

  • Käytä työkirjat sisältävät ulkoisia linkkejä muiden työkirjojen Excel-taulukoihin    Jos työkirja sisältää ulkoisen linkin toisen työkirjan Excel-taulukkoon, linkitetyn tietolähteen työkirjan on oltava avoinna Excelissä voit välttää #REF! kohdetyökirja linkit sisältävä virheet. Jos Avaa kohdetyökirja ja #REF! virheitä tulevat näkyviin, käyttäjät voivat ratkaista Jos avaat lähdetyökirja. Jos avaat lähdetyökirja, ei ole virhekoodit pitäisi näkyä.

  • Muunna alueeksi taulukoksi ja taulukon muuntaminen alueeksi    Kun taulukon muuntaminen alueeksi, kaikki soluviittaukset muuttuvat niiden vastaavat suora A1 tyyppisiksi viittauksiksi. Kun alueen muuntaminen taulukoksi, Excel ei muuttuvat tämän alueen kaikki soluviittaukset automaattisesti niiden vastaavat jäsennettyjä viittauksia.

  • Sarakeotsikoiden poistaminen käytöstä    Voit vaihtaa taulukon sarakeotsikoiksi käyttöön ja poistaminen käytöstä taulukko rakenne -välilehdestä > Otsikkorivi. Jos poistat taulukon sarakeotsikoiksi käytöstä, jäsennettyjä viittauksia, jotka käyttävät sarakkeiden nimet eivät muutu ja voit silti käyttää niitä kaavoissa. Jäsennettyjen viittausten käyttöön, jotka viittaavat taulukon otsikoita (esimerkiksi = OsastoMyynti [[#Headers], [% Myyntipalkkio]]) aiheuttaa #REF.

  • Lisää tai poista sarakkeiden ja rivien taulukko    Koska taulukon tietoalueet muuttuvat usein, Jäsennettyjen viittausten soluviittaukset mukautuvat automaattisesti. Esimerkiksi jos taulukon nimen käyttäminen kaavassa voit laskea kaikki tiedot taulukon solut ja lisäät sitten tietorivin, soluviittaus säätää 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.

  • Siirrä, kopioi ja täytä jäsennettyjä viittauksia    Kaikki jäsennetyt viittaukset pysyvät ennallaan, kun kopioit tai siirrät jäsennetyn viittauksen käyttävän kaavan.

    Huomautus: Jäsennetyn viittauksen kopioiminen ja tekevät jäsennetyn viittauksen täytön eivät ole sama. Kun olet kopioinut kaikki jäsennetyt viittaukset pysyvät ennallaan, samalla kun täytät kaavan, täysin hyväksytyt jäsennetyt viittaukset muuttaa sarakemääritteitä sarjana seuraavassa taulukossa on esitetty, kuten.

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, saada tukea yhteisön vastauksista tai ehdottaa uutta ominaisuutta tai parannusta Excel User Voice -sivustolla.

Aiheeseen liittyvät artikkelit

Yleistä Excel-taulukoista
Video: Voit luoda ja muokata Excel-taulukon
tietojen laskeminen yhteen Excel-taulukon
Excel-taulukon muotoileminen

Alueen tai taulukon tietojen suodattaminen
Muunna taulukko alueeksi
Excel-taulukoiden yhteensopivuusongelmat
vieminen SharePointiin Excel-taulukon
yhteenvetoja kaavoissa. Excelin

Huomautus: Konekäännöksestä ilmoittava vastuusvapauslauseke: Tämä artikkeli on käännetty tietokonejärjestelmällä, eikä kieliasiantuntija ole muokannut sitä. Microsoft tarjoaa nämä konekäännökset avuksi muille kuin englantia puhuville käyttäjille, jotta he saavat lisätietoja Microsoftin tuotteista, palveluista ja tekniikoista. Koska artikkeli on koneellisesti käännetty, se saattaa sisältää sanasto-, lauseoppi- ja kielioppivirheitä.

Kehitä taitojasi
Tutustu koulutusmateriaaliin
Saat uudet ominaisuudet ensimmäisten joukossa
Liity Office Insider -käyttäjiin

Oliko näistä tiedoista hyötyä?

Kiitos palautteesta!

Kiitos palautteestasi! Näyttää siltä, että Office-tukiedustajamme avusta voi olla sinulle hyötyä.

×