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.
Al escribir una fórmula de Excel, $ en las referencias de celda confunde a muchos usuarios. Pero la explicación es muy sencilla. El signo de dólar en una referencia de celda de Excel tiene solo un propósito: le indica a Excel si debe cambiar o no la referencia cuando la fórmula se copia a otras celdas. Y este breve tutorial proporciona todos los detalles sobre esta gran característica.
Difícilmente se puede subestimar la importancia de la referencia a las celdas de Excel. Obtenga información sobre la diferencia entre referencias absolutas, relativas y mixtas y estará a medio camino de dominar el poder y la versatilidad de las fórmulas y funciones de Excel.
Probablemente todos ustedes hayan visto el signo de dólar ($) en las fórmulas de Excel y se hayan preguntado de qué se trata. De hecho, puede hacer referencia a la misma celda de cuatro maneras diferentes, por ejemplo A1, $A$1, $A1 y A$1.
El signo de dólar en una referencia de celda de Excel afecta solo una cosa: le indica a Excel cómo tratar la referencia cuando la fórmula se mueve o copia a otras celdas. En pocas palabras, usar el signo $ antes de las coordenadas de fila y columna crea una referencia de celda absoluta que no cambiará. Sin el signo $, la referencia es relativa y cambiará.
Si está escribiendo una fórmula para una sola celda, puede utilizar cualquier tipo de referencia y obtener la fórmula correcta de todos modos. Pero si tiene la intención de copiar su fórmula a otras celdas, elegir el tipo de referencia de celda apropiado es crucial. Si te sientes afortunado, puedes lanzar una moneda 🙂 Si quieres hablar en serio, invierte unos minutos en aprender los entresijos de las referencias de celda absolutas y relativas en Excel, y cuándo usar cuál.
¿Qué es una referencia de celda de Excel?
En pocas palabras, una referencia de celda en Excel es una dirección de celda. Le indica a Microsoft Excel dónde buscar el valor que desea utilizar en la fórmula.
Por ejemplo, si ingresa una fórmula simple =A1 en la celda C1, Excel extraerá un valor de la celda A1 a C1:
Como ya se mencionó, siempre que escriba una fórmula para un unicelulareres libre de utilizar cualquier tipo de referencia, con o sin el signo de dólar ($), el resultado será el mismo:
Pero si quieres mover o Copiar la fórmula en la hoja de cálculo, es muy importante que elija el tipo de referencia correcto para que la fórmula se copie correctamente en otras celdas. Las siguientes secciones proporcionan una explicación detallada y ejemplos de fórmulas para cada tipo de referencia de celda.
Nota. Aparte de la estilo de referencia A1donde las columnas están definidas por letras y las filas por números, también existe el Estilo de referencia R1C1 donde tanto las filas como las columnas se identifican mediante números (R1C1 designa la fila 1, columna 1).
Debido a que A1 es el estilo de referencia predeterminado en Excel y se usa la mayor parte del tiempo, discutiremos solo las referencias de tipo A1 en este tutorial. Si alguien está usando actualmente el estilo R1C1, puede desactivarlo haciendo clic en Archivo > Opciones > Fórmulasy luego desmarcando la Estilo de referencia R1C1 caja.
Referencia de celda relativa de Excel (sin signo $)
A referencia relativa en Excel es una dirección de celda sin el signo $ en las coordenadas de fila y columna, como A1.
Cuando una fórmula con referencias de celda relativas se copia a otra celda, la referencia cambia según la posición relativa de filas y columnas. De forma predeterminada, todas las referencias en Excel son relativas. El siguiente ejemplo muestra cómo funcionan las referencias relativas.
Supongamos que tiene la siguiente fórmula en la celda B1:
=A1*10
Si copia esta fórmula a otra fila en la misma columna, digamos en la celda B2, la fórmula se ajustará a la fila 2 (A2*10) porque Excel supone que desea multiplicar un valor en cada fila de la columna A por 10.
Si copia la fórmula con una referencia de celda relativa a otra columna en la misma fila, Excel cambiará el referencia de columna respectivamente:
Y si copia o mueve una fórmula de Excel con una referencia de celda relativa a otra fila y otra columnaambos referencias de columnas y filas cambiará:
Como puede ver, usar referencias de celdas relativas en fórmulas de Excel es una forma muy conveniente de realizar los mismos cálculos en toda la hoja de cálculo. Para ilustrar mejor esto, analicemos un ejemplo de la vida real.
Usar referencia relativa es Excel: ejemplo de fórmula
Supongamos que tiene una columna de precios en USD (columna B) en su hoja de trabajo y desea convertirlos a EUR. Conociendo el tipo de conversión USD – EUR (0,93 al momento de escribir este artículo), la fórmula para la fila 2 es tan simple como =B2*0.93. Observe que estamos usando una referencia de celda relativa de Excel, sin el signo de dólar.
Al presionar la tecla Intro se calculará la fórmula y el resultado aparecerá inmediatamente en la celda.
Consejo. De forma predeterminada, todas las referencias de celda en Excel son referencias relativas. Entonces, al escribir una fórmula, puede agregar una referencia relativa haciendo clic en la celda correspondiente en la hoja de trabajo en lugar de escribir una referencia de celda manualmente.
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.
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.
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.
Usted también podría estar interesado en
Error 403 The request cannot be completed because you have exceeded your quota. : quotaExceeded
Preguntas frecuentes: ¿Por qué usar $ en la fórmula de Excel?
Excel es una herramienta ampliamente utilizada para realizar tareas de cálculo y análisis de datos en diferentes ámbitos profesionales. Cuando trabajamos con fórmulas en Excel, es común encontrarnos con el símbolo “$” utilizado en algunas ocasiones. En este artículo, responderemos algunas preguntas frecuentes sobre por qué utilizar “$” en una fórmula en Excel y cómo puede beneficiarnos.
1. ¿Qué significa el símbolo “$” en Excel?
El símbolo “$” es conocido como operador de fijación de referencia en Excel. Al utilizarlo en una celda o rango de celdas, estamos fijando esa celda o rango de celdas en una fórmula, lo que significa que su referencia no cambiará al copiar o arrastrar la fórmula a otras celdas.
2. ¿Por qué es importante utilizar “$” en Excel?
Utilizar el símbolo “$” en Excel puede resultar de gran utilidad cuando necesitamos realizar cálculos que involucran valores fijos. Si no utilizamos este símbolo, Excel ajustará automáticamente las referencias de celdas cuando se copie o arrastre una fórmula a otras celdas, lo que podría generar resultados incorrectos.
3. ¿En qué situaciones debo utilizar “$” en Excel?
Hay varias situaciones en las que es recomendable utilizar “$” en una fórmula en Excel, como por ejemplo:
- Referencias absolutas: Si deseamos fijar una referencia de celda en una fórmula y no queremos que cambie al copiarla, podemos utilizar “$” antes de la letra de la columna y/o el número de la fila. Por ejemplo, $A$1.
- Referencias relativas mixtas: Si deseamos fijar solo la columna o la fila en una fórmula y permitir que la otra se ajuste al copiarla, podemos utilizar “$” solo antes de la letra de la columna o el número de la fila. Por ejemplo, A$1 o $A1.
- Rangos absolutos: Si deseamos fijar un rango de celdas en una fórmula y evitar que cambie al copiarla, podemos utilizar “$” antes de la letra de la columna y/o el número de la fila en el primer y último elemento del rango. Por ejemplo, $A$1:$B$5.
4. ¿Cómo puedo aprender más sobre el uso de “$” en Excel?
Si deseas aprender más o profundizar tus conocimientos sobre el uso del símbolo “$” en Excel, puedes consultar las siguientes fuentes adicionales:
– Acerca de las celdas en Excel
– 13 trucos de Excel para aprovechar al máximo el programa de Microsoft
En conclusión, utilizar “$” en las fórmulas de Excel nos permite fijar referencias y rangos de forma precisa, evitando errores en nuestros cálculos. Aprovechar esta función puede ahorrarnos tiempo y garantizar resultados precisos en nuestras hojas de cálculo.