martes, 12 de julio de 2016

Conteo condicionado de elementos filtrados.

Consultaba un lector la manera de realizar un conteo de registros según una condición dada sobre una base de datos filtrada.
[...] La consulta es: ¿Cómo hago para que cuando filtre por un Departamento concreto me indique la cantidad exacta en cada uno de los estados: Alto, Medio o Bajo? [...]

Lo complejo es realizar el conteo únicamente sobre los elementos visibles, i.e., de aquellos que responden a unos criterios definidos.
Veamos la base de datos a analizar:

Conteo condicionado de elementos filtrados.



La idea es clara, el usuario filtrará por el campo 'Departamento', y sobre los elementos filtrados desea conocer el número de registros que corresponden a cada Estado (celdas A3, B3 y C3).

Emplearemos una técnica similar a la expuesta en este post, en el cual se hace uso de la función SUBTOTALES combinado con DESREF.

En concreto en la celda A3 insertamos matricialmente:
=SUMA(SI(SI(SUBTOTALES(103;DESREF($B$5;FILA($B$5:$B$19)-FILA($B$5);;1));SI($B$5:$B$19=A$2;$B$5:$B$19))=A$2;1;0))
con el que conseguimos el número de estados tipo 'Alto' de la base de datos.


Veamos la explicación sobre un ejemplo, en este caso filtramos el campo Departamento por su elemento 'Depto C':

Conteo condicionado de elementos filtrados.


Apreciamos el comportamiento de nuestra función en el rango de celdas F21:H35 en la imagen anterior.

La primera parte de nuestra función
SUBTOTALES(103;DESREF($B$5;FILA($B$5:$B$19)-FILA($B$5);;1))
nos permite averiguar qué filas están filtradas y cuales no.. un cero para las NO visibles y un uno para las visibles...
Fundamental para poder trabajar sobre los registros que nos interesan (ver rango G21:G35 en la imagen).

Si a la fórmula anterior añadimos
SI($B$5:$B$19=B$2;$B$5:$B$19)
como parte de un condicional:
=SI(SUBTOTALES(103;DESREF($B$5;FILA($B$5:$B$19)-FILA($B$5);;1));SI($B$5:$B$19=A$2;$B$5:$B$19))

obtenemos un rango de valores con datos relevantes solo para los registros visibles!; en concreto, un rango con los Estados de esos registros (ver rango H21:H35 en la imagen).


Nuestra fórmula matricial final:
=SUMA(SI(SI(SUBTOTALES(103;DESREF($B$5;FILA($B$5:$B$19)-FILA($B$5);;1));SI($B$5:$B$19=A$2;$B$5:$B$19))=A$2;1;0))
compendia todo lo anterior, añadiendo una SUMA de unos para las coincidencias de Estado 'Alto'.. o lo que es lo mismo, contando el estado 'Alto' de los registros visibles...

No hay comentarios:

Publicar un comentario en la entrada