Referencias de Celda – Absoluta, A1, F1C1, 3D, Circular en Excel y Google Sheets

Para realizar cálculos precisos en Excel, es esencial entender cómo funcionan los diferentes tipos de referencias de celda

 

Referencias A1 vs. F1C1

Las hojas de cálculo de Excel contienen muchas celdas y (por defecto) cada celda se identifica por su letra de columna seguida de su número de fila. Esto se conoce como Referencia Estilo A1. Ejemplos: A1, B4, C6

 

 

Opcionalmente, puedes cambiar al modo de Referencia F1C1 para referirte al número de fila y columna de una celda. En lugar de referirte a la celda A1 te referirías a F1C1 (fila 1, columna 1). La celda C4 se referiría a F4C3

 

El estilo de referencia F1C1 es extremadamente infrecuente en Excel. A menos que tengas una buena razón, probablemente deberías ceñirte al modo de referencia predeterminado de estilo A1. Sin embargo, si utilizas VBA es probable que te encuentres con este estilo de referencia

 

Cambiar al Estilo de Referencia F1C1

Para cambiar el estilo de referencia, vaya a Archivo > Opción > Fórmula. Marque la casilla junto a Estilo de referencia F1C1

 

Rangos con Nombre

Una de las funciones más infrautilizadas de Excel es la de Rangos con Nombre. En lugar de referirte a una celda (o grupo de celdas) por su ubicación (por ejemplo B3 o F3C2), puedes nombrar ese rango y simplemente hacer referencia al nombre del rango en tus fórmulas
Para nombrar un rango

1. Selecciona la celda o celdas que deseas nombrar
2. Haga clic dentro del Cuadro de Nombre
3. Introduzca el nombre deseado
4. Pulse Intro

 

Ahora puedes hacer referencia a la celda A1 escribiendo =Rango_Nombre1 en su lugar. Esto es muy útil cuando se trabaja con grandes libros de trabajo con múltiples hojas de cálculo.

 

Rango de Celdas

Cuando utilice las Funciones Incorporadas de Excel, puede necesitar hacer referencia a rangos de celdas. Los rangos de celdas aparecen así ‘A1:B3’. Esta referencia se refiere a todas las celdas entre A1 y B3: celdas A1,A2,A3, B1, B2, B3
Para seleccionar un rango de celdas al introducir una fórmula

• Escribe el rango (separa el rango inicial y final con dos puntos ‘:’ )
• Utiliza el ratón para hacer clic en la primera celda de referencia, mantén pulsado el botón del ratón y arrastra el rango que desees.
• Mantenga pulsada la tecla Mayús y desplácese con las teclas de flecha para seleccionar el rango

 

Referencias Absolutas (Congeladas) y Relativas

Cuando introduzcas referencias de celda en las fórmulas, puedes utilizar referencias relativas o absolutas (congeladas). Las referencias relativas se moverán proporcionalmente cuando copies la fórmula en una nueva celda. Las referencias absolutas no cambian. Veamos algunos ejemplos

Referencia Relativa

Una referencia relativa en Excel tiene el siguiente aspecto

=A1

Cuando copias y pegas una fórmula con referencias relativas, las referencias relativas se moverán proporcionalmente. A menos que especifiques lo contrario, tus referencias de celda serán relativas (no congeladas) por defecto
Ejemplo: Si copias ‘=A1’ una fila hacia abajo la referencia cambia a ‘=A2’

 

Referencias de Celda Absolutas (Congeladas)

Si no quieres que tus referencias de celda se muevan cuando copias una fórmula, puedes “congelar” tus referencias de celda añadiendo signos de dólar ($) delante de la referencia que quieres congelar. Ahora, cuando copies y pegues la fórmula, la referencia de celda permanecerá sin cambios. Puedes elegir congelar la referencia de la fila, la referencia de la columna o ambas

A1: No se congela nada

$A1: Se congela la columna, pero no la fila

A$1: Se congela la fila, pero no la columna

$A$1: Tanto la fila como la columna están congeladas

Método Abreviado de Referencia Absoluta

Añadir manualmente signos de dólar ($) en las fórmulas no es muy práctico. En su lugar, mientras crea su fórmula, utilice la tecla F4 para alternar entre referencias de celda absolutas/relativas

Ejemplo de Referencia Absoluta de Celda

¿Cuándo necesitaría realmente congelar una referencia de celda? Un ejemplo común es cuando tiene celdas de entrada que son referenciadas frecuentemente. En el siguiente ejemplo, queremos calcular el impuesto sobre las ventas para cada cantidad de artículos del menú. El impuesto sobre las ventas es constante en todos los artículos, por lo que haremos referencia a la celda del impuesto sobre las ventas repetidamente.

Para encontrar el total del impuesto sobre las ventas, introduzca la fórmula ‘=(B4*C4)*$C$1’ en la columna D y Copie la fórmula hacia abajo

Referencia Mixta

Puede que hayas oído hablar de las referencias de celdas mixtas. Una referencia mixta es cuando la referencia de la fila o de la columna está bloqueada (pero no ambas)

Recuerde, usando la tecla “F4” usted es capaz de recorrer sus referencias de celda relativas y absolutas

Referencias de Celda – Insertar y Borrar Filas/Columnas

Tal vez se pregunte qué sucede con sus referencias de celda cuando inserta o borra filas/columnas
La referencia de celda se actualizará automáticamente para referirse a la celda original. Esto es así independientemente de si la referencia de celda está congelada

Referencias 3D

En ocasiones, puede que necesite trabajar con varias hojas de cálculo con patrones de datos idénticos. Excel te permite hacer referencia a varias hojas a la vez sin necesidad de introducir manualmente cada hoja de trabajo. Puedes referenciar un rango de hojas de forma similar a como referenciarías un rango de celdas. Ejemplo ‘Hoja2!A1+Hoja3!A1’ haría referencia a las celdas A1 en todas las hojas desde la Hoja2 hasta la Hoja3

Veamos un ejemplo

Quieres sumar el total de unidades vendidas de cada producto en todas las tiendas. Cada tienda tiene su propia hoja de cálculo y todas las hojas de cálculo tienen un formato idéntico. Podrías crear una fórmula similar a esta:

 

Esto no es demasiado difícil con sólo tres hojas de cálculo, pero ¿qué pasaría si tuviera 40 hojas de cálculo? ¿Realmente querrías añadir manualmente cada referencia de celda?

En su lugar, puedes utilizar una referencia 3D para referenciar múltiples hojas a la vez con facilidad (de forma similar a como puedes referenciar un rango de celdas)

Cuidado El orden de las hojas de cálculo es importante. Si mueve otra hoja entre las hojas referenciadas (AlmacénA y AlmacénC) esa hoja será incluida. Por el contrario, si mueve una hoja fuera del rango de hojas (antes de AlmacénA o después de AlmacénC), ya no se incluirá

 

Referencia de Celda Circular

Una referencia de celda circular es cuando una celda hace referencia a sí misma. Por ejemplo, si el resultado de la celda B1 se utiliza como entrada para la celda B1, se crea una referencia circular. No es necesario que la celda se refiera directamente a sí misma, puede haber pasos intermedios

Por ejemplo

 

En este caso, la fórmula de la celda B2 es “=A2+A3+B2”. Como se encuentra en la celda B2, no puede utilizar B2 en la ecuación. Esto activaría la referencia circular y el valor de la celda “B2” se pondrá automáticamente a “0”
Normalmente, las referencias circulares son el resultado de un error del usuario, pero hay circunstancias en las que puede ser conveniente utilizar una referencia circular. El principal ejemplo de uso de una referencia circular es el cálculo iterativo de valores. Para ello, debe ir a Archivo > Opciones > Fórmulas y activar Cálculo iterativo

 

 

Referencias Externas

A veces, cuando se calculan datos, puede ser necesario hacer referencia a datos externos al libro de trabajo. Esto se denomina referencia externa (enlace)

Para seleccionar una referencia externa mientras creas una fórmula, navega al libro de trabajo externo y selecciona tu referencia como lo harías normalmente

Para navegar al otro libro puedes utilizar el atajo de teclado CTRL TAB o ir a Vista > Cambiar Ventanas

 

Una vez que hayas seleccionado la celda, verás que aparece una referencia externa con este aspecto:

 

Observa que el nombre del libro de trabajo está entre corchetes []

Una vez que cierres el libro de trabajo referenciado, la referencia mostrará la ubicación del archivo

 

Cuando vuelva a abrir el libro que contiene el enlace externo, se le pedirá que active la actualización automática de los enlaces. Si lo hace, Excel abrirá el valor de referencia con el valor actual del libro. ¡Aunque esté cerrado! Tenga cuidado Puede o no querer esto

Rangos Nombrados y Referencias Externas

¿Qué ocurre con su referencia de celda externa cuando se añaden o eliminan filas / columnas del libro de referencia? Si ambos libros están abiertos, las celdas de referencia se actualizan automáticamente. Sin embargo, si ambos libros no están abiertos, las referencias de celda no se actualizarán y dejarán de ser válidas. Esta es una gran preocupación cuando se enlaza a libros de trabajo externos. Esto provoca muchos errores
Si enlazas a libros de trabajo externos, deberías nombrar la celda de referencia con un rango nombrado (ver la sección anterior para más información). Ahora su fórmula se referirá al rango nombrado sin importar los cambios que ocurran en el libro de trabajo externo.