Los nombres de los rangos de celdas de Excel son una de estas viejas características del Excel que pocos usuarios entienden. Aprende como asignar nombre a rangos.
Pero los nombres de rangos son en realidad una característica muy buena. Pueden hacer que las fórmulas sean mucho más fáciles de crear, leer y mantener. Y como beneficio adicional, hacen que las fórmulas sean más fáciles de reutilizar.
De hecho, utilizo nombres de rangos todo el tiempo al probar y crear prototipos de fórmulas. Me ayudan a que las fórmulas funcionen más rápido.
¿Qué es un rango con nombre?
Un nombre de rango es solo un nombre legible para humanos para un rango de celdas en Excel. Por ejemplo, si nombro el rango A1: A100 como «datos», puedo usar MAX para obtener el valor máximo con una fórmula simple:
= MAX (datos) // valor máximo
Rango simple llamado llamado «datos»
La belleza de los nombres de rangos es que puedes usar nombres significativos en tus fórmulas sin pensar en las referencias de celda. Una vez que tengas un nombre de rango, simplemente úsalo como una referencia de celda. Todas estas fórmulas son válidas con el rango llamado «datos»:
= MAX (datos) // valor máximo
= MIN (datos) // valor mínimo
= COUNT (datos) // valores totales
= PROMEDIO (datos) // valor mínimo
Crear un rango con nombre es fácil
Crear un nombre de rango es rápido y fácil. Simplemente selecciona un rango de celdas y escribe un nombre en el cuadro de nombre. Cuando presionas return, se crea el nombre:
Crear un rango con nombre rápido con el cuadro de nombre
Para probar rápidamente el nuevo rango, elije el nuevo nombre en el menú desplegable al lado del cuadro de nombre. Excel seleccionará el rango en la hoja de trabajo.
Excel puede crear nombres automáticamente (ctrl + shift + F3)
Si tienes datos bien estructurados con etiquetas, puedes hacer que Excel cree rangos con nombre para ti. Simplemente selecciona los datos, junto con las etiquetas, y usa el comando «Crear desde la selección» en la pestaña Fórmulas de la cinta:
Crear nombres del comando de selección en la cinta
También puede usar el control de atajo de teclado + shift + F3.
Al usar esta función, podemos crear rangos con nombre para la población de 12 estados en un solo paso:
Crear nombres de la selección con datos y etiquetas seleccionadas
Cuando haces clic en Aceptar, se crean los nombres. Encontrarás todos los nombres nuevos en el menú desplegable al lado del cuadro de nombre:
Los nuevos nombres también aparecen en el menú desplegable del cuadro de nombre
Con los nombres creados, puede usarlos en fórmulas como esta
= SUM (MN, WI, MI)
Actualizar rangos nombrados en el Administrador de nombres (Control + F3)
Una vez que hayas creado un nombre de rango, usa el Administrador de nombres (Control + F3) para actualizarlo según sea necesario. Selecciona el nombre con el que deseas trabajar, luego cambia la referencia directamente (es decir, edita «se refiere a»), o haz clic en el botón derecho y selecciona un nuevo rango.
Actualizar nombres de rangos con el Administrador de nombres
No es necesario hacer clic en el botón Editar para actualizar una referencia. Al hacer clic en Cerrar, se actualizará el nombre del rango.
Nota: si seleccionas un rango con nombre completo en una hoja de trabajo, puedes arrastrarlo a una nueva ubicación y la referencia se actualizará automáticamente. Sin embargo, no conozco una forma de ajustar las referencias de rango haciendo clic y arrastrando directamente en la hoja de trabajo
Ver todos los nombres de rangos (control + F3)
Para ver rápidamente todos los rangos nombrados en un libro, use el menú desplegable al lado del cuadro de nombre.
Si deseas ver más detalles, abre el Administrador de nombres (Control + F3), que enumera todos los nombres con referencias, y proporciona también un filtro:
El administrador de nombres muestra todos los nombres nuevos
Copia y pega todos los rangos nombrados (F3)
Si deseas un registro más persistente de rangos con nombre en un libro, puedes pegar la lista completa de nombres en cualquier lugar que desees. Ves a Fórmulas> Usar en fórmula (o usa el acceso directo F3), luego elije Pegar nombres> Pegar lista:
Cuadro de diálogo Pegar nombres
Al hacer clic en el botón Pegar lista, verás los nombres y referencias pegadas en la hoja de trabajo:
Después de pegar rangos con nombre en la hoja de trabajo
Ver el nombre directamente en la hoja de trabajo
Si configuras el nivel de zoom a menos del 40%, Excel mostrará los nombres de rango directamente en la hoja de trabajo:
En el nivel de zoom <40%, Excel mostrará nombres de rango
Los nombres tienen reglas
Al crear rangos con nombre, siga estas reglas:
- Los nombres deben comenzar con una letra, un guión bajo (_) o una barra invertida (\)
- Los nombres no pueden contener espacios y la mayoría de los caracteres de puntuación.
- Los nombres no pueden entrar en conflicto con las referencias de celda; no puede nombrar un rango «A1» o «Z100».
- Las letras individuales son correctas para los nombres («a», «b», «c», etc.), pero las letras «r» y «c» están reservadas.
- Los nombres no distinguen entre mayúsculas y minúsculas: «home», «HOME» y «HoMe» son todos iguales para Excel.
Rangos nombrados en fórmulas
Los nombres de rangos son fáciles de usar en fórmulas
Por ejemplo, supongamos que nombras una celda en tu libro de trabajo «actualizada». La idea es que puedas poner la fecha actual en la celda (Ctrl +;) y referirte a la fecha en otro lugar en el libro de trabajo.
Usar un rango con nombre dentro de una fórmula de texto
La fórmula en B8 se ve así:
= «Actualizado:» & TEXT (actualizado, «ddd, mmmm d, yyyy»)
Puedes pegar esta fórmula en cualquier lugar del libro y se mostrará correctamente. Siempre que cambies la fecha en «actualizado», el mensaje se actualizará donde sea que se uses la fórmula.
Los nombres de rangos aparecen cuando se escribe una fórmula
Una vez que hayas creado un nombre de rango, aparecerá automáticamente en las fórmulas cuando escribas la primera letra del nombre. Presiona la tecla de tabulación para ingresar el nombre cuando tengas una coincidencia y deseas que Excel ingrese el nombre.
Los rangos nombrados aparecen al ingresar fórmulas
Los rangos con nombre pueden funcionar como constantes
Debido a que los rangos con nombre se crean en una ubicación central, puedes usarlos como constantes sin una referencia de celda. Por ejemplo, puedes crear nombres como «MPG» (millas por galón) y «CPG» (costo por galón) con y asignar valores fijos:
Los nombres de rangos pueden funcionar como constantes, sin referencia de celda
Luego puedes usar estos nombres en cualquier lugar que quieras en fórmulas y actualizar su valor en una ubicación central.
Usa un rango con nombre como una constante en una fórmula
Los nombres de rangos son absolutos por defecto
Por defecto, los nombres de rangos se comportan como referencias absolutas. Por ejemplo, en esta hoja de trabajo, la fórmula para calcular el combustible sería:
= C5 / $ D $ 2
Fórmula estándar con dirección absoluta
La referencia a D2 es absoluta (bloqueada) por lo que la fórmula se puede copiar sin cambiar D2.
Si nombramos D2 «MPG», la fórmula se convierte en:
= C5 / MPG
Usar un rango con nombre como una constante en una fórmula
Como MPG es absoluto por defecto, la fórmula se puede copiar en la columna D tal como está.
Los rangos nombrados también pueden ser relativos
Aunque los rangos con nombre son absolutos por defecto, también pueden ser relativos. Un rango relativo relativo se refiere a un rango que es relativo a la posición de la celda activa en el momento en que se crea el rango. Como resultado, los rangos con nombre relativo son útiles para construir fórmulas genéricas que funcionen donde sea que se muevan.
Por ejemplo, puedes crear un rango genérico llamado «CellAbove» como este:
Selecciona celda A2
Control + F3 para abrir el Administrador de nombres
Haz clic en la sección «Se refiere a», luego escribe: = A1
CellAbove ahora recuperará el valor de la celda de arriba donde sea que esté.
Importante: asegúrese de que la celda activa esté en la ubicación correcta antes de crear el nombre.
Aplicar rangos con nombre a las fórmulas existentes
Si tienes fórmulas existentes que no usan rangos con nombre, puedes pedirle a Excel que aplique los rangos nombrados en las fórmulas por usted. Comienza por seleccionar las celdas que contienen las fórmulas que deseas actualizar. Luego ejecuta Fórmulas> Definir nombres> Aplicar nombres.
El cuadro de diálogo Aplicar nombres
Excel reemplazará las referencias que tengan un rango con nombre correspondiente con el nombre mismo.
También puede aplicar nombres con Buscar y reemplazar:
Aplicar rangos de nombres con Buscar y reemplazar
Importante: Guarda una copia de seguridad de su hoja de cálculo y selecciona solo las celdas que deseas cambiar antes de usar Buscar y reemplazar en fórmulas.
Beneficios clave de rangos con nombre
Los rangos con nombre hacen que las fórmulas sean más fáciles de leer
El mayor beneficio individual para los rangos con nombre es que hacen que las fórmulas sean más fáciles de leer y mantener. Esto es porque reemplazan referencias crípticas con nombres significativos. Por ejemplo, considere esta hoja de trabajo con datos sobre planetas en nuestro sistema solar. Sin rangos con nombre, una fórmula VLOOKUP para obtener «Posición» de la tabla es bastante críptica:
= BUSCARV ($ H $ 4, $ B $ 3: $ E $ 11,2,0)
Sin rangos nombrados, las fórmulas pueden ser crípticas
Sin embargo, con B3: E11 llamado «datos», y H4 llamado «planeta», podemos escribir fórmulas como esta:
= VLOOKUP (planeta, datos, 2,0) // posición
= VLOOKUP (planeta, datos, 3,0) // diámetro
= VLOOKUP (planeta, datos, 4,0) // satélites
Con rangos con nombre, las fórmulas pueden ser simples
A simple vista, puede ver la única diferencia en estas fórmulas en el índice de columnas.
Los rangos nombrados hacen que las fórmulas sean portátiles y reutilizables
Los rangos con nombre pueden hacer que sea mucho más fácil reutilizar una fórmula en una hoja de cálculo diferente. Si defines nombres con anticipación en una hoja de cálculo, puede pegar una fórmula que use estos nombres y «simplemente funcionará». Esta es una gran manera de obtener rápidamente una fórmula de trabajo.
Por ejemplo, esta fórmula cuenta valores únicos en un rango de datos numéricos:
= SUMA (- (FRECUENCIA (datos, datos)> 0))
Para «portar» rápidamente esta fórmula a tu propia hoja de trabajo, asigna un rango de «datos» y pega la fórmula en la hoja de trabajo. Siempre que «datos» contenga valores numéricos, la fórmula funcionará de inmediato.
Consejo: Recomiendo que crees los nombres de rango necesarios primero en el libro de trabajo de destino, luego copia en la fórmula como texto solamente (es decir, no copies la celda que contiene la fórmula en otra hoja de trabajo, simplemente copia el texto de la fórmula). Esto evita que Excel cree nombres sobre la marcha y te permite controlar completamente el proceso de creación de nombres. Para copiar solo texto de fórmula, copia texto de la barra de fórmulas o cópialo a través de otra aplicación (por ejemplo, navegador, editor de texto, etc.).
Los rangos con nombre se pueden usar para la navegación
Los rangos con nombre son geniales para una navegación rápida. Simplemente selecciona el menú desplegable junto al cuadro de nombre y elije un nombre. Cuando sueltes el ratón, se seleccionará el rango. Cuando exista un rango con nombre en otra hoja, será llevado a esa hoja automáticamente.
Rangos con nombre permiten una navegación simple
Los rangos nombrados funcionan bien con hipervínculos
Los rangos nombrados hacen que los hipervínculos sean fáciles. Por ejemplo, si nombra A1 en la Hoja 1 «inicio», puede crear un hipervínculo en otro lugar que lo lleve de regreso.
Crear un hipervínculo a un rango con nombre
Ejemplo de hipervínculo de rango con nombre en la hoja de trabajo
Para usar un rango con nombre dentro de la función HYPERLINK, agrega un símbolo de libra delante del rango con nombre:
= HYPERLINK («# home», «llévame a casa»)
Nota: curiosamente, no se puede poner un hipervínculo a una tabla como se puede hacer con un nombre de rango normal. Sin embargo, puedes definir un nombre igual a una tabla (es decir, Tabla1) y poner un hipervínculo a eso. Si alguien conoce una forma de vincular directamente una tabla, ¡que hable ahora o calle para siempre! J
Rangos nombrados para la validación de datos
Los rangos de nombres funcionan bien para la validación de datos, ya que te permiten usar una referencia con un nombre lógico para validar la entrada con un menú desplegable. A continuación, el rango G4: G8 se denomina «lista de estado», luego aplicas la validación de datos con una lista vinculada así:
Usar un rango con nombre para la validación de datos con lista
El resultado es un menú desplegable en la columna E que solo permite valores en el rango con nombre:
Validación de datos con un ejemplo de rango con nombre
Rangos nominales dinámicos
Los rangos de nombres son extremadamente útiles cuando se ajustan automáticamente a los datos nuevos en una hoja de trabajo. Un rango configurado de esta manera se conoce como «rango con nombre dinámico». Hay dos formas de hacer un rango dinámico: fórmulas y tablas.
Rango llamado dinámico con una tabla
Una tabla es la forma más fácil de crear un rango con nombre dinámico. Selecciona cualquier celda en los datos, luego use el atajo Control + T:
Creando una tabla de Excel
Cuando creas una tabla de Excel, se crea automáticamente un nombre (por ejemplo, Table1), pero puede cambiar el nombre de la tabla como desees. Una vez que hayas creado una tabla, se expandirá automáticamente cuando se agreguen datos.
Las tablas se expandirán automáticamente y pueden renombrarse
Rango llamado dinámico con una fórmula
También puede crear un rango con nombre dinámico con fórmulas, usando funciones como OFFSET e INDEX. Aunque estas fórmulas son moderadamente complejas, proporcionan una solución liviana cuando no deseas utilizar una tabla. Los enlaces a continuación brindan ejemplos con explicaciones completas:
Ejemplo de fórmula de rango dinámico con ÍNDICE
Ejemplo de fórmula de rango dinámico con OFFSET
Eliminar rangos con nombre
Nota: Si tiene fórmulas que hacen referencia a rangos con nombre, es posible que desee actualizar primero las fórmulas antes de eliminar nombres. De lo contrario, verá #NAME? errores en fórmulas que todavía se refieren a nombres eliminados. Guarde siempre su hoja de trabajo antes de eliminar rangos con nombre en caso de que tenga problemas y necesite volver al original.
Los rangos con nombre se ajustan al eliminar e insertar celdas
Cuando elimina parte de un rango con nombre, o si insertas celdas / filas / columnas dentro de un rango con nombre, la referencia de rango se ajustará en consecuencia y seguirá siendo válida. Sin embargo, si eliminas todas las celdas que encierran un rango con nombre, el rango nombrado perderá la referencia y mostrará un error #REF. Por ejemplo, si llamo A1 «prueba», luego elimino la columna A, el administrador de nombres mostrará «se refiere a» como:
= Hoja1! #REF!
Eliminar nombres con Name Manager
Para eliminar rangos con nombre de un libro de trabajo manualmente, abre el administrador de nombres, selecciona un rango y haz clic en el botón Eliminar. Si deseas eliminar más de un nombre al mismo tiempo, puedes presionar Mayús + clic o Ctrl + clic para seleccionar varios nombres y luego eliminar en un solo paso.
Eliminar nombres con errores
Si tienes muchos nombres con errores de referencia, puedes usar el botón de filtro en el administrador de nombres para filtrar los nombres con errores:
Menú de filtro de nombre de administrador
A continuación, cambia + clic para seleccionar todos los nombres y eliminar.
Rangos nombrados y Alcance
Los rangos con nombre en Excel tienen algo llamado «alcance», que determina si un rango con nombre es local para una hoja de trabajo determinada o global en todo el libro de trabajo. Los nombres globales tienen un alcance de «libro de trabajo», y los nombres locales tienen un alcance igual al nombre de la hoja en que existen. Por ejemplo, el alcance de un nombre local podría ser «Hoja2».
El propósito del alcance
Los rangos con nombre con un alcance global son útiles cuando desea que todas las hojas de un libro de trabajo tengan acceso a ciertos datos, variables o constantes. Por ejemplo, puedes usar un rango con nombre global, una suposición de tasa impositiva utilizada en varias hojas de cálculo.
Los rangos con nombre de ámbito local
Los rangos con nombre de ámbito local tienen sentido para las hojas de trabajo que usan rangos con nombre solo para suposiciones locales. Por ejemplo, tal vez tengas un libro de trabajo con hojas de seguimiento mensuales (una por mes) que use rangos con nombre con el mismo nombre, todos con un alcance local. El alcance local permite que los nombres en cada hoja funcionen correctamente sin colisionar con nombres en las otras hojas.
Para hacer referencia a un nombre con un ámbito local, puedes poner un prefijo al nombre de la hoja en el nombre del rango:
Sheet1!total_revenue
Sheet2!total_revenue
Sheet3!total_revenue
Los nombres de rango creados con el cuadro de nombre tienen automáticamente alcance global. Para anular este comportamiento, agregs el nombre de la hoja cuando definas el nombre:
Sheet3!my_new_name
Alcance global
Alcance global significa que un nombre funcionará en cualquier parte de un libro de trabajo. Por ejemplo, podrías nombrar una celda «last_update», ingresar una fecha en la celda. Luego puedes usar la fórmula a continuación para mostrar la fecha de la última actualización en cualquier hoja de trabajo.
=last_update
Los nombres globales deben ser únicos dentro de un libro de trabajo.
Alcance local
Los rangos con nombre de ámbito local tienen sentido para las hojas de trabajo que usan rangos con nombre solo para suposiciones locales. Por ejemplo, tal vez tengas un libro de trabajo con hojas de seguimiento mensuales (una por mes) que use rangos con nombre con el mismo nombre, todos con un alcance local. El alcance local permite que los nombres en cada hoja funcionen correctamente sin colisionar con nombres en las otras hojas.
Administrar el alcance del rango nombrado
De forma predeterminada, los nuevos nombres creados con el cuadro de nombre son globales, y no puedes editar el alcance de un rango con nombre después de su creación. Sin embargo, como solución, puedes eliminar y volver a crear un nombre con el alcance deseado.
Si deseas cambiar varios nombres a la vez de global a local, a veces tiene sentido copiar la hoja que contiene los nombres. Cuando duplicas una hoja de cálculo que contiene rangos con nombre, Excel copia los rangos nombrados a la segunda hoja, cambiando el alcance a local. Después de tener la segunda hoja con los nombres de ámbito local, puedes eliminar opcionalmente la primera hoja.
Deja una respuesta
Debes estar conectado para dejar un comentario.