Los rangos con nombre son una de estas viejas características crujientes en Excel que pocos usuarios entienden. Los nuevos usuarios pueden encontrarlos extraños y aterradores, e incluso los expertos pueden evitarlos porque parecen inútiles y complejos.
Pero los rangos con nombre son en realidad una característica bastante interesante. Pueden hacer fórmulas * 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 (más portátiles).
De hecho, utilizo rangos con nombre todo el tiempo al probar y crear prototipos de fórmulas. Me ayudan a que las fórmulas funcionen más rápido. También uso rangos con nombre porque soy vago y no me gusta escribir referencias complejas :)
Los conceptos básicos de los rangos con nombre en Excel
¿Qué es un rango con nombre?
Un rango con nombre es solo un nombre legible por humanos para un rango de celdas en Excel. Por ejemplo, si nombro el rango A1: A100 'datos', puedo usar MAX para obtener el valor máximo con una fórmula simple:
|_+_| 
La belleza de los rangos con nombre es que puede usar nombres significativos en sus fórmulas sin pensar en las referencias de celda. Una vez que tenga un rango con nombre, utilícelo como una referencia de celda. Todas estas fórmulas son válidas con el rango con nombre 'datos':
|_+_|Video: Cómo crear un rango con nombre
Crear un rango con nombre es fácil
Crear un rango con nombre es rápido y fácil. Simplemente seleccione un rango de celdas y escriba un nombre en el cuadro de nombre. Cuando presiona retorno, se crea el nombre:
Para probar rápidamente el nuevo rango, elija el nuevo nombre en el menú desplegable junto al cuadro de nombre. Excel seleccionará el rango en la hoja de trabajo.
Excel puede crear nombres automáticamente (ctrl + shift + F3)
Si tiene datos bien estructurados con etiquetas, puede hacer que Excel cree rangos con nombre para usted. Simplemente seleccione los datos, junto con las etiquetas, y use el comando 'Crear a partir de la selección' en la pestaña Fórmulas de la cinta:
También puede utilizar el método abreviado de teclado control + shift + F3.
Con esta función, podemos crear rangos con nombre para la población de 12 estados en un solo paso:
¿Con qué comienza un comentario en código visual básico en Excel?
Al hacer clic en Aceptar, se crean los nombres. Encontrará todos los nombres recién creados en el menú desplegable junto al cuadro de nombre:
Con los nombres creados, puede usarlos en fórmulas como esta
|_+_|Actualizar rangos con nombre en el Administrador de nombres (Control + F3)
Una vez que cree un rango con nombre, use el Administrador de nombres (Control + F3) para actualizar según sea necesario. Seleccione el nombre con el que desea trabajar, luego cambie la referencia directamente (es decir, edite 'se refiere a'), o haga clic en el botón de la derecha y seleccione un nuevo rango.
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 selecciona un rango con nombre completo en una hoja de trabajo, puede arrastrar 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. Si conoce una forma de hacer esto, ¡intervenga a continuación!
Ver todos los rangos con nombre (control + F3)
Para ver rápidamente todos los rangos con nombre en un libro de trabajo, use el menú desplegable junto al cuadro de nombre.
Si desea ver más detalles, abra el Administrador de nombres (Control + F3), que enumera todos los nombres con referencias y también proporciona un filtro:
Nota: en una Mac, no hay Administrador de nombres, por lo que verá el cuadro de diálogo Definir nombre en su lugar.
Copie y pegue todos los rangos con nombre (F3)
Si desea un registro más persistente de rangos con nombre en un libro de trabajo, puede pegar la lista completa de nombres en cualquier lugar que desee. Vaya a Fórmulas> Usar en fórmula (o use el atajo F3), luego elija Pegar nombres> Pegar lista:
Cuando haga clic en el botón Pegar lista, verá los nombres y referencias pegados en la hoja de trabajo:
Ver nombres directamente en la hoja de trabajo
Si establece el nivel de zoom en menos del 40%, Excel mostrará los nombres de rango directamente en la hoja de trabajo:
¡Gracias por este consejo, Felipe!
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 simples están bien 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: 'casa', 'CASA' y 'HoMe' son todos iguales en Excel.
Rangos con nombre en fórmulas
Los rangos con nombre son fáciles de usar en fórmulas
Por ejemplo, digamos que nombra una celda en su libro de trabajo 'actualizado'. La idea es que puede poner la fecha actual en la celda (Ctrl +) y hacer referencia a la fecha en otra parte del libro.
La fórmula en B8 se ve así:
|_+_|Puede pegar esta fórmula en cualquier lugar del libro y se mostrará correctamente. Siempre que cambie la fecha en 'actualizado', el mensaje se actualizará siempre que se use la fórmula. Ver esta página para más ejemplos.
Los rangos con nombre aparecen al escribir una fórmula
Una vez que haya creado un rango con nombre, aparecerá automáticamente en fórmulas cuando escriba la primera letra del nombre. Presione la tecla de tabulación para ingresar el nombre cuando tenga una coincidencia y desee que Excel ingrese el nombre.
Los rangos con nombre pueden funcionar como constantes
Debido a que los rangos con nombre se crean en una ubicación central, puede usarlos como constantes sin una referencia de celda. Por ejemplo, puede crear nombres como 'MPG' (millas por galón) y 'CPG' (costo por galón) con y asignar valores fijos:
Luego, puede usar estos nombres en cualquier lugar que desee en las fórmulas y actualizar su valor en una ubicación central.
Los rangos con nombre son absolutos por defecto
De forma predeterminada, los rangos con nombre se comportan como referencias absolutas. Por ejemplo, en esta hoja de trabajo, la fórmula para calcular el combustible sería:
|_+_| 
La referencia a D2 es absoluta (bloqueada), por lo que la fórmula se puede copiar sin que D2 cambie.
Si nombramos D2 'MPG', la fórmula se convierte en:
|_+_| 
Dado que el MPG es absoluto de forma predeterminada, la fórmula se puede copiar en la columna D tal cual.
Los rangos con nombre también pueden ser relativos
Aunque los rangos con nombre son absolutos por defecto, también pueden ser relativos. Un rango con nombre 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 nombres relativos son útiles para la creación de fórmulas genéricas que funcionan dondequiera que se muevan.
Por ejemplo, puede crear un rango genérico llamado 'CellAbove' como este:
- Seleccione la celda A2
- Control + F3 para abrir el Administrador de nombres
- Vaya a la sección 'Se refiere a', luego escriba: = A1
CellAbove ahora recuperará el valor de la celda de arriba donde sea que se use.
Importante: asegúrese de que la celda activa esté en la ubicación correcta antes de crear el nombre.
Aplicar rangos con nombre a fórmulas existentes
Si tiene fórmulas existentes que no usan rangos con nombre, puede pedirle a Excel que aplique los rangos con nombre en las fórmulas por usted. Comience seleccionando las celdas que contienen fórmulas que desea actualizar. Luego, ejecute Fórmulas> Definir nombres> Aplicar nombres.
Excel reemplazará las referencias que tengan un rango con nombre correspondiente con el nombre en sí.
También puede aplicar nombres con buscar y reemplazar:
Beneficios clave de los rangos con nombre
Los rangos con nombre facilitan la lectura de las fórmulas
El mayor beneficio de los rangos con nombre es que facilitan la lectura y el mantenimiento de las fórmulas. Esto se debe a que 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 buscar 'Posición' de la tabla es bastante críptica:
|_+_| 
cómo poner múltiples funciones en una celda en Excel
Sin embargo, con B3: E11 llamado 'datos' y H4 llamado 'planeta', podemos escribir fórmulas como esta:
|_+_| 
De un vistazo, puede ver la única diferencia en estas fórmulas en el índice de la columna.
Los rangos con nombre hacen que las fórmulas sean portátiles y reutilizables
Los rangos con nombre pueden facilitar la reutilización de una fórmula en una hoja de trabajo diferente. Si define nombres con anticipación en una hoja de trabajo, puede pegar una fórmula que use estos nombres y 'simplemente funcionará'. Esta es una excelente manera de hacer que una fórmula funcione rápidamente.
Por ejemplo, esta fórmula cuenta valores únicos en un rango de datos numéricos:
|_+_|Para 'portar' rápidamente esta fórmula a su propia hoja de trabajo, nombre un rango 'datos' y pegue la fórmula en la hoja de trabajo. Siempre que 'datos' contenga valores numéricos, la fórmula funcionará de inmediato.
Consejo: le recomiendo que cree los nombres de rango necesarios * primero * en el libro de trabajo de destino, luego copie en la fórmula solo como texto (es decir, no copie la celda que contiene la fórmula en otra hoja de trabajo, simplemente copie el texto de la fórmula ). Esto evita que Excel cree nombres sobre la marcha y le permite controlar completamente el proceso de creación de nombres. Para copiar solo el texto de la fórmula, copie el texto de la barra de fórmulas o cópielo a través de otra aplicación (es decir, navegador, editor de texto, etc.).
Los rangos con nombre se pueden utilizar para la navegación
Los rangos con nombre son excelentes para una navegación rápida. Simplemente seleccione el menú desplegable junto al cuadro de nombre y elija un nombre. Cuando suelte el mouse, se seleccionará el rango. Cuando existe un rango con nombre en otra hoja, se le llevará a esa hoja automáticamente.
Los rangos con nombre funcionan bien con hipervínculos
Los rangos con nombre facilitan los hipervínculos. Por ejemplo, si nombra a A1 en Sheet1 'casa', puede crear un hipervínculo en otro lugar que lo lleve de regreso allí.
Para usar un rango con nombre dentro de la función HIPERVÍNCULO, agregue un símbolo de almohadilla delante del rango con nombre:
|_+_|Nota: curiosamente, no puede hacer un hipervínculo a una tabla como puede hacerlo con un nombre de rango normal. Sin embargo, puede definir un nombre igual a una tabla (es decir, = Table1) e hipervínculo a eso. Si alguien conoce una forma de vincular directamente una mesa, ¡intervenga!
Rangos con nombre para la validación de datos
Los rangos de nombres funcionan bien para la validación de datos, ya que le 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 aplique la validación de datos con una lista vinculada así:
El resultado es un menú desplegable en la columna E que solo permite valores en el rango nombrado:
Rangos dinámicos con nombre
Los rangos de nombres son extremadamente útiles cuando se ajustan automáticamente a los nuevos datos en una hoja de trabajo. Un rango configurado de esta manera se denomina 'rango dinámico con nombre'. Hay dos formas de dinamizar un rango: fórmulas y tablas.
Rango dinámico con nombre con una tabla
Una tabla es la forma más sencilla de crear un rango dinámico con nombre. Seleccione cualquier celda en los datos, luego use el atajo Control + T:
Cuando crea una tabla de Excel, se crea automáticamente un nombre (por ejemplo, Tabla1), pero puede cambiar el nombre de la tabla como desee. Una vez que haya creado una tabla, se expandirá automáticamente cuando se agreguen datos.
Rango dinámico con nombre con fórmula
También puede crear un rango dinámico con nombre con fórmulas, utilizando funciones como DESPLAZAMIENTO e ÍNDICE. Aunque estas fórmulas son moderadamente complejas, brindan una solución liviana cuando no desea utilizar una tabla. Los enlaces a continuación proporcionan ejemplos con explicaciones completas:
- Ejemplo de fórmula de rango dinámico con ÍNDICE
- Ejemplo de fórmula de rango dinámico con OFFSET
Nombres de tablas en la validación de datos
Dado que las tablas de Excel proporcionan un rango dinámico automático, parecerían ser un ajuste natural para las reglas de validación de datos, donde el objetivo es validar contra una lista que puede estar siempre cambiando. Sin embargo, un problema con las tablas es que no puede usar referencias estructuradas directamente para crear validación de datos o reglas de formato condicional. En otras palabras, no puede usar un nombre de tabla en áreas de entrada de validación de datos o formato condicional.
Sin embargo, como solución alternativa, puede definir un rango con nombre que apunte a una tabla y luego usar el rango con nombre para la validación de datos o el formato condicional. El siguiente video describe este enfoque en detalle.
Video: Cómo usar rangos con nombre con tablas
Eliminar rangos con nombre
Nota: Si tiene fórmulas que se refieren a rangos con nombre, es posible que desee actualizar las fórmulas primero antes de eliminar los nombres. De lo contrario, verá #NAME? errores en fórmulas que aún se refieren a nombres eliminados. Guarde siempre su hoja de trabajo antes de eliminar los 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 inserta celdas / filas / columnas dentro de un rango con nombre, la referencia del rango se ajustará en consecuencia y seguirá siendo válida. Sin embargo, si elimina todas las celdas que encierran un rango con nombre, el rango con nombre perderá la referencia y mostrará un error #REF. Por ejemplo, si nombro A1 'prueba', luego elimino la columna A, el administrador de nombres mostrará 'se refiere a' como:
|_+_|Eliminar nombres con el Administrador de nombres
Para eliminar rangos con nombre de un libro de trabajo manualmente, abra el administrador de nombres, seleccione un rango y haga clic en el botón Eliminar. Si desea eliminar más de un nombre al mismo tiempo, puede Mayús + Clic o Ctrl + Clic para seleccionar varios nombres y luego eliminarlos en un solo paso.
Eliminar nombres con errores
Si tiene muchos nombres con errores de referencia, puede usar el botón de filtro en el administrador de nombres para filtrar los nombres con errores:
Luego shift + clic para seleccionar todos los nombres y eliminar.
Alcance y rangos con nombre
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. Los nombres globales tienen un alcance de 'libro de trabajo' y los nombres locales tienen un alcance igual al nombre de la hoja en la que existen. Por ejemplo, el ámbito 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 tengan acceso a determinados datos, variables o constantes. Por ejemplo, puede usar un rango con nombre global, una suposición de tasa impositiva que se usa en varias hojas de trabajo.
Alcance local
El alcance local significa que un nombre solo funciona en la hoja en la que se creó. Esto significa que puede tener varias hojas de trabajo en el mismo libro de trabajo que usan el mismo nombre. Por ejemplo, quizás tenga un libro de trabajo con hojas de seguimiento mensuales (una por mes) que usan rangos con nombre con el mismo nombre, todos con alcance local. Esto podría permitirle reutilizar las mismas fórmulas en hojas diferentes. El ámbito local permite que los nombres de cada hoja funcionen correctamente sin chocar con los nombres de las otras hojas.
Para hacer referencia a un nombre con un ámbito local, puede anteponer el nombre de la hoja al nombre del rango:
|_+_|Nombres de rango creados con el nombre de Caja automáticamente tienen alcance global. Para anular este comportamiento, agregue el nombre de la hoja al definir el nombre:
|_+_|Alcance global
El alcance global significa que un nombre funcionará en cualquier lugar de un libro de trabajo. Por ejemplo, podría nombrar una celda 'last_update', ingresar una fecha en la celda. Luego, puede usar la fórmula a continuación para mostrar la fecha de la última actualización en cualquier hoja de trabajo.
|_+_|Los nombres globales deben ser únicos dentro de un libro.
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, quizás tenga un libro de trabajo con hojas de seguimiento mensuales (una por mes) que usan rangos con nombre con el mismo nombre, todos con alcance local. El ámbito local permite que los nombres de cada hoja funcionen correctamente sin chocar con los nombres de las otras hojas.
Administrar el alcance del rango con nombre
De forma predeterminada, los nuevos nombres creados con el cuadro de nombre son globales y no puede editar el alcance de un rango con nombre después de su creación. Sin embargo, como solución alternativa, puede eliminar y volver a crear un nombre con el alcance deseado.
Si desea cambiar varios nombres a la vez de global a local, a veces tiene sentido copiar la hoja que contiene los nombres. Cuando duplica una hoja de trabajo que contiene rangos con nombre, Excel copia los rangos con nombre en la segunda hoja, cambiando el alcance a local al mismo tiempo. Una vez que tenga la segunda hoja con nombres de ámbito local, puede eliminar opcionalmente la primera hoja.
Jan Karel Pieterse y Charles Williams han desarrollado una utilidad llamada Name Manager que proporciona muchas operaciones útiles para rangos con nombre. Usted puede descargue la utilidad Name Manager aquí .
Autor Dave Bruns
