Merwyn din Anglia a trimis această problemă.
Există o singură formulă în celula B2 care poate fi copiată în sus și în jos pentru a crea un tabel de înmulțire?
Scopul lui Merwyn este de a introduce o singură formulă în B2, care poate fi copiată cu ușurință în toate cele 144 de celule pentru a crea un tabel de referință de multiplicare.
Să atacăm acest lucru ca un novice Excel și să vedem în ce capcană ne confruntăm. Reacția inițială ar putea fi aceea de a avea formula în B2 =A2*B1. Această formulă produce rezultatul corect, dar nu este potrivită pentru atribuirea lui Merwyn.
Când copiați această formulă din celula B2 în celula C2, celulele la care se face referire în formulă se deplasează și peste o celulă. Formula care a fost =A2*B1acum devine =C1*B2. Aceasta este o caracteristică proiectată în Microsoft Excel și se numește o referință formulă relativă. Pe măsură ce copiați o formulă, referințele de celule din formulă mută, de asemenea, un număr corespunzător de celule peste și în jos.
Există momente în care nu doriți ca Excel să prezinte acest comportament, iar cazul actual este unul dintre acestea. Pentru a împiedica Excel să schimbe referința în timp ce copiați celulele, introduceți un „$” înainte de porțiunea de referință pe care doriți să o înghețați. Exemple:
- $ A1 îi spune Excel că doriți întotdeauna să faceți referire la coloana A.
- B $ 1 spune Excel că doriți întotdeauna să faceți referire la rândul 1.
- $ B $ 1 spune Excel că doriți întotdeauna să faceți referire la celula B1.
Învățarea acestui cod va reduce dramatic timpul necesar pentru a construi foi de lucru. În loc să trebuiască să introducă 144 de formule, Merwyn poate folosi această prescurtare pentru a introduce o singură formulă și a o copia în toate celulele.
Privind formula lui Merwyn =B1*A2, ne dăm seama că partea "B1" a expresiei ar trebui să indice întotdeauna un număr din rândul 1. Acesta ar trebui rescris ca "B $ 1". Secțiunea „A2” a formulei trebuie să indice întotdeauna un număr din coloana A. Acesta trebuie rescris ca „$ A2”. Deci, noua formulă din celula B2 este =B$1*$A2.
După introducerea formulei în B2, o pot copia și lipi în intervalul corespunzător. Excel îmi urmează instrucțiunile și după ce fac copia, găsesc următoarele formule:
- Celula M2 conține
=M$1*$A2 - Celula B13 conține
=B$1*$A13 - Celula M13 conține
=M$1*$A13
Fiecare dintre aceste tipuri de formule are un nume. Formulele fără semne de dolar se numesc formule relative. Formulele în care atât rândul cât și coloana sunt blocate cu un semn de dolar se numesc formule absolute. Formulele în care rândul sau coloana sunt blocate cu un semn de dolar se numesc formule mixte.
Există o metodă de prescurtare pentru introducerea semnelor de dolar. Pe măsură ce tastați o formulă și terminați o referință de celulă, puteți apăsa tasta pentru a comuta între cele 4 tipuri de referință. Să presupunem că ați început să tastați o formulă și ați tastat =100*G87.
- Apăsați F4 și formula dvs. se schimbă în
=100*$G$87 - Apăsați din nou pe F4 și formula dvs. se schimbă în
=100*G$87 - Apăsați din nou pe F4 și formula dvs. se schimbă în
=100*$G87 - Apăsați din nou pe F4 și formula dvs. revine la original
=100*G87
Puteți face o pauză în timpul introducerii formulei la fiecare referință de celulă pentru a atinge F4 până când obțineți tipul de referință corect. Tastările pentru a introduce formula lui Merwyn de mai sus sunt =B1*A1.
Una dintre utilizările mele preferate de referințe la formule mixte apare atunci când am o listă lungă de intrări în coloana A. Când vreau o metodă rapidă și murdară pentru a găsi duplicate, uneori introduc această formulă în celula B4 și apoi o copiez:
=VLOOKUP(A4,$A$2:$A3,1,FALSE)
Rețineți că al doilea termen al formulei VLOOKUP folosește atât o referință absolută ($ A $ 2), cât și o referință mixtă ($ A3) pentru a descrie intervalul de căutare. În limba engleză, îi spun Excel să caute întotdeauna de la celula $ A $ 2 până la celula din coloana A chiar deasupra celulei curente. De îndată ce Excel întâlnește o valoare duplicat, rezultatul acestei formule se schimbă din # N / A! la o valoare.
Cu utilizarea creativă a referințelor de celule $, vă puteți asigura că o singură formulă poate fi copiată în mai multe celule cu rezultate corecte.
