Apreneu a copiar fórmules a diverses cel·les, una columna cap avall, a cel·les no adjacents, copiar fórmules amb referències de cel·les absolutes o mixtes, etc.
Copiar fórmules és una de les tasques més comunes i fàcils que feu en un full de càlcul típic que es basa principalment en fórmules. En lloc d'escriure la mateixa fórmula una i altra vegada a Excel, podeu copiar i enganxar fàcilment una fórmula d'una cel·la a diverses cel·les.
Després d'escriure una fórmula a Excel, podeu utilitzar les ordres de copiar i enganxar a diverses cel·les, a diverses cel·les no adjacents o a columnes senceres. Si no ho feu bé, acabareu amb aquests horribles errors # REF i /DIV0. En aquest article, us mostrarem els diferents mètodes que podeu utilitzar per copiar fórmules a Excel.
Com copiar i enganxar fórmules a Excel
Microsoft Excel ofereix diverses maneres de copiar fórmules amb referències de cel·les relatives, referències de cel·les absolutes o referències mixtes.
- Copia la fórmula d'una cel·la a una altra
- Copieu la fórmula una cel·la a diverses cel·les
- S'està copiant la fórmula a tota la columna
- Còpia de fórmula sense formatar
- Copieu fórmules a cel·les no adjacents
- Copieu fórmules sense canviar les referències de cel·les
Com copiar una fórmula d'una cel·la a una altra a Excel
De vegades, és possible que vulgueu copiar una fórmula d'una cel·la a una altra a Excel per evitar tornar a escriure la fórmula sencera i estalviar temps mentre ho feu.
Suposem que tenim aquesta taula:
Hi ha pocs mètodes per copiar la fórmula d'una cel·la a una altra.
Primer, seleccioneu la cel·la amb la fórmula i feu clic amb el botó dret i, al menú contextual, seleccioneu "Copia" per copiar la fórmula. O podeu utilitzar l'opció "Copia" a la secció "Porta-retalls" de la pestanya "Inici".
Però també copieu fórmules simplement prement la drecera del teclat Ctrl + C. Aquest és un mètode més eficient i que estalvia temps.
Després anem a la cel·la que volem enganxar-la premeu la drecera Ctrl + V per enganxar la fórmula. O feu clic amb el botó dret a la cel·la que voleu enganxar i feu clic a les opcions a "Opcions d'enganxar": l'opció simple "Enganxa (P)" o enganxeu com a opció "Fórmula (F)".
Alternativament, també podeu fer clic a "Enganxa especial" a sota de les sis icones d'enganxar per obrir el quadre de diàleg "Enganxa especial". Aquí teniu diverses opcions, incloses les sis opcions d'enganxament del menú contextual. Seleccioneu "Tots" o "Fórmules" a la secció Enganxa i feu clic a "D'acord".
Ara la cel·la amb la fórmula enganxada hauria de tenir les mateixes fórmules (com la de la cel·la copiada) però amb les diferents referències de cel·la. L'adreça de la cel·la s'ajusta automàticament per excel perquè coincideixi amb el número de fila de la cel·la enganxada.
Copia la fórmula d'una cel·la a diverses cel·les
La mateixa operació d'enganxament funciona igual si seleccionem diverses cel·les o un rang de cel·les.
Seleccioneu la cel·la amb la fórmula i premeu Ctrl + C per copiar la fórmula. A continuació, seleccioneu totes les cel·les on voleu enganxar la fórmula i premeu Ctrl + V per enganxar la fórmula o utilitzar un dels mètodes d'enganxar anteriors per enganxar la fórmula (com vam fer per a la cel·la única).
Copieu la fórmula a una columna o fila sencera
A Excel, podeu copiar ràpidament una fórmula a una columna sencera o una fila.
Per copiar una fórmula a una columna o una fila, primer, introduïu una fórmula a una cel·la. A continuació, seleccioneu la cel·la de la fórmula (D1) i passeu el cursor per sobre d'un petit quadrat verd a la cantonada inferior dreta de la cel·la. A mesura que passeu el cursor, el cursor canviarà a un signe més negre (+), que s'anomena mànec d'ompliment. Feu clic i manteniu premuda aquesta nansa d'ompliment i arrossegueu-la en la direcció que vulgueu (columna o fila) per sobre de les cel·les per copiar la fórmula.
Quan copieu una fórmula a un rang de cel·les, les referències de cel·les de la fórmula s'ajustaran automàticament en funció de la ubicació relativa de les files i columnes i la fórmula realitzarà càlculs basats en els valors d'aquestes referències de cel·les (vegeu a continuació).
A l'exemple anterior, quan la fórmula a D1 (=A1*B1)/2) es copia a la cel·la D2, la referència relativa canvia en funció de la seva ubicació (=A2*B2)/2) i així successivament.
De la mateixa manera, podeu arrossegar la fórmula a les cel·les adjacents a l'esquerra, a la dreta o cap amunt.
Una altra manera de copiar la fórmula a una columna sencera és fent doble clic al controlador d'emplenament en lloc d'arrossegar-lo. Quan feu doble clic al mànec d'emplenament, s'aplica immediatament la fórmula en la mesura que hi hagi dades a la cel·la adjacent.
Copieu una fórmula a un rang sense copiar el format
Quan copieu una fórmula a un rang de cel·les amb el mànec d'emplenament, també copia el format de la cel·la font, com ara el color de la font o el color de fons, la moneda, el percentatge, el temps, etc. (com es mostra a continuació).
Per evitar copiar el format de la cel·la, arrossegueu el mànec d'emplenament i feu clic a "Opcions d'emplenament automàtic" a l'extrem inferior dret de l'última cel·la. A continuació, al menú desplegable, seleccioneu "Emplenar sense format".
El resultat:
Copieu una fórmula d'Excel només amb format numèric
Si voleu copiar la fórmula només amb la fórmula i el format, com ara el format de percentatge, els punts decimals, etc.
Copieu la fórmula i seleccioneu totes les cel·les a les quals voleu copiar la fórmula. A la pestanya "Inici", feu clic a la fletxa que hi ha a sota del botó "Enganxa" de la cinta. A continuació, feu clic a la icona "Format de fórmules i números" (la icona amb % fx) del menú desplegable per enganxar només la fórmula i el format del número.
Aquesta opció només copia el format de fórmules i números, però ignora tots els altres formats de cel·les com el color de fons, el color de la lletra, etc.
Copieu una fórmula a cel·les no adjacents/no contigües
Si voleu copiar una fórmula a cel·les no adjacents o intervals no adjacents, podeu fer-ho amb l'ajuda de Ctrl clau.
Seleccioneu la cel·la amb la fórmula i premeu Ctrl + C per copiar-lo. A continuació, seleccioneu cel·les/intervals no adjacents mentre manteniu premut el botó Ctrl clau. A continuació, premeu Ctrl + V per enganxar la fórmula i premeu Entra completar.
Còpia de fórmules sense canviar les referències de cel·les a Excel
Quan una fórmula es copia a una altra cel·la, Excel canvia automàticament les referències de cel·la perquè coincideixin amb la seva nova ubicació. Aquestes referències de cel·la utilitzen la ubicació relativa d'una adreça de cel·la, per tant s'anomenen referència de cel·la relativa (sense $). Per exemple, si teniu la fórmula "=A1*B1" a la cel·la C1 i copieu aquesta fórmula a la cel·la C2, la fórmula canviarà a "=A2*B2". Tots els mètodes que hem comentat anteriorment utilitzen referències relatives.
Quan copieu una fórmula amb referències de cel·les relatives, canvia automàticament les referències perquè la fórmula faci referència a les files i columnes corresponents. Si utilitzeu referències absolutes en una fórmula, la mateixa fórmula es copia sense canviar les referències de cel·la.
Quan poseu un signe de dòlar ($) davant la lletra de la columna i el número de fila d'una cel·la (per exemple, $A$1), la cel·la es converteix en una cel·la absoluta. Ara no importa on copieu la fórmula que conté la referència de cel·la absoluta, la fórmula mai no ho farà. Però si teniu una referència de cel·la relativa o mixta en una fórmula, utilitzeu qualsevol d'aquests mètodes següents per copiar sense canviar les referències de cel·la.
Copieu la fórmula amb la referència de cel·les absoluta mitjançant el mètode Copiar-Enganxar
De tant en tant, és possible que hàgiu de copiar/aplicar la fórmula exacta a la columna, sense canviar les referències de cel·la. Si voleu copiar o moure una fórmula exacta amb referència absoluta, feu això:
Primer, seleccioneu la cel·la amb la fórmula que voleu copiar. A continuació, feu clic a la barra de fórmules, seleccioneu la fórmula amb el ratolí i premeu Ctrl + C per copiar-lo. Si voleu moure la fórmula, premeu Ctrl + X per tallar-lo. A continuació, premeu Esc tecla per sortir de la barra de fórmules.
Alternativament, seleccioneu la cel·la amb la fórmula i premeu F2 o feu doble clic a la cel·la. Això posarà la cel·la seleccionada en mode d'edició. A continuació, seleccioneu la fórmula a la cel·la i premeu Ctrl + C per copiar la fórmula a la cel·la com a text.
A continuació, seleccioneu la cel·la de destinació i premeu Ctrl + V per enganxar la fórmula.
Ara la fórmula exacta es copia a la cel·la de destinació sense cap canvi de referència de cel·la.
Copieu fórmules amb referències de cel·les absolutes o mixtes
Si voleu moure o copiar fórmules exactes sense canviar les referències de cel·la, hauríeu de canviar les referències relatives de cel·la a referències absolutes. Per exemple, afegir el signe ($) a la referència relativa de la cel·la (B1) la converteix en una referència absoluta ($B$1), de manera que roman estàtica sense importar on es copie o es mogui la fórmula.
Però de vegades, és possible que hàgiu d'utilitzar referències de cel·les mixtes ($B1 o B$1) afegint un signe de dòlar ($) davant de la lletra de la columna o del número de fila per bloquejar una fila o una columna al seu lloc.
Expliquem-ho amb un exemple. Suposem que teniu aquesta taula que calcula l'estalvi mensual restant el lloguer (B9) dels ingressos (a la columna B) cada mes.
A l'exemple següent, la fórmula utilitza una referència de cel·la absoluta ($ B$ 9) per bloquejar l'import del lloguer a la cel·la B9 i una referència de cel·la relativa a la cel·la B2 perquè s'ha d'ajustar perquè cada fila coincideixi amb cada mes. B9 es fa referència de cel·la absoluta ($ B$ 9) perquè voleu restar la mateixa quantitat de lloguer dels guanys de cada mes.
Suposem que voleu moure els saldos de la columna C a la columna E. Si copieu la fórmula (amb el mètode habitual de copiar/enganxar) de la cel·la C2 (=B2-$B$9) canviarà a =D2-$B$9 quan s'enganxi a la cel·la E2, fent els vostres càlculs malament!
En aquest cas, canvieu la referència de cel·la relativa (B2) a una referència de cel·la mixta ($B2) afegint el signe "$" davant de la lletra de columna de la fórmula introduïda a la cel·la C2.
I ara, si copieu o moveu la fórmula de la cel·la C2 a la E2, o qualsevol altra cel·la, i apliqueu la fórmula a la columna, la referència de la columna romandrà igual mentre el número de fila s'ajustarà per a cada cel·la.
Copieu i enganxeu fórmules d'Excel sense canviar les referències mitjançant el Bloc de notes
Podeu veure totes les fórmules del vostre full de càlcul d'Excel utilitzant Mostra les opcions de fórmula. Per fer-ho, aneu a la pestanya "Fórmules" i seleccioneu "Mostrar fórmules".
Alternativament, podeu entrar al mode de visualització de la fórmula prement Ctrl + ` drecera, que mostra totes les fórmules del vostre full de treball. Pots trobar la tecla d'accent greu (`) a l'extrem superior esquerre del teu teclat a la fila amb les tecles numèriques (a sota de ESC tecla i abans de la tecla número 1).
Seleccioneu totes les cel·les amb les fórmules que voleu copiar i premeu Ctrl + C per copiar-los, o Ctrl + X per tallar-los. A continuació, obriu el Bloc de notes i premeu Ctrl + V per enganxar les fórmules al bloc de notes.
A continuació, seleccioneu la fórmula i copieu (Ctrl + C) des del bloc de notes i enganxeu-lo (Ctrl + V) a la cel·la on voleu copiar la fórmula exacta. Podeu copiar-los i enganxar-los un per un o tots alhora.
Després d'enganxar les fórmules, desactiveu el mode de visualització de fórmules prement Ctrl + ` o de nou aneu a ‘Fórmules’ –> ‘Mostrar fórmules’.
Copieu les fórmules exactes amb l'opció Cerca i substituïu d'Excel
Si voleu copiar una sèrie de fórmules exactes, també podeu utilitzar l'eina Cerca i substituïu d'Excel per fer-ho.
Seleccioneu totes les cel·les que tinguin les fórmules que voleu copiar. A continuació, aneu a la pestanya "Inici", feu clic a "Cerca i seleccioneu" al grup d'edició i seleccioneu l'opció "Reemplaça", o simplement premeu Ctrl + H per obrir el quadre de diàleg Cerca i substitueix.
Al quadre de diàleg Cerca i substitueix, introduïu el signe igual (=) al camp "Troba què". Al camp "Substitueix per", introduïu un símbol o caràcter que encara no sigui part de les vostres fórmules, com ara #, o , etc. A continuació, feu clic al botó "Substitueix-ho tot".
Rebràs un quadre de missatge que diu "Hem fet 6 substitucions" (perquè hem seleccionat 6 cel·les amb fórmules). A continuació, feu clic a "D'acord" i "Tanca" per tancar els dos diàlegs. En fer-ho, es substitueixen tots els signes iguals a (=) per signes hash (#) i es converteixen les fórmules en cadenes de text. Ara les referències de cel·les de les fórmules no es canviaran quan es copien.
Ara, podeu seleccionar aquestes cel·les, premeu Ctrl + C per copiar-los i enganxar-los a les cel·les de destinació amb Ctrl + V.
Finalment, heu de tornar a canviar els signes (#) per signes (=). Per fer-ho, seleccioneu els dos intervals (interval original i copiat) i premeu Ctrl + H per obrir el quadre de diàleg Cerca i substitueix. Aquesta vegada, escriviu el signe hash (#) al camp "Troba què" i el signe igual a (=) al camp "Substitueix per" i feu clic al botó "Substitueix-ho tot". Feu clic a "Tancar" per tancar el diàleg.
Ara, les cadenes de text es tornen a convertir a les fórmules i obtindreu aquest resultat:
Fet!