42. Crear un segmentador dinámico

segmentador_dinamico.rar

Descargar RAR • 110KB

Ya sabemos crear tablas y gráficos dinámicos.

Vamos a crear una segmentación, donde contenga la cantidad, el promedio, o, el valor máximo, para ello, vamos a crear KPI´s, y, haremos nuestros informes más flexibles.

Tenemos el siguiente modelo.

Esta en formato de tabla, si vamos a la pestaña diseño de tabla, dentro del grupo propiedades, podemos ver el nombre de la tabla, que es ventas.

Pues, dentro de la pestaña diseño de tabla, dentro del grupo herramientas, hacemos clic en resumir con tabla dinámica.

Marcamos, aunque ya viene por defecto, nueva hoja de cálculo.

Marcamos agregar estos datos al modelo de datos, porque vamos a crear medidas, al marcar la casilla, agregar estos datos al modelo de datos, podremos trabajar con el modelo en Power Pivot.

Aceptamos.

Vemos el esqueleto de la tabla dinámica, y, la zona de campos.

Vamos a la pestaña de power Pivot, desplegamos medida, y, elegimos nueva medida, porque vamos a crear una primera medida para calcular el total de ventas.

Se abre la ventana de medidas.

En la ventana, nombre de la tabla, seleccionamos nuestra tabla.

Vamos a llamar a la medida ventas, en la ventana, nombre de la medida.

En la ventana de fórmulas, ponemos la palabra SUM, abrimos un paréntesis, y, aparecen los campos con los que podemos trabajar.

Seleccionamos la columna total, es decir, el total de la columna total.

Cerramos paréntesis.

Hacemos clic en comprobar formula, para ver que es correcta la expresión, que en este caso, lo es.

Aceptamos.

Vemos que nos aparece la media en la tabla dinámica.

Sabemos, que es una medida, porque aparece con fx.

Vamos a bajar a filas, por ejemplo, centro comercial, y, a valores la medida.

Vemos las ventas por cada centro comercial.

Con una celda dentro de la columna ventas, hacemos clic con el botón secundario del ratón, y, elegimos formato de número.

En la ventana categoría, seleccionamos número, quitamos decimales, y, marcamos usar separador de miles.

Aceptamos.

Creamos otra medida, donde vamos a calcular el promedio de la columna total.

Se abre la ventana de medida, en la ventana, nombre de la tabla, seleccionamos la tabla ventas, y, en la ventana, nombre de la medida, ponemos, promedio.

Como las funciones, son en inglés, debemos de usar la función AVERAGE, pues, en la ventana formula, escribimos AVERAGE, de la columna total.

Hacemos clic en comprobar formula, y, vemos que todo es correcto.

Desde esta misma ventana, le vamos a dar formato de número, para ello, en la parte inferior, en la ventana categoría, seleccionamos number, en la ventana, formato, seleccionaos numero decimal, ponemos cero decimales, y, marcamos, usar separador de miles.

Aceptamos.

Vamos a crear una última medida, para sacar el valor máximo de la columna de total, y, damos formato de número.

Como nombre de la tabla, seleccionamos la tabla ventas, en nombre de la medida, le ponemos, máximo, y, en la ventana de formula, usamos la función MAX, de la columna de total.

Podemos comprobar la formula.

Aceptamos.

Ya tenemos una tabla dinámica, con el total, promedio, y, valor máximo para cada centro comercial.

Voy a copiar los encabezados en vertical, para ello, puedo usar la función TRANSPONER.

=TRANSPONER(B3:D3)

Le damos formato de tabla, para ello, con una celda dentro del modelo, pulsamos CTRL mas T, se abre la ventana de crear tabla, donde debemos de marcar la casilla, la tabla tiene encabezados.

La vamos a llamar encabezados.

La vamos a agregar al modelo de power Pivot.

Para ello, con una celda dentro del modelo, vamos a la pestaña power pívot, y, hacemos clic en agregar al modelo de datos.

Podemos ver las dos tablas dentro de power Pivot.

Bien, pues, vamos a crear segmentadores a partir de esta tabla de encabezados.

Si vamos a la zona de campos de la tabla dinámica, y, hacemos clic en ALL (Todas), veremos las dos tablas.

Donde solo tenemos una columna llamada encabezados.

Hacemos clic con el botón alternativo del ratón sobre el encabezado, y, seleccionamos agregar como segmentación de datos.

Si hacemos clic en cualquier elemento de la segmentación, vemos que no pasa nada.

Necesitamos crear una medida que tome el valor seleccionado en la segmentación.

Pues, vamos a crear una nueva medida en la tabla encabezados, que la llamaremos segmentador.

Donde vamos a usar la función VALUES, que nos devuelve los valores únicos de una columna.

Aceptamos.

La agregamos a filas de la tabla dinámica.

Si seleccionamos un elemento del segmentador vemos que cambia en la tabla dinámica.

Pero, si dejo pulsada la tecla CTRL y selecciono más de un elemento, me devuelve error, donde me dice que se seleccionaron varios valores, donde se esperaba solo un valor.

Por lo que debemos de asegurarnos que el usuario, solo seleccione un valor.

Vamos a editar la medida, para ello, dentro de la pestaña de Power Pivot, desplegamos medida, y, seleccionamos administrar medidas.

Se abre la ventana, administrar medidas, donde seleccionamos la medida de segmentador, y, hacemos clic en editar.

Vamos a agregar un IF, para agregar una condición, que va a ser que si selecciona un solo elemento, cosa que haremos con la función HASONVALUE, que nos devuelve verdadero, si se ha filtrado un solo valor, de lo contrario, que no haga nada.

Aceptamos, y, vemos que cuando seleccionamos más de elemento, no aparece nada.

Lo siguiente es poner los valores, es decir, el total, promedio o valor máximo, según el encabezado que seleccionemos en el segmentador.

Para ello, vamos a crear una nueva medida en la tabla ventas, la llamaremos selección, y, vamos a usar la función SWITCH, que es parecido al IF, pero poner más de una condición, resulta más fácil.

Vamos a preguntar, que si segmentador, es igual a ventas, que ponga la medida ventas, si es igual a promedio, que ponga la medida promedio, en caso contrario, que ponga la medida máximo.

Le damos formato.

Si probamos veremos que al seleccionar un elemento del segmentador, ya nos aparece en la tabla dinámica.

Ahora, solo necesito esta última medida, por lo que vamos a quitar el resto de los campos.

Lo siguiente va a ser crear un gráfico a partir de la tabla dinámica, por lo que con una celda dentro de la tabla dinámica, vamos a la pestaña de analizar tabla dinámica, dentro del grupo, herramientas, insertamos un gráfico.

Por ejemplo, de columnas agrupadas.

Ponemos etiquetas de datos como entrada de datos.

Con el grafico seleccionado, dentro de la pestaña de análisis de grafico dinámico, dentro del grupo mostrar u ocultar, desplegamos botones de campos, y, elegimos ocultar todos.

Quitamos el título.

Si elegimos un encabezado, vemos como se actualiza el grafico.

Pero, vemos que al elegir un encabezado la tabla se mueve, pues para arreglar esto, hacemos clic con el botón alternativo del ratón sobre la tabla dinámica, y, elegimos opciones de tabla dinámica.

Desmarcamos autoajustar anchos de columnas al actualizar, por lo que va a mantener siempre el mismo ancho.

Lo siguiente que vamos a hacer es introducir la segmentación dentro del gráfico.

Esta segmentación la vamos a poner en tres columnas, para ello, seleccionamos la segmentación, vamos a la pestaña segmentación, dentro del grupo botones, ponemos tres columnas.

Quitamos las líneas de cuadricula de la hoja de cálculo.

Quitamos los bordes del gráfico.

Quitamos las líneas de división.

Quitamos el eje vertical.

Bajamos el área de trazado.

Movemos la segmentación al gráfico, pero primero debemos de traer la segmentación hacia adelante.

Hacemos clic en la segmentación con el botón alternativo del ratón, seleccionamos configuración de segmentación de datos.

En la ventana que se abre, desmarcamos mostrar encabezado.

Lo siguiente es quitar los bordes de la segmentación, por lo que vamos a tener que crear un estilo propio.

Teniendo la segmentación, seleccionada, vamos a la pestaña segmentación.

Desplegamos estilos de segmentación de datos.

Elegimos nuevo estilo.

Seleccionamos formato.

Se abre la ventana, formato de elemento de segmentación de datos, donde vemos tres pestañas, pues, hacemos clic en borde.

Quitamos las líneas de los bordes.

Aceptamos.

Cuando creamos un estilo, no se aplica directamente, debemos de desplegar, de nuevo, estilos de segmentación de datos, y, dentro del grupo personalizada, lo seleccionamos.

Con esto, damos por finalizado nuestro ejemplo.