Substitución de texto masivo con fórmulas

Supoñamos que tes unha lista na que, con distintos graos de "sencillez", se escriben os datos iniciais, por exemplo, enderezos ou nomes de empresas:

Vese claramente que a mesma cidade ou empresa está presente aquí en variantes abigarradas, o que, obviamente, creará moitos problemas ao traballar con estas táboas no futuro. E se pensas un pouco, podes atopar moitos exemplos de tarefas semellantes doutras áreas.

Agora imaxina que tales datos torcidos che chegan regularmente, é dicir, esta non é unha historia única de "resolvelo manualmente, esquéceo", senón un problema de forma regular e nun gran número de celas.

Que facer? Non substitúa manualmente o texto torcido 100500 veces polo correcto a través da caixa "Buscar e substituír" ou facendo clic Ctrl+H?

O primeiro que se me ocorre nunha situación deste tipo é facer unha substitución masiva segundo un libro de referencia precompilado de combinación de opcións incorrectas e correctas, como este:

Desafortunadamente, coa prevalencia obvia desta tarefa, Microsoft Excel non ten métodos integrados sinxelos para resolvelo. Para comezar, imos descubrir como facelo con fórmulas, sen implicar "artillaría pesada" en forma de macros en VBA ou Power Query.

Caso 1. Substitución total a granel

Comecemos cun caso relativamente sinxelo: unha situación na que cómpre substituír o texto torcido antigo por un novo. totalmente.

Digamos que temos dúas táboas:

No primeiro – os nomes variados orixinais das empresas. No segundo - un libro de referencia de correspondencia. Se atopamos no nome da empresa na primeira táboa algunha palabra da columna Atopar, entón cómpre substituír completamente este nome torto polo correcto - da columna Suplente segunda táboa de busca.

Por comodidade:

• Ambas táboas convértense en dinámicas ("intelixentes") mediante un atallo de teclado Ctrl+T ou equipo Inserir - Táboa (Inserir — Táboa).
  
• Na pestana que aparece Construtor (Deseño) primeira táboa nomeada Data, e a segunda táboa de referencia - Substitucións.

Para explicar a lóxica da fórmula, imos un pouco de lonxe.

Tomando como exemplo a primeira empresa da cela A2 e esquecéndonos temporalmente do resto das empresas, intentemos determinar que opción da columna Atopar reúnese alí. Para iso, seleccione calquera cela baleira na parte libre da folla e introduza alí a función ATOPAR (BUSCAR):

Esta función determina se se inclúe a subcadea dada (o primeiro argumento son todos os valores da columna Atopar) no texto fonte (a primeira empresa da táboa de datos) e debería mostrar o número ordinal do carácter desde o que se atopou o texto ou un erro se non se atopou a subcadea.

O truco aquí é que, dado que especificamos non un, senón varios valores como primeiro argumento, esta función tamén devolverá como resultado non un valor, senón unha matriz de 3 elementos. Se non tes a última versión de Office 365 que admita matrices dinámicas, despois de ingresar esta fórmula e facer clic en Entrar verás esta matriz xusto na folla:

Se tes versións anteriores de Excel, despois de facer clic en Entrar só veremos o primeiro valor da matriz de resultados, é dicir, erro #VALOR! (#VALOR!).

Non debes ter medo 🙂 De feito, a nosa fórmula funciona e aínda podes ver toda a gama de resultados se seleccionas a función introducida na barra de fórmulas e premes a tecla F9(non esquezas premer Escapepara volver á fórmula):

A matriz resultante de resultados significa que no nome orixinal da empresa torta (GK Morozko OAO) de todos os valores dunha columna Atopar atopou só o segundo (Morozko), e comezando polo 4o carácter consecutivo.

Agora imos engadir unha función á nosa fórmula VER(BUSCAR):

Esta función ten tres argumentos:

1. Valor desexado – pode usar calquera número suficientemente grande (o principal é que supera a lonxitude de calquera texto nos datos de orixe)
   
2. Vector_vista – o rango ou matriz onde buscamos o valor desexado. Aquí está a función introducida anteriormente ATOPAR, que devolve unha matriz {#VALUE!:4:#VALUE!}
   
3. Vector_resultados – o intervalo desde o que queremos devolver o valor se o valor desexado se atopa na cela correspondente. Aquí están os nomes correctos da columna Suplente nosa táboa de referencia.
   

A característica principal e non obvia aquí é que a función VER se non hai coincidencia exacta, sempre busca o valor máis pequeno (anterior) máis próximo. Polo tanto, especificando calquera número grande (por exemplo, 9999) como o valor desexado, forzaremos VER atopar a cela co número máis pequeno máis próximo (4) na matriz {#VALOR!:4:#VALOR!} e devolver o valor correspondente do vector de resultado, é dicir, o nome correcto da empresa da columna Suplente.

O segundo matiz é que, tecnicamente, a nosa fórmula é unha fórmula matricial, porque función ATOPAR devolve como resultados non un, senón unha matriz de tres valores. Pero xa que a función VER admite matrices fóra da caixa, entón non temos que introducir esta fórmula como unha fórmula matricial clásica, usando un atallo de teclado Ctrl+Desprazarse+Entrar. Un sinxelo será suficiente Entrar.

Iso é todo. Espero que entendas a lóxica.

Queda por transferir a fórmula rematada á primeira cela B2 da columna fixo - e a nosa tarefa está resolta!

Por suposto, con táboas comúns (non intelixentes), esta fórmula tamén funciona moi ben (non te esquezas da clave F4 e corrixindo as ligazóns pertinentes):

Caso 2. Substitución parcial a granel

Este caso é un pouco máis complicado. De novo temos dúas táboas "intelixentes":

A primeira táboa con enderezos mal escritos que hai que corrixir (chameino Datos2). A segunda táboa é un libro de consulta, segundo o cal cómpre facer unha substitución parcial dunha subcadea dentro do enderezo (chamei esta táboa Substitucións 2).

A diferenza fundamental aquí é que só cómpre substituír un fragmento dos datos orixinais; por exemplo, o primeiro enderezo é incorrecto “St. Petersburgo" á dereita “St. Petersburgo", deixando o resto do enderezo (código postal, rúa, casa) como está.

A fórmula acabada terá este aspecto (para facilitar a percepción, dividíno en cantas liñas usando alt+Entrar):

O traballo principal aquí realízao a función de texto estándar de Excel SUBSTITUTO (SUBSTITUTO), que ten 3 argumentos:

1. Texto de orixe: o primeiro enderezo incorrecto da columna Enderezo
2. O que buscamos: aquí usamos o truco coa función VER (BUSCAR)do xeito anterior para extraer o valor da columna Atopar, que se inclúe como un fragmento nun enderezo curvo.
3. Con que substituír: do mesmo xeito atopamos o valor correcto que lle corresponde na columna Suplente.

Introduza esta fórmula con Ctrl+Desprazarse+Entrar Tampouco é necesario aquí, aínda que é, de feito, unha fórmula matricial.

E vese claramente (ver erros #N/A na imaxe anterior) que unha fórmula deste tipo, por toda a súa elegancia, ten un par de inconvenientes:

• función SUBSTITUTE distingue entre maiúsculas e minúsculas, polo que "Spb" na penúltima liña non se atopou na táboa de substitución. Para resolver este problema, pode usar a función ZAMENIT (SUBSTITUÍR), ou traer previamente as dúas táboas ao mesmo rexistro.
• Se o texto é inicialmente correcto ou nel non hai fragmento que substituír (última liña), entón a nosa fórmula arroxa un erro. Este momento pódese neutralizar interceptando e substituíndo erros mediante a función SE ERRO (SE ERRO):

  

• Se o texto orixinal contén varios fragmentos do directorio á vez, entón a nosa fórmula substitúe só á última (na 8ª liña, Ligovsky «Avenida« cambiou a "pr-t", Pero "S-Pb" on “St. Petersburgo" xa non, porque “S-Pb” é máis alto no directorio). Este problema pódese resolver volvendo executar a nosa propia fórmula, pero xa ao longo da columna fixo:

  
  

Non é perfecto e engorroso nalgúns lugares, pero moito mellor que a mesma substitución manual, non? 🙂

PS

No seguinte artigo, descubriremos como implementar esa substitución masiva usando macros e Power Query.

• Como funciona a función SUBSTITUTE para substituír texto
  
• Buscar coincidencias de texto exactas usando a función EXACT
• Busca e substitución que distingue entre maiúsculas e minúsculas