Una hoja de cálculo en Excel está formada por celdas. Se puede hacer referencia a estas celdas especificando el valor de la fila y el valor de la columna.
Por ejemplo, A1 se referiría a la primera fila (especificada como 1) y la primera columna (especificada como A). De manera similar, B3 sería la tercera fila y la segunda columna.
El poder de Excel radica en el hecho de que puede usar estas referencias de celda en otras celdas al crear fórmulas.
Ahora hay tres tipos de referencias de celda que puede usar en Excel:
- Referencias de celda relativa
- Referencias de celda absoluta
- Referencias de celdas mixtas
Comprender estos diferentes tipos de referencias de celda lo ayudará a trabajar con fórmulas y a ahorrar tiempo (especialmente al copiar y pegar fórmulas).
¿Qué son las referencias de celda relativa en Excel?
Permítanme tomar un ejemplo simple para explicar el concepto de referencias de celda relativas en Excel.
Supongamos que tengo un conjunto de datos que se muestra a continuación:
Para calcular el total de cada artículo, debemos multiplicar el precio de cada artículo por la cantidad de ese artículo.
Para el primer elemento, la fórmula en la celda D2 sería B2 * C2 (como se muestra a continuación):
Ahora, en lugar de ingresar la fórmula para todas las celdas una por una, simplemente puede copiar la celda D2 y pegarla en todas las demás celdas (D3: D8). Cuando lo haga, notará que la referencia de celda se ajusta automáticamente para referirse a la fila correspondiente. Por ejemplo, la fórmula en la celda D3 se convierte en B3 * C3 y la fórmula en D4 se convierte en B4 * C4.
Estas referencias de celda que se ajustan cuando se copia la celda se denominan referencias de celda relativas en Excel.
¿Cuándo usar referencias de celda relativas en Excel?
Las referencias de celda relativas son útiles cuando tiene que crear una fórmula para un rango de celdas y la fórmula debe hacer referencia a una referencia de celda relativa.
En tales casos, puede crear la fórmula para una celda y copiarla y pegarla en todas las celdas.
¿Qué son las referencias de celda absolutas en Excel?
A diferencia de las referencias de celda relativas, las referencias de celda absolutas no cambian cuando copia la fórmula en otras celdas.
Por ejemplo, suponga que tiene el conjunto de datos que se muestra a continuación, donde tiene que calcular la comisión por las ventas totales de cada artículo.
La comisión es del 20% y aparece en la celda G1.
Para obtener el monto de la comisión por cada venta de artículo, use la siguiente fórmula en la celda E2 y cópiela para todas las celdas:
= D2 * $ G $ 1
Tenga en cuenta que hay dos signos de dólar ($) en la referencia de celda que tiene la comisión: $GRAMO$2.
¿Qué hace el signo de dólar ($)?
Un símbolo de dólar, cuando se agrega delante del número de fila y columna, lo convierte en absoluto (es decir, evita que el número de fila y columna cambie cuando se copia a otras celdas).
Por ejemplo, en el caso anterior, cuando copio la fórmula de la celda E2 a E3, cambia de = D2 * $ G $ 1 a = D3 * $ G $ 1.
Tenga en cuenta que mientras D2 cambia a D3, $ G $ 1 no cambia.
Dado que hemos agregado un símbolo de dólar delante de "G" y "1" en G1, no dejaría que la referencia de celda cambie cuando se copia.
Por lo tanto, esto hace que la referencia de celda sea absoluta.
¿Cuándo usar referencias de celda absolutas en Excel?
Las referencias de celda absolutas son útiles cuando no desea que la referencia de celda cambie al copiar fórmulas. Este podría ser el caso cuando tiene un valor fijo que necesita usar en la fórmula (como tasa impositiva, tasa de comisión, número de meses, etc.)
Si bien también puede codificar este valor en la fórmula (es decir, usar 20% en lugar de $ G $ 2), tenerlo en una celda y luego usar la referencia de celda le permite cambiarlo en una fecha futura.
Por ejemplo, si su estructura de comisiones cambia y ahora paga el 25% en lugar del 20%, simplemente puede cambiar el valor en la celda G2 y todas las fórmulas se actualizarán automáticamente.
¿Qué son las referencias de celdas mixtas en Excel?
Las referencias de celda mixtas son un poco más complicadas que las referencias de celda absolutas y relativas.
Puede haber dos tipos de referencias de celda mixtas:
- La fila se bloquea mientras que la columna cambia cuando se copia la fórmula.
- La columna está bloqueada mientras que la fila cambia cuando se copia la fórmula.
Veamos cómo funciona con un ejemplo.
A continuación se muestra un conjunto de datos en el que debe calcular los tres niveles de comisión en función del valor porcentual en las celdas E2, F2 y G2.
Ahora puede usar el poder de la referencia mixta para calcular todas estas comisiones con una sola fórmula.
Ingrese la siguiente fórmula en la celda E4 y cópiela para todas las celdas.
= $ B4 * $ C4 * E $ 2
La fórmula anterior usa ambos tipos de referencias de celda mixtas (una donde la fila está bloqueada y otra donde la columna está bloqueada).
Analicemos cada referencia de celda y comprendamos cómo funciona:
- $ B4 (y $ C4) - En esta referencia, el signo de dólar está justo antes de la notación de columna, pero no antes del número de fila. Esto significa que cuando copia la fórmula en las celdas de la derecha, la referencia seguirá siendo la misma ya que la columna está fija. Por ejemplo, si copia la fórmula de E4 a F4, esta referencia no cambiará. Sin embargo, cuando lo copie, el número de fila cambiará ya que no está bloqueado.
- E $ 2 - En esta referencia, el signo de dólar está justo antes del número de fila y la notación de columna no tiene signo de dólar. Esto significa que cuando copia la fórmula en las celdas, la referencia no cambiará ya que el número de fila está bloqueado. Sin embargo, si copia la fórmula a la derecha, el alfabeto de las columnas cambiará porque no está bloqueado.
¿Cómo cambiar la referencia de relativa a absoluta (o mixta)?
Para cambiar la referencia de relativa a absoluta, debe agregar el signo de dólar antes de la notación de columna y el número de fila.
Por ejemplo, A1 es una referencia de celda relativa y se volvería absoluta cuando lo convierte en $ A $ 1.
Si solo tiene un par de referencias para cambiar, puede que le resulte fácil cambiar estas referencias manualmente. Entonces puede ir a la barra de fórmulas y editar la fórmula (o seleccionar la celda, presionar F2 y luego cambiarla).
Sin embargo, una forma más rápida de hacerlo es mediante el método abreviado de teclado: F4.
Cuando selecciona una referencia de celda (en la barra de fórmulas o en la celda en modo de edición) y presiona F4, cambia la referencia.
Suponga que tiene la referencia = A1 en una celda.
Esto es lo que sucede cuando selecciona la referencia y presiona la tecla F4.
- Presione la tecla F4 una vez: La referencia de celda cambia de A1 a $ A $ 1 (se convierte en "absoluta" de "relativa").
- Presione la tecla F4 dos veces: La referencia de celda cambia de A1 a A $ 1 (cambia a referencia mixta donde la fila está bloqueada).
- Presione la tecla F4 tres veces: La referencia de celda cambia de A1 a $ A1 (cambia a referencia mixta donde la columna está bloqueada).
- Presione la tecla F4 cuatro veces: La referencia de celda vuelve a ser A1.
