Suponha que você tenha uma lista na qual, com vários graus de “simplesidade”, os dados iniciais são escritos – por exemplo, endereços ou nomes de empresas:
 |  |
Vê-se claramente que a mesma cidade ou empresa está presente aqui em variantes heterogêneas, o que, obviamente, criará muitos problemas ao trabalhar com essas tabelas no futuro. E se você pensar um pouco, poderá encontrar muitos exemplos de tarefas semelhantes de outras áreas.
Agora imagine que esses dados tortuosos chegam a você regularmente, ou seja, essa não é uma história única de “conserte manualmente, esqueça”, mas um problema em uma base regular e em um grande número de células.
O que fazer? Não substitua manualmente o texto torto 100500 vezes pelo correto através da caixa “Localizar e substituir” ou clicando Ctrl+H?
A primeira coisa que vem à mente em tal situação é fazer uma substituição em massa de acordo com um livro de referência pré-compilado de correspondência de opções incorretas e corretas – assim:
Infelizmente, com a óbvia prevalência de tal tarefa, o Microsoft Excel não possui métodos internos simples para resolvê-la. Para começar, vamos descobrir como fazer isso com fórmulas, sem envolver “artilharia pesada” na forma de macros em VBA ou Power Query.
Caso 1. Substituição completa em massa
Vamos começar com um caso relativamente simples – uma situação em que você precisa substituir o texto torto antigo por um novo. totalmente.
Digamos que temos duas tabelas:
No primeiro – os nomes variegados originais das empresas. No segundo – um livro de referência de correspondência. Se encontrarmos no nome da empresa na primeira tabela qualquer palavra da coluna Para encontrar, então você precisa substituir completamente esse nome torto pelo correto - da coluna Substituto segunda tabela de pesquisa.
Por conveniência:
- Ambas as tabelas são convertidas em dinâmicas (“inteligentes”) usando um atalho de teclado Ctrl+T ou equipe Insira a tabela (Insira a tabela).
- Na aba que aparece Construtor (Desenhar) primeira tabela chamada Data, e a segunda tabela de referência – substituições.
Para explicar a lógica da fórmula, vamos um pouco mais longe.
Tomando a primeira empresa da célula A2 como exemplo e esquecendo temporariamente o resto das empresas, vamos tentar determinar qual opção da coluna Para encontrar encontra lá. Para fazer isso, selecione qualquer célula vazia na parte livre da planilha e insira a função lá ENCONTRAR (ACHAR):
Esta função determina se a substring dada está incluída (o primeiro argumento são todos os valores da coluna Para encontrar) no texto de origem (a primeira empresa da tabela de dados) e deve gerar o número ordinal do caractere do qual o texto foi encontrado ou um erro se a substring não for encontrada.
O truque aqui é que, como especificamos não um, mas vários valores como o primeiro argumento, essa função também retornará como resultado não um valor, mas um array de 3 elementos. Se você não tiver a versão mais recente do Office 365 que suporte matrizes dinâmicas, depois de inserir esta fórmula e clicar em Entrar você verá esta matriz diretamente na planilha:
Se você tiver versões anteriores do Excel, depois de clicar em Entrar veremos apenas o primeiro valor do array de resultados, ou seja, erro #VALUE! (#VALOR!).
Você não deve ter medo 🙂 Na verdade, nossa fórmula funciona e você ainda pode ver toda a matriz de resultados se selecionar a função inserida na barra de fórmulas e pressionar a tecla F9(só não se esqueça de pressionar Escpara voltar à fórmula):
A matriz de resultados resultante significa que no nome original da empresa torta (GK Morozko OAO) de todos os valores em uma coluna Para encontrar encontrou apenas o segundo (Morozko), e a partir do 4º caractere consecutivo.
Agora vamos adicionar uma função à nossa fórmula VEJA(OLHO PARA CIMA):
Esta função tem três argumentos:
- Valor desejado – você pode usar qualquer número suficientemente grande (o principal é que excede o comprimento de qualquer texto nos dados de origem)
- Vetor_visualizado – o intervalo ou array onde estamos procurando o valor desejado. Aqui está a função introduzida anteriormente ENCONTRAR, que retorna uma matriz {#VALUE!:4:#VALUE!}
- Vetor_resultados – o intervalo do qual queremos retornar o valor se o valor desejado for encontrado na célula correspondente. Aqui estão os nomes corretos da coluna Substituto nossa tabela de referência.
A característica principal e não óbvia aqui é que a função VEJA se não houver correspondência exata, sempre procura o menor valor (anterior) mais próximo. Portanto, especificando qualquer número robusto (por exemplo, 9999) como o valor desejado, forçaremos VEJA encontre a célula com o menor número mais próximo (4) na matriz {#VALUE!:4:#VALUE!} e retorne o valor correspondente do vetor de resultado, ou seja, o nome correto da empresa da coluna Substituto.
A segunda nuance é que, tecnicamente, nossa fórmula é uma fórmula de matriz, porque a função ENCONTRAR retorna como resultados não um, mas uma matriz de três valores. Mas como a função VEJA suporta matrizes prontas para uso, então não precisamos inserir esta fórmula como uma fórmula de matriz clássica - usando um atalho de teclado Ctrl+Shift+Entrar. Um simples será suficiente Entrar.
Isso é tudo. Espero que tenha entendido a lógica.
Resta transferir a fórmula finalizada para a primeira célula B2 da coluna Fixo – e nossa tarefa está resolvida!
Claro, com tabelas comuns (não inteligentes), esta fórmula também funciona muito bem (só não se esqueça da chave F4 e corrigindo os links relevantes):
Caso 2. Substituição parcial em massa
Este caso é um pouco mais complicado. Novamente temos duas tabelas “inteligentes”:
A primeira tabela com endereços tortos que precisam ser corrigidos (chamei Data2). A segunda tabela é um livro de referência, segundo o qual você precisa fazer uma substituição parcial de uma substring dentro do endereço (chamei esta tabela Substituições2).
A diferença fundamental aqui é que você precisa substituir apenas um fragmento dos dados originais – por exemplo, o primeiro endereço tem um “S. Petersburgo” a direita “S. Petersburgo”, deixando o restante do endereço (CEP, rua, casa) como está.
A fórmula final ficará assim (para facilitar a percepção, dividi em quantas linhas usando outro+Entrar):
O trabalho principal aqui é feito pela função de texto padrão do Excel SUBSTITUTO (SUBSTITUTO), que tem 3 argumentos:
- Texto de origem – o primeiro endereço torto da coluna Endereço
- O que estamos procurando – aqui usamos o truque com a função VEJA (OLHO PARA CIMA)da maneira anterior de extrair o valor da coluna Para encontrar, que é incluído como um fragmento em um endereço curvo.
- Com o que substituir – da mesma forma, encontramos o valor correto correspondente na coluna Substituto.
Digite esta fórmula com Ctrl+Shift+Entrar também não é necessário aqui, embora seja, de fato, uma fórmula de matriz.
E é claramente visto (veja os erros #N/A na imagem anterior) que tal fórmula, por toda a sua elegância, tem algumas desvantagens:
- função SUBSTITUTE diferencia maiúsculas de minúsculas, então “Spb” na penúltima linha não foi encontrado na tabela de substituição. Para resolver este problema, você pode usar a função ZAMENIT (SUBSTITUIR), ou trazer preliminarmente ambas as tabelas para o mesmo registro.
- Se o texto estiver inicialmente correto ou nele não há fragmento para substituir (última linha), então nossa fórmula gera um erro. Este momento pode ser neutralizado interceptando e substituindo erros usando a função SE ERRO (SE ERRO):
- Se o texto original contiver vários fragmentos do diretório de uma só vez, então nossa fórmula substitui apenas a última (na 8ª linha, Ligovsky «Avenida« alterado para "pr-t", Mas “S-Pb” on “S. Petersburgo” não mais, porque “S-Pb” é mais alto no diretório). Este problema pode ser resolvido executando novamente nossa própria fórmula, mas já ao longo da coluna Fixo:
Não é perfeito e complicado em alguns lugares, mas muito melhor do que a mesma substituição manual, certo? 🙂
PS
No próximo artigo, descobriremos como implementar essa substituição em massa usando macros e Power Query.
- Como a função SUBSTITUTE funciona para substituir o texto
- Encontrando correspondências de texto exatas usando a função EXACT
- Pesquisa e substituição com distinção entre maiúsculas e minúsculas (VLOOKUP com distinção entre maiúsculas e minúsculas)