Toda semana, um novo arquivo Excel é gerado por um sistema CRM, que possui o carimbo de data no nome do arquivo, mas colocado em uma pasta em local fixo (uma URL do sharepoint).
Este arquivo é bastante grande e geralmente contém entre 25 e 30 mil linhas. Fora disso, preciso criar uma nova tabela, relevante para o meu propósito.
As linhas relevantes para o meu propósito devem corresponder a uma (ou mais) palavras-chave/frases-chave, de uma lista de 30 a 40 dessas palavras-chave/frases-chave. Esta lista de palavras-chave/frases-chave, entretanto, também cresce, mas lentamente, adicionando novas palavras-chave a cada poucos meses.
Existe alguma maneira de automatizar esta tarefa manual, tediosa e um tanto propensa a erros?
Responder1
Como três colunas diferentes (assumidas abaixo como B, De F) precisam ser verificadas, uma ORfunção parece apropriada:
=OR(ISNUMBER(MATCH(B1,keyarray,0)),
ISNUMBER(MATCH(D1,keyarray,0)),
ISNUMBER(MATCH(F1,keyarray,0)))
para que uma correspondência para qualquer uma das três colunas retorne TRUE. Coloque isso em uma coluna 'auxiliar' na primeira linha ocupada por dados (digamos Linha 1 ou ajuste B1e D1acima F1de acordo).
MATCHverifica se, por exemplo, o valor de B1existe em keyarray, onde esse é o nome fornecido para o intervalo que contém a lista de palavras-chave/frases-chave (cerca de 30-40 em número) – não precisa estar na mesma planilha ou pasta de trabalho mas se não, o caminho completo deve ser especificado e recomendado que a 'outra' pasta de trabalho seja aberta ao aplicar a fórmula a um novo lote de dados.
0força apenas uma correspondência exata (alternativamente -1para o menor valor maior ou igual a B1ou 1para o maior).
MATCHretorna a localização do valor encontrado na matriz (caso contrário, com parâmetro 0, #N/A). Isso é numérico, então ISNUMBERtesta um número (qualquer número) – para excluir #N/Aresultados.
Portanto, desde que qualquer um dos ou B1esteja no resultado será - caso contrário .D1F1keyarrayTRUEFALSE
Para copiar a fórmula em mais de 25-30 mil linhas convenientemente, onde algumas contêm células mescladas, coloque algo (digamos 'fim') na interseção da última linha ocupada e a coluna 'auxiliar' (para evitar expandir desnecessariamente o tamanho da planilha ). Copie a célula que contém a fórmula, selecione a célula imediatamente abaixo dela e então Ctrl+Shift+Down/Colar para preencher a coluna auxiliar para baixo para todas as linhas ocupadas, sem continuar após a última linha ocupada e substituindo 'fim'.
Filtre na coluna 'auxiliar' para TRUE, selecione todas as colunas ocupadas, copie e cole em uma nova planilha/pasta de trabalho. Exclua linhas em branco na nova planilha/pasta de trabalho e salve. (Também pode optar por excluir a coluna 'auxiliar' da fonte.)
Certifique-se de que, ao adicionar itens ocasionalmente ao keyarrayintervalo nomeado, cubra as adições.
Responder2
Esta é uma tarefa maior que pode ser resolvida de diversas maneiras. Mas resumindo, SIM, você pode automatizar isso.
Só para começar:
Você tem certeza absoluta de que o seu sistema CRM não pode fornecer essas informações que você deseja extrair diretamente?
Geralmente eles são baseados em um sistema de banco de dados, como SQL, e como você já está extraindo dados dele, poderá modificar essa saída para atender às suas necessidades.
Agora vamos às possibilidades com o Excel:
- Importando os dados básicos do seu CRM
você poderá estabelecer uma conexão de dados com seu CRM
você pode criar um código VBA para importar o arquivo mais recente em sua pasta ou para atualizar uma conexão de dados estabelecida para este novo arquivo
você pode abrir o arquivo mais recente via VBA e copiar os dados desejados em sua planilha de destino
...
- Se ainda necessário, filtragem dos dados
Eu sugiro que você examine filtros e filtros avançados, vlookup, countifs e exemplos de código vba. Há algumas dúvidas aqui e no stackoverflow que trata a filtragem de múltiplas condições, mas você teria que fornecer mais detalhes para resolver isso corretamente.
como sugestão: eu dividiria essa tarefa em obter os dados base e somente se ainda necessário filtrá-los. A meu ver, deveria ser possível obter os dados filtrados muito mais cedo no processo. Ou me diga o contrário :)