Reporte por fecha minimo_maximo

Para el siguiente ejemplo vamos a trabajar con el modelo que suelo usar habitualmente.

El modelo esta en formato de tabla, y, se llama Ventas.

Tenemos movimientos desde enero a junio del año 2023.

Queremos crear un reporte con las ventas mínimas y máximas de cada mes.

En cada mes, el mismo valor mínimo, o, máximo puede aparecer varias veces.

Empecemos….

Si usamos la función MIN.SI.CONJUNTO, donde como argumento rango_min, es la columna total, como argumento rango_criterios1, es el mes de la columna fecha, y, como argumento criterios1, ponemos 1, haciendo referencia al mes de enero, vemos que nos devuelve error.

=MIN.SI.CONJUNTO(Ventas[Total];MES(Ventas[Fecha]);1)

En la celda J1, creamos una lista desplegable para seleccionar un mes.

En el rango AA2:AA7, tenemos los meses.

Seleccionamos la celda J1, vamos a la pestaña datos, dentro de herramientas de datos, hacemos clic en validación de datos.

Desplegamos permitir, seleccionamos lista, en la ventana origen, seleccionamos los meses en el rango AA2:AA7.

Aceptamos.

Seleccionamos un mes.

Vamos a usar LET como hemos hecho en otros ejemplos, e, iremos dividiendo los pasos.

En la celda I2, ponemos LET, creamos una variable, el valor a asignar a esta variable, va a ser filtrar la tabla ventas, siempre que el mes de la columna fecha sea igual al valor de la celda J1, que es el mes que hemos seleccionado, para ello, usamos la función FILTRAR, como argumento array es la tabla ventas, como argumento include, usamos la función MES, para extraer el mes de la columna fecha, y, lo igualamos al valor de la celda J1, cerramos paréntesis, y, probamos la variable.

=LET(a;FILTRAR(Ventas;MES(Ventas[Fecha])=J1);a)

Obtenemos una matriz desbordada con la tabla ventas con el mes que hemos seleccionado en la celda J1, he seleccionado el mes 1.

Creamos otra variable, donde almacenamos el valor mínimo de la columna 7 de la variable a, que es la columna total.

Probamos dicha variable.

=LET(a;FILTRAR(Ventas;MES(Ventas[Fecha])=J1);b;MIN(INDICE(a;;7));b)

El valor mínimo para el mes de enero es 202.

Creamos otra variable para extraer el valor máximo para el mes seleccionado, es la misma expresión que para la variable “b”, pero cambiando MIN por MAX.

Probamos dicha variable.

=LET(a;FILTRAR(Ventas;MES(Ventas[Fecha])=J1);b;MIN(INDICE(a;;7));c;MAX(INDICE(a;;7));c)

El valor máximo para el mes de enero es 9751.

Antes de seguir voy a hacer un inciso, si en una celda ponemos la función INDICE, como argumento matriz, ponemos la tabla ventas, omitimos el argumento numero de fila, y, numero de columna.

=INDICE(Ventas;;)

Obtenemos una matriz desbordada con la tabla ventas.

Seguimos.

Creamos otra variable, usamos la función FILTRAR, como argumento array, con INDICE nos traemos la variable “a”, como he mencionado antes, omitimos el argumento numero de fila y numero de columna.

=LET(a;FILTRAR(Ventas;MES(Ventas[Fecha])=J1);b;MIN(INDICE(a;;7));c;MAX(INDICE(a;;7));d;FILTRAR(INDICE(a;;)

Como argumento include, traemos la columna 7 de la variable “a”, y, la igualamos a la variable “b”.

Cerramos paréntesis, y, probamos dicha variable.

=LET(a;FILTRAR(Ventas;MES(Ventas[Fecha])=J1);b;MIN(INDICE(a;;7));c;MAX(INDICE(a;;7));d;FILTRAR(INDICE(a;;);INDICE(a;;7)=b);d)

Obtenemos los registros que corresponden al mes de enero con el valor mínimo.

Creamos otra variable, la expresión es la misma que la anterior, pero en el argumento include, igualamos a la variable c.

=LET(a;FILTRAR(Ventas;MES(Ventas[Fecha])=J1);b;MIN(INDICE(a;;7));c;MAX(INDICE(a;;7));d;FILTRAR(INDICE(a;;);INDICE(a;;7)=b);e;FILTRAR(INDICE(a;;);INDICE(a;;7)=c)

Creamos otra variable, donde usamos APILARV, como argumento matriz1, entre comillas dobles, ponemos Valor mínimo:, y, concatenamos con la variable “b”, que es el valor mínimo, como argumento matriz2, ponemos la variable “d”, como argumento matriz3, ponemos un texto en blanco, como argumento matriz4, entre comillas dobles, ponemos Valor máximo, y, concatenamos con la variable “c”, como argumento matriz5, ponemos la variable e, cerramos paréntesis, y, probamos dicha variable.

=LET(a;FILTRAR(Ventas;MES(Ventas[Fecha])=J1);b;MIN(INDICE(a;;7));c;MAX(INDICE(a;;7));d;FILTRAR(INDICE(a;;);INDICE(a;;7)=b);e;FILTRAR(INDICE(a;;);INDICE(a;;7)=c);f;APILARV("Valor minimo: "&b;d;"";"Valor maximo: "&c;e);f)

Obtenemos una matriz desbordada con los registros para el valor mínimo, y, máximo, pero, obtenemos error donde no hay coincidencia.

Para quitar el error, usamos la función SI.ERROR.

Por último, vamos a aplicar un formato condicional a valor mínimo, y, valor máximo.

La expresión para usar en formato condicional, la vamos a realizar en una celda aparte, para ello, la matriz desbordada donde tenemos los resultados es I2#, ponemos la función ENCONTRAR, como argumento texto buscado, entre comillas dobles, ponemos “V”, como argumento dentro del texto, seleccionamos desde I2 hasta, por ejemplo, I15.

Cerramos paréntesis.

=ENCONTRAR("V";I2:I15)

Aceptamos.

Obtenemos una matriz desbordada en vertical con 1 donde hay coincidencia, y, error donde no la hay.

Quitamos el error con la función SI.ERROR.

=SI.ERROR(ENCONTRAR("V";I2:I15);"")

Igualamos la función a 1.

=SI.ERROR(ENCONTRAR("Valor";I2:I15);"")=1

Obtenemos una matriz desbordada con VERDADERO donde hay coincidencia, y, FALSO donde no la hay.

Esta función ya la podemos usar en formato condicional, porque solo acepta expresiones que devuelvan VERDADERO o FALSO.

Seleccionamos la expresión, y, la copiamos.

Seleccionamos desde I2 a O15, desplegamos formato condicional, y, seleccionamos nueva regla.

Seleccionamos la ultima opción, y, en la ventana dar formato, pegamos la expresión.

Damos un formato.

Aceptamos, y, ya lo tenemos.

Miguel Angel Franco