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
