Referencias absolutas y relativas

Semana 3: Referencias absolutas y relativas

Utilizando referencias absolutas y relativas

En Excel puedes especificar tres tipos de referencias: relativas, absolutas y mixtas. Al utilizar el tipo de referencia adecuado nos aseguramos de que al copiar la fórmula a una nueva ubicación seguirá funcionando adecuadamente.

Referencias relativas

Si tienes una fórmula que hace referencia a otras celdas y copiamos esa fórmula a otra ubicación, Excel actualizará automáticamente las referencias de la fórmula para adecuarlas a la nueva ubicación.  Considera el siguiente ejemplo.

La celda C1 hace la suma de A1 y B1. Observa lo que sucede si copiamos el contenido de la celda C1 y lo pegamos en la celda C2.

Excel ha sido tan inteligente que ha supuesto que la fórmula de la celda C2 debe sumar los valores de A2 y B2. Si volvemos a copiar la fórmula y pegarla en la celda C3 obtendremos la fórmula: =A3 + B3.

Una referencia en Excel identifica a una celda (o rango de celdas) dentro de una hoja de trabajo y le hace saber a Excel en dónde buscar los valores que se quieren utilizar en una fórmula. Pero ¿Cuál es la diferencia entre las referencias absolutas y las referencias relativas?

Una referencia relativa es cuando Excel puede modificar libremente dicha referencia para ajustarla al utilizarla dentro de una fórmula. Por ejemplo, si la fórmula de la celda D1 es la siguiente:

=C1*2

Si arrastramos el controlador de relleno hacia abajo, Excel copiará la fórmula y la ajustará de manera que la referencia se modifique automáticamente conforme va avanzando la fila.

En este ejemplo la referencia C1 de la fórmula se fue incrementando automáticamente a C2, C3, C4 y C5 conforme fue cambiando de fila.

De manera predetermina Excel utiliza referencias relativas al momento de insertar una referencia de celda en una fórmula. Las referencias relativas son actualizadas automáticamente por Excel al momento de copiar la fórmula a otra ubicación.

En el siguiente ejemplo tenemos una fórmula en la celda E6 que realiza la suma de varias celdas. La imagen tiene habilitado el rastreo de precedentes para poder visualizar las celdas involucradas en el cálculo.

Al momento de copiar la fórmula una celda hacia abajo, descubriremos que Excel modifica todas las referencias agregando una fila a todas ellas. Observa el cambio en las referencias después de haber copiado la fórmula:

Si en lugar de copiar la fórmula hacia abajo, la copiamos hacia la derecha, entonces Excel modificará las referencias de celda agregando una columna a cada una de ellas de la siguiente manera:

De esta manera comprobamos que Excel actualiza las referencias de celdas al momento de copiar una fórmula. Estas referencias se conocen como relativas porque serán modificadas por Excel en relación con la celda a donde se copie la fórmula.

Cuando copiamos fórmulas Excel utiliza el tipo de referencia relativa que es el comportamiento predeterminado. Este tipo de referencia se adapta al contexto de la nueva celda cambiando las referencias de acuerdo a la nueva ubicación. Sin embargo, en algunas ocasiones no queremos tener este comportamiento sino que queremos hacer referencia siempre a una celda específica sin importar que copiemos la fórmula a otra ubicación y para ello necesitamos utilizar las referencias absolutas.

Referencias absolutas

Para fijar la columna o fila de una referencia utilizamos el signo $ y lo colocamos antes de la letra de la columna o del número  de fila de la referencia. Así por ejemplo podemos tener una referencia absoluta que fija tanto la columna como la fila, por ejemplo: $A$1

Ahora analicemos el caso de las referencias absolutas. Una referencia es absoluta cuando Excel no la puede ajustar para adaptarse a la fórmula conforme cambia de fila o de columna. Las referencias absolutas permanecen constantes sin importar a dónde se copie la fórmula y se definen utilizando el símbolo “$”. Por ejemplo, la referencia $A1 significa que en esta referencia la columna A será siempre fija mientras que la  fila podría ajustarse automáticamente. Por otro lado, la referencia A$1 significa que la fila 1 permanecerá siempre fija. Si quieres que tanto la columna como la fila permanezcan siempre fijas la referencia debe ser $A$1.

Con un ejemplo similar al anterior veamos lo que sucede cuando hacemos que la referencia sea absoluta. Nota que ahora la fórmula de la celda D1 está escrita de la siguiente manera:

=$C$1*2

Observa que sin importar a qué fila se copió la fórmula, la referencia siempre se mantiene hacia $C$1. Es decir, aún cuando se halla copiado la fórmula a filas diferentes la referencia dentro de la fórmula permaneció constante. Es importante que entiendas la diferencia entre estos dos tipos de referencias porque te permitirá escribir fórmulas efectivas en Excel.

A diferencias de las referencias relativas, las referencias absolutas no se modifican aunque copiemos la fórmula a otras celdas. Para hacer que una referencia se convierta en absoluta utilizamos el símbolo de moneda ($) anteponiéndolo a la columna y fila de la referencia.

Tomando el mismo ejemplo anterior, voy a convertir la referencia a la celda A1 en una referencia absoluta. En la siguiente imagen puedes ver en la barra de fórmulas que de todas las referencias incluidas en la fórmula solo la referencia a la celda A1 es absoluta:

Puedes notar que es una referencia absoluta porque está escrita como $A$1. Hasta este momento no podemos notar diferencia alguna con el ejemplo anterior ya que la suma sigue siendo la misma, pero veremos una diferencia al momento de copiar la fórmula. Observa lo que sucede cuando copio la fórmula hacia abajo:

Toda las referencias has sido modificadas agregando una fila, pero la referencia a la celda A1 se mantenido fija porque es la única referencia absoluta en nuestra fórmula. Ahora observa lo que sucede al copiar la fórmula de la celda E6 a la derecha:

Todas las referencias se desplazan una columna a la derecha, pero la referencia a la celda A1 permanece constante aún después de haber copiado la fórmula. Las referencias absolutas serán de mucha ayuda cuando necesitamos indicar a Excel que considere siempre el valor de una misma celda sin importar que la fórmula sea copiada a otra ubicación.

Un atajo para convertir una referencia relativa en una referencia absoluta es colocar el cursor de la barra de fórmulas sobre la referencia que deseamos transformar y entonces pulsar la tecla F4 para que Excel introduzca los símbolos de moneda a un lado de la columna y de la fila.

Referencias mixtas

Las referencias mixtas son aquellas donde solamente se fija la columna ($A1) o solamente se fija la fila(A$1).

Atajo para crear referencias absolutas y mixtas

Una manera muy sencilla de crear una referencia absoluta es editar la fórmula y posicionar el cursor sobre lareferencia relativa que deseamos convertir en referencia absoluta. Posteriormente debemos presionar la tecla F4 lo cual insertará el signo $ tanto para la columna como para el número de fila.

Si pulsas dos veces la tecla F4 observarás que Excel  solamente inserta el signo $ para la fila y si pulsas la tecla F4 tres veces se creará una referencia mixta con la columna fija. Al pulsar una cuarta vez la tecla F4 se removerán todos los signos $ y tendremos de nuevo una referencia relativa.

A continuación una tabla que te ayudará a recordar este tipo de referencias.

Referencia	Ejemplo	Descripción
Relativa	=A1	La columna y la fila pueden cambiar al momento de copiar la fórmula.
Absoluta	=$A$1	Ni la columna ni la fila pueden cambiar.
Mixta	=$A1	La columna no cambia, solamente la fila puede cambiar.
Mixta	=A$1	La fila no cambia, solamente la columna puede cambiar.