jueves, 4 de febrero de 2021

Sustitución múltiple con fórmulas en Excel

Una cuestión planteada recurrentemente en diferentes foros de Exce es la posibilidad de realizar múltiples sustituciones con fórmulas de una sola vez:
Sustitución múltiple con fórmulas en Excel


Esta sustitución múltiple es posible empleando la función XMLFILTRO (ya vista en articulos previos de este blog), y alguna función desbordada como SECUENCIA, y otras funciones clásicas como UNIRCADENAS, INDICE, BUSCARV o SI.ERROR.
Recomendado por tanto emplear versión de Microsoft 365!!.

Veamos lo que necesitamos...
En un rango A2:A6 las cadenas de texto donde aplicar nuestra sustitución múltiple.
Una tabla 'TblREEMPLAZO' donde tengamos qué deseamos reemplazar y por qué... No importa cuan larga sea la tabla!.
Y finalmente nuestra fórmula en B2:
=UNIRCADENAS(" ";0;
SI.ERROR(BUSCARV(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato");TblREEMPLAZO;2;0);
INDICE(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato");
SECUENCIA(LARGO(A2)-LARGO(SUSTITUIR(A2;" ";""))+1))))


O una versión más 'light':
=UNIRCADENAS(" ";0;
SI.ERROR(BUSCARV(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato");TblREEMPLAZO;2;0);
XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato")))


¿Un poco larga??... Pero muy potente!

Descompongámosla...

La clave como puedes comprobar es nuevamente el uso de XMLFILTRO, función que apareció en la versión 2013, y de poco conocimiento general... pero que a dia de hoy, con el comportamiento desbordado, con las fórmulas desbordadas, ha subido a un alto status entre las funciones.

Empezaremos analizando la fórmula:
XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato")
que nos retornaría una vector/matriz vertical de elementos, entendiendo elementos para nuestro caso por palabras:
Sustitución múltiple con fórmulas en Excel

En un segundo paso cada elemento o palabra recuperada la pasamos con un BUSCARV sobre nuestra TblREEMPLAZO:
BUSCARV(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato");TblREEMPLAZO;2;0)
Sustitución múltiple con fórmulas en Excel

En este paso hemos identificado cualquier palabra dentro de nuestra lista de reemplazos, y a la vez han sido sustituidas... Las palabras no encontradas devuelven lógicamente un #N/D.

Situación que controlamos en el siguiente paso con la función SI.ERROR:
SI.ERROR(BUSCARV(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato");TblREEMPLAZO;2;0);
INDICE(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato");
SECUENCIA(LARGO(A2)-LARGO(SUSTITUIR(A2;" ";""))+1)))

Sustitución múltiple con fórmulas en Excel

La idea de este paso es muy sencilla. Buscamos palabras a sustituir, y si no está en la lista nos quedamos con la palabra original.
La función INDICE combinada con SECUENCIA se encarga de retornar precisamente esa palabra no encontrada en la 'TblREEMPLAZO':
INDICE(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato");
SECUENCIA(LARGO(A2)-LARGO(SUSTITUIR(A2;" ";""))+1))


Recuerda que, en general, también se podría emplear simplemente:
XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato")

El último paso es el más obvio de todos, con UNIRCADENAS, juntamos cada palabra final obtenida con las fórmulas previas, y como vimos en una versión más abreviada:
=UNIRCADENAS(" ";0;
SI.ERROR(BUSCARV(XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato");TblREEMPLAZO;2;0);
XMLFILTRO("<ini><fila><dato>"&SUSTITUIR(A2;" ";"</dato><dato>")&"</dato></fila></ini>";"//ini/fila/dato")))


Y con la fórmula acabada podemos arrastrar en B2:B6 como necesitábamos.
Todo un éxito!!.

En definitiva hemos conseguido una búsqueda y sustitución 'recursiva', evitando el uso de anidaciones de SUSTITUIR.

No hay comentarios:

Publicar un comentario

Nota: solo los miembros de este blog pueden publicar comentarios.