Pri sklicevanju na celico v formuli v Excelu je privzeti tip sklica relativni sklic. Ti sklici se bodo spremenili, ko bo formula kopirana v druge celice glede na njihov relativni stolpec in vrstico. Če želite ohraniti referenco konstantno, ne glede na to, kje je formula kopirana, morate uporabiti absolutno referenco.

• Kaj je absolutna referenca
• Kako narediti absolutne reference
• Uporabite absolutno referenco s primeri
• Izračunajte odstotek skupnega
• Poiščite vrednost in se vrnite na ustrezno vrednost ujemanja
• 2 klika za skupinsko absolutno absolutno referenco celic s programom Kutools
• Relativna in mešana referenca
• Stvari, ki jih je treba spomniti

Brezplačno prenesite vzorčno datoteko

Video: Absolutna referenca

Kaj je absolutna referenca

Absolutna referenca je vrsta sklica na celico v Excelu.

V primerjavi z relativnim sklicem, ki se spremeni glede na svoj relativni položaj, ko je formula kopirana v druge celice, bo absolutni sklic ostal nespremenjen ne glede na to, kam je formula kopirana ali premaknjena.

Absolutni sklic se ustvari z dodajanjem znaka za dolar ($) pred sklici stolpcev in vrstic v formuli. Na primer, če želite ustvariti absolutno referenco za celico A1, jo morate predstaviti kot $A$1.

Absolutni sklici so uporabni, ko se želite sklicevati na fiksno celico ali obseg v formuli, ki bo kopirana v več celic, vendar ne želite, da se sklic spremeni.

Na primer, obseg A4:C7 vsebuje cene izdelkov in želite pridobiti davek, ki ga je treba plačati za vsak izdelek na podlagi davčne stopnje v celici B2.

Če v formuli uporabite relativno sklicevanje, kot je »=B5*B2«, se vrne nekaj napačnih rezultatov, ko povlečete ročico za samodejno izpolnjevanje navzdol, da uporabite to formulo. Ker se bo sklic na celico B2 spremenil glede na celice v formuli. Zdaj je formula v celici C6 "=B6*B3", formula v celici C7 pa je "=B7*B4"

Toda če uporabite absolutno sklicevanje na celico B2 s formulo »=B5*$B$2«, boste zagotovili, da bo davčna stopnja ostala enaka za vse celice, ko formulo povlečete navzdol z ročko za samodejno izpolnjevanje, so rezultati pravilni.

Uporaba relativne reference		Uporaba absolutne reference

Kako narediti absolutne reference

Če želite narediti absolutno referenco v Excelu, morate pred sklici stolpcev in vrstic v formuli dodati znake za dolar ($). Obstajata dva načina za ustvarjanje absolutne reference:

Sklicu na celico ročno dodajte znake za dolar

Med vnašanjem formule v celico lahko ročno dodate znake za dolar ($) pred sklice na stolpce in vrstice, ki jih želite narediti absolutne.

Na primer, če želite sešteti številki v celici A1 in B1 in obe narediti absolutni, preprosto vnesite formulo kot "=$A$1+$B$1". To bo zagotovilo, da bodo sklice na celice ostale nespremenjene, ko se formula kopira ali premakne v druge celice.

Če pa želite sklice v obstoječi formuli v celici spremeniti v absolutne, lahko izberete celico in nato pojdite v vrstico s formulami, da dodate znake za dolar ($).

Uporaba bližnjice F4 za pretvorbo relativne reference v absolutno

1. Dvokliknite celico s formulo, da vstopite v način urejanja;
2. Kazalec postavite na referenco celice, ki jo želite narediti absolutno;
3. Pritisnite F4 tipko na tipkovnici, da preklapljate med tipi sklicevanja, dokler se znaki za dolar ne dodajo pred sklice na stolpce in vrstice;
4. Pritisnite Vnesite tipko za izhod iz načina urejanja in uporabo sprememb.

Tipka F4 lahko preklaplja med relativno referenco, absolutno referenco in mešano referenco.

A1 → $A$1 → A$1 → $A1 → A1

Če želite vse sklice v formuli narediti absolutne, izberite celotno formulo v vrstici s formulami in pritisnite F4 tipka za preklapljanje med vrstami sklicev, dokler znaki za dolar niso dodani pred sklici stolpcev in vrstic.

A1+B1 → $A$1+$B$1 → A$1+B$1 → $A1+$B1 → A1+B1

Uporabite absolutno referenco s primeri

Ta del ponuja 2 primera, ki prikazujeta, kdaj in kako uporabiti absolutne reference v Excelovi formuli.

Primer 1 Izračunajte odstotek skupne vrednosti

Recimo, da imate obseg podatkov (A3:B7), ki vsebuje prodajo vsakega sadja, celica B8 pa vsebuje skupno količino prodaje tega sadja, zdaj pa želite izračunati odstotek prodaje vsakega sadja od celotne prodaje.

Splošna formula za izračun odstotka skupne vrednosti:

Percentage = Sale/Amount

Uporabite relativno referenco v formuli, da dobite odstotek prvega sadja, kot je ta:

=B4/B8

Ko ročico za samodejno polnjenje povlečete navzdol, da izračunate odstotek drugega sadja, #DIV/0! napake bodo vrnjene.

Ker ko povlečete ročico za samodejno izpolnjevanje, da kopirate formulo v spodnje celice, se relativna referenca B8 samodejno prilagodi drugim referencam celic (B9, B10, B11) glede na njihove relativne položaje. In celice B9, B10 in B11 so prazne (ničle), ko je delitelj nič, se formula vrne na napako.

Če želite odpraviti napake, morate v tem primeru referenco celice B8 v formuli narediti absolutno ($B$8), da se ne spremeni, ko premaknete ali kopirate formulo kamor koli. Zdaj je formula posodobljena na:

=B4/$B$8

Nato povlecite ročico za samodejno polnjenje navzdol, da izračunate odstotek drugega sadja.

Primer 2 Poiščite vrednost in se vrnite na ustrezno vrednost ujemanja

Ob predpostavki, da želite poiskati seznam imen v D4:D5 in vrniti njihove ustrezne plače na podlagi imen osebja in ustrezne letne plače, navedene v obsegu (A4:B8).

Splošna formula za iskanje je:

=VLOOKUP(lookup_value, table_range, column_index, logical)

Če uporabite relativni sklic v formuli za iskanje vrednosti in vrnete ustrezno vrednost ujemanja, kot je ta:

=VLOOKUP(D4,A4:B8,2,FALSE)

Nato povlecite ročico za samodejno izpolnjevanje navzdol, da poiščete spodnjo vrednost, vrnjena bo napaka.

Ko ročico za polnjenje povlečete navzdol, da kopirate formulo v spodnjo celico, se sklicevanja v formuli samodejno prilagodijo za eno vrstico navzdol. Posledično sklic na obseg tabele, A4:B8, postane A5:B9. Ker »Lisa: ni mogoče najti v obsegu A5:B9, formula vrne napako.

Da se izognete napakam, uporabite absolutni sklic $A$4:$B$8 namesto relativnega sklica A4:B8 v formuli:

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

Nato povlecite ročico za samodejno izpolnjevanje navzdol, da dobite plačo Lise.

2 klika za skupinsko absolutno absolutno referenco celic s programom Kutools

Ne glede na to, ali se odločite za ročno tipkanje ali uporabo bližnjice F4, lahko v Excelu spremenite samo eno formulo hkrati. Če želite sklice na celice v stotinah formul v Excelu narediti absolutne, Pretvori sklice orodje za Kutools za Excel vam lahko pomaga opraviti delo z 2 kliki.

Izberite celice s formulami, za katere želite, da so sklice na celice absolutne, kliknite Kutools > Več (fx) > Pretvori sklice. Nato izberite Na absolutno Možnost in kliknite Ok or Uporabi. Zdaj so bili vsi sklice na celice izbranih formul pretvorjeni v absolutne.

Relativna in mešana referenca

Poleg absolutne reference obstajata še dve vrsti referenc: relativna referenca in mešana referenca.

Relativna referenca je privzeti tip sklica v Excelu, ki je brez znakov za dolar ($) pred sklici vrstic in stolpcev. Ko se formula z relativnimi sklici kopira ali premakne v druge celice, se sklici samodejno spremenijo glede na njihov relativni položaj.

Na primer, ko vnesete formulo v celico, kot je "=A1+1", nato povlecite ročico za samodejno izpolnjevanje navzdol, da zapolnite to formulo v naslednjo celico, se bo formula samodejno spremenila v "=A2+1".

Mešana referenca je sestavljen iz absolutne in relativne reference. Z drugimi besedami, mešani sklic uporablja znak za dolar ($), da popravi vrstico ali stolpec, ko je formula kopirana ali izpolnjena.

Za primer vzemite tabelo množenja, v vrsticah in stolpcih so navedena števila od 1 do 9, ki jih boste med seboj pomnožili.

Za začetek lahko uporabite formulo "=B3*C2" v celici C3, da pomnožite 1 v celici B3 s številko (1) v prvem stolpcu. Ko pa ročico za samodejno izpolnjevanje povlečete v desno, da zapolnite druge celice, boste opazili, da so vsi rezultati nepravilni, razen prvega.

To je zato, ker ko kopirate formulo na desno, se položaj vrstice ne spremeni, položaj stolpca pa se spremeni iz B3 v C3, D3 itd. Posledično se formule v desnih celicah (D3, E3, itd.) spremenite v "=C3*D2", "=D3*E2" in tako naprej, ko dejansko želite, da so "=B3*D2", "=B3*E2" itd.

V tem primeru morate dodati znak za dolar ($), da zaklenete sklic stolpca »B3«. Uporabite spodnjo formulo:

=$B3*C2

Zdaj, ko povlečete formulo v desno, so rezultati pravilni.

Nato morate številko 1 v celici C2 pomnožiti s številkami v spodnjih vrsticah.

Ko kopirate formulo navzdol, se položaj stolpca celice C2 ne spremeni, vendar se položaj vrstice spremeni iz C2 v C3, C4 itd. Posledično se formule v spodnjih celicah spremenijo v "=$B4C3", "=$B5C4" itd., kar bo povzročilo napačne rezultate.

Če želite rešiti to težavo, spremenite »C2« v »C$2«, da preprečite spreminjanje sklicevanja na vrstico, ko povlečete ročico za samodejno polnjenje navzdol, da zapolnite formule.

=$B3*C$2

Zdaj lahko ročico za samodejno izpolnjevanje povlečete desno ali navzdol, da dobite vse rezultate.

Stvari, ki jih je treba spomniti

• Povzetek referenc celic

  tip	Primer	Povzetek
  Absolutna referenca	$ A $ 1	Nikoli ne spreminjajte, ko je formula kopirana v druge celice
  Relativna referenca	A1	Tako sklic vrstice kot stolpca se spremeni glede na relativni položaj, ko se formula kopira v druge celice
  Mešana referenca	$A1/A$1	Sklic na vrstico se spremeni, ko je formula kopirana v druge celice, vendar je sklic na stolpec fiksen/Sklic na stolpec se spremeni, ko je formula kopirana v druge celice, vendar je sklic na vrstico fiksen;

• Na splošno se absolutne reference nikoli ne spremenijo, ko se formula premakne. Vendar se bodo absolutne reference samodejno prilagodile, ko dodate ali odstranite vrstico ali stolpec z vrha ali leve strani delovnega lista. Na primer, v formuli »=$A$1+1«, ko vstavite vrstico na vrh lista, se bo formula samodejno spremenila v »=$A$2+1«.

  

• O F4 tipka lahko preklaplja med relativno referenco, absolutno referenco in mešano referenco.