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ă.

• Ce este o referință absolută
• Cum să faci referințe absolute
• Utilizați referință absolută cu exemple
• Calculați procentul din total
• Căutați o valoare și reveniți la valoarea de potrivire corespunzătoare
• 2 clicuri pentru a face referințele la celule absolute cu Kutools
• Referință relativă și referință mixtă
• Lucruri de amintit

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

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ă

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

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

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.

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.

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.

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.

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.

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

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

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ă.