Transcripción del video
Antes de copiar una fórmula en Excel, debes detenerte y preguntar si la fórmula fue construida pensando en que sería copiada a otra celda.
Si no haces una revisión previa de la fórmula es probable que tengas un resultado diferente de lo que esperabas.
Es muy probable que, al momento de copiar una fórmula, las referencias a otras celdas cambien, sin que lo hayas pedido a Excel.
Esto no es una desventaja, sino todo lo contrario, las referencias absolutas y relativas son una de las características más poderosas de la herramienta, pero es importante entenderlas correctamente para hacer buen uso de ellas.
Comenzaré con un ejemplo, donde tengo una lista de productos y sus precios, así como el costo del envío.
Mi objetivo es calcular la suma de ambos para obtener el total, así que activaré la celda D2 y pulsaré el botón Autosuma y se insertará correctamente la función SUMA con la referencia a las celdas de la columna Precio y Envío, así que pulsaré la tecla Entrar y obtendré el Total.
Quiero que observes que la fórmula hace referencia a la celda B2 que contiene el precio y a la celda C2 con el costo de envío.
Ahora voy a copiar la fórmula una celda hacia abajo y el resultado devuelto por la fórmula es correcto y cuando revisamos el código de la fórmula, vemos que Excel ha modificado automáticamente las referencias por nosotros.
La fórmula original tenía la referencia a la celda B2 y C2 pero la copia de la fórmula hace referencia a la celda B3 y a la celda C3.
Si comparas ambas referencias podrás notar que Excel aumentó el número de fila, así que esta es nuestra primera lección.
Cuando copiamos una fórmula hacia abajo, Excel aumentará automáticamente el número de fila de las referencias.
Si en la celda D3 tengo la referencia a las celdas B3 y C3, ¿Qué crees que sucederá si la copio a la celda inferior?
Copiaré la fórmula y al revisar el código podrás notar que el número tres se ha convertido en 4, es decir, Excel ha aumentado el número de la fila en uno.
Esto nos ofrece una gran ventaja porque puedo copiar rápidamente la fórmula hacia abajo y obtendré los resultados sin la necesidad de ingresar con el teclado cada una de las fórmulas.
Así que, no puedes olvidar esta primera conclusión a la que hemos llegado.
Excel aumentará automáticamente las filas de las referencias al copiar la fórmula a una celda inferior.
Por el contrario, si copias una fórmula hacia arriba, entonces Excel disminuirá el número de la fila. ¿Y qué crees que sucederá cuando copiamos una fórmula de manera horizontal?
De nuevo regresaré a la celda D2, donde la fórmula suma los valores de las celdas B2 y C2.
Copiaré la fórmula a la derecha, y Excel me devolverá un resultado, pero no es el mismo que el de la columna Total, así que esto no puede ser una copia exacta de la fórmula.
Al activar la celda E2 observarás que Excel ha modificado las columnas de las referencias.
En la celda D2 teníamos las referencias a las columnas B y C, pero en la nueva fórmula tenemos las referencias a las celdas C y D.
Es muy importante que este concepto quede claro para todos los usuarios de Excel ya que siempre estaremos trabajando con fórmulas y es conveniente conocer el comportamiento de las referencias para no cometer errores.
Este es el comportamiento normal de las fórmulas, pero hay una manera de evitarlo y lo veremos en el siguiente ejemplo.
Activaré la Hoja2 del libro la cual tiene un rango de datos muy similar al ejemplo anterior, pero en esta ocasión tenemos la tasa del impuesto en la celda F2 y debemos incluirla en el cálculo del total.
Activaré la celda D2 y de nuevo pulsaré el comando Autosuma, pero en esta ocasión editaré la fórmula para incluir el cálculo del impuesto, así que haré clic en la barra de fórmulas e ingresaré el asterisco para indicar que haré una multiplicación y en seguida ingresaré un paréntesis y aquí ingresaré el 100% más el valor de la celda F2 y finalmente cierro el paréntesis.
No me detendré mucho en explicar la operación aritmética, pero debes saber que para agregar el 15% a cualquier valor, lo podemos hacer multiplicándolo por el 115%. Si quieres agregar el 20% lo multiplicas por el 120% y así sucesivamente.
Al pulsar la tecla Entrar obtendremos el valor 81 como el resultado de la operación, pero aquí te daré un consejo.
Siempre que trabajamos con porcentajes o con divisiones es importante asegurarnos que el formato de la celda muestra los decimales.
En este caso, pulsaré el botón aumentar decimales dos veces para obtener el resultado exacto de la operación anterior.
La suma del precio y envío es de 70 y el 15% de 70 es 10.5, así que el resultado final es 70 más 10.5 que en total nos da el 80.5 mostrado en la celda.
Pero aquí es donde viene la parte importante que nos ocupa en esta clase.
Al copiar la fórmula hacia abajo obtendremos resultados erróneos y es que a partir de la fila del Producto 2 se ha dejado de sumar el impuesto y solamente se hace la suma del precio y envío.
El Producto 2 tiene un precio de 59 y un envió de 10 y la columna total muestra solamente la suma de ambos valores, que es 69, y no ha incluido la tasa del impuesto.
Y si observamos el código de la fórmula, ya no hace referencia a la celda F2 que tiene la tasa del impuesto, sino que Excel ha aumentado la fila para dejarla como F3.
Si me muevo a la celda de abajo, Excel ha modificado esa misma referencia por la celda F4 y así sucesivamente.
Sin tocar las fórmulas que hemos creado en la columna Total, copiaré el porcentaje de la celda F2 hacia abajo y verás de inmediato que los cálculos se actualizan, lo que quiere decir que cada fórmula intenta tomar la tasa de impuesto de la columna F, pero en su misma fila y no desde la celda F2.
Aunque podría dejar los datos de esta manera, sería muy ineficiente dejar una columna que repite el mismo valor en todas sus filas, así que lo mejor será pedirle a Excel que, al copiar la fórmula, no modifique la referencia a la celda F2 sino que dicha referencia permanezca fija.
Borraré los valores de la columna F y solo dejaré el 15% de la celda F2 y en seguida editaré la fórmula de la celda D2 para fijar la referencia a la celda que contiene la tasa del impuesto.
Tal vez ya lo sepas o tal vez no, pero para fijar una referencia en Excel debemos convertirla en una referencia absoluta y eso lo logramos anteponiendo el símbolo de moneda, o de dólar, antes de la letra de la columna y antes del número de fila.
Así que para nuestra fórmula ingresaré el símbolo de moneda antes de la letra F, y de nueva cuenta el símbolo de moneda antes del número 2.
Una vez que termino de editar la fórmula pulsaré la tecla Entrar y no observarás ningún cambio en el resultado de la celda, y eso está bien, porque recuerda que el problema no estaba con la celda D2 sino con el resto de las celdas después de haber copiado la fórmula.
Haré clic en la esquina inferior derecha de la celda para copiar la fórmula hacia abajo y ahora tendremos el resultado esperado en cada una de las filas.
Voy a seleccionar la celda inferior para que observes la fórmula y verás que la referencia a la celda F2 se ha mantenido.
Me muevo a la celda de abajo y solo cambian las filas de las referencias a las columnas B y C pero la celda F2 se mantiene fija.
Así puedes recorrer todas las fórmulas que acabamos de copiar y podrás comprobar que Excel ha respetado la celda F2 en todas ellas, y esto se debe al símbolo de moneda que hemos ingresado, lo cual ha dejado fija la referencia.
Lo mejor de esta fórmula es que, si algún día cambia la tasa del impuesto y quiero ver los cálculos con el nuevo porcentaje, solo debo editar el valor de la celda F2.
Por ejemplo, ingresaré el 20% y al pulsar Entrar tendremos los nuevos resultados para todos los productos.
Después de terminar con estos ejercicios podemos concluir lo siguiente.
Si nunca vas a copiar una fórmula, entonces no importa el tipo de referencias que utilices ya que eso no afectará en lo absoluto el resultado.
Por el contrario, al copiar una fórmula, debes recordar que no siempre obtendrás una réplica exacta, ya que Excel modificará las referencias para ajustarla a su nueva posición.
Así que antes de copiarla asegurarte que estás utilizando el tipo de referencias adecuadas.
Una vez hecha esta revisión esteramos listos para copiar la fórmula a otras celdas, así que enseguida te mostraré algunas técnicas que tenemos para hacerlo.
Borraré las fórmulas de la columna Total y solo dejaré la primera fórmula. En los ejemplos anteriores me has visto utilizar el controlador de relleno para hacer la copia y me refiero al borde inferior derecho de la celda.
Si haces clic sobre el controlador de relleno y lo arrastras, entonces copiaremos la fórmula.
Con esta técnica yo decido en donde debe detenerse la copia, es decir, si quiero copiar la fórmula hasta el final, entonces debo arrastrar hasta la última celda.
Voy a pulsar el atajo de teclado Ctrl + Z para deshacer esta última acción y mostrarte otra alternativa.
En esas ocasiones en las que tenemos una columna adyacente con datos, una manera rápida de hacer la copia es haciendo doble clic en la esquina inferior derecha de la celda y Excel copiará la fórmula hasta la última fila de los datos.
De nueva cuenta voy a pulsar el atajo Ctrl + Z para deshacer la copia de las fórmulas y en seguida te mostraré que podemos copiar la fórmula con el atajo Ctrl+C y en seguida selecciono el rango completo a donde voy a pegar la fórmula y pulso el atajo Ctrl+V.
Por supuesto que, en lugar de utilizar los atajos de teclado, puedes utilizar el botón copiar y el botón pegar que se encuentran en la pestaña Inicio.
Como puedes ver, el copiar y pegar una fórmula es muy sencillo así que es mucho más relevante el revisar la fórmula antes de hacer la copia para tener la seguridad de que tendremos el resultado esperado.
Con cualquiera de los métodos anteriores, no solamente hemos copiado la fórmula sino también el formato de la celda.
Activaré la Hoja3 del libro y aquí tenemos un rango de datos muy similar a nuestro primer ejemplo, con la diferencia de que hemos resaltado de un color diferente las filas alternas.
Nuestra fórmula ya está lista en la celda D2 y al momento de copiarla hacia abajo puedes ver que no solamente se ha copiado la fórmula sino también el color de relleno de la celda.
Para evitar copiar el formato puedes pulsar el botón de Opciones de Autorrelleno y elegir la opción Rellenar sin formato y eso evitará que se copie el formato y solo habremos copiado la fórmula.
Voy a pulsar el atajo Ctrl +Z para deshacer esta acción porque voy a mostrarte otra manera de hacerlo utilizando el comando Pegado Especial.
Pulsaré el atajo Ctrl + C para copiar la fórmula y en seguida seleccionaré las celdas a donde será pegada y luego abriré el menú del botón Pegar y elegiré la opción Fórmulas y habremos copiado la fórmula a todo el rango respetando el formato de las celdas.
Por supuesto que también tendremos el mismo resultado si ingresamos la fórmula en todas las celdas al mismo tiempo.
Pulsaré de nuevo el atajo Ctrl+Z para deshacer la última acción y borraré la fórmula de la celda C2 y en seguida seleccionaré todo el rango donde ingresaremos la fórmula y para eso pulsaré la tecla Shift y haré clic en la última celda de la columna y se seleccionará todo el rango.
Aunque en este ejemplo puedo pulsar el botón Autosuma o su atajo, te mostraré que esta técnica funciona con cualquier fórmula de Excel al ingresarla con el teclado, así que ingresaré el símbolo igual y en seguida la función SUMA y como argumento colocaré la referencia al rango B2:C2 y cierro los paréntesis.
Finalmente pulsaré el atajo Ctrl + Entrar y habremos ingresado la fórmula en todas las celdas.
Concluimos esta clase diciendo que, antes de copiar una fórmula debes revisarla para tener la seguridad de que se ha utilizado el tipo de referencias adecuado y en seguida podrás utilizar cualquiera de los métodos de copiado que hemos revisado en la clase, ya sea con el controlador de relleno, con los comandos copiar y pegar o ingresando la fórmula en varias celdas al mismo tiempo.
No olvides los conceptos básicos que hemos revisado en esta ocasión en cuanto al comportamiento de las fórmulas de Excel al momento de copiarlas a otras celdas.