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.

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.

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”.