Salt la conținutul principal

Referință absolută Excel (cum se face și cum se utilizează)

Când se face referire la o celulă într-o formulă în Excel, tipul de referință implicit este o referință relativă. Aceste referințe se vor schimba atunci când formula este copiată în alte celule în funcție de coloana și rândul lor relative. Dacă doriți să păstrați o referință constantă, indiferent de locul în care este copiată formula, trebuie să utilizați referința absolută.

Descărcați gratuit fișierul eșantion eșantion doc


Video: referință absolută

 


Ce este o referință absolută

 

O referință absolută este un tip de referință de celulă în Excel.

În comparație cu o referință relativă care se va schimba în funcție de poziția sa relativă atunci când o formulă este copiată în alte celule, o referință absolută va rămâne constantă indiferent de locul în care este copiată sau mutată formula.

O referință absolută este creată prin adăugarea unui semn dolar ($) înaintea referințelor coloanei și rândurilor din formulă. De exemplu, pentru a crea o referință absolută pentru celula A1, ar trebui să o reprezentați ca $A$1.

Referințele absolute sunt utile atunci când doriți să faceți referire la o celulă sau un interval fix într-o formulă care va fi copiată în mai multe celule, dar nu doriți ca referința să se modifice.

De exemplu, intervalul A4:C7 conține prețurile produselor și doriți să obțineți taxa de plătit pentru fiecare produs pe baza cotei de impozitare din celula B2.

Dacă utilizați referință relativă în formulă, cum ar fi „=B5*B2”, unele rezultate greșite sunt returnate când trageți mânerul de umplere automată în jos pentru a aplica această formulă. Deoarece referința la celula B2 se va schimba în raport cu celulele din formulă. Acum, formula din celula C6 este „=B6*B3”, iar formula din celula C7 este „=B7*B4”

Dar dacă utilizați o referință absolută la celula B2 cu formula „=B5*$B$2”, vă veți asigura că rata de impozitare rămâne aceeași pentru toate celulele atunci când formula este trasă în jos folosind mânerul de completare automată, rezultatele sunt corecte.

Folosind referință relativă   Folosind referință absolută
doc referință absolută 3 1   doc referință absolută 4 1

Cum să faci referințe absolute

 

Pentru a face o referință absolută în Excel, trebuie să adăugați semne dolar ($) înainte de referințele coloanei și rândurilor din formulă. Există două moduri de a crea o referință absolută:

Adăugați manual semne de dolar la referința celulei

Puteți adăuga manual semnele dolarului ($) înainte de referințele de coloană și rând pe care doriți să le faceți absolute în timp ce introduceți formula într-o celulă.

De exemplu, dacă doriți să adăugați numerele din celula A1 și B1 și să le faceți absolut absolute, trebuie doar să introduceți formula „=$A$1+$B$1”. Acest lucru va asigura că referințele de celule rămân constante atunci când formula este copiată sau mutată în alte celule.

Sau dacă doriți să schimbați referințele dintr-o formulă existentă într-o celulă în absolut, puteți selecta celula, apoi mergeți la bara de formule pentru a adăuga semnele dolarului ($).

Folosind comanda rapidă F4 pentru a converti referința relativă în absolută
  1. Faceți dublu clic pe celula cu formula pentru a intra în modul de editare;
  2. Plasați cursorul la referința de celulă pe care doriți să o faceți absolută;
  3. Anunturi F4 tasta de pe tastatură pentru a comuta tipurile de referință până când semnele dolar sunt adăugate înaintea referințelor de coloană și rând;
  4. Anunturi Intrați pentru a ieși din modul de editare și a aplica modificările.

Tasta F4 poate comuta referința între referință relativă, referință absolută și referință mixtă.

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

referință absolută f4 comutator 1

Dacă doriți să faceți toate referințele absolute într-o formulă, selectați întreaga formulă în bara de formule, apăsați F4 tasta pentru a comuta tipurile de referință până când semnele dolar sunt adăugate înaintea referințelor de coloană și rând.

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

referință absolută f4 comutator 2


Utilizați referință absolută cu exemple

 

Această parte oferă 2 exemple pentru a arăta când și cum să folosiți referințe absolute într-o formulă Excel.

Exemplul 1 Calculați procentul din total

Să presupunem că aveți un interval de date (A3:B7) care conține vânzările fiecărui fruct, iar celula B8 conține valoarea totală a vânzărilor acestor fructe, acum doriți să calculați procentul vânzărilor de fructe din total.

Formula generică pentru a calcula procentul din total:

Percentage = Sale/Amount

Utilizați referința relativă din formulă pentru a obține procentul din primul fruct astfel:

=B4/B8

Când trageți în jos mânerul de umplere automată pentru a calcula procentul de alte fructe, #DIV/0! erorile vor fi returnate.

Deoarece atunci când trageți mânerul de umplere automată pentru a copia formula în celulele de mai jos, referința relativă B8 este ajustată automat la alte referințe de celule (B9, B10, B11) în funcție de pozițiile lor relative. Și celula B9, B10 și B11 sunt goale (zero), când divizorul este zero, formula revine la o eroare.

Pentru a remedia erorile, în acest caz, trebuie să faceți referință la celulă B8 absolută ($ B $ 8) în formulă pentru a nu se schimba atunci când mutați sau copiați formula oriunde. Acum formula este actualizată la:

=B4/$B$8

Apoi trageți mânerul de umplere automată în jos pentru a calcula procentul de alte fructe.

Exemplul 2 Căutați o valoare și reveniți la valoarea de potrivire corespunzătoare

Presupunând că doriți să căutați lista de nume în D4:D5 și să returnați salariile corespunzătoare pe baza numelor personalului și a salariului anual corespunzător furnizat în intervalul (A4:B8).

Formula generică de căutat este:

=VLOOKUP(lookup_value, table_range, column_index, logical)

Dacă utilizați referința relativă din formulă pentru a căuta o valoare și a returna valoarea de potrivire corespunzătoare, astfel:

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

Apoi trageți mânerul de umplere automată în jos pentru a căuta valoarea de mai jos, va fi returnată o eroare.

Când glisați mânerul de umplere în jos pentru a copia formula în celula de mai jos, referințele din formulă se ajustează automat cu un rând. Ca rezultat, referința la intervalul tabelului, A4:B8, devine A5:B9. Deoarece „Lisa: nu poate fi găsită în intervalul A5:B9, formula returnează o eroare.

Pentru a evita erorile, utilizați referința absolută $A$4:$B$8 în loc de referința relativă A4:B8 în formula:

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

Apoi trageți mânerul de umplere automată în jos pentru a obține salariul Lisei.


 

2 clicuri pentru a face referințele la celule absolute cu Kutools

 

Indiferent dacă alegeți să tastați manual sau să utilizați comanda rapidă F4, puteți schimba o singură formulă odată în Excel. Dacă doriți să faceți referințe de celule în sute de formule absolute în Excel, Conversia se referă instrument de Kutools pentru Excel vă poate ajuta să vă descurcați cu 2 clicuri.

Selectați celulele formulei pe care doriți să le faceți absolute referințe la celule, faceți clic Kutools > Mai multe (fx) > Conversia se referă. Apoi alegeți La absolut și faceți clic pe Ok or Aplică. Acum toate referințele de celule ale formulelor selectate au fost convertite în absolut.

notițe:

Referință relativă și referință mixtă

 

În afară de referința absolută, există alte două tipuri de referință: referință relativă și referință mixtă.

Referință relativă este tipul de referință implicit în Excel, care este fără semne dolar ($) înainte de referințele de rând și coloane. Și atunci când o formulă cu referințe relative este copiată sau mutată în alte celule, referințele se vor schimba automat în funcție de poziția lor relativă.

De exemplu, când tastați o formulă într-o celulă, cum ar fi „=A1+1”, apoi trageți mânerul de completare automată în jos pentru a completa această formulă în celula următoare, formula se va schimba automat în „=A2+1”.

Referință mixtă este alcătuită atât dintr-o referință absolută, cât și dintr-o referință relativă. Cu alte cuvinte, referința mixtă folosește semnul dolar ($) pentru a fixa fie rândul, fie coloana atunci când o formulă este copiată sau completată.

Luați ca exemplu o tabelă de înmulțire, rândurile și coloanele listează numerele de la 1 la 9, pe care le veți înmulți între ele.

doc referință absolută 15 1

Pentru a începe, puteți folosi formula „=B3*C2” din celula C3 pentru a înmulți 1 din celula B3 cu numărul (1) din prima coloană. Cu toate acestea, când trageți mânerul de completare automată la dreapta pentru a umple celelalte celule, veți observa că toate rezultatele sunt incorecte, cu excepția primei.

doc referință absolută 16 1

Acest lucru se datorează faptului că atunci când copiați formula la dreapta, poziția rândului nu se va schimba, dar poziția coloanei se schimbă de la B3 la C3, D3 etc.. Ca rezultat, formulele din celulele din dreapta (D3, E3, etc.) schimbați la „=C3*D2”, „=D3*E2”, și așa mai departe, când doriți de fapt să fie „=B3*D2”, „=B3*E2”, și așa mai departe.

În acest caz, trebuie să adăugați un semn dolar ($) pentru a bloca referința coloanei „B3”. Utilizați formula ca mai jos:

=$B3*C2

Acum, când trageți formula spre dreapta, rezultatele sunt corecte.

doc referință absolută 17 1

Apoi, trebuie să înmulțiți numărul 1 din celula C2 cu numerele din rândurile de mai jos.

Când copiați formula în jos, poziția coloanei celulei C2 nu se va modifica, dar poziția rândului se schimbă de la C2 la C3, C4 etc. Ca urmare, formulele din celulele de mai jos se schimbă în „=$B4C3”, „=$B5C4”, etc. care va produce rezultate incorecte.

doc referință absolută 18 1

Pentru a rezolva această problemă, schimbați „C2” în „C$2” pentru a împiedica modificarea referinței la rând atunci când trageți în jos mânerul de completare automată pentru a completa formulele.

=$B3*C$2

doc referință absolută 19 1

Acum puteți trage mânerul de umplere automată la dreapta sau în jos pentru a obține toate rezultatele.

doc referință absolută 20 1


Lucruri de amintit

 
  • Rezumatul referințelor de celule

    Tip Exemplu Rezumat
    Referință absolută $ A $ 1 Nu schimbați niciodată când formula este copiată în alte celule
    Referință relativă A1 Referința atât la rând, cât și la coloană se modifică în funcție de poziția relativă atunci când formula este copiată în alte celule
    Referință mixtă

    1 USD/1 USD

    Referința la rând se modifică atunci când formula este copiată în alte celule, dar referința la coloană este fixă/Referința la coloană se modifică atunci când formula este copiată în alte celule, dar referința la rând este fixă;
  • În general, referințele absolute nu se schimbă niciodată atunci când o formulă este mutată. Cu toate acestea, referințele absolute se vor ajusta automat atunci când un rând sau o coloană este adăugată sau eliminată din partea de sus sau din stânga în foaia de lucru. De exemplu, într-o formulă „=$A$1+1”, când inserați un rând în partea de sus a foii, formula se va schimba automat în „=$A$2+1”.

  • F4 tasta poate comuta între referință relativă, referință absolută și referință mixtă.

Cele mai bune instrumente de productivitate de birou

Caracteristici populare: Găsiți, evidențiați sau identificați duplicatele   |  Ștergeți rândurile goale   |  Combinați coloane sau celule fără a pierde date   |   Rundă fără Formula ...
Super căutare: VLookup cu mai multe criterii    VLookup cu valori multiple  |   VLookup pe mai multe foi   |   Căutare fuzzy ....
Listă derulantă avansată: Creați rapid o listă derulantă   |  Listă drop-down dependentă   |  Listă derulantă cu selectare multiplă ....
Manager de coloane: Adăugați un număr specific de coloane  |  Mutați coloanele  |  Comutați starea vizibilității coloanelor ascunse  |  Comparați intervale și coloane ...
Caracteristici prezentate: Focus pe grilă   |  Vedere de proiectare   |   Big Formula Bar    Manager registru de lucru și foi   |  Biblioteca de resurse (Text automat)   |  Data Picker   |  Combinați foi de lucru   |  Criptare/Decriptare celule    Trimiteți e-mailuri după listă   |  Super Filtru   |   Filtru special (filtrează bold/italic/barat...) ...
Top 15 seturi de instrumente12 Text Instrumente (Adăuga text, Eliminați caractere,...)   |   50+ Diagramă Tipuri de (Gantt Chart,...)   |   40+ Practic Formule (Calculați vârsta pe baza zilei de naștere,...)   |   19 inserare Instrumente (Introduceți codul QR, Inserați imaginea din cale,...)   |   12 Convertire Instrumente (Numere la cuvinte, conversie valutara,...)   |   7 Merge & Split Instrumente (Rânduri combinate avansate, Celule divizate,...)   |   ... și altele

Îmbunătățiți-vă abilitățile Excel cu Kutools pentru Excel și experimentați eficiența ca niciodată. Kutools pentru Excel oferă peste 300 de funcții avansate pentru a crește productivitatea și a economisi timp.  Faceți clic aici pentru a obține funcția de care aveți cea mai mare nevoie...

fila kte 201905


Fila Office aduce interfața cu file în Office și vă face munca mult mai ușoară

  • Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
  • Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ​​ferestrele noi.
  • Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse pentru dvs. în fiecare zi!
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