Todo sobre los rangos con nombre de Excel - Nombre del rango de Excel

Mientras trabajaba en Excel, debe haber oído hablar de los rangos con nombre en Excel. Tal vez de un amigo, colega o algún tutorial en línea. Incluso lo he mencionado muchas veces en mis artículos. En este artículo, aprenderemos sobre los rangos con nombre en Excel y exploraremos todos sus aspectos.

¿Qué son los rangos con nombre en Excel?

Bueno, los rangos con nombre no son más que algunos rangos de Excel que se etiquetan con un nombre significativo. Por ejemplo, si tiene una celda que dice B1, contiene el objetivo diario, puede nombrar esa celda específicamente como "Objetivo". Ahora puede usar "Destino" para referirse a A1 en lugar de escribir B1.
En pocas palabras, el rango con nombre es solo nombrar rangos.

¿Cómo nombrar un rango en Excel?

Definir el nombre manualmente:

Para definir un nombre para un rango, puede usar el atajo CTRL + F3. O puede seguir estos pasos.

• • Ir a la pestaña Fórmula
  • Busque la sección Nombres definidos y haga clic en Definir nombres. Abrirá Name Manger.

• • Haga clic en Nuevo.
  • Escriba el nombre.

• Seleccione el alcance (libro de trabajo u hoja)
• Escribe un comentario si quieres.
• En el cuadro Se refiere a, escriba la referencia o seleccione un rango con el mouse.
• Presiona OK. Se hace.

Ahora puede consultarlo simplemente escribiendo su nombre.
Hay algunas reglas a seguir al crear nombres. Son

1. Los nombres no deben comenzar con dígitos o caracteres especiales que no sean el subrayado (_) y la barra invertida (\).
2. Los nombres no pueden tener espacios ni caracteres especiales excepto _ y \.
3. El rango no debe nombrarse como referencias de celda. Por ejemplo, los nombres de A1, B1 o AZ100, etc. no son válidos.
4. No puede nombrar un rango como "r" y "c" porque están reservados para referencias de filas y columnas.
5. Dos rangos con nombre no pueden tener el mismo nombre en un libro de trabajo.
6. El mismo rango puede tener varios nombres.

Definir nombre automáticamente

Bueno, la mayor parte del tiempo trabajará con una tabla de datos estructurados. Tendrán columnas y filas con encabezados de columna y encabezados de fila. Y la mayoría de las veces, estos nombres son significativos para los datos, y le gustaría nombrar su rango como estos encabezados de columna. Excel proporciona una herramienta para nombrar rangos usando títulos automáticamente. Sigue estos pasos.

• • Los rangos que desea nombrar como encabezados
  • prensa CTRL + MAYÚS + F3, o Ubique la sección Nombres definidos en la pestaña Fórmula y haga clic en Crear a partir de la selección.

• • Aparecerá el cuadro de opción siguiente. Seleccioné solo la fila superior porque quiero nombrar este rango como el encabezado y no quiero nombrar filas.

• Haga clic en Aceptar.

Ahora cada columna se nombra como su encabezado. Siempre que escriba, escriba una fórmula, estos nombres se enumerarán en la opción que se utilizará.

Rango de nombres usando tablas de Excel

Cuando organizamos un dato como una tabla en Excel usando CTRL + T, los encabezados de columna se asignan automáticamente como el nombre de la columna respectiva. Debería explorar las tablas de Excel y sus beneficios.

¿Cómo ver todos los rangos con nombre?

Bueno, habrá ocasiones en las que le gustaría ver todos los rangos con nombre disponibles en el libro de trabajo. Para ver todos los rangos de nombres Presione CTRL + F3. O puedes ir a Ficha Fórmula> Administrador de nombres. Enumerará todos los rangos con nombre que están disponibles en el libro de trabajo. Puede editar rangos con nombre disponibles, eliminarlos, agregar nuevos nombres.

Un rango de varios nombres

Excel permite a los usuarios nombrar el mismo rango con diferentes nombres. Por ejemplo, el rango A2: A10 puede denominarse "Clientes" y "Clientes" al mismo tiempo. Ambos nombres se referirán al mismo rango A2: A10.
Pero no puede tener los mismos nombres para dos rangos diferentes. Elimina la posibilidad de ambigüedad.

Obtener lista de rangos con nombre en la hoja

Entonces, si desea tener una lista de rangos con nombre y los rangos que cubren, puede usar este atajo para pegarlos en un lugar de una hoja.

• • Seleccione una celda en la que desee obtener la lista de rangos con nombre.
  • Presione F3. Esto abrirá un PAGaste Nombre cuadro de diálogo.
  • Haga clic en pegar lista botón.

• La lista se pegará en la celda seleccionada y en adelante.

Si hace doble clic en el nombre de los rangos con nombre en el cuadro de pegar nombre, se escribirán como fórmulas en la celda. Intentalo.

Actualizar rangos con nombre manualmente

Bueno, cuando inserta una celda dentro de un rango con nombre, se actualiza automáticamente y lo expande. Pero si agrega datos al final de la tabla, deberá actualizar el campo nombrado. Para actualizar rangos con nombre, siga estos pasos.

• prensa CTRL + F3, para abrir el administrador de nombres.
• Haga clic en el rango con nombre que desea editar. Haga clic en Editar.
• En Se refiere a la columna, escriba el rango al que desea expandir y presione Aceptar.

Y esta hecho. Esta es la actualización manual de rangos con nombre. Sin embargo, podemos hacerlo dinámico usando algunas fórmulas.

Actualizar rangos con nombre dinámicamente

Es aconsejable hacer que sus rangos con nombre sean dinámicos para que no tenga que editarlos cada vez que sus datos superen el rango predefinido.
Lo he cubierto en un artículo separado llamado Rangos dinámicos con nombre. Puede aprender y comprender los beneficios en detalle aquí.

Eliminar rangos con nombre

Cuando elimina alguna parte del rango con nombre, se ajusta automáticamente su rango. Pero cuando elimina todo el rango de nombres, el nombre desaparece de la lista de nombres. Cualquier fórmula que dependa de esos rangos mostrará el error #REF o darán una salida incorrecta (funciones de conteo).
Por cualquier motivo, si desea eliminar rangos con nombre, siga estos pasos.

• Presione CTRL + F3. Se abrirá el administrador de nombres.
• Seleccione Rangos con nombre que desee eliminar.
• Haga clic en el botón Eliminar o presione el botón Eliminar en el teclado.

Precaución: Antes de eliminar los rangos con nombre, asegúrese de que ninguna fórmula dependa de estos nombres. Si hay alguno, conviértalo primero a rangos. De lo contrario, verá el error #REF.

Eliminar nombres con errores

Excel proporciona una herramienta para eliminar nombres que solo tienen errores. No es necesario que los identifique usted mismo. Para eliminar nombres con errores, siga estos pasos:

• • Abra el Administrador de nombres (CTRL + F3).
  • Haga clic en el menú desplegable Filtro en la esquina superior derecha.
  • Seleccione "Nombre con errores"

• Seleccione Todo y presione el botón Eliminar.

Y se han ido. Todos los nombres con errores se eliminarán del registro de inmediato.

Rangos con nombre con fórmulas

El mejor uso de rangos con nombre se experimenta con fórmulas. Las fórmulas se vuelven más flexibles y legibles con los rangos con nombre. Veamos cómo.

Fórmulas fáciles de escribir

Ahora supongamos que ha nombrado un rango como "Elementos". Ahora la lista de elementos que desea contar "lápices". Con nombre, es fácil escribir esta fórmula CONTAR. Solo escribe

= CONTAR.SI (artículo, "Lápiz")

Tan pronto como escriba el paréntesis de apertura de la fórmula, aparecerá la lista de rangos con nombre disponibles. Sin un nombre, escribiría una función gi COUNTIF de Excel con rangos, para lo cual es posible que primero tenga que mirar el rango y luego seleccionar el rango o escribirlo en la fórmula.

Excel atiende los rangos de nombres disponibles.

Los nombres de los rangos se muestran como sugerencias cuando escribe cualquier letra después del signo =. Igual que Excel muestra la lista de fórmulas. Por ejemplo, si escribe = u, cada método y rango con nombre se mostrarán comenzando con u, para que pueda usarlos fácilmente.

Hacer constantes usando rangos con nombre

Hasta ahora, aprendimos acerca de los rangos de nombres, pero en realidad también puedes nombrar valores. Por ejemplo, si el nombre de su cliente es Sunder Pichai, puede hacer un nombre "Cliente" y se refiere a escribir "Sundar Pichai". Ahora, siempre que escriba = Cliente en cualquier celda, se mostrará Sundar Pichai.


No solo texto, sino que también puede asignar un número como constante para trabajar. Por ejemplo, define un objetivo. O el valor de algo que no cambiará.
Referenciación absoluta y relativa con rangos con nombre

La referencia con rangos nombrados en es muy flexible. Por ejemplo, si escribe el nombre de un rango con nombre en una celda relativa, se comportará como una referencia correspondiente. Vea la imagen de abajo.

Pero cuando lo use con fórmulas, se comportará como absoluto. Bueno, la mayoría de las veces los usará con fórmulas, así que puedes decir que son absolutos por defecto, pero en realidad son flexibles.

Pero también podemos hacerlos relativos.

¿Cómo hacer rangos con nombre relativo en Excel?

Digamos que si quiero nombrar un rango "Antes", que se referirá a la celda a la izquierda donde esté escrito. ¿Cómo puedo hacer eso? Sigue estos pasos:

• Presione CTRL + F3
• Haga clic en Nuevo
• Escriba "Antes" en la sección "Nombre".
• En la sección "Se refiere a:" escriba la dirección de la celda a la izquierda. Por ejemplo, si está en la celda B1, escriba "= A2" en la sección "Se refiere a:". Asegúrese de que no tenga el signo $.

Ahora, dondequiera que escriba "Antes" en la fórmula, se referirá a la celda que le queda.

Aquí, utilicé antes en la función COLUMNA. La fórmula devuelve el número de columna de la celda izquierda donde está escrito. Para mi sorpresa, A1 muestra el número de columna de la última columna. Lo que significa que la hoja está circulando. Pensé que mostraría un error #REF.

¿Dar nombre a fórmulas de uso frecuente?

Ahora bien, este es asombroso. Muchas veces usa la misma fórmula una y otra vez en una hoja de trabajo. Por ejemplo, es posible que desee comprobar si un nombre está en su lista de clientes o no. Y esta necesidad puede ocurrir muchas veces. Para esto, escribirás la misma fórmula compleja cada vez.

= SI (CONTAR.SI (Cliente, I3), "En la lista", "No en la lista")

¿Qué tal si escribe "= IsInCustomer" en una celda y le mostrará si el valor en la celda de la izquierda está en la lista de clientes o no?

Por ejemplo, he preparado una mesa aquí. Ahora solo quiero escribir "= IsInCustomer" en J5 y me gustaría ver si el valor en I5 está en la lista de clientes o no. Para hacerlo, siga estos pasos.

• • Presione CTRL + F3
  • Haga clic en Nuevo
  • En Nombre, escriba "IsInCustomer"
  • En "Se refiere a", escriba su fórmula. = SI (CONTAR.SI (Cliente, I5), "En la lista", "No en la lista")

• Presiona el botón OK.

Ahora, donde sea que escriba "IsInCustomer", comprobará el valor en la celda de la izquierda en la lista de clientes.

Esto evita que te repitas una y otra vez.

Aplicar rangos con nombre a fórmulas

Muchas veces, definimos nombres para nuestros rangos después de haber escrito fórmulas basadas en rangos. Por ejemplo, tengo el precio total como celdas = E2 * F2. ¿Cómo podemos cambiarlo a Unidades * Unit_Cost.

• • Seleccione las fórmulas.
  • Vaya a la pestaña de fórmulas. Haga clic en el menú desplegable Definir nombre.

• Haga clic en Aplicar nombres.
• Aparecerá una lista de todos los rangos con nombre. Elija los nombres correctos y presione Aceptar.

Y ahora se aplican los nombres. Puedes verlo en la barra de fórmulas.

Fórmulas fáciles de leer con rangos con nombre

Como ha visto, los rangos con nombre facilitan la lectura de las fórmulas. Si escribo = CONTAR.SI (“A2: A100”, B2), nadie entenderá lo que estoy tratando de contar, hasta que vean los datos o alguien se los explique.

Pero si escribo = CONTAR.SI (región, 'este'), la mayoría de los usuarios entenderán inmediatamente que estamos contando la ocurrencia de 'este' en la región denominada rango.

Fórmulas portátiles

Los rangos con nombre hacen que sea muy fácil copiar y pegar fórmulas sin preocuparse por cambiar las referencias. Y puede llevar una fórmula de un libro a otro y funcionará bien hasta que el libro de destino tenga los mismos nombres.

Por ejemplo, si tiene una fórmula = CONTAR.SI (región, este) en la tabla de distribución y tiene otro libro de trabajo, dice clientes que también tiene un rango con nombre "Región". Ahora, si copia esta fórmula directamente en cualquier lugar de ese libro, le mostrará la información correcta. La estructura de los datos no importará. No importa dónde diablos esté esa columna en su libro de trabajo. Esto funcionará correctamente.

En la imagen de arriba, he usado exactamente la misma fórmula en dos archivos diferentes para contar el número o este que ocurre en la lista de regiones. Ahora están en columnas diferentes, pero dado que ambos se nombran región, funcionará perfectamente.

Navegar fácilmente en el libro de trabajo

Es más fácil navegar en el libro de trabajo con rangos con nombre. Solo necesita escribir el nombre del nombre en el cuadro de nombre. Excel lo llevará al rango, no importa dónde se encuentre en el libro de trabajo. Dado que el rango con nombre es del ámbito del libro de trabajo.
Por ejemplo, si está en la hoja 10 y desea obtener la lista de clientes y no sabe en qué hoja está. Simplemente vaya al cuadro de nombre y escriba "cliente". Serás dirigido al rango nombrado en una fracción de segundo.

Reducirá el esfuerzo de recordar los rangos.

Navegar usando hipervínculos con rango con nombre

Cuando su hoja es grande y a menudo va de un punto a otro, le gusta usar hipervínculos para navegar fácilmente. Los intervalos bien nombrados pueden funcionar perfectamente con hipervínculos. Para agregar hipervínculos utilizando rangos con nombre, siga estos pasos.

• • Elija una celda donde desee hipervínculo
  • Presione CTRL + K o vaya a Insertar pestaña> Hipervínculo para abrir el cuadro de diálogo Insertar hipervínculo.

• • Haga clic en Colocar en este documento.
  • Desplácese hacia abajo para ver los rangos con nombre disponibles en Nombres definidos

• Seleccione el rango con nombre para insertar un hipervínculo a ese rango.

Y esta hecho. Tiene su hipervínculo a su rango con nombre elegido. Con esto, puede crear un índice de rangos con nombre que puede ver y hacer clic para navegar a ellos directamente. Esto hará que su libro de trabajo sea realmente fácil de usar.

Validación de datos y rango con nombre

Los rangos con nombre y la validación de datos están hechos el uno para el otro. Los rangos con nombre hacen que la validación de datos sea altamente personalizable. Es mucho más fácil agregar una validación de una lista usando un rango con nombre. Veamos cómo …

• • Ir a la pestaña de datos
  • Haga clic en Validación de datos
  • Seleccione Lista en la sección "Permitir:"
  • En la sección "Fuente:", escriba "= Cliente" (escriba el rango con nombre que tenga)

• Presiona OK

Ahora, esta celda tendrá los nombres de los clientes que forman parte del rango de nombre del Cliente. Fácil, ¿no es así?

Validación de datos dependientes o en cascada con rangos con nombre

Ahora, ¿qué pasa si desea una validación de datos dependientes o en cascada? Por ejemplo, si desea una lista desplegable que tenga categorías, Frutas y Verduras. Ahora, si elige frutas, otro menú desplegable debería mostrar solo la opción de frutas y si elige Vegetales, solo verduras.

Esto se puede lograr fácilmente mediante el uso de rangos con nombre. Aprender cómo.

• Desplegable dependiente usando rango con nombre
• Otras formas de validación de datos en cascada

Sin validación de datos con nombres de datos en tablas

Aunque las tablas de Excel proporcionan nombres estructurados, no se pueden usar con la validación de datos y el formato condicional. No sé por qué Excel no lo permite.
Pero no significa que no se pueda hacer. Puede nombrar rangos dentro de una tabla y luego usarlos para la validación. Excel no tiene ningún problema con eso.

Alcance de rangos nombrados

Hasta ahora hablamos de rangos con nombre que tenían alcance de libro de trabajo. ¿Qué? ¿No lo discutimos? Bien, entendamos rápidamente qué es el alcance de los rangos con nombre.

¿Qué es el alcance de un rango de nombres?

El alcance del pozo define dónde se puede reconocer un rango de nombres. No se puede reconocer ningún nombre fuera de su alcance. Por ejemplo, un nombre en el libro de trabajo1 no se puede reconocer en un libro de trabajo diferente. Excel proporciona dos opciones para el alcance de los rangos con nombre Hoja de trabajo y Libro de trabajo.

¿Cómo definir un alcance de rango con nombre?

Cuando crea un nuevo rango de nombres, puede ver una sección "Alcance:". Haga clic en el menú desplegable y elija el alcance para su rango de nombres. No puede cambiar el alcance una vez que haya creado un rango con nombre. Así que mejor hazlo antes. De forma predeterminada, es un libro de trabajo.

Alcance del libro de trabajo

Este es el alcance predeterminado para un rango con nombre. Un nombre definido con el alcance del libro de trabajo se puede usar en todo el libro de trabajo en el que está definido (no en otros libros de trabajo).
Todos los ejemplos anteriores tenían alcance de libro de trabajo.

Alcance de la hoja de trabajo

Un nombre que se define con un alcance de hoja de trabajo solo se puede usar en definir hoja de trabajo. Por ejemplo, si defino "Total" para una celda total con el alcance de sheet1. Entonces el total se reconocerá solo en la hoja 1. Otras hojas no reconocerán.

Quiero un alcance de Excel

Excel no tiene Global ni dice Excel Scope. De hecho, me gustaría definir algunos nombres que puedan reconocerse en todos los libros de trabajo de mi sistema. Si alguien sabe cómo podemos hacer esto, hágamelo saber.

Editar el alcance después de crear nombres

No puedes. Excel no le permite editar el alcance de un rango con nombre una vez que haya creado. Dado que todos los rangos con nombre en una hoja tienen un ámbito de libro de trabajo predeterminado, es posible que desee cambiar su alcance a una hoja.
Para hacerlo, simplemente haga una copia de esa hoja y Excel hará que cada nombre en esa hoja sea local para evitar ambigüedades. Ahora puede eliminar la hoja original si lo desea.

Rango de nombre de pasta de corte

Cuando corta y pega un rango con nombre de un destino a otro, la referencia cambia a una nueva ubicación. Por ejemplo, si tiene un rango con nombre "Cliente" en A2: A10 y lo corta y pega en B2: B10, el nombre del cliente se referirá a la nueva ubicación B2: B10.

Rangos dinámicos con nombre en Excel

17 características asombrosas de las tablas de Excel

Articulos populares:

50 accesos directos de Excel para aumentar su productividad

Cómo utilizar la función BUSCARV en Excel

Cómo usar la función CONTAR.SI en Excel

Cómo utilizar la función SUMIF en Excel