Cómo crear imágenes de filtro cruzado en Excel (increíbles imágenes interactivas)

Power BI tiene una forma realmente agradable de realizar filtros cruzados de imágenes. Cuando hace clic en un objeto visual, resalta el elemento seleccionado en otros gráficos y al mismo tiempo conserva el valor total en un color más claro. ¿Podemos realizar filtros cruzados de elementos visuales en Excel? No. Pero podemos crear algo bastante parecido.

Ejemplo de PowerBI:

Esta publicación se inspiró en una pregunta que recibí:

Con una segmentación de datos vinculada a un gráfico dinámico, ¿hay alguna forma de resaltar la columna pero manteniendo todos los datos?

Entonces, veamos cómo podemos lograr algo similar en Excel.

Tabla de contenido

• Los datos
• Los cálculos
• Crear los gráficos
• Crear las rebanadoras
• La presentación de formato
• Formatear los gráficos
• Formatear la segmentación
• Disposición
• Conclusión: imágenes con filtros cruzados

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre de archivo: 0159 Visuales de filtrado cruzado en Excel.xlsx

Tener acceso

Los datos

Los datos de este ejemplo provienen de una única tabla llamada Datos .

Todos los datos son valores fijos, excepto la columna Incluir que contiene la siguiente fórmula:

=SUBTOTAL(3,[@Value])

Como la fórmula está en una tabla de Excel, se copia automáticamente en las filas siguientes.

Esta fórmula es fundamental para todo el proceso. Actualmente calcula 1 en cada fila porque cada fila es visible. Pero si filtramos la Tabla, las filas ocultas se recalculan a 0 . Este es el método que utilizamos para identificar si un valor debe mostrarse en el gráfico.

Obtenga más información sobre esta técnica aquí: Uso de segmentaciones con fórmulas de matriz dinámica en Excel

Los cálculos

Se requieren varios cálculos para lograr el efecto de filtro cruzado.

Estamos utilizando fórmulas de matriz dinámica para esta solución. Necesitará Excel 2021 o posterior para trabajar con el archivo de ejemplo. Sin embargo, no es esencial y esta solución puede funcionar sin fórmulas de matriz dinámica.

Los cálculos tienen una columna de categoría y dos columnas de serie de datos (Seleccionados y No seleccionados). Esta no es una publicación sobre las fórmulas, por lo que no las analizaré en detalle.

Las fórmulas para el gráfico de artículos son las siguientes

Celda B5:

=SORT(UNIQUE(Data[Item]))

Celda C5:

=SUMIFS(Data[Value],Data[Include],1,Data[Item],B5#)

Celda D5:

=SUMIFS(Data[Value],Data[Item],B5#)-D5#

Luego, estos cálculos se repiten para el gráfico de región equivalente:

Celda C15:

=SORT(UNIQUE(Data[Region]))

Celda D15:

=SUMIFS(Data[Value],Data[Include],1,Data[Region],C15#)

Celda E15:

=SUMIFS(Data[Value],Data[Region],C15#)-D15#

Como paso posterior, podríamos usar HSTACK para poner todos los cálculos en una sola matriz. Los gráficos vinculados a una única matriz se expanden automáticamente; por lo tanto, eso podría reducir la cantidad de esfuerzo manual requerido para mantener los gráficos para datos nuevos.

Crear los gráficos

Usando las columnas Artículo , Seleccionado y No seleccionado , cree un gráfico de barras apiladas para los cálculos de Artículos.

Repita lo anterior para los cálculos de Región .

Luego, corte y pegue los gráficos en una nueva hoja de trabajo de Presentación.

Volveremos y daremos formato a estos gráficos en breve.

Crear las rebanadoras

Ahora, creemos las segmentaciones.

• Seleccione una celda en la tabla de datos .
• Haga clic en Insertar segmentación en la cinta
• En el cuadro de diálogo Insertar segmentaciones , seleccione Elemento y región y luego haga clic en Aceptar .
  

Esto crea 2 nuevas segmentaciones. Corte y pegue las segmentaciones en la misma hoja de trabajo que los gráficos.

La presentación de formato

Todos los cálculos y conexiones están configurados. Al hacer clic en las segmentaciones de datos se filtran ambos gráficos. Ahora todo es cuestión de formato y presentación.

Formatear los gráficos

Repita los siguientes pasos para ambos gráficos.

• Eliminar el título
• Eliminar la leyenda
• Eliminar el borde del gráfico
• Seleccione colores adecuados para las barras.
• Dar formato al eje de categorías
  
  • Verifique las categorías en orden inverso
  • Establezca el eje horizontal para cruzar en la categoría máxima
• Formatee la serie de datos estableciendo el ancho del espacio en al menos 50%
  
• Agregue etiquetas de datos a la serie seleccionada .
• Formatee la etiqueta de datos como desee, pero asegúrese de que el formato del número aparezca en blanco para el cero.
  

Con Este y Oeste seleccionados en la segmentación de regiones y Alfa, Bravo y Charlie seleccionados en la segmentación de regiones, los gráficos se verán un poco así.

Formatear la segmentación

Ahora es el momento de formatear las segmentaciones.

• Haga clic derecho en una segmentación y seleccione Configuración de segmentación en el menú:
  
  • Desmarque Mostrar encabezado
  • Seleccione Ascendente (A a Z)
  • Desmarque Usar listas personalizadas al ordenar
  • Desmarcar Indicar visualmente elementos sin datos
  • Asegúrese de que Ocultar elementos sin datos no esté marcado
  • Haga clic en Aceptar
• Seleccione una segmentación de datos, luego, en la cinta Segmentación , haga clic con el botón derecho en un estilo de segmentación y haga clic en Duplicar ...
  
• En el cuadro de diálogo Modificar estilo de segmentación , modifique el estilo de segmentación para que se ajuste a sus necesidades.
  
• Después de crear el estilo de segmentación, aplique el estilo a las segmentaciones de Elemento y Región.

Para nuestro ejemplo, las segmentaciones se ven así:

Elegí un color de relleno bastante oscuro para la segmentación, pero puedes elegir una fuente en negrita u otro formato para indicar qué elementos se han seleccionado.

Disposición

El último paso es diseñar la segmentación y el gráfico de modo que la segmentación se parezca a las etiquetas de los ejes de los gráficos.

Si es necesario, cambie el tamaño de los botones de la segmentación seleccionándola y luego cambie la altura del botón en la cinta Segmentación .

Ahora, cambie el tamaño del gráfico para que las barras se alineen visualmente con los botones de segmentación.

Al hacer clic en los botones de segmentación se filtran los demás gráficos.

¡AUGE! Imágenes de filtrado cruzado en Excel: ¡listo!

Conclusión: imágenes con filtros cruzados

Si bien Excel no tiene filtrado cruzado, podemos crear un efecto similar utilizando segmentaciones y gráficos estándar. La columna Incluir es la clave para que esta técnica funcione; esto identifica qué elementos se han seleccionado.

Algunas otras soluciones de filtrado cruzado utilizan tablas dinámicas y Power Pivot, así que asegúrese de revisarlas también:

• ¡Asombroso! Gráficos de filtrado cruzado en paneles de Excel como en Power BI
• Filtro cruzado y resaltado de gráficos de Excel como Power BI

Artículos Relacionados :

• Uso de rebanadores con fórmulas matriciales dinámicas en Excel
• Cómo crear un panel interactivo de solo vista desde Excel
• 5 reglas para una paleta de colores de tablero

---