En hojas de cálculo reales, es muy raro que utilices solo referencias absolutas en tu fórmula de Excel. Sin embargo, hay muchas tareas que requieren el uso de referencias absolutas y relativas, como se demuestra en los siguientes ejemplos.

Usar referencias de celda relativas y absolutas en una fórmula

Muy a menudo es posible que necesite una fórmula en la que algunas referencias de celda se ajusten para las columnas y filas donde se copia la fórmula, mientras que otras permanecen fijas en celdas específicas. En otras palabras, debes utilizar referencias de celda relativas y absolutas en una única fórmula.

Ejemplo 1. Referencias de celda relativas y absolutas para calcular números

En nuestro ejemplo anterior con precios en USD y EUR, es posible que no desee codificar el tipo de cambio en la fórmula. En su lugar, puede ingresar ese número en alguna celda, digamos C1, y corregir la referencia de esa celda en la fórmula usando el signo de dólar ($), como se muestra en la siguiente captura de pantalla:

>En esta fórmula (B4*$C$1), hay dos tipos de referencia de celda:

• B4 – relativo referencia de celda que se ajusta para cada fila, y
• $C$1 – absoluto referencia de celda que nunca cambia sin importar dónde se copie la fórmula.

Una ventaja de este enfoque es que sus usuarios pueden calcular los precios en EUR basándose en un tipo de cambio variable sin cambiar la fórmula. Una vez que cambia la tasa de conversión, todo lo que tienes que hacer es actualizar el valor en la celda C1.

Ejemplo 2. Referencias de celda relativas y absolutas para calcular fechas

Otro uso común de referencias de celda absolutas y relativas en una sola fórmula es calcular fechas en Excel basándose en la fecha de hoy.

Supongamos que tiene una lista de fechas de entrega en la columna B e ingresa la fecha actual en C1 usando la función HOY(). Lo que desea saber es en cuántos días se envía cada artículo y puede calcularlo utilizando la siguiente fórmula: =B4-$C$1

>Y nuevamente usamos dos tipos de referencia en la fórmula:

• Relativo para la celda con la primera fecha de entrega (B4), porque desea que esta referencia de celda varíe dependiendo de la fila donde reside la fórmula.
• Absoluto para la celda con la fecha de hoy ($C$1), porque desea que esta referencia de celda permanezca constante.

Para concluir, siempre que desee crear una referencia de celda estática de Excel que siempre haga referencia a la misma celda, asegúrese de incluir el signo de dólar ($) en su fórmula para crear una referencia absoluta en Excel.

Referencia de celda mixta de Excel

Una referencia de celda mixta en Excel es una referencia en la que se fija la letra de la columna o el número de la fila. Por ejemplo, $A1 y A$1 son referencias mixtas. Pero ¿qué significa cada uno? Es muy sencillo.

Como recordarás, un referencia absoluta de excel contiene 2 signos de dólar ($) que bloquean tanto la columna como la fila. En una referencia de celda mixta, solo una coordenada es fija (absoluta) y la otra (relativa) cambiará según una posición relativa de la fila o columna:

• Columna absoluta y fila relativa., como $A1. Cuando una fórmula con este tipo de referencia se copia a otras celdas, el signo $ delante de la letra de la columna bloquea la referencia a la columna especificada para que nunca cambie. La referencia de fila relativa, sin el signo de dólar, varía según la fila en la que se copia la fórmula.
• Columna relativa y fila absoluta., como 1 dólar australiano. En este tipo de referencia, es la referencia de la fila la que no cambiará y la referencia de la columna sí.

>A continuación encontrará un ejemplo del uso de ambos tipos de referencias de celdas mixtas que, con suerte, facilitará la comprensión.

Uso de una referencia mixta en Excel: ejemplo de fórmula

Para este ejemplo, usaremos nuestro tabla de conversión de moneda de nuevo. Pero esta vez no nos limitaremos sólo a la conversión USD – EUR. Lo que vamos a hacer es convertir los precios del dólar a varias otras monedas, todo con un fórmula única!

Para empezar, ingresemos las tasas de conversión en alguna fila, digamos la fila 2, como se muestra en la captura de pantalla a continuación. Y luego, escribe solo una fórmula para la celda superior izquierda (C5 en este ejemplo) para calcular el precio en EUR:

=$B5*C$2

Donde $B5 es el precio en dólares en la misma fila y C$2 es el tipo de conversión de USD a EUR.

>Y ahora, copie la fórmula a otras celdas en la columna C, y luego complete automáticamente otras columnas con la misma fórmula arrastrando el controlador de relleno. Como resultado, tendrá 3 columnas de precios diferentes calculadas correctamente en función del tipo de cambio correspondiente en la fila 2 de la misma columna. Para verificar esto, seleccione cualquier celda de la tabla y vea la fórmula en la barra de fórmulas.

Por ejemplo, seleccionemos la celda D7 (en la columna GBP). Lo que vemos aquí es la fórmula. =$B7*D$2 eso toma un precio en USD en B7 y lo multiplica por el valor en D2, que es la tasa de conversión USD-GBP, justo lo que recetó el médico 🙂

>Y ahora, entendamos por qué Excel sabe exactamente qué precio tomar y por qué tipo de cambio multiplicarlo. Como habrás adivinado, son las referencias de celdas mixtas las que funcionan ($B5*C$2).

• $B5 – columna absoluta y fila relativa. Aquí agrega el signo de dólar ($) solo antes de la letra de la columna para anclar la referencia a la columna A, por lo que Excel siempre usa los precios originales en USD para todas las conversiones. La referencia de fila (sin el signo $) no está bloqueada porque desea calcular los precios para cada fila individualmente.
• 2$ – columna relativa y fila absoluta. Debido a que todos los tipos de cambio residen en la fila 2, bloquea la referencia de la fila colocando el signo de dólar ($) delante del número de fila. Y ahora, no importa en qué fila copie la fórmula, Excel siempre buscará el tipo de cambio en la fila 2. Y como la referencia de la columna es relativa (sin el signo $), se ajustará para la columna a la que se aplica la fórmula. copiado.

Cómo hacer referencia a una columna o fila completa en Excel

Cuando trabaja con una hoja de cálculo de Excel que tiene un número variable de filas, es posible que desee hacer referencia a todas las celdas dentro de una columna específica. Para hacer referencia a toda la columna, simplemente escriba una letra de la columna dos veces y dos puntos en el medio, por ejemplo AUTOMÓVIL CLUB BRITÁNICO.

Una referencia de toda la columna

Además de las referencias de celda, una referencia de columna completa puede ser absoluta y relativa, por ejemplo:

• Referencia de columna absoluta, como $A:$A
• Referencia de columna relativa, como A:A

Y nuevamente, usas el signo de dólar ($) en un referencia de columna absoluta para bloquearlo en una determinada columna, para que la referencia de toda la columna no cambie cuando copia una fórmula a otras celdas.

A referencia de columna relativa cambiará cuando la fórmula se copie o se mueva a otras columnas y permanecerá intacta cuando copie la fórmula a otras celdas dentro de la misma columna.

Una referencia de fila completa

Para hacer referencia a la fila completa, utiliza el mismo enfoque excepto que escribe números de fila en lugar de letras de columna:

• Referencia de fila absoluta, como $1:$1
• Referencia de fila relativa, como 1:1

En teoría, también puedes crear un referencia mixta de toda la columna o mezclado completo–referencia de fila, como $A:A o $1:1, respectivamente. Digo «en teoría» porque no se me ocurre ninguna aplicación práctica de tales referencias, aunque Ejemplo 4 demuestra que las fórmulas con tales referencias funcionan exactamente como se supone que deben hacerlo.

Ejemplo 1. Referencia de columna completa de Excel (absoluta y relativa)

Supongamos que tiene algunos números en la columna B y desea conocer su total y promedio. El problema es que cada semana se agregan nuevas filas a la tabla, por lo que escribir una fórmula SUM() o PROMEDIO() habitual para un rango fijo de celdas no es el camino a seguir. En su lugar, puede hacer referencia a toda la columna B:

=SUM($B:$B) – usa el signo de dólar ($) para hacer un absoluto referencia de toda la columna que bloquea la fórmula en la columna B.

=SUM(B:B) – escribe la fórmula sin $ para hacer un relativo referencia de toda la columna eso se cambiará a medida que copie la fórmula a otras columnas.

Ejemplo 2. Referencia de fila completa de Excel (absoluta y relativa)

Si los datos de su hoja de Excel están organizados en filas en lugar de columnas, entonces puede hacer referencia a una fila completa en su fórmula. Por ejemplo, así es como podemos calcular un precio promedio en la fila 2:

=AVERAGE($2:$2) – un absoluto referencia de fila completa está bloqueado en una fila específica mediante el uso del signo de dólar ($).

=AVERAGE(2:2) – a relativo referencia de fila completa cambiará cuando la fórmula se copie a otras filas.

En este ejemplo, necesitamos una referencia relativa de fila completa porque tenemos 3 filas de datos y queremos calcular un promedio en cada fila copiando la misma fórmula:

>Ejemplo 3. Cómo hacer referencia a una columna completa excluyendo las primeras filas

Este es un problema muy actual, porque muy a menudo las primeras filas de una hoja de trabajo contienen alguna cláusula introductoria o información explicativa y no desea incluirlas en sus cálculos. Lamentablemente, Excel no permite referencias como B5:B que incluirían todas las filas de la columna B comenzando con la fila 5. Si intenta agregar dicha referencia, lo más probable es que su fórmula devuelva el error #NOMBRE.

En su lugar, puede especificar un fila máxima, para que su referencia incluya todas las filas posibles en una columna determinada. En Excel 2016, 2013, 2010 y 2007, un máximo es 1.048.576 filas y 16.384 columnas. Las versiones anteriores de Excel tenían un máximo de filas de 65.536 y un máximo de columnas de 256.

Entonces, para encontrar un promedio para cada columna de precio en la siguiente tabla (columnas B a D), ingrese la siguiente fórmula en la celda F2 y luego cópiela en las celdas G2 y H2:

=AVERAGE(B5:B1048576)

>Si está utilizando la función SUMA, también puede restar las filas que desea excluir:

=SUM(B:B)-SUM(B1:B4)

Ejemplo 4. Uso de una referencia mixta de columna completa en Excel

Como mencioné unos párrafos antes, también puedes hacer una referencia mixta de toda la columna o de toda la fila en Excel:

• Referencia de columnas mixtas, como $A:A
• Referencia de fila mixta, como $1:1

Ahora, veamos qué sucede cuando copias una fórmula con tales referencias a otras celdas. Suponiendo que ingresas la fórmula =SUM($B:B) en alguna celda, F2 en este ejemplo. Cuando copia la fórmula en la celda adyacente de la derecha (G2), cambia a =SUM($B:C) porque la primera B se fija con el signo $, mientras que la segunda no. Como resultado, la fórmula sumará todos los números en las columnas B y C. No estoy seguro de si esto tiene algún valor práctico, pero es posible que desee saber cómo funciona:

>¡Una palabra de precaución! No utilice demasiadas referencias de columnas o filas completas en una hoja de cálculo porque pueden ralentizar su Excel.

Cómo cambiar entre referencias absolutas, relativas y mixtas (tecla F4)

Cuando escribe una fórmula de Excel, el signo $, por supuesto, se puede escribir manualmente para cambiar una referencia de celda relativa a absoluta o mixta. O puedes presionar el F4 clave para acelerar las cosas. Para que funcione el atajo F4, debes estar en el modo de edición de fórmulas:

Si ha seleccionado una referencia de celda relativa sin el signo $, como A1, presionando repetidamente el botón F4 La tecla alterna entre una referencia absoluta con ambos signos de dólar como $A$1, fila absoluta A$1, columna absoluta $A1 y luego regresa a la referencia relativa A1.

>

Nota. Si presiona F4 sin seleccionar ninguna referencia de celda, la referencia a la izquierda del puntero del mouse se seleccionará automáticamente y se cambiará a otro tipo de referencia.