Reporte por cantidades mayores a cero

Tenemos una serie de provincias en vertical, en horizontal, tenemos los días de la semana, y, tenemos la cantidad que corresponde con cada provincia y día de la semana.

Debemos de crear un informe, o, reporte con las provincias y días de la semana cuya cantidad no incluya los valores ceros ni las cantidades negativas.

En una celda, usamos LET, creamos una variable, seleccionamos el rango B2:B21 (provincias), concatenamos con la barra inclinada, concatenamos con el rango C1:I1 (días de la semana), concatenamos con la barra inclinada y concatenamos con el rango C2:I21 (Cantidades).

=LET(a;B2:B21&"/"&C1:I1&"/"&C2:I21;a)

Tenemos una matriz desbordada de siete columnas, una por día, donde tenemos unido separado por la barra invertida, la provincia, el día de la semana y la cantidad correspondiente.

Usamos ENCOL, como argumento matriz, es la concatenación.

=LET(a;ENCOL(B2:B21&"/"&C1:I1&"/"&C2:I21);a)

Creamos otra variable, usamos TEXTODESPUES, como argumento texto, ponemos la variable “a”, como argumento delimitador, entre comillas dobles, ponemos la barra inclinada, como argumento numero de instancia, ponemos 2, es decir, que nos de el texto que hay después del segundo delimitador, y, multiplicamos por 1, para que las cantidades estén en formato de número.

=LET(a;ENCOL(B2:B21&"/"&C1:I1&"/"&C2:I21);b;TEXTODESPUES(a;"/";2)*1;b)

Tenemos los valores numéricos.

Creamos otra variable, usamos FILTRAR, como argumento array, ponemos la variable “a”, como argumento Include, ponemos la variable “b” y comparamos con mayor a 0.

=LET(a;ENCOL(B2:B21&"/"&C1:I1&"/"&C2:I21);b;TEXTODESPUES(a;"/";2);c;FILTRAR(a;b>0);c)

Ya tenemos la provincia, día de la semana, y, cantidad que cumple con la condición.

Usamos el argumento calculo de LET, concatenamos la variable “c” con un punto.

=LET(a;ENCOL(B2:B21&"/"&C1:I1&"/"&C2:I21);b;TEXTODESPUES(a;"/";2);c;FILTRAR(a;b>0);CONCAT(c&"."))

Tenemos todos los datos en una sola fila, donde cada cadena esta separada por un punto.

Usamos DIVIDIRTEXTO, como argumento texto, es la funcion CONCAT, como argumento delimitador de columna, entre comillas dobles, ponemos la barra inclinada, como argumento delimitador de fila, entre comillas dobles, ponemos punto.

=LET(a;ENCOL(B2:B21&"/"&C1:I1&"/"&C2:I21);b;TEXTODESPUES(a;"/";2)*1;c;FILTRAR(a;b>0);DIVIDIRTEXTO(CONCAT(c&".");"/";"."))

Volvemos a la expresión, usamos APILARV, como argumento matriz1, usamos una constante de matriz, abrimos unas llaves, entre comillas dobles, ponemos Provincias, ponemos barra inclinada, entre comillas dobles, ponemos Días, ponemos barra inclinada, entre comillas dobles, ponemos Total, cerramos llaves, como argumento matriz2, es la funcion DIVIDIRTEXTO.

=LET(a;ENCOL(B2:B21&"/"&C1:I1&"/"&C2:I21);b;TEXTODESPUES(a;"/";2)*1;c;FILTRAR(a;b>0);APILARV({"Provincias"\"Dias"\"Total"};DIVIDIRTEXTO(CONCAT(c&".");"/";".")))

Aceptamos, y, ya lo tenemos.

Por último, vamos a aplicar un formato condicional para resaltar la ultima provincia de cada grupo, vamos a probar la funcion a usar en una celda aparte, seleccionamos el rango K2:K122, igualamos a la funcion DESREF, como argumento referencia, seleccionamos el rango K2:K122, como argumento filas, ponemos 1, ignoramos el argumento columnas, por lo que ponemos punto y coma.

Es decir, vamos a comparar cada celda del rango K2:K122 con la siguiente celda.

=K2:K122=DESREF(K2:K122;1;)

Obtenemos una matriz desbordada con VERDADERO donde una celda es igual a la celda siguiente, y, FALSO donde no lo es.

Como esta funcion nos devuelve un valor booleano, podemos usarla en formato condicional, para ello, seleccionamos la expresión, pulsamos CTRL más C para copiar, desplegamos formato condicional desde la pestaña de inicio, y, seleccionamos nueva regla.

En la ventana dar formato a los valores, pegamos la expresión, y, damos un formato.

Pero, vemos que nos devuelve la última fila con error, pues, después del signo igual, ponemos EXCLUIR, como argumento matriz es la funcion LET, como argumento filas, ponemos -1, ignoramos le argumento columnas.

=EXCLUIR(LET(a;ENCOL(B2:B21&"/"&C1:I1&"/"&C2:I21);b;TEXTODESPUES(a;"/";2)*1;c;FILTRAR(a;b>0);APILARV({"Provincias"\"Dias"\"Total"};DIVIDIRTEXTO(CONCAT(c&".");"/";".")));-1)

Aceptamos, y, ya lo tenemos.

Miguel Angel Franco