Uso de rebanadores con fórmulas matriciales dinámicas en Excel
Hace unos años, creé un vídeo de YouTube sobre el uso de segmentaciones con fórmulas en Excel . Fue un vídeo bastante popular. Ese método utilizaba una tabla dinámica ficticia para actuar como criterio para calcular las fórmulas. Hoy quiero ofrecerles una opción más limpia para usar segmentaciones con fórmulas. Este método utiliza sólo una tabla de Excel y funciones de matriz dinámica.
Las segmentaciones son una excelente herramienta para agregar interactividad. Cuando un usuario hace clic en un botón de segmentación, los resultados cambian para incluir solo los elementos seleccionados. Las segmentaciones son compatibles con tablas dinámicas, gráficos dinámicos, fórmulas cúbicas y tablas... pero no con fórmulas estándar. Entonces, veamos cómo podemos usar una tabla de una manera nueva para sortear esta limitación.
Este enfoque es engañosamente simple. Pero como la mayoría de las cosas en Excel, la habilidad es saber cómo conectar funciones entre sí.
Tabla de contenido
- Los datos de ejemplo
- Funciones de fila visibles
- Crear la segmentación
- Conectando el Slicer a la fórmula
- Usando un LAMBDA (avanzado)
- Conclusión
Los datos de ejemplo
Los datos utilizados en el ejemplo son los siguientes:
Esta es una Tabla llamada Lista, con una sola columna, también llamada Lista.
Funciones de fila visibles
En Excel, existen funciones especiales que solo calculan en filas visibles. Creo que solo hay dos SUBTOTAL y AGREGADO.
No entraremos en SUBTOTAL o AGREGADO en detalle en esta publicación. Pero si quieres saber más, aquí tienes algunas referencias útiles:
- SUBTOTAL: https://exceljet.net/excel-functions/excel-subtotal-function
- AGREGAR: https://exceloffthegrid.com/aggregate-the-best-excel-function-youre-not-using/
Para nuestro ejemplo, usaremos la función SUBTOTAL.
La siguiente captura de pantalla muestra una tabla con la versión CONTAR, solo celdas visibles, de la función SUBTOTAL en la parte inferior. El total cuenta el número de filas visibles. Si filtramos la Tabla, el total cambia. Observe que el total en la parte inferior cambia de 8 cuando no está filtrado a 3 cuando hemos seleccionado 3 elementos.
La fórmula en la celda B11 es:
=SUBTOTAL(103,[Lista])
El primer argumento de 103 le dice a SUBTOTAL que cuente las celdas ignorando las filas ocultas; [Lista] es el nombre de la tabla.
En lugar de usar SUBTOTAL para el total, agreguemos una columna llamada Incluir y usemos SUBTOTAL en cada línea. Este método devuelve 1 por cada fila visible o 0 por una fila oculta. Por supuesto, no podemos ver las filas ocultas, por lo que no podemos ver el resultado de la fórmula, pero las filas ocultas son 0 (lo prometo).
La fórmula en la celda C3 es:
=SUBTOTAL(103,[@List])
Como usamos una tabla de Excel, las fórmulas se copian automáticamente en cada fila.
Crear la segmentación
Creemos el Slicer para usar con nuestros datos.
- Seleccione una celda dentro de la tabla
- Haga clic en Insertar segmentación en la cinta
- Se abrirá el cuadro de diálogo Insertar segmentaciones
- Verifique la columna requerida
- Haga clic en Aceptar
Al hacer clic en la segmentación se filtrará la tabla.
Conectando el Slicer a la fórmula
El siguiente paso es hacer que la plomería funcione entre la Mesa y una fórmula.
Usaremos la función FILTRO para devolver solo las filas visibles de la Tabla, que son solo aquellas con 1 en la columna Incluir .
La fórmula en la celda C5 es:
=FILTRO(Lista[Lista],Lista[Incluir]=1)
Esta fórmula devuelve una matriz de todos los elementos de la columna Lista donde el valor correspondiente en la columna Incluir es igual a 1 (es decir, sólo las filas visibles).
Para obtener más información sobre la función FILTRO, consulte esta publicación: Función FILTRO en Excel
Ahora, comience a hacer clic en Slicer; a medida que cambia la selección, también lo hace la matriz devuelta.
Usando un LAMBDA (avanzado)
¿Estás pensando: “¿Realmente necesitamos usar esa columna adicional en la tabla? ¿No podemos simplemente crear una fórmula para manejar esto?”.
La respuesta es: Sí, podemos.
Notas:
- Debo agradecer a Sergei Baklan por ayudarme con esta fórmula. Creé algo que funcionó pero que era horrible de leer. Sergei pudo crear una función mucho mejor; por lo tanto, he utilizado la fórmula de Sergei a continuación.
- Al momento de escribir este artículo, esta solución utiliza funciones que solo están disponibles en el canal Beta. Por lo tanto, es posible que esto no funcione en su versión de Excel.
La fórmula en la celda E5 es:
=DROP(REDUCE("",Lista[Lista],LAMBDA(a,v,IF(SUBTOTAL(103,v),VSTACK(a,v),a))),1)
Esta publicación no trata sobre las funciones LAMBDA, por lo que no repasaremos la fórmula línea por línea. Efectivamente, la fórmula aplica el SUBTOTAL a cada fila de la tabla. Todas las filas que devuelven 1 (es decir, las que son visibles) se apilan en una única matriz y se devuelven a la hoja de trabajo.
Para utilizarlo en su escenario, simplemente reemplace Lista[Lista] con el nombre de la tabla y la columna que está utilizando. El resto de la función simplemente hará lo suyo y arrojará los resultados requeridos.
Conclusión
Excel no nos brinda una forma sencilla de utilizar segmentaciones con fórmulas. Con este enfoque, podemos extraer las selecciones con una función de matriz dinámica y luego usar esos valores para realizar otros cálculos.
Vea mi video anterior de YouTube para conocer otras técnicas de fórmulas que se pueden utilizar con este enfoque.
Deja una respuesta