Cómo copiar fórmulas con Autocompletar en Excel 2016

Si solo necesita copiar una sola fórmula en Excel 2016, use la función Autocompletar o los comandos Copiar y Pegar. Este tipo de copia de fórmulas, aunque es bastante común, no se puede hacer con arrastrar y soltar.

No olvide la opción Totales en la herramienta Análisis rápido. Puede usarlo para crear una fila o una columna de totales en el borde inferior o derecho de una tabla de datos en un instante. Simplemente seleccione la tabla como un rango de celdas y haga clic en el botón Análisis rápido seguido de Totales en su paleta. Luego haga clic en el botón Suma al principio de la paleta para crear fórmulas que sumen las columnas en una nueva fila en la parte inferior de la tabla y / o el botón Suma al final de la paleta para crear fórmulas que sumen las filas en una nueva columna en el extremo derecho.

A continuación, le mostramos cómo puede usar Autocompletar para copiar una fórmula en un rango de celdas. En esta figura, puede ver la hoja de trabajo Mother Goose Enterprises - Ventas 2016 con todas las empresas, pero esta vez con solo un total mensual en la fila 12, que está en proceso de copiarse a través de la celda E12.

Copiar una fórmula a un rango de celdas con Autocompletar.

La siguiente figura muestra la hoja de trabajo después de arrastrar el controlador de relleno en la celda B12 para seleccionar el rango de celdas C12: E12 (donde se debe copiar esta fórmula).

La hoja de trabajo después de copiar la fórmula que totaliza las ventas mensuales (y trimestrales).

Hablando relativamente

La figura muestra la hoja de trabajo después de que la fórmula en una celda se copia en el rango de celdas C12: E12 y la celda B12 está activa. Observe cómo Excel maneja la copia de fórmulas. La fórmula original en la celda B12 es la siguiente:

= SUMA (B3: B11)

Cuando la fórmula original se copia en la celda C12, Excel cambia la fórmula ligeramente para que se vea así:

= SUMA (C3: C11)

Excel ajusta la referencia de la columna, cambiándola de B a C, porque copió de izquierda a derecha en las filas.

Cuando copia una fórmula en un rango de celdas que se extiende por las filas, Excel ajusta los números de fila en las fórmulas copiadas en lugar de las letras de las columnas para adaptarse a la posición de cada copia. Por ejemplo, la celda E3 de la hoja de trabajo Mother Goose Enterprises - Ventas de 2016 contiene la siguiente fórmula:

= SUMA (B3: D3)

Cuando copia esta fórmula en la celda E4, Excel cambia la copia de la fórmula a lo siguiente:

= SUMA (B4: D4)

Excel ajusta la referencia de fila para mantenerse actualizada con la nueva posición de la fila 4. Debido a que Excel ajusta las referencias de celda en copias de una fórmula en relación con la dirección de la copia, las referencias de celda se conocen como referencias de celda relativas .

¡Algunas cosas son absolutas!

Todas las fórmulas nuevas que cree contienen naturalmente referencias de celda relativas a menos que diga lo contrario. Debido a que la mayoría de las copias que realiza de fórmulas requieren ajustes de sus referencias de celda, rara vez tiene que pensar dos veces en esta disposición. Luego, de vez en cuando, se encuentra con una excepción que exige limitar cuándo y cómo se ajustan las referencias de celda en copias.

Una de las excepciones más comunes es cuando desea comparar un rango de valores diferentes con un solo valor. Esto sucede con mayor frecuencia cuando desea calcular qué porcentaje es cada parte del total. Por ejemplo, en la hoja de trabajo de Mother Goose Enterprises - Ventas de 2016, se encuentra con esta situación al crear y copiar una fórmula que calcula qué porcentaje de cada total mensual (en el rango de celdas B14: D14) es del total trimestral en la celda E12.

Suponga que desea ingresar estas fórmulas en la fila 14 de la hoja de trabajo Mother Goose Enterprises - Ventas 2016, comenzando en la celda B14. La fórmula en la celda B14 para calcular el porcentaje de las ventas de enero al total del primer trimestre es muy sencilla:

= B12 / E12

Esta fórmula divide el total de ventas de enero en la celda B12 por el total trimestral en E12 (¿qué podría ser más fácil?). Sin embargo, observe lo que sucedería si arrastrara el controlador de relleno una celda hacia la derecha para copiar esta fórmula en la celda C14:

= C12 / F12

El ajuste de la primera referencia de celda de B12 a C12 es justo lo que recetó el médico. Sin embargo, el ajuste de la segunda referencia de celda de E12 a F12 es un desastre. No solo no calcula qué porcentaje son las ventas de febrero en la celda C12 de las ventas del primer trimestre en E12, ¡sino que también termina con uno de esos horribles # DIV / 0! cosas de error en la celda C14.

Para evitar que Excel ajuste una referencia de celda en una fórmula en cualquier copia que realice, convierta la referencia de celda de relativa a absoluta. Para ello, presione la tecla de función F4, después de poner Excel en el modo de edición (F2). Excel indica que hace que la referencia de celda sea absoluta colocando signos de dólar delante de la letra de la columna y el número de fila. Por ejemplo, en esta figura, la celda B14 contiene la fórmula correcta para copiar en el rango de celdas C14: D14:

Copia de la fórmula para calcular la relación entre las ventas mensuales y las trimestrales con una referencia de celda absoluta.

= B12 / $ E $ 12

Mire la hoja de trabajo después de que esta fórmula se haya copiado en el rango C14: D14 con el controlador de relleno y la celda C14 está seleccionada (consulte la siguiente figura). Observe que la barra de fórmulas muestra que esta celda contiene la siguiente fórmula:

La hoja de trabajo después de copiar la fórmula con la referencia de celda absoluta.

= C12 / $ E $ 12

Debido a que E12 se cambió a $ E $ 12 en la fórmula original, todas las copias tienen la misma referencia absoluta (no cambiante).

Si comete errores y copia una fórmula en la que una o más de las referencias de celda deberían haber sido absolutas, pero las dejó todas relativas, edite la fórmula original de la siguiente manera:

Haga doble clic en la celda con la fórmula o presione F2 para editarla.

Coloque el punto de inserción en algún lugar de la referencia que desee convertir en absoluto.

Presione F4.

Cuando termine de editar, haga clic en el botón Entrar en la barra de fórmulas y luego copie la fórmula en el rango de celdas desordenado con el controlador de relleno.

Asegúrese de presionar F4 solo una vez para cambiar una referencia de celda a completamente absoluta como describí anteriormente. Si presiona la tecla de función F4 por segunda vez, terminará con una denominada referencia mixta, donde solo la parte de la fila es absoluta y la parte de la columna es relativa (como en E $ 12). Si luego presiona F4 nuevamente, Excel presenta otro tipo de referencia mixta, donde la parte de la columna es absoluta y la parte de la fila es relativa (como en $ E12). Si continúa y presiona F4 una vez más, Excel vuelve a cambiar la referencia de celda a completamente relativa (como en E12).

Una vez que regrese a donde comenzó, puede continuar usando F4 para recorrer este mismo conjunto de cambios de referencia de celda nuevamente.

Si está utilizando Excel 2016 en un dispositivo sin acceso a un teclado físico con teclas de función (como una tableta con pantalla táctil), la única forma de convertir direcciones de celda en sus fórmulas de relativa a absoluta o alguna forma de dirección mixta es abrir el teclado táctil y úselo para agregar los signos de dólar antes de la letra de la columna y / o el número de fila en la dirección de celda correspondiente en la barra de fórmulas.