4

Megaguía: Los nombres en Excel

En esta megaguía te hablaré sobre los nombres de Excel, una herramienta que apenas se utiliza pero que facilita mucho el trabajo con rangos de celdas y con fórmulas. ¿Quieres ahorrar tiempo? Pues utiliza esta herramienta.

¿Qué es un nombre?

Básicamente un nombre es una palabra que es capaz de sustituir a un rango de celdas. Por ejemplo, si asigno el nombre “datos” al rango A1:A100, luego lo podré utilizar de la siguiente forma:

=SUMA(datos)

nombres excel definidos

Esta fórmula sustituye a =SUMA(A1:A100) cuando quieres sumar los valores de este rango.

La utilidad de los nombres de Excel la encuentras cuando te puedes olvidar sin problemas de las referencias de las celdas. Una vez que has definido un nombre para un rango, lo puedes utilizar como si fueran referencias de celdas en cualquier fórmula. Mira unos ejemplos:

=MAX(datos)

=BUSCARV(“425”;datos;1;0)

=PROMEDIO(datos)

Además de rangos de celda, los nombres se utilizan también para almacenar valores constantes y  sustituir fórmulas complejas que simplifican otras fórmulas. Esto te lo mostraré más adelante.

Cómo se crea un nombre

Crear un nombre es muy sencillo. Sólo tienes que seleccionar las celdas que quieras agrupar y escribir el nombre en el cuadro de nombres. Al finalizar pulsa Intro para crear el nombre.

nombre definido excel

Para comprobar que has introducido el nombre correctamente, haz clic en una celda diferente y pulsa en el desplegable del cuadro de nombres. Si aparece el nombre que acabas de introducir, significa que ya puedes utilizarlo en los cálculos.

Selecciónalo y comprobarás que las celdas que contiene el nombre, se seleccionan también. Incluso, cuando te encuentras en otra hoja del libro, es posible dirigirte al rango seleccionando el nombre del cuadro de nombres.

Si necesitas crear muchos nombres diferentes puede que te interese hacerlo automáticamente. Excel cuenta con una opción llamada Crear desde la selección (ficha Fórmulas) que crea automáticamente los nombres de los rangos con los valores que se encuentran al lado.

cinta de opciones fórmulas excel

En primer lugar selecciona los datos que vas a incluir en el nombre y luego accede a la ficha Fórmulas > Crear desde la selección. Para hacerlo más rápidamente pulsa Ctrl + Mayús + F3. Se mostrará el cuadro de diálogo Crear desde la selección:

cuadro crear selección excel

Observa en la imagen cómo los nombres de los rangos están situados en la fila de arriba y en el cuadro de diálogo he seleccionado la opción Fila superior. Al hacer clic en Aceptar, se crean automáticamente los nombres de Excel. Puedes comprobarlo haciendo clic en el desplegable del cuadro de nombres.

desplegable nombres excel

Una vez creado un nombre es posible modificar el rango al que hace referencia. Para ello pulsa Ctrl + F3 para acceder al Administrador de nombres. También puedes encontrar el botón correspondiente en la ficha Fórmulas.

administrador excel nombres

Una vez modificado el rango pulsa en el botón Cerrar para guardar los cambios.

Si no quieres escribir el rango en el cuadro, puedes seleccionar las celdas directamente con el ratón, ya que el Administrador de nombres es un cuadro “no modal” (puedes seguir usando Excel sin necesidad de cerrarlo). Es la mejor opción; así te evitas errores en la introducción de las referencias.

Algunas normas para establecer un nombre

Antes de crear un nombre debes tener en cuenta las siguientes reglas:

  • Deben comenzar con una letra, un guión bajo (_) o una barra invertida(\).
  • No pueden contener espacios ni signos de puntiación.
  • No pueden coincidir con referencias de celdas, es decir, no puedes crear el nombre AB1123 o A1.
  • Puedes crear un nombre de una sola letra, pero no puedes utilizar las letras “r” y “c”, ya que están reservadas.
  • Los nombres no distinguen entre mayúsculas y minúsculas. “DATOS” y “datos” es lo mismo para Excel.

El Administrador de nombres

El Administrador de nombres es el cuadro en el que se encuentran todos los nombres definidos. Desde aquí puedes realizar cualquier operación con nombres, incluso crearlos además de poder hacerlo como te he mostrado antes.

Como digo, desde el administrador de nombres puedes crear, modificar y eliminar nombres.

nombres cuadro excel

En la parte superior izquierda se encuentran los botones correspondientes para crear, editar y eliminar los nombres. A la derecha puedes ver un botón de filtro. Muy útil para mostrar solamente los nombres deseados cuando el archivo contiene una cantidad considerable.

En la parte central del cuadro se muestra la lista de los nombres que tengas ya definidos. En esta lista se muestra el valor que contiene el nombre (o la matriz de valores), el rango al que hace referencia, el ámbito de uso (que te los mostraré más abajo) y las notas o comentarios que contenga. El cuadro de la parte de abajo ya te lo he mostrado antes, y sirve para modificar rápidamente el rango que contiene el nombre.

Mostrar todos los nombres de rangos

Ya has visto lo accesible que es el Administrador de nombres. Lo tienes en la ficha Fórmulas y puedes abrirlo más rápidamente pulsando Ctrl + F3.

Pero si no es suficiente para ti, puedes tener un mayor control de los nombres definidos, pegando la lista completa en cualquier parte de la cuadrícula que desees. Haz clic en Fórmulas > Utilizar en la fórmula > Pegar nombres o pulsa directamente la tecla F3.

nombres rangos excel

En el cuadro de diálogo que aparece, haz clic en el botón Pegar nombres. Los nombres que tengas almacenado se pegarán a partir de la celda activa.

Otra forma de ver los nombres de los rangos es reducir el zoom de la ventana de Excel por debajo del 40%. Los rangos con nombre se mostrarán dibujados en la pantalla.

zoom rangos excel

Otros usos de los nombres

Además de almacenar rangos de celdas, en un nombre también puedes guardar valores fijos o fórmulas.

Almacenar constantes

La velocidad de la luz es de 299 792 458 metros por segundo, un número que seguramente ya se te haya olvidado… Para evitar tener que recordarlo, puedes guardarlo en un nombre y utilizarlo, como otros nombres, en una fórmula.

Abre el Administrador de nombres haciendo clic en Fórmulas > Administrador de nombres. A continuación haz clic en Nuevo y rellena los datos que se solicitan:

nuevo nombre excel

  • Elige el nombre que le quieras dar al nombre (por ejemplo V_Luz).
  • Selecciona el ámbito donde este nombre tendrá validez (puedes elegir cualquiera de las hojas que contiene el libro o todas ellas).
  • Si quieres poner un comentario como recordatorio del valor que almacena, puedes hacerlo. Es opcional.
  • En Hace referencia a tienes que escribir el valor de la constante (en este caso 299792458).

Cuando hagas clic en Aceptar, el nombre estará listo para su uso. Haz la prueba escribiendo en cualquier celda =V_Luz.

constantes excel nombres

 

Almacenar fórmulas

También puedes crear un nombre para guardar una fórmula de la misma forma que lo has hecho para el valor fijo (utilizando el Administrador de nombres).

Por ejemplo, puedes crear un nombre que calcule la tasa de interés mensual con respecto a un interés anual. Observa la siguiente imagen:

formulas nombres excel

Cuando escribes en una celda =Int_men, el nombre obtiene el valor de A1 y lo divide entre 12 mostrando el resultado.

interes nombres excel

Observa que la referencia de la celda utilizada en el cuadro Nuevo nombre es absoluta. Esto es así por defecto cuando seleccionas los rangos de celdas con el ratón, pero se puede cambiar para que su utilidad sea aún mayor.

Imagina que tienes la siguiente tabla:

Como ves, muestra las ventas que una empresa ha tenido en un semestre. Los informes de ventas de cada semestre tienen la misma estructura, así que puedes crear un nombre que haga referencia a las celdas que se encuentran inmediatamente a la izquierda de la celda donde se encuentra la fórmula. Para ello tendrías que crear el siguiente nombre:

Como puedes observar, en la fórmula he modificado el rango para que no aparezcan referencias absolutas.

Desde ahora, al introducir el nombre en una celda, calculará la suma de las seis celdas que se encuentren inmediatamente a la izquierda de ella.

ventas nombres rango excel

 

Ventajas de utilizar nombres

Uno de los grandes beneficios de utilizar nombres es que hacen las fórmulas más fáciles de entender y de editar. Queda más clara la fórmula =Capital*Interes/Tiempo que =A1*A2/A3.

Además, es más fácil reutilizar una fórmula. En el ejemplo de las ventas semestrales, si quieres que todos los productos muestren un 5% más en el total del semestre, sólo debes cambiar la fórmula almacenada en el nombre. Así evitas tener que cambiar todas las fórmulas una a una.

Otra ventaja del uso de nombres es que puedes utilizarlos para navegar por el libro. Al principio te mostré que cuando seleccionas un nombre del cuadro de nombres, el rango al que hace referencia se muestra seleccionado en la pantalla.

navegación nombres excel

Por último, es posible utilizar los nombres como marcadores de hipervínculos, es decir, que los nombres también aparecen en el cuadro Insertar hipervínculo, en la sección Lugar de este documento.

hipervinculos nombres excel

 

Ámbitos de los nombres

¿Qué es eso de ámbito de los nombres? Pues es algo que determina si un nombre se puede utilizar en todo el libro (global) o en una determinada hoja (local).

El uso de nombres con el ámbito correcto es especialmente útil para hacer que solo se carguen en la memoria los nombres que podrían utilizarse, acelerando la velocidad de cálculo. También evita cometer errores al seleccionar nombres incorrectos.

El ámbito de hoja (local) significa que el nombre sólo se podrá utilizar en una determinada hoja, de forma que en un libro con varias hojas, podrás utilizar el mismo nombre para cada una.

Imagina que tienes una hoja por cada mes del año con las ventas de la empresa. Definiendo el mismo nombre de forma local para cada una de las hojas, podrías hacer la suma de todos los meses de forma muy sencilla:

=Enero!Ventas+Febrero!Ventas+Marzo!Ventas…..

Algunas macros para gestionar nombres

Con VBA es posible manipular todo tipo de objetos y, con los nombres no iba a ocurrir lo contrario. Te muestro algunas macros que podrían serte útiles a la hora de trabajar con nombres:

Crear nombres con VBA

Sub CrearNombres()

Dim celda As Range Dim rng As Range Dim Nombrerango As String Dim Nombrecelda As String

‘Referencia a celda individual (ámbito de libro) Nombrerango = “Precio” Nombrecelda = “D7”

Set celda = Worksheets(“hoja1”).Range(Nombrecelda) ThisWorkbook.Names.Add Name:=Nombrerango, RefersTo:=celda

‘Referencia a celda individual (ámbito de hoja) Nombrerango = “Ventas_Mes” Nombrecelda = “A2”

Set celda = Worksheets(“hoja1”).Range(Nombrecelda) Worksheets(“hoja1”).Names.Add Name:=Nombrerango, RefersTo:=celda

‘Referencia a rangos de celda (ámbito de libro) Nombrerango = “Mi_Rango” Nombrecelda = “F9:J18”

Set celda = Worksheets(“hoja1”).Range(Nombrecelda) ThisWorkbook.Names.Add Name:=Nombrerango, RefersTo:=celda

‘Nombre de celda oculto (no se muestra en el administrador de nombres) Nombrerango = “Usuario” Nombrecelda = “sproper”

Set celda = Worksheets(“hoja1”).Range(Nombrecelda) ThisWorkbook.Names.Add Name:=Nombrerango, RefersTo:=celda, Visible:=False

End Sub

Recorrer nombres con VBA

Sub Recorrer_Nombres()

Dim Nombre As Name

‘Recorre todos los nombres del libro For Each Nombre In ActiveWorkbook.Names Debug.Print Nombre.Name, Nombre.RefersTo Next Nombre

‘Recorre todos los nombres de la hoja For Each Nombre In Worksheets(“Hoja1”).Names Debug.Print Nombre.Name, Nombre.RefersTo Next Nombre

End Sub

Eliminar nombres con VBA

Sub Borrar_Nombres()

Dim Nombre As Name Dim BorrarCuenta As Long

‘Eliminar también áreas de impresión? Respuesta = MsgBox(“¡Quieres saltar las áreas de impresión?”, vbYesNoCancel) If Respuesta = vbYes Then SkipPrintAreas = True If Respuesta = vbCancel Then Exit Sub

‘Recorre cada nombre y lo elimina For Each Nombre In ActiveWorkbook.Names On Error GoTo Salta

If SkipPrintAreas = True And Right(Nombre.Name, 10) = “Área_de_impresión” Then GoTo Salta

Nombre.Delete BorrarCuenta = BorrarCuenta + 1

Salta:

Next

‘Gestión de errores On Error GoTo 0

‘Muestra el resultado If BorrarCuenta = 1 Then MsgBox “Se ha eliminado 1 nombre del libro.” Else MsgBox “Se han eliminado ” & BorrarCuenta & “] nombres del libro.” End If

End Sub

Si ya utilizabas los nombres de Excel, me encantará leer en los comentarios para qué los utilizas y en qué momento los conociste. Si esta es tu primera toma de contacto, también me gustará leer qué te ha parecido el artículo y la herramienta.

¡Compartir es vivir!
Sergio
 

La destreza y el perfeccionismo quizá sean las dos virtudes que me permiten ayudar a mis clientes a facilitar las tareas administrativas de sus negocios.

Haz clic aquí para dejar un comentario 4 comentarios
Carlos Clemente - 29/01/2018

Hola:
Me ha gustado tu articulo sobre los nombres porque habia cosas que no conocia de su uso, como la posibilidad de usarlo en rangos dinamicos usando referencias relativas en vez de absolutas; seguro que lo probare.

Yo hace años confeccione una hoja de excel para el calculo de un cuadro de amortizacion de prestamos, y use un nombre que no has comentado aqui; se trata del nombre “Área_de_impresión” que se crea automaticamente cuando fijas un area de impresion en el menu de configrar pagina. El uso que yo le di es hacerlo como un rango dinamico que se recalcula cuando se modifican las celdas que hacen que el cuadro de amortizacion cambie su longitud; para ello cree una macro haciendo uso de la funcion “Union” que fija el rango de impresion del cuadro de amortizacion.
La formula completa es la siguiente:
If Union(Target, Range(“años”)).Address = Range(“años”).Address Then
Worksheets(“Cuadro amortizacion”).PageSetup.PrintArea = Range(Cells(1, 1), Cells(Range(“cuotas”) + 7, 12)).Address
End If
El nombre “años” hace referencia a los años de amortizacion del prestamo, que logicamente si se modifican afectará al area de impresion de la tabla de amortizacion. Y el nombre “cuotas” tiene el calculo del total de cuotas a pagar que por ende seran las lineas finales que tendra el rago final. Los numeros que sumo son por la propia configuracion de las filas y columnas para que solo salga la informacion que necesito.
Cada vez que se modifican los años del prestamo, el area de impresion se adapta al total de cuotas para no tener que imprimir hojas de mas.
Espero os sea de utilidad. Saludos
Espero

Responder
    Sergio - 29/01/2018

    Hola, Carlos! Te agradezco tu sugerencia.

    La verdad es que nunca se me ha presentado la oportunidad de hacer que el área de impresión sea dinámica, pero me parece muy buena idea que no se me había ocurrido…

    Si tuviera que utilizar tu aporte traduciría la macro que propones en una fórmula de hoja. Siempre que puedo evito utilizar VBA.

    Gracias de nuevo.

    Responder
William - 08/02/2018

Hola Sergio buenas tardes

Amigo, una consulta:

Tengo un archivo el cual lo estoy manejando con macros, solo pesa 2.336 KB, pero usa muchas formulas, si se puso extremadamente lento al ejecutar cualquier macro, incluso sin ejectura macros, al hacer cuanquier modificaión o cálculo en alguna de las hojas, se tarda demasiado.

Cómo pudeira mejorar el tiempo en la ejecución de las rutinas.

Saludos desde Venezuela
William

Responder
    Sergio - 08/02/2018

    Hola, William!
    Seguramente podrás sustituir muchas de las fórmulas por un nombre.
    Trata de crear una fórmula que sirva para todas las celdas prestando atención a los tipos de referencias que utilizas en las celdas.
    Eso te acelerará mucho el libro.

    Responder

Escribe una respuesta: