Utiliza slicers con la función SUMAR.SI.CONJUNTO

Utiliza slicers con la función SUMAR.SI.CONJUNTO

Utilizar slicers en cálculos sin tablas dinámicas y mejora la usabilidad de tus informes

Montar reports en Excel con funciones agregadas como SUMAR.SI.CONJUNTO o SUMAPRODUCTO es muy habitual, pero estos informes suelen quedarse "estáticos": calculan lo que les pides, sin más. En este ejercicio, sencillo pero muy útil, vamos a ver cómo conectar las segmentaciones de datos (los slicers) con un report basado en SUMAR.SI.CONJUNTO, de forma que al hacer clic en los filtros el resultado se actualice automáticamente, sin depender de una tabla dinámica.

El punto de partida

Trabajamos sobre una tabla de datos de ventas que incluye, entre otras columnas, la cuenta contable, el nombre del cliente, el sector, el centro, el saldo y el escenario (real o presupuesto, budget).

A partir de esa tabla ya tenemos preparado un mini report que, por epígrafe (productos o servicios) y por escenario (real o presupuesto), muestra la suma correspondiente. Ese cálculo está montado con SUMAR.SI.CONJUNTO:

  • El rango de suma es la columna de saldo.
  • Un criterio filtra por el escenario (real o presupuesto).
  • Otro criterio filtra por el epígrafe (productos o servicios).

El planteamiento es simple, y la idea ahora es enriquecerlo añadiendo slicers que recalculen los datos en función de las selecciones.

Insertar las segmentaciones de datos

Lo primero es asegurarse de que el origen es una tabla de Excel. Si no lo es, basta con seleccionar los datos y pulsar Ctrl + T.

Con la tabla lista, vamos a insertar la segmentación de datos y elegimos los campos por los que queremos filtrar. En el ejemplo se eligen tres: el centro, el sector y el nombre del cliente. Al aceptar, Excel genera los tres slicers.

A medida que hacemos selecciones en estos filtros, la tabla original se va ajustando: muestra solo las filas que cumplen las combinaciones elegidas e incluso indica qué combinaciones son válidas y cuáles no.

El problema: los slicers no afectan al cálculo

Aquí aparece la limitación. Si llevamos los slicers junto a nuestro report y empezamos a filtrar, comprobamos que el resultado de SUMAR.SI.CONJUNTO no cambia, aunque el filtro sí se esté aplicando sobre la tabla original.

El motivo es que estas funciones agregadas no "saben" qué filas están visibles tras aplicar la segmentación. Necesitamos un puente entre lo que muestra el slicer y lo que calcula la fórmula.

La columna de enganche con SUBTOTALES

La solución es crear una columna de enganche dentro de la tabla usando la función SUBTOTALES. Esta función nos permite, primero, indicar qué tipo de operación queremos (contar, sumar, máximo, mínimo, etc.) mediante un número de función y, después, señalar el rango sobre el que operar.

La clave está en una virtud de SUBTOTALES: cuando se usan los números de función del 1 al 11, solo tiene en cuenta las filas visibles. Las filas ocultas por la segmentación quedan fuera.

Para aprovecharlo, añadimos una nueva columna (por ejemplo, llamada "filtro") y le pedimos que cuente los elementos de la propia fila, eligiendo un campo cualquiera, como el del saldo. El comportamiento es el siguiente:

  • Cuando la fila está visible, la fórmula devuelve un 1.
  • Cuando la fila está oculta por los slicers, devuelve un 0.

Ese 1 / 0 será el vínculo fundamental entre la segmentación y nuestra función agregada.

Conectar la columna con SUMAR.SI.CONJUNTO

Una vez tenemos la columna de enganche, volvemos al cuadro de SUMAR.SI.CONJUNTO (o SUMAPRODUCTO, según el caso) y le añadimos una condición más:

  • Como rango de criterio indicamos la nueva columna filtro de la tabla.
  • Como criterio, obligamos a que ese valor sea igual a 1.

Extendemos la fórmula al resto de celdas del report. En ese momento los valores no cambian todavía, porque no hay nada filtrado, pero es justo ahí donde todo cobra sentido.

El resultado: un report navegable

A partir de ahora, cada vez que filtramos con cualquiera de los slicers, el report se recalcula solo:

  • Si seleccionamos un epígrafe como "consumibles", el cuadro se ajusta.
  • Si elegimos un sector (retail, software…), solo se muestra lo de ese sector.
  • Si combinamos cliente, tipo de centro y sector, el resultado se acomoda a esa combinación concreta.

Y todo ello usando funciones que no están vinculadas directamente a una tabla dinámica.

Conclusión

Como se ve, es una técnica muy sencilla: la función SUBTOTALES nos permite crear una columna de enganche entre las selecciones de los slicers y un filtro que SUMAR.SI.CONJUNTO puede aprovechar. Esto da un potencial enorme para generar informes más complejos y navegables a nivel de usuario, permitiendo buscar el dato que queremos sin depender de la generación de tablas dinámicas.

Más casos con estas funciones

Más contenido de Excel en InflueXcel