Resumen nombre y materia

Tenemos dos columnas, en la primera columna tenemos una serie de nombres, y, en la segunda columna, tenemos las asignaturas que imparten cada nombre.

Tenemos otras dos columnas donde en una primera columna tenemos las materias únicas, y, en la siguiente columna, tenemos la letra que corresponde con cada asignatura.

Debemos de crear un reporte donde en vertical deben de aparecer los nombres únicos, en horizontal, deben de aparecer las materias únicas, y, en el centro, donde deben de aparecer los datos, tiene que aparecer el código que corresponde a cada materia para cada nombre.

En la celda G2, nos traemos los nombres únicos.

=UNICOS(A2:A10)

En la celda H1, en horizontal, nos traemos las materias únicas.

=TRANSPONER(UNICOS(B2:B10))

Ordenamos, después del signo igual, ponemos la funcion ORDENAR, como argumento matriz es la funcion TRANSPONER, omitimos el argumento ordenar índice, omitimos el argumento criterio ordenación, como argumento por_col, seleccionamos ordenar por columnas.

=ORDENAR(TRANSPONER(UNICOS(B2:B10));;;VERDADERO)

En la celda H2, usamos LET, creamos una variable, seleccionamos el rango A2:A10, y, fijamos, creamos otra variable, seleccionamos el rango B2:B10, y, fijamos.

=LET(nombres;$A$2:$A$10;materias;$B$2:$B$10

Creamos otra variable, usamos la misma expresión que hemos usado para la celda H1.

=LET(nombres;$A$2:$A$10;materias;$B$2:$B$10;unimat;ORDENAR(TRANSPONER(UNICOS(materias));;;VERDADERO);unimat)

Creamos otra variable, usamos la funcion MAP, como argumento array, ponemos la variable nombres, como argumento array2, ponemos la variable materias, como argumento funcion, ponemos LAMBDA, y, declaramos dos variables.

=LET(nombres;$A$2:$A$10;materias;$B$2:$B$10;unimat;ORDENAR(TRANSPONER(UNICOS(materias));;;VERDADERO);a;MAP(nombres;materias;LAMBDA(x;y

Como argumento calculo de LAMBDA, usamos la funcion BUSCARX, como argumento valor buscado, usamos la funcion ELEGIRFILAS, como argumento matriz, nos traemos los valores únicos de la variable nombres, como argumento número de fila1, usamos la funcion FILA, y, como argumento ponemos A1, de manera, que, al arrastrar, nos vaya devolviendo el siguiente nombre.

=LET(nombres;$A$2:$A$10;materias;$B$2:$B$10;unimat;ORDENAR(TRANSPONER(UNICOS(materias));;;VERDADERO);a;MAP(nombres;materias;LAMBDA(x;y;BUSCARX(ELEGIRFILAS(UNICOS(nombres)

Como argumento matriz de búsqueda, ponemos la variable “x”, como argumento matriz devuelta, ponemos la variable “y”, cerramos paréntesis.

=LET(nombres;$A$2:$A$10;materias;$B$2:$B$10;unimat;ORDENAR(TRANSPONER(UNICOS(materias));;;VERDADERO);a;MAP(nombres;materias;LAMBDA(x;y;BUSCARX(ELEGIRFILAS(UNICOS(nombres);FILA(A1));x;y)));a)

Tenemos una matriz desbordada en vertical donde tenemos las materias que imparte el primer nombre, y, error donde no hay coincidencia.

Usamos la funcion ENFILA, como argumento matriz es la funcion MAP, como argumento ignorar, seleccionamos 3, ignorar blancos y errores.

=LET(nombres;$A$2:$A$10;materias;$B$2:$B$10;unimat;ORDENAR(TRANSPONER(UNICOS(materias));;;VERDADERO);a;ENFILA(MAP(nombres;materias;LAMBDA(x;y;BUSCARX(ELEGIRFILAS(UNICOS(nombres);FILA(A1));x;y)));3);a)

Ordenamos por columnas, como lo hemos hecho antes.

=LET(nombres;$A$2:$A$10;materias;$B$2:$B$10;unimat;ORDENAR(TRANSPONER(UNICOS(materias));;;VERDADERO);a;ORDENAR(ENFILA(MAP(nombres;materias;LAMBDA(x;y;BUSCARX(ELEGIRFILAS(UNICOS(nombres);FILA(A1));x;y)));3);;;VERDADERO);a)

Creamos otra variable, usamos la funcion REDUCE, como argumento valor inicial, ponemos blanco, como argumento array, ponemos la variable unimat, como argumento funcion, ponemos LAMBDA, y, declaramos dos variables.

=LET(nombres;$A$2:$A$10;materias;$B$2:$B$10;unimat;ORDENAR(TRANSPONER(UNICOS(materias));;;VERDADERO);a;ORDENAR(ENFILA(MAP(nombres;materias;LAMBDA(x;y;BUSCARX(ELEGIRFILAS(UNICOS(nombres);FILA(A1));x;y)));3);;;VERDADERO);b;REDUCE("";unimat;LAMBDA(x;y

Como argumento calculo de LAMBDA, usamos la funcion APILARH, como argumento matriz1, ponemos la variable “x”, como argumento matriz2, usamos el condicional SI, como argumento prueba lógica, preguntamos si la variable “y” es igual a la variable “a” pero en vertical, por lo que usamos la funcion ENCOL, como argumento valor si verdadero, ponemos la variable “y”, como argumento valor si falso, ponemos blanco.

=LET(nombres;$A$2:$A$10;materias;$B$2:$B$10;unimat;ORDENAR(TRANSPONER(UNICOS(materias));;;VERDADERO);a;ORDENAR(ENFILA(MAP(nombres;materias;LAMBDA(x;y;BUSCARX(ELEGIRFILAS(UNICOS(nombres);FILA(A1));x;y)));3);;;VERDADERO);b;REDUCE("";unimat;LAMBDA(x;y;APILARH(x;SI(y=ENCOL(a);y;""))));b)

Vamos a obtener las materias que imparte el primer nombre.

La primera columna está de más, por lo que usamos la funcion EXCLUIR, como argumento matriz, es la funcion REDUCE, omitimos el argumento filas, como argumento columnas, ponemos 1.

=LET(nombres;$A$2:$A$10;materias;$B$2:$B$10;unimat;ORDENAR(TRANSPONER(UNICOS(materias));;;VERDADERO);a;ORDENAR(ENFILA(MAP(nombres;materias;LAMBDA(x;y;BUSCARX(ELEGIRFILAS(UNICOS(nombres);FILA(A1));x;y)));3);;;VERDADERO);b;EXCLUIR(REDUCE("";unimat;LAMBDA(x;y;APILARH(x;SI(y=ENCOL(a);y;""))));;1);b)

Creamos otra variable, usamos la funcion BYCOL, como argumento array, ponemos la variable “b”, como argumento funcion, ponemos LAMBDA, declaramos una variable, como argumento calculo de LAMBDA, usamos la funcion FILTRAR, como argumento array, ponemos la variable “x”, como argumento Include, ponemos la variable “x” y comparamos con desigual a blanco.

=LET(nombres;$A$2:$A$10;materias;$B$2:$B$10;unimat;ORDENAR(TRANSPONER(UNICOS(materias));;;VERDADERO);a;ORDENAR(ENFILA(MAP(nombres;materias;LAMBDA(x;y;BUSCARX(ELEGIRFILAS(UNICOS(nombres);FILA(A1));x;y)));3);;;VERDADERO);b;EXCLUIR(REDUCE("";unimat;LAMBDA(x;y;APILARH(x;SI(y=ENCOL(a);y;""))));;1);c;BYCOL(b;LAMBDA(x;FILTRAR(x;x<>"")));c)

Tenemos en horizontal las materias para el primer nombre, y, error donde no hay materia que imparte dicho profesor.

Usamos el argumento si vacío de FILTRAR, y, ponemos blanco.

=LET(nombres;$A$2:$A$10;materias;$B$2:$B$10;unimat;ORDENAR(TRANSPONER(UNICOS(materias));;;VERDADERO);a;ORDENAR(ENFILA(MAP(nombres;materias;LAMBDA(x;y;BUSCARX(ELEGIRFILAS(UNICOS(nombres);FILA(A1));x;y)));3);;;VERDADERO);b;EXCLUIR(REDUCE("";unimat;LAMBDA(x;y;APILARH(x;SI(y=ENCOL(a);y;""))));;1);c;BYCOL(b;LAMBDA(x;FILTRAR(x;x<>"";"")));c)

Como argumento calculo de LET, usamos la funcion BUSCARX, como argumento valor buscado, ponemos la variable “c”, como argumento matriz de búsqueda, seleccionamos el rango D2:D5, y, fijamos, como argumento matriz devuelta, ponemos el rango E2:E5, y, fijamos.

=LET(nombres;$A$2:$A$10;materias;$B$2:$B$10;unimat;ORDENAR(TRANSPONER(UNICOS(materias));;;VERDADERO);a;ORDENAR(ENFILA(MAP(nombres;materias;LAMBDA(x;y;BUSCARX(ELEGIRFILAS(UNICOS(nombres);FILA(A1));x;y)));3);;;VERDADERO);b;EXCLUIR(REDUCE("";unimat;LAMBDA(x;y;APILARH(x;SI(y=ENCOL(a);y;""))));;1);c;BYCOL(b;LAMBDA(x;FILTRAR(x;x<>"";"")));BUSCARX(c;$D$2:$D$5;$E$2:$E$5;""))

Aceptamos, arrastramos, y, lo tenemos.

Ahora lo vamos a realizar de forma matricial, pero, en vez de que aparezcan los resultados como en el ejemplo anterior, deben de aparecer los nombres y únicos, y, los códigos de profesores que imparten una materia, unido, y, separados por comas.

En la celda G1, usamos LET, creamos una variable, seleccionamos el rango B2:B10, creamos otra variable, seleccionamos el rango A2:A10.

=LET(a;B2:B10;b;A2:A10

Creamos otra variable, usamos la funcion REDUCE, como argumento valor inicial, ponemos blanco, como argumento array, nos traemos los valores únicos de la variable “b”, y, lo ponemos en horizontal, para ello, usamos la funcion ENFILA, como argumento funcion, ponemos LAMBDA, y, declaramos dos variables.

=LET(a;B2:B10;b;A2:A10;c;REDUCE("";ENFILA(UNICOS(b));LAMBDA(x;y

Como argumento calculo de LAMBDA, usamos la funcion APILARH, como argumento matriz1, ponemos la variable “x”, como argumento matriz2, usamos la funcion FILTRAR, como argumento array, ponemos la variable “a”, como argumento Include, ponemos la variable “b”, e, igualamos a la variable “y”.

=LET(a;B2:B10;b;A2:A10;c;REDUCE("";ENFILA(UNICOS(b));LAMBDA(x;y;APILARH(x;FILTRAR(a;b=y))));c)

Tenemos una matriz desbordada de seis columnas, donde la primera columna sobra, y, después tenemos las materias que imparten cada nombre, y, error donde no tienen materia que impartir.

Usamos la funcion EXCLUIR, como argumento matriz es la funcion REDUCE, omitimos el argumento filas, como argumento columnas, ponemos 1.

=LET(a;B2:B10;b;A2:A10;c;EXCLUIR(REDUCE("";ENFILA(UNICOS(b));LAMBDA(x;y;APILARH(x;FILTRAR(a;b=y))));;1);c)

Creamos ora variable, y, transponemos la variable “c”.

=LET(a;B2:B10;b;A2:A10;c;SI.ERROR(EXCLUIR(REDUCE("";ENFILA(UNICOS(b));LAMBDA(x;y;APILARH(x;FILTRAR(a;b=y))));;1);"");d;TRANSPONER(c );d)

Creamos otra variable, usamos la funcion BYROW, como argumento array, usamos la funcion BUSCARX, como argumento valor buscado, ponemos la variable “d”, como argumento matriz de búsqueda, seleccionamos el rango D2:D5, como argumento matriz devuelta, seleccionamos el rango E2:E5, como argumento funcion, ponemos LAMBDA, y, declaramos una variable.

=LET(a;B2:B10;b;A2:A10;c;SI.ERROR(EXCLUIR(REDUCE("";ENFILA(UNICOS(b));LAMBDA(x;y;APILARH(x;FILTRAR(a;b=y))));;1);"");d;TRANSPONER(c );e;BYROW(BUSCARX(d;D2:D5;E2:E5;"");LAMBDA(x

Como argumento calculo de LAMBDA, usamos la funcion UNIRCADENAS, como argumento delimitador, entre comillas dobles ponemos coma, y, dejamos un espacio, ignoramos celdas vacías, como argumento texto1, ponemos la variable “x”.

Cerramos paréntesis, probamos variable.

=LET(a;B2:B10;b;A2:A10;c;SI.ERROR(EXCLUIR(REDUCE("";ENFILA(UNICOS(b));LAMBDA(x;y;APILARH(x;FILTRAR(a;b=y))));;1);"");d;TRANSPONER(c );e;BYROW(BUSCARX(d;D2:D5;E2:E5;"");LAMBDA(x;UNIRCADENAS(", ";VERDADERO;x)));e)

Tenemos los códigos de materias para cada nombre.

Como argumento calculo de LET, usamos APILARV, como argumento matriz1, usamos una constante de matriz, abrimos unas llaves, entre comillas dobles, ponemos Nombres, ponemos la barra inclinada, entre comillas dobles, ponemos Profesores, cerramos llaves, ponemos coma, como argumento matriz2, usamos APILARH, como argumento matriz1, usamos UNICOS, como argumento ponemos la variable “b”, como argumento matriz2, ponemos la variable “e”.

Vamos a obtener el encabezado en horizontal, en vertical, vamos a tener los nombres, y, los códigos.

=LET(a;B2:B10;b;A2:A10;c;SI.ERROR(EXCLUIR(REDUCE("";ENFILA(UNICOS(b));LAMBDA(x;y;APILARH(x;FILTRAR(a;b=y))));;1);"");d;TRANSPONER(c );e;BYROW(BUSCARX(d;D2:D5;E2:E5;"");LAMBDA(x;UNIRCADENAS(", ";VERDADERO;x)));APILARV({"Nombres"\"profesores"};APILARH(UNICOS(b);e)))

Ya lo tenemos.

Ahora, lo vamos a realizar, también de forma matricial, pero para obtener el resultado del primer ejemplo.

La sintaxis va a quedar mucho más reducida.

En una celda usamos la funcion PIVOTARPOR, como argumento row fields, es decir, lo que debe de aparecer en vertical, seleccionamos el rango A2:A10, como argumento col fields, lo que debe de aparecer en horizontal, seleccionamos el rango B2:B10, como argumento values, usamos BUSCARX, como argumento valor buscado, seleccionamos el rango B2:B10, como argumento matriz de búsqueda, seleccionamos el rango D2:D5, como argumento matriz devuelta, seleccionamos el rango E2:E5, como argumento funcion, ponemos CONCAT, omitimos el argumento field headers, como argumento row total Depth, ponemos cero, omitimos el argumento row sort order, como argumento col total Depth, ponemos 0.

Cerramos paréntesis.

=PIVOTARPOR(A2:A10;B2:B10;BUSCARX(B2:B10;D2:D5;E2:E5);CONCAT;;0;;0)

Aceptamos, y, ya lo tenemos.

Si queremos un recuento de las materias que imparten cada nombre, usamos la funcion AGRUPARPOR, como argumento row fields, seleccionaos el rango A2:A10, como argumento values, usamos BUSCARX, como argumento valor buscado, seleccionamos el rango B2:B10, como argumento matriz de búsqueda, seleccionamos el rango D2:D5, como argumento matriz devuelta, seleccionamos el rango E2:E5, concatenamos con un espacio, como argumento funcion, ponemos CONTARA.

=AGRUPARPOR(A2:A10;BUSCARX(B2:B10;D2:D5;E2:E5)&" ";CONTARA)

Después del signo igual usamos APILARV, como argumento matriz1, usamos una constante de matriz, abrimos unas llaves, entre comillas dobles, ponemos Nombres, ponemos barra inclinada, entre comillas dobles, ponemos Recuento, cerramos llaves, como argumento matriz2, es la funcion AGRUPARPOR.

=APILARV({"Nombres"\"Recuento"};AGRUPARPOR(A2:A10;BUSCARX(B2:B10;D2:D5;E2:E5)&" ";CONTARA))

Aceptamos, y, ya lo tenemos.

Miguel Angel Franco