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 valige tabelilahtrid, millele soovite viidata, selle asemel, et tippida lahtriviide valemisse. Järgmiste näidisandmete abil sisestame valemi, mis kasutab müügi vahendustasu summa arvutamiseks automaatselt liigendatud viiteid.
Müügiesindaja |
Regioon |
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% |
-
Kopeerige ülaltoodud tabelis asuvad näidisandmed (sh veerupäised) ja kleepige need uue Exceli töölehe lahtrisse A1.
-
Valige tabeli loomiseks andmevahemikus suvaline lahter ja vajutage klahvikombinatsiooni CTRL + T.
-
Veenduge, et ruut Minu tabelil on päised oleks märgitud, ja valige OK.
-
Tippige lahtrisse E2 võrdusmärk (=) ja valige lahter C2.
Valemiribal kuvatakse pärast võrdusmärki liigendatud viide [@[Müügisumma]].
-
Tippige kohe lõpusulu järele tärn (*) ja valige lahter D2.
Valemiribal kuvatakse pärast tärni liigendatud viide [@[Vahendustasu %]].
-
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.
-
Valige näidistöölehte lahter E2
-
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.
-
Lindil menüü Tabeli kujundus kuvamiseks valige tabelis suvaline lahter.
-
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.
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 valemi, valib tabelist lahtrivahemiku valimine vaikimisi poolkäsud ja sisestab valemis 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üü Tabeli kujundus > Päiserida kaudu. 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.
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?
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