Para el siguiente ejemplo, tenemos una tabla, llamada ventas, con una columna de fechas, provincias, y, totales.
Queremos calcular por fecha, y, provincia el total, debemos de tener en cuenta la fecha, por ejemplo, la primera fecha es 03/01/2021, y, la provincia es Granada, quiere decir que debemos de calcular el total entre el día tres de enero y el ultima día de enero, además, la provincia debe de ser Granada, porque en Granada se ha podido realizar ventas en diferentes días de enero, así con el resto de los meses.
Tenemos que saber cuál es el último día del mes de cada fecha, para tener un margen inferior, y, superior con que comparar, para ello, vamos a usar la función FIN.MES.
En una celda escribimos el signo igual, seguido de la función, y, abrimos un paréntesis, como argumento fecha inicial, seleccionamos la columna fecha.
=FIN.MES(Ventas[Fecha]
Punto, y, como argumento meses, ponemos cero, porque queremos el fin de mes de cada fecha.
Cerramos paréntesis, y, aceptamos.
=FIN.MES(Ventas[Fecha];0)
Nos devuelve el error de valor, esto es debido a que esta función no podemos usarla de forma matricial, pero necesitamos usarla.
Tenemos una función llamada BYROW, que examinara cada fila del rango que especifiquemos, pues, después del signo igual, ponemos BYROW, y, abrimos un paréntesis.
Como argumento array, seleccionamos la columna fecha.
=BYROW(Ventas[Fecha]
Punto y coma, como argumento función, ponemos la función LAMBDA, abrimos un paréntesis, y, declaramos una variable, esta variable, tendrá almacenada la columna fecha.
=BYROW(Ventas[Fecha];LAMBDA(a
Punto y coma, como argumento calculo de la función LAMBDA, ponemos la función FIN.MES, como argumento fecha inicial, ponemos la variable A, y, como argumento meses, ponemos cero, cerramos paréntesis, y, aceptamos.
=BYROW(Ventas[Fecha];LAMBDA(a;FIN.MES(a;0)))
Aceptamos, y, tenemos el fin de mes para cada fecha.
Si no disponemos de la función BYROW, podemos hacerlo con el doble signo negativo, para ello, lo ponemos después de la apertura de paréntesis de la función FIN.MES, con esto conseguimos, que la función FIN.MES, trabaje de forma matricial.
=FIN.MES(--Ventas[Fecha];0)
Vemos que tenemos los mismos resultados obtenidos con la función BYROW.
Ya tenemos el final de mes, que sería el margen superior, pero también necesitamos el principio de mes, que sería el margen inferior.
Dentro de la función FIN.MES, como argumento meses, podemos usar una constante de matriz, si ponemos -1, quiere decir que me de el mes anterior, si dentro de la constante de matriz, ponemos -1, y, 0, nos devolverá una matriz desbordada de dos columnas, la primera de ellas, es el final de mes del mes anterior de la columna fecha, y, la siguiente columna, es el final de mes de la columna fecha.
=FIN.MES(--Ventas[Fecha];{-1\0})
Vemos, por ejemplo, que la primera fecha, el final de mes de la primera columna es 31/12/2020, es decir, el mes anterior, y, la siguiente columna el final de mes es 31/01/2021, que es el final de mes de la columna fecha.
Ahora, lo que voy a hacer es traerme a esta matriz desbordada, la columna de provincia, para ello, voy a usar la función APILARH, donde como argumento matriz1, es la expresión anterior, y, como argumento matriz2, es la columna de provincia.
=APILARH(FIN.MES(--Ventas[Fecha];{-1\0});Ventas[Provincia])
Si no disponemos de la función APILARH, podemos hacerlo con la función ELEGIR, bien, ponemos la función ELEGIR, el primer argumento es número de índice, es decir, de los valores dentro de ELEGIR, cual queremos elegir ponemos 1, y, como argumento valor1, ponemos la función FIN.MES.
=ELEGIR(1;FIN.MES(--Ventas[Fecha];{-1\0}))
Como solo tenemos un valor, pues el número de índice 1, es el único valor que tenemos, es decir, nos devuelve las dos columnas de fechas.
Ahora, a la función ELEGIR, voy a agregar el argumento valor2, que va a ser la columna de provincia.
=ELEGIR(1;FIN.MES(--Ventas[Fecha];{-1\0});Ventas[Provincia])
De momento, nos sigue devolviendo las dos columnas de fechas, que es el número de índice 1, pues, vamos a cambiar el número de índice a 2.
=ELEGIR(2;FIN.MES(--Ventas[Fecha];{-1\0});Ventas[Provincia])
En este caso, nos devuelve la columna de provincias, pero queremos que nos devuelva la matriz desbordada de fechas, y, la columna de provincias, como argumento numero de índice, podemos usar una constante de matriz, por lo que vamos a cambiar el argumento numero de índice, abrimos unas llaves, ponemos 1\2, y, cerramos las llaves.
=ELEGIR({1\2};FIN.MES(--Ventas[Fecha];{-1\0});Ventas[Provincia])
Pero, nos devuelve la primera columna de fechas, y, la columna de provincia.
Bien, el truco esta en repetir el valor 1 dentro de la constante de matriz.
=ELEGIR({1\1\2};FIN.MES(--Ventas[Fecha];{-1\0});Ventas[Provincia])
Ahora, tenemos las tres columnas.
Nos quedamos con los valores únicos.
=UNICOS(ELEGIR({1\1\2};FIN.MES(--Ventas[Fecha];{-1\0});Ventas[Provincia]))
Ya tenemos el modelo preparado para calcular el total por fecha, y, provincia, para ello, voy a usar la función LET, que la pongo después del signo igual, creo una variable, donde quedara almacenada la expresión anterior.
=LET(
a;
UNICOS(ELEGIR({1\1\2};FIN.MES(--Ventas[Fecha];{-1\0});Ventas[Provincia]))
Punto y coma, creamos otra variable, y, almacenamos la columna de fecha.
=LET(
a;
UNICOS(ELEGIR({1\1\2};FIN.MES(--Ventas[Fecha];{-1\0});Ventas[Provincia]));
b;
Ventas[Fecha]
Punto y coma, creamos otra variable, donde almacenamos la columna provincia.
=LET(
a;
UNICOS(ELEGIR({1\1\2};FIN.MES(--Ventas[Fecha];{-1\0});Ventas[Provincia]));
b;
Ventas[Fecha];
c;
Ventas[Provincia]
Punto y coma, creamos una ultima variable, donde almacenamos la columna total.
=LET(
a;
UNICOS(ELEGIR({1\1\2};FIN.MES(--Ventas[Fecha];{-1\0});Ventas[Provincia]));
b;
Ventas[Fecha];
c;
Ventas[Provincia];
d;
Ventas[Total]
Ahora, vamos a usar la función SUMAR.SI.CONUNTO para calcular el total, para ello, ponemos punto y coma, y, la función.
=LET(
a;
UNICOS(ELEGIR({1\1\2};FIN.MES(--Ventas[Fecha];{-1\0});Ventas[Provincia]));
b;
Ventas[Fecha];
c;
Ventas[Provincia];
d;
Ventas[Total];
SUMAR.SI.CONJUNTO(
Como argumento rango de suma, ponemos la variable d.
=LET(
a;
UNICOS(ELEGIR({1\1\2};FIN.MES(--Ventas[Fecha];{-1\0});Ventas[Provincia]));
b;
Ventas[Fecha];
c;
Ventas[Provincia];
d;
Ventas[Total];
SUMAR.SI.CONJUNTO(d
Punto y coma, como argumento rango de criterios1, ponemos la variable c, y, como argumento criterios1, de la matriz desbordada, debemos de compararla con la columna 3, por lo que voy a usar la función INDICE, donde como argumento matriz, ponemos la variable a.
=LET(
a;
UNICOS(ELEGIR({1\1\2};FIN.MES(--Ventas[Fecha];{-1\0});Ventas[Provincia]));
b;
Ventas[Fecha];
c;
Ventas[Provincia];
d;
Ventas[Total];
SUMAR.SI.CONJUNTO(d;c;INDICE(a
Punto y coma, salto el argumento numero de fila, y, como argumento numero de columna ponemos 3, y, cerramos paréntesis.
=LET(
a;
UNICOS(ELEGIR({1\1\2};FIN.MES(--Ventas[Fecha];{-1\0});Ventas[Provincia]));
b;
Ventas[Fecha];
c;
Ventas[Provincia];
d;
Ventas[Total];
SUMAR.SI.CONJUNTO(d;c;INDICE(a;;3)
Punto y coma, como argumento rango de criterios2, ponemos la variable b, punto y coma, como argumento criterios2, entre comillas dobles, ponemos el símbolo de mayor, y, lo concatenamos, en este caso, con la primera columna por lo que volvemos a usar la función INDICE.
=LET(
a;
UNICOS(ELEGIR({1\1\2};FIN.MES(--Ventas[Fecha];{-1\0});Ventas[Provincia]));
b;
Ventas[Fecha];
c;
Ventas[Provincia];
d;
Ventas[Total];
SUMAR.SI.CONJUNTO(d;c;INDICE(a;;3);b;">"&INDICE(a;;1)
Punto y coma, como argumento rango de criterios3, volvemos a poner la variable b, y, como argumento criterios3, entre comillas dobles, ponemos “<=”, y, concatenamos, en este caso, vamos a comparar con la segunda columna, por lo que volvemos a usar la función INDICE, para obtener la columna 2.
=LET(
a;
UNICOS(ELEGIR({1\1\2};FIN.MES(--Ventas[Fecha];{-1\0});Ventas[Provincia]));
b;
Ventas[Fecha];
c;
Ventas[Provincia];
d;
Ventas[Total];
SUMAR.SI.CONJUNTO(d;c;INDICE(a;;3);b;">"&INDICE(a;;1);b;"<="&INDICE(a;;2)
Cerramos paréntesis, aceptamos, y, ya tenemos el total por provincia, y, fecha.
=LET(
a;
UNICOS(ELEGIR({1\1\2};FIN.MES(--Ventas[Fecha];{-1\0});Ventas[Provincia]));
b;
Ventas[Fecha];
c;
Ventas[Provincia];
d;
Ventas[Total];
SUMAR.SI.CONJUNTO(d;c;INDICE(a;;3);b;">"&INDICE(a;;1);b;"<="&INDICE(a;;2)))
Pero, me gustaría que antes de los totales, apareciera las fechas, y, la provincia, para ello, volvemos a la función, antes de SUMAR.SI,CONJUNTO, ponemos la función ELEGIR, ahora tenemos cuatro columnas, por lo que ponemos una constante de matriz como argumento número de índice, e igual que antes, repetimos el numero 1 tres veces, y, después ponemos el numero 2.
=LET(
a;
UNICOS(ELEGIR({1\1\2};FIN.MES(--Ventas[Fecha];{-1\0});Ventas[Provincia]));
b;
Ventas[Fecha];
c;
Ventas[Provincia];
d;
Ventas[Total];
ELEGIR({1\1\1\2}
Punto y coma, como argumento valor1, ponemos la variable A, y, como argumento valor2, es la función SUMAR.SI.CONJUNTO, y, cerramos paréntesis de ELEGIR.
Aceptamos, y, ahora, tenemos las tres columnas antes de la columna de total.
Comments