Referencia absoluta en Excel (cómo hacerla y usarla)

Al hacer referencia a una celda en una fórmula en Excel, el tipo de referencia predeterminado es una referencia relativa. Estas referencias cambiarán cuando la fórmula se copie en otras celdas según su columna y fila relativas. Si desea mantener una referencia constante, independientemente de dónde se copie la fórmula, necesita usar la referencia absoluta.

• ¿Qué es una referencia absoluta?
• Cómo hacer referencias absolutas
• Usar referencia absoluta con ejemplos
• Calcular el porcentaje del total
• Buscar un valor y devolver el valor coincidente correspondiente
• 2 clics para hacer absolutas las referencias de celda por lotes con Kutools
• Referencia relativa y referencia mixta
• Cosas que recordar

Descarga gratuita del archivo de muestra

Video: Referencia absoluta

¿Qué es una referencia absoluta?

Una referencia absoluta es un tipo de referencia de celda en Excel.

En comparación con una referencia relativa que cambiará según su posición relativa cuando una fórmula se copia a otras celdas, una referencia absoluta permanecerá constante sin importar dónde se copie o mueva la fórmula.

Una referencia absoluta se crea añadiendo un signo de dólar ($) antes de las referencias de columna y fila en la fórmula. Por ejemplo, para crear una referencia absoluta para la celda A1, debe representarla como $A$1.

Las referencias absolutas son útiles cuando desea referirse a una celda o rango fijo en una fórmula que se copiará en múltiples celdas, pero no desea que la referencia cambie.

Por ejemplo, el rango A4:C7 contiene los precios de los productos, y desea obtener el impuesto pagable de cada producto basado en la tasa de impuestos en la celda B2.

Si utiliza una referencia relativa en la fórmula como "=B5*B2", se devolverán resultados incorrectos al arrastrar el controlador de autollenado hacia abajo para aplicar esta fórmula. Dado que la referencia a la celda B2 cambiará en relación con las celdas en la fórmula. Ahora, la fórmula en la celda C6 es "=B6*B3" y la fórmula en la celda C7 es "=B7*B4".

Pero si utiliza una referencia absoluta a la celda B2 con la fórmula "=B5*$B$2", asegurará que la tasa de impuestos permanezca igual para todas las celdas cuando la fórmula se arrastre hacia abajo usando el controlador de autollenado; los resultados serán correctos.

Uso de referencia relativa		Uso de referencia absoluta

Cómo hacer referencias absolutas

Para hacer una referencia absoluta en Excel, agregue signos de dólar ($) antes de las referencias de columna y fila en una fórmula. Hay dos formas de crear una referencia absoluta:

Agregar manualmente signos de dólar a la referencia de celda

Podría agregar manualmente signos de dólar ($) antes de las referencias de columna y fila que desea hacer absolutas mientras escribe la fórmula en una celda.

Por ejemplo, si desea sumar los números en la celda A1 y B1 y hacer que ambas sean absolutas, simplemente escriba la fórmula como "=$A$1+$B$1". Esto asegurará que las referencias de celda permanezcan constantes cuando la fórmula se copie o mueva a otras celdas.

Alternativamente, si desea cambiar las referencias en una fórmula existente a absolutas, puede seleccionar la celda, luego ir a la barra de fórmulas para agregar los signos de dólar ($).

Usar el atajo F4 para convertir la referencia relativa en absoluta

1. Haga doble clic en la celda con la fórmula para entrar en modo de edición;
2. Coloque el cursor en la referencia de celda que desea hacer absoluta;
3. Presione la tecla "F4" en el teclado para alternar entre los tipos de referencia hasta que se agreguen los signos de dólar antes de las referencias de columna y fila;
4. Presione la tecla "Enter" para salir del modo de edición y aplicar los cambios.

La tecla F4 alterna las referencias entre relativas, absolutas y mixtas.

A1 → $A$1 → A$1 → $A1 → A1

Si desea hacer absolutas todas las referencias en una fórmula, seleccione toda la fórmula en la barra de fórmulas, presione la tecla "F4" para alternar los tipos de referencia hasta que se agreguen los signos de dólar antes de las referencias de columna y fila.

A1+B1 → $A$1+$B$1 → A$1+B$1 → $A1+$B1 → A1+B1

Usar referencia absoluta con ejemplos

Esta parte proporciona 2 ejemplos para mostrar cuándo y cómo usar referencias absolutas en una fórmula de Excel.

Ejemplo 1 Calcular el porcentaje del total

Supongamos que tiene un rango de datos (A3:B7) que contiene las ventas de cada fruta, y la celda B8 contiene el monto total de ventas de estas frutas, ahora desea calcular el porcentaje de venta de cada fruta sobre el total.

La fórmula genérica para calcular el porcentaje del total:

Percentage = Sale/Amount

Use la referencia relativa en la fórmula para obtener el porcentaje de la primera fruta así:

=B4/B8

Al arrastrar el controlador de autollenado hacia abajo para calcular el porcentaje de otras frutas, se devolverán errores #¡DIV/0!.

Dado que al arrastrar el controlador de autollenado para copiar la fórmula a las celdas inferiores, la referencia relativa B8 se ajusta automáticamente a otras referencias de celda (B9, B10, B11) según sus posiciones relativas. Y las celdas B9, B10 y B11 están vacías (ceros), cuando el divisor es cero, la fórmula devuelve un error.

Para corregir los errores, en este caso, necesita hacer absoluta la referencia de celda B8 ($B$8) en la fórmula para evitar que cambie al mover o copiar la fórmula a cualquier lugar. Ahora la fórmula se actualiza a:

=B4/$B$8

Luego arrastre el controlador de autollenado hacia abajo para calcular el porcentaje de otras frutas.

Ejemplo 2 Buscar un valor y devolver el valor coincidente correspondiente

Supongamos que desea buscar la lista de nombres en D4:D5 y devolver sus salarios correspondientes basados en los nombres del personal y los salarios anuales correspondientes proporcionados en el rango (A4:B8).

La fórmula genérica para buscar es:

=VLOOKUP(lookup_value, table_range, column_index, logical)

Si usa la referencia relativa en la fórmula para buscar un valor y devolver el valor coincidente correspondiente así:

=VLOOKUP(D4,A4:B8,2,FALSE)

Luego arrastre el controlador de autollenado hacia abajo para buscar el valor debajo, se devolverá un error.

Cuando arrastra el controlador de relleno hacia abajo para copiar la fórmula a la celda inferior, las referencias en la fórmula se ajustan automáticamente hacia abajo una fila. Como resultado, la referencia al rango de tabla, A4:B8, se convierte en A5:B9. Dado que Lisa no se encuentra en el rango A5:B9, la fórmula devuelve un error.

Para evitar los errores, use la referencia absoluta $A$4:$B$8 en lugar de la referencia relativa A4:B8 en la fórmula:

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

Luego arrastre el controlador de autollenado hacia abajo para obtener el salario de Lisa.

2 clics para hacer absolutas las referencias de celda por lotes con Kutools

Ya sea que elija escribir manualmente o usar el atajo F4, solo puede cambiar una fórmula a la vez en Excel. Si desea hacer absolutas las referencias de celda en cientos de fórmulas en Excel, la herramienta "Convert Refers" de "Kutools para Excel" puede ayudarle a manejar el trabajo con 2 clics.

Para hacer absolutas las referencias de celda en múltiples fórmulas, seleccione las celdas de las fórmulas y haga clic en "Kutools" > "Más" > "Convert Refers". Luego elija la opción "A Absoluta" y haga clic en "Aceptar" o "Aplicar". Ahora todas las referencias de celda de las fórmulas seleccionadas se han convertido a absolutas.

Referencia relativa y referencia mixta

Aparte de la referencia absoluta, hay otros dos tipos de referencia: referencia relativa y referencia mixta.

La referencia relativa es el tipo de referencia predeterminado en Excel, que no tiene signos de dólar ($) antes de las referencias de fila y columna. Y cuando una fórmula con referencias relativas se copia o mueve a otras celdas, las referencias cambiarán automáticamente según su posición relativa.

Por ejemplo, cuando escribe una fórmula en una celda como "=A1+1", luego arrastra el controlador de autollenado hacia abajo para llenar esta fórmula en la siguiente celda, la fórmula cambiará automáticamente a "=A2+1".

La referencia mixta está compuesta tanto por una referencia absoluta como relativa. En otras palabras, la referencia mixta usa el signo de dólar ($) para fijar ya sea la fila o la columna cuando una fórmula se copia o llena.

Tomemos una tabla de multiplicar como ejemplo, las filas y columnas enumeran los números del 1 al 9, que multiplicarás entre sí.

Para comenzar, puede usar la fórmula "=B3*C2" en la celda C3 para multiplicar 1 en la celda B3 por el número (1) en la primera columna. Sin embargo, cuando arrastra el controlador de autollenado hacia la derecha para llenar las otras celdas, notará que todos los resultados son incorrectos excepto el primero.

Esto se debe a que cuando copia la fórmula hacia la derecha, la posición de la fila no cambia, pero la posición de la columna cambia de B3 a C3, D3, etc. Como resultado, las fórmulas en las celdas de la derecha (D3, E3, etc.) cambian a "=C3*D2", "=D3*E2", y así sucesivamente, cuando realmente desea que sean "=B3*D2", "=B3*E2", y así sucesivamente.

En este caso, necesita agregar un signo de dólar ($) para bloquear la referencia de columna de “B3”. Use la fórmula de la siguiente manera:

=$B3*C2

Ahora, cuando arrastre la fórmula hacia la derecha, los resultados son correctos.

Luego necesita multiplicar el número 1 en la celda C2 por los números en las filas inferiores.

Cuando copia la fórmula hacia abajo, la posición de la columna de la celda C2 no cambiará, pero la posición de la fila cambia de C2 a C3, C4, etc. Como resultado, las fórmulas en las celdas inferiores cambian a "=$B4C3", "=$B5C4", etc., lo que producirá resultados incorrectos.

Para resolver este problema, cambie “C2” a “C$2” para mantener la referencia de fila constante al arrastrar el controlador de autollenado hacia abajo para llenar las fórmulas.

=$B3*C$2

Ahora puede arrastrar el controlador de autollenado hacia la derecha o hacia abajo para obtener todos los resultados.

Cosas que recordar

• Resumen de referencias de celda

  Tipo	Ejemplo	Resumen
  Referencia Absoluta	$A$1	Nunca cambia cuando la fórmula se copia a otras celdas
  Referencia Relativa	A1	Tanto la referencia de fila como de columna cambian según la posición relativa cuando la fórmula se copia a otras celdas
  Referencia Mixta	$A1/A$1	La referencia de fila cambia cuando la fórmula se copia a otras celdas, pero la referencia de columna está fija/La referencia de columna cambia cuando la fórmula se copia a otras celdas, pero la referencia de fila está fija;

• En general, las referencias absolutas nunca cambian cuando se mueve una fórmula. Sin embargo, las referencias absolutas se ajustarán automáticamente cuando se agrega o elimina una fila o columna desde la parte superior o izquierda de la hoja de cálculo. Por ejemplo, en una fórmula "=$A$1+1", cuando inserta una fila en la parte superior de la hoja, la fórmula cambiará automáticamente a "=$A$2+1".

  

• La tecla "F4" puede alternar entre referencias relativas, absolutas y mixtas.