Supongamos que tiene una lista en la que, con diversos grados de "sencilla", se escriben los datos iniciales, por ejemplo, direcciones o nombres de empresas:
 |  |
Se ve claramente que la misma ciudad o empresa está presente aquí en variantes abigarradas, lo que, obviamente, creará muchos problemas al trabajar con estas tablas en el futuro. Y si piensas un poco, puedes encontrar muchos ejemplos de tareas similares de otras áreas.
Ahora imagine que estos datos torcidos le llegan con regularidad, es decir, no se trata de una historia única de "arréglelo manualmente, olvídelo", sino de un problema regular y en una gran cantidad de celdas.
¿Qué hacer? No reemplace manualmente el texto torcido 100500 veces con el correcto a través del cuadro "Buscar y reemplazar" o haciendo clic en Ctrl+H?
Lo primero que viene a la mente en tal situación es hacer un reemplazo masivo de acuerdo con un libro de referencia precompilado de coincidencia de opciones incorrectas y correctas, como este:
Desafortunadamente, con la prevalencia obvia de tal tarea, Microsoft Excel no tiene métodos integrados simples para resolverla. Para empezar, averigüemos cómo hacer esto con fórmulas, sin involucrar "artillería pesada" en forma de macros en VBA o Power Query.
Caso 1. Reemplazo completo a granel
Comencemos con un caso relativamente simple: una situación en la que necesita reemplazar el texto antiguo torcido por uno nuevo. completamente.
Digamos que tenemos dos tablas:
En el primero, los nombres variados originales de las empresas. En el segundo – un libro de referencia de correspondencia. Si encontramos en el nombre de la empresa de la primera tabla alguna palabra de la columna Encontrar, entonces debe reemplazar completamente este nombre torcido con el correcto, de la columna Suplente segunda tabla de búsqueda.
Por conveniencia:
- Ambas tablas se convierten en dinámicas ("inteligentes") usando un atajo de teclado Ctrl+T o equipo Insertar – Tabla (Insertar — Tabla).
- En la pestaña que aparece Constructor (Diseño) primera tabla nombrada Datos, y la segunda tabla de referencia: Sustituciones.
Para explicar la lógica de la fórmula, vayamos un poco de lejos.
Tomando como ejemplo la primera empresa de la celda A2 y olvidándonos temporalmente del resto de empresas, intentemos determinar qué opción de la columna Encontrar se reúne allí. Para hacer esto, seleccione cualquier celda vacía en la parte libre de la hoja e ingrese la función allí ENCONTRAR (ENCONTRAR):
Esta función determina si se incluye la subcadena dada (el primer argumento son todos los valores de la columna Encontrar) en el texto de origen (la primera empresa de la tabla de datos) y debe generar el número ordinal del carácter desde el que se encontró el texto o un error si no se encontró la subcadena.
El truco aquí es que como especificamos no uno, sino varios valores como primer argumento, esta función también devolverá como resultado no un valor, sino una matriz de 3 elementos. Si no tiene la última versión de Office 365 que admita matrices dinámicas, luego de ingresar esta fórmula y hacer clic en Participar verá esta matriz directamente en la hoja:
Si tiene versiones anteriores de Excel, luego de hacer clic en Participar solo veremos el primer valor de la matriz de resultados, es decir, el error #¡VALOR! (#¡VALOR!).
No debe tener miedo 🙂 De hecho, nuestra fórmula funciona y aún puede ver toda la gama de resultados si selecciona la función ingresada en la barra de fórmulas y presiona la tecla F9(simplemente no olvide presionar Escpara volver a la fórmula):
La matriz de resultados resultante significa que en el nombre torcido original de la empresa (GK Morozko OAO) de todos los valores en una columna Encontrar solo encontre el segundo (Morožko), y comenzando desde el cuarto carácter consecutivo.
Ahora agreguemos una función a nuestra fórmula. VER(BUSCAR):
Esta función tiene tres argumentos:
- Valor deseado – puede usar cualquier número lo suficientemente grande (lo principal es que exceda la longitud de cualquier texto en los datos de origen)
- Vector_visto – el rango o matriz donde estamos buscando el valor deseado. Aquí está la función introducida anteriormente. ENCONTRAR, que devuelve una matriz {#¡VALOR!:4:#¡VALOR!}
- Vector_dE TRATAMIENTOS – el rango desde el que queremos devolver el valor si el valor deseado se encuentra en la celda correspondiente. Aquí están los nombres correctos de la columna. Suplente nuestra tabla de referencia.
La característica principal y no obvia aquí es que la función VER si no hay una coincidencia exacta, siempre busca el valor más pequeño (anterior) más cercano. Por lo tanto, al especificar cualquier número pesado (por ejemplo, 9999) como el valor deseado, forzaremos VER encuentre la celda con el número más pequeño más cercano (4) en la matriz {#VALUE!:4:#VALUE!} y devuelva el valor correspondiente del vector de resultados, es decir, el nombre correcto de la empresa de la columna Suplente.
El segundo matiz es que, técnicamente, nuestra fórmula es una fórmula matricial, porque función ENCONTRAR devuelve como resultado no uno, sino una matriz de tres valores. Pero dado que la función VER admite matrices listas para usar, entonces no tenemos que ingresar esta fórmula como una fórmula de matriz clásica, usando un atajo de teclado Ctrl+Shift+Participar. Bastará con uno sencillo Participar.
Eso es todo. Espero que entiendas la lógica.
Queda por transferir la fórmula terminada a la primera celda B2 de la columna. fijo – ¡y nuestra tarea está resuelta!
Por supuesto, con tablas ordinarias (no inteligentes), esta fórmula también funciona muy bien (simplemente no se olvide de la clave F4 y la fijación de los enlaces pertinentes):
Caso 2. Reemplazo parcial masivo
Este caso es un poco más complicado. Nuevamente tenemos dos tablas "inteligentes":
La primera tabla con direcciones escritas torcidas que necesita ser corregida (la llamé Data2). La segunda tabla es un libro de referencia, según el cual debe realizar un reemplazo parcial de una subcadena dentro de la dirección (llamé a esta tabla Sustituciones2).
La diferencia fundamental aquí es que necesita reemplazar solo un fragmento de los datos originales; por ejemplo, la primera dirección tiene una dirección incorrecta. "S t. Petersburgo” a la derecha "S t. Petersburgo”, dejando el resto de la dirección (código postal, calle, casa) como está.
La fórmula terminada se verá así (para facilitar la percepción, la dividí en cuántas líneas usando otro+Participar):
El trabajo principal aquí lo realiza la función de texto estándar de Excel SUSTITUIR (SUSTITUTO), que tiene 3 argumentos:
- Texto de origen: la primera dirección torcida de la columna Dirección
- Lo que estamos buscando – aquí usamos el truco con la función VER (BUSCAR)de la forma anterior de extraer el valor de la columna Encontrar, que se incluye como fragmento en una dirección curva.
- Con qué reemplazar: de la misma manera encontramos el valor correcto correspondiente en la columna Suplente.
Introduzca esta fórmula con Ctrl+Shift+Participar tampoco es necesario aquí, aunque, de hecho, es una fórmula matricial.
Y se ve claramente (ver errores #N/A en la foto anterior) que tal fórmula, a pesar de su elegancia, tiene un par de inconvenientes:
- Función SUSTITUIR distingue entre mayúsculas y minúsculas, por lo que "Spb" en la penúltima línea no se encontró en la tabla de reemplazo. Para resolver este problema, puede usar la función ZAMENITO (REEMPLAZAR), o traer ambas tablas preliminarmente al mismo registro.
- Si el texto es inicialmente correcto o en él no hay fragmento para reemplazar (última línea), entonces nuestra fórmula arroja un error. Este momento se puede neutralizar interceptando y reemplazando errores usando la función IFERROR (SI ERROR):
- Si el texto original contiene varios fragmentos del directorio a la vez, entonces nuestra fórmula reemplaza solo la última (en la octava línea, Ligovsky «Avenida« cambiado a "pr-t", Pero “S-Pb” on "S t. Petersburgo” ya no, porque “S-Pb” está más arriba en el directorio). Este problema se puede resolver volviendo a ejecutar nuestra propia fórmula, pero ya a lo largo de la columna fijo:
No perfecto y engorroso en algunos lugares, pero mucho mejor que el mismo reemplazo manual, ¿verdad? 🙂
PS
En el próximo artículo, descubriremos cómo implementar una sustitución masiva de este tipo mediante macros y Power Query.
- Cómo funciona la función SUSTITUIR para reemplazar texto
- Encontrar coincidencias de texto exactas usando la función EXACTA
- Búsqueda y sustitución que distingue entre mayúsculas y minúsculas (BUSCARV que distingue entre mayúsculas y minúsculas)