Delovni list v Excelu je sestavljen iz celic. Na te celice se lahko sklicujete tako, da podate vrednost vrstice in vrednosti stolpca.
A1 bi se na primer skliceval na prvo vrstico (določeno kot 1) in prvi stolpec (podano kot A). Podobno bi bil B3 tretja vrstica in drugi stolpec.
Moč Excela je v tem, da lahko te sklice na celice uporabite v drugih celicah pri ustvarjanju formul.
Zdaj lahko v Excelu uporabite tri vrste sklicev na celice:
- Relativne reference celic
- Absolutne reference celic
- Reference mešanih celic
Razumevanje teh različnih vrst sklicev na celice vam bo pomagalo pri delu s formulami in prihranilo čas (zlasti pri formulah za lepljenje kopij).
Kaj so relativne reference celic v Excelu?
Dovolite mi preprost primer za razlago koncepta relativnih referenc celic v Excelu.
Recimo, da imam spodaj prikazan niz podatkov:
Za izračun skupne vrednosti za vsako postavko moramo ceno vsake postavke pomnožiti s količino te postavke.
Za prvo postavko bi bila formula v celici D2 B2* C2 (kot je prikazano spodaj):
Namesto da formulo za vse celice vnesete eno za drugo, lahko preprosto kopirate celico D2 in jo prilepite v vse druge celice (D3: D8). Ko to storite, boste opazili, da se sklic na celico samodejno prilagodi glede na ustrezno vrstico. Na primer, formula v celici D3 postane B3*C3 in formula v D4 postane B4*C4.
Pokličejo se te sklice na celice, ki se prilagodijo, ko se celica kopira relativne reference celic v Excelu.
Kdaj uporabiti relativne reference celic v Excelu?
Relativne sklice na celice so uporabne, ko morate ustvariti formulo za obseg celic in se mora formula sklicevati na relativno referenco celice.
V takih primerih lahko ustvarite formulo za eno celico in jo kopirate in prilepite v vse celice.
Kaj so absolutne reference celic v Excelu?
Za razliko od relativnih sklicev na celice se absolutne reference celic ne spremenijo, ko formulo kopirate v druge celice.
Recimo, da imate nabor podatkov, kot je prikazano spodaj, kjer morate izračunati provizijo za skupno prodajo vsakega artikla.
Provizija znaša 20% in je navedena v celici G1.
Če želite dobiti znesek provizije za vsako prodajo artiklov, uporabite naslednjo formulo v celici E2 in kopirajte za vse celice:
= D2*$ G $ 1
Upoštevajte, da sta v sklicu na celico dva znaka za dolar ($), ki ima provizijo - $G$2.
Kaj počne znak Dollar ($)?
Simbol dolarja, ki je dodan pred številko vrstice in stolpca, ga naredi absolutnega (tj. Ustavi spreminjanje številke vrstice in stolpca pri kopiranju v druge celice).
Na primer, v zgornjem primeru, ko kopiram formulo iz celice E2 v E3, se spremeni iz = D2*$ G $ 1 v = D3*$ G $ 1.
Upoštevajte, da medtem ko se D2 spremeni v D3, se $ G $ 1 ne spremeni.
Ker smo pred "G" in "1" v G1 dodali simbol dolarja, se kopija celice ne dovoli spremeniti.
Zato je referenca celice absolutna.
Kdaj uporabiti absolutne sklice na celice v Excelu?
Absolutne sklice na celice so uporabne, če ne želite, da se sklic na celico spreminja med kopiranjem formul. To se lahko zgodi, če imate fiksno vrednost, ki jo morate uporabiti v formuli (na primer davčno stopnjo, stopnjo provizije, število mesecev itd.)
Čeprav lahko to vrednost tudi težko formulirate v formuli (tj. Uporabite 20% namesto $ G $ 2), jo lahko v celici in nato z uporabo sklica na celico spremenite na datum v prihodnosti.
Če se na primer spremeni struktura vaše provizije in zdaj izplačujete 25% namesto 20%, lahko preprosto spremenite vrednost v celici G2 in vse formule se bodo samodejno posodobile.
Kaj so reference mešanih celic v Excelu?
Sklicevanje na mešane celice je nekoliko bolj zapleteno kot absolutne in relativne reference celic.
Obstajata dve vrsti mešanih referenc celic:
- Vrstica je zaklenjena, medtem ko se stolpec spremeni, ko se kopira formula.
- Stolpec je zaklenjen, vrstica pa se pri kopiranju formule spremeni.
Poglejmo, kako to deluje na primeru.
Spodaj je niz podatkov, kjer morate izračunati tri stopnje provizije na podlagi odstotne vrednosti v celici E2, F2 in G2.
Zdaj lahko uporabite moč mešanih referenc za izračun vseh teh provizij s samo eno formulo.
Spodnjo formulo vnesite v celico E4 in kopirajte za vse celice.
= $ B4*$ C4*E $ 2
Zgornja formula uporablja obe vrsti mešanih sklicev na celice (eno, kjer je vrstica zaklenjena, in drugo, kjer je stolpec zaklenjen).
Analizirajmo vsako referenco celic in razumejmo, kako deluje:
- B4 USD (in C4 USD) - V tem sklicu je znak dolarja tik pred zapisom stolpca, ne pa pred številko vrstice. To pomeni, da ko kopirate formulo v celice na desni, bo sklic ostal isti, kot je stolpec fiksiran. Če na primer kopirate formulo iz E4 v F4, se ta sklic ne bo spremenil. Ko pa jo kopirate, se številka vrstice spremeni, ker ni zaklenjena.
- 2 USD - V tem sklicu je znak dolarja tik pred številko vrstice, zapis v stolpcu pa nima znaka dolarja. To pomeni, da se pri kopiranju formule po celicah sklic ne bo spremenil, ker je številka vrstice zaklenjena. Če pa kopirate formulo na desno, se abeceda stolpca spremeni, ker ni zaklenjena.
Kako spremeniti referenco iz relativne v absolutno (ali mešano)?
Če želite sklic spremeniti iz relativnega v absolutnega, morate pred zapisom stolpca in številko vrstice dodati znak dolarja.
Na primer, A1 je relativna referenca celice in bi postala absolutna, ko bi to pomenilo $ A $ 1.
Če imate le nekaj referenc za spremembo, jih boste morda preprosto ročno spremenili. Tako lahko odprete vrstico s formulami in uredite formulo (ali izberite celico, pritisnite F2 in jo nato spremenite).
Vendar je to hitrejši način z bližnjico na tipkovnici - F4.
Ko izberete sklic na celico (v vrstici s formulo ali v celici v načinu urejanja) in pritisnete F4, se sklic spremeni.
Recimo, da imate referenco = A1 v celici.
Evo, kaj se zgodi, ko izberete referenco in pritisnete tipko F4.
- Enkrat pritisnite tipko F4: Referenca celice se spremeni iz A1 v $ A $ 1 (postane "absolutna" iz "relativna").
- Dvakrat pritisnite tipko F4: Sklic celice se spremeni iz A1 v A $ 1 (spremeni se v mešani sklic, kjer je vrstica zaklenjena).
- Trikrat pritisnite tipko F4: Referenca celice se spremeni iz A1 v $ A1 (spremeni se v mešano referenco, kjer je stolpec zaklenjen).
- Štirikrat pritisnite tipko F4: Referenca celice spet postane A1.
