Radni list u Excelu sastoji se od ćelija. Ove ćelije se mogu referencirati navođenjem vrijednosti retka i vrijednosti stupca.
Na primjer, A1 će se odnositi na prvi redak (naveden kao 1) i prvi stupac (naveden kao A). Slično, B3 bi bio treći redak i drugi stupac.
Snaga Excela leži u činjenici da ove reference ćelija možete koristiti u drugim ćelijama pri stvaranju formula.
Sada postoje tri vrste referenci ćelija koje možete koristiti u Excelu:
- Relativne reference ćelija
- Apsolutne ćelijske reference
- Reference na mješovite ćelije
Razumijevanje ovih različitih vrsta referenci ćelija pomoći će vam u radu s formulama i uštedjeti vrijeme (osobito pri kopiranju formula).
Što su relativne reference ćelija u Excelu?
Dopustite mi da uzmem jednostavan primjer za objašnjenje koncepta relativnih referenci ćelija u Excelu.
Pretpostavimo da imam niz podataka prikazan u nastavku:
Da bismo izračunali ukupni iznos za svaku stavku, moramo pomnožiti cijenu svake stavke s količinom te stavke.
Za prvu stavku formula u ćeliji D2 bila bi B2* C2 (kao što je prikazano u nastavku):
Sada, umjesto da formulu za sve ćelije unosite jednu po jednu, jednostavno možete kopirati ćeliju D2 i zalijepiti je u sve ostale ćelije (D3: D8). Kad to učinite, primijetit ćete da se referenca ćelije automatski prilagođava prema odgovarajućem retku. Na primjer, formula u ćeliji D3 postaje B3*C3, a formula u D4 postaje B4*C4.
Pozivaju se te reference ćelija koje se same prilagođavaju pri kopiranju ćelije relativne reference ćelija u Excelu.
Kada koristiti relativne reference ćelija u Excelu?
Relativne reference ćelija korisne su kada morate stvoriti formulu za raspon ćelija, a formula se mora odnositi na relativnu referencu ćelije.
U takvim slučajevima možete stvoriti formulu za jednu ćeliju i kopirati-zalijepiti je u sve ćelije.
Što su apsolutne reference ćelija u Excelu?
Za razliku od relativnih referenci ćelija, apsolutne reference ćelija ne mijenjaju se kada kopirate formulu u druge ćelije.
Na primjer, pretpostavimo da imate skup podataka kako je prikazano u nastavku gdje morate izračunati proviziju za ukupnu prodaju svake stavke.
Provizija je 20% i navedena je u ćeliji G1.
Da biste dobili iznos provizije za svaku prodaju artikla, upotrijebite sljedeću formulu u ćeliji E2 i kopirajte za sve ćelije:
= D2*$ G $ 1
Imajte na umu da u referenci ćelije postoje dva znaka dolara ($) koji imaju proviziju - $G$2.
Čemu služi znak dolara ($)?
Simbol dolara, kada se doda ispred broja retka i stupca, čini ga apsolutnim (tj. Zaustavlja promjenu broja retka i stupca pri kopiranju u druge ćelije).
Na primjer, u gornjem slučaju, kada kopiram formulu iz ćelije E2 u E3, ona se mijenja iz = D2*$ G $ 1 u = D3*$ G $ 1.
Imajte na umu da, iako se D2 mijenja u D3, $ G $ 1 se ne mijenja.
Budući da smo dodali simbol dolara ispred 'G' i '1' u G1, to ne bi dopustilo da se referenca ćelije promijeni pri kopiranju.
Stoga referenca stanice postaje apsolutna.
Kada koristiti apsolutne ćelijske reference u Excelu?
Apsolutne reference ćelija korisne su ako ne želite da se referenca ćelije mijenja dok kopirate formule. To bi mogao biti slučaj kada imate fiksnu vrijednost koju trebate koristiti u formuli (kao što su porezna stopa, stopa provizije, broj mjeseci itd.)
Iako možete i teško kodirati ovu vrijednost u formuli (tj. Upotrijebiti 20% umjesto $ G $ 2), njeno držanje u ćeliji, a zatim upotreba reference ćelije omogućuje vam da je promijenite na neki budući datum.
Na primjer, ako se promijeni struktura vaše provizije i sada umjesto 20% plaćate 25%, možete jednostavno promijeniti vrijednost u ćeliji G2 i sve će se formule automatski ažurirati.
Što su reference mješovitih ćelija u Excelu?
Referencije mješovitih ćelija malo su zeznutije od apsolutnih i relativnih referenci ćelija.
Mogu postojati dvije vrste mješovitih referenci ćelija:
- Redak je zaključan dok se stupac mijenja pri kopiranju formule.
- Stupac je zaključan dok se redak mijenja pri kopiranju formule.
Pogledajmo kako to funkcionira na primjeru.
Dolje je skup podataka u kojem morate izračunati tri razine provizije na temelju postotne vrijednosti u ćeliji E2, F2 i G2.
Sada možete koristiti moć mješovite reference za izračun svih ovih provizija sa samo jednom formulom.
Unesite donju formulu u ćeliju E4 i kopirajte za sve ćelije.
= $ B4*$ C4*E $ 2
Gornja formula koristi obje vrste mješovitih referenci ćelija (jedna u kojoj je zaključan red i druga u kojoj je stupac zaključan).
Analizirajmo svaku referencu ćelije i shvatimo kako ona radi:
- B4 USD (i C4 USD) - U ovoj referenci znak dolara nalazi se točno ispred oznake stupca, ali ne i ispred broja retka. To znači da kada kopirate formulu u ćelije s desne strane, referenca će ostati ista kao što je stupac fiksiran. Na primjer, ako kopirate formulu iz E4 u F4, ova se referenca neće promijeniti. Međutim, kada ga kopirate, broj retka će se promijeniti jer nije zaključan.
- 2 USD - U ovoj referenci znak dolara nalazi se točno ispred broja retka, a oznaka stupca nema znak dolara. To znači da kada kopirate formulu u ćelije, referenca se neće promijeniti jer je broj retka zaključan. Međutim, ako kopirate formulu udesno, abeceda stupca promijenila bi se jer nije zaključana.
Kako promijeniti referencu iz relativne u apsolutnu (ili mješovitu)?
Da biste referencu promijenili iz relativne u apsolutnu, morate dodati znak dolara ispred zapisa stupca i broja retka.
Na primjer, A1 je relativna referenca ćelije i postat će apsolutna kada to učinite $ A $ 1.
Ako imate samo nekoliko referenci za promjenu, možda ćete ih lako promijeniti ručno. Dakle, možete otići na traku s formulama i urediti formulu (ili odabrati ćeliju, pritisnuti F2, a zatim je promijeniti).
Međutim, brži način za to je korištenje prečaca na tipkovnici - F4.
Kad odaberete referencu ćelije (na traci s formulama ili u ćeliji u načinu uređivanja) i pritisnete F4, ona mijenja referencu.
Pretpostavimo da imate referencu = A1 u ćeliji.
Evo što se događa kada odaberete referencu i pritisnete tipku F4.
- Pritisnite tipku F4 jednom: Referenca ćelije mijenja se iz A1 u $ A $ 1 (postaje "apsolutna" iz "relativna").
- Pritisnite tipku F4 dva puta: Referenca ćelije mijenja se iz A1 u A $ 1 (mijenja se u mješovitu referencu gdje je redak zaključan).
- Pritisnite tipku F4 tri puta: Referenca ćelije mijenja se iz A1 u $ A1 (mijenja se u mješovitu referencu gdje je stupac zaključan).
- Pritisnite tipku F4 četiri puta: Referenca ćelije ponovno postaje A1.
