Referencia absoluta en Excel (cómo crearla y utilizarla)

Al hacer referencia a una celda en una fórmula de Excel, el tipo de referencia predeterminado es la referencia relativa. Este tipo de referencia se ajusta automáticamente al copiar la fórmula a otras celdas, según su posición relativa en filas y columnas. Si desea que la referencia permanezca fija, sin importar dónde se copie la fórmula, debe usar una referencia absoluta.

• Qué es una referencia absoluta
• Cómo crear referencias absolutas
• Uso de la referencia absoluta con ejemplos
• Calcular el porcentaje del total
• Buscar un valor y devolver el valor correspondiente
• 2 clics para convertir en lote referencias de celda en absolutas con Kutools
• Referencia relativa y referencia mixta
• Aspectos a recordar

Descargue gratuitamente el archivo de ejemplo

Vídeo: Referencia absoluta

Qué es una referencia absoluta

Una referencia absoluta es un tipo de referencia a celdas en Excel.

A diferencia de una referencia relativa, que varía según su posición al copiar la fórmula a otras celdas, una referencia absoluta permanece inalterable, sin importar dónde se copie o mueva la fórmula.

Se crea una referencia absoluta colocando el signo de dólar ($) delante de la columna y la fila en la fórmula. Por ejemplo, para hacer una referencia absoluta a la celda A1, se escribe como $A$1.

Las referencias absolutas son ideales cuando necesitas hacer referencia a una celda o rango fijo en una fórmula que se copiará en varias celdas, sin que dicha referencia varíe.

Por ejemplo, el rango A4:C7 contiene los precios de los productos, y desea calcular el impuesto a pagar por cada uno en función del tipo impositivo indicado en la celda B2.

Si utiliza una referencia relativa en la fórmula, como «=B5*B2», obtendrá resultados incorrectos al arrastrar el controlador de autorrelleno hacia abajo para aplicarla. Esto ocurre porque la referencia a la celda B2 se ajusta según la posición relativa de las celdas en cada nueva fila. Así, la fórmula en la celda C6 pasa a ser «=B6*B3» y la de la celda C7, «=B7*B4».

Sin embargo, si utiliza una referencia absoluta a la celda B2 con la fórmula «=B5*$B$2», asegurará que el tipo impositivo se mantenga constante en todas las celdas al arrastrar la fórmula hacia abajo con el controlador de autorrelleno, obteniendo así resultados correctos.

Uso de referencia relativa		Uso de referencia absoluta

Cómo crear referencias absolutas

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

Añadir manualmente los signos de dólar a la referencia de celda

Puede añadir manualmente los signos de dólar ($) delante de las referencias de columna y fila que quiera convertir en absolutas mientras escribe la fórmula en una celda.

Por ejemplo, si desea sumar los números de las celdas A1 y B1 y convertir ambas en referencias absolutas, simplemente escriba la fórmula como «=$A$1+$B$1». Así garantizará que las referencias de celda permanezcan constantes al copiar o mover la fórmula a otras celdas.

Alternativamente, si desea convertir las referencias existentes en una fórmula a absolutas, seleccione la celda y, a continuación, vaya a la Barra de fórmulas para añadir los signos de dólar ($).

Usar la tecla de acceso directo F4 para convertir una 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 convertir en absoluta;
3. Pulse la tecla "F4" del teclado para cambiar los tipos de referencia hasta que aparezcan signos de dólar delante de las referencias de columna y fila;
4. Pulse la tecla Intro para salir del modo de edición y aplicar los cambios.

La tecla F4 permite alternar entre referencias relativas, absolutas y mixtas.

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

Si desea convertir todas las referencias de una fórmula en absolutas, seleccione toda la fórmula en la Barra de fórmulas y pulse la tecla "F4" para alternar entre los tipos de referencia hasta que aparezcan signos de dólar delante de las referencias de columna y fila.

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

Uso de la referencia absoluta con ejemplos

Esta sección ofrece dos ejemplos que ilustran cuándo y cómo usar referencias absolutas en una fórmula de Excel.

Ejemplo 1: Calcular el porcentaje del total

Suponga que tiene un rango de datos (A3:B7) con las ventas de cada fruta y que la celda B8 contiene el importe total de dichas ventas. Ahora desea calcular el porcentaje que representa la venta de cada fruta respecto al total.

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

Percentage = Sale/Amount

Utilice la referencia relativa en la fórmula para obtener el porcentaje de la primera fruta de la siguiente manera:

=B4/B8

Al arrastrar el controlador de autorrelleno hacia abajo para calcular el porcentaje de las demás frutas, aparecerán errores #¡DIV/0!

Esto ocurre porque, al arrastrar el controlador de autorrelleno para copiar la fórmula en las celdas inferiores, la referencia relativa B8 se ajusta automáticamente a otras celdas (B9, B10, B11) según su posición relativa. Como las celdas B9, B10 y B11 están vacías —lo que equivale a un valor cero—, la fórmula intenta dividir entre cero y devuelve un error.

Para corregir estos errores, en este caso debe convertir la referencia de la celda B8 en absoluta ($B$8) en la fórmula, de modo que no cambie al moverla o copiarla a cualquier ubicación. Ahora, la fórmula se actualiza a:

=B4/$B$8

A continuación, arrastre el controlador de autorrelleno hacia abajo para calcular el porcentaje del resto de frutas.

Ejemplo 2: Buscar un valor y devolver el valor coincidente correspondiente

Suponga que desea buscar los nombres en el rango D4:D5 y obtener sus salarios correspondientes según la lista de personal y los salarios anuales indicados en el rango A4:B8.

Fórmula genérica para buscar:

=VLOOKUP(lookup_value, table_range, column_index, logical)

Si utiliza la referencia relativa en la fórmula para buscar un valor y devolver el valor coincidente correspondiente de la siguiente manera:

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

Al arrastrar el controlador de autorrelleno hacia abajo para buscar el valor inferior, se producirá un error.

Al arrastrar el controlador de relleno hacia abajo para copiar la fórmula en la celda inferior, las referencias de la fórmula se ajustan automáticamente una fila hacia abajo. Así, el rango de la tabla A4:B8 pasa a ser A5:B9. Como Lisa no está incluida en el rango A5:B9, la fórmula devuelve un error.

Para evitar estos errores, utilice 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)

A continuación, arrastre el controlador de autorrelleno hacia abajo para obtener el salario de Lisa.

2 clics para convertir en lote referencias de celda en absolutas con Kutools

Tanto si prefiere escribir manualmente como si utiliza el acceso directo F4, solo podrá modificar una fórmula a la vez en Excel. Si desea convertir en absolutas las referencias de celda en cientos de fórmulas, la herramienta «Convertir referencias de celda» de Kutools para Excel le permitirá hacerlo en solo dos clics.

Para convertir en absolutas las referencias de celda en varias fórmulas, seleccione las celdas que contienen las fórmulas y haga clic en **Kutools > Más > Convertir Referencias de Celda**. A continuación, elija la opción **A absoluta** y haga clic en **Aceptar** o **Aplicar**. ¡Listo! Todas las referencias de celda de las fórmulas seleccionadas se habrán convertido en absolutas.

Referencia relativa y referencia mixta

Además de la referencia absoluta, existen otros dos tipos: la relativa y la mixta.

La referencia relativa es el tipo de referencia predeterminado en Excel, que no incluye signos de dólar ($) delante de las referencias de fila y columna. Cuando copias o mueves a otras celdas una fórmula con referencias relativas, estas se ajustan automáticamente según su posición relativa.

Por ejemplo, al escribir una fórmula en una celda como «=A1+1» y arrastrar el controlador de autorrelleno hacia abajo para aplicarla a la celda siguiente, la fórmula se actualizará automáticamente a «=A2+1».

La referencia mixta combina una referencia absoluta y una relativa. Es decir, utiliza el signo de dólar ($) para fijar bien la fila o bien la columna al copiar o rellenar una fórmula.

Tomemos como ejemplo una tabla de multiplicar, en la que las filas y las columnas contienen los números del 1 al 9, que se multiplican entre sí.

Para comenzar, puede introducir la fórmula «=B3*C2» en la celda C3 para multiplicar el valor de la celda B3 por el número (1) de la primera columna. Sin embargo, al arrastrar el controlador de autorrelleno hacia la derecha para completar las demás celdas, verá que todos los resultados son incorrectos, excepto el primero.

Esto se debe a que, al copiar la fórmula hacia la derecha, la fila permanece fija, pero la columna se desplaza 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 deberían mantenerse como «=B3*D2», «=B3*E2», etc.

En este caso, debe añadir un signo de dólar ($) para bloquear la referencia de columna de «B3». Utilice la siguiente fórmula:

=$B3*C2

Ahora, al arrastrar la fórmula hacia la derecha, los resultados son correctos.

A continuación, debe multiplicar el número 1 de la celda C2 por los números de las filas siguientes.

Al copiar la fórmula hacia abajo, la columna de la celda C2 permanece fija, pero la fila se actualiza automáticamente pasando de C2 a C3, C4, etc. Como resultado, las fórmulas en las celdas inferiores se convierten en «=$B4*C3», «=$B5*C4», etc., lo que genera resultados incorrectos.

Para resolver este problema, sustituya «C2» por «C$2» y así mantendrá fija la referencia de fila al arrastrar el controlador de autorrelleno hacia abajo para completar las fórmulas.

=$B3*C$2

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

Aspectos a 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 la 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 permanece fija / La referencia de columna cambia cuando la fórmula se copia a otras celdas, pero la referencia de fila permanece fija;

• Por lo general, las referencias absolutas no cambian al mover una fórmula. Sin embargo, se ajustan automáticamente cuando se inserta o elimina una fila o columna por encima o a la izquierda de la celda referenciada en la hoja de cálculo. Por ejemplo, si tienes la fórmula «=$A$1+1» e insertas una fila en la parte superior de la hoja, esta se actualizará automáticamente a «=$A$2+1».

  

• La tecla «F4» te permite alternar fácilmente entre referencias relativas, absolutas y mixtas.