Tres productos mas vendidos

Jaime Franco Jimenez
2 sept 2023
3 Min. de lectura

Tenemos el modelo que suelo usar habitualmente, pero algo más corto, vemos en la columna cantidad que tenemos diferentes cantidades separadas por comas.

Nos solicitan que calculemos el total de cada fila.

En la celda G2, usamos la función EXTRAE, como argumento texto, seleccionamos el rango E2:E20, que son las cantidades, como argumento posición inicial, usamos la función SECUENCIA, omitimos el argumento filas, como argumento columnas, usamos la función MAX, como argumento numero1, usamos la función LARGO, como argumento texto de LARGO seleccionamos el rango E2:E20, como argumento número de caracteres de EXTRAE, ponemos 1.

=EXTRAE(E2:E20;SECUENCIA(;MAX(LARGO(E2:E20)));1))

Obtenemos una matriz desbordada con cada carácter de cada celda de la columna cantidad.

Usamos la función ABS, que nos devuelve el valor absoluto de un número, si no es número nos devolverá error.

=ABS(EXTRAE(E2:E20;SECUENCIA(;MAX(LARGO(E2:E20)));1))

Usamos la función SI.ERROR, y, ponemos un texto en blanco en caso de error.

=SI.ERROR(ABS(EXTRAE(E2:E20;SECUENCIA(;MAX(LARGO(E2:E20)));1));"")

Ahora, debemos de sumar cada fila de forma independiente, por lo que usamos la función BYROW, como argumento array es la expresión anterior.

=BYROW(SI.ERROR(ABS(EXTRAE(E2:E20;SECUENCIA(;MAX(LARGO(E2:E20)));1));"")

Como argumento función, ponemos LAMBDA, declaramos una variable, como argumento calculo de LAMBDA, sumamos dicha variable.

=BYROW(SI.ERROR(ABS(EXTRAE(E2:E20;SECUENCIA(;MAX(LARGO(E2:E20)));1));"");LAMBDA(x;SUMA(x)))

Obtenemos las sumas de las cantidades para cada fila.

Multiplicamos por la columna precio.

=BYROW(SI.ERROR(ABS(EXTRAE(E2:E20;SECUENCIA(;MAX(LARGO(E2:E20)));1));"");LAMBDA(x;SUMA(x)))*F2:F20

Ya lo tenemos.

Ahora queremos extraer los tres productos que más cantidades vendieron.

La función BYROW es la misma, pero, después del signo igual, usamos LET, creamos una variable, y, almacenamos la función BYROW.

=LET(a;BYROW(SI.ERROR(ABS(EXTRAE(E2:E20;SECUENCIA(;MAX(LARGO(E2:E20)));1));"");LAMBDA(x;SUMA(x)))

Creamos otra variable, para extraer los tres valores mayores, para ello, usamos la función K.ESIMO.MAYOR, como argumento matriz, ponemos la función UNICOS, y, como argumento ponemos la variable “a”.

¿Por qué usamos UNICOS?

Si entre los tres valores mayores hay un numero repetido, aparecerá como uno de los tres valores mayores, de esta forma, nos aseguramos de que los valores devueltos provienen de valores únicos.

=LET(a;BYROW(SI.ERROR(ABS(EXTRAE(E2:E20;SECUENCIA(;MAX(LARGO(E2:E20)));1));"");LAMBDA(x;SUMA(x)));b;K.ESIMO.MAYOR(UNICOS(a)

Como argumento K, usamos una constante de matriz, para ello, abrimos unas llaves, ponemos los valores 1, 2, y, 3, usamos como delimitador el punto y coma, porque los valores a rescatar se encuentran en filas.

=LET(a;BYROW(SI.ERROR(ABS(EXTRAE(E2:E20;SECUENCIA(;MAX(LARGO(E2:E20)));1));"");LAMBDA(x;SUMA(x)));b;K.ESIMO.MAYOR(UNICOS(a);{1;2;3});b)

Obtenemos los tres valores mayores.

Declaramos otra variable, usamos la función APILARH, como argumento matriz1, ponemos la variable “a”, como argumento matriz2, ponemos la variable “b”.

Probamos variable.

=LET(a;BYROW(SI.ERROR(ABS(EXTRAE(E2:E20;SECUENCIA(;MAX(LARGO(E2:E20)));1));"");LAMBDA(x;SUMA(x)));b;K.ESIMO.MAYOR(UNICOS(a);{1;2;3});c;APILARH(a;b);c)

Tenemos una matriz desbordada de dos columnas, en la primera columna, tenemos la suma de las cantidades, en la segunda columna, tenemos los tres valores mayores, en el resto de las celdas, tenemos un error.

Quitamos el error con la función SI.ERROR, y, ponemos un texto en blanco.

=LET(a;BYROW(SI.ERROR(ABS(EXTRAE(E2:E20;SECUENCIA(;MAX(LARGO(E2:E20)));1));"");LAMBDA(x;SUMA(x)));b;K.ESIMO.MAYOR(UNICOS(a);{1;2;3});c;SI.ERROR(APILARH(a;b);"");c)

Declaramos otra variable, la llamaremos resul, abrimos un paréntesis, usamos la función INDICE, como argumento matriz, ponemos la variable “c”, omitimos el argumento numero de fila, como argumento numero de columna, ponemos 1, e, igualamos de nuevo con la función INDICE, como argumento matriz, ponemos la variable “c”, como argumento número de fila, ponemos 1, como argumento número de columna, ponemos 2, es decir, vamos a comparar la suma de todas las cantidades por filas con el primer número mayor.

Obtenemos una matriz desbordada con VERDADERO donde hay coincidencia, y, FALSO donde no la hay.

Volvemos a la expresión, ponemos el símbolo de más (+), que es igual que el operador lógico O, y, comparamos con el segundo valor mayor.

=LET(a;BYROW(SI.ERROR(ABS(EXTRAE(E2:E20;SECUENCIA(;MAX(LARGO(E2:E20)));1));"");LAMBDA(x;SUMA(x)));b;K.ESIMO.MAYOR(UNICOS(a);{1;2;3});c;SI.ERROR(APILARH(a;b);"");result;SI((INDICE(c;;1)=INDICE(c;1;2))+(INDICE(c;;1)=INDICE(c;2;2)

Volvemos a poner el signo de más, y, comparamos con el tercer valor mayor.

Probamos variable.

Obtenemos una matriz desbordada con 1 donde hay coincidencia con no de los valores mayores, y, 0 donde no la hay.

Esta expresión es el argumento prueba lógica del condicional SI que vamos a usar, y, que ponemos delante de la primera condición, como argumento valor si verdadero, seleccionamos el rango D2:D20, que son los productos, como argumento valor si falso, ponemos un texto en blanco.

=LET(a;BYROW(SI.ERROR(ABS(EXTRAE(E2:E20;SECUENCIA(;MAX(LARGO(E2:E20)));1));"");LAMBDA(x;SUMA(x)));b;K.ESIMO.MAYOR(UNICOS(a);{1;2;3});c;SI.ERROR(APILARH(a;b);"");result;SI((INDICE(c;;1)=INDICE(c;1;2))+(INDICE(c;;1)=INDICE(c;2;2)+(INDICE(c;;1)=INDICE(c;3;2)))=1;D2:D20;"");result)

Obtenemos aquellos productos que corresponden a las tres cantidades mas vendidas, y, si alguna cantidad mayor se repite, también aparece, como la cantidad 20.

Usamos el argumento calculo de LET, y, filtramos la variable resul, siempre que dicha variable sea distinta a blanco.

Ya lo tenemos.

Miguel Angel Franco

¿ Listo para empezar a usar Microsoft Excel?

Tres productos mas vendidos

Entradas recientes

Comentarios