Modelo normalizado

Tenemos el siguiente modelo:

Debemos de crear un modelo como el que sigue:

Empecemos…

En la primera columna del modelo original aparecen una serie de secuencias (Seq), que está asociado a una ciudad.

En el modelo a obtener debe de aparecer primero la secuencia, después el estado, debe de aparecer tantos estados como clases haya, mas un estado mas para el subtotal, quiere decir que se debe de repetir el numero de secuencia para cada estado.

En la celda G2, usamos LET, creamos una variable, vamos a filtrar el modelo siempre que la columna estado sea diferente a Seq, para ello, usamos FILTRAR, como argumento array, seleccionamos el rango A2:E21, como argumento Include, seleccionamos el rango B2:B21, ponemos el símbolo de indistinto, y, entre comillas dobles, ponemos Seq.

Probamos variable.

=LET(a;FILTRAR(A2:E21;B2:B21<>"Seq");a)

Obtenemos una matriz desbordada con el modelo, excepto, con las filas donde aparece Seq.

Creamos otra variable, vamos a rellenar los ceros con 1, 2, 3, u, 4, para ello, usamos SCAN, como argumento valor inicial, ponemos la celda A2, como argumento array, usamos la funcion TOMAR, como argumento matriz, ponemos la variable “a”, omitimos el argumento filas, como argumento columnas, ponemos 1.

=LET(a;FILTRAR(A2:E21;B2:B21<>"Seq");b;SCAN(A2;TOMAR(a;;1)

Como argumento funcion, ponemos LAMBDA, creamos una variable, preguntamos si la variable “y” es igual a blanco, en ese caso, que ponga la variable “x”, en caso contrario, que ponga la variable “y”.

=LET(a;FILTRAR(A2:E21;B2:B21<>"Seq");b;SCAN(A2;TOMAR(a;;1);LAMBDA(x;y;SI(y="";x;y)));b)

Vemos como los ceros han sido rellenados.

Creamos otra variable, usamos BUSCARX, como argumento valor buscado, ponemos la variable “b”, como argumento matriz de búsqueda, seleccionamos el rango A2:A21, como argumento matriz devuelta, seleccionamos el rango C2:C21.

Obtenemos el estado que corresponde a cada secuencia.

=LET(a;FILTRAR(A2:E21;B2:B21<>"Seq");b;SCAN(A2;TOMAR(a;;1);LAMBDA(x;y;SI(y="";x;y)));c;BUSCARX(b;A2:A21;C2:C21);c)

Creamos otra variable, usamos otro BUSCARX, como argumento valor buscado, ponemos la variable “c”, como argumento matriz de búsqueda, seleccionamos el rango C2:C21, como argumento matriz devuelta, seleccionamos el rango B2:B21, vamos a obtener la ciudad de cada estado.

=LET(a;FILTRAR(A2:E21;B2:B21<>"Seq");b;SCAN(A2;TOMAR(a;;1);LAMBDA(x;y;SI(y="";x;y)));c;BUSCARX(b;A2:A21;C2:C21);d;BUSCARX(c;C2:C21;B2:B21);d)

Creamos otra variable, preguntamos si es numero la segunda columna de la variable “a”, para ello, ponemos la funcion ESNUMERO, como argumento usamos INDICE, como argumento matriz, ponemos la variable “a”, como argumento numero de fila, usamos SECUENCIA, como argumento filas, ponemos 16, que son las filas que tenemos, como argumento numero de columna, ponemos 2.

=LET(a;FILTRAR(A2:E21;B2:B21<>"Seq");b;SCAN(A2;TOMAR(a;;1);LAMBDA(x;y;SI(y="";x;y)));c;BUSCARX(b;A2:A21;C2:C21);d;BUSCARX(c;C2:C21;B2:B21);e;ESNUMERO(INDICE(a;SECUENCIA(16);2));e)

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

Preguntamos que si es número, nos debe de devolver las columnas 2, 3, 4, y, 5 de la variable “a”, en caso contrario, que devuelva un blanco, para ello, usamos el condicional SI, como argumento prueba lógica es la funcion ESNUMERO, como argumento valor si verdadero, usamos INDICE, como argumento matriz, ponemos la variable “a”, como argumento numero de fila, ponemos SECUENCIA, como argumento filas, ponemos 16, como argumento numero de columna, usamos una constante de matriz, para ello, abrimos unas llaves, ponemos los valores 2, 3, 4, y, 5 separados por barra inclinada, y, cerramos llaves, como argumento valor si falso, ponemos un error.

=LET(a;FILTRAR(A2:E21;B2:B21<>"Seq");b;SCAN(A2;TOMAR(a;;1);LAMBDA(x;y;SI(y="";x;y)));c;BUSCARX(b;A2:A21;C2:C21);d;BUSCARX(c;C2:C21;B2:B21);e;SI(ESNUMERO(INDICE(a;SECUENCIA(16);2));INDICE(a;SECUENCIA(16);{2\3\4\5});NOD());e)

Obtenemos el modelo con las clases donde hay coincidencia, y, blanco donde no la hay.

Quitamos la primera fila, para ello, usamos la funcion EXCLUIR, como argumento matriz es el condicional SI, como argumento filas, ponemos 1, omitimos el argumento columnas.

=LET(a;FILTRAR(A2:E21;B2:B21<>"Seq");b;SCAN(A2;TOMAR(a;;1);LAMBDA(x;y;SI(y="";x;y)));c;BUSCARX(b;A2:A21;C2:C21);d;BUSCARX(c;C2:C21;B2:B21);e;EXCLUIR(SI(ESNUMERO(INDICE(a;SECUENCIA(16);2));INDICE(a;SECUENCIA(16);{2\3\4\5});NOD());1);e)

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

=LET(a;FILTRAR(A2:E21;B2:B21<>"Seq");b;SCAN(A2;TOMAR(a;;1);LAMBDA(x;y;SI(y="";x;y)));c;BUSCARX(b;A2:A21;C2:C21);d;BUSCARX(c;C2:C21;B2:B21);e;EXCLUIR(SI(ESNUMERO(INDICE(a;SECUENCIA(16);2));INDICE(a;SECUENCIA(16);{2\3\4\5});"");1);f;APILARH(b;c;d;e);f)

Ya tenemos el modelo preparado.

Vemos que la ultima fila nos devuelve error, usamos la funcion SI.ERROR, y, ponemos blanco si la expresión devuelve un error.

=LET(a;FILTRAR(A2:E21;B2:B21<>"Seq");b;SCAN(A2;TOMAR(a;;1);LAMBDA(x;y;SI(y="";x;y)));c;BUSCARX(b;A2:A21;C2:C21);d;BUSCARX(c;C2:C21;B2:B21);e;EXCLUIR(SI(ESNUMERO(INDICE(a;SECUENCIA(16);2));INDICE(a;SECUENCIA(16);{2\3\4\5});"");1);f;SI.ERROR(APILARH(b;c;d;e);"");f)

Pero, aun faltan algunas cosas, si nos fijamos en el modelo original, vemos que están los subtotales para cada ciudad.

Pues, debemos de insertar en la columna de cantidades donde es blanco el subtotal que le corresponde, para ello, creamos otra variable, usamos SECUENCIA como argumento filas, ponemos 20, que son las filas que hay en el modelo original.

=LET(a;FILTRAR(A2:E21;B2:B21<>"Seq");b;SCAN(A2;TOMAR(a;;1);LAMBDA(x;y;SI(y="";x;y)));c;BUSCARX(b;A2:A21;C2:C21);d;BUSCARX(c;C2:C21;B2:B21);e;EXCLUIR(SI(ESNUMERO(INDICE(a;SECUENCIA(16);2));INDICE(a;SECUENCIA(16);{2\3\4\5});NOD());1);f;SI.ERROR(APILARH(b;c;d;e);"");g;SECUENCIA(20)

Creamos otra variable, la llamamos Total, usamos la funcion INDICE, como argumento matriz, seleccionamos el rango E1:E21, como argumento número de fila, usamos el condicional SI, donde preguntamos que si el rango E2:E21 es igual a E3, que nos devuelva la variable “g”, en caso contrario que devuelva un texto en blanco, envolvemos el condicional SI en la funcion ENCOL, donde usamos el argumento ignorar, y, ponemos 3, es decir, ignorar errores y blancos.

=LET(a;FILTRAR(A2:E21;B2:B21<>"Seq");b;SCAN(A2;TOMAR(a;;1);LAMBDA(x;y;SI(y="";x;y)));c;BUSCARX(b;A2:A21;C2:C21);d;BUSCARX(c;C2:C21;B2:B21);e;EXCLUIR(SI(ESNUMERO(INDICE(a;SECUENCIA(16);2));INDICE(a;SECUENCIA(16);{2\3\4\5});NOD());1);f;SI.ERROR(APILARH(b;c;d;e);"");g;SECUENCIA(20);total;ENCOL(INDICE(E1:E21;SI(E2:E21=E3;g;""));3);total)

Tenemos una matriz desbordada con el número de fila donde hay coincidencia.

Creamos otra variable, la llamamos total2, preguntamos si la columna 7 es igual a blanco, para ello, usamos INDICE, como argumento matriz, ponemos la variable “f”, omitimos le argumento numero de fila, como argumento numero de columna, ponemos 7, igualamos a blanco.

=LET(a;FILTRAR(A2:E21;B2:B21<>"Seq");b;SCAN(A2;TOMAR(a;;1);LAMBDA(x;y;SI(y="";x;y)));c;BUSCARX(b;A2:A21;C2:C21);d;BUSCARX(c;C2:C21;B2:B21);e;EXCLUIR(SI(ESNUMERO(INDICE(a;SECUENCIA(16);2));INDICE(a;SECUENCIA(16);{2\3\4\5});NOD());1);f;SI.ERROR(APILARH(b;c;d;e);"");g;SECUENCIA(20);total;"SubTotal..."&ENCOL(INDICE(E1:E21;SI(E2:E21=E3;g;""));3);total2;SI(INDICE(f;;7)=""

Como argumento valor si verdadero, usamos, de nuevo, INDICE, como argumento matriz, ponemos la variable “Total”, como argumento numero de fila, volvemos a usar INDICE, como argumento matriz, ponemos la variable “f”, omitimos el argumento número de fila, como argumento numero de columna, ponemos 1.

=LET(a;FILTRAR(A2:E21;B2:B21<>"Seq");b;SCAN(A2;TOMAR(a;;1);LAMBDA(x;y;SI(y="";x;y)));c;BUSCARX(b;A2:A21;C2:C21);d;BUSCARX(c;C2:C21;B2:B21);e;EXCLUIR(SI(ESNUMERO(INDICE(a;SECUENCIA(16);2));INDICE(a;SECUENCIA(16);{2\3\4\5});NOD());1);f;SI.ERROR(APILARH(b;c;d;e);"");g;SECUENCIA(20);total;"SubTotal..."&ENCOL(INDICE(E1:E21;SI(E2:E21=E3;g;""));3);total2;SI(INDICE(f;;7)="";INDICE(total;INDICE(f;;1))

Como argumento valor si falso, usamos INDICE, como argumento matriz, ponemos la variable “f”, omitimos el argumento número de fila, como argumento numero de columna, ponemos 7.

=LET(a;FILTRAR(A2:E21;B2:B21<>"Seq");b;SCAN(A2;TOMAR(a;;1);LAMBDA(x;y;SI(y="";x;y)));c;BUSCARX(b;A2:A21;C2:C21);d;BUSCARX(c;C2:C21;B2:B21);e;EXCLUIR(SI(ESNUMERO(INDICE(a;SECUENCIA(16);2));INDICE(a;SECUENCIA(16);{2\3\4\5});NOD());1);f;SI.ERROR(APILARH(b;c;d;e);"");g;SECUENCIA(20);total;"SubTotal..."&ENCOL(INDICE(E1:E21;SI(E2:E21=E3;g;""));3);total2;SI(INDICE(f;;7)="";INDICE(total;INDICE(f;;1));INDICE(f;;7));total)

Obtenemos el subtotal de cada país.

Creamos otra variable, la llamamos resul, usamos APILARH, como argumento matriz1, usamos INDICE, como argumento matriz, ponemos la variable “f”, como argumento numero de fila, usamos SECUENCIA, como argumento filas, ponemos 16, que son las filas que tenemos, como argumento número de columna, volvemos a usar SECUENCIA, omitimos el argumento filas, como argumento columnas, ponemos 6, como argumento matriz2, ponemos la variable total2.

=LET(a;FILTRAR(A2:E21;B2:B21<>"Seq");b;SCAN(A2;TOMAR(a;;1);LAMBDA(x;y;SI(y="";x;y)));c;BUSCARX(b;A2:A21;C2:C21);d;BUSCARX(c;C2:C21;B2:B21);e;EXCLUIR(SI(ESNUMERO(INDICE(a;SECUENCIA(16);2));INDICE(a;SECUENCIA(16);{2\3\4\5});NOD());1);f;SI.ERROR(APILARH(b;c;d;e);"");g;SECUENCIA(20);total;"SubTotal..."&ENCOL(INDICE(E1:E21;SI(E2:E21=E3;g;""));3);total2;SI(INDICE(f;;7)="";INDICE(total;INDICE(f;;1));INDICE(f;;7));resul;APILARH(INDICE(f;SECUENCIA(16);SECUENCIA(;6));total2);resul)

Ya lo tenemos.

Miguel Angel Franco