En este artículo, aprenderemos cómo usar nombres en una fórmula en Excel.
Escenario:
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. Lo he mencionado muchas veces en mis artículos. En este artículo, aprenderemos sobre el rango con nombre en Excel y exploraremos todos y cada uno de sus aspectos.
Rangos con nombre en Excel
Fórmulas -> Administrador de nombres -> Definir nombre
Bueno, los rangos con nombre no son más que algunos rangos de Excel a los que se les da un nombre significativo. Por ejemplo, si tiene una celda que dice B1, que contiene objetivos diarios, puede nombrar esa celda específicamente como "Destino". 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 Fórmula Pestaña
- Busque la sección Nombres definidos y haga clic en Definir nombres. Esto abrirá el Nombre pesebre.
- 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. Aquí están algunas.
- Los nombres no deben comenzar con dígitos o caracteres especiales que no sean el subrayado (_) y la barra invertida (\).
- Los nombres no pueden tener espacios ni caracteres especiales excepto _ y \.
- El rango no debe nombrarse como referencias de celda. Por ejemplo, A1, B1 o AZ100, etc.
- No puede nombrar un rango como "r" y "c" porque están reservados para referencias de filas y columnas.
- Dos rangos con nombre no pueden tener el mismo nombre en un libro de trabajo.
- El mismo rango puede tener varios nombres.
Bueno, la mayor parte del tiempo trabajará con tablas 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 automáticamente usando encabezados. 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, ya que 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 una fórmula, estos nombres se enumerarán como opciones a las que se puede acceder.
Cuando tablizamos un dato en Excel usando CTRL + T, el encabezado de la columna se asigna automáticamente como el nombre de la columna respectiva. Debería explorar las tablas de Excel y sus beneficios.
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. Esto mostrará una lista de 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", ambos al mismo tiempo. Ambos nombres se referirán al mismo rango A2: A10.
Pero no puede tener los mismos nombres para dos rangos diferentes. Esto es bueno. Esto 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 su lugar en una hoja.
- Seleccione una celda en la que desee obtener una lista de rangos con nombre.
- Presione F3. Esto abrirá un pegar lista cuadro de diálogo.
- Haga clic en la pasta lista botón.
- La lista se pegará en las celdas seleccionadas 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 rango con nombre. Para actualizar los 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 la parte de la suma del rango con nombre, ajusta automáticamente su rango. Pero cuando elimina todo el rango de nombres 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, simplemente 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 herramientas 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 los rangos con nombre se explora con fórmulas. Las fórmulas se vuelven realmente flexibles y legibles con 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 nombres 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 nombre, escribiría un rango de dar un rango a la función CONTAR.SI de Excel, 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 rangos nombrados se muestran como sugerencias cuando escribe cualquier letra. Como Excel muestra la lista de fórmulas. Por ejemplo, si escribe = u, cada fórmula 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 números como constantes 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 al rango con nombre, se comportará como una referencia relativa. Vea la imagen de abajo.
Pero cuando lo use con fórmulas, se comportará como absoluto. Bueno, la mayor parte del tiempo los usará con fórmulas, así que se puede 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í, lo usé 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 de 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 apariciones 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 el mismo nombre.
Por ejemplo, si tienes una Fórmula = CONTAR.SI (región, este) en la tabla de distribución y tiene otro libro de trabajo que 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 como regiones, funcionará perfectamente.
Navegar fácilmente en el libro de trabajo
Es más fácil navegar en un 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 una 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 nombres de 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
Espero que este artículo sobre cómo usar nombres en una fórmula en Excel sea explicativo. Encuentre más artículos sobre rangos de nombres y fórmulas de Excel relacionadas aquí. Si te gustaron nuestros blogs, compártelo con tus amigos en Facebook. Y también puedes seguirnos en Twitter y Facebook. Nos encantaría saber de usted, háganos saber cómo podemos mejorar, complementar o innovar nuestro trabajo y hacerlo mejor para usted. Escríbanos al sitio de correo electrónico.
El cuadro de nombre en Excel : El cuadro de nombre de Excel no es más que un área de visualización pequeña en la parte superior izquierda de la hoja de Excel que muestra el nombre de la celda o los rangos activos en Excel. Puede cambiar el nombre de una celda o matriz para referencias.
Cómo obtener el nombre de la hoja de la hoja de trabajo en Excel : La función CELDA en Excel le proporciona la información sobre cualquier hoja de trabajo como col, contenido, nombre de archivo,… etc. Aprenda cómo obtener el nombre de la hoja usando la función CELDA aquí.
Cómo obtener el número de fila secuencial en Excel: A veces necesitamos obtener un número de fila secuencial en una tabla, puede ser un número de serie o cualquier otra cosa. En este artículo, aprenderemos cómo numerar filas en Excel desde el inicio de los datos.
Incrementar un número en una cadena de texto en Excel: Si tiene una lista grande de elementos y necesita aumentar el último número del texto del texto antiguo en Excel, necesitará ayuda de las dos funciones TEXTO y DERECHA.
Articulos populares :
Cómo usar la función SI en Excel : La declaración IF en Excel verifica la condición y devuelve un valor específico si la condición es VERDADERA o devuelve otro valor específico si es FALSO.
Cómo utilizar la función BUSCARV en Excel : Esta es una de las funciones más utilizadas y populares de Excel que se utiliza para buscar valores de diferentes rangos y hojas.
Cómo utilizar la función SUMIF en Excel : Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.
Cómo usar la función CONTAR.SI en Excel : Cuente valores con condiciones usando esta asombrosa función. No necesita filtrar sus datos para contar valores específicos. La función Countif es esencial para preparar su tablero.