Para el siguiente ejemplo, tenemos una columna con fechas, y, otra columna con totales.
Para el primer ejercicio, vamos a realizar un pronóstico, vamos a poner una fecha, que será la fecha de fin de pronóstico.
En otra celda vamos a poner los meses a pronosticar, meses que se contaran hacia atrás, por ejemplo, si tenemos la fecha 15/09/2022, y, como meses a pronosticar, ponemos 1, pues debe de ir desde la fecha 15/08/2022 hasta el 15/09/2022.
Empezamos el ejercicio.
En la celda F2, vamos a calcular la fecha de inicio del pronóstico, para ello, vamos a usar la función FECHA, donde como argumento año, ponemos 2022, pues es el único que tenemos.
=FECHA(2022
Punto y coma, como argumento mes, con la función MES, extraemos el mes de fecha fin pronostico, y, restamos 1.
=FECHA(2022;MES(D2)-E2
Punto y coma, como argumento día, extraemos el día de fecha fin pronostico.
Cerramos paréntesis.
=FECHA(2022;MES(D2)-E2;DIA(D2))
Aceptamos, y, ya tenemos la fecha de comienzo para el pronóstico.
Como he puesto 3 meses, el inicio del pronostico es la fecha 15/06/2022 y como fin de pronóstico, es la fecha 15/09/2022, es decir, debemos de rescatar los totales entre las fechas 15/06/2022 y 15/09/2022.
Vamos a crear un reporte con dichas fechas con sus totales, para ello, usamos FILTRAR, donde como argumento array, es la tabla ventas.
=FILTRAR(Ventas
Punto y coma, como argumento include, abrimos unos paréntesis para poner una primera condición, la cual es que columna fecha de la tabla ventas debe de ser mayor o igual al valor de la celda F2.
=FILTRAR(Ventas;(Ventas[Fecha]>=F2)
Ponemos el símbolo de asterisco, abrimos otros paréntesis, y, la siguiente condición es que la columna fecha debe de ser menor o igual al valor de la celda D2.
Cerramos paréntesis.
=FILTRAR(Ventas;(Ventas[Fecha]>=F2)*(Ventas[Fecha]<=D2))
Ya tenemos nuestro reporte donde podemos comprobar que va desde la fecha de inicio hasta la fecha de fin, si en la fecha de inicio, no existe ventas, pasara a la siguiente fecha.
Lo siguiente es calcular el total para la fecha fin pronostico desde el día hasta final de mes de dicha fecha, igual para la fecha de inicio de pronóstico.
Primero, calculamos el total para fecha fin pronostico, para ello, vamos a usar la función SUMAR.SI.CONJUNTO, donde como argumento rango de suma, ponemos la columna de total.
=SUMAR.SI.CONJUNTO(Ventas[Total]
Punto y coma, como argumento rango de criterios1, seleccionamos la columna de fecha.
=SUMAR.SI.CONJUNTO(Ventas[Total];Ventas[Fecha]
Punto y coma, como argumento criterios1, entre comillas dobles, ponemos “>=”, y, concatenamos con el valor de la celda D2.
=SUMAR.SI.CONJUNTO(Ventas[Total];Ventas[Fecha];">="&D2
Punto y coma, como argumento rango de criterios2, volvemos a poner la columna fecha.
=SUMAR.SI.CONJUNTO(Ventas[Total];Ventas[Fecha];">="&D2;Ventas[Fecha]
Punto y coma, como argumento criterios2, entre comillas dobles, ponemos “<=”, y, concatenamos con la función FIN.MES, donde como argumento fecha inicial es la celda D2, y, como numero de meses ponemos 0.
Cerramos paréntesis.
=SUMAR.SI.CONJUNTO(Ventas[Total];Ventas[Fecha];">="&D2;Ventas[Fecha];"<="&FIN.MES(D2;0))
Aceptamos, y, tenemos el total para la fecha de fin pronostico hasta final de mes.
Para el calculo de la fecha de inicio de pronóstico, es la misma función, pero debemos de cambiar la celda D2 por la celda F2.
=SUMAR.SI.CONJUNTO(Ventas[Total];Ventas[Fecha];">="&F2;Ventas[Fecha];"<="&FIN.MES(F2;0))
Debemos de asegurarnos, que no se introduce un numero de meses mayor a 12, para ello, vamos a la celda D6, donde comienza el reporte, vamos a la barra de fórmulas, después del signo igual, usamos el condicional SI, donde preguntamos que si el valor de la celda E2 es mayor a 12.
=SI(E2>12
En ese caso, que ponga el siguiente texto.
=SI(E2>12;"Nº de meses maximo: 12"
En caso contrario, que ejecute la función FILTRAR.
Cerramos paréntesis.
=SI(E2>12;"Nº de meses maximo: 12";FILTRAR(Ventas;(Ventas[Fecha]>=F2)*(Ventas[Fecha]<=D2)))
Aceptamos, y, ya lo tenemos, pero probémoslo, pongamos como numero de meses 13.
Vemos que funciona correctamente.
También, seria importante que las siguientes cantidades, si el numero de meses es mayor a 12, no aparecieran.
Para ello, vamos a la celda D3, que es el total para fecha fin pronostico, usamos LET y almacenamos en una variable la función.
=LET(a;SUMAR.SI.CONJUNTO(Ventas[Total];Ventas[Fecha];">="&D2;Ventas[Fecha];"<="&FIN.MES(D2;0))
Punto y coma, con el condicional SI, preguntamos que, si no es número el valor de la celda D6, que ponga un texto en blanco, en caso contrario, que devuelva la variable A.
Cerramos paréntesis.
=LET(a;SUMAR.SI.CONJUNTO(Ventas[Total];Ventas[Fecha];">="&D2;Ventas[Fecha];"<="&FIN.MES(D2;0));SI(NO(ESNUMERO(D6));"";a))
Ya lo tenemos, para el total de fecha inicio pronostico, la función es la misma, solo debemos de cambiar D2 por F2.
=LET(a;SUMAR.SI.CONJUNTO(Ventas[Total];Ventas[Fecha];">="&F2;Ventas[Fecha];"<="&FIN.MES(F2;0));SI(NO(ESNUMERO(D6));"";a))
Pero, tenemos otro problema, estamos trabajando con el año 2022, por ejemplo, si tenemos la fecha 15/09/2022 y queremos hasta 9 nueves atrás, nos devuelve la fecha 15/12/2021, pero para dicha fecha no tenemos ventas, de ahí, que aparezca como resultado 0.
Para solventarlo, en la celda G2, vamos a usar el condicional SI, donde preguntamos que, si el valor de la celda F2 es menor, y, con la función FECHA ponemos la fecha 01/01/2022, en ese caso, que nos devuelva el valor de D2 menos 1, en caso contrario, que ponga un texto en blanco.
=SI(F2<FECHA(2022;1;1);MES(D2)-1;"")
Para este caso, vemos el numero de meses a introducir como máximo.
Si ponemos ocho meses a retrasar, vemos como la fecha de inicio, es el día 15 de enero de 2022, y, en la celda donde debe de aparecer los meses que se pueden solicitar, aparece en blanco.
Por último, vamos a la celda F2, y, preguntamos que, si el valor de la celda G2 es igual a blanco, que ejecute la expresión existente, en caso contrario, que aparezca un texto en blanco.
=SI(G2="";LET(a;SUMAR.SI.CONJUNTO(Ventas[Total];Ventas[Fecha];">="&F2;Ventas[Fecha];"<="&FIN.MES(F2;0));SI(NO(ESNUMERO(D6));"";a));"")
Ya no aparece valor, si introducimos mas de los meses disponibles.
Ahora, queremos crear el mismo reporte, pero en este caso, tenemos varios años.
Empecemos.
Igual que antes, tenemos meses a pronosticar, y, una fecha para fin de pronóstico.
En este caso, va a ser desde el ultimo día del mes de la fecha a pronosticar hasta el último día del mes de la fecha de inicio de pronóstico.
Vamos a calcular la fecha de inicio del pronóstico, para ello, usamos la función FIN.MES, donde como argumento fecha inicial es la celda E2, que es la fecha fin de pronóstico, punto y coma, y, como argumento numero de meses, ponemos el signo menos y la celda D2, porque vamos a restar meses.
=FIN.MES(E2;-D2)
Ya tenemos la fecha de fin de pronóstico.
Para la fecha final de pronóstico, usamos FIN.MES, donde como argumento fecha inicial es la celda E2, y, como argumento numero de meses, ponemos 0.
Cerramos paréntesis.
=FIN.MES(E2;0)
Ya tenemos la fecha donde comenzar y terminar.
Lo siguiente es crear el reporte entre dicho intervalo de fechas, para ello, usamos la función FILTRAR, este modelo esta en formato de tabla, y, se llama ventas2.
Como argumento array, es la table ventas2.
=FILTRAR(Ventas2
Punto y coma, como argumento include, abrimos unos paréntesis para poner la primera condición, dicha condición es que la columna fecha de ventas2, debe de ser mayor o igual a E4.
=FILTRAR(Ventas2;(Ventas2[Fecha]>=E4)
Ponemos el símbolo de asterisco, abrimos otros paréntesis, la siguiente condición es que la columna fecha de ventas2, debe de ser menor o igual al valor de E5, cerramos paréntesis.
=FILTRAR(Ventas2;(Ventas2[Fecha]>=E4)*(Ventas2[Fecha]<=E5))
Ya tenemos nuestro reporte.
Lo siguiente es calcular el total para ambas fechas, para ello, debemos de sumar la columna de total de la matriz desbordada obtenida, lo cual, haremos con SUMA, y, como argumento usamos INDICE, omitimos el argumento numero de fila, y, como argumento numero de columna, ponemos 2.
Cerramos paréntesis.
=SUMA(INDICE(D8#;;2))
Pues, ya lo tenemos.
Veamos otra forma de hacerlo, copiamos la hoja ejemplo2, y, la llamamos ejemplo3, en este caso, usaremos BUSCARX dentro de la función SUMA, donde vamos a especificar un rango, ya sabemos que, para especificar un rango como separador, debemos de poner desde donde comenzar, seguido de los dos puntos, y, donde terminar.
Borramos la función donde hemos calculado el total.
Con BUSCARX vamos a buscar la fecha comienzo de pronostico en la tabla ventas3, y, que nos devuelva la columna de total.
BUSCARX(E4;Ventas3[Fecha];Ventas3[Total])
Con el siguiente BUSCARX, buscamos la fecha de fin de pronóstico, en la columna fecha, y, que nos devuelva el total.
BUSCARX(E5;Ventas3[Fecha];Ventas3[Total])
Pues, ya tenemos el comienzo y final del rango que debemos de especificar a la función SUMA, quedaría como sigue:
=SUMA(BUSCARX(E4;Ventas3[Fecha];Ventas3[Total]):BUSCARX(E5;Ventas3[Fecha];Ventas3[Total]))
Vemos que tenemos el mismo resultado que para el ejercicio anterior.
Debemos de fijarnos en un detalle, que ocurre si en la celda E2, introduzco la siguiente fecha:
Pues, que nos devuelve un error, porque no tenemos ventas para el año 2024, o, posterior, igual que no la tenemos para el año 2016 o anterior, para solventar, haremos los pasos en una celda aparte, voy a filtrar la columna fecha de ventas3.
=FILTRAR(Ventas3[Fecha]
=FILTRAR(Ventas3[Fecha];(AÑO(Ventas3[Fecha])=AÑO(E4))
Siempre que el año de la columna fecha de ventas3 sea igual año de la celda E4, que es la fecha de inicio de pronóstico.
Además, debe de ser, también, igual al año del valor de la celda E5, que es la fecha de fin de pronóstico.
Cerramos paréntesis.
=FILTRAR(Ventas3[Fecha];(AÑO(Ventas3[Fecha])=AÑO(E4))*(AÑO(Ventas3[Fecha]=AÑO(E5))))
Aceptamos, y, tenemos una matriz desbordada con los valores correspondientes a dichos años, porque, en este caso, ambos años se encuentran dentro del modelo.
He puesto la siguiente fecha.
Nos devuelve un error de cálculo, porque el año 2016 no se encuentra dentro del modelo.
Y, si pongo la siguiente fecha.
Me sigue devolviendo un error de cálculo, porque la fecha de inicio de pronóstico, el año no se encuentra dentro del modelo.
Por lo que debemos de controlar dicho error, para ello, vamos a usar la función ESERR, uno podría preguntarse, ¿Por qué no usamos la función ESERROR?, bien, la función ESERR, contempla cualquier
valor de error excepto #N/A, y, la función ESERROR, contempla cualquier valor se refiere a uno de los siguientes valores de error (#N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #¡NULO!), donde vemos que no se contempla el error de cálculo (#CALC!).
Continuamos.
A la función FILTRAR le voy a preguntar si es error.
=ESERR(FILTRAR(Ventas3[Fecha];(AÑO(Ventas3[Fecha])=AÑO(E4))*(AÑO(Ventas3[Fecha]=AÑO(E5)))))
Vemos que para este caso nos devuelve VERDADERO, porque devuelve un error.
Debemos de preguntar que, si la función FILTRAR devuelve un error, que ponga “Años incorrectos”, en caso contrario, que ejecuta la función SUMA.
=SI(ESERR(FILTRAR(Ventas3[Fecha];(AÑO(Ventas3[Fecha])=AÑO(E4))*(AÑO(Ventas3[Fecha]=AÑO(E5)))));"Años incorrectos";SUMA(BUSCARX(E4;Ventas3[Fecha];Ventas3[Total]):BUSCARX(E5;Ventas3[Fecha];Ventas3[Total])))
Pero, nos devuelve el mismo total para cada celda, por lo que después del signo igual, usamos UNICOS.
=UNICOS(SI(ESERR(FILTRAR(Ventas3[Fecha];(AÑO(Ventas3[Fecha])=AÑO(E4))*(AÑO(Ventas3[Fecha]=AÑO(E5)))));"Años incorrectos";SUMA(BUSCARX(E4;Ventas3[Fecha];Ventas3[Total]):BUSCARX(E5;Ventas3[Fecha];Ventas3[Total]))))
Ya lo tenemos, para este caso, nos devuelve años incorrectos.
Pero, veamos otro problema que surge en la hoja ejemplo3, cosa que no ocurre en la hoja ejemplo2.
La matriz desbordada obtenida al filtrar por las fechas para el ejemplo2, vamos a ver las ultimas líneas de dicha matriz, es la siguiente:
Vemos que para las fechas solicitadas.
Debemos de buscar hasta el día 31/03/2017, pero vemos que el ultimo día es el día 30/03/2017, es decir, no se encuentra el día 31/03/2021, pero, vemos que el calculo del total se realiza correctamente.
Vayamos a la hoja ejemplo3, donde las fechas a buscar son las mismas.
Volvemos a mirar las ultimas líneas de la matriz desbordada, donde vemos de nuevo que aparece el día 30/03/2017 dos veces.
Pero el total nos devuelve un error.
Repasemos las funciones.
En la función del ejemplo2, sumamos la segunda columna de la matriz desbordada filtrada.
=SUMA(INDICE(D8#;;2))En el ejemplo3, debemos de usar la función BUSCARX, donde especificamos que valor buscar, en ese caso, el valor de la celda E4, y, de la celda E5, después con SUMA sumamos los totales en ese rango.
=UNICOS(SI(ESERR(FILTRAR(Ventas3[Fecha];(AÑO(Ventas3[Fecha])=AÑO(E4))*(AÑO(Ventas3[Fecha]=AÑO(E5)))));"Años incorrectos";SUMA(BUSCARX(E4;Ventas3[Fecha];Ventas3[Total]):BUSCARX(E5;Ventas3[Fecha];Ventas3[Total]))))
Ocurre que, para este caso, el valor de E5 no lo encuentra, porque no está, de ahí, que la SUMA nos devuelva un error.
Debemos de conseguir la fecha siguiente hacia atrás con ventas, o, hacia adelante, como cada uno vea mejor, vamos a conseguir las cinco siguientes fechas hacia atrás de la especificada en final pronostico, aunque también debemos de hacerlo en la fecha de inicio pronostico, en este caso, no hay error por que la fecha a buscar si se encuentra.
El caso consiste, por ejemplo, en la celda E5 tenemos la fecha 31/03/2017, he modificado algunos datos de las últimas fechas de marzo, donde vemos que el ultimo día de ventas para el mes de marzo de 2017, fue el día 27.
Quiere decir, que como en la fecha 31/03/2017 no hubo ventas, de ahí, el error de BUSCARX, debemos de buscar la siguiente fecha para el mes de marzo con ventas.
Para que la función no siga siendo cada vez más larga, en una hoja nueva, vamos a dividir las funciones.
Añadimos una hoja nueva.
En la celda A2, ponemos el primer BUSCARX.
En la celda B2, ponemos el segundo BUSCARX.
En la celda C3, ponemos la suma de los dos BUSCARX.
Tenemos el error porque no encuentra la fecha de fin de pronóstico.
En la celda E2 de esta hoja nueva, voy a filtrar la tabla ventas3, siempre que la columna fecha sea menor o igual al valor de E5 de la hoja ejemplo3, y, que nos devuelva la columna de total de ventas3.
FILTRAR(Ventas3;(Ventas3[Fecha]<=Ejemplo3!E5)*(Ventas3[Total]<>""))
Obtenemos una matriz desbordada desde la primera fecha desde inicio pronostico hasta la última fecha de fin pronostico.
Nos quedamos con el valor máximo de la columna fecha, por lo que debemos de usar INDICE para quedarnos con la primera columna.
=MAX(INDICE(FILTRAR(Ventas3;(Ventas3[Fecha]<=Ejemplo3!E5)*(Ventas3[Total]<>""));;1))
Ya tenemos la siguiente fecha anterior al día 31 de marzo de 2017.
Pero, debemos de hacer lo mismo para la fecha de comienzo pronostico, para ello, usamos la expresión anterior, pero cambiamos E5 por E4.
=MAX(INDICE(FILTRAR(Ventas3;(Ventas3[Fecha]<=Ejemplo3!E4)*(Ventas3[Total]<>""));;1))
Volvemos a la celda donde calculamos el total, cambiamos la función SUMA con los dos BUSCARX por el valor de la celda C2 de la hoja operaciones.
Vamos hasta después de la palabra años incorrectos, ponemos punto y coma, y, preguntamos si la celda E4 de la hoja ejemplo3 es igual al valor de la celda H2 de la hoja operaciones, o, la celda E5 de la hoja ejemplo3 es igual a la celda E2 de la hoja operaciones, en ese caso, debe de devolver el valor de la celda M2 de la hoja operaciones, en caso contrario, debe de devolver el valor de la celda C2 de la hoja operaciones.
=UNICOS(SI(ESERR(FILTRAR(Ventas3[Fecha];(AÑO(Ventas3[Fecha])=AÑO(E4))*(AÑO(Ventas3[Fecha]=AÑO(E5)))));"Años incorrectos";SI(O(Ejemplo3!E4=Operaciones!H2;Ejemplo3!E5=Operaciones!E2);Operaciones!M2;Operaciones!C2)))
Aceptamos, y, si probamos veremos que haya coincidencia, o, no la haya con la primera o última fecha, tenemos el total correcto.
Miguel Angel Franco Garcia
Comments