Para el siguiente ejemplo, trabajamos con el modelo que suelo usar habitualmente, pero mar corto, tenemos los meses de enero y febrero para el año 2023.
Pero no todos los días de ambos meses tienen ventas, nos solicitan crear un nuevo informe donde aparezcan todos los días para el mes de enero y febrero aunque no haya habido ventas.
Lo primero que vamos a hacer es crear una lista única de meses, para saber con los meses que vamos a trabajar, para ello, usare la función UNICOS, y, como argumento usare la función MES aplicada a la columna fecha.
=UNICOS(MES(Ventas3[Fecha]))
Tenemos una matriz desbordada con los meses 1 y 2.
Debemos de crear un calendario empezando, en este caso, desde el día 1 de enero y hasta el 20 de febrero.
Voy a utilizar la función FECHA, donde como argumento año, ponemos 2023, como argumento mes, es la expresión anterior, y, como argumento día, ponemos 1.
=FECHA(2023;UNICOS(MES(Ventas3[Fecha]));1)
Aceptamos, y, tenemos una matriz desbordada de dos filas con el principio de cada mes.
Necesitamos otra columna con el ultimo día de cada mes, para ello, usaremos la función APILARH (HSTOCK), donde como argumento matriz1, es la expresión anterior, y, como argumento matriz2, usamos FIN.MES, donde como argumento fecha inicial, es la función FECHA, y, como argumento meses, ponemos 0.
=APILARH(FECHA(2023;UNICOS(MES(Ventas3[Fecha]));1);FIN.MES(FECHA(2023;UNICOS(MES(Ventas3[Fecha]));1);0))
Tenemos una matriz desbordada de dos filas y dos columnas, donde tenemos el inicio y fin para cada mes.
Necesitamos una columna más con la diferencia de días para poder crear ambos calendarios, vamos a usar la función LET donde creamos una primera variable y almacenamos la función FECHA.
=LET(a;FECHA(2023;UNICOS(MES(Ventas3[Fecha]));1)
Creamos otra variable y almacenamos la función FIN.MES.
=LET(a;FECHA(2023;UNICOS(MES(Ventas3[Fecha]));1);b;FIN.MES(a;0)
Punto y coma, ponemos APILARH, como argumento matriz1, es la variable A, como argumento matriz2 es la variable B, y, como argumento matriz3, es la resta de ambas variables mas 1, si no sumamos 1, nos dará un día menos.
=LET(a;FECHA(2023;UNICOS(MES(Ventas3[Fecha]));1);b;FIN.MES(a;0);APILARH(a;b;b-a+1))
Aceptamos y tenemos una matriz desbordada de dos filas y tres columnas, con la fecha de inicio, de fin, y, la diferencia de días.
Ahora, vamos a crear el calendario para el mes de enero, para ello, usaremos la función SECUENCIA, como argumento filas, es la columna 3 de la matriz desbordada obtenida anteriormente, para ello, usamos la función INDICE, donde como argumento matriz es la matriz desbordada, como argumento número de fila, ponemos 1, y, como argumento número de columna, ponemos 3.
=SECUENCIA(INDICE(I2#;1;3)
Punto y coma, omitimos el argumento columnas, como argumento inicio es la primera celda de la matriz desbordada, volvemos a usar la función INDICE.
Como argumento paso, ponemos 1.
Cerramos paréntesis.
=SECUENCIA(INDICE(I2#;1;3);;INDICE(I2#;1;1);1)
Ya tenemos el calendario para el mes de enero, pero el mes de febrero debe de aparecer después del último día del mes de enero, para ello, usamos la función APILARV (VSTACK), como como argumento matriz1, es la expresión anterior.
=APILARV(SECUENCIA(INDICE(I2#;1;3);;INDICE(I2#;1;1);1)
Como argumento matriz2, es la misma función INDICE, pero cambiamos el argumento numero de fila de 1 a 2.
=APILARV(SECUENCIA(INDICE(I2#;1;3);;INDICE(I2#;1;1);1);SECUENCIA(INDICE(I2#;2;3);;INDICE(I2#;2;1);1))
Cerramos paréntesis.
Añadimos una hoja nueva, donde crearemos el informe final.
Lo primero que vamos a hacer es traernos los encabezados.
=Ventas[#Encabezados]
En la celda A2, vamos a poner I5# de la hoja donde se encuentra el modelo, que es el calendario para el mes de enero y febrero.
='DATOS (2)'!I5#
Lo siguiente es traernos los datos de la tabla donde si hay fechas de ventas.
Para ello, usare la función BUSCARX, donde como valor buscado, seleccionamos la celda A2.
=BUSCARX(A2
Punto y coma, como argumento matriz de búsqueda, seleccionamos la columna de fecha.
=BUSCARX(A2;Ventas[Fecha]
Punto y coma, como argumento matriz devuelta, seleccionamos las columnas desde provincia hasta total.
=BUSCARX(A2;Ventas[Fecha];Ventas[[Provincia]:[Total]]
Punto y coma, como argumento si no se encuentra, ponemos el texto Sin datos.
Cerramos paréntesis.
=BUSCARX(A2;Ventas3[Fecha];Ventas3[[Provincia]:[Total]];"Sin datos")
Aceptamos y en la primera celda aparece sin datos, arrastramos y ya tenemos nuestro modelo con todas las fechas, donde hubo ventas, aparecen dichas ventas, y, donde no hubo aparece sin datos.
Pero, que ocurre si añadimos un nuevo mes, por ejemplo, marzo, pues, no pasa absolutamente nada, porque manualmente le estamos indicando a que fila y columna debe de ir.
Vamos a convertir nuestro modelo en dinámico.
Si añadimos un nuevo mes, en la matriz desbordada aparece dicho mes.
Donde no aparece es en los listados por la explicación anterior.
Con la siguiente expresión, creamos el calendario del mes de enero y febrero, suponiendo que no va a ver más meses.
=APILARV(SECUENCIA(INDICE(I2#;1;3);;INDICE(I2#;1;1);1);SECUENCIA(INDICE(I2#;2;3);;INDICE(I2#;2;1);1))
Con la expresión siguiente que corresponde a la expresión anterior, creamos el calendario para el mes de enero.
SECUENCIA(INDICE(I2#;1;3);;INDICE(I2#;1;1);1)
Con la siguiente expresión, creamos el calendario para el mes de febrero.
SECUENCIA(INDICE(I2#;2;3);;INDICE(I2#;2;1);1)
¿Cómo lo hacemos dinámico?
Con la expresión APILARV, ponemos primero el mes de enero, y a continuación, el mes de febrero, pero si añadimos un nuevo mes, no pasa nada, porque siempre va a las filas 1, 2, y, columnas 3 y 1, para hacerlo dinámico, debo de saber el numero de filas totales de dicha matriz desbordada, cosa que consigo con la función FILAS, si uso la siguiente expresión:
Vemos que nos devuelve 3, ahora, si es dinámico, porque siempre nos dará el numero de filas que haya en la matriz desbordada.
Si con la función INDICE, donde como argumento array, es la matriz desbordada con las fechas de inicio, fin, y, diferencia días.
=INDICE(I2#
Punto y coma, como argumento numero de filas, es la función SECUENCIA, donde como argumento filas, es la función FILAS.
=INDICE(I2#;SECUENCIA(FILAS(I2#))
Punto y coma, ahora viene el argumento numero de columna, que también lo haremos dinámico, y, siempre será la ultima columna, pues, igual que antes usamos FILAS para calcular el numero de filas, ahora usamos la función COLUMNAS para obtener en numero de columnas, y, es el argumento numero de columna de la función INDICE.
Cerramos parentisis.
=INDICE(I2#;SECUENCIA(FILAS(I2#));COLUMNAS(I2#))
Aceptamos, y, obtenemos una matriz desbordada en vertical con la diferencia de días para cada mes.
Con la función SUMA, sumamos todos los valores.
=SUMA(INDICE(I2#;SECUENCIA(FILAS(I2#));COLUMNAS(I2#)))
En este caso, obtenemos 90 días.
Esta función es el argumento filas de SECUENCIA para crear el calendario.
=SECUENCIA(SUMA(INDICE(I2#;SECUENCIA(FILAS(I2#));COLUMNAS(I2#)))
Omitimos el argumento columnas, como argumento inicio seleccionamos la celda I2, que es la primera fecha, como argumento paso, ponemos 1, y, cerramos paréntesis.
=SECUENCIA(SUMA(INDICE(I2#;SECUENCIA(FILAS(I2#));COLUMNAS(I2#)));;I2;1)
Ya tenemos nuestro calendario de manera dinámica, podemos ver que empieza en el día 1 de enero y termina el día 31 de marzo.
El informe lo vamos a hacer en una nueva hoja, en la celda A1 volvemos a traernos el calendario, en la celda A2, nos traemos el calendario.
La formula para extraer los datos es la misma que hemos usado para el primer ejercicio, solo tenemos que cambiar el nombre de la tabla.
=BUSCARX(A2;Ventas2[Fecha];Ventas2[[Provincia]:[Total]];"Sin datos")
Arrastramos y ya lo tenemos de forma dinámica, podemos añadir o quitar meses que nuestro informe seguirá funcionando.
Y si queremos mostrar el informe, pero deseleccionado un mes.
Vamos a ello.
Lo primero que debemos de hacer es crear una lista con los meses disponibles, para ello, usare la función TEXTO, donde como argumento valor es la matriz derramada de I2.
=TEXTO(INDICE(I2#
Punto y coma, como argumento formato, debemos de usar la función INDICE, donde como argumento array, es la matriz I2#, como argumento número de fila es la función SECUENCIA, donde como argumento filas, usamos la función FILAS sobre la matriz en I2#, como argumento numero de columna ponemos 1, o, podemos omitirlo.
=TEXTO(INDICE(I2#;SECUENCIA(FILAS(I2#));1)
Punto y coma, como argumento formato de TEXTO, entre comillas dobles, ponemos “mmmm”).
Cerramos paréntesis.
=TEXTO(INDICE(I2#;SECUENCIA(FILAS(I2#));1);"mmmm")
Aceptamos, y, ya tenemos los meses disponibles.
Creamos una lista desplegable con los meses, para ello, vamos a la pestaña datos, dentro de herramientas de datos, hacemos clic en validación de datos.
Se abre la ventana de validación de datos, desplegamos permitir y seleccionamos lista, en la ventana origen, ponemos N2 seguido del operador de rango derramado.
Aceptamos, y, seleccionamos un mes, dicho mes es el que no debe de aparecer en el informe, por ejemplo, he seleccionado febrero.
Para este ejemplo, vamos a usar la función DESREF.
Primero, debemos de crear el mismo bloque con las fechas de inicio, fin, y, diferencia, excepto el mes seleccionado, para ello, uso la función FILTRAR donde como argumento array es la matriz I2#, como argumento include es la matriz N2# siempre que sea diferente a M2, que es el mes seleccionado.
Cerramos paréntesis.
=FILTRAR(I2#;N2#<>M2)
Aceptamos, y, tenemos los datos con que trabajar, como he seleccionado febrero, solo aparecen los meses de enero, marzo, y, abril.
Ahora, debemos de crear los calendarios para estos meses.
¿para que vamos a usar la función DESREF?
Para conseguir de forma dinámica el numero de fila donde movernos.
La primera fecha donde debemos de crear el primer calendario se encuentra en la celda I9.
Si uso la función FILAS sobre la matriz I9#, obtengo, en este caso, el valor de 3.
Si con la función DESREF, le digo que a partir de la celda I1, la cual fijamos con F4.
=DESREF(I1
Se mueva el numero de filas que indica la función FILAS, y, cero columnas.
=DESREF(I1;FILA(I9);0)
Me devuelve la segunda fecha.
Porque la celda A1 no cuenta, empieza a contar desde la celda siguiente, por lo que a la función FILAS debemos de restar 1.
=DESREF(I1;FILA(I9)-1;0)
Ya tengo la fecha correcta para crear el primer calendario, donde debemos de usar de nuevo la función SECUENCIA, donde como argumento array, es la función DESREF, pero en el argumento columnas, ponemos 2, para obtener la diferencia de días.
=SECUENCIA(DESREF(I1;FILA(I9)-1;2)
Omitimos el argumento columnas, como argumento inicio, es de nuevo la función DESREF, pero en el argumento columnas, ponemos 0, para que inicie en la primera fecha, como argumento paso, ponemos 1, y, cerramos paréntesis.
Transponemos.
=TRANSPONER(SECUENCIA(DESREF(I1;FILA(I9)-1;2);;DESREF(I1;FILA(I9)-1;0);1))
Ya tenemos el calendario para la primera fecha.
Fijamos I1.
=TRANSPONER(SECUENCIA(DESREF($I$1;FILA(I9)-1;2);;DESREF($I$1;FILA(I9)-1;0);1))
Arrastramos, y, tenemos el calendario para cada fecha.
Ahora nos queda el informe final, que igual que antes, lo haremos en una nueva hoja.
Como tenemos una matriz desbordada para cada mes, a la hora de trabajar debemos de estar arrastrando, y, queremos evitarlo, queremos que sea de forma automática, en la hoja nueva, volvemos a traernos los encabezados.
=Ventas3[#Encabezados]
Nos colocamos en la celda A2.
Vamos a usar la función DESREF para traernos las tres matrices, en este caso, en una sola matriz, ponemos la función, como argumento referencia, ponemos la celda L1 de la hoja DATOS3, porque es en esa columna donde empiezan nuestras matrices.
=DESREF(DATOS3!$L$1
Punto y coma, como argumento filas, aplicamos la función FILA a la celda I9 de la hoja DATOS3, que es donde empieza la primera matriz, y, restamos 1.
=DESREF(DATOS3!$L$1;FILA(DATOS3!$I$9)-1
Punto y coma, como argumento columnas ponemos 0.
=DESREF(DATOS3!$L$1;FILA(DATOS3!$I$9)-1;0
Como argumento alto, usamos la función FILAS sobre la matriz de I9# de la hoja DATOS3.
=DESREF(DATOS3!$L$1;FILA(DATOS3!$I$9)-1;0;FILAS(DATOS3!I9#)
Punto y coma, como argumento ancho, voy a poner el máximo de días, es decir, 31.
Cerramos paréntesis.
=DESREF(DATOS3!$L$1;FILA(DATOS3!$I$9)-1;0;FILAS(DATOS3!I9#);31)
Aceptamos.
Tenemos una matriz desbordada en horizontal, pero una sola matriz con todas las fechas, si nos movemos al final de las fechas, como hemos puesto un ancho de 31, si el mes no tiene 31 días, aparece cero días.
Pero esto ahora no nos preocupa, ahora, debemos de poner todas estas fechas en una sola columna, para ello, voy a usar la función ENCOL, y, el argumento es la función DESREF.
=ENCOL(DESREF(DATOS3!$L$1;FILA(DATOS3!$I$9)-1;0;FILAS(DATOS3!I9#);31))
Ya tenemos la fechas en vertical, pero ahora si debemos de preocuparnos de esos ceros de más, para ello, voy a filtrar la expresión anterior, siempre que dicha expresión sea diferente a cero.
=FILTRAR(ENCOL(DESREF(DATOS3!$L$1;FILA(DATOS3!$I$9)-1;0;FILAS(DATOS3!I9#);31));ENCOL(DESREF(DATOS3!$L$1;FILA(DATOS3!$I$9)-1;0;FILAS(DATOS3!I9#);31))<>0)
Podemos usar la función LET para acortar la sintaxis.
Si vamos bajando las fechas, veremos que todas son consecutivas sin ceros de más.
Si vamos cambiando el mes para que no aparezca, veremos que las fechas son correctas.
Ahora, nos queda traernos la función para extraer los datos de la tabla, pero la expresión es la misma que para los ejercicios anteriores, lo único que tenemos que cambiar es el nombre de la tabla.
=BUSCARX(A2;Ventas3[Fecha];Ventas3[[Provincia]:[Total]];"Sin datos")
Arrastramos y ya lo tenemos.
Y si queremos además filtrar por un día del mes.
Hagámoslo.
Será en una hoja nueva.
Con la función SECUENCIA, uso el argumento columnas con el valor de 31.
=SECUENCIA(;31)
Creamos una validación de datos, con dichos valores.
Seleccionamos un día, por ejemplo, el día 7.
Vamos a la expresión que ya tenemos.
TRANSPONER(SECUENCIA(DESREF($I$1;FILA(I9)-1;2);;DESREF($I$1;FILA(I9)-1;0);1))
Témenos que filtrar la expresión anterior, si el día de dicha expresión es menor o igual al día seleccionado.
=FILTRAR(TRANSPONER(SECUENCIA(DESREF($I$1;FILA(I9)-1;2);;DESREF($I$1;FILA(I9)-1;0);1));DIA(TRANSPONER(SECUENCIA(DESREF($I$1;FILA(I9)-1;2);;DESREF($I$1;FILA(I9)-1;0);1)))<=$O$2)
Vamos a crear una función LET para acortar la sintaxis.
=LET(a;TRANSPONER(SECUENCIA(DESREF($I$1;FILA(I9)-1;2);;DESREF($I$1;FILA(I9)-1;0);1));FILTRAR(a;DIA(a)<=$O$2))
Ahora, debemos de seguir los mismos pasos que pare el ejercicio anterior para traernos los datos en una nueva hoja, solo debemos de cambiar el nombre de la hoja.
=FILTRAR(ENCOL(DESREF(DATOS4!$L$1;FILA(DATOS4!$I$9)-1;0;FILAS(DATOS4!I9#);31));ENCOL(DESREF(DATOS4!$L$1;FILA(DATOS4!$I$9)-1;0;FILAS(DATOS4!I9#);31))<>0)
Y para obtener los datos sigue siendo la misma función que para el ejercicio anterior, solo debemos de cambiar el nombre de la tabla.
=BUSCARX(A2;Ventas4[Fecha];Ventas4[[Provincia]:[Total]];"Sin datos")
Y ya lo tenemos.
Miguel Angel Franco Garcia
Comments