Como montar fórmulas com conjuntos no Excel sem perder o resto do dia
A primeira vez que eu precisei trabalhar com formula conjuntos foi num relatório de estoque onde cada célula continha uma lista de produtos separada por vírgula e eu precisava cruzar esses dados com outra planilha. O resultado foi quase uma hora de tentativa e erro com SEERRE e PROCV funcionando em círculos. A solução que encontrei acabou sendo mais simples do que a maioria dos tutoriais sugere, mas só chegou nela depois de testar três abordagens diferentes. O que vou mostrar aqui é o método que funcionou na prática, incluindo o caso específico onde tudo deu errado e como resolvi.
Entendendo o conceito de formula conjuntos
Quando falo de formula conjuntos, estou me referindo ao uso de fórmulas que manipulam grupos de dados como coleções distintas — seja para união, interseção, diferença ou filtragem. No Excel, isso se traduz em funções como UNSORTED, INTERSECT, FILTER e nas versões mais recentes do Microsoft 365, em funções matriciais dinâmicas que tratam arrays como objetos primários. O problema é que a maioria dos materiais didáticos explica isso como se fosse matemática discreta. Na realidade, é pura manipulação de intervalos com consequências imprevisíveis quando um dos elementos está vazio ou contém texto onde se espera número.
O método prático
Vou começar pelo caso concreto. Eu tinha uma coluna A com listas de SKU separadas por ponto e vírgula, e uma coluna B com os mesmos SKUs em formato de matriz. Precisava identificar quais SKU apareciam em ambas as colunas. A abordagem ingênua seria usar CONTAINS ou procurar caractere por caractere, mas isso falha quando há SKUs como BR-100 e BR-1000 na mesma célula. A solução que adottai foi dividir os dados em linhas usando Text to Columns, criar uma tabela auxiliar com UNIQUE para remover duplicatas, e então aplicar INTERSECT entre os dois arrays. O passo final foi usar FILTER combinado com COUNTIFS para validar correspondências. No total, o processo leva cerca de 15 minutos para um conjunto de dados com até 5.000 itens, dependendo da velocidade do processador.
Ponto crítico: não use SEMPRE como substituto direto de INTERSECT. A função SEMPRE verifica se todos os valores de um intervalo estão presentes em outro, mas não retorna o resultado da interseção em si. Se você precisa dos valores em comum,.INTERSECT é a opção correta, mas ela só está disponível a partir do Excel 365.
Dica técnica que ninguém menciona
Antes de aplicar qualquer função de conjunto, garanta que os dados estejam no mesmo formato. Eu perdi cerca de 20 minutos porque um dos SKUs vinha com um espaço em branco invisível no final, gerado por uma exportação automática. A solução foi aplicar TRIM em ambos os arrays antes de qualquer operação. Isso reduz drasticamente falsos negativos em operações de comparação. Outro detalhe importante: quando trabalhar com formula conjuntos em versões mais antigas do Excel (2019 ou anterior), você precisará usar de INDEX + MATCH ou SUMPRODUCT. A sintaxe é mais verbosa, mas o resultado é idêntico. Um exemplo prático de interseção via SUMPRODUCT seria:
👉 Clique no botão abaixo para saber mais sobre o assunto!
=SUMPRODUCT((A:A=B:B)*A:A) Isso retorna a soma dos valores em A que também aparecem em B. Se precisar apenas dos valores únicos em comum, combine com UNIQUE ou elimine duplicatas manualmente.
Limitações e quando não usar
As funções de conjunto têm um problema claro: performance. Para intervalos maiores que 50.000 linhas, o cálculo pode levar de 30 segundos a vários minutos, dependendo da memória disponível. Nesse cenário, recomendo migrar os dados para o Power Query ou usar Python com pandas. A diferença é que o Power Query processa os conjuntos de forma stream-based, sem carregar tudo na memória de uma vez. Outro cenário onde formula conjuntos falham completamente é quando há dados faltantes não tratados. Se uma célula dentro do seu array estiver vazia, a função INTERSECT vai ignorá-la, mas COUNTIFS vai contar o vazio como um valor válido, gerando resultados inconsistentes. Sempre aplique FILTRO ou LIMPAR antes de operar.
Se você está lidando com conjuntos muito grandes e precisa de interseções frequentes, considere usar uma base de dados relacional com SQL. Operações de JOIN são nativamente otimizadas e escalam muito melhor do que qualquer solução baseada em fórmulas de planilha.
Download e recursos
Preparei um arquivo de exemplo com os três cenários descritos acima — interseção simples, interseção com limpeza de dados e a versão otimizada via Power Query. Você pode baixar o arquivo através do link abaixo: Baixar exemplo de formula conjuntos (Excel + Power Query)
O arquivo inclui abas separadas para cada método, com dados de teste e comentários explicando cada etapa. Use como referência quando estiver montando suas próprias fórmulas.
