Crear modelo por grupos y valores

Jaime Franco Jimenez
20 nov 2023
3 Min. de lectura

Tenemos los siguientes datos:

Debemos de crear un modelo como sigue:

En la celda H2, usamos LET, creamos una variable, preguntamos si es texto el rango B1:F10.

=LET(a;ESTEXTO(B1:F10);a)

Obtenemos una matriz desbordada de 5 columnas, y, 10 filas, con VERDADERO donde es texto, y, FALSO donde no lo es.

Preguntamos que, si es texto, nos devuelva el rango B1:F10, en caso contrario, que devuelva un error.

=LET(a;SI(ESTEXTO(B1:F10);B1:F10;NOD());a)

Obtenemos los textos, y, error donde no lo es.

Usamos la funcion ENFILA, e, ignoramos blancos, y, errores.

=LET(a;ENFILA(SI(ESTEXTO(B1:F10);B1:F10;NOD());3);a)

Nos quedamos con los valores únicos, pero, por columnas.

=LET(a;UNICOS(ENFILA(SI(ESTEXTO(B1:F10);B1:F10;NOD());3);VERDADERO);a)

Ya tenemos el encabezado.

Creamos otra variable, usamos la funcion FILA, como argumento seleccionamos el rango A1:A10.

Volvemos a la expresión, usamos BYROW, como argumento array, es la funcion FILA, ponemos LAMBDA, creamos una variable, preguntamos si es par la variable “x”.

=LET(a;UNICOS(ENFILA(SI(ESTEXTO(B1:F10);B1:F10;NOD());3);VERDADERO);b;BYROW(FILA(A1:A10);LAMBDA(x;ES.PAR(x)));b)

Obtenemos VERDADERO si la fila es par, y, FALSO donde no lo es.

Creamos otra variable, preguntamos si la variable “b” es igual a VERDADERO, que nos devuelva el rango B1:F10, en caso contrario, que nos devuelva un error.

=LET(a;UNICOS(ENFILA(SI(ESTEXTO(B1:F10);B1:F10;NOD());3);VERDADERO);b;BYROW(FILA(A1:A10);LAMBDA(x;ES.PAR(x)));c;SI(b;B1:F10;NOD());c)

Tenemos los valores, y, error donde es texto.

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

=LET(a;UNICOS(ENFILA(SI(ESTEXTO(B1:F10);B1:F10;NOD());3);VERDADERO);b;BYROW(FILA(A1:A10);LAMBDA(x;ES.PAR(x)));c;ENCOL(SI(b;B1:F10;NOD());3);c)

Tenemos los valores en una columna.

Creamos otra variable, preguntamos si la variable “c” es igual a cero, que devuelva blanco, en caso contrario, que devuelva la variable “c”, vamos a sustituir los valores ceros por blancos.

=LET(a;UNICOS(ENFILA(SI(ESTEXTO(B1:F10);B1:F10;NOD());3);VERDADERO);b;BYROW(FILA(A1:A10);LAMBDA(x;ES.PAR(x)));c;ENCOL(SI(b;B1:F10;NOD());3);d;SI(c=0;"";c);d)

Creamos otra variable, preguntamos si la variable “b” es igual a FALSO, que nos devuelva el rango B1:F10, en caso contrario, que devuelva un error.

=LET(a;UNICOS(ENFILA(SI(ESTEXTO(B1:F10);B1:F10;NOD());3);VERDADERO);b;BYROW(FILA(A1:A10);LAMBDA(x;ES.PAR(x)));c;ENCOL(SI(b;B1:F10;NOD());3);d;SI(c=0;"";c);e;SI(b=FALSO;B1:F10;NOD());e)

Obtenemos una matriz con los textos, y, error donde no hay coincidencia.

Usamos ENCOL, e, ignoramos blancos y errores.

=LET(a;UNICOS(ENFILA(SI(ESTEXTO(B1:F10);B1:F10;NOD());3);VERDADERO);b;BYROW(FILA(A1:A10);LAMBDA(x;ES.PAR(x)));c;ENCOL(SI(b;B1:F10;NOD());3);d;SI(c=0;"";c);e;ENCOL(SI(b=FALSO;B1:F10;NOD());3);e)

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

Tenemos el modelo enfilas.

Creamos otra variable, filtramos la variable “f”, si la primera columna de la variable “f”, por lo que usamos la funcion TOMAR, como argumento matriz, ponemos la variable “f”, ignoramos el argumento filas, como argumento columnas, ponemos 1.

Tenemos el modelo solo con los datos.

Ordenamos la funcion FILTRAR por la columna 2.

Creamos otra variable, preguntamos si la variable “a” es igual a la ultima columna de la variable “g”, para ello, usamos TOMAR, como argumento matriz ponemos la variable “g”, omitimos el argumento filas, como argumento columnas, ponemos -1.

Como argumento valor si verdadero, que nos devuelva la primera columna de la variable “g”, volvemos a usar TOMAR y en el argumento columnas, ponemos 1, como argumento valor si falso, ponemos blanco.

Obtenemos una matriz desbordada donde en cada columna, tenemos los valores que corresponden con cada valor.

Creamos otra variable, usamos BYCOL, como argumento array, ponemos la variable “h”, ponemos LAMBDA, creamos una variable, usamos UNIRCADENAS, como argumento delimitador, ponemos un espacio, ignoramos celdas vacías, como argumento texto1, ponemos la variable “x”.

Obtenemos unidas las cantidades para cada valor.

Creamos otra variable, usamos APILARV, como argumento matriz1, usamos DIVIDIRTEXTO, como argumento texto, ponemos la variable “i”, como argumento delimitador de columna, ponemos un espacio.

=LET(a;UNICOS(ENFILA(SI(ESTEXTO(B1:F10);B1:F10;NOD());3);VERDADERO);b;BYROW(FILA(A1:A10);LAMBDA(x;ES.PAR(x)));c;ENCOL(SI(b;B1:F10;NOD());3);d;SI(c=0;"";c);e;ENCOL(SI(b=FALSO;B1:F10;NOD());3);f;APILARH(c;e);g;ORDENAR(FILTRAR(f;TOMAR(f;;1)<>0);2);h;SI(TOMAR(f;;-1)=a;TOMAR(f;;1);"");i;BYCOL(h;LAMBDA(x;UNIRCADENAS(" ";VERDADERO;x)));j;APILARV(DIVIDIRTEXTO(i;" ")

Como argumento matriz2, usamos DIVIDIRTEXTO, como argumento texto, usamos TEXTODESPUES, como argumento texto, ponemos la variable “i”, como delimitador, ponemos un espacio, como argumento delimitador de columna, ponemos un espacio.

Como argumento matriz3, usamos TEXTODESPUES, como argumento texto, ponemos la variable “i”, como argumento delimitador, ponemos un espacio, como argumento numero de instancia, ponemos 2.

Ya tenemos las cantidades que corresponden con cada valor, y, error donde no hay coincidencia.

Usamos SI.ERROR, y, ponemos blanco si la expresión anterior devuelve un error.

Usamos el argumento calculo de LET, usamos APILARV, como argumento matriz1, ponemos APILARH, como argumento matriz1, entre comillas dobles, ponemos Grupos, como argumento matriz2, ponemos la variable “a”, cerramos paréntesis, como argumento matriz2 del primer APILARV, ponemos APILARH, como argumento matriz1, nos traemos los valores únicos del rango A1:A10, como argumento matriz2, ponemos la variable “j”, cerramos paréntesis.

Aceptamos, y, ya lo tenemos.

Miguel Angel Franco

¿ Listo para empezar a usar Microsoft Excel?

Crear modelo por grupos y valores

Entradas recientes

Comentarios