En esta entrada me centraré en la explicación del modelo de nuestro ejemplo mostrando los elementos más importantes y las fórmulas en uso.
Desde ya, la mejor forma de entenderlo y estudiarlo es descargar y “disecar” el archivo.
Para descargar el archivo hay que seguir este enlace.
El modelo cuenta con siete hojas
La hoja Dashboard contiene el tablero de comandos y es en la que el usuario interactúa. Esta hoja tiene dos columnas ocultas: A y B
A y B son columnas auxiliares que controlan los datos visibles en la tabla Detalle por Sucursal
En el rango B23:B32 ponemos la fórmula
=DERECHA(D23;LARGO(D23)-HALLAR(" ";D23))
cuyo cometido es extraer el número de sucursal.
En el rango A23:A32 ponemos la expresión
D23=0
Al elegir una de las zonas de la lista desplegable, sólo aparecen las sucursales pertenecientes a la zona. Esto hace que las celdas relevantes del rango A23:A32 den el resultado VERDADERO. Ese resultado controla el formato condicional de la tabla que es el responsable de ocultar las celdas de las sucursales que no pertenecen a la zona elegida
La hoja tiene tres listas desplegables. Las dos superiores, que controlan la fecha de comienzo y fin del período analizado, están hechas usando los controles de la barra de Formularios.
La lista de valores está controlado por el nombre tdPeriodos que es un rango dinámico basado en la tabla dinámica de la hoja “tdZonas” que analizaremos más adelante. Todos los nombres usados en el modelo están descritos en la hoja “Documentacion”.
La lista desplegable para elegir las zonas en la tabla Detalle por sucursal está basada en un control de la barra Cuadro de Controles (ActiveX).
Los valores de la lista están controlados por el nombre “Zonas” y el valor elegido es guardado en la celda E18 quedando oculto por el control.
El valor de esta celda determina qué sucursales serán exhibidas en la tabla. Para este objetivo usamos la fórmula
=INDICE(INDIRECTO("col"&$E$18);FILA()-20)
donde INDIRECTO("col"&$E$18) componen el nombre del rango dinámico que contiene las sucursales de cada zona.
Así si elegimos la zona Norte, por ejemplo, esta expresión da como resultado “colNorte”, cuya definición puede verse en la hoja Documentacion.
Los datos residen en la hoja BD (obviamente, base de datos), donde son captados con alguno de los métodos mencionados en la nota anterior.
Estos datos son elaborados por tablas dinámicas en las hojas “tdZonas” y “tdSucursales”.
Estas hojas son los verdaderos motores del modelo. Cada una de ellas contiene una tabla dinámica (de allí “td”) que totalizan los datos de acuerdo al corte necesario (por zona o por sucursal).
Los rangos de estas tablas están definidos por el mismo rango dinámico (“tdRango”, documentado en la hoja Documentacion) de manera que no haga falta actualizar el rango cada vez que agregamos datos.
Tanto la tabla de Zonas como la de Sucursales utilizan una fórmula compuesta por las funciones SUMA, INDIRECTO y DIRECCION para extraer los datos correspondientes.
La fórmula en la celda E7 es
=SUMA(INDIRECTO(DIRECCION($E$3+5;3;;;"tdZonas")&":"&DIRECCION($E$4+5;3)))
En esta fórmula $E$3 es el número de orden de la fecha de incio (así funciona el control combobox de la barra de Formularios); le sumamos 5 para ubicarnos en la fila correspondiente de la hoja “tdZonas” (ver la explicación en el párrafo siguiente).
Lo mismo con $E$4. Usamos INDIRECTO para convertir el texto resultado de DIRECCION en un rango que la sunción SUMA pueda usar.
Las listas de zonas y sucursales están ubicadas en la hoja “Parametros”.
Los gráficos de la hoja Dashboard son imágenes capturadas con la herramienta Cámara fotográfica de Excel. Los verdaderos gráficos están en la hoja “graficos”.
Esta técnica nos permite no sólo controlar mejor el diseño de los gráficos sino también documentar el funcionamiento de éstos. Por ejemplo, los nombres en uso en el gráfico del detalle mensual
El último elemento importante del modelo es una pequeña macro que nos asegura que cada vez que terminamos de agregar datos en la hoja BD, las tablas dinámicas sean actualizadas.
Como sabemos, las tablas dinámicas no tienen un mecanismo de actualización automático. Esta macro es un evento de la hoja BD que es disparado cada vez que desactivamos la hoja
Technorati Tags: MS Excel
Primero que todo muchas gracias por la informacion que entregas.
ResponderBorrarSegundo, las personas que vivimos en America y no tenemos tarjeta de credito, que posibilidad tenemos de obtener el archivo?...
Hola Jorge...estoy muy interesado en revisar tu archivo, sin embargo, no dispongo de euros para pagar. Es posible que se pueda pagar en dolares?. en caso de ser afirmativa tu respuesta, cuanto seria el costo?
ResponderBorrarGracias por tu atencion
Ariel, creo que PayPal te permite también hacer pagos con otros medios como cuenta bancaria o tarjeta de débito. Te sugiero que te fijes en el sitio de PayPal cuáles son las posibilidades. Por mi parte estoy tratando de averiguar si existen otras posibilidades seguras de aceptar pagos no basados en tarjetas de crédito.
ResponderBorrarHola Joaquín,
ResponderBorrarel pago se hace en Euros, pero en tu cuenta aparecerá la suma en la moneda local (o la moneda de tu cuenta). PayPal está convirtiendo los 5 Euros en algo algo así como 7,15 Dólares
Hola, Jorge:
ResponderBorrarme interesa mucho este tema, pero tengo problema con la planilla, al ingresar a la hoja BD, me arroja errores ya que en las columnas venta, clientes y ganancia no hay datos lo que provocan datos de error en las tablas dinamicas, dejando el dashboard con error, Saludos
Hola Francisco,
ResponderBorrarel modelo viene con datos que podés reemplazar con los tuyos. Si borrás todos los datos de BD y tratas de actualizar las tablas dinámicas, recibirás un mensaje que no se puede actualizar ya que no hay datos.
De todas maneras, puede ser que el nombre dinámico que define la base de datos (teRango)de las tablas dinámicas se haya corrompido. Podés reestablecerlo con est fórmula
=DESREF(BD!$B$2;0;0;CONTARA(BD!$B:$B);CONTARA(BD!$2:$2))
Si no lográs corregir el problema, mandame el archivo por mail.
Estimado Jorge:
ResponderBorrarEl problema era que la formula de la tabla estaba en ingles, lo solucioné con la función "aleatorio.ente()".
seguire descubriendo la planilla, excelente trabajo.
Gracias. un Saludo.
Hola Jorge, me he descargado tu archivo y he intentado hacerlo yo paso por paso, pero me es imposible....
ResponderBorrarPodrias publicar los pasos a seguir en orden?
Estoy muy interesada en el tema de los cuadros de mando, creo que son muy útiles.
Un saludo y muchisimas gracias por adelantado!!
Más que una nota estoy pensando en armar una animación que muestre los pasos. Pero llevará algún tiempo.
ResponderBorrar