Tenemos los datos de todo el año 2017, donde, vamos a crear un cuadro con los nombres de los meses, para ello, voy a usar la función TEXTO, para extraer el nombre del mes de la columna fecha.
=TEXTO(Table16[Fecha];"mmmm"))
Voy a quedarme con los valores únicos, por lo que uso la función UNICOS.
=UNICOS(TEXTO(Table16[Fecha];"mmmm")))
Por último, transpongo con la función TRANSPONER.
=TRANSPONER(UNICOS(TEXTO(Table16[Fecha];"mmmm"))))
Vamos a calcular cual fue el último día de venta de cada mes, para ello, voy a preguntar si el mes de la columna fecha, es igual al valor de la celda E1, es decir, enero, que me devuelva el día de la columna fecha.
=SI(TEXTO(Table1[Fecha];"mmmm")=E1;DIA(Table1[Fecha])
Obtengo una matriz desbordada con los días del mes de enero, y, FALSO donde no hay coincidencia.
Pues, ahora me quedo con el día máximo, para ello, uso la función MAX.
=MAX(SI(TEXTO(Table1[Fecha];"mmmm")=E1;DIA(Table1[Fecha]);""))
Ya tengo el día máximo para el mes de enero.
Seleccionamos la función, pulsamos CTRL mas C para copiar, seleccionamos desde F2 a P2, botón alternativo del ratón, y, seleccionamos pegar formula.
Ya tenemos el ultimo día de venta para cada mes.
Ahora, vamos a poner un estado para cada mes, donde si no se ha realizado ventas hasta el último día del mes, que ponga incompleto, en caso contrario, que ponga completo.
Si uso la función SECUENCIA, donde uso el argumento numero de columnas, e, inicio, de la siguiente manera:
=SECUENCIA(;12;1)
Me crea una matriz desbordada de doce columnas, empezando desde el numero 1, hasta el número 12.
Ahora, voy a usar la función FECHA, donde como argumento año, pongo 2017, como argumento mes, es la función SECUENCIA anterior, y, como argumento día, ponemos 1.
=FECHA(2017;SECUENCIA(;12;1);1)
Obtengo una matriz desbordada con el día 1 de cada mes.
Ahora, con la función FIN.MES, voy a traerme el ultimo día de cada mes, esta función tiene dos argumentos, el primero de ellos es la fecha con la que vamos a trabajar, y, el segundo argumento son los meses para adelantar, o, retrasar, en este caso, como voy a trabajar con la fecha de una celda, ponemos cero.
=FIN.MES(FECHA(2017;SECUENCIA(;12;1);1);0)
Tenemos el ultimo día de cada mes.
Ahora, resto estos días con la ultima fecha de venta de cada mes.
=DIA(FIN.MES(FECHA(2017;SECUENCIA(;12;1);1);0))-E2:P2
Vemos la diferencia de días, y, cero donde se ha realizado una venta, el último día del mes.
Ahora, preguntamos que, si la diferencia de días es cero, que ponga completo, en caso contrario, que ponga incompleto.
=SI(DIA(FIN.MES(FECHA(2017;SECUENCIA(;12;1);1);0))-E2:P2=0;"Completo";"Incompleto")
Lo siguiente es calcular el total para cada mes, para ello, voy a preguntar si el mes de la columna fecha es igual al valor de la celda E1, es decir, enero, que me devuelva la columna total, en caso contrario, que me devuelva un texto en blanco, y, sumamos el resultado, lo vamos a hacer con la función FILTRAR.
=SUMA(FILTRAR(Table16[Total];TEXTO(Table16[Fecha];"mmmm")=E1))
Igual que antes, seleccionamos la función, pulsamos CTRL mas C, para copiar, seleccionamos las celdas de los siguientes meses, botón alternativo de ratón, y, seleccionamos pegar formula.
Lo siguiente va a ser crear dos matrices, una donde deben de aparecer los meses completos, y, otra los meses no completos junto con los totales.
Para los meses completos, voy a usar la función FILTRAR, para que me filtre desde E1 a P1, si el valor del rango E3:P3, es igual al valor de la celda K3, que es completo.
=FILTRAR(E1#;E3#=K3)
Obtenemos una matriz desbordada en horizontal, con los meses, pues usamos TRANSPONER, para ponerlos en vertical.
=TRANSPONER(FILTRAR(E1#;E3#=K3))
Ahora, vamos a calcular los totales, para ello, voy a usar la función BUSCARX, donde el argumento valor buscado, son los meses que hemos obtenido, como matriz de búsqueda, es desde E1 a P1, y, como matriz devuelta, es desde E4 a P4.
=BUSCARX(D7#;E1#;E4:P4)
Ya tenemos los totales.
Para los meses no completos, son las mismas funciones, pero en la función FILTRAR, cambiamos completo, por incompleto.
Lo siguiente es marcar las ventas máximas de cada bloque, para ello, selecciono los meses, y, los valores del primer bloque, vamos a la pestaña de inicio, desplegamos formato condicional, y, seleccionamos nueva regla.
En la ventana que se abre, seleccionamos la ultima opción, que nos permite introducir una función, o, formula, debemos de tener en cuenta, que solo podemos poner funciones que devuelvan VERDADERO o FALSO.
En la ventana para introducir la función, vamos a preguntar si el valor máximo del rango que hemos seleccionado es igual al valor de la celda E7, que ponga el valor de la celda E7, debemos de añadir un símbolo dólar antes de la columna de cada referencia, para que se resalte la fila completa.
Hacemos clic en formato, marco tachado y le doy un fondo claro.
Aceptamos.
Vemos como queda resaltado el mes de más ventas.
Hacemos lo mismo, para los meses no completos.
Por último, vamos a crear un desplegable para seleccionar un mes, y, queremos ver las ventas a partir del mes seleccionado, hasta el último día de venta del mes seleccionado.
En la celda G8, voy a crear este desplegable, para ello, vamos a la pestaña de datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.
Desplegamos permitir, y, seleccionamos lista, y, en la ventana de origen, seleccionamos los meses.
Aceptamos.
Elegimos en mes.
Primero, vamos a calcular, como lo hemos hecho antes, el ultimo día de venta de este mes, para ello, uso la función BUSCARX, donde l argumento valor buscado, es el mes que hemos seleccionado, debe de buscarlo en E1:P1, y, debe de devolvernos el resultado de E2:P2.
=BUSCARX(G8;E1#;E2:P2)
Ahora, nos debe de mostrar los meses a partir de abril, pero no abril, sino del siguiente mes.
Si en una celda, uso la función COINCIDIR, para buscar el valor de G8, es decir, el mes que hemos seleccionado, en el rango R1:R12, donde están los meses, con una coincidencia exacta.
=COINCIDIR(G8;R1#;0)
Me devuelve la posición 4, esta sería la posición del mes que hemos seleccionado, pero queremos a partir del mes siguiente, por lo que voy a sumar 1 al resultado de COINCIDIR.
=COINCIDIR(G8;R1#;0)+1
Ahora, me devuelve la posición 5.
En otra celda, voy a poner solo la función COINCIDIR, y, le resto 12, que son los meses.
=12-COINCIDIR(G8;R1#;0)
Me devuelve 8, que son los meses desde mayo a diciembre.
Bien, pues voy a crear una matriz con la función SECUENCIA, donde el argumento filas, va a 12 menos COINCIDIR.
=SECUENCIA((12-COINCIDIR(G8;R1#;0))
El argumento columnas, lo voy a saltar.
=SECUENCIA((12-COINCIDIR(G8;R1#;0));
Y el argumento inicio, debe de empezar donde indicar COINCIDIR +1.
=SECUENCIA((12-COINCIDIR(G8;R1#;0));;COINCIDIR(G8;R1#;0)+1)
Tenemos una matriz desbordada desde el mes siguiente al seleccionado hasta el mes de diciembre.
Pero, quiero los nombres de los meses, para ello, voy a usar la función BUSCARX, donde el argumento valor buscado, es cada numero devuelto por la función SECUENCIA, como argumento matriz de búsqueda, va a ser la columna fecha, pero usamos la función MES, para que compare con el numero de mes, y, como argumento matriz devuelta, usamos la función TEXTO, para que nos devuelva el nombre del mes.
=BUSCARX(SECUENCIA((12-COINCIDIR(G8;R1#;0));;COINCIDIR(G8;R1#;0)+1);MES(Table16[Fecha]);TEXTO(Table16[Fecha];"mmmm"))
Ya tenemos los nombres de los meses.
Si cambiamos de mes, veremos que los meses que aparecen son los correctos.
Ahora, voy a usar la función SECUENCIA, como lo hemos hecho antes, para extraer el número de mes.
=SECUENCIA((12-COINCIDIR(G8;R1#;0));1;COINCIDIR(G8;R1#;0)+1)
Ahora, calculamos el ultimo día de venta de los meses que vemos en pantalla.
Voy a preguntar que si el mes de la columna fecha, es igual a H11, que me devuelva la columna fecha, pero me quedo con el valor máximo, y, con el día, entonces, si el resultado de esta función es cero.
=SI(DIA(MAX((TEXTO(Table16[Fecha];"mmmm")=H11)*(Table16[Fecha])))=0
Que ponga un texto en blanco.
=SI(DIA(MAX((TEXTO(Table16[Fecha];"mmmm")=H11)*(Table16[Fecha])))=0;""
En caso contrario, que ejecute la función.
=SI(DIA(MAX((TEXTO(Table16[Fecha];"mmmm")=H11)*(Table16[Fecha])))=0;"";DIA(MAX((TEXTO(Table16[Fecha];"mmmm")=H11)*(Table16[Fecha]))))
Obtenemos el último día de venta del primer mes que vemos.
Pero, usamos dos veces la misma función, por lo que podemos usar la función LET, donde creamos una variable, y, almacenamos la función, como sigue:
=LET(a;DIA(MAX((TEXTO(Table16[Fecha];"mmmm")=H11)*(Table16[Fecha])));SI(a=0;""))
Arrastramos hacia abajo.
Si elijo noviembre, vemos que solo nos aparece diciembre.
Nos queda calcular el total para los meses que vemos en pantalla, pero hasta el último día del mes seleccionado, para ello, voy a usar la función FILTRAR, para que me filtre la columna total, si el mes de la columna fecha es igual al valor de G11, es decir, el primer mes que vemos, y, además, es menor o igual al último día de venta del mes seleccionado.
=FILTRAR(Table16[Total];(MES(Table16[Fecha])=G11)*(DIA(Table16[Fecha])<=$H$8))
Vamos a obtener una matriz desbordada, pero queremos el total, por lo que después del signo igual, usamos la función SUMA.
=SUMA(FILTRAR(Table16[Total];(MES(Table16[Fecha])=G11)*(DIA(Table16[Fecha])<=$H$8)))
Arrastramos, y, vemos que donde no hay meses, nos devuelve un error, pues vamos a usar la función SI.ERROR, donde vamos a preguntar que si la función devuelve un error, que devuelva un texto en blanco, y, arrastramos doce veces, por el número de meses.
=SI.ERROR(SUMA(FILTRAR(Table16[Total];(MES(Table16[Fecha])=G11)*(DIA(Table16[Fecha])<=$H$8)));"")
Ya tenemos el total de los meses que vemos en pantalla.
Comments