Cuprins
Pentru a facilita calculele folosind formule în modul automat, se folosesc referințe la celule. În funcție de tipul de scriere, acestea sunt împărțite în trei tipuri principale:
- Legături relative. Folosit pentru calcule simple. Copierea unei formule presupune modificarea coordonatelor.
- Legături absolute. Dacă trebuie să realizați calcule mai complexe, această opțiune este potrivită. Pentru reparare folosiți simbolul „$”. Exemplu: $A$1.
- legături mixte. Acest tip de adresare este utilizat în calcule când este necesară fixarea separată a unei coloane sau a unei linii. Exemplu: $A1 sau A$1.
Dacă este necesară copierea datelor formulei introduse, se folosesc referințe cu adresare absolută și mixtă. Articolul va dezvălui cu exemple cum se fac calculele folosind diferite tipuri de link-uri.
Referință relativă a celulei în Excel
Acesta este un set de caractere care definesc locația unei celule. Legăturile din program sunt scrise automat cu adresare relativă. De exemplu: A1, A2, B1, B2. Mutarea pe un alt rând sau coloană modifică caracterele din formulă. De exemplu, poziția de pornire A1. Deplasarea pe orizontală schimbă litera în B1, C1, D1 etc. În același mod, modificările apar la deplasarea de-a lungul unei linii verticale, doar în acest caz numărul se schimbă – A2, A3, A4 etc. Dacă este necesar să se dubleze un calcul de același tip într-o celulă adiacentă, un calcul este efectuat folosind o referință relativă. Pentru a utiliza această funcție, urmați câțiva pași:
- De îndată ce datele sunt introduse în celulă, mutați cursorul și faceți clic cu mouse-ul. Evidențierea cu un dreptunghi verde indică activarea celulei și pregătirea pentru lucrări ulterioare.
- Apăsând combinația de taste Ctrl + C, copiem conținutul în clipboard.
- Activăm celula în care doriți să transferați date sau o formulă scrisă anterior.
- Prin apăsarea combinației Ctrl + V transferăm datele salvate în clipboard-ul sistemului.
Consultanță de specialitate! Pentru a efectua același tip de calcule în tabel, utilizați un hack de viață. Selectați celula care conține formula introdusă anterior. Trecând cursorul peste un pătrat mic care apare în colțul din dreapta jos și ținând apăsat butonul stâng al mouse-ului, trageți la rândul de jos sau coloana extremă, în funcție de acțiunea efectuată. Prin eliberarea butonului mouse-ului, calculul va fi efectuat automat. Acest instrument se numește marcator de umplere automată.
Exemplu de link relativ
Pentru a fi mai clar, luați în considerare un exemplu de calcul folosind o formulă cu o referință relativă. Să presupunem că proprietarul unui magazin de sport după un an de muncă trebuie să calculeze profitul din vânzări.
Ordinea acțiunilor:
- Exemplul arată că coloanele B și C au fost folosite pentru a completa cantitatea de mărfuri vândute și prețul acesteia. În consecință, pentru a scrie formula și a obține răspunsul, selectați coloana D. Formula arată astfel: = B2 *C
Fii atent! Pentru a facilita procesul de scriere a unei formule, folosiți un mic truc. Puneți semnul „=”, faceți clic pe cantitatea de marfă vândută, setați semnul „*” și faceți clic pe prețul produsului. Formula de după semnul egal va fi scrisă automat.
- Pentru răspunsul final, apăsați „Enter”. În continuare, trebuie să calculați suma totală a profitului primit de la alte tipuri de produse. Ei bine, dacă numărul de linii nu este mare, atunci toate manipulările pot fi efectuate manual. Pentru a completa un număr mare de rânduri în același timp în Excel, există o funcție utilă care face posibilă transferul formulei în alte celule.
- Mutați cursorul peste colțul din dreapta jos al dreptunghiului cu formula sau rezultatul final. Apariția unei cruci negre servește drept semnal că cursorul poate fi tras în jos. Astfel, se face un calcul automat al profitului primit pentru fiecare produs separat.
- Prin eliberarea butonului mouse-ului apăsat, obținem rezultatele corecte în toate rândurile.
Făcând clic pe celula D3, puteți vedea că coordonatele celulei au fost modificate automat și acum arată astfel: =B3 *C3. Rezultă că legăturile erau relative.
Posibile erori atunci când lucrați cu legături relative
Fără îndoială, această funcție Excel simplifică foarte mult calculele, dar în unele cazuri pot apărea dificultăți. Să luăm în considerare un exemplu simplu de calcul al coeficientului de profit al fiecărui articol de mărfuri:
- Creați un tabel și completați: A – numele produsului; B – cantitatea vândută; C – cost; D este suma primită. Să presupunem că există doar 11 articole în sortiment. Prin urmare, ținând cont de descrierea coloanelor, sunt completate 12 rânduri și suma totală a profitului este D
- Faceți clic pe celula E2 și intrați =D2 /D13.
- După apăsarea butonului „Enter”, apare coeficientul ponderii relative a vânzărilor primului articol.
- Întindeți coloana în jos și așteptați rezultatul. Cu toate acestea, sistemul dă eroarea „#DIV/0!”
Motivul erorii este utilizarea unei referințe relative pentru calcule. Ca urmare a copierii formulei, coordonatele se modifică. Adică, pentru E3, formula va arăta așa =D3 /D13. Deoarece celula D13 nu este completată și teoretic are o valoare zero, programul va da o eroare cu informația că împărțirea la zero este imposibilă.
Important! Pentru a corecta eroarea, este necesar să scrieți formula în așa fel încât coordonatele D13 să fie fixe. Adresarea relativă nu are o astfel de funcționalitate. Pentru a face acest lucru, există un alt tip de legături – absolute.
Cum faci o legătură absolută în Excel
Datorită utilizării simbolului $, a devenit posibil să se stabilească coordonatele celulei. Cum funcționează, vom analiza în continuare. Deoarece programul utilizează în mod implicit adresarea relativă, în consecință, pentru a o face absolută, va trebui să efectuați o serie de acțiuni. Să analizăm soluția la eroarea „Cum să găsim coeficientul din vânzarea mai multor articole de mărfuri”, efectuând calculul folosind adresarea absolută:
- Faceți clic pe E2 și introduceți coordonatele linkului =D2 /D13. Deoarece legătura este relativă, trebuie setat un simbol pentru a fixa datele.
- Fixați coordonatele celulei D Pentru a efectua această acțiune, precedați litera care indică coloana și numărul rândului prin plasarea unui semn „$”.
Consultanță de specialitate! Pentru a facilita sarcina de a intra, este suficient să activați celula pentru editarea formulei și să apăsați tasta F4 de mai multe ori. Până obțineți valori satisfăcătoare. Formula corectă este următoarea: =D2/$D$13.
- Apăsați butonul „Enter”. Ca urmare a acțiunilor efectuate, ar trebui să apară rezultatul corect.
- Trageți marcatorul în linia de jos pentru a copia formula.
Datorită utilizării adresei absolute în calcule, rezultatele finale din rândurile rămase vor fi corecte.
Cum să puneți o legătură mixtă în Excel
Pentru calculele formulelor se folosesc nu numai referințe relative și absolute, ci și cele mixte. Caracteristica lor distinctivă este că fixează una dintre coordonate.
- De exemplu, pentru a schimba poziția unei linii, trebuie să scrieți semnul $ în fața desemnării literei.
- Dimpotrivă, dacă semnul dolarului este scris după desemnarea literei, atunci indicatorii din linie vor rămâne neschimbați.
De aici rezultă că pentru a rezolva problema anterioară cu determinarea coeficientului de vânzări de mărfuri folosind adresarea mixtă, este necesar să se stabilească numărul rândului. Adică semnul $ este plasat după litera coloanei, deoarece coordonatele acesteia nu se schimbă nici măcar într-o referință relativă. Să luăm un exemplu:
- Pentru calcule precise, introduceți =D1/$D$3 și apăsați „Enter”. Programul oferă răspunsul exact.
- Pentru a muta formula în celulele ulterioare în jos pe coloană și pentru a obține rezultate precise, trageți mânerul în celula de jos.
- Ca rezultat, programul va oferi calculele corecte.
Atenție! Dacă setați semnul $ în fața literei, atunci Excel va da o eroare „#DIV/0!”, ceea ce va însemna că această operație nu poate fi efectuată.
Adresare „Superabsolută”.
În cele din urmă, să ne uităm la un alt exemplu de legătură absolută – adresarea „SuperAbsolute”. Care sunt caracteristicile și diferențele sale. Luați numărul aproximativ 30 și introduceți-l în celula B2. Acest număr va fi principalul, este necesar să efectuați o serie de acțiuni cu el, de exemplu, pentru a-l ridica la o putere.
- Pentru executarea corectă a tuturor acțiunilor, introduceți următoarea formulă în coloana C: =$B$2^$D2. În coloana D introducem valoarea gradelor.
- După ce apăsăm butonul „Enter” și activăm formula, întindem markerul în jos pe coloană.
- Obținem rezultatele corecte.
Concluzia este că toate acțiunile efectuate se refereau la o celulă fixă B2, prin urmare:
- Copierea unei formule din celula C3 în celula E3, F3 sau H3 nu va modifica rezultatul. Va rămâne neschimbat – 900.
- Dacă trebuie să introduceți o nouă coloană, coordonatele celulei cu formula se vor schimba, dar rezultatul va rămâne, de asemenea, neschimbat.
Aceasta este particularitatea linkului „SuperAbsolute”: dacă trebuie să mutați, rezultatul nu se va schimba. Cu toate acestea, există situații în care datele sunt inserate din surse terțe. Astfel, coloanele sunt deplasate în lateral, iar datele sunt setate în modul vechi în coloana B2. Ce se întâmplă în acest caz? La amestecare, formula se schimbă în funcție de acțiunea efectuată, adică nu va mai indica B2, ci C2. Dar din moment ce inserarea a fost făcută în B2, rezultatul final va fi greșit.
Referinţă! Pentru a putea insera macrocomenzi din surse terțe, trebuie să activați setările pentru dezvoltatori (acestea sunt dezactivate implicit). Pentru a face acest lucru, accesați Opțiuni, deschideți setările panglicii și bifați caseta din coloana din dreapta vizavi de „Dezvoltator”. După aceea, se va deschide accesul la multe funcții care anterior erau ascunse de ochii utilizatorului obișnuit.
Aceasta ridică întrebarea: este posibil să se modifice formula din celula C2, astfel încât numărul inițial să fie colectat din celula B, în ciuda inserării de noi coloane de date? Pentru a vă asigura că modificările din tabel nu afectează determinarea totalului la instalarea datelor din surse terțe, trebuie să efectuați următorii pași:
- În loc de coordonatele celulei B2, introduceți următorii indicatori: =INDIRECT(„B2”). Ca rezultat, după mutare, compoziția de formulare va arăta astfel: =INDIRECT(„B2”)^$E2.
- Datorită acestei funcții, legătura indică întotdeauna către pătratul cu coordonatele B2, indiferent dacă coloanele sunt adăugate sau eliminate în tabel.
Trebuie înțeles că o celulă care nu conține date arată întotdeauna valoarea „0”.
Concluzie
Datorită utilizării a trei varietăți de link-uri descrise, apar o mulțime de oportunități care fac mai ușor să lucrezi cu calcule în Excel. Prin urmare, înainte de a începe să lucrați cu formule, citiți mai întâi linkurile și regulile de instalare a acestora.