Conteúdo
A pesquisa de palavras-chave no texto de origem é uma das tarefas mais comuns ao trabalhar com dados. Vejamos sua solução de várias maneiras usando o seguinte exemplo:
Suponhamos que você e eu tenhamos uma lista de palavras-chave – os nomes das marcas de carros – e uma grande tabela de todos os tipos de peças de reposição, onde as descrições às vezes podem conter uma ou várias dessas marcas de uma só vez, se a peça de reposição couber em mais de uma marca de carro. Nossa tarefa é encontrar e exibir todas as palavras-chave detectadas nas células vizinhas por meio de um determinado caractere separador (por exemplo, uma vírgula).
Método 1. Consulta de energia
Claro, primeiro transformamos nossas tabelas em dinâmicas (“inteligentes”) usando um atalho de teclado Ctrl+T ou comandos Home – Formatar como uma tabela (Início — Formatar como Tabela), dê-lhes nomes (por exemplo Selosи Peças de reposição) e carregue um a um no editor do Power Query selecionando na guia Dados - Da Tabela/Intervalo (Dados — Da Tabela/Intervalo). Se você tiver versões mais antigas do Excel 2010-2013, nas quais o Power Query está instalado como um suplemento separado, o botão desejado estará na guia Consulta de energia. Se você tiver uma versão totalmente nova do Excel 365, o botão Da tabela/intervalo liguei lá agora Com folhas (Da folha).
Após carregar cada tabela no Power Query, voltamos ao Excel com o comando Home — Fechar e carregar — Fechar e carregar para… — Criar apenas conexão (Casa — Fechar e Carregar — Fechar e Carregar para… — Apenas criar conexão).
Agora vamos criar uma solicitação duplicada Peças de reposiçãoclicando com o botão direito sobre ele e selecionando Solicitação duplicada (consulta duplicada)e renomeie a solicitação de cópia resultante para Os resultados e continuaremos a trabalhar com ele.
A lógica das ações é a seguinte:
- Na guia Avançado Adicionando uma coluna escolha um time Coluna personalizada (Adicionar coluna — coluna personalizada) e digite a fórmula = Marcas. Após clicar em OK obteremos uma nova coluna, onde em cada célula haverá uma tabela aninhada com uma lista de nossas palavras-chave – marcas de montadoras:
- Use o botão com setas duplas no cabeçalho da coluna adicionada para expandir todas as tabelas aninhadas. Ao mesmo tempo, as linhas com descrições de peças de reposição serão multiplicadas por um múltiplo do número de marcas e obteremos todas as combinações possíveis de pares de “marca de peças de reposição”:
- Na guia Avançado Adicionando uma coluna escolha um time Coluna condicional (coluna condicional) e definir uma condição para verificar a ocorrência de uma palavra-chave (marca) no texto de origem (descrição da peça):
- Para tornar a pesquisa insensível, adicione manualmente o terceiro argumento na barra de fórmulas Compare.OrdinalIgnoreCase para a função de verificação de ocorrência Texto.Contém (se a barra de fórmulas não estiver visível, ela pode ser ativada na guia Avaliações):
- Filtramos a tabela resultante, deixando apenas alguns na última coluna, ou seja, correspondências e removemos a coluna desnecessária ocorrências.
- Agrupando descrições idênticas com o comando Agrupar por aba Transformação (Transformar — Agrupar por). Como uma operação de agregação, selecione Todas as linhas (Todas as linhas). Na saída, obtemos uma coluna com tabelas, que contém todos os detalhes de cada peça de reposição, incluindo as marcas das montadoras que precisamos:
- Para extrair as notas de cada peça, adicione outra coluna calculada na guia Adicionando uma Coluna – Coluna Personalizada (Adicionar coluna — coluna personalizada) e use uma fórmula composta por uma tabela (elas estão localizadas em nossa coluna Adicionar ao carrinho) e o nome da coluna extraída:
- Clicamos no botão com setas duplas no cabeçalho da coluna resultante e selecionamos o comando Extrair valores (Extrair valores)para imprimir carimbos com qualquer caractere delimitador que desejar:
- Removendo uma coluna desnecessária Adicionar ao carrinho.
- Para adicionar à tabela resultante as peças que dela desapareceram, onde não foram encontradas marcas nas descrições, realizamos o procedimento de combinação da consulta Resultado com pedido original Peças de reposição botão Combinar aba Início (Página inicial — Mesclar consultas). Tipo de conexão - Junção Externa Direita (junção externa direita):
- Tudo o que resta é remover as colunas extras e renomear e mover as restantes – e nossa tarefa está resolvida:
Método 2. Fórmulas
Se você possui uma versão do Excel 2016 ou posterior, nosso problema pode ser resolvido de maneira muito compacta e elegante usando a nova função COMBINAR (TEXTOIN):
A lógica por trás dessa fórmula é simples:
- função Pesquisar (ACHAR) procura a ocorrência de cada marca sucessivamente na descrição atual da peça e retorna o número de série do símbolo, a partir do qual a marca foi encontrada, ou o erro #VALUE! se a marca não estiver na descrição.
- Então usando a função IF (E SE) и EOSHIBKA (ERRO) substituímos os erros por uma string de texto vazia “”, e os números ordinais dos caracteres pelos próprios nomes das marcas.
- A matriz resultante de células vazias e marcas encontradas é montada em uma única string por meio de um determinado caractere separador usando a função COMBINAR (TEXTOIN).
Comparação de desempenho e buffer de consulta de consulta de energia para acelerar
Para testes de desempenho, vamos usar uma tabela com 100 descrições de peças sobressalentes como dados iniciais. Nele obtemos os seguintes resultados:
- Tempo de recálculo por fórmulas (Método 2) – 9 seg. quando você copia pela primeira vez a fórmula para a coluna inteira e 2 seg. em repetido (buffering afeta, provavelmente).
- O tempo de atualização da consulta do Power Query (Método 1) é muito pior – 110 segundos.
É claro que depende muito do hardware de um determinado PC e da versão instalada do Office e das atualizações, mas acho que o quadro geral é claro.
Para acelerar uma consulta do Power Query, vamos armazenar em buffer a tabela de pesquisa Selos, porque não muda no processo de execução da consulta e não é necessário recalculá-lo constantemente (como o Power Query de fato faz). Para isso usamos a função Tabela.Buffer da linguagem interna do Power Query M.
Para fazer isso, abra uma consulta Os resultados e na aba Avaliações aperte o botão Editor Avançado (Visualizar — Editor Avançado). Na janela que se abre, adicione uma linha com uma nova variável Marco 2, que será uma versão em buffer do diretório da montadora, e usar essa nova variável posteriormente no seguinte comando de consulta:
Após esse refinamento, a velocidade de atualização de nossa solicitação aumenta quase 7 vezes – até 15 segundos. Bem diferente 🙂
- Pesquisa de texto difuso no Power Query
- Substituição de texto em massa com fórmulas
- Substituição de texto em massa no Power Query com função List.Accumulate