Aflați cum să copiați formule în mai multe celule, în jos pe o coloană, în celule neadiacente, să copiați formule cu referințe de celule absolute sau mixte etc.
Copierea formulelor este una dintre cele mai comune și mai ușoare sarcini pe care le faci într-o foaie de calcul tipică care se bazează în principal pe formule. În loc să tastați aceeași formulă din nou și din nou în Excel, puteți doar să copiați și să lipiți cu ușurință o formulă dintr-o celulă în mai multe celule.
După ce ați scris o formulă în Excel, puteți utiliza comenzile de copiere și inserare în mai multe celule, mai multe celule neadiacente sau coloane întregi. Dacă nu o faci corect, vei ajunge cu acele erori groaznice # REF și /DIV0. În acest articol, vă vom arăta diferitele metode pe care le puteți utiliza pentru a copia formule în Excel.
Cum să copiați și să lipiți formule în Excel
Microsoft Excel oferă diferite moduri de a copia formule cu referințe de celule relative, referințe de celule absolute sau referințe mixte.
- Copiați formula dintr-o celulă în alta
- Copiați formula o celulă în mai multe celule
- Copierea formulei în întreaga coloană
- Copierea formulei fără formatare
- Copiați formule în celule non-adiacente
- Copiați formule fără a modifica referințele de celule
Cum să copiați o formulă dintr-o celulă în alta în Excel
Uneori este posibil să doriți să copiați o formulă dintr-o celulă în alta în Excel pentru a evita să resetați întreaga formulă din nou și să economisiți ceva timp în timp ce faceți asta.
Să presupunem că avem acest tabel:
Există puține metode de a copia formula dintr-o celulă în alta.
Mai întâi, selectați celula cu formula și faceți clic dreapta, iar în meniul contextual, selectați „Copiare” pentru a copia formula. Sau puteți utiliza opțiunea „Copiere” din secțiunea „Clipboard” din fila „Acasă”.
Dar copiați și formule prin simpla apăsare a comenzii rapide de la tastatură Ctrl + C. Aceasta este o metodă mai eficientă și care economisește timp.
Apoi mergem la celula pe care vrem să o lipim apăsați comanda rapidă Ctrl + V pentru a lipi formula. Sau faceți clic dreapta pe celula pe care doriți să o inserați și faceți clic pe opțiunile din „Opțiuni de lipire”: fie opțiune simplă „Lipiți (P)”, fie lipiți ca opțiune „Formulă (F)”.
Alternativ, puteți, de asemenea, să faceți clic pe „Lipire specială” sub cele șase pictograme de lipire pentru a deschide caseta de dialog „Lipire specială”. Aici, aveți mai multe opțiuni, inclusiv cele șase opțiuni de lipire din meniul contextual. Selectați „Toate” sau „Formulele” în secțiunea Lipire și faceți clic pe „OK”.
Acum, celula cu formula lipită ar trebui să aibă aceleași formule (ca cea din celula copiată), dar cu referințe de celulă diferite. Adresa celulei este autoajustată de Excel pentru a se potrivi cu numărul de rând al celulei lipite.
Copiați formula dintr-o celulă în mai multe celule
Aceeași operațiune de lipire funcționează la fel dacă selectăm mai multe celule sau o serie de celule.
Selectați celula cu formula și apăsați Ctrl + C pentru a copia formula. Apoi, selectați toate celulele în care doriți să lipiți formula și apăsați Ctrl + V pentru a lipi formula sau utilizați una dintre metodele de lipire de mai sus pentru a lipi formula (cum am făcut pentru o singură celulă).
Copiați formula pe o întreagă coloană sau rând
În Excel, puteți copia rapid o formulă pe o întreagă coloană sau pe un rând.
Pentru a copia o formulă într-o coloană sau un rând, mai întâi, introduceți o formulă într-o celulă. Apoi, selectați celula formulei (D1) și plasați cursorul peste un mic pătrat verde din colțul din dreapta jos al celulei. Pe măsură ce treceți cu mouse-ul, cursorul se va schimba într-un semn plus negru (+), care se numește mâner de umplere. Faceți clic și țineți apăsat acel mâner de umplere și trageți-l în orice direcție doriți (coloană sau rând) peste celule pentru a copia formula.
Când copiați o formulă într-un interval de celule, referințele de celule ale formulei se vor ajusta automat în funcție de locația relativă a rândurilor și coloanelor, iar formula va efectua calcule pe baza valorilor din acele referințe de celule (vezi mai jos).
În exemplul de mai sus, când formula din D1 (=A1*B1)/2) este copiată în celula D2, referința relativă se schimbă pe baza locației sale (=A2*B2)/2) și așa mai departe.
În același mod, puteți trage formula în celulele adiacente la stânga, la dreapta sau în sus.
O altă modalitate de a copia formula într-o coloană întreagă este făcând dublu clic pe mânerul de umplere în loc să o trageți. Când faceți dublu clic pe mânerul de umplere, acesta aplică imediat formula în măsura în care există date în celula adiacentă.
Copiați o formulă într-un interval fără a copia formatarea
Când copiați o formulă într-un interval de celule cu mânerul de umplere, aceasta copiază și formatarea celulei sursă, cum ar fi culoarea fontului sau culoarea fundalului, moneda, procentul, timpul etc. (după cum se arată mai jos).
Pentru a preveni copierea formatării celulei, trageți mânerul de umplere și faceți clic pe „Opțiuni de completare automată” din colțul din dreapta jos al ultimei celule. Apoi, în meniul derulant, selectați „Umpleți fără formatare”.
Rezultatul:
Copiați o formulă Excel cu doar formatare numerică
Dacă doriți să copiați formula doar cu formula și formatarea, cum ar fi formatul procentual, punctele zecimale etc.
Copiați formula și selectați toate celulele în care doriți să copiați formula. În fila „Acasă”, faceți clic pe săgeata de sub butonul „Lipire” de pe panglică. Apoi, faceți clic pe pictograma „Formule și formatare numere” (pictograma cu % fx) din meniul derulant pentru a lipi doar formula și formatarea numărului.
Această opțiune copiează doar formatarea formulei și a numărului, dar ignoră toate celelalte formatări ale celulelor, cum ar fi culoarea de fundal, culoarea fontului etc.
Copiați o formulă în celule non-adiacente/necontigue
Dacă doriți să copiați o formulă în celule neadiacente sau în intervale neadiacente, puteți face asta cu ajutorul Ctrl cheie.
Selectați celula cu formula și apăsați Ctrl + C să-l copieze. Apoi, selectați celule/intervaluri neadiacente în timp ce apăsați și mențineți apăsat butonul Ctrl cheie. Apoi, apăsați Ctrl + V pentru a lipi formula și a apăsa introduce a termina.
Copierea formulelor fără modificarea referințelor de celule în Excel
Când o formulă este copiată într-o altă celulă, Excel modifică automat referințele celulei pentru a se potrivi cu noua sa locație. Aceste referințe de celule folosesc locația relativă a unei adrese de celulă, de aceea sunt numite referință de celulă relativă (fără $). De exemplu, dacă aveți formula „=A1*B1” în celula C1 și copiați această formulă în celula C2, formula se va schimba în „=A2*B2”. Toate metodele pe care le-am discutat mai sus folosesc referințe relative.
Când copiați o formulă cu referințe de celule relative, aceasta schimbă automat referințele, astfel încât formula să se refere la rândurile și coloanele corespunzătoare. Dacă utilizați referințe absolute într-o formulă, atunci aceeași formulă este copiată fără a modifica referințele celulei.
Când puneți un semn dolar ($) în fața literei coloanei și a numărului rândului unei celule (De exemplu, $A$1), aceasta transformă celula într-o celulă absolută. Acum, indiferent unde copiați formula care conține referința absolută a celulei, formula nu va fi niciodată. Dar dacă aveți o referință de celulă relativă sau mixtă într-o formulă, utilizați oricare dintre următoarele metode pentru a copia fără a modifica referințele de celule.
Copiați formula cu referință absolută a celulei utilizând metoda Copy-Paste
Ocazional, poate fi necesar să copiați/aplicați formula exactă în jos pe coloană, fără a modifica referințele celulelor. Dacă doriți să copiați sau să mutați o formulă exactă cu referință absolută, atunci procedați astfel:
Mai întâi, selectați celula cu formula pe care doriți să o copiați. Apoi, faceți clic pe bara de formule, selectați formula folosind mouse-ul și apăsați Ctrl + C să-l copieze. Dacă doriți să mutați formula, apăsați Ctrl + X să o tai. Apoi, apasă pe Esc tasta pentru a părăsi bara de formule.
Alternativ, selectați celula cu formula și apăsați F2 tasta sau faceți dublu clic pe celulă. Aceasta va pune celula selectată în modul de editare. Apoi, selectați formula din celulă și apăsați Ctrl + C pentru a copia formula din celulă ca text.
Apoi, selectați celula de destinație și apăsați Ctrl + V pentru a lipi formula.
Acum formula exactă este copiată în celula de destinație fără nicio modificare a referinței celulei.
Copiați formule cu referințe de celule absolute sau mixte
Dacă doriți să mutați sau să copiați formule exacte fără a modifica referințele de celule, ar trebui să schimbați referințele relative la celule în referințe absolute. De exemplu, adăugarea semnului ($) la referința relativă a celulei (B1) îl face o referință absolută ($B$1), deci rămâne static indiferent de locul în care este copiată sau mutată formula.
Dar uneori, poate fi necesar să utilizați referințe de celule mixte ($B1 sau B$1) adăugând un semn dolar ($) în fața literei coloanei sau a numărului rândului pentru a bloca fie un rând, fie o coloană.
Să explicăm cu un exemplu. Să presupunem că aveți acest tabel care calculează economiile lunare scăzând chiria (B9) din câștiguri (în coloana B) în fiecare lună.
În exemplul de mai jos, formula utilizează o referință de celulă absolută ($ B $ 9) pentru a bloca suma chiriei la celula B9 și o referință relativă a celulei la celula B2, deoarece trebuie ajustată pentru fiecare rând pentru a se potrivi cu fiecare lună. B9 este făcută referință absolută la celulă ($ B$ 9) deoarece doriți să scădeți aceeași sumă de chirie din câștigurile fiecărei luni.
Să presupunem că doriți să mutați soldurile din coloana C în coloana E. Dacă copiați formula (prin metoda obișnuită de copiere/lipire) din celula C2 (=B2-$B$9) se va schimba în =D2-$B$9 când este lipită în celula E2, făcând calculele greșite!
În acest caz, modificați referința relativă de celulă (B2) la o referință de celulă mixtă ($ B2) adăugând semnul „$” în fața literei coloanei a formulei introduse în celula C2.
Și acum, dacă copiați sau mutați formula din celula C2 în E2 sau orice altă celulă și aplicați formula în jos pe coloană, referința coloanei va rămâne aceeași, în timp ce numărul rândului va fi ajustat pentru fiecare celulă.
Copiați lipiți formule Excel fără a modifica referințele folosind Notepad
Puteți vedea fiecare formulă din foaia de calcul Excel utilizând opțiunile Afișare formulă. Pentru a face acest lucru, accesați fila „Formule” și selectați „Afișați formulele”.
Alternativ, puteți intra în modul de vizualizare a formulei apăsând butonul Ctrl + ` scurtătură, care afișează fiecare formulă din foaia de lucru. Puteți găsi tasta de accent grav (`) în colțul din stânga sus al tastaturii pe rândul cu tastele numerice (sub ESC tasta și înaintea tastei numărul 1).
Selectați toate celulele cu formulele pe care doriți să le copiați și apăsați Ctrl + C pentru a le copia, sau Ctrl + X pentru a le tăia. Apoi deschide Notepad și apasă Ctrl + V pentru a lipi formulele în blocnotes.
Apoi, selectați formula și copiați(Ctrl + C) din blocnotes și lipiți-l (Ctrl + V) în celula în care doriți să fie copiată formula exactă. Le puteți copia și lipi unul câte unul sau pe toate simultan.
După ce lipiți formulele, dezactivați modul de vizualizare a formulei apăsând Ctrl + ` sau din nou accesați „Formule” –> „Afișați formule”.
Copiați formulele exacte folosind Excel-ul Căutare și înlocuire
Dacă doriți să copiați o serie de formule exacte, puteți utiliza și instrumentul de căutare și înlocuire din Excel pentru a face acest lucru.
Selectați toate celulele care au formulele pe care doriți să le copiați. Apoi accesați fila „Acasă”, faceți clic pe „Găsiți și selectați” din grupul de editare și selectați opțiunea „Înlocuire” sau pur și simplu apăsați Ctrl + H pentru a deschide caseta de dialog Găsiți și înlocuiți.
În caseta de dialog Găsiți și înlocuiți, introduceți semnul egal (=) în câmpul „Găsiți ce”. În câmpul „Înlocuiește cu”, introduceți un simbol sau un caracter care nu face deja parte din formulele dvs., cum ar fi # sau , etc. Apoi, faceți clic pe butonul „Înlocuiți tot”.
Veți primi o casetă de mesaj promptă care spune „Am făcut 6 înlocuiri” (pentru că am selectat 6 celule cu formule). Apoi faceți clic pe „OK” și „Închidere” pentru a închide ambele casete de dialog. Procedând astfel, toate semnele egale cu (=) sunt înlocuite cu semne hash (#) și transformă formulele în șiruri de text. Acum referințele de celule ale formulelor nu vor fi modificate atunci când sunt copiate.
Acum, puteți selecta aceste celule, apăsați Ctrl + C să le copiați și să le lipiți în celulele de destinație cu Ctrl + V.
În cele din urmă, trebuie să schimbați semnele (#) înapoi în semnele (=). Pentru a face acest lucru, selectați ambele intervale (interval original și copiat) și apăsați Ctrl + H pentru a deschide caseta de dialog Găsiți și înlocuiți. De data aceasta, tastați semnul hash (#) în câmpul „Găsiți ce” și semnul egal cu (=) în câmpul „Înlocuiți cu” și faceți clic pe butonul „Înlocuiește tot”. Faceți clic pe „Închidere” pentru a închide dialogul.
Acum, șirurile de text sunt convertite înapoi în formule și veți obține acest rezultat:
Terminat!