Crear modelo en vertical

Jaime Franco Jimenez
22 ago 2023
5 Min. de lectura

Para el siguiente ejemplo, tenemos el siguiente modelo.

Debemos de crear un modelo donde en vertical aparezca la clase a la que pertenece cada alumno, en otra columna deben de aparecer los estudiantes, a continuación, las notas de cada alumno.

Respuesta esperada:

Empecemos…

En la celda A11, ponemos LET, creamos una variable, como valor para la variable, usamos la función ENCONTRAR, como argumento texto buscado, ponemos la celda B1, que indica Estudiante, como argumento dentro del texto, seleccionamos el rango A1:Y1, que son los encabezados.

=LET(a;ENCONTRAR(B1;A1:Y1);a)

Probamos variable.

Obtenemos una matriz desbordada en horizontal con la posición de la letra “E” dentro del encabezado, y, error donde no hay coincidencia.

Seguimos en la variable “a”, preguntamos si el resultado de ENCONTRAR es igual a VERDADERO, el cual no hay que poner la comparación, porque de forma predeterminada, comparara con VERDADERO, en ese caso, que nos devuelva la columna entre A1:J1, en caso contrario, que devuelva un texto en blanco.

=LET(a;SI(ENCONTRAR(B1;A1:Y1);COLUMNA(A1:Y1);"");a)

Obtenemos la columna donde el resultado de ENCONTRAR es igual a VERDADERO, y, error donde no hay coincidencia.

Usamos la función ENFILA, como argumento matriz es la expresión anterior, como argumento ignorar, ponemos 3, es decir, ignoramos blancos y errores.

=LET(a;ENFILA(SI(ENCONTRAR(B1;A1:Y1);COLUMNA(A1:Y1);"");3);a)

Obtenemos las columnas en horizontal.

Tenemos la columna donde se encuentran los alumnos (2,8,14, y, 20), para rescatar los valores de los primeros cuatro alumnos en vertical, para rescatar las notas de estos cuatro alumnos, debemos de rescatar las filas 2,3,4, y, 5, y, las columnas 3,4,5,6, y, 7.

Para los siguientes alumnos, volvemos a rescatar las filas 2,3,4, y 5, y, las columnas 9,10,11,12, y 13, así, para el resto de los alumnos.

Creamos otra variable, donde con la función INDICE, nos vamos a traer el primer valor de la variable “a”.

Probamos variable.

=LET(a;ENFILA(SI(ENCONTRAR(B1;A1:Y1);COLUMNA(A1:Y1);"");3);b;INDICE(a;;1);b)

Obtenemos el valor 2.

Creamos otra variable, que llamaremos estu1, usamos la función INDICE, como argumento matriz, seleccionamos el rango A2:Y5, como argumento numero de fila, usamos una constante de matriz, para ello, abrimos unas llaves, ponemos los valores 1, 2, 3, y, 4, separados por comas, ya que estamos solicitando filas, como argumento numero de columna, usamos la función APILARH, como argumento matriz1, ponemos la variable “b” y sumamos 1, como argumento matriz2, ponemos la variable “b” y sumamos 2, como argumento matriz3, ponemos la variable “b”, y, sumamos 3, como argumento matriz4, ponemos la variable “b” y sumamos 4, y, como argumento matriz5, ponemos la variable “b” y sumamos 5.

Cerramos paréntesis, probamos variable.

=LET(a;ENFILA(SI(ENCONTRAR(B1;A1:Y1);COLUMNA(A1:Y1);"");3);b;INDICE(a;;1);estu1;INDICE(A2:Y5;{1;2;3;4};APILARH(b+1;b+2;b+3;b+4;b+5));estu1)

Tenemos los valores para los cuatro primeros alumnos.

Creamos dos variables más, estas dos variables son la misma expresión que la expresión de la variable “b” y la variable “estu1”.

A la variable “b” la llamamos “c”, y, a la variable “estu1”, la llamamos “estu2”, cambiamos el argumento numero de columna de INDICE de la variable “c” por 2, y, cambiamos el argumento numero de columna de INDICE de la variable “estu2” por la variable “c”.

=LET(a;ENFILA(SI(ENCONTRAR(B1;A1:Y1);COLUMNA(A1:Y1);"");3);b;INDICE(a;;1);estu1;INDICE(A2:Y5;{1;2;3;4};APILARH(b+1;b+2;b+3;b+4;b+5));c;INDICE(a;;2);estu2;INDICE(A2:Y5;{1;2;3;4};APILARH(c+1;c+2;c+3;c+4;b+5));estu2)

Probamos variable, y, tenemos los valores para los siguientes cuatro alumnos.

Pero, solo tenemos dos alumnos, creamos otra variable, la llamaremos filtro_estu2, y, filtramos la variable “estu2”, siempre que dicha variable sea diferente a cero.

Creamos otras dos variables, y, tenemos que realizar los mismos cambios que para “estu2”, pero, en este caso por 3 el argumento numero de columna de INDICE de la variable “d”, y, del argumento numero de columna de INDICE de la variable “estu3” por la variable “d”.

Probamos variable.

=LET(a;ENFILA(SI(ENCONTRAR(B1;A1:Y1);COLUMNA(A1:Y1);"");3);b;INDICE(a;;1);estu1;INDICE(A2:Y7;{1;2;3;4};APILARH(b+1;b+2;b+3;b+4;b+5));c;INDICE(a;;2);estu2;INDICE(A2:Y7;{1;2;3;4};APILARH(c+1;c+2;c+3;c+4;b+5));filtro_estu2;FILTRAR(estu2;INDICE(estu2;;1)<>0);d;INDICE(a;;3);estu3;INDICE(A2:Y7;{1;2;3;4};APILARH(d+1;d+2;d+3;d+4;d+5));estu3)

Tenemos las notas para el tercer rango de estudiantes.

Vemos que hay algunos valores que son ceros, pues, lo quitamos igual que antes, creamos otra variable, la llamaremos filtro_estu3, usamos la misma función FILTRAR que para la variable “estu2”, pero, cambiamos “estu2” por “estu3”.

Para el cuarto grupo de estudiantes, repetimos las variables “d”, “estu3” y “filtro_estu3”, realizando los siguientes cambios.

Ya tenemos los valores de cada estudiante.

Vamos a crear otra variable, la llamaremos “estudiantes”, una de las cosas que debemos de hacer es poner la clase de cada estudiante, los estudiantes de la fila 2, le corresponden la clase 9, a los estudiantes de la fila 3, le corresponden la clase 10, a los estudiantes de la fila 4, le corresponden la clase 11, y, a los estudiantes de la fila 5, le corresponde la clase 12, como valor para dicha variable, usamos la función ESTEXTO, como argumento seleccionamos el rango B2:Y5, probamos variable.

Obtenemos una matriz desbordada con VERDADERO donde es texto, y, FALSO donde no lo es.

Preguntamos que, si el resultado de ESTEXTO es VERDADERO, que nos devuelva el rango A2:A5, concatenado con un espacio, concatenado con el rango B2:Y5, en caso de no coincidencia, que nos devuelva un error, probamos variable.

Tenemos una matriz desbordada con la clase y estudiante donde es VERDADERO, y, error donde no hay coincidencia.

Transponemos.

Antes de TRANSPONER, ponemos la función ENCOL, como argumento array, es la función TRANSPONER, como argumento ignorar, ponemos 3, es decir, ignoramos blancos y errores.

Ya tenemos la clase y alumno.

Creamos otra variable, la llamaremos “notas”, usamos la función APILARV, como argumento matriz1, es la variable “estu1”, como argumento matriz2, es la variable “filtro_estu2”, como argumento matriz3, es la variable “filtro_estu3”, y, como argumento matriz4, ponemos la variable “filtro_estu4”.

Probamos variable.

Tenemos las notas de cada alumno.

Lo siguiente es dividir la columna anterior, en una columna vamos a tener solo las clases, y, en otra columna, los alumnos, creamos otra variable, la llamaremos “orden”, usamos la función ENCONTRAR, como argumento texto buscado, ponemos un espacio, como argumento dentro del texto, ponemos la variable “estudiantes”, probamos variable.

=LET(a;ENFILA(SI(ENCONTRAR(B1;A1:Y1);COLUMNA(A1:Y1);"");3);b;INDICE(a;;1);estu1;INDICE(A2:Y5;{1;2;3;4};APILARH(b+1;b+2;b+3;b+4;b+5));c;INDICE(a;;2);estu2;INDICE(A2:Y5;{1;2;3;4};APILARH(c+1;c+2;c+3;c+4;b+5));filtro_estu2;FILTRAR(estu2;INDICE(estu2;;1)<>0);d;INDICE(a;;3);estu3;INDICE(A2:Y5;{1;2;3;4};APILARH(d+1;d+2;d+3;d+4;d+5));filtro_estu3;FILTRAR(estu3;INDICE(estu3;;1)<>0);e;INDICE(a;;4);estu4;INDICE(A2:Y5;{1;2;3;4};APILARH(e+1;e+2;e+3;e+4;e+5));filtro_estu4;FILTRAR(estu4;INDICE(estu4;;1)<>0);estudiantes;ENCOL(TRANSPONER(SI(ESTEXTO(B2:Y5);A2:A5&" "&B2:Y5;NOD()));3);notas;APILARV(estu1;filtro_estu2;filtro_estu3;filtro_estu4);estudiante_maximo;ENFILA(SI(MAX(BYROW(notas;LAMBDA(x;K.ESIMO.MAYOR(x;1))))=notas;estudiantes;NOD());3);orden;ENCONTRAR(" ";estudiantes);orden)

Obtenemos la posición del espacio de cada cadena, restamos 1.

Usamos la función EXTRAE, como argumento texto, ponemos la variable “estudiantes”, como argumento numero de posición ponemos 1, como argumento numero de caracteres, es la función ENCONTRAR, como el resultado nos lo devuelve en formato de texto, antes de EXTRAE, ponemos la función ABS.

Ya tenemos la clase de cada estudiante.

Ahora, nos vamos a extraer los estudiantes, creamos otra variable, la llamaremos orden_estu, volvemos a usar la función EXTRAE, como argumento texto, ponemos la variable “estudiantes”, como argumento posición inicial, ponemos la función ENCONTRAR, como argumento texto buscado, ponemos un espacio, como argumento dentro del texto, ponemos la variable “estudiantes”, cerramos paréntesis de ENCONTRAR, y, sumamos 1, como argumento numero de caracteres, usamos la función LARGO como argumento de LARGO, ponemos la variable “estudiantes”, restamos a la función ENCONTRAR, como argumento texto buscado, ponemos un blanco, como argumento dentro del texto, ponemos la variable “estudiantes”, cerramos paréntesis, estamos extrayendo el nombre a partir del espacio.

Tenemos los estudiantes.

Creamos otra variable, la llamaremos resultado, usamos la función APILARH, como argumento matriz1, ponemos la variable “orden”, como argumento matriz2, ponemos la variable “orden_estu”, como argumento matriz2, ponemos la variable “notas”, cerramos paréntesis.

Usamos la función ORDENAR, como argumento matriz es la función APILARH, como argumento ordenar índice, ponemos 1, es decir, que ordene el modelo por la primera columna.

Probamos variable.

Pues, ya tenemos nuestro ejemplo resuelto.

Miguel Angel Franco Garcia

¿ Listo para empezar a usar Microsoft Excel?

Crear modelo en vertical

Entradas recientes

Comentarios