O foaie de lucru în Excel este alcătuită din celule. Aceste celule pot fi referite prin specificarea valorii rândului și a valorii coloanei.
De exemplu, A1 se va referi la primul rând (specificat ca 1) și la prima coloană (specificat ca A). În mod similar, B3 ar fi al treilea rând și a doua coloană.
Puterea Excel constă în faptul că puteți utiliza aceste referințe de celule în alte celule atunci când creați formule.
Acum există trei tipuri de referințe de celule pe care le puteți utiliza în Excel:
- Referințe relative de celule
- Referințe de celule absolute
- Referințe de celule mixte
Înțelegerea acestor diferite tipuri de referințe de celulă vă va ajuta să lucrați cu formule și să economisiți timp (mai ales atunci când copiați formule de lipire).
Ce sunt referințele celulare relative în Excel?
Permiteți-mi să iau un exemplu simplu pentru a explica conceptul referințelor de celule relative în Excel.
Să presupunem că am un set de date prezentat mai jos:
Pentru a calcula totalul pentru fiecare articol, trebuie să înmulțim prețul fiecărui articol cu cantitatea respectivului articol.
Pentru primul element, formula din celula D2 ar fi B2 * C2 (așa cum se arată mai jos):
Acum, în loc să introduceți formula pentru toate celulele una câte una, puteți copia pur și simplu celula D2 și lipiți-o în toate celelalte celule (D3: D8). Când o faceți, veți observa că referința celulei se ajustează automat pentru a se referi la rândul corespunzător. De exemplu, formula din celula D3 devine B3 * C3 și formula din D4 devine B4 * C4.
Aceste referințe de celule care se reglează singure atunci când celula este copiată sunt numite referințe relative de celule în Excel.
Când se utilizează referințe relative de celule în Excel?
Referințele de celule relative sunt utile atunci când trebuie să creați o formulă pentru o gamă de celule, iar formula trebuie să se refere la o referință de celulă relativă.
În astfel de cazuri, puteți crea formula pentru o celulă și copiați-o lipiți în toate celulele.
Ce sunt referințele de celule absolute în Excel?
Spre deosebire de referințele de celule relative, referințele de celule absolute nu se modifică atunci când copiați formula în alte celule.
De exemplu, să presupunem că aveți setul de date așa cum se arată mai jos, unde trebuie să calculați comisionul pentru vânzările totale ale fiecărui articol.
Comisionul este de 20% și este listat în celula G1.
Pentru a obține suma comisionului pentru fiecare articol de vânzare, utilizați următoarea formulă în celula E2 și copiați pentru toate celulele:
= D2 * $ G $ 1
Rețineți că există două semne de dolar ($) în referința celulei care are comisionul - $G$2.
Ce face semnul dolar ($)?
Un simbol dolar, atunci când este adăugat în fața numărului rândului și coloanei, îl face absolut (adică, oprește modificarea numărului rândului și coloanei atunci când este copiat în alte celule).
De exemplu, în cazul de mai sus, când copiez formula din celula E2 în E3, aceasta se schimbă de la = D2 * $ G $ 1 la = D3 * $ G $ 1.
Rețineți că, în timp ce D2 se schimbă în D3, $ G $ 1 nu se schimbă.
Deoarece am adăugat un simbol dolar în fața „G” și „1” în G1, nu ar permite ca referința celulei să se schimbe atunci când este copiată.
Prin urmare, acest lucru face ca referința celulei să fie absolută.
Când se utilizează referințe de celule absolute în Excel?
Referințele de celule absolute sunt utile atunci când nu doriți ca referința de celulă să se schimbe pe măsură ce copiați formulele. Acesta ar putea fi cazul când aveți o valoare fixă pe care trebuie să o utilizați în formulă (cum ar fi rata de impozitare, rata comisionului, numărul de luni etc.)
Deși puteți codifica această valoare în formulă (de exemplu, utilizați 20% în loc de $ G $ 2), dacă o aveți într-o celulă și apoi folosiți referința la celulă vă permite să o modificați la o dată viitoare.
De exemplu, dacă structura comisionului dvs. se schimbă și acum plătiți 25% în loc de 20%, puteți schimba pur și simplu valoarea din celula G2 și toate formulele se vor actualiza automat.
Ce sunt referințele de celule mixte în Excel?
Referințele de celule mixte sunt puțin mai complicate decât referințele de celule absolute și relative.
Pot exista două tipuri de referințe de celule mixte:
- Rândul este blocat în timp ce coloana se schimbă atunci când formula este copiată.
- Coloana este blocată în timp ce rândul se modifică atunci când formula este copiată.
Să vedem cum funcționează folosind un exemplu.
Mai jos este un set de date în care trebuie să calculați cele trei niveluri de comision pe baza valorii procentuale din celulele E2, F2 și G2.
Acum puteți utiliza puterea referinței mixte pentru a calcula toate aceste comisioane cu o singură formulă.
Introduceți formula de mai jos în celula E4 și copiați pentru toate celulele.
= $ B4 * $ C4 * E $ 2
Formula de mai sus folosește ambele tipuri de referințe de celule mixte (una în care rândul este blocat și unul în care coloana este blocată).
Să analizăm fiecare referință de celulă și să înțelegem cum funcționează:
- $ B4 (și $ C4) - În această referință, semnul dolarului este chiar înainte de notația Coloanei, dar nu înainte de numărul rândului. Aceasta înseamnă că atunci când copiați formula în celulele din dreapta, referința va rămâne aceeași cu coloana fixă. De exemplu, dacă copiați formula de la E4 la F4, această referință nu se va modifica. Cu toate acestea, când îl copiați, numărul rândului se va schimba deoarece nu este blocat.
- 2 USD - În această referință, semnul dolar este chiar înainte de numărul rândului, iar notația Coloană nu are niciun semn dolar. Acest lucru înseamnă că, atunci când copiați formula în jos, celulele, referința nu se va schimba, deoarece numărul rândului este blocat. Cu toate acestea, dacă copiați formula în dreapta, alfabetul coloanei se va modifica deoarece nu este blocat.
Cum se schimbă referința de la relativ la absolut (sau mixt)?
Pentru a schimba referința de la relativ la absolut, trebuie să adăugați semnul dolar înainte de notația coloanei și numărul rândului.
De exemplu, A1 este o referință relativă a celulei și ar deveni absolut atunci când o faceți $ A $ 1.
Dacă aveți doar câteva referințe de modificat, este posibil să vă fie ușor să modificați manual aceste referințe. Deci, puteți merge la bara de formule și edita formula (sau selectați celula, apăsați F2 și apoi modificați-o).
Cu toate acestea, o modalitate mai rapidă de a face acest lucru este folosirea comenzii rapide de la tastatură - F4.
Când selectați o referință de celulă (în bara de formulă sau în celulă în modul de editare) și apăsați F4, aceasta modifică referința.
Să presupunem că aveți referința = A1 într-o celulă.
Iată ce se întâmplă când selectați referința și apăsați tasta F4.
- Apăsați tasta F4 o dată: Referința celulei se schimbă de la A1 la $ A $ 1 (devine „absolut” din „relativ”).
- Apăsați tasta F4 de două ori: Referința celulei se schimbă de la A1 la A $ 1 (se modifică la referința mixtă unde rândul este blocat).
- Apăsați tasta F4 de trei ori: Referința celulei se schimbă de la A1 la $ A1 (se modifică la referința mixtă acolo unde coloana este blocată).
- Apăsați tasta F4 de patru ori: Referința celulei devine din nou A1.
