Crear modelo normalizado

Jaime Franco Jimenez
1 ene 2024
7 Min. de lectura

Diariamente, nos envían un informe como el que sigue:

Donde vemos que no sigue un orden, por ejemplo, en la primera línea, tenemos el producto, la fecha de venta, el precio del producto, y, las diferentes cantidades que se han vendido, en la cuarta línea, vemos que se ha vendido dos cantidades, pero, no aparece como cantidad1 y cantidad2, aparece como cantidad2 y cantidad4, en la última línea no aparece la fecha, no aparece el precio, y, la cantidad aparece como cantidad2.

Debemos de crear un modelo normalizado, donde en una primera columna debe de aparecer el producto con la primera letra en mayúscula, en una segunda columna la fecha donde la haya, a partir de la tercera columna, deben de aparecer tantas cantidades como se indica en cada línea, aunque en ciertas cantidades no hay, y, una ultima columna con el total, el total es multiplicar cada cantidad por el precio y sumarlos.

El modelo debe de quedar como sigue:

Empecemos…

Lo primero que vamos a realizar es preparar el encabezado, porque debemos de tener en cuenta la cantidad de cantidades que hay, por ejemplo, si en una línea aparece cantidad6, el encabezado debe de ir desde cantidad1 a cantidad6, aunque haya líneas que en ciertas cantidades no hay cantidades.

En una celda, usamos LET, creamos una variable, usamos CONCAT, como argumento texto1, seleccionamos el rango B3:B7, y, concatenamos con la barra inclinada.

=LET(a;CONCAT(B3:B7&"/");a)

Tenemos todas las líneas unidas separadas por la barra inclinada.

Usamos DIVIDIRTEXTO, como argumento texto, es la funcion CONCAT, como argumento delimitador de columna, entre comillas dobles, ponemos la barra inclinada, como argumento delimitador de fila, entre comillas dobles, ponemos coma.

=LET(a;DIVIDIRTEXTO(CONCAT(B3:B7&"/");"/";",");a)

Obtenemos una matriz desbordada de tres columnas.

Vemos que tenemos espacios de más, pues, antes de DIVIDIRTEXTO, usamos ESPACIOS.

=LET(a;ESPACIOS(DIVIDIRTEXTO(CONCAT(B3:B7&"/");"/";","));a)

De las tres columnas, solo nos interesa la primera columna, que es donde tenemos los encabezados que necesitamos, por lo que usamos la funcion TOMAR, como argumento matriz, es la funcion DIVIDIRTEXTO, omitimos el argumento filas, como argumento columnas, ponemos 1.

=LET(a;TOMAR(ESPACIOS(DIVIDIRTEXTO(CONCAT(B3:B7&"/");"/";","));;1);a)

Ordenamos.

=LET(a;TOMAR(ESPACIOS(DIVIDIRTEXTO(CONCAT(B3:B7&"/");"/";","));;1);b;ORDENAR(FILTRAR(a;IZQUIERDA(a;3)="can"));b)

Usamos la funcion TEXTOANTES, como argumento texto es la funcion ORDENAR, como argumento delimitador, entre comillas dobles, ponemos dos puntos.

=LET(a;TOMAR(ESPACIOS(DIVIDIRTEXTO(CONCAT(B3:B7&"/");"/";","));;1);b;TEXTOANTES(ORDENAR(FILTRAR(a;IZQUIERDA(a;3)="can"));":");b)

Nos quedamos con los valore únicos.

=LET(a;TOMAR(ESPACIOS(DIVIDIRTEXTO(CONCAT(B3:B7&"/");"/";","));;1);b;UNICOS(TEXTOANTES(ORDENAR(FILTRAR(a;IZQUIERDA(a;3)="can"));":"));b)

Ya tenemos las cantidades a usar como encabezados.

Lo ponemos en horizontal, para ello, usamos la funcion ENFILA.

=LET(a;TOMAR(ESPACIOS(DIVIDIRTEXTO(CONCAT(B3:B7&"/");"/";","));;1);b;ENFILA(UNICOS(TEXTOANTES(ORDENAR(FILTRAR(a;IZQUIERDA(a;3)="can"));":")));b)

Usamos el argumento calculo de LET, usamos APILARH, como argumento matriz1, entre comillas dobles, ponemos Producto, como argumento matriz2, entre comillas dobles, ponemos Fecha, como argumento matriz3, ponemos la variable “b”, como argumento matriz4, entre comillas dobles, ponemos Total.

=LET(a;TOMAR(ESPACIOS(DIVIDIRTEXTO(CONCAT(B3:B7&"/");"/";","));;1);b;ENFILA(UNICOS(TEXTOANTES(ORDENAR(FILTRAR(a;IZQUIERDA(a;3)="can"));":")));APILARH("Producto";"Fecha";b;"Total"))

Ya tenemos el encabezado, pero, de forma dinámica, si hay mas o menos cantidades, el encabezado se actualizara.

Antes de APILARH, usamos la función SUSTITUIR, como argumento texto, es la función APILARH, como argumento texto original, entre comillas dobles, ponemos cantidad, como texto nuevo, entre comillas dobles, ponemos Total.

=LET(a;TOMAR(ESPACIOS(DIVIDIRTEXTO(CONCAT($B$3:$B$7&"/");"/";","));;1);enca_cant;ENFILA(UNICOS(TEXTOANTES(ORDENAR(FILTRAR(a;IZQUIERDA(a;3)="can"));":")));SUSTITUIR(APILARH("Producto";"Fecha";enca_cant;"Total");"cantidad";"Total"))

Hacemos esto, porque cuando realicemos el informe, va a aparecer el precio multiplicado por cada cantidad.

En la celda de abajo al primer encabezado, creamos una variable, usamos la función DIVIDIRTEXTO, como argumento texto, seleccionamos la celda B3, donde tenemos la primera línea, omitimos el argumento delimitador de columna, como argumento delimitador de fila, entre comillas dobles, ponemos coma.

=LET(xx;DIVIDIRTEXTO(B3;;",");xx)

Tenemos una matriz desbordada en vertical con cada cadena antes de cada coma.

Usamos la funcion ESPACIOS, para quitar los espacios de más.

=LET(xx;ESPACIOS(DIVIDIRTEXTO(B3;;","));xx)

Creamos otra variable, y, nos traemos el modelo, y, fijamos.

=LET(xx;ESPACIOS(DIVIDIRTEXTO(B3;;","));yy;$B$3:$B$7;yy)

Creamos otra variable, usamos CONCAT, como argumento texto1, ponemos la variable “yy” y concatenamos con la barra inclinada mirando hacia la derecha.

=LET(xx;ESPACIOS(DIVIDIRTEXTO(B3;;","));yy;$B$3:$B$7;a;CONCAT(yy&"/");a)

Tenemos todas las líneas del modelo en una sola fila, donde cada registro esta separado por la barra inclinada.

Usamos DIVIDIRTEXTO, como argumento texto es la funcion CONCAT, como argumento delimitador de columna, ponemos la barra inclinada mirando hacia la derecha, como argumento delimitador de fila, ponemos coma.

=LET(xx;ESPACIOS(DIVIDIRTEXTO(B3;;","));yy;$B$3:$B$7;a;DIVIDIRTEXTO(CONCAT(yy&"/");"/";",");a)

Tenemos una matriz desbordada de tres columnas.

Como tenemos espacios de más, usamos la funcion ESPACIOS.

=LET(xx;ESPACIOS(DIVIDIRTEXTO(B3;;","));yy;$B$3:$B$7;a;ESPACIOS(DIVIDIRTEXTO(CONCAT(yy&"/");"/";","));a)

Solo nos interesa la primera columna, que contiene los encabezados que necesitamos, que son las cantidades, pues, usamos la funcion TOMAR, como argumento matriz, es la funcion ESPACIOS, omitimos el argumento filas, como argumento columnas, ponemos 1.

=LET(xx;ESPACIOS(DIVIDIRTEXTO(B3;;","));yy;$B$3:$B$7;a;TOMAR(ESPACIOS(DIVIDIRTEXTO(CONCAT(yy&"/");"/";","));;1);a)

Creamos otra variable, la llamamos enca_cant, usamos la funcion FILTRAR, como argumento array, ponemos la variable “a”, como argumento Include, usamos la funcion IZQUIERDA, como argumento texto, ponemos la variable “a”, como argumento número de caracteres, ponemos 3, e, igualamos a “can”.

=LET(xx;ESPACIOS(DIVIDIRTEXTO(B3;;","));yy;$B$3:$B$7;a;TOMAR(ESPACIOS(DIVIDIRTEXTO(CONCAT(yy&"/");"/";","));;1);enca_cant;FILTRAR(a;IZQUIERDA(a;3)="can");enca_cant)

Tenemos solo las cantidades.

Ordenamos.

=LET(xx;ESPACIOS(DIVIDIRTEXTO(B3;;","));yy;$B$3:$B$7;a;TOMAR(ESPACIOS(DIVIDIRTEXTO(CONCAT(yy&"/");"/";","));;1);enca_cant;ORDENAR(FILTRAR(a;IZQUIERDA(a;3)="can"));enca_cant)

Usamos la funcion TEXTOANTES, como argumento texto, es la funcion ORDENAR, como argumento delimitador, entre comillas dobles, ponemos dos puntos.

=LET(xx;ESPACIOS(DIVIDIRTEXTO(B3;;","));yy;$B$3:$B$7;a;TOMAR(ESPACIOS(DIVIDIRTEXTO(CONCAT(yy&"/");"/";","));;1);enca_cant;TEXTOANTES(ORDENAR(FILTRAR(a;IZQUIERDA(a;3)="can"));":");enca_cant)

Tenemos solo los encabezados de cantidades.

Nos quedamos con los valores únicos.

=LET(xx;ESPACIOS(DIVIDIRTEXTO(B3;;","));yy;$B$3:$B$7;a;TOMAR(ESPACIOS(DIVIDIRTEXTO(CONCAT(yy&"/");"/";","));;1);enca_cant;UNICOS(TEXTOANTES(ORDENAR(FILTRAR(a;IZQUIERDA(a;3)="can"));":"));enca_cant)

Ponemos las cantidades en horizontal, para ello, usamos la funcion ENFILA.

=LET(xx;ESPACIOS(DIVIDIRTEXTO(B3;;","));yy;$B$3:$B$7;a;TOMAR(ESPACIOS(DIVIDIRTEXTO(CONCAT(yy&"/");"/";","));;1);enca_cant;ENFILA(UNICOS(TEXTOANTES(ORDENAR(FILTRAR(a;IZQUIERDA(a;3)="can"));":")));enca_cant)

Creamos otra variable, usamos la funcion FILTRAR, como argumento array, ponemos la variable “xx”, como argumento Include, usamos la funcion IZQUIERDA, como argumento texto, ponemos la variable “xx”, como argumento número de caracteres, ponemos 3 e igualamos a “can”.

Tenemos las cantidades para la primera línea.

Ordenamos.

Creamos otra variable, usamos la funcion TEXTOANTES, como argumento texto, ponemos la variable “b”, como argumento delimitador, entre comillas dobles, ponemos dos puntos.

Tenemos solo los encabezados de cantidades.

Creamos otra variable, usamos la funcion BYCOL, como argumento array, usamos el condicional SI, como argumento prueba lógica, preguntamos si la variable “c” es igual a la variable “enca_cant”, en ese caso, (valor si verdadero), usamos la funcion TEXTODESPUES, como argumento texto, ponemos la variable “b”, como argumento delimitador, entre comillas dobles, ponemos dos puntos, y, multiplicamos por 1, para que el resultado nos lo de en formato de número, como argumento valor si falso, ponemos blanco.

Como argumento funcion, ponemos LAMBDA, creamos una variable, y, sumamos dicha variable.

Tenemos las cantidades para la primera línea.

Arrastramos, y, vemos que cada cantidad se coloca en su encabezado.

Creamos otra variable, la llamamos fecha1, usamos la funcion TEXTOANTES, como argumento texto, ponemos la celda B3, como argumento delimitador, entre comillas dobles, ponemos coma, como argumento numero de instancia, ponemos 2.

Tenemos el producto y la fecha.

Usamos la funcion TEXTODESPUES, como argumento texto es la funcion TEXTOANTES, como argumento delimitador, entre comillas dobles, ponemos coma.

Tenemos solo la fecha.

La fecha como delimitador se puede usar la barra inclinada, o, el guion medio, usamos DIVIDIRTEXTO, como argumento texto, es la funcion TEXTODESPUES, como argumento delimitador de columna, usamos una constante de matriz, abrimos unas llaves, entre comillas dobles, ponemos la barra inclinada mirando hacia la derecha, ponemos la barra inclinada mirando hacia la izquierda, entre comillas dobles, ponemos el guion medio, cerramos llaves, y, cerramos paréntesis.

Tenemos separada las fecha.

Creamos otra variable, usamos la funcion TOMAR, como argumento matriz, ponemos la variable fecha1, omitimos el argumento filas, como argumento columnas, ponemos -1.

Tenemos el año.

Usamos la funcion LARGO, para obtener la longitud del año.

Nos devuelve 2.

Preguntamos que, si la longitud de LARGO es igual a 2, como argumento valor si verdadero, ponemos el numero 20 concatenado con el año, para ello, usamos la funcion TOMAR, como argumento matriz, ponemos la variable fecha1, omitimos el argumento filas, como argumento columnas, ponemos -1, como argumento valor si falso, usamos, de nuevo, la funcion TOMAR, como argumento matriz, ponemos la variable fecha1, omitimos el argumento filas, como argumento columnas, ponemos -1.

Tenemos el año con cuatro dígitos.

Creamos otra variable, vamos a formar la fecha, usamos la funcion FECHA, como argumento año, ponemos la variable fecha2, como argumento mes, usamos la funcion ELEGIRCOLS, como argumento matriz, ponemos la variable fecha1, como numero de columna1, ponemos 2, es decir, vamos a tomar el mes de la variable fecha1, como argumento día, usamos la funcion TOMAR, como argumento matriz, ponemos la variable fecha1, omitimos el argumento filas, como argumento columnas, ponemos 1.

=LET(xx;ESPACIOS(DIVIDIRTEXTO(B3;;","));yy;$B$3:$B$7;a;TOMAR(ESPACIOS(DIVIDIRTEXTO(CONCAT(yy&"/");"/";","));;1);enca_cant;ENFILA(UNICOS(TEXTOANTES(ORDENAR(FILTRAR(a;IZQUIERDA(a;3)="can"));":")));b;ORDENAR(FILTRAR(xx;IZQUIERDA(xx;3)="can"));c;TEXTOANTES(b;":");d;BYCOL(SI(c=enca_cant;TEXTODESPUES(b;":")*1;"");LAMBDA(x;SUMA(x)));fecha1;DIVIDIRTEXTO(TEXTODESPUES(TEXTOANTES(B3;",";2);",");{"/"\"-"});fecha2;SI(LARGO(TOMAR(fecha1;;-1))=2;20&TOMAR(fecha1;;-1);TOMAR(fecha1;;-1));fecha3;FECHA(fecha2;ELEGIRCOLS(fecha1;2);TOMAR(fecha1;;1));fecha3)

Ya tenemos la fecha.

Arrastramos, y, vemos que donde no hay fecha devuelve un error.

Usamos la funcion SI.ERROR, como argumento valor, es la funcion FECHA, como argumento valor si error, ponemos blanco.

Creamos otra variable, usamos la funcion HALLAR, como argumento valor buscado, entre comillas dobles, ponemos “pre”, como argumento dentro del texto, ponemos la variable “xx”.

=LET(xx;ESPACIOS(DIVIDIRTEXTO(B3;;","));yy;$B$3:$B$7;a;TOMAR(ESPACIOS(DIVIDIRTEXTO(CONCAT(yy&"/");"/";","));;1);enca_cant;ENFILA(UNICOS(TEXTOANTES(ORDENAR(FILTRAR(a;IZQUIERDA(a;3)="can"));":")));b;ORDENAR(FILTRAR(xx;IZQUIERDA(xx;3)="can"));c;TEXTOANTES(b;":");d;BYCOL(SI(c=enca_cant;TEXTODESPUES(b;":")*1;"");LAMBDA(x;SUMA(x)));fecha1;DIVIDIRTEXTO(TEXTODESPUES(TEXTOANTES(B3;",";2);",");{"/"\"-"});fecha2;SI(LARGO(TOMAR(fecha1;;-1))=2;20&TOMAR(fecha1;;-1);TOMAR(fecha1;;-1));fecha3;FECHA(fecha2;ELEGIRCOLS(fecha1;2);TOMAR(fecha1;;1));precio2;HALLAR("pre";xx);precio2)

Tenemos una matriz desbordada en vertical, con error donde no hay coincidencia, y, la posición de la primera letra donde hay coincidencia.

Preguntamos si es numero el resultado de HALLAR.

=LET(xx;ESPACIOS(DIVIDIRTEXTO(B3;;","));yy;$B$3:$B$7;a;TOMAR(ESPACIOS(DIVIDIRTEXTO(CONCAT(yy&"/");"/";","));;1);enca_cant;ENFILA(UNICOS(TEXTOANTES(ORDENAR(FILTRAR(a;IZQUIERDA(a;3)="can"));":")));b;ORDENAR(FILTRAR(xx;IZQUIERDA(xx;3)="can"));c;TEXTOANTES(b;":");d;BYCOL(SI(c=enca_cant;TEXTODESPUES(b;":")*1;"");LAMBDA(x;SUMA(x)));fecha1;DIVIDIRTEXTO(TEXTODESPUES(TEXTOANTES(B3;",";2);",");{"/"\"-"});fecha2;SI(LARGO(TOMAR(fecha1;;-1))=2;20&TOMAR(fecha1;;-1);TOMAR(fecha1;;-1));fecha3;FECHA(fecha2;ELEGIRCOLS(fecha1;2);TOMAR(fecha1;;1));precio2;ESNUMERO(HALLAR("pre";xx));precio2)

Obtenemos VERDADERO donde es número, y, FALSO donde no lo es.

Preguntamos que si es numero el resultado de HALLAR, en ese caso, usamos TEXTODESPUES, como argumento texto, ponemos la variable “xx, como argumento delimitador, entre comillas dobles, ponemos dos puntos, multiplicamos por 1, para que nos devuelva el resultado en formato de número, en caso contrario, que nos devuelva un error.

=LET(xx;ESPACIOS(DIVIDIRTEXTO(B3;;","));yy;$B$3:$B$7;a;TOMAR(ESPACIOS(DIVIDIRTEXTO(CONCAT(yy&"/");"/";","));;1);enca_cant;ENFILA(UNICOS(TEXTOANTES(ORDENAR(FILTRAR(a;IZQUIERDA(a;3)="can"));":")));b;ORDENAR(FILTRAR(xx;IZQUIERDA(xx;3)="can"));c;TEXTOANTES(b;":");d;BYCOL(SI(c=enca_cant;TEXTODESPUES(b;":")*1;"");LAMBDA(x;SUMA(x)));fecha1;DIVIDIRTEXTO(TEXTODESPUES(TEXTOANTES(B3;",";2);",");{"/"\"-"});fecha2;SI(LARGO(TOMAR(fecha1;;-1))=2;20&TOMAR(fecha1;;-1);TOMAR(fecha1;;-1));fecha3;FECHA(fecha2;ELEGIRCOLS(fecha1;2);TOMAR(fecha1;;1));precio2;SI(ESNUMERO(HALLAR("pre";xx));TEXTODESPUES(xx;":")*1;NOD());precio2)

Obtenemos el precio donde hay coincidencia, y, error donde no hay coincidencia.

Usamos ENCOL, como argumento matriz, es el condicional SI, como argumento ignorar, ponemos 3.

=LET(xx;ESPACIOS(DIVIDIRTEXTO(B3;;","));yy;$B$3:$B$7;a;TOMAR(ESPACIOS(DIVIDIRTEXTO(CONCAT(yy&"/");"/";","));;1);enca_cant;ENFILA(UNICOS(TEXTOANTES(ORDENAR(FILTRAR(a;IZQUIERDA(a;3)="can"));":")));b;ORDENAR(FILTRAR(xx;IZQUIERDA(xx;3)="can"));c;TEXTOANTES(b;":");d;BYCOL(SI(c=enca_cant;TEXTODESPUES(b;":")*1;"");LAMBDA(x;SUMA(x)));fecha1;DIVIDIRTEXTO(TEXTODESPUES(TEXTOANTES(B3;",";2);",");{"/"\"-"});fecha2;SI(LARGO(TOMAR(fecha1;;-1))=2;20&TOMAR(fecha1;;-1);TOMAR(fecha1;;-1));fecha3;FECHA(fecha2;ELEGIRCOLS(fecha1;2);TOMAR(fecha1;;1));precio2;ENCOL(SI(ESNUMERO(HALLAR("pre";xx));TEXTODESPUES(xx;":")*1;NOD());3);precio2)

Ya tenemos el precio.

Arrastramos, y, donde no hay precio obtenemos un error.

Creamos otra variable, multiplicamos la variable precio2 por la variable “d”.

=LET(xx;ESPACIOS(DIVIDIRTEXTO(B3;;","));yy;$B$3:$B$7;a;TOMAR(ESPACIOS(DIVIDIRTEXTO(CONCAT(yy&"/");"/";","));;1);enca_cant;ENFILA(UNICOS(TEXTOANTES(ORDENAR(FILTRAR(a;IZQUIERDA(a;3)="can"));":")));b;ORDENAR(FILTRAR(xx;IZQUIERDA(xx;3)="can"));c;TEXTOANTES(b;":");d;BYCOL(SI(c=enca_cant;TEXTODESPUES(b;":")*1;"");LAMBDA(x;SUMA(x)));fecha1;DIVIDIRTEXTO(TEXTODESPUES(TEXTOANTES(B3;",";2);",");{"/"\"-"});fecha2;SI(LARGO(TOMAR(fecha1;;-1))=2;20&TOMAR(fecha1;;-1);TOMAR(fecha1;;-1));fecha3;FECHA(fecha2;ELEGIRCOLS(fecha1;2);TOMAR(fecha1;;1));precio2;ENCOL(SI(ESNUMERO(HALLAR("pre";xx));TEXTODESPUES(xx;":")*1;NOD());3);resul1;precio2*d;resul1)

Tenemos el total de cada cantidad.

Arrastramos, y, donde no hay precio obtenemos un error.

Usamos la funcion SI.ERROR, como argumento valor es la funcion ENCOL, como argumento valor si error, ponemos la variable “d”, es decir, las cantidades.

=LET(xx;ESPACIOS(DIVIDIRTEXTO(B3;;","));yy;$B$3:$B$7;a;TOMAR(ESPACIOS(DIVIDIRTEXTO(CONCAT(yy&"/");"/";","));;1);enca_cant;ENFILA(UNICOS(TEXTOANTES(ORDENAR(FILTRAR(a;IZQUIERDA(a;3)="can"));":")));b;ORDENAR(FILTRAR(xx;IZQUIERDA(xx;3)="can"));c;TEXTOANTES(b;":");d;BYCOL(SI(c=enca_cant;TEXTODESPUES(b;":")*1;"");LAMBDA(x;SUMA(x)));fecha1;DIVIDIRTEXTO(TEXTODESPUES(TEXTOANTES(B3;",";2);",");{"/"\"-"});fecha2;SI(LARGO(TOMAR(fecha1;;-1))=2;20&TOMAR(fecha1;;-1);TOMAR(fecha1;;-1));fecha3;SI.ERROR(FECHA(fecha2;ELEGIRCOLS(fecha1;2);TOMAR(fecha1;;1));"");precio2;ENCOL(SI(ESNUMERO(HALLAR("pre";xx));TEXTODESPUES(xx;":")*1;NOD());3);resul1;SI.ERROR(precio2*d;d);resul1)

Arrastramos, y, vemos que donde había error, ahora, aparecen las cantidades, además, cada cantidad aparece en su posición.

Creamos otra variable, usamos APILARH, como argumento matriz1, usamos la funcion NOMPROPIO, como argumento, usamos la funcion TEXTOANTES, como argumento texto, ponemos la celda B3, como argumento delimitador, ente comillas dobles, ponemos coma.

=LET(xx;ESPACIOS(DIVIDIRTEXTO(B3;;","));yy;$B$3:$B$7;a;TOMAR(ESPACIOS(DIVIDIRTEXTO(CONCAT(yy&"/");"/";","));;1);enca_cant;ENFILA(UNICOS(TEXTOANTES(ORDENAR(FILTRAR(a;IZQUIERDA(a;3)="can"));":")));b;ORDENAR(FILTRAR(xx;IZQUIERDA(xx;3)="can"));c;TEXTOANTES(b;":");d;BYCOL(SI(c=enca_cant;TEXTODESPUES(b;":")*1;"");LAMBDA(x;SUMA(x)));fecha1;DIVIDIRTEXTO(TEXTODESPUES(TEXTOANTES(B3;",";2);",");{"/"\"-"});fecha2;SI(LARGO(TOMAR(fecha1;;-1))=2;20&TOMAR(fecha1;;-1);TOMAR(fecha1;;-1));fecha3;SI.ERROR(FECHA(fecha2;ELEGIRCOLS(fecha1;2);TOMAR(fecha1;;1));"");precio2;ENCOL(SI(ESNUMERO(HALLAR("pre";xx));TEXTODESPUES(xx;":")*1;NOD());3);resul1;SI.ERROR(precio2*d;d);resul2;APILARH(NOMPROPIO(TEXTOANTES(B3;","))

Como argumento matriz2, ponemos la variable fecha3.

=LET(xx;ESPACIOS(DIVIDIRTEXTO(B3;;","));yy;$B$3:$B$7;a;TOMAR(ESPACIOS(DIVIDIRTEXTO(CONCAT(yy&"/");"/";","));;1);enca_cant;ENFILA(UNICOS(TEXTOANTES(ORDENAR(FILTRAR(a;IZQUIERDA(a;3)="can"));":")));b;ORDENAR(FILTRAR(xx;IZQUIERDA(xx;3)="can"));c;TEXTOANTES(b;":");d;BYCOL(SI(c=enca_cant;TEXTODESPUES(b;":")*1;"");LAMBDA(x;SUMA(x)));fecha1;DIVIDIRTEXTO(TEXTODESPUES(TEXTOANTES(B3;",";2);",");{"/"\"-"});fecha2;SI(LARGO(TOMAR(fecha1;;-1))=2;20&TOMAR(fecha1;;-1);TOMAR(fecha1;;-1));fecha3;SI.ERROR(FECHA(fecha2;ELEGIRCOLS(fecha1;2);TOMAR(fecha1;;1));"");precio2;ENCOL(SI(ESNUMERO(HALLAR("pre";xx));TEXTODESPUES(xx;":")*1;NOD());3);resul1;SI.ERROR(precio2*d;d);resul2;APILARH(NOMPROPIO(TEXTOANTES(B3;","));fecha3

Como argumento matriz3, ponemos la variable resul1.

Como argumento matriz4, usamos SUMA, y, como argumento ponemos la variable resul1.

=LET(xx;ESPACIOS(DIVIDIRTEXTO(B3;;","));yy;$B$3:$B$7;a;TOMAR(ESPACIOS(DIVIDIRTEXTO(CONCAT(yy&"/");"/";","));;1);enca_cant;ENFILA(UNICOS(TEXTOANTES(ORDENAR(FILTRAR(a;IZQUIERDA(a;3)="can"));":")));b;ORDENAR(FILTRAR(xx;IZQUIERDA(xx;3)="can"));c;TEXTOANTES(b;":");d;BYCOL(SI(c=enca_cant;TEXTODESPUES(b;":")*1;"");LAMBDA(x;SUMA(x)));fecha1;DIVIDIRTEXTO(TEXTODESPUES(TEXTOANTES(B3;",";2);",");{"/"\"-"});fecha2;SI(LARGO(TOMAR(fecha1;;-1))=2;20&TOMAR(fecha1;;-1);TOMAR(fecha1;;-1));fecha3;SI.ERROR(FECHA(fecha2;ELEGIRCOLS(fecha1;2);TOMAR(fecha1;;1));"");precio2;ENCOL(SI(ESNUMERO(HALLAR("pre";xx));TEXTODESPUES(xx;":")*1;NOD());3);resul1;SI.ERROR(precio2*d;d);resul2;APILARH(NOMPROPIO(TEXTOANTES(B3;","));fecha3;resul1;SUMA( resul1));resul2)

Ya tenemos formada la primera línea.

Arrastramos y ya lo tenemos.

Vemos que cada cantidad ha sido colocada en su posición.

Nos colocamos en una fila mas a la ultima fila del modelo, usamos LET, creamos una variable, usamos BYCOL, como argumento array, seleccionamos el rango D3:K7, como argumento funcion, ponemos LAMBDA, creamos una variable, y, sumamos dicha variable.

=LET(a;BYCOL(D3:K7;LAMBDA(x;SUMA(x)));a)

Tenemos la suma de todas las cantidades, incluida las fechas.

Las fechas no nos interesa, creamos otra variable, usamos el condicional SI, como argumento prueba lógica, preguntamos si la variable “a” es igual al valor máximo de la variable “a”, en ese caso, que ponga blanco, en caso contrario, que devuelva la variable “a”.

=LET(a;BYCOL(D3:K7;LAMBDA(x;SUMA(x)));b;SI(a=MAX(a);"";a);b)

Vemos que la fecha ya no aparece.

Usamos el argumento calculo de LET, usamos APILARH, como argumento matriz1, entre comillas dobles, ponemos Total, como argumento matriz2, ponemos la variable “b”.

=LET(a;BYCOL(D3:K7;LAMBDA(x;SUMA(x)));b;SI(a=MAX(a);"";a);APILARH("Total";b))

Aceptamos.

Y, si por error, encontramos la fecha en una posición distinta.

Podemos hacerlo de la siguiente manera:

En una celda, usamos LET, creamos una variable, usamos ESPACIOS, porque vamos a obtener espacios de más, como argumento usamos DIVIDIRTEXTO, como argumento texto, seleccionamos la celda con la cadena, omitimos el argumento delimitador de columna, como argumento delimitador de fila, entre comillas dobles, ponemos coma.

=LET(a;ESPACIOS(DIVIDIRTEXTO(B13;;","));a)

Obtenemos cada cadena antes de cada coma.

Ordenamos.

Creamos otra variable, usamos la funcion HALLAR, como argumento texto buscado, usamos una constante de matriz, abrimos unas llaves, entre comillas dobles, ponemos el guion medio, ponemos la barra inclinada hacia la izquierda, entre comillas dobles, ponemos la barra inclinada, cerramos llaves, como argumento dentro del texto, ponemos la variable “a”.

=LET(a;ESPACIOS(DIVIDIRTEXTO(B13;;","));b;HALLAR({"-"\"/"};a);b)

Tenemos una matriz desbordada de dos columnas, donde aparece la posición del guion medio, o, barra inclinada, y, error donde no hay coincidencia.

Usamos el argumento calculo de LET, preguntamos que, si en numero la variable “b”, que nos devuelva la variable “a”, en caso contrario, que devuelva un error.

=LET(a;ESPACIOS(DIVIDIRTEXTO(B13;;","));b;HALLAR({"-"\"/"};a);SI(ESNUMERO(b);a;NOD()))

Tenemos una matriz de dos columnas, con la fecha donde hay coincidencia, y, error donde no la hay.

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

=LET(a;ESPACIOS(DIVIDIRTEXTO(B13;;","));b;HALLAR({"-"\"/"};a);ENFILA(SI(ESNUMERO(b);a;NOD());3))

Ya tenemos la fecha.

Miguel Angel Franco

¿ Listo para empezar a usar Microsoft Excel?

Crear modelo normalizado

Entradas recientes

Comentarios