Conteúdo
Eu já escrevi sobre como você pode colar rapidamente o texto de várias células em uma e, inversamente, analisar uma longa string de texto em componentes. Agora vamos ver uma tarefa próxima, mas um pouco mais complexa – como colar texto de várias células quando uma determinada condição especificada é atendida.
Digamos que temos um banco de dados de clientes, onde o nome de uma empresa pode corresponder a vários e-mails diferentes de seus funcionários. Nossa tarefa é coletar todos os endereços por nomes de empresas e concatená-los (separados por vírgulas ou ponto e vírgula) para fazer, por exemplo, uma lista de discussão para clientes, ou seja, obter saída algo como:
Em outras palavras, precisamos de uma ferramenta que irá colar (linkar) o texto de acordo com a condição – um análogo da função SUMMESLI (SOMA), mas para texto.
Método 0. Fórmula
Não muito elegante, mas a maneira mais fácil. Você pode escrever uma fórmula simples que verificará se a empresa na próxima linha difere da anterior. Se não for diferente, cole o próximo endereço separado por uma vírgula. Se for diferente, “reinicializamos” o acumulado, começando de novo:
As desvantagens dessa abordagem são óbvias: de todas as células da coluna adicional obtidas, precisamos apenas das últimas para cada empresa (amarela). Se a lista for grande, para selecioná-los rapidamente, você precisará adicionar outra coluna usando a função DLSTR (LEN), verificando o comprimento das strings acumuladas:
Agora você pode filtrar os e copiar o endereço de colagem necessário para uso posterior.
Método 1. Macrofunção de colagem por uma condição
Se a lista original não for classificada por empresa, a fórmula simples acima não funcionará, mas você poderá facilmente contornar uma pequena função personalizada no VBA. Abra o Editor do Visual Basic pressionando um atalho de teclado Alt + F11 ou usando o botão Visual Basic aba revelador (Desenvolvedor). Na janela que se abre, insira um novo módulo vazio através do menu Inserir - Módulo e copie o texto da nossa função lá:
Function MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " colagens não são iguais entre si - saímos com um erro Se SearchRange.Count <> TextRange.Count Then MergeIf = CVErr(xlErrRef) Exit Function End If 'passe por todas as células, verifique a condição e colete o texto na variável OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'exibe os resultados sem o último delimitador MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End função
Se você agora retornar ao Microsoft Excel, na lista de funções (botão fx na barra de fórmulas ou guia Fórmulas - Inserir Função) será possível encontrar nossa função MesclarSe na categoria Usuário definido (Usuário definido). Os argumentos da função são os seguintes:
Método 2. Concatenar texto por condição inexata
Se substituirmos o primeiro caractere na 13ª linha de nossa macro = para o operador de correspondência aproximada Como, então será possível realizar a colagem por uma correspondência inexata dos dados iniciais com o critério de seleção. Por exemplo, se o nome da empresa puder ser escrito em diferentes variantes, podemos verificar e coletar todos eles com uma função:
Os curingas padrão são suportados:
- asterisco (*) – denota qualquer número de caracteres (incluindo sua ausência)
- ponto de interrogação (?) – representa qualquer caractere único
- sinal de libra (#) – representa qualquer dígito (0-9)
Por padrão, o operador Like diferencia maiúsculas de minúsculas, ou seja, entende, por exemplo, “Orion” e “orion” como empresas diferentes. Para ignorar maiúsculas e minúsculas, você pode adicionar a linha no início do módulo no editor do Visual Basic Opção Comparar Texto, que mudará Like para não diferenciar maiúsculas de minúsculas.
Dessa forma, você pode compor máscaras muito complexas para verificar condições, por exemplo:
- ?1##??777RUS – seleção de todas as placas da região 777, começando com 1
- LLC* – todas as empresas cujo nome começa com LLC
- ##7## – todos os produtos com código digital de cinco dígitos, onde o terceiro dígito é 7
- ????? – todos os nomes de cinco letras, etc.
Método 3. Função macro para colar texto em duas condições
No trabalho pode haver um problema quando você precisa vincular o texto mais de uma condição. Por exemplo, vamos imaginar que em nossa tabela anterior, foi adicionada mais uma coluna com a cidade, e a colagem deve ser realizada não apenas para uma determinada empresa, mas também para uma determinada cidade. Nesse caso, nossa função terá que ser um pouco modernizada adicionando outra verificação de intervalo a ela:
Função MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimiter As String, i As Long Delimiter = ", " 'caracteres delimitadores (podem ser substituídos por espaço ou ; etc.) e.) 'se os intervalos de validação e colagem não forem iguais entre si, saia com um erro If SearchRange1.Count <> TextRange.Count Ou SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End If 'passe por todas as células, verifique todas as condições e colete o texto na variável OutText For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Then OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'exibe os resultados sem o último delimitador MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Ele será aplicado exatamente da mesma maneira - apenas os argumentos agora precisam ser especificados mais:
Método 4. Agrupamento e colagem no Power Query
Você pode resolver o problema sem programar em VBA, se usar o suplemento Power Query gratuito. Para o Excel 2010-2013 pode ser baixado aqui, e no Excel 2016 já vem embutido por padrão. A sequência de ações será a seguinte:
O Power Query não sabe trabalhar com tabelas normais, então o primeiro passo é transformar nossa tabela em uma tabela “inteligente”. Para fazer isso, selecione-o e pressione a combinação Ctrl+T ou selecione na guia Home – Formatar como uma tabela (Início — Formatar como Tabela). Na guia que aparece Construtor (Desenhar) você pode definir o nome da tabela (deixei o padrão tabela 1):
Agora vamos carregar nossa tabela no suplemento do Power Query. Para isso, na aba Data (se você tiver o Excel 2016) ou na guia Power Query (se você tiver o Excel 2010-2013), clique em Da mesa (Dados - Da Tabela):
Na janela do editor de consultas que se abre, selecione a coluna clicando no cabeçalho Empresa e pressione o botão acima Grupo (Agrupar por). Digite o nome da nova coluna e o tipo de operação no agrupamento – Todas as linhas (Todas as Linhas):
Clique em OK e obtemos uma minitabela de valores agrupados para cada empresa. O conteúdo das tabelas é claramente visível se você clicar com o botão esquerdo do mouse no fundo branco das células (não no texto!) na coluna resultante:
Agora vamos adicionar mais uma coluna, onde, usando a função, colamos o conteúdo das colunas Address em cada uma das mini-tabelas, separadas por vírgulas. Para isso, na aba Adicionar coluna nós pressionamos Coluna personalizada (Adicionar coluna — coluna personalizada) e na janela que aparece, insira o nome da nova coluna e a fórmula de acoplamento na linguagem M incorporada ao Power Query:
Observe que todas as funções M diferenciam maiúsculas de minúsculas (ao contrário do Excel). Após clicar em OK obtemos uma nova coluna com endereços colados:
Resta remover a coluna já desnecessária Endereços de tabela (clique com o botão direito no título) Apagar coluna) e faça o upload dos resultados para a planilha clicando na guia Home — Fechar e baixar (Início — Fechar e carregar):
Nuance importante: Ao contrário dos métodos anteriores (funções), as tabelas do Power Query não são atualizadas automaticamente. Se no futuro houver alterações nos dados de origem, você precisará clicar com o botão direito do mouse em qualquer lugar da tabela de resultados e selecionar o comando Atualizar e salvar (Atualizar).
- Como dividir uma string de texto longa em partes
- Várias maneiras de colar texto de células diferentes em uma
- Usando o operador Like para testar o texto em uma máscara