Exceli tööleht koosneb lahtritest. Nendele lahtritele saab viidata, määrates rea ja veeru väärtuse.
Näiteks viitaks A1 esimesele reale (määratud kui 1) ja esimesele veerule (määratud kui A). Samamoodi oleks B3 kolmas rida ja teine veerg.
Exceli jõud peitub selles, et saate neid lahtriviiteid kasutada ka teistes lahtrites valemite loomisel.
Nüüd saate Excelis kasutada kolme tüüpi lahtriviiteid:
- Suhtelised lahtrite viited
- Absoluutsed rakuviited
- Segarakkude viited
Nende erinevat tüüpi lahtriviidete mõistmine aitab teil valemitega töötada ja säästa aega (eriti valemite kopeerimisel ja kleepimisel).
Mis on suhtelised lahtriviited Excelis?
Toon lihtsa näite, et selgitada suhteliste lahtriviidete mõistet Excelis.
Oletame, et mul on allpool näidatud andmekogum:
Iga üksuse kogusumma arvutamiseks peame iga üksuse hinna korrutama selle kauba kogusega.
Esimese üksuse valem lahtris D2 oleks B2* C2 (nagu allpool näidatud):
Nüüd, selle asemel, et sisestada kõigi lahtrite valem ükshaaval, saate lihtsalt kopeerida lahtri D2 ja kleepida selle kõikidesse teistesse lahtritesse (D3: D8). Seda tehes märkate, et lahtri viide kohandub automaatselt vastavale reale viitamiseks. Näiteks lahtris D3 olev valem muutub B3*C3 ja valem lahtris D4 B4*C4.
Neid lahtriviiteid, mis kohanduvad lahtri kopeerimisel, nimetatakse suhtelised lahtriviited Excelis.
Millal kasutada Excelis suhtelisi lahtriviiteid?
Suhtelised lahtriviited on kasulikud, kui peate looma valemi lahtrite vahemikule ja valem peab viitama suhtelisele lahtri viitele.
Sellistel juhtudel saate luua ühe lahtri valemi ja kopeerida-kleepida selle kõikidesse lahtritesse.
Mis on Exceli absoluutsed lahtriviited?
Erinevalt suhtelistest lahtriviidetest ei muutu absoluutsed lahtriviited, kui kopeerite valemi teistesse lahtritesse.
Oletame näiteks, et teil on allpool näidatud andmekogum, kus peate arvutama komisjonitasu iga kauba kogumüügi eest.
Komisjonitasu on 20% ja see on loetletud lahtris G1.
Iga üksuse müügi vahendustasu saamiseks kasutage lahtris E2 järgmist valemit ja kopeerige kõik lahtrid:
= D2*$ G $ 1
Pange tähele, et vahendustasus on kaks dollarimärki ($) - $G$2.
Mida teeb dollar ($) märk?
Dollarisümbol, kui see lisatakse rea ja veeru numbri ette, muudab selle absoluutseks (st peatab rea ja veeru numbri muutumise, kui need kopeeritakse teistesse lahtritesse).
Näiteks ülaltoodud juhul, kui kopeerin valemi lahtrist E2 lahtrisse E3, muutub see väärtusest = D2*$ G $ 1 väärtuseks = D3*$ G $ 1.
Pange tähele, et kuigi D2 muutub D3 -ks, ei muutu $ G $ 1.
Kuna oleme G1 -s lisanud dollari sümboli „G” ja „1” ette, ei lase see lahtri viitel kopeerimisel muutuda.
Seega muudab see lahtri viite absoluutseks.
Millal kasutada Excelis absoluutsete lahtrite viiteid?
Absoluutsed lahtriviited on kasulikud, kui te ei soovi, et lahtrite viide muutuks valemite kopeerimisel. See võib juhtuda juhul, kui teil on kindel väärtus, mida peate valemis kasutama (nt maksumäär, vahendustasu, kuude arv jne).
Kuigi saate selle väärtuse ka kõvakoodiga kodeerida (st kasutada 20% asemel $ G $ 2), võimaldab selle lahtrisse paigutamine ja seejärel lahtri viite kasutamine seda tulevikus muuta.
Näiteks kui teie komisjonitasu struktuur muutub ja maksate nüüd 20% asemel välja 25%, saate lahtris G2 lihtsalt väärtust muuta ja kõik valemid värskendatakse automaatselt.
Mis on Exceli segarakulised viited?
Segalahtrite viited on natuke keerulisemad kui absoluutsed ja suhtelised lahtriviited.
Segarakulisi viiteid võib olla kahte tüüpi:
- Rida lukustatakse veeru muutmisel valemi kopeerimisel.
- Veerg on valemi kopeerimisel rea muutumisel lukustatud.
Vaatame näite abil, kuidas see toimib.
Allpool on andmekogum, kus peate arvutama kolm vahendustasu, lähtudes lahtri E2, F2 ja G2 protsentuaalsest väärtusest.
Nüüd saate segatud viite abil arvutada kõik need komisjonitasud vaid ühe valemiga.
Sisestage lahtrisse E4 allolev valem ja kopeerige kõik lahtrid.
= $ B4*$ C4*E $ 2
Ülaltoodud valem kasutab mõlemat tüüpi segarakulisi viiteid (üks, kus rida on lukustatud ja teine, kus veerg on lukustatud).
Analüüsime iga lahtri viidet ja mõistame, kuidas see toimib:
- B4 dollarit (ja C4 dollarit) - Selles viites on dollarimärk otse veerumärke ees, kuid mitte rea rea ees. See tähendab, et kui kopeerite valemi parempoolsetesse lahtritesse, jääb viide samaks kui veerg on fikseeritud. Näiteks kui kopeerite valemi E4 -st F4 -le, siis see viide ei muutu. Selle kopeerimisel aga muutub rea number, kuna see pole lukus.
- 2 dollarit - Selles viites on dollarimärk otse rea numbri ees ja veerumärgisel pole dollarimärki. See tähendab, et kui kopeerite valemi lahtritesse, ei muutu viide, kuna rea number on lukustatud. Kui aga kopeerite valemi paremale, muutub veeru tähestik, kuna see pole lukus.
Kuidas muuta võrdlusväärtust suhtelisest absoluutseks (või segatud)?
Viite muutmiseks suhtelisest absoluutseks peate veeru märkimise ja rea numbri ette lisama dollarimärgi.
Näiteks A1 on lahtri suhteline viide ja see muutub absoluutseks, kui teete selle $ 1 $.
Kui teil on muuta ainult paar viidet, võib teil olla lihtne neid viiteid käsitsi muuta. Nii saate minna valemiribale ja seda valemit muuta (või valige lahter, vajutage klahvi F2 ja muutke seda).
Kiirem viis seda teha on aga kasutada kiirklahvi - F4.
Kui valite lahtriviite (valemiribal või lahtris redigeerimisrežiimis) ja vajutate klahvi F4, muudab see viidet.
Oletame, et lahtris on viide = A1.
Siin on see, mis juhtub, kui valite viite ja vajutate klahvi F4.
- Vajutage üks kord klahvi F4: Lahtri viide muutub A1 -lt $ A $ 1 -le (muutub "absoluutseks" väärtusest "suhteline").
- Vajutage kaks korda klahvi F4: Lahtri viide muutub A1 -st A $ 1 -ks (muutub segatud viide, kus rida on lukustatud).
- Vajutage kolm korda klahvi F4: Lahtri viide muutub A1 -lt $ A1 -le (muutub segatud viiteks, kus veerg on lukustatud).
- Vajutage klahvi F4 neli korda: Lahtri viide muutub uuesti A1 -ks.
