Construir un tablero de comandos (Dashboard) en Excel – cuarta nota

jueves, diciembre 17, 2009

En esta cuarta nota mostraremos el uso de otra herramienta importante para la construcción de tableros de comandos: la función IMPORTARDATOSDINAMICOS.

Esta función nos permite extraer datos de una tabla dinámica. La importancia de esta herramienta reside en el hecho que las tablas dinámicas son el método más eficiente de Excel para totalizar y analizar datos de una base de datos.

Vamos a mostrar un ejemplo basándonos en los datos del ejemplo de las notas anteriores.

El cuaderno con el ejemplo se puede descargar aquí.

Después de totalizar los datos en esta tabla dinámica



donde hemos agrupado los datos por zonas, años y trimestres.

El tablero de comandos que construiremos nos permitirá extraer datos dinámicamente para realizar comparaciones por años, trimestres y zonas




En este video pueden apreciar el funcionamiento del dashboard



El cuaderno contiene cuatro hojas






Reporte: contiene el tablero de comandos

td: donde reside la tabla dinámica que totaliza nuestros datos; esta hoja es el “motor” del modelo

BD: la base de datos

definiciones: esta hoja contiene los valores de dos rangos nominados (nombres) que usamos en las listas desplegables del dashboard.

Nuestro tablero de comandos se divide en dos zonas: la zona de los parámetros



donde establecemos los parámetros de los datos a comparar, y la zona de los resultados



Las columnas A y B están ocultas. Estas columnas nos sirven para manejar rangos auxiliares, cuyo objetivo mostraremos más adelante.

El rango F4:H6 es donde ponemos los parámetros del análisis, eligiéndolos de listas desplegables cuyas definiciones pueden verse en la hoja “definiciones”.

Antes de poner la fórmula en la celda F10 nos aseguramos que el generador de la función IMPORTARDATOS DINAMICOS esté activo.

En Excel 2007 seleccionamos una celda de la tabla dinámica y en la cinta de Opciones de las herramientas de tablas dinámicas abrimos la pestaña Tabla Dinámica-Opciones



En Excel Clásico (97-2003) encontramos esta opción en la barra de herramientas de las tablas dinámicas



Una vez que esta opción está activa creamos una referencia a la celda correspondiente en la tabla dinámica



Excel crea automáticamente la función con los argumentos relevantes

=IMPORTARDATOSDINAMICOS("Clientes ";td!$A$3;"Fecha";1;"Zona";"Este";"Años";2007)

Analicemos esta fórmula:

el “1” que aparece después de “Fecha” es la referencia al Trimestre 1 (el primer elemento del campo Fecha). Este elemento debe ser dinámico, es decir, cambiar de acuerdo a la elección del usuario.

Este” define que zona hemos elegido. También este elemento debe cambiar dinámicamente.

2007” es el otro elemento que deberá cambiar de acuerdo a la elección del usuario.

Estos elementos serán reemplazados de esta manera:

=IMPORTARDATOSDINAMICOS("Ventas ";td!$A$3;"Fecha";$A$5;"Zona";F6;"Años";F4)

$A$5 reemplaza el índice del elemento del campo fecha.

La celda A5 contiene la fórmula

=COINCIDIR(F5;Trimestre;0)

cuyo valor sirve como argumento en las funciones IMPORTARDATOS DINAMICOS con las que extraemos los datos



Lo mismo con B5 que contiene la fórmula =COINCIDIR(H5;Trimestre;0)



Los argumentos “Año” y “Zona” podemos pasarlos directamente de las celdas F4 y F6 donde el usuario hace su elección.
Hacemos lo mismo con el resto de las celdas en las filas 10 y 11. Con esto nuestro modelo es absolutamente dinámico.

Las celdas F12, H12 y J12 contienen simples fórmulas para calculas el cambio porcentualmente.

Finalmente agregamos los gráficos con técnicas como las que he mostrado en la nota anterior.

El cuaderno se puede descargar aquí.




Technorati Tags:


3 comments:

Luis Fernando Rocha (Bolivia),  31 diciembre, 2009 21:05  

Estimados Amigos,

Es realmente agradable encontrar espacios como este, en los que podemos compartir el conocimiento... Jorge, te felicito, y espero que en este lugar puedan ayudarme con las dudas que tenga en Excel. Así mismo aportaré con mi granito de arena a esta noble iniciativa, siempre y cuando eso esté permitido.

Gracias Amigos.

William Mendez 07 diciembre, 2015 19:24  

Hola estoy interesado por el cuadro de mando, existe algún otro medio de pago, que no sean con tarjetas de crédito.?

Jorge Dunkelman 12 diciembre, 2015 09:32  

Hola, no. El único método es con PayPal.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP