Formar modelo a partir de codigos

Para el siguiente ejemplo, tenemos el siguiente modelo.

Vemos el código 1, que corresponde al centro El corte inglés, después, vemos 1.1, es decir se ha vendido el producto aspiradora en el corte inglés, por ejemplo, tenemos el código 4, que es el centro Carrefour, tenemos el código 4.1, es un televisor de 65 pulgadas vendido en Carrefour, después, tenemos 4.1.1, que es una accesorio del televisor de 65 pulgadas, como es soporte TV, tenemos el código 4.1.2, que es otro accesorio para televisor 65 pulgadas.

Debemos de crear el siguiente modelo.

Pues, ya tenemos la tarea a realizar.

Nos interesan aquellos códigos compuestos, es decir, primero el centro, y, después el producto, como 1.1, en la celda, en la celda D2, ponemos la función LET, creamos una variable, como valor para dicha variable, usamos la función LARGO, como argumento texto, seleccionamos el rango A2:A17, probamos variable.

=LET(a;LARGO(A2:A17);a)

Obtenemos una matriz desbordada con la longitud de cada cadena.

Nos interesan esos registros cuya longitud es mayor a 1, pues, después de la función LARGO comparamos con mayor a 1.

=LET(a;LARGO(A2:A17)>1;a)

Obtenemos VERDADERO donde es mayor a 1, y, FALSO donde no lo es.

Seguimos en la expresión de la variable “a”.

Usamos la función FILTRAR, como argumento array, seleccionamos el rango A2:A17, como argumento include, es la función LARGO.

LET(a;FILTRAR(A2:A17;LARGO(A2:A17)>1);a)

Obtenemos una matriz desbordada con aquellos códigos que son compuestos.

Creamos otra variable, usamos la función BUSCARX, como argumento valor buscado, usamos la función IZQUIERDA, como argumento texto, es la variable “a”, omitimos el argumento numero de caracteres, por lo que de forma predeterminada extraerá 1 carácter por la izquierda, como argumento matriz de búsqueda, volvemos a usar la función IZQUIERDA, como argumento texto, seleccionamos el rango A2:A17, omitimos el argumento número de caracteres, como argumento matriz devuelta, seleccionamos el rango B2:B17, probamos variable.

Obtenemos el centro comercial.

Creamos otra variable, volvemos a usar BUSCARX, como argumento valor buscado, volvemos a usar la función IZQUIERDA, como argumento texto, es la variable “a”, como argumento numero de caracteres, ponemos 3, es decir, vamos a extraer 1.1, o, 2.1, como argumento matriz de búsqueda, volvemos a usar la función IZQUIERDA, y, extraemos tres caracteres por la izquierda del rango A2:A17, como argumento matriz devuelta, seleccionamos el rango B2:B17, probamos variable.

LET(a;FILTRAR(A2:A17;LARGO(A2:A17)>1);b;BUSCARX(IZQUIERDA(a);IZQUIERDA(A2:A17);B2:B17);c;BUSCARX(IZQUIERDA(a;3);IZQUIERDA(A2:A17;3);B2:B17);c)

Obtenemos el producto.

Creamos otra variable, volvemos a usar la función BUSCARX, como argumento valor buscado, preguntamos su la longitud de la variable “a” es igual a 5.

=LET(a;FILTRAR(A2:A17;LARGO(A2:A17)>1);b;BUSCARX(IZQUIERDA(a);IZQUIERDA(A2:A17);B2:B17);c;BUSCARX(IZQUIERDA(a;3);IZQUIERDA(A2:A17;3);B2:B17);d;BUSCARX(SI(LARGO(a)=5;a;"")

Como argumento matriz de búsqueda, seleccionamos el rango A2:A17, como argumento matriz devuelta, seleccionamos el rango B2:B17, probamos variable.

=LET(a;FILTRAR(A2:A17;LARGO(A2:A17)>1);b;BUSCARX(IZQUIERDA(a);IZQUIERDA(A2:A17);B2:B17);c;BUSCARX(IZQUIERDA(a;3);IZQUIERDA(A2:A17;3);B2:B17);d;BUSCARX(SI(LARGO(a)=5;a;"");A2:A17;B2:B17;"");d)

Obtenemos los accesorios del producto que los tiene.

Como argumento calculo de LET, usamos la función APILAR, como argumento matriz1, ponemos la variable “b”, como argumento matriz2, ponemos la variable “c”, como argumento matriz3, ponemos la variable “d”.

Aceptamos.

=LET(a;FILTRAR(A2:A17;LARGO(A2:A17)>1);b;BUSCARX(IZQUIERDA(a);IZQUIERDA(A2:A17);B2:B17);c;BUSCARX(IZQUIERDA(a;3);IZQUIERDA(A2:A17;3);B2:B17);d;BUSCARX(SI(LARGO(a)=5;a;"");A2:A17;B2:B17;"");APILARH(b;c;d))

Ya tenemos nuestro ejemplo resuelto, vemos Carrefour, como producto TV 65 pulgadas, y, como accesorios, Soporte TV, Altavoces TV, y, barra de sonido.

Donde no tenemos accesorios, vemos el centro y producto, vemos que Metromar aparece cuatro veces, porque vendió cuatro productos.

Pero, queremos verlo de manera que aparezca cada centro, cada producto vendido en cada centro, y, si tiene accesorios, que aparezca una sola vez el centro, una vez el producto, y, en la siguiente fila, los accesorios, como sigue.

Lo vamos a realizar de la siguiente manera, en la celda H2, nos traemos los valores únicos de las columnas códigos, y, centros.

=UNICOS(D2:E13)

En la celda J2, comparamos si el valor de la celda H2 e I2, es igual al rango D2:E13, fijamos D2:E13.

=H2:I2=$D$2:$E$13

Tenemos una matriz desbordada con VERDADERO donde hay coincidencia, y, FALSO donde no lo hay.

Encerramos entre paréntesis la expresión anterior, y, antes de la apertura de paréntesis, ponemos el doble signo negativo.

Donde es VERDADERO aparece 1, y, donde es FALSO aparece 0.

Debemos de sumar los unos de cada columna, por lo que debemos de trabajar con cada fila de forma independiente, antes del doble signo negativo ponemos la función BYROW, como argumento array, es la expresión anterior.

=BYROW(--(H2:I2=$D$2:$E$13)

Como argumento función, ponemos LAMBDA, declaramos una variable, como argumento cálculo de LAMBDA, sumamos la variable.

Tenemos una matriz desbordada con la suma de los valores donde era VERDADERO.

Preguntamos que, si el resultado de la función BYROW es mayor a 1, que nos devuelva el rango F2:F13, y, fijamos.

=SI(BYROW(--(H2:I2=$D$2:$E$13);LAMBDA(x;SUMA(x)))>1;$F$2:$F$13;"")

Usamos la función UNIRCADENAS, como argumento delimitador, entre comillas dobles, ponemos coma, ignoramos celdas vacías, como argumento texto1, es la expresión anterior.

=UNIRCADENAS(",";VERDADERO;SI(BYROW(--(H2:I2=$D$2:$E$13);LAMBDA(x;SUMA(x)))>1;$F$2:$F$13;""))

Aceptamos, arrastramos, y, ya lo tenemos.

Copiamos encabezado y damos formato.

Ocultamos las columnas D, E, y, F.

Miguel Angel Franco