Tipos de Referencias en Excel

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.

21-Mar-2016

Referencias a celdas

Como identificamos a las celdas de la hoja

En Excel podemos 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.

Si tenemos 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.  Observemos el siguiente ejemplo.

Fórmula con Referencias Relativas

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

Fórmula con Referencias Relativas

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.

Referencias relativas

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

Referencias mixtas

Este tipo de referencias tiene la peculiaridad de que sólo se mantiene fija una parte de la referencia, sólo la columna $A1 (mixta de columna) o sólo la fila A$1 (mixta de fila).

Se suelen utilizar en diseños de cálculo donde los datos que utilizan las fórmulas están dispuestos por columnas en la fila superior de cálculo y por filas en la columna izquierda de cálculo, estos diseños también suelen llamarse tablas de doble entrada, debido a que tienen dos entradas de datos, una por columnas y otro por filas.

En el siguiente ejemplo se muestra una tabla para calcular porcentajes (dispuestos en filas) de cantidades (dispuestas en columnas), donde la fórmula necesita obtener el valor de un porcentaje de la fila y multiplicarlo por la cantidad de la columna.

Fórmula con Referencias Mixtas

Para la resolución del cálculo, utilizamos dos referencias mixtas, donde el valor del porcentaje mantendrá fija la referencia de la columna ($A2) para que no se modifique al rellenar hacia la derecha y el valor de la cantidad mantendrá fija la fila (B$1) para que no se modifique al rellenar hacia abajo.

Fórmula con Referencias Mixtas

Atajo para crear referencias relativas, absolutas y mixtas

Una manera muy sencilla de crear una referencia absoluta es editar la fórmula y posicionar el cursor sobre la referencia 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 observareis 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 eliminan todos los signos $ y tendremos de nuevo una referencia relativa.

A continuación una tabla que os 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 de Columna =$A1 La columna no cambia, solamente la fila puede cambiar.
Mixta de Fila =A$1 La fila no cambia, solamente la columna puede cambiar.

Relativas y Absolutas

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?

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 (cuadro negro que se muestra en el vértice inferior derecho de la celda o selección activa), Excel copiará la fórmula y la ajustará de manera que la referencia se modifique automáticamente conforme va avanzando la fila.

Fórmula con Referencias Relativas

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.

Absolutas

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 queremos 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. Observar que ahora la fórmula de la celda D1 está escrita de la siguiente manera:

=$C$1*2

Fórmula con Referencias Absolutas

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

Detectar las referencias absolutas

Las referencias absolutas en la mayoría de los casos se suelen detectar, porque hacen referencia a datos comunes (fijos) a todas las fórmulas resultantes y estos datos suelen estar dispuestos fuera de la zona donde se escriben las fórmulas que los utilizan.

Un caso típico son los valores de porcentajes que se aplican por igual a todos los datos de un grupo dispuesto en filas.

Observemos el siguiente ejemplo, donde a todos los precios de los artículos se les tiene que aplicar un porcentaje de descuento promocional.

Detectar Referencias Absolutas

El porcentaje del descuento al ser un valor que se tiene que aplicar para todos los precios de los artículos  (valor constante) y se encuentra separado de la zona de relleno de la fórmula inicial del Dto, se referencia utilizando una referencia absoluta a su celda ($B$2), para que no sea modificado por Excel a medida que se vayan rellenando el resto de las celdas con la fórmula inicial.

Detectar Referencias Absolutas
Detectar Referencias Absolutas