Liigendatud viidete kasutamine Exceli tabelite abil

NB! :  See artikkel on masintõlgitud, vaadake lahtiütlust. Selle artikli ingliskeelse versiooni leiate aadressilt (viiteks).

Kui Exceli tabeli loomineExcel määrab nimi tabeli ja iga tabeli veerus. Exceli tabeli valemites lisamisel nende nimesid saab automaatselt kui sisestate valemi ja valige lahtriviited tabelis neid käsitsi sisestamise asemel. Siin on näide sellest, mida Excel ei:

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üügiesindaja

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 tühja Exceli töölehe lahtrisse A1.

  2. Tabeli loomiseks valige lahter A1 ja vajutage klahvikombinatsiooni Ctrl+T.

  3. Veenduge, et ruut Minu tabelil on päised oleks märgitud, ja klõpsake siis 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]].

  1. Tippige kohe lõpusulu järele tärn (*) ja klõpsake lahtrit D2.

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

  1. Vajutage sisestusklahvi (Enter).

Excel loob arvutatud veeru automaatselt ning kopeerib valemi terve veeru jaoks, mida iga rea jaoks täpsustades. Valemite ja arvutatud veergude kohta leiate lisateavet teemast valemite kasutamine Exceli tabeli.

Mis juhtub konkreetsete lahtriviidete kasutamisel?

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

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

  2. Sisestage valemiribale = 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. Menüü Tabeliriistad kuvamiseks valige Exceli tabelis suvaline lahter.

  2. Sisestage soovitud nimi menüüs Kujundus väljale Tabeli nimi ja vajutage sisestusklahvi (Enter).

Näidisandmetes kasutasime nime Osak_müük.

Kasutage tabelinimede jaoks järgmisi reegleid.

  • Kasutage v alid märke Alati algama tähega, nimi allkriipsu (_) või kurakriipsu (\). Kasutage tähtede, numbrite, perioodid ja ülejäänud nime allkriipsumärkideks.

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 c lahter viited Nimed ei või kattuda lahtriviitega, nt Z$ 100 või R1C1.

  • Ärge kasutage s tempos sõnade eraldamiseks Nimes ei tohi kasutada tühikuid. Saate kasutada tühikuid või sisestage Wordi eraldavate allkriipsu (_) ja punkt (.). Näiteks Osak_müük, punkte 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.

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. Vaatame lähemalt järgmist valeminäidet:

=SUM(DeptSales[[#Totals],[Sales Amount]],DeptSales[[#Data],[Commission Amount]])

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    Kuid nad ei nõua hinnapakkumised, kui neid kasutatakse liigendatud viidetes. Arvude või kuupäevade, nt 2014 või 1/1/2014, käsitletakse ka tekstistringid. Ei saa kasutada avaldisi veerupäistega. Näiteks avaldis DeptSalesFYSummary [[2014]: [2012]] ei tööta.

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.

  • Tabeldusklahv (Tab)

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

Siit leiate loendi erimärgid, mida tuleb paomärki (') valemi:

  • 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 o n 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 leida, et funktsiooni valemi Automaattekst kasutamisest on palju kasu Liigendatud viidete sisestamisel ja õige süntaksi kasutamise tagamisel. Lisateavet leiate teemast Valemi automaatteksti funktsiooni kasutamine.

  • Tabelite jaoks liigendatud viidete võimalik loomine poolvalikus    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, mis asub dialoogiboksi Exceli suvandid kategooria Valemid jaotises 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.

  • Teisenda vahemikuks tabeliks ja tabeli teisendamine vahemikuks    Tabeli teisendamine andmevahemikuks kõik lahtriviited vahetada oma võrdväärse A1 laadi absoluutviited. Kui vahemiku teisendamine tabeliks Excel ei muuda automaatselt kõik selle vahemiku lahtriviidete nende võrdväärse liigendatud viited.

  • Veerupäiste väljalülitamine    Tabeli veerupäiste väljalülitamine (tühjendage menüü Kujundus jaotises Tabelilaadi suvandid märkeruut Päiserida) ei mõjuta neid päiseid kasutavaid liigendatud viiteid. Seega saate neid valemites edasi kasutada. Liigendatud viited, mis viitavad otseselt tabelipäistele (nt= DeptSales [[#Headers], [vahendustasu %]]) annavad tulemuseks #REF.

  • Veergude ja ridade tabelisse lisamine ja sealt kustutamine    Tabeli andmevahemike sagedasel muutumisel 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.

  • Teisaldamine, kopeerimine, ja sisestage struktureeritud viited    Kõik liigendatud viited samaks liigendatud viidet kasutava valemi teisaldamine või kopeerimine.

    Märkus. : Liigendatud viite kopeerimise ja tehes täite liigendatud viite pole sama. Kui kopeerite, kõik liigendatud viited samaks, samal ajal, kui sisestate valemi, nõuetele täielikult vastavad liigendatud viited reguleerida veerutunnuseid sarjana, nagu on kokku võetud järgmises tabelis.

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.

Märkus. : Masintõlke lahtiütlus. Selle artikli tõlkis arvutisüsteem ilma inimese sekkumiseta. Microsoft pakub selliseid masintõlkeid, et inglise keelt mittekõnelevad kasutajad saaksid vaadata sisu Microsofti toodete, teenuste ja tehnoloogiate kohta. Kuna artikkel on masintõlgitud, võib see sisaldada sõnavara-, süntaksi- või grammatikavigu.

Vt ka

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.

×