Pentru a face calcule exacte în Excel, este esențial să înțelegem cum funcționează diferitele tipuri de referințe de celulă.
Referințe A1 vs. R1C1
Fișele de lucru Excel conțin multe celule și (în mod implicit) fiecare celulă este identificată prin scrisoare coloană urmat de numărul rândului său. Aceasta este cunoscută sub numele de referință în stil A1. Exemple: A1, B4, C6
Stil de referință A1
Opțional, puteți trece la Mod de referință R1C1 pentru a face referire la rândul unei celule și numărul coloanei. În loc să faceți referire la celula A1, vă referiți la R1C1 (rândul 1, coloana 1). Celula C4 ar fi menționată la R4C3.
Stil de referință R1C1
Referințele în stil R1C1 sunt extrem de neobișnuite în Excel. Cu excepția cazului în care aveți un motiv întemeiat, probabil că ar trebui să rămâneți la modul implicit de referință în stil A1. Cu toate acestea, dacă utilizați VBA, veți întâlni probabil acest stil de referință.
Treceți la stilul de referință R1C1
Pentru a comuta stilul de referință, accesați Fișier> Opțiune> Formula. Bifați caseta de lângă Stil de referință R1C1.
Gama denumită
Una dintre cele mai subutilizate caracteristici ale Excel este caracteristica Named Ranges. În loc să faceți referire la o celulă (sau grup de celule) după locația sa de celulă (ex B3 sau R3C2), puteți denumi acest interval și pur și simplu faceți referire la numele intervalului în formulele dvs.
Pentru a denumi un interval:
- Selectați celula sau celulele pe care doriți să le denumiți
- Faceți clic pe interiorul casetei Nume gamă
- Introduceți numele dorit
- Apăsați Enter
Acum puteți face referință la celula A1 tastând în schimb = range_name1. Acest lucru este foarte util atunci când lucrați cu registre de lucru mari cu mai multe foi de lucru.
Gama de celule
Când utilizați funcțiile încorporate Excel, poate fi necesar să faceți referire la intervale de celule. Intervalele de celule apar astfel: „A1: B3”. Această referință se referă la toate celulele cuprinse între A1 și B3: celulele A1, A2, A3, B1, B2, B3.
Pentru a selecta un interval de celule atunci când introduceți o formulă:
- Tastați intervalul (separați intervalul de început și sfârșit cu un punct și virgulă)
- Folosiți mouse-ul pentru a face clic pe prima referință de celulă, țineți apăsat butonul mouse-ului și trageți zona dorită.
- Țineți apăsată tasta Shift și navigați cu tastele săgeată pentru a selecta raza de acțiune
Referințe absolute (înghețate) și relative
Când introduceți referințe de celule în formule, puteți utiliza referințe relative sau absolute (înghețate). Referințele relative se vor mișca proporțional atunci când copiați formula într-o celulă nouă. Referințele absolute vor rămâne neschimbate. Să vedem câteva exemple:
Referință relativă
O referință relativă în Excel arată astfel
= A1
Când copiați și lipiți o formulă cu referințe relative, referințele relative se vor deplasa proporțional. Dacă nu specificați altfel, referințele dvs. de celule vor fi relative (neînghetate) în mod implicit.
Exemplu: Dacă copiați „= A1” în jos un rând, referința se schimbă în „= A2”.
Referințe de celule absolute (înghețate)
Dacă nu doriți ca referințele dvs. de celule să se mute atunci când copiați o formulă, puteți „îngheța” referințele de celule adăugând semne de dolar ($ s) în fața referinței pe care doriți să o înghețați. Acum, când copiați și lipiți formula, referința celulei va rămâne neschimbată. Puteți alege să înghețați referința rândului, referința coloanei sau ambele.
A1: Nimic nu este înghețat
$ A1: Coloana este înghețată, dar rândul nu este înghețat
A $ 1: rândul este înghețat, dar coloana nu este înghețată
$ A $ 1: Atât rândul, cât și coloana sunt înghețate
Comandă rapidă de referință absolută
Adăugarea manuală a semnelor de dolar ($ s) în formulele dvs. nu este foarte practic. În schimb, în timp ce creați formula, utilizați tasta F4 pentru a comuta între referințele de celule absolute / relative.
Exemplu de referință de celulă absolută
Când ar trebui să înghețați o referință de celulă? Un exemplu comun este atunci când aveți celule de intrare la care se face referire frecvent. În exemplul de mai jos, dorim să calculăm impozitul pe vânzări pentru fiecare cantitate de articole din meniu. Impozitul pe vânzări este constant în toate articolele, așa că vom face referință la celula taxei pe vânzări în mod repetat.
Pentru a găsi impozitul total pe vânzări, introduceți formula „= (B3 * C3) * $ C $ 1” în coloana D și copiați formula în jos.
Referință mixtă
Este posibil să fi auzit de referințe de celule mixte. O referință mixtă este atunci când referința la rând sau la coloană este blocată (dar nu la ambele).
Referință mixtă
Amintiți-vă, folosind tasta „F4”, puteți parcurge referințele de celule absolute și relative.
Referințe celulare - Inserarea și ștergerea rândurilor / coloanelor
S-ar putea să vă întrebați ce se întâmplă cu referințele celulare atunci când introduceți sau ștergeți rânduri / coloane?
Referința celulei se va actualiza automat pentru a se referi la celula originală. Acesta este cazul indiferent dacă referința celulei este înghețată.
Referințe 3D
Uneori, poate fi necesar să lucrați cu mai multe foi de lucru cu modele de date identice. Excel vă permite să faceți referire la mai multe foi simultan fără a fi nevoie să introduceți manual fiecare fișă de lucru. Puteți face referire la o gamă de foi similară cu modul în care ar face referire la o gamă de celule. Exemplul „Sheet1: Sheet5! A1” ar face referință la celulele A1 de pe toate foile de la Sheet1 la Sheet5.
Să parcurgem un exemplu:
Doriți să adăugați unitățile totale vândute pentru fiecare produs în toate magazinele. Fiecare magazin are propria foaie de lucru și toate foile de lucru au un format identic. Puteți crea o formulă similară cu aceasta:
Acest lucru nu este prea dificil doar cu patru foi de lucru, dar dacă ai avea 40 de foi de lucru? Doriți cu adevărat să adăugați manual fiecare referință de celulă?
În schimb, puteți utiliza o referință 3D pentru a face referire la mai multe foi simultan cu ușurință (similar modului în care puteți face referire la o gamă de celule).
Atenție! Contează ordinea foilor dvs. de lucru. Dacă mutați o altă foaie între foile de referință (StoreA și StoreD), foaia va fi inclusă. În schimb, dacă mutați o foaie în afara intervalului de foi (înainte de StoreA sau după StoreD), aceasta nu va mai fi inclusă.
Referință de celulă circulară
O referință circulară de celulă este atunci când o celulă se referă la ea însăși. De exemplu, dacă rezultatul celulei B1 este utilizat ca intrare pentru celula B1, atunci se creează o referință circulară. Celula nu trebuie să se refere direct la ea însăși. Pot exista pași intermediari.
Exemplu:
În acest caz, formula pentru celula B2 este „A2 + A3 + B2”. Deoarece vă aflați în celula B2, este posibil să nu utilizați B2 în ecuație. Aceasta va declanșa referința circulară, iar valoarea din celula „B2” va fi setată automat la „0”.
De obicei, referințele circulare sunt rezultatul unei erori a utilizatorului, dar există circumstanțe în care poate doriți să utilizați o referință circulară. Exemplul principal al utilizării unei referințe circulare este calcularea iterativă a valorilor. Pentru a face acest lucru, trebuie să mergeți la Fișier> Opțiuni> Formule și Activat Iterative Calcul:
Referințe externe
Uneori când calculați date, poate fi necesar să vă referiți la datele din afara registrului de lucru. Aceasta se numește o referință externă (link).
Pentru a selecta o referință externă în timp ce creați o formulă, navigați la registrul de lucru extern și selectați referința dvs. așa cum ați face în mod normal.
Pentru a naviga la celălalt registru de lucru, puteți utiliza comanda rapidă CTRL + TAB sau accesați Vizualizare> Comutare Windows.
După ce ați selectat celula, veți vedea o referință externă care arată astfel:
Observați că numele registrului de lucru este inclus între paranteze [].
După ce închideți registrul de lucru la care faceți referire, referința va afișa locația fișierului:
Când redeschideți registrul de lucru care conține linkul extern, vi se va solicita să activați actualizarea automată a linkurilor. Dacă faceți acest lucru, atunci Excel va deschide valoarea de referință cu valoarea curentă a registrului de lucru. Chiar dacă este închis! Atenție! Poate doriți sau nu acest lucru.
Domenii denumite și referințe externe
Ce se întâmplă cu referința dvs. de celulă externă atunci când rândurile / coloanele sunt adăugate sau șterse din registrul de lucru de referință? Dacă ambele registre de lucru sunt deschise, referințele celulei se actualizează automat. Cu toate acestea, dacă ambele registre de lucru nu sunt deschise, referințele celulei nu se vor actualiza și nu vor mai fi valabile. Aceasta este o îngrijorare imensă atunci când faceți legătura cu registre de lucru externe. Multe erori sunt cauzate de aceasta.
Dacă faceți link către registre de lucru externe, ar trebui să denumiți referința celulei cu un interval numit (a se vedea secțiunea anterioară pentru mai multe informații). Acum formula dvs. se va referi la intervalul numit, indiferent de modificările care apar pe registrul de lucru extern.
