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 a otras celdas según su columna y fila relativas. Si desea mantener una referencia constante, independientemente de dónde se copie la fórmula, debe usar la referencia absoluta.

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

Descarga gratis el archivo de muestra

Vídeo: 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 en otras celdas, una referencia absoluta permanecerá constante sin importar dónde se copie o mueva la fórmula.

Una referencia absoluta se crea agregando 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 hacer referencia a una celda o rango fijo en una fórmula que se copiará en varias 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 a pagar de cada producto en función de la tasa de impuestos en la celda B2.

Si usa una referencia relativa en la fórmula como "=B5*B2", se obtienen algunos resultados incorrectos cuando arrastra el controlador de autocompletar hacia abajo para aplicar esta fórmula. Dado que la referencia a la celda B2 cambiará en relación con las celdas de 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 usa una referencia absoluta a la celda B2 con la fórmula "=B5*$B$2" se asegurará de que la tasa impositiva permanezca igual para todas las celdas cuando la fórmula se arrastre hacia abajo con el controlador de autocompletar, los resultados son correctos.

Usando referencia relativa		Usando referencia absoluta

Cómo hacer referencias absolutas

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

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

Puede 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 las celdas A1 y B1 y hacer que ambos sean absolutos, simplemente escriba la fórmula como "=$A$1+$B$1". Esto garantizará que las referencias de las celdas permanezcan constantes cuando la fórmula se copie o se mueva a otras celdas.

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

Usando el atajo F4 para convertir una referencia relativa a absoluta

1. Haga doble clic en la celda con la fórmula para ingresar al modo de edición;
2. Coloque el cursor en la referencia de celda que desea convertir en absoluta;
3. Prensa F4 tecla en el teclado para cambiar los tipos de referencia hasta que se agreguen los signos de dólar antes de las referencias de columna y fila;
4. Prensa Enviar para salir del modo de edición y aplicar los cambios.

La tecla F4 puede alternar la referencia entre referencia relativa, referencia absoluta y referencia mixta.

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

Si desea que todas las referencias sean absolutas en una fórmula, seleccione la fórmula completa en la barra de fórmulas, presione 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 porcentaje del total

Suponga que tiene un rango de datos (A3:B7) que contiene las ventas de cada fruta, y la celda B8 contiene la cantidad total de ventas de estas frutas, ahora desea calcular el porcentaje de venta de cada fruta del 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 las primicias así:

=B4/B8

Al arrastrar el controlador de autocompletar hacia abajo para calcular el porcentaje de otras frutas, ¡#DIV/0! los errores serán devueltos.

Dado que cuando arrastra el controlador de relleno automático para copiar la fórmula a las celdas a continuación, la referencia relativa B8 se ajusta automáticamente a otras referencias de celda (B9, B10, B11) en función de sus posiciones relativas. Y las celdas B9, B10 y B11 están vacías (ceros), cuando el divisor es cero, la fórmula vuelve a dar error.

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

=B4/$B$8

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

Ejemplo 2 Busque un valor y regrese al valor de coincidencia correspondiente

Suponiendo que desea buscar la lista de nombres en D4:D5 y devolver sus salarios correspondientes en función de los nombres del personal y el salario anual correspondiente proporcionado 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 de coincidencia correspondiente como este:

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

Luego, arrastre el controlador de autocompletar hacia abajo para buscar el valor a continuación, se devolverá un error.

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

Para evitar 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 autocompletar hacia abajo para obtener el salario de Lisa.

2 clics para hacer por lotes que las referencias de celda sean absolutas 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 que las referencias de celda en cientos de fórmulas sean absolutas en Excel, el Convertir se refiere herramienta de Kutools for Excel puede ayudarlo a manejar el trabajo con 2 clics.

Seleccione las celdas de fórmula que desea que las referencias de celda sean absolutas, haga clic en Kutools > Más (efectos) > Convertir se refiere. Entonces elige el Al absoluto Y haga clic en Ok or Aplicar. Ahora todas las referencias de celda de las fórmulas seleccionadas se han convertido en absolutas.

Referencia relativa y referencia mixta

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

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 se 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 autocompletar hacia abajo para completar esta fórmula a la siguiente celda, la fórmula cambiará automáticamente a "=A2+1".

referencia mixta se compone tanto de una referencia absoluta como de una referencia relativa. En otras palabras, la referencia mixta usa el signo de dólar ($) para corregir la fila o la columna cuando se copia o completa una fórmula.

Tome una tabla de multiplicar como ejemplo, las filas y columnas enumeran los números del 1 al 9, que multiplicará 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 autocompletar hacia la derecha para completar las otras celdas, notará que todos los resultados son incorrectos, excepto el primero.

Esto se debe a que cuando copia la fórmula a 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 correctas (D3, E3, etc.) cambia a "=C3*D2", "=D3*E2", y así sucesivamente, cuando en realidad quieres que sean "=B3*D2", "=B3*E2", y así sucesivamente.

En este caso, debe agregar un signo de dólar ($) para bloquear la referencia de columna de "B3". Utilice la fórmula de la siguiente manera:

=$B3*C2

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

Luego, debe multiplicar el número 1 en la celda C2 por los números en las filas a continuación.

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

Para resolver este problema, cambie "C2" a "C$2" para evitar que la referencia de la fila cambie al arrastrar el controlador de autocompletar hacia abajo para completar las fórmulas.

=$B3*C$2

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

Cosas para recordar

• Resumen de referencias de celda

  Tipo	Ejemplo	Resumen
  Referencia absoluta	$ A $ 1	Nunca cambie 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 en 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 es fija/La referencia de columna cambia cuando la fórmula se copia a otras celdas pero la referencia de fila es fija;

• Generalmente, las referencias absolutas nunca cambian cuando se mueve una fórmula. Sin embargo, las referencias absolutas se ajustarán automáticamente cuando se agregue o elimine una fila o columna de la parte superior o izquierda de la hoja de trabajo. 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 F4 La tecla puede cambiar entre referencia relativa, referencia absoluta y referencia mixta.