Promedio par de ciudades

Tenemos dos columnas, en una primera columna tenemos una serie de ciudades, en una segunda columna, otra serie de ciudades, y, un costo para cada par de ciudades.

Debemos de crear un reporte, o, informe donde debe de aparecer el promedio para cada par de provincias, pero, cada par de provincias deben de aparecer de forma ordenada, por ejemplo, tenemos Nueva York y Múnich, pues, debe de aparecer Múnich y Nueva York.

En la celda E3, usamos LET, creamos una variable, seleccionamos el rango A2:A11, creamos otra variable, seleccionamos el rango B2:B11.

=LET(a;A2:A11;b;B2:B11

Creamos otra variable, usamos la funcion CODIGO, como argumento usamos la funcion IZQUIERDA, como argumento texto, ponemos la variable “a”, ignoramos el argumento numero de caracteres, por lo que va a extraer 1 carácter por la izquierda.

=LET(a;A2:A11;b;B2:B11;c;CODIGO(IZQUIERDA(a));c)

Tenemos una matriz desbordada en vertical con el código al que corresponde la primera letra de cada cadena.

Creamos otra variable, es la misma expresión que para la variable “c”, pero, cambiamos el argumento texto de la funcion IZQUIERDA por la variable “b”.

=LET(a;A2:A11;b;B2:B11;c;CODIGO(IZQUIERDA(a));d;CODIGO(IZQUIERDA(b));d)

Para ordenar cada par de ciudades, debemos de comparar los códigos de cada par de ciudades, si el código de la variable “c” es menor que el código de la variable “d”, quiere decir que están ordenados de menor a mayor, en caso contrario, quiere decir, que esta ordenado de mayor a menor.

Vamos a crear otra variable, usamos el condicional SI, como argumento prueba lógica, preguntamos si la variable “c” es menor que la variable “d”, como argumento valor si verdadero, poneos la variable “a”, concatenamos con un espacio, y, concatenamos con la variable “b”, como argumento valor si falso, ponemos la variable “b”, concatenamos con un espacio, y, concatenamos con la variable “a”.

=LET(a;A2:A11;b;B2:B11;c;CODIGO(IZQUIERDA(a));d;CODIGO(IZQUIERDA(b));e;SI(c<d;a&" "&b;b&" "&a);e)

Vemos que ya tenemos cada par de ciudades ordenadas.

Creamos otra variable, nos traemos los valores únicos de la variable “e”.

=LET(a;A2:A11;b;B2:B11;c;CODIGO(IZQUIERDA(a));d;CODIGO(IZQUIERDA(b));e;SI(c<d;a&" "&b;b&" "&a);f;UNICOS(e);f)

Creamos otra variable, y, seleccionamos el rango C2:C11.

=LET(a;A2:A11;b;B2:B11;c;CODIGO(IZQUIERDA(a));d;CODIGO(IZQUIERDA(b));e;SI(c<d;a&" "&b;b&" "&a);f;UNICOS(e);g;C2:C11;g)

Creamos otra variable, usamos la funcion MAP, como argumento array, ponemos la variable “f”, como argumento LAMBDA, ponemos LAMBDA, creamos una variable, usamos la funcion PROMEDIO, como argumento numero1, usamos el condicional SI, como argumento prueba lógica, preguntamos si la variable “e” es igual a la variable “x”, como argumento valor si verdadero, ponemos la variable “g”, omitimos el argumento valor si falso.

=LET(a;A2:A11;b;B2:B11;c;CODIGO(IZQUIERDA(a));d;CODIGO(IZQUIERDA(b));e;SI(c<d;a&" "&b;b&" "&a);f;UNICOS(e);g;C2:C11;h;MAP(f;LAMBDA(x;PROMEDIO(SI(e=x;g))));h)

Tenemos el promedio para cada par de ciudades únicas.

Usamos el argumento calculo de LET, usamos la funcion APILARH, como argumento matriz1, usamos la funcion TEXTOANTES, como argumento texto, ponemos la variable “f·, como argumento delimitador, entre comillas dobles, ponemos espacio, como argumento matriz2, usamos la funcion TEXTODESPUES, como argumento texto, ponemos la variable “f”, como argumento delimitador, ponemos un espacio, como argumento matriz3, ponemos la variable “h”.

=LET(a;A2:A11;b;B2:B11;c;CODIGO(IZQUIERDA(a));d;CODIGO(IZQUIERDA(b));e;SI(c<d;a&" "&b;b&" "&a);f;UNICOS(e);g;C2:C11;h;MAP(f;LAMBDA(x;PROMEDIO(SI(e=x;g))));APILARH(TEXTOANTES(f;" ");TEXTODESPUES(f;" ";);h))

Ya tenemos el modelo.

Por último, vamos a ordenar, para ello, usamos la función ORDENAR, como argumento matriz, es la funcion APILARH, como argumento ordenar índice, ponemos 1.

=LET(a;A2:A11;b;B2:B11;c;CODIGO(IZQUIERDA(a));d;CODIGO(IZQUIERDA(b));e;SI(c<d;a&" "&b;b&" "&a);f;UNICOS(e);g;C2:C11;h;MAP(f;LAMBDA(x;PROMEDIO(SI(e=x;g))));ORDENAR(APILARH(TEXTOANTES(f;" ");TEXTODESPUES(f;" ";);h);1))

Aceptamos, y, ya lo tenemos.

Miguel Angel Franco