Si només necessiteu copiar una fórmula única a Excel 2016, utilitzeu la funció Emplenament automàtic o les ordres Copia i Enganxa. Aquest tipus de còpia de fórmules, tot i que és força habitual, no es pot fer arrossegant i deixant anar.
No oblideu l'opció Totals de l'eina d'anàlisi ràpida. Podeu utilitzar-lo per crear una fila o una columna de totals a la part inferior o dreta d'una taula de dades en un instant. Simplement seleccioneu la taula com a rang de cel·les i feu clic al botó Anàlisi ràpida seguit de Totals a la seva paleta. A continuació, feu clic al botó Suma al principi de la paleta per crear fórmules que sumen les columnes d'una fila nova a la part inferior de la taula i/o al botó Suma al final de la paleta per crear fórmules que sumen les files d'una nova fila. columna a l'extrem dret.
A continuació s'explica com podeu utilitzar l'emplenament automàtic per copiar una fórmula a un rang de cel·les. En aquesta figura, podeu veure el full de treball Mother Goose Enterprises – Vendes 2016 amb totes les empreses, però aquesta vegada amb només un total mensual a la fila 12, que està en procés de copiar-se a través de la cel·la E12.
Còpia d'una fórmula a un interval de cel·les amb Emplenament automàtic.
La figura següent mostra el full de treball després d'arrossegar el mànec d'emplenament a la cel·la B12 per seleccionar l'interval de cel·les C12:E12 (on s'ha de copiar aquesta fórmula).
El full de treball després de copiar la fórmula que suma les vendes mensuals (i trimestrals).
Relativament parlant
La figura mostra el full de treball després de copiar la fórmula d'una cel·la a l'interval de cel·les C12:E12 i la cel·la B12 està activa. Observeu com Excel gestiona la còpia de fórmules. La fórmula original a la cel·la B12 és la següent:
=SUMA(B3:B11)
Quan la fórmula original es copia a la cel·la C12, Excel canvia lleugerament la fórmula perquè sembli així:
=SUMA(C3:C11)
Excel ajusta la referència de la columna, canviant-la de B a C, perquè heu copiat d'esquerra a dreta a través de les files.
Quan copieu una fórmula a un interval de cel·les que s'estén per les files, Excel ajusta els números de fila de les fórmules copiades en lloc de les lletres de la columna per adaptar-se a la posició de cada còpia. Per exemple, la cel·la E3 del full de treball Mother Goose Enterprises - 2016 Sales conté la fórmula següent:
=SUMA(B3:D3)
Quan copieu aquesta fórmula a la cel·la E4, Excel canvia la còpia de la fórmula a la següent:
=SUMA(B4:D4)
Excel ajusta la referència de la fila per mantenir-se actual amb la nova posició de la fila 4. Com que Excel ajusta les referències de cel·la en còpies d'una fórmula en relació a la direcció de la còpia, les referències de cel·la es coneixen com a referències de cel·la relatives .
Algunes coses són absolutes!
Totes les fórmules noves que creeu contenen naturalment referències de cel·les relatives tret que digueu el contrari. Com que la majoria de còpies que feu de fórmules requereixen ajustos de les seves referències de cel·la, poques vegades haureu de pensar en aquesta disposició. Aleshores, de tant en tant, us trobeu amb una excepció que demana limitar quan i com s'ajusten les referències de cel·les a les còpies.
Una de les excepcions més comunes és quan es vol comparar un rang de valors diferents amb un sol valor. Això passa més sovint quan voleu calcular quin percentatge és cada part respecte al total. Per exemple, al full de treball Mother Goose Enterprises - 2016 Sales, trobareu aquesta situació en crear i copiar una fórmula que calcula quin percentatge és cada total mensual (a l'interval de cel·les B14:D14) del total trimestral de la cel·la E12.
Suposem que voleu introduir aquestes fórmules a la fila 14 del full de treball Mother Goose Enterprises - 2016 Sales, començant per la cel·la B14. La fórmula de la cel·la B14 per calcular el percentatge de les vendes de gener al total del primer trimestre és molt senzilla:
=B12/E12
Aquesta fórmula divideix el total de vendes de gener a la cel·la B12 pel total trimestral a E12 (què podria ser més fàcil?). Mireu, però, què passaria si arrossegueu el mànec d'emplenament una cel·la cap a la dreta per copiar aquesta fórmula a la cel·la C14:
=C12/F12
L'ajust de la referència de la primera cel·la de B12 a C12 és just el que va ordenar el metge. Tanmateix, l'ajust de la referència de la segona cel·la d'E12 a F12 és un desastre. No només no calculeu quin percentatge són les vendes de febrer a la cel·la C12 de les vendes del primer trimestre de l'E12, sinó que també acabeu amb un d'aquells horribles #DIV/0! coses d'error a la cel·la C14.
Per evitar que Excel ajusti una referència de cel·la en una fórmula en qualsevol còpia que feu, convertiu la referència de cel·la de relativa a absoluta. Ho feu prement la tecla de funció F4, després de posar Excel en mode d'edició (F2). Excel indica que feu que la referència de la cel·la sigui absoluta col·locant signes de dòlar davant de la lletra de la columna i el número de fila. Per exemple, en aquesta figura, la cel·la B14 conté la fórmula correcta per copiar a l'interval de cel·les C14:D14:
Copia de la fórmula per calcular la proporció de vendes mensuals a trimestrals amb una referència de cel·la absoluta.
=B12/$E$12
Mireu el full de treball després de copiar aquesta fórmula a l'interval C14:D14 amb el mànec d'ompliment i la cel·la C14 està seleccionada (vegeu la figura següent). Tingueu en compte que la barra de Fórmules mostra que aquesta cel·la conté la fórmula següent:
El full de treball després de copiar la fórmula amb la referència de cel·la absoluta.
=C12/$E$12
Com que E12 es va canviar a $E$12 a la fórmula original, totes les còpies tenen aquesta mateixa referència absoluta (no canviant).
Si enganxeu i copieu una fórmula on una o més de les referències de cel·les haurien d'haver estat absolutes però les heu deixat relatives, editeu la fórmula original de la següent manera:
Feu doble clic a la cel·la amb la fórmula o premeu F2 per editar-la.
Col·loqueu el punt d'inserció en algun lloc de la referència que voleu convertir en absoluta.
Premeu F4.
Quan acabeu d'editar, feu clic al botó Intro a la barra de fórmules i després copieu la fórmula a l'interval de cel·les desordenat amb el mànec d'emplenament.
Assegureu-vos de prémer F4 només una vegada per canviar una referència de cel·la a absolutament absoluta, tal com he descrit anteriorment. Si premeu la tecla de funció F4 una segona vegada, acabeu amb una anomenada referència mixta, on només la part de fila és absoluta i la part de columna és relativa (com a E$12). Si torneu a prémer F4, Excel apareix un altre tipus de referència mixta, on la part de la columna és absoluta i la part de la fila és relativa (com a $E12). Si continueu i torneu a prémer F4, Excel torna a canviar la referència de la cel·la a completament relativa (com a E12).
Després de tornar on vau començar, podeu continuar utilitzant F4 per tornar a fer un cicle a través d'aquest mateix conjunt de canvis de referència de cel·la.
Si utilitzeu Excel 2016 en un dispositiu sense accés a un teclat físic amb tecles de funció (com ara una tauleta de pantalla tàctil), l'única manera de convertir les adreces de cel·les de les vostres fórmules de relatives a absolutes o d'alguna forma d'adreça mixta és obrir el teclat tàctil i utilitzeu-lo afegiu els signes del dòlar abans de la lletra de la columna i/o el número de fila a l'adreça de cel·la adequada a la barra de fórmules.