martes, 13 de agosto de 2013

Ejemplo de Cuadro de Mando aplicado a la EPA del 2º Trimestre del 2013

Los últimos post del blog sobre Excel, están siendo dedicados a cómo construir un cuadro de mando. Una herramienta que te permite condensar la información que necesitas en unos indicadores que puedan ser analizados de un golpe de vista, de la misma forma que el salpicadero de un coche te facilita la información para la conducción.
Un buen ejemplo del uso de los cuadros de mando es la aplicación que la asociación 3e a puesto en circulación sobre los datos trimestrales de la EPA. Puedes descargarla aqui.

EPA, Encuesta de Población Activa.

Cada tres meses asistimos al mismo ritual, el Instituto Nacional de Estadística de España (INE ) hace pública la Encuesta de Población Activa( EPA). La mañana de ese día, políticos, periodistas, economistas, profesionales y toda la sociedad Española sufre una avalancha de datos, de titulares, de análisis, informes, entrevistas, etc. Todos encuentran en la EPA la justificación que necesitaban.
Asistimos al Bigdata, a una avalancha de datos. Cada trimestre son encuestados mas de 66.000 hogares, de los que obtienen información sobre 171.000 individuos, y que es tabulada en 108 campos. Cada EPA tiene 19 millones de celdas de información para tratar. Esta información es suministrada en los llamados ficheros de microdatos de la encuesta.
El análisis de toda esta ingente información en bruto dura lo que dure tu curiosidad, y las ganas de profundizar en ella, alguno de estos análisis serán relevantes otros no, es fácil que el detalle te impida tener una visión en conjunto, de que “los arboles no te dejen ver el bosque”. Por ello, necesitas de una herramienta potente que te permita manejar, mover y explorar la información.

La utilidad buscada.

La “Asociación 3e, Educación, Esfuerzo y Excelencia”, se encontraba en esa situación. Dicha  asociación pretende un cambio y mejora del sistema de becas para los estudios universitarios, más justo y eficaz. Uno de los proyectos de trabajo de dicha asociación es la de crear un Observatorio Laboral de la Educación, que tiene como objetivo facilitar información actual de cómo el nivel de educación, junto con otros factores, constituye y condiciona el capita humano de cada individuo, y este a su vez determina los rendimientos económicos, las posibilidades de buscar empleo, de tenerlo, de mantenerlo. Es por ello que cuando se analiza cómo financiar la inversión social de la educación universitaria, se estudie, qué rendimiento tiene esa inversión, es decir qué hacemos con todos esos conocimientos.

La solución, del “cuadro de mandos” al “panel de datos”.

La gente de la Asociación 3e buscaban una herramienta que pudiese mostrar rápidamente gran cantidad de información, condensarla y plasmarla en una imagen que ayudase a profundizar en la misma. Y la solución vino de los cuadro de mando, del panel de control que manejase y moviese la información aportada en los ficheros de microdatos, de la EPA.

No obstante lo importante es el informe que han elaborado sobre la situación del empleo, el paro y la educación durante estos años de crisis: con información novedosa sobre sobreeducación, impacto de las buenas calificaciones en las tasas de paro, etc.

Puedes utilizar un cuadro de mandos, para manejar toda esa información. El cuadro de mandos se transforma en un panel de datos interactivo, donde puedes filtrar la información por diversos criterios, y comparar, estudiar, relacionar diversas características de dicha encuesta, y plasmarla en una serie de gráficos que se van modificando en tiempo real.

El modelo de panel de datos, se asienta en una Excel, que utiliza una combinación de:
·         una base de datos,
·         de tablas dinámicas,
·         de gráficos,
·         y de segmentos asociados a ellas.

De esta forma cada vez seleccionas un estuche o una combinación de ellos, estas interactuando con los segmentos, y la selección realizada es traducida en filtros y criterios, que son aplicados a  la base de datos, y esta mostrada en las tablas dinámicas y presentada en gráficos.

El modelo de Excel “Panel de Datos EPA 2013-2T”, esta construido para las versiones de Excel 2010 y posteriores, y guardada en un libro binario de Excel, que es el formato de fichero optimizado para cargar y guardar libros de Excel de gran tamaño, ya que el modelo contiene la información procesada de las EPA’s 2005-2013,  estamos hablando de más de 34.000 registros de información.

Para confeccionar dicho panel de datos, se han usado las técnicas que aparecen en la serie de posts, que aparecen en este blog con el título de ¿Cómo construir un cuadro de mando en Excel?

  Puedes descargarte el fichero en la web de la asociación 3e, www.mibeca.es (aquí). 





Y puedes ver este video tutorial.


sábado, 29 de junio de 2013

Cómo hacer un cuadro de mando interactivo en Excel paso a paso? Ordenación de los segmentos de las TDD (6).


En el post del pasado 28/06/13, dejamos la 2ª segmentación y su TDD a medio terminar. Nos encontramos con un problema de ordenación que afectaba a la ergonomía del uso de los filtros de segunda TDD y su segmento correspondiente.  Vamos que los estuches de los segmentos se ordenan alfabéticamente, sea del tipo que sea el elemento, contra toda lógica. 



La solución a este problema está relacionado con el concepto de lista personalizada, del que hablamos en el post del 10 de mayo del 2013 “Listas personalizadas en Excel: Aprende a usarlas y crearlas y trabajaras más eficazmente.

Primero, seleccione el bloque A6: A13. Como se trata de una sección de una TDD, puedes seleccionar automáticamente todo el bloque. Sigue los pasos que indicaba en el citado post, aunque este para la versión Office:mac, para un especialista de Excel como tu, no va a suponer ninguna dificultad, el crear una lista personalizada. Añade también los valores de datos de "<4 y="">28".


A continuación habilita en la cinta "Opciones" “Herramientas segmentos", a su vez en  "Configuración de segmentos de datos" y activa la opción de "Usar listas personalizadas al ordenar".

“Et voilà”, deberías tener una tabla de datos y un segmento añadido con esta pinta.

¿Sabes alguna forma más sencilla de hacer esto? Pues deja un mensaje por favor, compartir hace crecer al que da como al que recibe el consejo.






Puedes ver los pasos a seguir en este video





Colección de post sobre “panel de control”


  1. Primer paso: construimos una base de datos aleatoria.
  2. Creación de la Primera tabla dinámica. Los 10 mejores clientes.
  3. De las TDD a la segmentación de tablas. Una novedad de Excel 2010.
  4. Construcción de la 2ª TDD. Duración de las llamadas telefónicas y crear un segundo segmento.
  5. Ordenar los elementos de los segmentos, para que aparezca como nosotros queremos.
  6. Creación de una tercera tabla y su segmento correspondiente. Las ventas por meses.
  7. Empezamos con los gráficos anidados a las TDD.
  8. El cuadro de mando en bruto, sin pulir.
  9. Cómo doy formato a los segmentos
  10. Cuadro maquillado y listo para la fiesta.
  11. Vincular tablas y segmentos, que este todo interrelacionado.



En la web de Flashforward, podrás descargarte el modelo en Excel que vamos a utilizar. El fichero tiene alguna macro, pero no son necesarias para el funcionamiento del cuadro de mando, aquí encontrarás la base de datos y el cuadro de mando terminado, así como una serie de hojas donde podrás ir haciendo el cuadro de mando paso a paso. Con cada post lo iremos ampliando y actualizando el modelo.

viernes, 28 de junio de 2013

Cómo hacer un cuadro de mando interactivo en Excel paso a paso? Construcción de una 2ª TDD y de un segundo segmento. (5)



Seguimos con el modelo ”Cuadro de mando”, y las bases de datos construidas sobre pedidos realizados por un equipo de tele operadores, y con la que estamos trabajando los últimos post..
En este post construiremos una segunda TDD en la que vamos a calcular un tabla de frecuencias de la duración del tiempo de las llamadas comerciales medidas en minutos. Todo ello puedes encontrarlo en el modelo Cuadro de mando v1.5 Para ello nos situamos en la hoja  “03”y en la celda A5, y en la cinta de menú en la sección de “Insertar  y pulsamos  Tabla dinámica”, con la tecla de función F3, podremos indicarle a Excel que utilice como fuente de datos le rango  al que hemos dado el nombre de “Base”, y que contiene la BD.


 Tienes que obtener algo así, y dado que la duración de las llamadas tiene una dispersión, para poder analizar mejor y más rápidamente la información vamos a agrupar las llamadas por rangos.
Sitúate en el campo de etiquetas de fila, y clica el botón derecho del ratón. En el emnu contextual, encontrarás el comando “Agrupar…”







Tienes que obtener la siguiente tabla.


Cambia los nombres de los campos para hacer más intuitiva y amigable la información, así como el uso de los estilos de tablas. ¿Lo tienes? No te preocupes de que tengas datos y resultados diferentes, tiene que ser así, hemos construido una base de datos aleatoria que cambia continuamente los resultados.
¿Ya?, no pues sigue intentándolo, y cuando lo tengas , a por el 2º segmento.

Segundo segmento del modelo.

Ya tenemos la segunda TD creada en la hoja  “03”, en ella obtenemos la información de la frecuencia absoluta distribuida por intervalos de duración de las mismas. A continuación, podemos crear un segundo segmento en el modelo, que automatice el filtro dinámico, en base a la duración de la llamada.
 
Para ello, procederemos como lo hicimos durante la creación del primer segmento consulta el post del 21 de junio (aquí).



Todo parece correcto….   pero hay un problema. Los estuches de los filtros están desordenado, u ordenados según un criterio alfabético y no numérico, que es el que corresponde a esa magnitud. Absurdo, ¡sí! Y por mucho que enredes no encontraras una solución simple, no pierdas el tiempo, no hay solución sencilla, directa o intuitiva.
Pero no tires la toalla hay alguna posibilidad, pero no en este post, sino en el próximo.

Colección de post sobre “panel de control”

  1. Primer paso: construimos una base de datos aleatoria.
  2. Creación de la Primera tabla dinámica. Los 10 mejores clientes.
  3. De las TDD a la segmentación de tablas. Una novedad de Excel 2010.
  4. Construcción de la 2ª TDD. Duración de las llamadas telefónicas y crear un segundo segmento.
  5. Ordenar los elementos de los segmentos, para que aparezca como nosotros queremos.
  6. Creación de una tercera tabla y su segmento correspondiente. Las ventas por meses.
  7. Empezamos con los gráficos anidados a las TDD.
  8. El cuadro de mando en bruto, sin pulir.
  9. Cómo doy formato a los segmentos
  10. Cuadro maquillado y listo para la fiesta.
  11. Vincular tablas y segmentos, que este todo interrelacionado.


En la web de Flashforward, podrás descargarte el modelo en Excel que vamos a utilizar, El fichero tiene alguna macro, pero no son necesarias para el funcionamiento del cuadro de mando, aquí encontrarás la base de datos y el cuadro de mando terminado, así como una serie de hojas donde podrás ir haciendo el cuadro de mando paso a paso. Con cada post lo iremos ampliando y actualizando el modelo.

viernes, 21 de junio de 2013

Cómo hacer un cuadro de mando interactivo en Excel paso a paso? Creación de una segmentación en la TDD (4)




Cuando ya la versión Excel 2010 llega a su fin y nos proponen sustituirla por nuevas versiones, empiezo a sacarle el “gustillo” a los SEGMENTOS.

“El SEGMENTO es una de las principales innovaciones de la versión 2010. Un segmento, es una herramienta a una TDD, y que actúa como un filtro dinámico.”

Lo habitual con las TDD es utilizar los filtros de informes para filtrar los datos que quieras que se utilicen en el resumen del informe de dicha TDD. Para tablas pequeñas, sin muchos registros y campos, es útil, y como la costumbre se convierte en virtud, y esta en rutina, te impide descubrir este nuevo interface para manejar, automatizar y controlar los filtros de la TDD. La segmentación de datos proporciona unos botones o estuches, en los que se puede hacer clic para filtrar los datos de las tablas dinámicas. Además del filtrado rápido, la segmentación de datos también indica el estado actual de filtrado, lo cual facilita el entendimiento de lo que se muestra. Ganas en visibilidad, en claridad lo que ayuda mucho cuando tienes que compartir el modelo con más gente, que desconoce el proceso y la estructura que has utilizado para crear la TDD.


Este comando aparece por primera vez con la versión Excel 2010, y no está presente en la versión Excel:Mac 2011, cosa curiosa. 


Veamos con un ejemplo como se utiliza. Ponte en la hoja “01” del modelo “Cuadro de mando v1.4” (puedes descargártelo aquí gratis). Sitúate encima de la tabla, clicando en cualquiera de las celdas de la misma. 

En la cinta de menú aparecerá las opciones de “Herramientas de tabla dinámica”, en la Pestaña de “Opciones”, encontraras el comando de “Insertar segmentación de datos”.

 

Excel muestra una lista de los campos. Activa el campo "cliente", y aparecerá un nuevo panel formado por rectángulos estuchados, que contienen el nombre de todos los clientes de la BD
Al clicar un estuche de ellos, automáticamente se aplicará en la TD el filtro correspondiente a ese valor y sustituirá al anterior filtro. Puedes seleccionar mas clientes, mas “estuches”, utilizando las teclas de [Ctrl] y [Shift] al igual que en cualquier el software. Con la tecla [Shift] + clic, se selecciona todo entre el estuche anterior y el estuche seleccionado. Con [Ctrl] + clic activa un estuche si está apagado, y viceversa.











 


Prueba para lograr este resultado. En nuestro caso, seleccionamos doce clientes, y obtenemos la clasificación de estos doce clientes por orden decreciente de su cifra de negocio. Este es el resultado en este caso:











Aquí tienes las pistas para lograrlo:




En definitiva, un segmento se comporta como un filtro dinámico. De la misma  forma que el filtro normal de una TDD, te permite seleccionar a los clientes que deseas incluir en la tabla. Un SEGMENTO hace lo mismo pero con la ventaja de que vemos en directo como los clientes son seleccionados. Es lo mismo, pero no es igual.





Cómo hacer un cuadro de mando interactivo en Excel paso a paso?

  1. Introducción
  2.  Primer paso: construimos una base dedatos aleatoria.
  3. Creación de la Primera tabla dinámica. Los 10 mejores clientes.
  4. De las TDD a la segmentación de tablas. Una novedad de Excel 2010.
  5. Construcción de la 2ª TDD. Duración de las llamadas telefónicas y crear un segundo segmento
  6. Ordenar los elementos de los segmentos, para que aparezca como nosotros queremos.
  7. Creación de una tercera tabla y su segmento correspondiente. Las ventas por meses.
  8. Empezamos con los gráficos anidados a las TDD.
  9. El cuadro de mando en bruto, sin pulir.
  10. Cómo doy formato a los segmentos
  11. Cuadro maquillado y listo para la fiesta.
  12. Vincular tablas y segmentos, que este todo interrelacionado.



En la web de Flashforward, podrás descargarte el modelo en Excel que vamos a utilizar, El fichero tiene alguna macro, pero no son necesarias para el funcionamiento del cuadro de mando, aquí encontrarás la base de datos y el cuadro de mando terminado, asi como una serie de hojas donde podrás ir haciendo el cuadro de mando paso a paso. Con cada post lo iremos ampliando y actualizando el modelo. Los modelos utilizados en este post esta en el fichero de la version 1.4, hojas "01" y el resultado definitivo en "02".



Colabora en el blog con tu experiencia, con tus comentarios y observaciones, o participando en la encuesta de cuál es la mejor traducción al español de “tableau de bord”.