Enumerar las 3 mejores calificaciones

Para el siguiente ejemplo, tenemos una seria de alumnos en vertical, en horizontal, asignaturas, y, una serie de notas por alumno y asignatura.

Debemos de enumerar las 3 mejores calificaciones, los nombres, y, asignaturas.

Ordenamos por las marcas de manera descendente.

Empecemos…

En la celda G2, usamos LET, creamos una variable, usamos la funcion FILAS, como argumento seleccionamos el rango A2:A10.

Probamos variable.

=LET(a;FILAS(A2:A10);a)

Nos devuelve 9.

Creamos otra variable, usamos la funcion COLUMNAS, como argumento seleccionamos el rango B1:E1.

=LET(a;FILAS(A2:A10);b;COLUMNAS(B1:E1);b)

Nos devuelve 4.

Creamos otra variable, usamos la funcion ENCOL, como argumento matriz, seleccionamos el rango B2:E10.

=LET(a;FILAS(A2:A10);b;COLUMNAS(B1:E1);c;ENCOL(B2:E10);c)

Obtenemos las marcas en una columna.

Creamos otra variable, usamos la funcion SECUENCIA, como argumento filas, ponemos la variable “b”, como argumento columnas, ponemos la variable “a”, omitimos el argumento inicio, como argumento paso, ponemos dividimos 1 entre la variable “a”, dicha división nos devuelve 0,1.

=LET(a;FILAS(A2:A10);b;COLUMNAS(B1:E1);c;ENCOL(B2:E10);d;SECUENCIA(b;a;;1/a);d)

Tenemos una matriz desbordada de 9 columnas, y cuatro filas con los siguientes valores.

Antes de la funcion SECUENCIA, usamos la funcion REDONDEAR.MENOS, como argumento numero es la funcion SECUENCIA, como argumento numero de decimales, ponemos 0.

=LET(a;FILAS(A2:A10);b;COLUMNAS(B1:E1);c;ENCOL(B2:E10);d;REDONDEAR.MENOS(SECUENCIA(b;a;;1/a);0);d)

Ahora, aparece en cada columna los números 1, 2, 3, y, 4.

Antes de REDONDEAR.MENOS, entre comillas dobles, ponemos Asignatura, y, dejamos un espacio, y, concatenamos con la funcion REDONDEAR.MENOS.

Usamos ENCOL.

=LET(a;FILAS(A2:A10);b;COLUMNAS(B1:E1);c;ENCOL(B2:E10);d;ENCOL("Asignatura "&REDONDEAR.MENOS(SECUENCIA(b;a;;1/a);0));d)

Tenemos en una columna, repetido 9 veces cada asignatura.

Creamos otra variable, volvemos a usar REDONDEAR.MENOS, como argumento número, ponemos la funcion SECUENCIA, como argumento filas, multiplicamos la variable “a” por la variable “b”, que devuelve 36, omitimos los argumentos columnas, e, inicio, como argumento paso, volvemos a poner la división de 1 entre la variable “a”, como argumento numero de decimales, ponemos 0.

=LET(a;FILAS(A2:A10);b;COLUMNAS(B1:E1);c;ENCOL(B2:E10);d;ENCOL("Asignatura "&REDONDEAR.MENOS(SECUENCIA(4;10;;1/a);0));e;REDONDEAR.MENOS(SECUENCIA(a*b;;;1/a);0);e)

Tenemos una matriz desbordada de una columna con un total de 36 filas.

Usamos la funcion INDICE, como argumento matriz, seleccionamos el rango A2:A10, como argumento numero de fila es la funcion REDONDEAR.MENOS.

=LET(a;FILAS(A2:A10);b;COLUMNAS(B1:E1);c;ENCOL(B2:E10);d;ENCOL("Asignatura "&REDONDEAR.MENOS(SECUENCIA(4;10;;1/a);0));e;INDICE(A2:A10;REDONDEAR.MENOS(SECUENCIA(a*b;;;1/a);0));e)

Tenemos cada nombre repetido 9 veces.

Creamos otra variable, usamos la funcion APILARH, como argumento matriz1, ponemos la variable “e”, como argumento matriz2, ponemos la variable “d”, como argumento matriz3, ponemos la variable “c”.

=LET(a;FILAS(A2:A10);b;COLUMNAS(B1:E1);c;ENCOL(B2:E10);d;ENCOL("Asignatura "&REDONDEAR.MENOS(SECUENCIA(4;10;;1/a);0));e;INDICE(A2:A10;REDONDEAR.MENOS(SECUENCIA(a*b;;;1/a);0));f;APILARH(e;d;c);f)

Ya tenemos el modelo formado para poder formar el modelo con las tres notas mayores.

Creamos otra variable, nos traemos los valores únicos de la variable “c”.

=LET(a;FILAS(A2:A10);b;COLUMNAS(B1:E1);c;ENCOL(B2:E10);d;ENCOL("Asignatura "&REDONDEAR.MENOS(SECUENCIA(4;10;;1/a);0));e;INDICE(A2:A10;REDONDEAR.MENOS(SECUENCIA(a*b;;;1/a);0));f;APILARH(e;d;c);g;UNICOS(c );g)

Creamos otra variable, usamos la funcion K.ESIMO.MAYOR, como argumento matriz, ponemos la variable “g”, como argumento K, usamos una constante de matriz, abrimos unas llaves, ponemos los valores 3, 2, y, 1, cerramos llaves, vamos a obtener el tercer, segundo, y, primer valor mayor.

=LET(a;FILAS(A2:A10);b;COLUMNAS(B1:E1);c;ENCOL(B2:E10);d;ENCOL("Asignatura "&REDONDEAR.MENOS(SECUENCIA(4;10;;1/a);0));e;INDICE(A2:A10;REDONDEAR.MENOS(SECUENCIA(a*b;;;1/a);0));f;APILARH(e;d;c);g;UNICOS(c );h;K.ESIMO.MAYOR(g;{3;2;1});h)

Creamos otra variable, abrimos paréntesis, usamos la funcion INDICE, como argumento matriz ponemos la variable “f”, omitimos el argumento número de fila, como argumento número de columna, ponemos 3, ponemos el símbolo de comparación mayor o igual (>=), ponemos la funcion TOMAR, como argumento matriz, ponemos la variable “h”, como argumento filas, ponemos 1, ignoramos el argumento columnas, cerramos paréntesis.

=LET(a;FILAS(A2:A10);b;COLUMNAS(B1:E1);c;ENCOL(B2:E10);d;ENCOL("Asignatura "&REDONDEAR.MENOS(SECUENCIA(4;10;;0,1);0));e;INDICE(A2:A10;REDONDEAR.MENOS(SECUENCIA(36;;;0,1);0));f;APILARH(e;d;c);g;UNICOS(c);h;K.ESIMO.MAYOR(g;{3;2;1});i;(INDICE(f;;3)>=TOMAR(h;1))

Ponemos el símbolo de asterisco, abrimos otro paréntesis, volvemos a usar la funcion INDICE, como argumento matriz, ponemos la variable “f”, omitimos el argumento número de fila, como argumento numero de columna, ponemos 3, ponemos el símbolo de comparación menor o igual (<=), ponemos la funcion TOMAR, como argumento matriz, ponemos la variable “h”, como argumento filas, ponemos -1, omitimos el argumento columnas, cerramos paréntesis.

Vamos a comparar la columna 3 de la variable “f”, las marcas, siendo mayor o igual al valor mínimo y menor o igual al valor máximo.

=LET(a;FILAS(A2:A10);b;COLUMNAS(B1:E1);c;ENCOL(B2:E10);d;ENCOL("Asignatura "&REDONDEAR.MENOS(SECUENCIA(4;10;;0,1);0));e;INDICE(A2:A10;REDONDEAR.MENOS(SECUENCIA(36;;;0,1);0));f;APILARH(e;d;c);g;UNICOS(c);h;K.ESIMO.MAYOR(g;{3;2;1});i;(INDICE(f;;3)>=TOMAR(h;1))*(INDICE(f;;3)<=TOMAR(h;-1));i)

Tenemos 0 donde no hay coincidencia, y, 1 donde la hay.

Creamos otra variable, usamos el condicional SI, preguntamos si la variable “i” es igual a 1, en ese caso, que devuelva la variable “f”, en caso contrario, que devuelva un error.

=LET(a;FILAS(A2:A10);b;COLUMNAS(B1:E1);c;ENCOL(B2:E10);d;ENCOL("Asignatura "&REDONDEAR.MENOS(SECUENCIA(4;10;;0,1);0));e;INDICE(A2:A10;REDONDEAR.MENOS(SECUENCIA(36;;;0,1);0));f;APILARH(e;d;c);g;UNICOS(c);h;K.ESIMO.MAYOR(g;{3;2;1});i;(INDICE(f;;3)>=TOMAR(h;1))*(INDICE(f;;3)<=TOMAR(h;-1));j;SI(i;f;NOD());j)

Tenemos una matriz con error donde no hay coincidencia, y, el alumno, la asignatura y la nota donde hay coincidencia.

Usamos el argumento cálculo de LET, usamos la funcion FILTRAR, como argumento array, ponemos la variable “j”, como argumento Include, usamos INDICE, como argumento matriz, peguntamos si es error la variable “j”, omitimos el argumento número de fila, como argumento número de columna, ponemos 1 e igualamos a FALSO.

=LET(a;FILAS(A2:A10);b;COLUMNAS(B1:E1);c;ENCOL(B2:E10);d;ENCOL("Asignatura "&REDONDEAR.MENOS(SECUENCIA(4;10;;0,1);0));e;INDICE(A2:A10;REDONDEAR.MENOS(SECUENCIA(36;;;0,1);0));f;APILARH(e;d;c);g;UNICOS(c);h;K.ESIMO.MAYOR(g;{3;2;1});i;(INDICE(f;;3)>=TOMAR(h;1))*(INDICE(f;;3)<=TOMAR(h;-1));j;SI(i;f;NOD());FILTRAR(j;INDICE(ESERROR(j);;1)=FALSO))

Antes de la funcion FILTRAR, ponemos la funcion ORDENAR, como argumento matriz es la funcion FILTRAR, como argumento ordenar índice, ponemos 3, como argumento criterio ordenación, seleccionamos descendente.

=LET(a;FILAS(A2:A10);b;COLUMNAS(B1:E1);c;ENCOL(B2:E10);d;ENCOL("Asignatura "&REDONDEAR.MENOS(SECUENCIA(4;10;;0,1);0));e;INDICE(A2:A10;REDONDEAR.MENOS(SECUENCIA(36;;;0,1);0));f;APILARH(e;d;c);g;UNICOS(c);h;K.ESIMO.MAYOR(g;{3;2;1});i;(INDICE(f;;3)>=TOMAR(h;1))*(INDICE(f;;3)<=TOMAR(h;-1));j;SI(i;f;NOD());ORDENAR(FILTRAR(j;INDICE(ESERROR(j);;1)=FALSO);;-1))

Ya lo tenemos.

Miguel Angel Franco