Mostrando las entradas con la etiqueta segmentación de datos. Mostrar todas las entradas
Mostrando las entradas con la etiqueta segmentación de datos. Mostrar todas las entradas

miércoles, 20 de abril de 2022

Ranking (Top) con Slicers (Segmentación de datos) y Power Pivot

Hace un par de meses, en uno de los foros de Excel en los cuales participo, preguntaron si era posible tener un Top (Ranking) de vendedores que se visualice en algún gráfico y que no se necesite usar macros. Si bien yo no participé en dicho debate, la alternativa que le dieron al usuario fue, finalmente, usar macros. Me quedé con la curiosidad y hace algunos días logré algo muy cercano, pero necesariamente tuve que usar cuatro columnas auxiliares (en mi experiencia a la mayoría de usuarios no les gusta usarlas) y, además de la tabla de datos, tuve que usar una tabla adicional en la que coloqué los números Top.

Relacioné a ambas con una de las columnas auxiliares y logré que con un Slicer de los Top la tabla dinámica y el gráfico dinámico de datos cambien dependiendo de lo elegido en el Slicer.

Claro, el pequeño dilema es que para que todo se actualice, después de elegir el valor en el Slicer de los top, pues necesariamente hay que usar el botón de actualizar de la tabla dinámica. Por supuesto se puede hacer que se actualice con una macro, pero volveríamos al dilema inicial que les comenté: Hacerlo sin macros (por más que el actualizar con macros no involucre directamente lo del ranking). Pero bueno, no se preocupen, al final basándome en lo logrado, conseguí el objetivo a través de Power Pivot, pero eso sí, antes de explicarles esa solución, igual les comentaré lo previo, ya que fue la base para todo. Trataré de hacerlo de forma resumida:

Columna “Sumarsi”. Tiene la siguiente fórmula:

=SUMAR.SI(Hoja1!$B$2:$B$201;Hoja1!$B2;Hoja1!$C$2:$C$201)

Creo que para muchos/as es obvio que lo que intento con ella es que a cada vendedor tenga el total de sus ventas y por el momento no importa que se repita. Si bien eso se puede hacer con una tabla dinámica, en esta ocasión lo necesitaremos para lograr crear un ranking.


Columna RankingVendedor. Tiene la siguiente fórmula:

=SUMA(1/SI($E$2:$E$201>E2;CONTAR.SI($E$2:E201;$E$2:$E$201);9.999999999E+307))+1

Esta fórmula, apelando a el número más alto que puede calcular Excel, pues logra generar números de ranking sin repetidos a diferencia de JERARQUIA (tilde omitida como en la función). No, olvidar que se tiene que ingresar como fórmula matricial (excepto para Office 365).

Columna EligeRanking. Tiene la siguiente fórmula:

=SUBTOTALES(4;DatosRanking[NivelTop])

Esta columna es la que relaciona a la tabla de datos con la de los top. No olvidemos que los Slicer, al igual que los filtros, lo que hacen es ocultar filas, así que con SUBTOTALES podemos conseguir el valor de la tabla de los top, sea cual sea. Uso el 4 (equivalente a MAX), ya que, al ser valores distintos en dicha tabla, elijamos cual elijamos se mostrará siempre uno, es decir, siempre será el máximo (también podría usarse MIN).

Columna FiltrarVendedor. Tiene la siguiente fórmula:

=SI(F2 <= G2;1;0)

Es decir, si el RankingVendedor es menor o igual a EligeRanking, pues colocamos un 1 ¿Para qué? Para que al elegir el valor del Slicer del top/ranking, pues nos indique con un 1 (uno) cuales son los valores de los datos que son mayores o iguales y ese uno será lo que usaremos para mostrar o no los valores que necesitemos. ¿Por qué mayores? Porque para un ranking los mejores son los de menor número ¿O no recordaban eso? Igual cuando vean el archivo se entenderá mejor.

Luego, tipo dashboard, insertaremos una tabla dinámica (TD) y un gráfico dinámico. Eso sí, en la TD hagamos los siguiente. Usaremos la columna “C” (Monto) y sus valores sumados y la columna FiltrarVendedor (“H”), pero ojo con esta última, en la configuración del campo usaremos “Promedio”.

>

Ahora en la TD, en la columna de los vendedores, en el filtro eligen “Filtros de valor”, luego “Mayor que…”, en la primera lista desplegable elegimos “Promedio de FiltrarVendedor”, en la segunda “Es mayor que” y en el último casillero agregamos un cero (0).

Luego en la tabla de los Top agregamos un Slicer (asumo que saben cómo), lo cortan y lo pegan en la misma hoja de la TD y el gráfico dinámico. Para evitar, como comenté al inicio, estar eligiendo el valor top del slicer y actualizando la TD, agregamos una pequeña macro, pero todo esto lo podrán ver en el archivo que pueden descargar de aquí.

Ahora sí vamos a hacerlo con Power Pivot, que estoy seguro es lo que ustedes están esperando. Lo primero es que usaremos dos tablas similares a las del ejemplo anterior. Agreguen ambas al modelo de datos (igual asumo que saben cómo hacerlo).

Luego vamos a Power Pivot, a la tabla de datos que yo he llamado “TablaDatos” y vamos a agregar las siguientes funciones cuyos nombres serán similares a los del archivo anterior para que se entienda el porqué de su uso.

EligeRanking:=MAX('DatosRanking'[NivelTop])

SumaMonto:=SUM(TablaDatos[Monto]) 'Esto de aquí es equivalente a la de columna “Sumasi” del anterior ejemplo

En esas dos no hay mucho que explicar ya que estoy seguro todo/as saben lo que hacen MAX y SUM pues en DAX hacen lo mismo que en Excel. Vamos con las otras:

RankingVendedor:=RANKX(ALLSELECTED(TablaDatos[Vendedor]); [SumaMonto]; ; 0)

En esta función sí vamos a detenernos un poco. RANKX es una función DAX que nos devuelve la clasificación de una lista para cada fila de la tabla elegida, basado en la expresión que se elija (muy parecido a JERARQUIA). En este caso la tabla es TablaDatos con el detalle que usamos ALLSELECTED para nos devuelva todas las filas de la tabla omitiendo los filtros que se apliquen y como ven para este caso usaremos el campo Vendedor. Para el argumento de elección del ranking, usaremos la función que hemos creado previamente “SumaMonto”.

La siguiente es:

FiltrarVendedor:=IF([RankingVendedor] <= [EligeRanking];1;0)

Creo que en esa tampoco hay mucho que detallar.

Ahora que ya tenemos las funciones, crearemos, desde Power Pivot, una TD. Ojo, si en la hoja de Excel en donde han creado la TD les sale un aviso así, ni caso.

Luego activen los siguientes campos:

Luego en el campo de Vendedor, usaremos el mismo filtro del anterior ejemplo.

Quiten el campo FiltrarVendedor para que no salga en el gráfico dinámico que vamos a insertar. Sugiero también ordenar de forma descendente el campo RankingVendedor de la TD y recién ahí insertar el gráfico. Luego insertamos un Slicer del Top, obivamente con Segmentación de Datos. Deberíamos tener algo así y ya sin necesidad de macros al elegir cualquier ítem del Slicer, automáticamente cambian tanto la TD como el gráfico.

Si ven el detalle del gráfico también se ven los valores de RankingVendedor, pero hay algunos trucos para ocultarlos, pero eso ya queda como tarea para cada uno.

Espero les haya gustado y, sobre todo, espero que les sea útil. Hasta la próxima.

Abraham Valencia
Lima, Perú

Descargue el ejemplo aquí

domingo, 19 de mayo de 2019

"Dashboard" en Excel: Creando tu propio tablero de control

Cada día es más habitual encontrar libros de Excel en los cuales se ha agregado “Tableros de Control” más conocidos simplemente como “Dashboards”. Dichos tableros facilitan la presentación y/o análisis de datos que en muchas ocasiones necesitamos por lo que en esta ocasión veremos cómo hacer uno que incluso nos permita visualizar los datos a través de gráficos.

Para comenzar, la disposición de nuestros datos para el ejemplo será de este modo:


Para que el ejercicio resulte mucho más visible, he agregado varios registros con datos de tres años.  Para que no haya problema cuando vamos agregando más datos, yo siempre recomiendo convertir en “Tabla” dichos rangos. Además de usar el ya “clásico” ejemplo de ventas para estos casos, también necesitaremos, como siempre se recomienda, tener tres hojas en nuestro libro: Una para los datos, otras para las tablas dinámicas que vamos a necesitar y una para el “Dashboard”.

En este ejemplo lo que vamos a necesitar, para nuestro “Dashboard”, son los cruces de ventas por vendedor y de ventas por tienda, para eso vamos a necesitar dos tablas dinámicas. Obviamente cada quien debe decidir lo que desea mostrar. Para eso, entonces, crearemos dos tablas dinámicas que colocaremos en la hoja que yo he llamado “TD” (estoy asumiendo que todos/as saben cómo crearlas) de tal modo que tengamos esto como resultado:

Sugiero dar formato a los números para que después en los gráficos que usemos se refleje dicho formato. En mi caso usaré el formato de moneda, tal cual está en la tabla de datos.

Ahora lo que haremos será insertar un gráfico dinámico para cada una de las tablas dinámicas. Por si se han olvidado en dónde está la opción para crearlas, se sitúan en cualquier celda de la tabla dinámica respectiva y en la pestaña “Analizar” la veremos así:

Podemos elegir el tipo de gráfico que deseemos, en mi caso yo he elegido barras 3D agrupadas (horizontales).

No olviden que es un gráfico por cada tabla dinámica que usemos.

Luego lo que debemos hacer es cortar y pegar cada gráfico en la hoja llamada “Dashboard”. Por supuesto que podemos cambiar el diseño de cada gráfico con las opciones, justamente, de la pestaña “Diseño”. Además de eso, sugiero usar las opciones de cada gráfico dinámico para ocultar los botones de campos.

Una vez hecho eso, seleccionamos cualquiera de nuestros gráficos dinámico y vamos a la pestaña “Analizar” y en el grupo “Filtrar” usaremos el botón “Insertar escala de tiempo”.


En el menú que veremos elegimos, en mi caso, el campo “Día” y aceptamos.

Nuestra escala de tiempo incluirá todos los meses ya años que existan en nuestros datos.

Podemos cambiar el diseño a dicha escala y, además, una de las ventajas que tiene es que nos permitirá elegir días, meses, trimestres o años.

Lo que toca hacer ahora es relacionar nuestra escala de tiempo con las tablas dinámicas, para ello seleccionaremos nuestra escala y en la pestaña “Opciones”, en el grupo “Escala de tiempo” vamos a usar el botón “Conexiones de informes”.

Y activaremos la conexión a todas las tablas dinámicas que estamos usando.

Si bien ya tenemos como ir filtrando por tiempo, ahora lo que haremos es agregar algo que nos permita también hacerlo por vendedor o tienda, para eso seleccionamos cualquiera de los gráficos y en la pestaña “Analizar”, en el grupo “Filtrar”, esta vez vamos a elegir “Insertar segmentación de datos” y elegiremos “Vendedor” y “Tienda”.

Con los “slicer” que tenemos ahora, lo que debemos hacer es usar “Conexiones de informes” y relacionarlos a nuestras tablas dinámicas, tal y como hicimos con la escala de tiempo. Por supuesto también podemos cambiarle el diseño a dichos “slicer”.

Para ver como seguir agregando datos que nos pueden ser útiles, en una celda obtendremos el total de ventas y haremos que dependa de lo que vamos a ir eligiendo en la escala de tiempo y/o los “slicer”. Para ello vamos a usar el total de general de una de las tablas dinámicas y vincular dicho resultado a la celda deseada. En este caso he usado el total de la tabla dinámica con los resultados de las “Tiendas” .

Como las tablas dinámicas trabajan sobre los mismos datos, vamos a usar la de “Vendedores” y en el campo de totales vamos cambiar para que nos dé como resultado el promedio.

Vinculamos otra celda con dicho resultado del promedio, y con ello tendremos el promedio de venta de cada vendedor en base a lo que vayamos filtrando en la hoja “Dashboard”.

Para finalizar podemos colocar cada elemento de los obtenidos en la parte de la hoja que deseemos y tendremos algo como esto:

Y listo, ya podemos comenzar a “jugar” con los datos. Espero les sea útil. Hasta la próxima.

Abraham Valencia

Descarga el ejemplo aquí: Enlace

 

lunes, 29 de abril de 2019

Informes con segmentación de datos

Constantemente los usuarios/as de Excel están en busca de cómo mostrar sus datos en informes/reportes que, además de ser visualmente agradables a la vista, permita mostrar resúmenes de datos o similares. Hay varias formas de lograr hacerlo (Fórmulas, VBA, Tablas Dinámicas, etc.) pero en esta ocasión vamos a usar la “Segmentación de datos”.

Para comenzar, vamos a suponer que tenemos datos dispuestos de esta manera:

Entonces lo primero que haremos será convertir esos datos en “Tabla” (Enlace).

Si no situamos en cualquier celda de la tabla podremos usar las “Herramientas de tabla” y agregaremos la “Fila de totales” de tal modo que podamos usarlos a la hora de hacer nuestros informes.

Como también queremos datos por meses (en este caso todos los datos son de un solo año) y las tablas no permiten agrupar por meses, agregaremos una columna usando la siguiente fórmula:

Es suficiente que coloquemos la fórmula en la primera celda de la nueva columna y como se habrán dado cuenta se agregará en todas las otras celdas de manera automática (ventaja de las tablas).

Ahora lo que haremos será insertar unas diez filas encima de nuestra tabla, luego colocaremos el cursor en cualquier celda de la tabla y en las “Herramientas de tabla” elegiremos el botón “Insertar segmentación de datos”:

Para nuestro caso vamos a elegir tres opciones: Vendedor, producto y mes.

-------

Los tres cuadros que obtendremos podemos cambiarles el tamaño y colocarlos en la parte en que hemos insertado las filas en blanco, además podemos cambiarles el formato si seleccionamos cada uno y en las “Herramientas de segmentación de datos” cambiamos su estilo de tal modo que podríamos tener, finalmente, algo así:

Y listo, ya podemos elegir/filtrar las opciones que deseamos para nuestros informes:

Hasta la próxima.

Abraham Valencia

Descargar el ejemplo aquí