Excel

Rangos con nombre en Excel

Named Ranges Excel

Los rangos con nombre son una de estas viejas características crujientes en Excel que pocos usuarios entienden. Los nuevos usuarios pueden encontrarlos raros 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:

|_+_|

Rango con nombre simple llamado



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:



Cree un rango con nombre rápidamente con el cuadro de 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:

Crear nombres a partir del comando de selección en 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?

Crear nombres a partir de la selección con datos y etiquetas seleccionados

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:

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

|_+_|

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.

Rangos con nombre actualizados 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 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 conoces una forma de hacer esto, ¡participa 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:

El administrador de nombres muestra todos los nombres recién creados

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:

Cuadro de diálogo pegar nombres

Al hacer clic en el botón Pegar lista, verá los nombres y referencias pegados en la hoja de trabajo:

Después de pegar rangos con nombre 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:

Con un nivel de zoom <40%, Excel mostrará los nombres de los rangos

¡Gracias por este consejo, Felipe!

Los nombres tienen reglas

Al crear rangos con nombre, siga estas reglas:

  1. Los nombres deben comenzar con una letra, un guión bajo (_) o una barra invertida ()
  2. Los nombres no pueden contener espacios y la mayoría de los caracteres de puntuación.
  3. Los nombres no pueden entrar en conflicto con las referencias de celda; no puede nombrar un rango 'A1' o 'Z100'.
  4. Las letras simples están bien para los nombres ('a', 'b', 'c', etc.), pero las letras 'r' y 'c' están reservadas.
  5. 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.

Usando un rango con nombre dentro de una fórmula de texto

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 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, 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:

Los rangos con nombre pueden funcionar como constantes, sin referencia de celda

Luego, puede usar estos nombres en cualquier lugar que desee en las fórmulas y actualizar su valor en una ubicación central.

Usar un rango con nombre como una constante en una fórmula

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:

|_+_|

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 que D2 cambie.

Si nombramos D2 'MPG', la fórmula se convierte en:

|_+_|

Usar un rango con nombre como una constante en una fórmula

Dado que el MPG es absoluto por defecto, 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:

  1. Seleccione la celda A2
  2. Control + F3 para abrir el Administrador de nombres
  3. Tab en la sección 'Se refiere a', luego escribe: = 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.

El cuadro de diálogo Aplicar nombres

Excel reemplazará las referencias que tengan un rango de nombre correspondiente con el nombre en sí.

También puede aplicar nombres con buscar y reemplazar:

Aplicar rangos de nombres con buscar y reemplazar

Importante: guarde una copia de seguridad de su hoja de trabajo y seleccione solo las celdas que desea cambiar antes de usar buscar y reemplazar en fórmulas.

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 las 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:

|_+_|

Sin rangos con nombre, las fórmulas pueden ser crípticas

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:

|_+_|

Con rangos con nombre, las fórmulas pueden ser simples

De un vistazo, puede ver la única diferencia en estas fórmulas en el índice de columna.

Los rangos con nombre 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 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 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 permiten una navegación sencilla

Los rangos con nombre funcionan bien con hipervínculos

Los rangos con nombre facilitan los hipervínculos. Por ejemplo, si nombra A1 en Sheet1 'casa', puede crear un hipervínculo en otro lugar que lo lleve de regreso allí.

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 HIPERVÍNCULO, agregue un símbolo de libra 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 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í:

Usando un rango con nombre para la validación de datos con la lista

El resultado es un menú desplegable en la columna E que solo permite valores en el rango nombrado:

Validación de datos con ejemplo de rango con nombre

Rangos dinámicos con nombre

Los rangos de nombres son extremadamente útiles cuando se ajustan automáticamente a los nuevos datos de 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:

Crear una tabla de Excel

Cuando crea una tabla de Excel, se crea un nombre automáticamente (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.

Las tablas se expandirán automáticamente y se les puede cambiar el nombre

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 INDICE
  • 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 con nombre 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 video a continuación 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 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 incluyen 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 Shift + Click o Ctrl + Click 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:

Menú de filtro Administrador de nombres

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 utilizada 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. 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


^