Referencias relativas y absolutas en Excel 2010

Uno de los grandes beneficios de Excel es que podemos copiar fácilmente las fórmulas que hayamos creado y aplicarlas en otras celdas para obtener resultados similares.

Más información en: Referencias relativas y absolutas en Excel

En esta hoja tengo una lista de productos con sus respectivos precios del año pasado y necesito  aplicar un incremento del 10% a cada producto para obtener los precios para el nuevo año. La fórmula que aplicaré en la celda C2 es muy sencilla =B2*1.1 y al pulsar la tecla Entrar obtendré el total de $44 que ya incluye un incremento del 10% en el precio. Esta fórmula también la podríamos escribir como =B2*110% y obtener el mismo resultado.

Ahora solo me falta calcular los nuevos precios para el resto de los productos. Una opción es  introducir con el teclado cada fórmula para obtener el resultado deseado pero tendríamos que repetir esta acción varias veces. Esto no es necesario, ya que Excel me permite copiar una fórmula a otra celda adaptando automáticamente las referencias de la fórmula original. Vamos a comprobar lo que te acabo de decir. Copiamos la celda C2 pulsando el botón copiar dela ficha Inicio y posteriormente seleccionamos la celda C3 y pulsamos el botón Pegar.

Observa que Excel muestra el precio correcto para el segundo producto con un incremento del 10% y no tuvimos que editar la fórmula en lo absoluto. ¿Cómo ha sucedido esto? Cuando copiamos y pegamos una fórmula de Excel que contiene referencias de celda, siempre se realizará un ajuste para adaptar dichas referencias de celda a la nueva ubicación.

Déjame explicar un poco más sobre esto. Cuando hablamos de fórmulas en Excel, la celda que contiene la fórmula se convierte en la ubicación inicial a partir de la cual Excel se desplazará un determinado número de celdas hasta llegar al valor que debe incluir en la fórmula. En nuestro ejemplo, Excel sabe que debe moverse una posición a la izquierda para tomar dicho valor y multiplicarlo por 110%. De esta manera, sin importar a donde copiemos la fórmula, siempre se tomará la celda que se encuentra una posición a la izquierda de la celda actual y se multiplicará por 110%.

Hagamos otra prueba. Copiaré la fórmula de la celda C2 y la pegará en la celda C6. El resultado es correcto porque Excel ha tomado la celda que se encuentra una posición a la izquierda de la celda C6 y ha multiplicado su valor por 110%. Este es el comportamiento predeterminado de Excel y siempre que copiemos una fórmula que contiene referencias de celda, éstas serán modificadas automáticamente por Excel para adaptarse a la nueva ubicación de la fórmula. Es por esta razón que a este tipo de referencias de celda las conocemos como referencias relativas porque las referencias cambian en relación a la celda que contiene la fórmula.

Ahora te mostraré que existen tres métodos para copiar la fórmula de una celda a otra. El primer método es utilizando los comandos copiar y pegar. Comenzaré por seleccionar la fórmula original de la celda C2 y pulsando el botón copiar. En seguida seleccionaré todas las celdas a donde deseo copiar la fórmula y terminaré pulsando el botón Pegar. Observa cómo Excel ha modificado automáticamente todas las referencias de las fórmulas en relación con la celda que las contiene, de manera que obtenemos el resultado correcto para todos los productos.

Ahora borraré estos resultados para mostrarte otro método de copiar y pegar fórmulas. El segundo método consiste en colocar el puntero del ratón en la esquina inferior derecha de la celda que contiene la fórmula original y cuando el puntero cambia de forma hacemos clic y arrastramos hacia abajo para seleccionar las celdas a donde se copiará la fórmula y finalmente soltamos el botón del ratón. Observa que los resultados son correctos y Excel ha modificado las referencias de celda de manera adecuada.

Borraré de nuevo los resultados para mostrarte el último método que es precisamente uno de los más utilizados debido a su rapidez. Solo es necesario seleccionar la celda con la fórmula original y ubicarse en la esquina inferior derecha y hacer doble clic para que Excel copie automáticamente la fórmula hacia abajo. De nueva cuenta puedes notar que Excel modifica correctamente las referencias de celda en cada una de las fórmulas.

Para dejar en claro el tema de las referencias relativas en Excel, hagamos un último ejemplo. En la celda D2 colocaré una fórmula que calculará la diferencia entre el precio original y el nuevo precio. La fórmula que colocaré será =C2-B2 y que me da como resultado el valor 4. Recuerda que esta fórmula puede ser interpretada de la siguiente manera: toma el valor de la celda que se encuentra una posición a la izquierda y resta el valor de la celda que se encuentra dos posiciones a la izquierda.

Al momento de copiar la fórmula hacia abajo, Excel sabrá exactamente lo que debe hacer con las referencias de celda y las modificará para obtener el resultado correcto para cada producto. Las referencias relativas nos ahorran mucho trabajo al momento de copiar nuestras fórmulas a otras celdas, sin embargo no siempre es el comportamiento que deseamos tener en nuestras hojas de Excel.

Considera este segundo ejemplo. En otra hoja de Excel tengo una tabla muy similar a la anterior. Pero ahora, en lugar de establecer el porcentaje de incremento dentro de la fórmula deseo colocarlo en la celda G1 de manera que pueda modificarlo en cualquier momento y ver el resultado de manera inmediata.

La fórmula que colocaré en la celda C2 es la siguiente: =B2*(100% + G1) y al pulsar la tecla Entrar obtendré el resultado correcto. Esta fórmula calcula el nuevo precio considerando el incremento establecido en la celda G1. Ahora observa con detenimiento lo que sucede al copiar la fórmula hacia abajo. Excel coloca el mismo valor que la columna B para el resto de las celdas pero ese no es el resultado que esperamos. Analicemos la razón de este comportamiento de Excel.

La fórmula en C2 indica a Excel que tome el valor de la celda que está una posición a la izquierda y lo multiplique por el valor de la celda que se encuentra 4 posiciones a la derecha y una posición hacia arriba. Ahora revisemos la fórmula que ha colocado Excel en la celda C3. Y nos encontramos con la celda B3 que efectivamente está una posición a la izquierda, y tenemos la celda G2 que se encuentra 4 posiciones a la derecha y una posición hacia arriba. De esta manera Excel ha respetado el comportamiento de la fórmula original y podemos constatar que hace lo mismo para el resto de las celdas: B4 – G3, B5 – G4, B6 – G5, etc.

¿Qué es lo que necesitamos hacer para que nuestro ejemplo funcione correctamente? Por un lado necesitamos que Excel modifique automáticamente la referencia de la celda que contiene el precio del año anterior, pero por otro lado necesitamos que mantenga fija la referencia a la celda G1 para que todos los precios reflejen el incremento del 10%.

Es aquí donde haremos uso de otro tipo de referencias en Excel que son conocidas como referencias absolutas. Una referencia absoluta es aquella que permanecerá fija y Excel no la modificará aunque copiemos la fórmula a otras celdas. La manera en que indicamos a Excel que deseamos convertir una referencia relativa en absoluta es utilizando el símbolo de moneda.

De esta manera nuestra fórmula original de la celda C2 será la siguiente: =B2*(100% + $G$1). Observa que he colocado un símbolo de moneda que precede tanto a la columna como a la fila de la referencia a la celda G1. Nuestra fórmula ahora tiene una referencia relativa a la celda B2, y una referencia absoluta a la celda G1. Así que, ahora que tenemos lista nuestra fórmula voy a copiarla al resto de las celdas y en esta ocasión obtendré el resultado esperado.

En cada fórmula Excel ha modificado la referencia relativa pero ha dejado intacta la referencia absoluta. De esta manera todas las fórmulas multiplican el valor que se encuentra en la celda de la izquierda por el valor de la celda G1. Al cambiar el valor de esta celda por 20% veremos reflejado automáticamente el incremento para todos los productos. Es así como las referencias absolutas nos ayudan a indicar a Excel que utilice siempre la misma referencia aun cuando copiemos las fórmulas a otras celdas.

Para finalizar con este ejemplo, en la celda D2 colocaré la fórmula para calcular la diferencia de precios: =C2-B2 y copiaré la fórmula hacia abajo para obtener el monto de diferencia en el precio para cada producto. Si vuelvo a cambiar el valor del incremento a 10%, inmediatamente todas las celdas reflejarán los nuevos valores. Espero que con este ejemplo tengas claro el beneficio de utilizar referencias absolutas en Excel.

Finalmente haremos un ejercicio que nos ayudará a dejar en claro las diferencias entre los diferentes tipos de referencias en Excel. En la primera columna de esta tabla he colocado los valores numéricos del  10 al 50. En la celda B2 crearé una referencia hacia A2 y al pulsar entrar ambas celdas tendrán el mismo valor. Ya que esta es una referencia relativa, al copiarla hacia abajo Excel ajustará el número de fila y terminaré con dos columnas de valores idénticos. La única diferencia entre una fórmula y la otra es que Excel ha modificado la fila.

Si copio una referencia relativa hacia la derecha, Excel modificará la columna de la fórmula. Por ejemplo, al copiar la fórmula de la celda B6 hacia la derecha, se copiará el valor de la celda a su izquierda y la fórmula reflejará un cambio en la columna de la referencia. Borraré este último ejemplo para mostrar la diferencia que existe con las referencias absolutas.

En la celda C2 colocará la fórmula =A2 y para convertirla en absoluta puedo introducir manualmente el símbolo de moneda, pero te mostraré un atajo de teclado. Para crear una referencia absoluta será suficiente con colocar el cursor sobre la referencia y pulsar una vez la tecla F4.

Al copiar esta fórmula hacia abajo, los valores se repiten porque la fórmula no cambia en lo absoluto. Observa que todas las celdas tienen la misma fórmula. Y lo mismo sucede si copio la fórmula de la celda C6 hacia la derecha. Todas las celdas mantienen la misma referencia a la celda A2. Borraré las últimas celdas insertadas para mostrarte una variación que existe de las referencias absolutas. La primera variación es conocida como fila absoluta.

En la celda D2 colocaré de nuevo la fórmula =A2 y pulsaré la tecla F4 una vez y se convertirá en absoluta, pero si vuelvo a pulsar la tecla F4, el símbolo de moneda solamente aparecerá al lado del número de fila. Esto quiere decir que al copiar la fórmula a otras celdas Excel podrá modificar la columna, pero la fila permanecerá estática.  Al copiar la fórmula hacia abajo obtengo el mismo resultado que si utilizara una referencia absoluta. Pero ahora observa que al copiar la fórmula de la celda D6 a la derecha, Excel dejará fijo el número de fila pero ajustará la columna en cada fórmula.

La segunda variación de una referencia absoluta es conocida como columna absoluta. En la celda E2 introduciré la fórmula =A2 pero ahora pulsaré la tecla F4 3 veces y podrás observar que el símbolo de moneda aparece ahora solo al lado de la columna. Esto indica a Excel que podrá ajustar la fila pero no la columna.

Al copiar la fórmula hacia abajo veremos que Excel ajusta el número de fila dentro de las fórmulas. Pero si copio la fórmula de la celda E6 hacia la derecha podrás observar que la columna permanece fija en todas las fórmulas.

Es probable que en este momento sea difícil encontrar alguna aplicación directa de las últimas dos variaciones que revisamos, pero hay casos específicos donde pueden ser de utilidad. Cuando hagamos un uso extensivo de las fórmulas y funciones de Excel podrás ver claramente esos beneficios. Lo que no debes olvidar de esta lección es que al momento de colocar un símbolo de moneda en una referencia, ocasionará que la columna o la fila permanezcan fijas al momento de copiar la fórmula.

Hagamos entonces un resumen de lo que hemos aprendido. Cuando copiamos una fórmula que contiene referencias de celda, Excel modificará dichas referencias para adaptarlas a la nueva posición de la fórmula. Estas referencias son conocidas como referencias relativas y es el comportamiento predeterminado de Excel. Si por el contrario necesitamos evitar este comportamiento y necesitamos mantener constante una referencia aún después de copiarla, debemos utilizar las referencias absolutas agregando el símbolo de moneda.

Es importante que aprendas a identificar correctamente cada tipo de referencia para poder utilizarlas en el momento adecuado dentro de las fórmulas. Si quieres experimentar con el libro de Excel que he utilizado lo puedes descargar en el vínculo que he colocado en la parte inferior. Recibe un saludo.

About these ads

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s