Liigendatud viidete kasutamine Exceli tabelite abil

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.

Erimärkidega veerupäiste ümber nurksulgude kasutamine    Kui on erimärke, peab kogu veerupäis olema sulgudes, mis tähendab, et veerutunnuses on nõutavad topeltnurksulud. Näide: =Osak_müügisummaFYKokkuvõ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ärk (@)

• Kurakriips (\)

• Hüüumärk (!)

• Vasaksulg (()

• Paremsulg ())

• Protsendimärk (%)

• Küsimärk (?)

• Backtick (')

• Semikoolon (;)

• Tilde (~)

• Allkriips (_)

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

Järgmises loendis on erimärgid, mis vajavad valemis paomärki (').

• Vasak nurksulg ([)

• Parem nurksulg (])

• Trellid (#)

• Ühekordne jutumärk (')

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

Eriandmetunnused

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

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.

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

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    Tabeli veerupäiseid saate sisse ja välja lülitada menüüs Kujundus > Päiserida. Kui lülitate tabeli veerupäised välja, ei mõjuta see liigendatud viiteid, mis kasutavad veerunimesid, ja saate neid siiski valemites kasutada. Liigendatud viited, mis viitavad otse tabelipäistele (nt =Osak_müük[[#Headers],[Vahendustasu %]]) annavad tulemiks #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.

Kas vajate rohkem abi?

Kui teil on küsimusi, saate need esitada Exceli tehnikakogukonnafoorumis, kus teile vastavad asjatundjad, või teistele kasutajatele kogukonnafoorumis.

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