18 dic 20233 Min. de lectura

3 productos + vendidos por años

Tenemos el modelo que suelo usar para muchos ejemplos.

Debemos de crear un reporte, donde aparezcan para cada año, los tres productos más vendidos, en dicho informe debe de aparecer los años y productos, después, crearemos el mismo reporte, pero debe de aparecer todos los encabezados del modelo.

El modelo está en formato de tabla.

En una celda, usamos LET, creamos una variable, usamos ENFILA, como argumento matriz, usamos UNICOS, como argumento matriz de UNICOS, usamos AÑO, como argumento número de serie, seleccionamos la columna de fecha, y, probamos variable.

=LET(a;ENFILA(UNICOS(AÑO(Ventas[Fecha])));a)

Tenemos los años únicos en horizontal.

Creamos otra variable, usamos REDUCE, como valor inicial, ponemos blanco, como argumento array, ponemos la variable “a”, como argumento función ponemos LAMBDA, y, declaramos dos variables.

=LET(a;ENFILA(UNICOS(AÑO(Ventas[Fecha])));b;REDUCE("";a;LAMBDA(x;y

Como argumento calculo de LAMBDA, usamos APILARH, como argumento matriz1, ponemos la variable “x”, como argumento matriz2, ponemos K.ESIMO.MAYOR, como argumento matriz, usamos FILTRAR, como argumento array, seleccionamos la columna de total, como argumento Include, usamos la funcion AÑO, como argumento numero de serie, seleccionamos la columna de fecha e igualamos a la variable “y”, como argumento K de K.ESIMO.MAYOR, usamos una constante de matriz, por lo que abrimos unas llaves, ponemos los números 1, 2, y, 3, separados por punto y coma.

Con la funcion FILTRAR, obtenemos el reporte filtrado por los totales para cada año, y, con la funcion K.ESIMO.MAYOR, obtenemos los tres primeros valores mayores.

=LET(a;ENFILA(UNICOS(AÑO(Ventas[Fecha])));b;REDUCE("";a;LAMBDA(x;y;APILARH(x;K.ESIMO.MAYOR(FILTRAR(Ventas[Total];AÑO(Ventas[Fecha])=y);{1;2;3}))));b)

Obtenemos una columna de más, que es el argumento valor inicial, pues, antes de REDUCE usamos EXCLUIR, como argumento matriz es la función REDUCE, omitimos el argumento filas, como argumento columnas, ponemos 1.

=LET(a;ENFILA(UNICOS(AÑO(Ventas[Fecha])));b;EXCLUIR(REDUCE("";a;LAMBDA(x;y;APILARH(x;K.ESIMO.MAYOR(FILTRAR(Ventas[Total];AÑO(Ventas[Fecha])=y);{1;2;3}))));;1);b)

Creamos otra variable, usamos ENCOL, como argumento, ponemos TRANSPONER, y, como argumento matriz de TRANSONER, ponemos la variable “b”.

=LET(a;ENFILA(UNICOS(AÑO(Ventas[Fecha])));b;EXCLUIR(REDUCE("";a;LAMBDA(x;y;APILARH(x;K.ESIMO.MAYOR(FILTRAR(Ventas[Total];AÑO(Ventas[Fecha])=y);{1;2;3}))));;1);c;ENCOL(TRANSPONER(b));c)

Vamos a obtener las cantidades por años en vertical.

Creamos otra variable, usamos, de nuevo, REDUCE, como valor inicial, ponemos blanco, como argumento array, ponemos la variable “a”, como argumento funcion, ponemos LAMBDA, y, declaramos dos variables.

Como argumento cálculo de LAMBDA, usamos APILARV, como argumento matriz1, ponemos la variable “x”, como argumento matriz2, usamos el condicional SI, como argumento prueba lógica, preguntamos si la variable “c” es igual a la variable “b”, en ese caso (Valor si verdadero), que nos devuelva la variable “y”, ignoramos el argumento valor si falso, porque siempre habrá coincidencia.

Vamos a obtener una matriz desbordada de cinco columnas, una por año, donde en la primera columna vemos el número de años para cada cantidad.

Nos vamos a quedar con la primera columna, para ello, usamos TOMAR, como argumento matriz es la funcion REDUCE, omitimos el argumento filas, como argumento columnas, ponemos 1.

Usamos ENCOL, como argumento matriz es la funcion TOMAR, como argumento ignorar, seleccionamos 3.

Usamos EXCLUIR para quitar la celda en blanco añadida por el argumento valor inicial de REDUCE, como argumento matriz, es la función ENCOL, como argumento filas, ponemos 1.

Ya tenemos los años para cada cantidad.

Creamos otra variable, concatenamos la variable “d” con la variable “c”.

Tenemos cada año y cada cantidad unidas.

Creamos otra variable, concatenamos el año de la columna fecha con la columna de total.

Creamos otra variable, usamos una constante de matriz, abrimos unas llaves, entre comillas dobles, ponemos Años, ponemos barra inclinada, entre comillas dobles, ponemos Productos.

=LET(a;ENFILA(UNICOS(AÑO(Ventas[Fecha])));b;EXCLUIR(REDUCE("";a;LAMBDA(x;y;APILARH(x;K.ESIMO.MAYOR(FILTRAR(Ventas[Total];AÑO(Ventas[Fecha])=y);{1;2;3}))));;1);c;ENCOL(TRANSPONER(b));d;EXCLUIR(ENCOL(TOMAR(REDUCE("";a;LAMBDA(x;y;APILARV(x;SI(c=b;y))));;1);3);1);e;d&c;f;AÑO(Ventas[Fecha])&Ventas[Total];g;{"Años"\"Productos"};g)

Creamos otra variable, usamos BUSCARX, como argumento valor buscado, ponemos la variable “e”, como argumento matriz de búsqueda, ponemos la variable “f”, como argumento matriz devuelta, seleccionamos la columna de producto.

Ya tenemos los tres productos más vendidos para cada año.

Usamos APILARH, como argumento matriz1, ponemos la variable “d”, como argumento matriz2, es la funcion BUSCARX.

Tenemos el año para cada producto.

Usamos el argumento cálculo de LET, usamos APILARV, como argumento matriz1, ponemos la variable “g”, como argumento matriz2, ponemos la variable “h”.

Aceptamos, y, ya lo tenemos.

Pero, si queremos que nos devuelva el modelo con las filas coincidentes, debemos de usar, de nuevo, REDUCE, para trabajar con cada año y total unidos, quitamos el argumento cálculo de LET, declaramos una variable, ponemos REDUCE, como argumento valor inicial, ponemos blanco, como argumento array, ponemos la variable “e”, como argumento función, ponemos LAMBDA, y, declaramos dos variables.

Como argumento calculo de LAMBDA, ponemos APILARV, como argumento matriz1, poneos la variable “x”, como argumento matriz2, usamos FILTRAR, como argumento array, ponemos Ventas, como argumento Include, ponemos la variable “f” e igualamos a la variable “y”.

Pues, ya lo tenemos.

Quitamos la primera fila con la función EXCLUIR.

Usamos el argumento cálculo de LET, ponemos APILARV, como argumento matriz1, nos traemos el encabezado del modelo, como argumento matriz2, es la variable “i”.

Ya tenemos nuestro ejercicio resuelto.

Miguel Angel Franco

¿ Listo para empezar a usar Microsoft Excel?

3 productos + vendidos por años

Entradas recientes

Comments