Liigendatud viidete kasutamine Exceli tabelites

Exceli tabeli loomisel määrab Excel tabelile ja igale tabeli veerupäisele nime. Kui lisate Exceli tabelisse valemid, saab need nimed valemi sisestamisel ja tabelis lahtriviidete valimisel automaatselt kuvada – te ei pea neid käsitsi sisestama. Excel käitub umbes järgmiselt.

Konkreetsete lahtriviidete kasutamise asemel

Excel kasutab tabeli- ja veerunimesid

=Sum(C2:C7)

=SUM(Osak_müük[Müügisumma])

Seda tabeli- ja veerunimede kombinatsiooni nimetatakse liigendatud viiteks. Liigendatud viidetes olevaid nimesid täpsustatakse iga kord, kui lisate tabelisse andmeid või eemaldate neid.

Liigendatud viited kuvatakse ka siis, kui loote väljaspool Exceli tabelit valemi, mis viitab tabeliandmetele. Viidete abil on lihtsam tabeleid mahukas töövihikus üles leida,

Valemisse liigendatud viidete kaasamiseks klõpsake tabelilahtreid, millele soovite viidata, mitte ärge tippige lahtriviidet valemisse. Järgmiste näidisandmete abil sisestame valemi, mis kasutab müügi vahendustasu summa arvutamiseks automaatselt liigendatud viiteid.

Müügi- esindaja

Piirkond

Müügisumma

Vahendustasu %

Vahendustasu summa

Jaak

Põhi

260

10%

Ivo

Lõuna

660

15%

Agnes

Ida

940

15%

Henrik

Lääs

410

12%

Aigi

Põhi

800

15%

Elmu

Lõuna

900

15%

  1. Kopeerige ülaltoodud tabelis asuvad näidisandmed (sh veerupäised) ja kleepige need uue Exceli töölehe lahtrisse A1.

  2. Valige tabeli loomiseks andmevahemikus suvaline lahter ja vajutage klahvikombinatsiooni CTRL + T.

  3. Veenduge, et ruut Minu tabelil on päised oleks märgitud, ja seejärel klõpsake nuppu OK.

  4. Tippige lahtrisse E2 võrdusmärk (=) ja klõpsake lahtrit C2.

    Valemiribal kuvatakse pärast võrdusmärki liigendatud viide [@[Müügisumma]].

  5. Tippige kohe paremsulu järele tärn (*) ja klõpsake lahtrit D2.

    Valemiribal kuvatakse pärast tärni liigendatud viide [@[Vahendustasu %]].

  6. Vajutage sisestusklahvi (ENTER).

    Excel loob arvutusliku veeru automaatselt ja kopeerib valemi teie eest tervesse veergu, kohandades seda iga rea jaoks.

Mis juhtub konkreetsete lahtriviidete kasutamisel?

Kui sisestate arvutatud veergu konkreetsed lahtriviited, võib olla raskem näha, mida valem arvutab.

  1. Klõpsake näitetöölehel lahtrit E2.

  2. Sisestage valemiribale väärtus =C2*D2 ja vajutage sisestusklahvi (ENTER).

Pange tähele, et kui Excel kopeerib teie valemi veerus allapoole, ei kasuta valem liigendatud viiteid. Kui lisate näiteks praeguste veergude C ja D vahele uue veeru, peate valemit muutma.

Kuidas tabeli nime muuta?

Iga kord, kui loote mõne Exceli tabeli, loob Excel vaikimisi tabelinime (Tabel1, Tabel2 jne). Soovi korral saate tabeli nime endale tähendusrikkamaks muuta.

  1. Valige lindil menüü Tabeliriistad > Kujundus kuvamiseks tabelis mõni lahter.

  2. Sisestage väljale Tabeli nimi soovitud nimi ja vajutage sisestusklahvi (ENTER).

Näidisandmetes kasutasime nime Osak_müük.

Kasutage tabelinimede jaoks järgmisi reegleid.

  • Kasutage sobivaid märke. Alustage nime alati tähe, allkriipsu (_) või kurakaldkriipsuga (\). Ülejäänud nime jaoks võite kasutada tähti, numbreid, punkte ja allkriipse. Nime jaoks ei saa kasutada tähti „C“, „c“, „R“ ega „r“, kuna need on juba määratud aktiivse lahtri veeru või rea valimise otseteena, kui sisestate need tähed väljale Nimi või Minek.

  • Ärge kasutage lahtriviiteid. Nimed ei või kattuda lahtriviitega (nt Z$100 või R1C1).

  • Ärge kasutage sõnade eraldamiseks tühikuid.  Tühikuid ei saa nimes kasutada. Võite kasutada sõnade eraldamiseks allkriipsu (_) või punkti (.). Näide: RiiklikMaks, Osak_müük või Esimene.kvartal.

  • Ärge kasutage üle 255 märgi. Tabeli nimi võib sisaldada kuni 255 märki.

  • Kasutage kordumatuid tabelinimesid. Topeltnimed pole lubatud. Excel ei erista nimedes suur- ja väiketähti: kui olete sisestanud nime „Müük“, ent teil on samas töövihikus juba olemas teine nimi „MÜÜK“, palutakse teil valida kordumatu nimi.

  • Kasutage objektiidentifikaatorit.  Kui kavatsete kasutada tabelite, PivotTable-liigendtabelite, diagrammide ja PivotChart-liigenddiagrammide kooslust, on hea mõte lisada nimedele objektitüübile viitavad eesliited. Näide: tbl_Müük müügitabeli, pt_Müük PivotTable-liigendtabelist müügitabeli ja chrt_Müük müügidiagrammi jaoks või ptchrt_Müük müügi PivotChart-liigenddiagrammi jaoks. Nii on kõik nimed esitatud nimehalduri korrapärase loendina.

Liigendatud viidete süntaksireeglid

Liigendatud viiteid saate ka käsitsi muuta või valemitesse sisestada, kuid selleks oleks hea mõista liigendatud viite süntaksit. Vaadake lähemalt järgmist valeminäidet.

=SUM(Osak_müük[[#Kogusummad];[Müügisumma]];Osak_müük[[#Andmed];[Vahendustasu summa]])

See valem koosneb järgmistest liigendatud viite komponentidest.

  • Tabeli nimi:   Osak_müük on kohandatud tabelinimi. Nimi viitab tabeliandmetele, kuid mitte päistele ega summaridadele. Võite kasutada vaiketabelinime (nt Tabel1) või seda kohandatud nime kasutamiseks muuta.

  • Veerutunnus:   [Müügisumma] ja [Vahendustasu summa] on veerutunnused, mis kasutavad vastavate veergude nimesid. Need viitavad veeruandmetele (v.a veerupäisele ja summareale). Pange tunnused alati nurksulgudesse, nagu joonisel näidatud.

  • Andmetunnused:   [#Kogusummad] ja [#Andmed] on eriandmetunnused, mis viitavad tabeli kindlatele osadele, näiteks summareale.

  • Tabelitunnus:   [[#Kogusummad],[Müügisumma]] ja [[#Andmed],[Vahendustasu summa]] on tabelitunnused, mis tähistavad liigendatud viite väliseid osi. Välised viited järgnevad tabeli nimele ja need tuleb alati panna nurksulgudesse.

  • Liigendatud viide:   (Osak_müük[[#Kogusummad];[Müügisumma]] ja Osak_müük[[#Andmed];[Vahendustasu summa]] on liigendatud viited, mida tähistab tervikstring, mis algab tabeli nimega ja lõpeb veerutunnusega.

Liigendatud viidete käsitsi loomisel või redigeerimisel kasutage järgmisi süntaksireegleid.

  • Pange tunnused nurksulgudesse.    Kõik tabeli-, veeru- ja eriandmetunnused peavad olema paarisnurksulgudes ([ ]). Tunnus, mis sisaldab muid tunnuseid, nõuab vastavaid väliseid sulge muude tunnuste vastavate sisemiste sulgude haaramiseks. Näide: =Osak_müük[[Müügiesindaja]:[Piirkond]]

  • Kõik veerupäised on tekstistringid.    Nende kasutamisel liigendatud viites pole aga jutumärke vaja. Tekstistringidena käsitletakse ka arve või kuupäevi (nt 2014 või 01.01.2014). Veerupäistes ei saa kasutada avaldisi. Avaldis Osak_müükFAKokkuvõte[[2014]:[2012]] näiteks ei toimi.

Pange erimärke sisaldavad veerupäised nurksulgudesse.    Kui veerupäis sisaldab erimärke, tuleb terve veerupäis panna nurksulgudesse. See tähendab, et veerutunnuse jaoks tuleb kasutada topeltnurksulge. Näide: =Osak_müükFAKokkuvõte[[Kokku $ summa]]

Valemis tuleb lisasulge kasutada järgmiste erimärkide korral.

  • Tabeldusmärk

  • Reavahetus

  • Tagasijooks

  • Koma (,)

  • Koolon (:)

  • Punkt (.)

  • Vasak nurksulg ([)

  • Parem nurksulg (])

  • Trellid (#)

  • Ühekordne jutumärk (')

  • Topeltjutumärk (")

  • Vasak looksulg ({)

  • Parem looksulg (})

  • Dollarimärk ($)

  • Katusemärk (^)

  • Ampersand (&)

  • Tärn (*)

  • Plussmärk (+)

  • Võrdusmärk (=)

  • Miinusmärk (-)

  • Märk Suurem kui (>)

  • Märk Väiksem kui (<)

  • Jagamismärk (/)

  • Mõne erimärgi korral tuleb veerupäises kasutada paomärki.    Mõni märk on eritähendusega ja nende kasutamiseks tuleb paomärgina lisada ühekordne jutumärk ('). Näide: =Osak_müükFAKokkuvõte['#Üksusi]

Valemis tuleb paomärki (’) kasutada järgmiste erimärkide korral.

  • Vasak nurksulg ([)

  • Parem nurksulg (])

  • Trellid (#)

  • Ühekordne jutumärk (')

Liigendatud viite loetavuse parandamiseks kasutage tühikumärki.    Liigendatud viite loetavuse parandamiseks saate kasutada tühikumärke. Näide: =Osak_müük[ [Müügiesindaja]:[Piirkond] ] või =Osak_müük[[#Päised], [#Andmed], [Komisjonitasu %]]

Soovitatav on kasutada ühte tühikut:

  • pärast esimest vasakpoolset nurksulgu ([);

  • enne viimast parempoolset nurksulgu (]);

  • pärast koma.

Viitemärgid

Lahtrivahemike määramise hõlbustamiseks saate kasutada veerutunnuste kombineerimiseks järgmisi viitemärke.

See liigendatud viide:

Viitab vahemikule:

Kasutades tunnust:

Milleks on lahtrivahemik:

=Osak_müük[[Müügiesindaja]:[Piirkond]]

Kõik lahtrid kahes või enamas külgnevas veerus

: (koolon) vahemiku märk

A2:B7

=Osak_müük[Müügisumma],Osak_müük[Vahendustasu summa]

Kahe või enama veeru kombinatsioon

, (koma) ühendi märk

C2:C7, E2:E7

=Osak_müük[[Müügiesindaja]:[Müügisumma]] Osak_müük[[Piirkond]:[Vahendustasu %]]

Kahe või enama veeru ühisosa

 (tühik) ühisosa märk

B2:C7

Eriandmetunnused

Tabeli kindlatele osadele (nt ainult summareale) viitamiseks võite oma liigendatud viidetes kasutada mõnda järgmistest eriandmetunnustest.

See eriandmetunnus:

Viitab vahemikule:

#Kõik

Terve tabel, kaasa arvatud veerupäised, andmed ja summad.

#Andmed

Ainult andmeread.

#Päised

Ainult päiserida.

#Kogusummad

Ainult summarida. Kui seda ei ole, tagastab see valem nulli.

#See rida

või

@

või

@[Veeru nimi]

Ainult valemiga samas reas asuvad lahtrid. Neid tunnuseid ei saa kombineerida ühegi muu eriandmetunnusega. Kasutage neid viite ilmutamata ühisosana kehtestamiseks või ilmutamata ühisosana alistamiseks ja veeru üksikutele väärtustele viitamiseks.

Excel muudab tunnused #See rida tabelites, kus on rohkem kui üks andmerida, automaatselt lühemaks @-tunnuseks. Kui teie tabelis on aga ainult üks rida, ei asenda Excel tunnust #See rida, mis võib täiendavate ridade lisamisel põhjustada ootamatuid arvutustulemusi. Arvutusprobleemide vältimiseks lisage enne liigendatud viidetega valemite sisestamist tabelisse mitu rida.

Nõuetele vastavad liigendatud viited arvutatud veergudes

Arvutatud veeru loomisel kasutate sageli valemi loomiseks liigendatud viidet. See liigendatud viide võib olla nõuetele täielikult vastav või mittevastav. Näiteks komisjonitasude näitajat dollarites arvutava arvutatud veeru nimega „Vahendustasu summa“ loomiseks saate kasutada järgmisi valemeid.

Liigendatud viite tüüp

Näide

Kommentaar

Nõuetele mittevastav

=[Müügisumma]*[Vahendustasu %]

Korrutab praeguse rea vastavad väärtused.

Nõuetele täielikult vastav

=Osak_müük[Müügisumma]*Osak_müük[Vahendustasu %]

Korrutab mõlema veeru kõikide ridade vastavad väärtused.

Üldreegel, mida järgida: kui kasutate liigendatud viiteid tabelis (nt arvutatud veeru loomisel), saate kasutada nõuetele mittevastavat liigendatud viidet. Kui kasutate liigendatud viidet tabelist väljaspool, peate kasutama nõuetele täielikult vastavat liigendatud viidet.

Näiteid liigendatud viidete kasutamise kohta

Allpool kirjeldatakse mõnda liigendatud viidete kasutamise võimalust.

See liigendatud viide:

Viitab vahemikule:

Milleks on lahtrivahemik:

=Osak_müük[[#Kõik],[Müügisumma]]

Kõik lahtrid veerus Müügisumma.

C1:C8

=Osak_müük[[#Päised],[Vahendustasu %]]

Veeru „Vahendustasu %“ päis.

D1

=Osak_müük[[#Kogusummad],[Piirkond]]

Veeru Piirkond summa. Kui summarida pole, tagastab see nulli.

B8

=Osak_müük[[#Kõik],[Müügisumma]:[Vahendustasu %]]

Kõik lahtrid veergudes „Müügisumma“ ja „Vahendustasu %“.

C1:D8

=Osak_müük[[#Andmed],[Vahendustasu %]:[Vahendustasu summa]]

Ainult veergude „Vahendustasu %“ ja „Vahendustasu summa“ andmed.

D2:E7

=Osak_müük[[#Päised],[Piirkond]:[Vahendustasu summa]]

Ainult veergude „Piirkond“ ja „Vahendustasu summa“ vahel olevate veergude päised.

B1:E1

=Osak_müük[[#Kogusummad],[Müügisumma]:[Vahendustasu summa]]

Veergude „Müügisumma“ kuni „Vahendustasu summa“ kogusummad. Kui summarida pole, tagastab see nulli.

C8:E8

=Osak_müük[[#Päised],[#Andmed],[Vahendustasu %]]

Ainult veeru „Vahendustasu %“ päis ja andmed.

D1:D7

=Osak_müük[[#See rida], [Vahendustasu summa]]

või

=Osak_müük[@Vahendustasu summa]

Praeguse rea ja veeru „Vahendustasu summa“ ristumiskohas olev lahter. Kui seda kasutatakse päise või summareaga samas reas, tagastab vea #VALUE!.

Selle liigendatud viite pikema variandi (#See rida) tippimisel mitme andmereaga tabelisse asendab Excel selle automaatselt lühema variandiga (@). Mõlemad viitevariandid toimivad sarnaselt.

E5 (kui praegune rida on 5)

Liigendatud viidetega töötamise strateegiad

Töötamisel liigendatud viidetega kaaluge järgmiste võimaluste kasutamist.

  • Valemi automaatteksti funktsiooni kasutamine    Võite avastada, et funktsiooni Valemi automaattekst kasutamisest on palju kasu liigendatud viidete sisestamisel ja õige süntaksi kasutamise tagamisel. Lisateavet leiate artiklist Valemi automaatteksti funktsiooni kasutamine.

  • Tabelite jaoks liigendatud viidete võimalik loomine poolvalikutes    Kui loote valemit ja klõpsate selleks tabeli lahtrivahemikku, valib tabel vaikimisi pooleldi lahtrid ja sisestab valemis oleva lahtrivahemiku asemel automaatselt liigendatud viite. Selline poolvalikuna käitumine teeb liigendatud viite sisestamise palju lihtsamaks. Saate selle käitumise sisse ja välja lülitada, märkides või tühjendades ruudu Kasuta valemites tabelinimesid, mille leiate, valides Fail > Suvandid > Valemid > Valemitega töötamine.

  • Töötamine töövihikutega, mis sisaldavad väliseid linke teistes töövihikutes asuvatele Exceli tabelitele.    Kui töövihik sisaldab välist linki mõnes teises töövihikus asuvale Exceli tabelile, peab see lingitud lähtetöövihik olema Excelis avatud, kuna muidu kuvatakse linke sisaldavas sihttöövihikus veateated #REF!. Kui avate esmalt sihttöövihiku ja näete veateateid #REF!, avage lähtetöövihik ja vead lahendatakse. Kui avate lähtetöövihiku esimesena, ei peaks te veateateid nägema.

  • Vahemiku teisendamine tabeliks ja tabeli teisendamine vahemikuks.    Tabeli teisendamisel vahemikuks muutuvad kõik lahtriviited nendega võrdseteks A1 laadis absoluutviideteks. Vahemiku teisendamisel tabeliks ei muuda Excel ühtegi selle vahemiku lahtriviidet automaatselt sellega võrdseks liigendatud viiteks.

  • Veerupäiste väljalülitamine.    Saate tabeli veerupäised lülitada sisse või välja, valides Kujundus > Päiserida. Kui lülitate tabeli veerupäised välja, ei mõjuta see vastavaid päiseid kasutavaid liigendatud viiteid. Seega saate neid valemites edasi kasutada. Liigendatud viited, mis viitavad otseselt tabelipäistele (nt=Osak_müük[[#Päised];[Vahendustasu %]])) tagastavad tulemusena vea #REF.

  • Veergude ja ridade tabelisse lisamine ja sealt kustutamine.    Kuna tabeli andmevahemikud muutuvad sageli, kohandatakse liigendatud viidete lahtriviiteid automaatselt. Kui kasutate näiteks tabeli nime valemis, mille eesmärk on loendada kõik tabelis leiduvad andmelahtrid, ja seejärel lisate uue andmerea, kohandatakse lahtriviidet automaatselt.

  • Tabeli või veeru nime muutmine    Kui muudate tabeli või veeru nime, muudab Excel automaatselt selle tabeli või veeru päise kasutust kõigis töövihikus kasutatavates liigendatud viidetes.

  • Liigendatud viite teisaldamine, kopeerimine ja täitmine.    Liigendatud viidet kasutava valemi kopeerimisel ja teisaldamisel jäävad kõik liigendatud viited samaks.

    Märkus. : Liigendatud viite kopeerimine ja liigendatud viite täitmine pole sama. Kopeerimisel jäävad liigendatud viited samaks, valemi täitmisel saavad nõuetele täielikult vastavad liigendatud viited kohandada veerutunnuseid (nt sarju) vastavalt järgmises tabelis olevale kokkuvõttele.

Kui täitesuund on:

Ja täitmise ajal vajutate klahvi:

Siis:

Üles või alla

Puudub

Veerutunnust ei reguleerita.

Üles või alla

Juhtklahv (Ctrl)

Veerutunnuseid reguleeritakse sarjana.

Paremale või vasakule

Pole

Veerutunnuseid reguleeritakse sarjana.

Üles, alla, paremale või vasakule

Tõstuklahv (Shift)

Praegustes lahtrites väärtuste ülekirjutamise asemel teisaldatakse praegused lahtriväärtused ja lisatakse veerutunnused.

Kas vajate rohkem abi?

Võite oma küsimuse alati esitada mõnele Exceli tehnikakogukonna eksperdile, otsida abi vastustefoorumist või soovitada mõnd uut funktsiooni või täiustust Exceli User Voice’i lehel.

Seotud teemad

Exceli tabelite ülevaade
Video: Exceli tabeli loomine ja vormindamine
Exceli tabeli andmete summeerimine
Exceli tabeli vormindamine
Tabeli suuruse muutmiseks ridade või veergude lisamine või eemaldamine
Vahemikus või tabelis olevate andmete filtreerimine
Tabeli teisendamine vahemikuks
Exceli tabeli ühilduvusprobleemid
Exceli tabeli eksportimine SharePointi
Exceli valemite ülevaade

Täiendage oma oskusi
Tutvuge koolitusmaterjalidega
Kasutage uusi funktsioone enne teisi
Liituge Office Insideri programmiga

Kas sellest teabest oli abi?

Täname tagasiside eest!

Täname tagasiside eest! Tundub, et võiksime teid kokku viia ühega meie Office'i tugiagentidest, kes aitab teil probleemi lahendada.

×