Seguramente en algún momento has escuchado hablar de las referencias absolutas, relativas y mixtas en Excel. Tal vez has utilizado alguna de ellas, pero no sabes exactamente cómo funcionan y cuándo debes utilizar cada una. En este tutorial te explicamos todo acerca de las referencias en Excel para que dejen de ser un misterio para ti.
¿Qué es una referencia?
Podríamos decir que una referencia en Excel es una llamada que hacemos a otra celda cuando trabajamos con fórmulas.
Por ejemplo, si en una celda introducimos la función =C6+D6 estamos haciendo referencia a las celdas C6 y D6.
Como sabes, las celdas se identifican por su posición de fila y columna dentro de la hoja de Excel, aplicando una letra a la columna y un número a la fila.
Otra opción, es referenciar a celdas que se encuentran en otras hojas del libro. En ese caso también tomamos en la referencia el nombre de la hoja. Por ejemplo hoja1!A1.
La mejor forma de explicar las referencias es con ejemplos, así que vamos con ellos.
Referencias relativas en Excel
Una referencia en Excel es relativa cuando al mover o arrastrar la fórmula las referencias a las celdas que intervienen en la función se mueven del mismo modo que lo hace la función.
Por ejemplo, tenemos la facturación por trimestres de 4 hoteles y queremos calcular el total.
Para ello, en la celda G6 introduciremos la siguiente función =SUMA(C6:F6).
De esta forma nos sumará todos los valores correspondientes al primer hotel. Para aplicar esta función al resto de totales solo tenemos que arrastrar la función de la esquina inferior derecha de la celda hacia abajo o bien hacer doble clic sobre esta esquina. Excel aplicará automáticamente la función al resto de celdas de total.
Seguidamente, si clicas en cualquier celda de totales puedes comprobar como al arrastrar la función Excel ha arrastrado del mismo modo los valores que tenía que sumar. De forma que en la celda G8 la función que ha aplicado es =SUMA(C8:F8)
Al desplazar hacia abajo la función también se han desplazado hacia abajo los valores a sumar. Esto es una referencia relativa.
La posición de los valores que intervienen en la función es relativa en función de la posición de la celda donde insertamos la función.
Referencias absolutas en Excel
Las referencias absolutas son aquellas que se mantienen fijas independientemente de la posición de la función.
Por ejemplo, nos piden que calculemos el porcentaje de facturación de cada uno de los hoteles sobre el total. Lo primero que necesitamos calcular es el total de facturación sumando los totales de cada hotel que hemos calculado anteriormente. En la celda G10 introduce la función =SUMA(G6:G9)
Después, para calcular el porcentaje debes dividir el total de cada uno de los hoteles entre la suma total de la facturación de los 4 hoteles.
De modo que en la celda H10 debes introducir la función =G6/G10.
¿Qué pasará si arrastramos la función hacia abajo para calcular el resto de porcentajes?
Como se muestra la imagen al arrastrar la función en el resto de celdas aparece el error #¡DIV/0!
Esto es porque en el resto de casos está dividiendo el total de cada hotel entre 0, porque al arrastrar la función ha arrastrado tanto el dividendo como el divisor. En el caso del dividendo es correcto, pero en el caso del divisor necesitas que sea siempre el mismo.
Por ello, al divisor de esta función debes aplicarle una referencia absoluta.
Cómo aplicar referencia absoluta
Debes modificar la función que has introducido en la celda H6.
Para fijar el divisor, es decir, la celda G10 que interviene en la función, selecciona la celda H6 y en la barra de fórmulas coloca el cursor junto al argumento G10 y pulsa F4.
Verás que aparecen dos símbolos de dólar; uno delante de la G y otro entre la G y el 10. Quedando la función de esta forma =G6/$G$10
Con esta acción le estás indicando a Excel que en la celda G10 aplicas una referencia absoluta. De modo que, al arrastrar la función esta celda permanecerá invariable y siempre utilizará el valor que aparezca en la celda G10.
También puedes escribir con tu teclado los símbolos de dólar y funcionará del mismo modo. Pero siempre es más sencillo utilizar el atajo de F4.
Finalmente, al arrastrar ahora la función comprueba como se aplica correctamente. El divisor que se encuentra en la celda G10 permanece invariable en todas las funciones.
Por último, sabrás que se está utilizando una referencia absoluta cuando aparezca un símbolo de dolar delante de la letra de la columna y el número de la fila.
Referencias mixtas
Las referencias mixtas son aquellas en las que mantenemos fija la posición de la columna o de la fila.
A continuación, vemos un ejemplo para que entiendas su uso.
Supongamos que queremos expresar la facturación trimestral de los hoteles en porcentaje. Para ello, crea una nueva tabla en la que mediante fórmulas calcularemos los porcentajes.
Para hacer el cálculo en la celda C15 introducimos la función =C6/$G$6. Arrastramos la función al resto de los trimestres de ese hotel y vemos que funciona correctamente.
Pero, ¿Qué pasa si arrastramos las funciones de la primera fila hacia abajo?
Si compruebas qué valores utiliza en el resto de celdas verás que el cálculo no es correcto.
En realidad, necesitamos que sólo fije la columna para poder arrastrar la función hacia abajo.
Cómo aplicar referencias mixtas
Ve a la celda C15 y en la barra de fórmulas borra el símbolo de dólar que aparece delante del 6. A continuación, arrastra la función al resto de la fila y después arrastra la primera fila completa hacia abajo. Comprueba que el cálculo es correcto.
En este caso solo fijamos la columna, por lo que al arrastrar la función en la primera fila hacia la derecha mantiene la celda G6 en el divisor. Al arrastrar las funciones al resto de hoteles sí mueve la fija a la que hace referencia, manteniendo fija solo la columna.
Referencia mixta en filas
Como ejemplo, podemos ver la referencia mixta aplicada solo a las filas. Solo tienes que coger la tabla de datos y pasar de filas a columnas los datos.
En este caso, al cambiar la disposición de los datos en la celda K15 introduciremos la función =K6/K$10. Fijando únicamente la fila del divisor, para que al arrastrar la función al resto de columnas se aplique correctamente.
Truco para aplicar referencias en Excel
Por último, como vimos en el punto de las referencias absolutas, para aplicar la referencia a una celda solo tenemos que pulsar F4, pero si pulsamos de nuevo F4 pero si pulsamos de nuevo F4 la referencia cambia de la siguiente forma:
- F4 x 1 – Aplica referencia absoluta
- F4 x 2 – Aplica referencia mixta a las filas
- F4 x 3 – Aplica referencia mixta a las columnas.
- F4 x 4 – Aplica referencia relativa.
Con este truco puedes moverte rápidamente entre todas las opciones de referencias que nos brinda Excel.
Finalmente, esperamos haber despejado todas tus dudas sobre las referencias en Excel.
podria enviar el archivo de referencias