Preskoči na glavno vsebino

Excel absolutna referenca (kako narediti in uporabljati)

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.

Brezplačno prenesite vzorčno datoteko doc vzorec


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
doc absolutna referenca 3 1   doc absolutna referenca 4 1

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

absolutna referenca f4 preklop 1

Č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

absolutna referenca f4 preklop 2


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.

Opombe:

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.

doc absolutna referenca 15 1

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.

doc absolutna referenca 16 1

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.

doc absolutna referenca 17 1

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.

doc absolutna referenca 18 1

Č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

doc absolutna referenca 19 1

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

doc absolutna referenca 20 1


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.

Najboljša pisarniška orodja za produktivnost

Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike   |  Izbriši prazne vrstice   |  Združite stolpce ali celice brez izgube podatkov   |   Krog brez formule ...
Super iskanje: Več kriterijev VLookup    Multiple Value VLookup  |   VLookup na več listih   |   Nejasno iskanje ....
Napredni spustni seznam: Hitro ustvarite spustni seznam   |  Odvisni spustni seznam   |  Večkrat izberite spustni seznam ....
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  |  Primerjaj obsege in stolpce ...
Predstavljene funkcije: Mrežni fokus   |  Pogled oblikovanja   |   Velika vrstica formule    Upravitelj delovnih zvezkov in listov   |  Knjižnica virov (Samodejno besedilo)   |  Izbirnik datuma   |  Združite delovne liste   |  Šifriranje/dešifriranje celic    Pošljite e-pošto po seznamu   |  Super filter   |   Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij12 Besedilo Orodja (dodajanje besedila, Odstrani znake,...)   |   50 + Graf Vrste (Gantt Chart,...)   |   40+ Praktično Formule (Izračunajte starost glede na rojstni dan,...)   |   19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti,...)   |   12 Pretvorba Orodja (Številke v besede, Pretvorba valut,...)   |   7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeljene celice,...)   |   ... in več

Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa.  Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...

zavihek kte 201905


Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations