Computación para los Negocios: Administrador de nombres y la opción "Nombre Definido" y su uso en: Rangos Dinámicos, Gráficos Dinámicos y Reportes Dinámicos.

En la siguiente entrada de este blog explicaremos de manera detallada, mediante la utilización de vídeos e imágenes, la forma de utilizar la función "Nombre Definido" en Excel, la cual, pese a ser una función bastante poco usada, entrega gran utilidad al momento de querer hacer más eficiente el manejo de grandes volúmenes de datos, ya sea para la creación de rangos y gráficos dinámicos, como para simplemente la utilización de una función que haga referencia a un rango definido previamente.

Esperamos que la información entregada aquí sea de gran utilidad, y de fácil entendimiento, el cual fue nuestro principal objetivo durante el desarrollo del blog.

Marco Apablaza

Carolina Garcia

Benjamín León

Bastián Valenzuela

Grupo 3

Computación para los negocios

Facultad de Economía y Negocios

Universidad de Chile

Cómo crear un Rango Dinámico utilizando la función "Nombre Definido”

Un rango dinámico corresponde a un rango de numérico o de texto que se ajusta automáticamente a la cantidad de elementos presentes en él. Visto desde otra perspectiva, cuando uno simplemente define un nombre para un rango, éste es estático, es decir, si se agregan nuevos elementos justo debajo de dicho rango, estos no pasan a estar dentro del rango que definimos previamente, para este problema es que existe la opción de crear rangos dinámicos, en esta ocasión, utilizando la función "Nombre definido".

En el vídeo se muestra la forma de construir rangos dinámicos, y a continuación mostraremos una secuencia de pasos a través de imágenes que busca explicar de manera detallada lo hecho en el vídeo.

Partiremos teniendo en una hoja de Excel una serie de datos, los cuales en este caso corresponde a una lista de nombres con sus respectivos promedios de notas de la Universidad.

El primer paso consta de definir un nombre para dicho rango que contiene los nombres de los alumnos, para esto nos dirigiremos al menú "Fórmulas", Sección "Nombres Definidos", y pulsaremos en "Administrador de Nombres", una vez que se haya desplegado el cuadro del administrador de nombres, debemos hacer click en la opción "Nuevo...", y debemos completar dichos campos de la siguiente forma:

En el campo "Nombre:" definimos cómo queremos llamar a dicho rango, es irrelevante el nombre que se le dé, sin embargo es recomendable escoger uno que ayude a recordar qué información estaremos almacenando en dicho rango, en este caso, utilizaremos "Nombres".

Ahora, en el campo "Hace referencia a:" es donde se encuentra la parte más compleja de la construcción, dado que es, básicamente, lo que hará que el rango sea dinámico.

Para el ejemplo llenaremos este cuadro con la siguiente fórmula:

=DESREF(Hoja1!$B$2;1;0;CONTARA(Hoja1!$B:$B)-1;1)

La cual, mediante la fórmula CONTARA, tal como su nombre lo indica, cuenta los elementos presentes en la columba B, el "-1" que se encuentra justo después de la cuenta corresponde a la sustracción del encabezado al total de la cuenta de elementos.

La fórmula DESREF devuelve una referencia de un rango, dado una cantidad de filas y columnas específico, según la estructura de dicha fórmula, es que se encuentra primero la celda a la que se hace referencia (Hoja!$B$2), luego la fila número 1, la columna 0, el alto correspondiente a la cuenta, y finalmente el ancho de 1. Personalmente esta fórmula consideramos que es poco intuitiva, sin embargo, emulando la estructura que le dimos en esta explicación y en el vídeo del inicio, no debiesen existir problemas para la construcción.

Para el siguiente rango, correspondiente a los promedios, hemos seguido prácticamente los mismos pasos, con la única diferente que al momento de hacer la cuenta, hemos utilizado la función CONTAR, dado que de esa forma sólo se incluirán los elementos numéricos, excluyendo automáticamente el encabezado. Tal como se muestra en la siguiente imagen:

Una vez hecho esto, y tal como mostramos en el vídeo, al agregar un nombre nuevo, o una nota nueva, en las columnas B o C, respectivamente, veremos cómo los rangos se actualizan automáticamente.

Como podemos ver en el vídeo, a manera de probar la efectividad de nuestra construcción de rangos dinámicos, hemos hecho 2 fórmulas que cuenten los elementos de los rangos "Nombres" y "Promedios", (de la forma =CONTARA(Nombres) y =CONTARA(Promedios)), las cuales, al agregar nuevos elementos, dichas cuentas cambian automáticamente, lo que corrobora la creación exitosa de nuestros rangos dinámicos.

Ejemplo aplicación: Creación de Gráfico Dinámico utilizando Rangos Dinámicos

http://www.youtube.com/watch?v=JQyo5alWNUk

Una vez que hemos creado nuestros rangos dinámicos, es posible crear gráficos que se actualicen automáticamente según la cantidad de elementos presentes en las columnas.

Para esto, tal como se puede ver en el vídeo, al momento de crear el gráfico, y seleccionar los datos, haremos lo siguiente: En los valores de la serie, escribimos "=Libro1!Promedios", para hacer referencia al rango que contiene los promedios. Mientras que en los rótulos de nombres, escribiremos "=Libro1!Nombres".

De esta forma tendremos un gráfico que cambiará automáticamente según la cantidad de elementos presentes en dichas columnas.

En el siguiente link se encuentra el archivo sobre el cual se desarrollaron los rangos dinámicos y el gráfico:

http://goo.gl/QHkiC

______________________________________

Cómo crear un Reporte/Gráfico Dinámico utilizando la función "Nombre Definido”

En este vídeo se muestra el resultado esperado al seguir los pasos del siguiente tutorial:

http://www.youtube.com/watch?v=h1uhuRrXFMY

Para desarrollar el ejemplo que exponemos en el video, utilizaremos una tabla que contiene una lista de nombres y sus respectivas notas en cada año de universidad –creadas aleatoriamente para la ocasión- desde la cual obtendremos los datos con que crearemos el gráfico.

En primer lugar, definiremos un nombre que se refiera al rango que contiene los nombres de los alumnos. Para esto, vamos al menú “Fórmulas”, y en la sección “Nombres Definidos” pulsamos en “Crear desde la selección”, cabe destacar que es necesario haber seleccionado las celdas indicadas, en este caso, la columna correspondiente a los nombres, incluyendo el encabezado (celda con el texto “Nombre”). Luego de haber pulsado “Crear desde la selección”, aparecerá un cuadro en que se consulta a partir de qué se desea crear el nombre, normalmente Excel detectará qué es lo que se quiere hacer y dará como predeterminada dicha opción, la cual en este caso corresponde a “Fila Superior”.

De la misma forma en que creamos este nombre, ahora utilizaremos esta función para definir un nombre para cada uno de los rangos que contienen las notas.

Una vez creados los nombres, crearemos una lista desplegable dentro de una celda, con el fin de escoger en dicha lista el nombre sobre el cual uno quiere consultar a través del gráfico dinámico.

En primer lugar seleccionamos la celda (N#1) , luego en el menú Datos, sección Herramientas de datos pulsamos sobre “Validación de datos” y escogemos la primera opción de las que se despliegan (N#2). Nos aparecerá el cuadro que se ve en la imagen, acá escogeremos la opción Lista (N#3) y finalmente en la sección “Origen”, seleccionaremos la lista de nombre, esta vez sin incluir el encabezado “Nombres” (N#4) y le damos a Aceptar, cabe destacar que al seleccionar la columnas de nombres, en el cuadro origen aparecerá “=nombres”, dado que fue el nombre que definimos en el primer paso para dicho rango. Con esto tendremos creada una celda que contiene una lista desplegable con los nombres presentes en la tabla de datos.

Una vez creada la lista desplegable en la celda, debemos definir un nombre para ésta. Para esto, existen dos opciones, siendo la mostrada en la imagen la más simple: Seleccionamos la celda, y en el cuadro superior izquierdo presente en Excel definimos un nombre para dicha celda, en este caso usaremos “TituloGrafico”, dado que luego asociaremos esta celda al título del gráfico para que se actualice automáticamente a los diferentes nombres de cada alumno, cabe destacar que el nombre que uno defina es irrelevante en sí, sin embargo es conveniente escoger nombres que ayuden a recordar fácilmente para qué fueron definidos, y así hacer menos dificultoso el trabajo.

Ahora nos enfrentamos a una de las partes menos intuitivas del proceso, correspondiente a definir un nombre el cual se asocie a los datos de cada uno de los alumnos, el cual será utilizado al momento de escoger los valores de la serie en el gráfico.

Para esto, nos dirigiremos al menú “Formulas” y en la sección “Nombres definidos” pulsamos sobre “Administrador de nombres”, una vez que se muestre el cuadro que contiene todos los nombres que hemos definido en el libro de Excel actual, pulsamos en “Nuevo…” y se nos mostrará el siguiente cuadro:

Nuevamente el nombre es irrelevante, pero es necesario recordarlo fácilmente para los siguientes pasos, en este caso usamos “SerieGrafico”.

En la sección “Hace referencia a”, debemos utilizar la siguiente fórmula:

=INDIRECTO(SUSTITUIR(Hoja1!$A$15;" ";"_"))

La celda a la que se hace referencia en este caso (A15) corresponde a la cual contiene la lista desplegable. La función INDIRECTO se utiliza para interpretar el texto almacenado en dicha celda y convertirlo en el rango que definimos anteriormente.

Dado que en este caso los nombres de nuestra lista contienen espacios, es necesario utilizar la función SUSTITUIR, la cual se encargará de convertir los espacios contenidos en la celda, en guiones bajos, para que el formato sea compatible con el de la herramienta “Nombres Definidos”.

Posteriormente, debemos crear un gráfico -en este caso de líneas-. Para crearlo fácilmente seleccionamos las dos primeras filas de la tabla de datos, es decir, la fila que contiene los encabezados (Nombre, 1er Año, 2do Año, etc) y la fila correspondiente a “Marco Apablaza” con sus respectivas notas, nos vamos al menú “Insertar” y seleccionamos Gráfico de Lineas, el gráfico se construirá automáticamente de la forma que vemos en la imagen.

Luego, para que el gráfico sea dinámico, hacemos click derecho sobre la línea y pulsamos sobre la opción “Seleccionar datos…” y luego, en el siguiente cuadro que aparecerá, pulsamos en “Editar”, con lo cual nos encontraremos con el siguiente cuadro:

En la sección “Nombre de la serie” y “Valores de la serie”, estarán la celda pertenecientes a la tabla de datos originales de la cual se creó el gráfico, debemos modificar esto utilizando los nombres definidos en los pasos anteriores. De esta forma, en “Nombre de la serie” escribiremos =Libro4!TituloGrafico y en Valores de la serie =Libro4!SerieGrafico.

Cabe destacar que es necesario mantener la referencia a la hoja en la que se está trabajando, es decir en este caso, “Libro4!”, incluyendo el signo de exclamación.

Luego de esto, tendremos un gráfico dinámico tal como vimos al principio, al cual sólo le faltará pulir detalles tales como fijar los valores mínimo y máximo de los ejes, con el fin de hacer más comparables los resultados entre cada uno de los alumnos.

Hacemos click derecho sobre el eje de las ordenadas, y pulsamos sobre la opción "Dar formato a eje...". En el cuadro que aparecerá debemos fijar los valores máximo y mínimo como se ve en la siguiente imagen.