Seguro que has oído hablar de las referencias relativas y absolutas en Excel, pero…
¿Qué significa cada una de ellas exactamente?
¿Cómo funcionan?
¿Cómo saber cuándo usar unas o las otras?
Realmente, una vez se ha entendido el concepto, es como el fuera de juego en el fútbol, ya no se olvida y se ve meridianamente claro.
Vamos a verlas para entenderlas…
Contenidos
Referencias en Excel
Lo primero que tenemos que entender es qué son y para que sirven las referencias.
Este es un concepto muy básico así que, si ya lo conoces, sáltate esta parte porque no te va a hacer falta.
Una referencia es cuando en una celda de Excel ponemos, de alguna manera, otra celda (o un rango de celdas).
Es decir, es cuando apuntamos a la identificación que Excel hace de una celda o un conjunto de celdas.
Por ejemplo, si en la celda A1 tenemos un 5 y en la celda B1 ponemos un =A1, en la celda B1 estamos haciendo referencia a la celda A1.
Esto así, a palo seco, se usa poco, pero es muy fácil poner, por ejemplo, en una suma =SUMA(A1;B1).
En esta fórmula estamos haciendo una referencia a la celda A1 y otra referencia a la celda B1.
Por tanto, las referencias se utilizan para hacer cálculos sobre el contenido de las celdas referenciadas.
Una vez entendido esto, vamos a por los tipos de referencias que tiene Excel.
Referencias relativas en Excel
Las referencias relativas son las más comunes, de hecho, en el ejemplo de la suma que te he puesto antes, he utilizado referencias relativas.
Este tipo de referencias están compuestas por la letra de la columna y el número de la fila a la que pertenece una celda (o un rango de celdas).
La característica principal que tienen las referencias relativas es que, si copiamos una celda que contiene una referencia relativa a otra celda distinta, Excel va a ajustar automáticamente esa referencia.
Es decir, si copiamos la fórmula anterior (situada en la celda C1) y la pegamos en la celda C2, Excel entiende que quieres sumar lo que está en las celdas A2 y B2 y ajusta automáticamente las dos referencias que contiene esta fórmula:
Por tanto, las referencias relativas son muy útiles cuando tenemos datos en unos rangos determinados y queremos realizar el mismo cálculo sobre esos datos.
Cuándo utilizar referencias relativas
Vamos a poner un ejemplo.
Tenemos la facturación trimestral de unos productos.
Calcularemos el total facturado por producto sumando lo que contienen las columnas correspondientes a cada trimestre.
Así, si creamos la fórmula en la primera fila y la arrastramos a lo largo de todas las demás, Excel adaptará el cálculo a cada una de las filas.
Lo que hace Excel aquí es mantener fija la referencia a las columnas C, D, E y F y va variando el número de fila a medida que bajamos.
Esto mismo, se puede hacer tanto en filas como en columnas.
Así, en el mismo ejemplo, podríamos calcular el total de cada trimestre de la siguiente manera:
En este caso, Excel mantiene la referencia a las filas 4 a 7 y va variando las referencias a las columnas a medida que arrastramos hacia la derecha.
Por tanto, las referencias relativas las deberemos utilizar cada vez que queramos que Excel adapte la fórmula a la celda o las celdas en las que tengamos que realizar los cálculos.
Referencias absolutas en Excel
Las referencias absolutas son aquellas en las que la celda a la que se hace referencia es estática.
Es decir, que por más que arrastremos o la copiemos en otra celda, Excel no cambiará la celda a la que se hace referencia.
Cómo cambiar de referencias relativas a referencias absolutas
Para cambiar una referencia de relativa a absoluta, tenemos que añadir un símbolo de $ delante de la columna y otro delante de la fila, es decir, en lugar de =A1, debemos poner =$A$1.
Pero no hagas lo mismo que yo he hice durante un tiempo, que era escribir manualmente el símbolo de dólar.
Para convertir la referencia relativa en absoluta, simplemente tienes que ir a la barra de fórmulas y, colocando el cursor en la referencia, pulsar la tecla F4.
Excel pondrá los símbolos automáticamente.
Cuándo utilizar referencias absolutas
Vamos a seguir con el ejemplo anterior.
En este caso, calcularemos el porcentaje de ventas que corresponde a cada producto.
Para ello debemos dividir las ventas de cada producto entre el total de ventas, por tanto, lo primero que haremos será calcular el total general de todas las ventas:
Ahora, dividiremos el total del primer producto entre el total general para obtener ese porcentaje.
Si ahora arrastramos la fórmula vemos que Excel nos devuelve un error #¡DIV/0!.
Esto es porque hemos utilizado una referencia relativa y Excel arrastra tanto el dividendo como el divisor.
Lo podemos ver en la siguiente imagen:
Arrastrar el dividendo está bien, pero el divisor debería mantenerse fijo.
En este caso, lo que debemos hacer es crear una referencia absoluta para el divisor.
Eliminamos las fórmulas incorrectas y antes de arrastrar, fijamos el divisor pulsando la tecla F4.
Si ahora arrastramos la fórmula, vemos que los cálculos se hacen correctamente y que el total del porcentaje calculado corresponde al 100%.
Haciendo esto, Excel ha mantenido la referencia a la celda de los totales:
Entonces, las referencias absolutas se deben utilizar cuando la celda a la que apunta la referencia es fija y no debe ser modificada automáticamente por Excel.
Como puedes ver en este ejemplo, en una misma operación o en una misma fórmula, podemos combinar el uso de referencias relativas y absolutas en Excel.
Referencias mixtas en Excel
Las referencias mixtas son aquellas en las que fijamos una parte de la referencia y la otra no.
Es decir, fijamos o bien la fila o bien la columna.
En este caso, la referencia quedaría de una de estas dos formas:
=$A1 si fijamos la columna o
=A$1 si fijamos la fila.
Así, al arrastrar o copiar la fórmula en otra celda diferente, el comportamiento de Excel será el que venga determinado por la parte de la referencia fijada y la parte no fijada.
Cómo cambiar de referencias relativas a referencias mixtas
De la misma forma que hemos cambiado de referencias relativas a absolutas pulsando en F4, para cambiar de absolutas a mixtas, seguiremos pulsando F4 tantas veces como sea necesario para que se fije una u otra parte de la referencia.
Es decir, el comportamiento de pulsar repetidamente F4 será el siguiente:
- Si pulsamos una vez, se fijan tanto columna como fila (Referencia absoluta. Columna absoluta y fila absoluta).
- Si pulsamos dos veces, se fija la fila y la columna queda móvil (Referencia mixta. Columna relativa y fila absoluta).
- Si pulsamos una tercera vez, se fija la columna y la fila queda móvil (Referencia mixta. Columna absoluta y fila relativa).
- Si pulsamos una cuarta vez, eliminamos todas las partes fijas (Referencia relativa. Columna relativa y fila relativa).
Cuándo utilizar referencias mixtas
Hay distintos momentos en los que vamos a necesitar utilizar referencias mixtas y los irás descubriendo a medida que vayas trabajando con referencias en Excel.
De todas formas, vamos a ver un par de ejemplos para que veas en qué casos puede ser necesario hacer uso de ellas.
Referencias mixtas a nivel de columna
Siguiendo con el ejemplo, vamos a imaginar que queremos mostrar el porcentaje de cada uno de los totales facturados.
Para ello, deberemos dividir cada total trimestral de cada producto entre el total de todos los trimestres:
Como has podido ver, he fijado el total general para que el cálculo se haga en todos los trimestres sobre el mismo importe.
Pero, ¿qué ocurrirá si ahora arrastramos las fórmulas para calcular lo que corresponde a los otros productos?
Como puedes ver, se calculan porcentajes, pero son incorrectos.
Los totales no se corresponden con el 100% de cada producto.
Esto es así porque hemos puesto una referencia absoluta hacia el total del primer producto.
En este caso, para que las fórmulas funcionen correctamente, deberíamos fijar la columna G y dejar libre la fila.
Por tanto, en este caso deberíamos haber utilizado una referencia mixta.
Deshacemos y vemos cómo hacerlo correctamente:
Referencias mixtas a nivel de fila
Para mostrarte un ejemplo de las referencias mixtas a nivel de fila, utilizaré el mismo ejemplo, pero en lugar de calcular los totales por producto, los calcularemos por trimestre.
Como puedes ver, en este caso, los totales están en una fila en lugar de estar en una columna como anteriormente.
Por tanto, para calcular los porcentajes, tendremos que dividir cada uno de los importes facturados por el total que se encuentra en la última fila de la tabla.
Como ya sabemos que deberemos utilizar una referencia mixta y fijar únicamente la fila, lo vamos a hacer directamente.
Un último apunte.
Los ejemplos que te he mostrado son muy sencillos y está muy claro qué tipo de referencia es necesario en cada uno de ellos, pero en ocasiones, dependiendo de la complejidad de los datos, te encontrarás con que no es tan evidente.
Te aconsejo que antes de poner referencias mixtas, te acostumbres a pensar bien qué parte quieres dejar fija.
Te evitarás errores que no querrás tener.
