Grupo_ID_compañia_precio

Tenemos una columna con un grupo, cada grupo lleva asignado una o más empresas junto con si ID, separadas por punto y coma, o, coma.

También, tenemos otra tabla con cada compañía y el precio.

Debemos de crear un modelo, donde en una primera columna, aparezca el grupo, en una segunda columna, el ID, en la tercera columna, la compañía, y, una ultima columna, con el precio de la compañía.

El modelo debe de quedar como sigue:

En la celda D2, usamos LET, vamos a usar SUSTITUIR para sustituir la coma por el punto y coma, para trabajar solo con el punto y coma, como argumento texto, seleccionamos el rango B2:B5, como argumento texto antiguo, entre comillas dobles, ponemos coma, como argumento texto nuevo, entre comillas dobles, ponemos punto y coma.

=LET(xx;SUSTITUIR(B2:B5;",";";");xx)

Aceptamos, y, tenemos el modelo separado por punto y coma.

Creamos otra variable, usamos REDUCE, como valor inicial, ponemos dobles comillas dobles, o sea, nada, como argumento array, ponemos la variable “xx”, ponemos LAMBDA, y, declaramos dos variables.

=LET(xx;SUSTITUIR(B2:B5;",";";");a;REDUCE("";xx;LAMBDA(x;y

Como argumento funcion de LAMBDA, ponemos DIVIDIRTEXTO, como argumento texto, ponemos la variable “y”, como argumento delimitador de columna, entre comillas dobles, ponemos dos puntos, como delimitador de fila, ponemos, entre comillas dobles, punto y coma.

=LET(xx;SUSTITUIR(B2:B5;",";";");a;REDUCE("";xx;LAMBDA(x;y;DIVIDIRTEXTO(y;":";";")));a)

¿Qué obtenemos?

Obtenemos la división de la última celda.

Porque ya sabemos que la funcion REDUCE, nos devuelve el ultimo valor, por ejemplo, teniendo los siguientes datos:

Si usamos REDUCE, como sigue:

=REDUCE(;E11:E15;LAMBDA(x;y;x+y))

Obtenemos 15.

Si en vez de REDUCE, usamos SCAN.

=SCAN(;E11:E15;LAMBDA(x;y;x+y))

Obtenemos:

Veamos el desarrollo de SCAN.

Valor inicial, entra valiendo cero, y, array, entre valiendo 1, como el calculo es “x” mas “y”, quiere decir que el resultado es 0 + 1, por lo que devuelve 1.

Ahora, valor inicial, vale 1, y, array, vale 2, el calculo es 1 + 2, por lo que nos devuelve 3.

Valor inicial, vale 3, y, array, vale 3, el calculo es 3 + 3, es decir, 6.

Valor inicial, vale 6, y, array vale 4, el calculo es 6 + 4, que es 10.

Por último, valor inicial, vale 10, y, array vale 5, el calculo es 10 + 5, que es igual a 15.

Pero, la funcion REDUCE, nos devuelve el ultimo calculo.

De ahí que nos devuelva la división de la última celda.

Si en la funcion REDUCE, en el argumento calculo de LAMBDA, usamos APILARV, como argumento matriz1, ponemos la variable “x”, como argumento matriz2, ponemos la variable “y”.

=REDUCE(;E14:E18;LAMBDA(x;y;APILARV(x;y)))

Obtenemos cada valor.

Vamos a modificar la funcion REDUCE, como argumento valor inicial, ponemos 0, y, a la variable “y” dentro de APILARV, sumamos 1.

=REDUCE(0;E14:E18;LAMBDA(x;y;APILARV(x;y+1)))

Obtenemos el valor inicial, y, cada valor de la variable “y” donde se le suma 1.

Quiere decir que, de esta manera, puede trabajar con cada celda de forma individual, por lo que vamos a volver a la expresión inicial.

=LET(xx;SUSTITUIR(B2:B5;",";";");a;REDUCE("";xx;LAMBDA(x;y;DIVIDIRTEXTO(y;":";";")));a)

Antes de DIVIDIRTEXTO, ponemos APILARV, como argumento matriz1, ponemos la variable “x”, como argumento matriz2, es la funcion DIVIDIRTEXTO.

=LET(xx;SUSTITUIR(B2:B5;",";";");a;REDUCE("";xx;LAMBDA(x;y;APILARV(x;DIVIDIRTEXTO(y;":";";"))));a)

Aceptamos, y, ahora, tenemos dividida cada celda.

Tenemos espacios de más, por lo que delante de APILARV, usamos ESPACIOS.

=LET(xx;SUSTITUIR(B2:B5;",";";");a;REDUCE("";xx;LAMBDA(x;y;ESPACIOS(APILARV(x;DIVIDIRTEXTO(y;":";";")))));a)

En la primera fila tenemos error, porque no hay operación que realizar, pues, antes de REDUCE, usamos EXCLUIR, como argumento matriz, es la funcion ESPACIOS, como argumento filas, ponemos 1.

=LET(xx;SUSTITUIR(B2:B5;",";";");a;EXCLUIR(REDUCE("";xx;LAMBDA(x;y;ESPACIOS(APILARV(x;DIVIDIRTEXTO(y;":";";")))));1);a)

Aceptamos.

Creamos otra variable, usamos BUSCARX, como argumento valor buscado, usamos la funcion TOMAR, como argumento matriz, ponemos la variable “a”, ignoramos el argumento filas, como argumento columnas, ponemos -1, es decir, vamos a trabajar con la columna de compañías, como argumento matriz de búsqueda, seleccionamos el rango A10:A21, como argumento matriz devuelta, seleccionamos el rango B10:B21.

=LET(xx;SUSTITUIR(B2:B5;",";";");a;EXCLUIR(REDUCE("";xx;LAMBDA(x;y;APILARV(x;ESPACIOS(DIVIDIRTEXTO(y;":";";")))));1);b;BUSCARX(TOMAR(a;;-1);A10:A21;B10:B21);b)

Ya tenemos el precio de cada compañía.

Creamos otra variable, usamos ENFILA, como argumento matriz, usamos TOMAR, como argumento matriz, ponemos la variable “a”, ignoramos el argumento filas, como argumento columnas, ponemos -1, para trabajar con las compañías en horizontal.

=LET(xx;SUSTITUIR(B2:B5;",";";");a;EXCLUIR(REDUCE("";xx;LAMBDA(x;y;APILARV(x;ESPACIOS(DIVIDIRTEXTO(y;":";";")))));1);b;BUSCARX(TOMAR(a;;-1);A10:A21;B10:B21);c;ENFILA(TOMAR(a;;-1));c)

Usamos ENCONTRAR, como argumento valor buscado, es la expresión anterior, como argumento dentro del texto, seleccionamos el rango B2:B5.

=LET(xx;SUSTITUIR(B2:B5;",";";");a;EXCLUIR(REDUCE("";xx;LAMBDA(x;y;APILARV(x;ESPACIOS(DIVIDIRTEXTO(y;":";";")))));1);b;BUSCARX(TOMAR(a;;-1);A10:A21;B10:B21);c;ENCONTRAR(ENFILA(TOMAR(a;;-1));B2:B5);c)

Obtenemos el numero de coincidencia con la primera letra, y, error donde no hay coincidencia.

Preguntamos que si el resultado de ENCONTRAR es número, que nos devuelva el rango A2:A5, en caso contrario, que devuelva un error.

=LET(xx;SUSTITUIR(B2:B5;",";";");a;EXCLUIR(REDUCE("";xx;LAMBDA(x;y;APILARV(x;ESPACIOS(DIVIDIRTEXTO(y;":";";")))));1);b;BUSCARX(TOMAR(a;;-1);A10:A21;B10:B21);c;SI(ESNUMERO(ENCONTRAR(ENFILA(TOMAR(a;;-1));B2:B5));A2:A5;NOD());c)

Obtenemos la letra donde corresponde, y, error donde no hay coincidencia.

Usamos ENCOL, como argumento matriz, es la expresión anterior, como argumento ignorar, seleccionamos 3, ignorar blancos y errores.

=LET(xx;SUSTITUIR(B2:B5;",";";");a;EXCLUIR(REDUCE("";xx;LAMBDA(x;y;APILARV(x;ESPACIOS(DIVIDIRTEXTO(y;":";";")))));1);b;BUSCARX(TOMAR(a;;-1);A10:A21;B10:B21);c;ENCOL(SI(ESNUMERO(ENCONTRAR(ENFILA(TOMAR(a;;-1));B2:B5));A2:A5;NOD());3);c)

Tenemos el grupo de cada compañía.

Usamos el argumento calculo de LET, ponemos APILARH, como argumento matriz1, ponemos la variable “c”, como argumento matriz2, ponemos la variable “a”, como argumento matriz3, ponemos la variable “b”.

=LET(xx;SUSTITUIR(B2:B5;",";";");a;EXCLUIR(REDUCE("";xx;LAMBDA(x;y;APILARV(x;ESPACIOS(DIVIDIRTEXTO(y;":";";")))));1);b;BUSCARX(TOMAR(a;;-1);A10:A21;B10:B21);c;ENCOL(TRANSPONER(SI(ESNUMERO(ENCONTRAR(ENFILA(TOMAR(a;;-1));B2:B5));A2:A5;NOD()));3);APILARH(c;a;b))

Aceptamos, y, ya lo tenemos.

Miguel Angel Franco