Ordenar palabras con números, y, palabras sin numeros

Veamos otro desafío de Excel BI, aunque hay cosas que he añadido.

Dicho desafío consiste en que en cada celda tenemos varias palabras, cada palabra al final va con un número, entonces, debemos de ordenar las palabras según el numero que contenga de menor a mayor, si el número es el mismo en diversas palabras, se sigue el orden en el que aparecen.

Empecemos…

En la celda B2, ponemos LET, creamos una variable, y, almacenamos la división del valor de la celda A2, para ello, usamos la función DIVIDIRTEXTO, y, probamos dicha variable.

=LET(a;DIVIDIRTEXTO(A2;" ");a)

Obtenemos una matriz desbordada en horizontal con cada palabra.

Creamos otra variable, donde con la función DERECHA nos traemos el ultimo carácter, usamos la función ABS para convertirlo en formato de número.

=LET(a;TRANSPONER(DIVIDIRTEXTO(A2;" "));b;ABS(DERECHA(a));b)

Obtenemos una matriz desbordada en vertical con el número de cada cadena.

Creamos otra variable, usamos APILARH, como argumento matriz1, es la variable “a”, como argumento matriz2, ponemos la variable “b”, cerramos paréntesis de APILARH, y, probamos la variable.

=LET(a;TRANSPONER(DIVIDIRTEXTO(A2;" "));b;ABS(DERECHA(a));c;APILARH(a;b);c)

Obtenemos una matriz de dos filas y dos columnas, en la primera columna tenemos los nombres de las cadenas, en la segunda columna tenemos el número de cada cadena.

Ordenamos de menor a mayor por la segunda columna, para ello, usamos la función ORDENAR, como argumento matriz, es la variable “c”, como argumento ordenar índice, ponemos la columna 2.

=LET(a;TRANSPONER(DIVIDIRTEXTO(A2;" "));b;ABS(DERECHA(a));c;APILARH(a;b);ORDENAR(c;2))

Vemos como tenemos ordenada la cadena.

Con la función INDICE, nos quedamos con la primera columna.

=LET(a;TRANSPONER(DIVIDIRTEXTO(A2;" "));b;ABS(DERECHA(a));c;APILARH(a;b);INDICE(ORDENAR(c;2);;1))

Unimos las cadenas con UNIRCADENAS, donde como argumento delimitador, ponemos un espacio, ignoramos celdas vacías, y, como argumento texto1, es la expresión anterior.

=LET(a;TRANSPONER(DIVIDIRTEXTO(A2;" "));b;ABS(DERECHA(a));c;APILARH(a;b);UNIRCADENAS(" ";VERDADERO;INDICE(ORDENAR(c;2);;1)))

Aceptamos, arrastramos, y, ya lo tenemos.

A partir de ahora, los siguientes desafíos son míos.

He rescatado la siguiente cadena del modelo, donde he añadido números a cada cadena.

Queremos hacer lo mismo que para el ejercicio anterior.

En la celda B2, dividimos la cadena por el delimitador espacio, usamos la función LET, y, creamos una variable (“a”).

=LET(a;DIVIDIRTEXTO(A2;" ");a)

Creamos otra variable (“b”) y sacamos la longitud de cada cadena dividida.

=LET(a;DIVIDIRTEXTO(A2;" ");b;LARGO(a);b)

Creamos otra variable (“c”), donde con la función EXTRAE vamos a extraer cada carácter de cada cadena, como argumento texto es la variable “a”.

=LET(a;DIVIDIRTEXTO(A2;" ");b;LARGO(a);c;EXTRAE(a

Como argumento posición inicial, usamos SECUENCIA, como argumento filas de SECUENCIA, debemos de extraer el primer valor mayor de los valores obtenidos en la variable “b”, para asegurarnos que se extraen todos los caracteres, para ello, usamos la función K.ESIMO.MAYOR, como argumento matriz es la variable “b”, como argumento K ponemos 1, cerramos paréntesis de K.ESIMO.MAYOR.

=LET(a;DIVIDIRTEXTO(A2;" ");b;LARGO(a);c;EXTRAE(a;SECUENCIA(K.ESIMO.MAYOR(b;1))

Como argumento numero de caracteres, ponemos 1, cerramos paréntesis, punto y coma, como argumento nombre4, ponemos la variable “c”, y, cerramos paréntesis.

=LET(a;DIVIDIRTEXTO(A2;" ");b;LARGO(a);c;EXTRAE(a;SECUENCIA(K.ESIMO.MAYOR(b;1));1);c)

Aceptamos, y, tenemos una matriz desbordada con varias columnas, en cada columna, están los caracteres extraídos de cada cadena.

Volvemos a la expresión de la variable “c”.

Debemos de saber la cantidad de números contenidos en cada cadena, esos serán los caracteres a extraer por la derecha, vamos a usar el código ASCII de cada carácter, el código del numero 0 es 48, y, el código del numero 9 es 57, debemos de encontrar un código que este entre 48 y 57, por lo que debemos de realizar dos preguntas, pero ya sabemos que el operador lógico Y no lo podemos usar de forma matricial, para poder usarlo, vamos a hacer uso de la función MAP, donde como argumento array es la función EXTRAE.

=LET(a;DIVIDIRTEXTO(A2;" ");b;LARGO(a);c;MAP(EXTRAE(a;SECUENCIA(K.ESIMO.MAYOR(b;1));1)

Como argumento función, ponemos LAMBDA, declaramos una variable (“x”), que va a almacenar la función EXTRAE.

=LET(a;DIVIDIRTEXTO(A2;" ");b;LARGO(a);c;MAP(EXTRAE(a;SECUENCIA(K.ESIMO.MAYOR(b;1));1);LAMBDA(x

Como argumento calculo de LAMBDA, ponemos el condicional SI, como argumento prueba lógica, ponemos el operador lógico Y, donde preguntamos si la variable “x” es mayor o igual a 48, y, menor o igual a 57.

=LET(a;DIVIDIRTEXTO(A2;" ");b;LARGO(a);c;MAP(EXTRAE(a;SECUENCIA(K.ESIMO.MAYOR(b;1));1);LAMBDA(x;SI(Y(CODIGO(x)>=48;CODIGO(x)<=57)

Como argumento valor si verdadero, ponemos la variable “x”, como argumento valor si falso, ponemos un texto en blanco.

Cerramos paréntesis.

=LET(a;DIVIDIRTEXTO(A2;" ");b;LARGO(a);c;MAP(EXTRAE(a;SECUENCIA(K.ESIMO.MAYOR(b;1));1);LAMBDA(x;SI(Y(CODIGO(x)>=48;CODIGO(x)<=57);x;"")));c)

Aceptamos.

Podemos ver que tenemos solo los números, como en el argumento filas de SECUENCIA, indicamos el valor máximo, que en este caso es 8, todas las cadenas fueron rellenadas hasta 8 celdas, aunque no tuvieran datos, porque hay cadenas que tienen una longitud menor a 8, por eso, nos aparece error.

Para quitar el error, usamos la función SI.ERROR.

=LET(a;DIVIDIRTEXTO(A2;" ");b;LARGO(a);c;SI.ERROR(MAP(EXTRAE(a;SECUENCIA(K.ESIMO.MAYOR(b;1));1);LAMBDA(x;SI(Y(CODIGO(x)>=48;CODIGO(x)<=57);x;"")));"");c)

Antes de la función SI.ERROR, ponemos la función LARGO.

=LET(a;DIVIDIRTEXTO(A2;" ");b;LARGO(a);c;LARGO(SI.ERROR(MAP(EXTRAE(a;SECUENCIA(K.ESIMO.MAYOR(b;1));1);LAMBDA(x;SI(Y(CODIGO(x)>=48;CODIGO(x)<=57);x;"")));""));c)

Nos aparece cero, donde es blanco, y, 1 que es la longitud de cada valor de cada celda.

Debemos de sumar los valores 1 de cada columna, por lo que debemos de trabajar con cada columna de forma independiente, por lo que vamos a hacer uso de la función BYCOL, como argumento array es la expresión anterior.

=LET(a;DIVIDIRTEXTO(A2;" ");b;LARGO(a);c;BYCOL(LARGO(SI.ERROR(MAP(EXTRAE(a;SECUENCIA(K.ESIMO.MAYOR(b;1));1);LAMBDA(x;SI(Y(CODIGO(x)>=48;CODIGO(x)<=57);x;"")));""))

Como argumento calculo, ponemos LAMBDA, declaramos una “x” que va a almacenar el argumento array.

=LET(a;DIVIDIRTEXTO(A2;" ");b;LARGO(a);c;BYCOL(LARGO(SI.ERROR(MAP(EXTRAE(a;SECUENCIA(K.ESIMO.MAYOR(b;1));1);LAMBDA(x;SI(Y(CODIGO(x)>=48;CODIGO(x)<=57);x;"")));""));LAMBDA(x;

Como argumento calculo de LAMBDA, sumamos la variable “x”.

=LET(a;DIVIDIRTEXTO(A2;" ");b;LARGO(a);c;BYCOL(LARGO(SI.ERROR(MAP(EXTRAE(a;SECUENCIA(K.ESIMO.MAYOR(b;1));1);LAMBDA(x;SI(Y(CODIGO(x)>=48;CODIGO(x)<=57);x;"")));""));LAMBDA(x;SUMA(x)));c)

Aceptamos, y, vemos la cantidad de números que hay en cada cadena.

Ya sabemos los caracteres a extraer por la derecha, pues, a la expresión de la variable “c”, antes de BYCOL, ponemos la función DERECHA, como argumento texto, es la variable “a”, como argumento número de caracteres, es la función BYCOL.

=LET(a;DIVIDIRTEXTO(A2;" ");b;LARGO(a);c;DERECHA(a;BYCOL(LARGO(SI.ERROR(MAP(EXTRAE(a;SECUENCIA(K.ESIMO.MAYOR(b;1));1);LAMBDA(x;SI(Y(CODIGO(x)>=48;CODIGO(x)<=57);x;"")));""));LAMBDA(x;SUMA(x))));c)

Usamos la función ENCOL, para transponer los datos.

=LET(a;DIVIDIRTEXTO(A2;" ");b;LARGO(a);c;ENCOL(DERECHA(a;BYCOL(LARGO(SI.ERROR(MAP(EXTRAE(a;SECUENCIA(K.ESIMO.MAYOR(b;1));1);LAMBDA(x;SI(Y(CODIGO(x)>=48;CODIGO(x)<=57);x;"")));""));LAMBDA(x;SUMA(x)))));c)

Estos valores vemos que se alinean a la izquierda, es decir, están en formato de texto, pues, antes de la función ENCOL, usamos la función ABS.

=LET(a;DIVIDIRTEXTO(A2;" ");b;LARGO(a);c;ABS(ENCOL(DERECHA(a;BYCOL(LARGO(SI.ERROR(MAP(EXTRAE(a;SECUENCIA(K.ESIMO.MAYOR(b;1));1);LAMBDA(x;SI(Y(CODIGO(x)>=48;CODIGO(x)<=57);x;"")));""));LAMBDA(x;SUMA(x))))));d;ORDENAR(APILARH(ENCOL(a);c);2);d)

Creamos otra variable “d”, usamos APILARH, como argumento matriz1, es la variable “a”, pero usamos ENCOL en dicha variable, como argumento matriz2, es la variable “c”, cerramos paréntesis.

=LET(a;DIVIDIRTEXTO(A2;" ");b;LARGO(a);c;ENCOL(DERECHA(a;BYCOL(LARGO(SI.ERROR(MAP(EXTRAE(a;SECUENCIA(K.ESIMO.MAYOR(b;1));1);LAMBDA(x;SI(Y(CODIGO(x)>=48;CODIGO(x)<=57);x;"")));""));LAMBDA(x;SUMA(x)))));d;APILARH(ENCOL(a);c);d)

Aceptamos, y, tenemos una matriz desbordada de dos columnas, en la primera columna, tenemos los nombres completos, y, en la segunda columna tenemos solo los valores numéricos.

Usamos la función ORDENAR, como argumento matriz es la función APILARH, como argumento ordenar índice, ponemos 2, es decir, que ordene por la columna 2 de menor a mayor.

=LET(a;DIVIDIRTEXTO(A2;" ");b;LARGO(a);c;ENCOL(DERECHA(a;BYCOL(LARGO(SI.ERROR(MAP(EXTRAE(a;SECUENCIA(K.ESIMO.MAYOR(b;1));1);LAMBDA(x;SI(Y(CODIGO(x)>=48;CODIGO(x)<=57);x;"")));""));LAMBDA(x;SUMA(x)))));d;ORDENAR(APILARH(ENCOL(a);c);2);d)

Aceptamos, y, ya tenemos los nombres ordenados de menor a mayor.

Con la función INDICE, nos quedamos con la primera columna.

=LET(a;DIVIDIRTEXTO(A2;" ");b;LARGO(a);c;ABS(ENCOL(DERECHA(a;BYCOL(LARGO(SI.ERROR(MAP(EXTRAE(a;SECUENCIA(K.ESIMO.MAYOR(b;1));1);LAMBDA(x;SI(Y(CODIGO(x)>=48;CODIGO(x)<=57);x;"")));""));LAMBDA(x;SUMA(x))))));d;INDICE(ORDENAR(APILARH(ENCOL(a);c);2);;1);d)

Usamos la función UNIRCADENAS, como argumento delimitador, ponemos un espacio, ignoramos celdas vacías, y, como argumento texto1, es la función INDICE.

=LET(a;DIVIDIRTEXTO(A2;" ");b;LARGO(a);c;ABS(ENCOL(DERECHA(a;BYCOL(LARGO(SI.ERROR(MAP(EXTRAE(a;SECUENCIA(K.ESIMO.MAYOR(b;1));1);LAMBDA(x;SI(Y(CODIGO(x)>=48;CODIGO(x)<=57);x;"")));""));LAMBDA(x;SUMA(x))))));d;UNIRCADENAS(" ";VERDADERO;INDICE(ORDENAR(APILARH(ENCOL(a);c);2);;1));d)

Aceptamos, y, ya lo tenemos.

El siguiente desafío es extraer cada cadena, pero sin los números, el modelo es el mismo que para el primer ejemplo.

Lo primero es dividir en cadenas independientes el valor de la celda A2, para ello, volvemos a usar la función DIVIDIRTEXTO.

=DIVIDIRTEXTO(A2;" ")

Si en una celda, usamos la función LARGO, y, como argumento es la matriz desbordada obtenida anteriormente, obtendremos otra matriz desbordada con la longitud de cada cadena.

Estos valores son los caracteres para extraer por la izquierda, pero restando 1.

La expresión quedaría:

=IZQUIERDA(DIVIDIRTEXTO(A2;" ");LARGO(DIVIDIRTEXTO(A2;" "))-1)

Usamos la función UNIRCADENAS, como argumento delimitador, ponemos un espacio ignoramos celdas vacías, y, como argumento texto1, es la expresión anterior.

=UNIRCADENAS(" ";VERDADERO;IZQUIERDA(DIVIDIRTEXTO(A2;" ");LARGO(DIVIDIRTEXTO(A2;" "))-1))

Aceptamos, arrastramos, y, ya lo tenemos.

Miguel Angel Franco