¿Te has encontrado alguna vez en medio de una hoja de cálculo de Excel, preguntándote por qué algunos valores llevan un símbolo de dólar ($) delante? Si creías que solo se trataba de un simple adorno, piénsalo de nuevo. Usar el símbolo de dólar en las fórmulas de Excel no solo es una cuestión de estilo, sino que puede transformar la manera en que trabajas con datos. En este artículo, exploraremos la magia del símbolo de dólar, desvelando sus secretos y mostrándote cómo puede aumentar tu eficacia y precisión al manejar hojas de cálculo. Prepárate para llevar tus habilidades de Excel al siguiente nivel. ¡Comencemos!
En el mundo laboral, Excel se ha convertido en una herramienta indispensable para la gestión de datos y el análisis de información. Sin embargo, a medida que profundizamos en sus funciones y fórmulas, es común encontrarnos con algunos símbolos y conceptos que pueden resultar confusos. Uno de ellos es el famoso signo de dólar ($), que a simple vista puede parecer un mero adorno en las fórmulas de Excel. ¡Pero nada más lejos de la realidad! En este artículo descubriremos por qué es tan importante utilizar el signo de dólar en las fórmulas de Excel y cómo puede facilitar nuestro trabajo diario.
A copia la fórmula en la columna, coloque el mouse sobre el controlador de relleno (un pequeño cuadrado en la esquina inferior derecha de la celda seleccionada). Al hacer esto, el cursor cambiará a una delgada cruz negra, y lo mantendrás presionado y lo arrastrarás sobre las celdas que deseas autocompletar.
>¡Eso es todo! La fórmula se copia a otras celdas con referencias relativas que se ajustan adecuadamente para cada celda individual. Para asegurarse de que un valor en cada celda se calcule correctamente, seleccione cualquiera de las celdas y vea la fórmula en la barra de fórmulas. En este ejemplo, seleccioné la celda C4 y veo que la referencia de celda en la fórmula es relativa a la fila 4, exactamente como debería ser:
>Referencia de celda absoluta de Excel (con signo $)
Un referencia absoluta en Excel es una dirección de celda con el signo de dólar ($) en las coordenadas de fila o columna, como $1$.
El signo de dólar fija la referencia a una celda determinada, de modo que permanece sin cambios no importa hacia dónde se mueva la fórmula. En otras palabras, usar $ en las referencias de celda le permite copiar la fórmula en Excel sin cambiar las referencias.
>Por ejemplo, si tienes 10 en la celda A1 y usas un referencia de celda absoluta ($1$), la formula =$A$1+5 siempre devolverá 15, sin importar en qué otras celdas se copie esa fórmula. Por otro lado, si escribes la misma fórmula con un referencia de celda relativa (A1) y luego cópielo en otras celdas de la columna; se calculará un valor diferente para cada fila. La siguiente imagen demuestra la diferencia:
>
Nota. Aunque hemos estado diciendo que una referencia absoluta en Excel nunca cambia, de hecho sí cambia cuando agregas o eliminas filas y/o columnas en tu hoja de cálculo, y esto cambia la ubicación de la celda a la que se hace referencia. En el ejemplo anterior, si insertamos una nueva fila en la parte superior de la hoja de cálculo, Excel es lo suficientemente inteligente como para ajustar la fórmula para reflejar ese cambio:
>
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.
Nota. Una referencia de celda absoluta no debe confundirse con el valor absoluto, que es la magnitud de un número sin tener en cuenta su signo.
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.
Consejo. Al escribir la fórmula, haga clic en la letra de la columna para agregar la referencia de toda la columna a la fórmula. Como es el caso con las referencias de celda, Excel inserta una referencia relativa (sin el signo $) de forma predeterminada:
>
De la misma forma escribimos una fórmula para calcular el precio medio en toda la columna B:
=AVERAGE(B:B)
En este ejemplo, utilizamos una referencia relativa de toda la columna, por lo que nuestra fórmula se ajusta correctamente cuando la copiamos a otras columnas:
>
Nota. Cuando utilice una referencia de columna completa en sus fórmulas de Excel, nunca ingrese la fórmula en ningún lugar dentro de la misma columna. Por ejemplo, podría parecer una buena idea ingresar la fórmula =SUMA(B:B) en una de las celdas vacías inferiores de la columna B para tener el total al final de la misma columna. ¡No hagas esto! Esto crearía lo que se llama referencia circular y la fórmula devolvería 0.
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:
Seleccione la celda con la fórmula.
Ingrese al modo de edición presionando el botón F2 o haga doble clic en la celda.
Seleccione la referencia de celda que desea cambiar.
Prensa F4 para alternar entre cuatro tipos de referencias de celda.
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.
Espero que ahora entiendas completamente qué son las referencias de celda relativas y absolutas, y que una fórmula de Excel con signos $ ya no sea un misterio. En los próximos artículos, continuaremos aprendiendo varios aspectos de las referencias de celdas de Excel, como hacer referencia a otra hoja de trabajo, referencia 3D, referencia estructurada, referencia circular, etc. Mientras tanto, te agradezco por leer y espero verte en nuestro blog la próxima semana.
Bhagwan: ¡Me encanta este artículo! La primera vez que usé el signo $ en Excel, me volví loco tratando de entender por qué no se movían las referencias al copiar fórmulas. Una vez que lo entendí, todo cambió y ahora no puedo vivir sin él. ¡Definitivamente un lifesaver!
Ugalde: Totalmente de acuerdo, Bhagwan! Yo también pasé un buen rato confundido con el tema del signo $, pero una vez que le agarré la onda, era como tener un superpoder en Excel. No sé cómo hacía antes, la verdad. ¡Es un must para cualquier que quiera trabajar más rápido!