Matrizes dinâmicas no Excel: FILTRO, CLASSIFICAR e ÚNICO explicados

Durante anos, tirar de uma tabela apenas as vendas de uma região, ordená-las ou obter uma lista sem repetições exigia filtros manuais, tabelas dinâmicas ou fórmulas enormes copiadas linha por linha. Com as matrizes dinâmicas, uma única fórmula digitada em uma célula retorna um intervalo inteiro de resultados, que se atualiza quando os dados mudam.

Aqui você vai ver como funcionam FILTRO, CLASSIFICAR e ÚNICO, como combiná-las e o que fazer quando aparece um erro de despejo. Todos os exemplos usam a mesma tabela, então você pode reproduzi-los em poucos minutos.

O que são matrizes dinâmicas e qual versão do Excel você precisa

Uma matriz dinâmica é o resultado de uma fórmula que ocupa várias células. Você digita a fórmula na primeira célula e o Excel “despeja” os resultados para baixo e para a direita, em quantas linhas e colunas forem necessárias. Se os dados de origem mudarem, o intervalo de resultados se ajusta automaticamente.

Essas funções estão disponíveis no Excel para Microsoft 365, no Excel 2021 e versões posteriores e no Excel para a Web. Elas não existem no Excel 2019 ou anteriores: se você abrir ali um arquivo que as usa, verá o erro #NOME? ou valores fixos que não são mais recalculados.

Antes de começar, tenha em mente três regras:

• A fórmula é digitada apenas na primeira célula. O restante do resultado aparece com uma borda azul quando você o seleciona.
• Não é possível editar nem apagar uma célula isolada do intervalo despejado. Para mudar algo, altere a fórmula de origem.
• Para se referir ao resultado completo em outra fórmula, use o operador # depois da primeira célula, por exemplo F2#.

A tabela de exemplo

Copie estes dados em uma planilha nova, começando na célula A1. As fórmulas das seções seguintes trabalham sobre o intervalo A2:D9.

FILTRO: extrair apenas as linhas que cumprem uma condição

A sintaxe é FILTRO(matriz; incluir; [se_vazia]). O primeiro argumento é o intervalo que você quer retornar, o segundo é uma condição avaliada linha por linha e o terceiro, opcional, define o que mostrar se nenhuma linha cumprir a condição.

Para obter todas as vendas da região Norte:

=FILTRO(A2:D9;C2:C9="Norte";"Sem resultados")

O resultado são quatro linhas completas (Cliente A, Cliente C, Cliente D e de novo Cliente C) com as quatro colunas.

Várias condições ao mesmo tempo

Dentro do argumento incluir não funcionam E nem OU, porque essas funções retornam um único valor, e não um por linha. Em vez disso, cada condição é escrita entre parênteses e elas são combinadas com operações:

• Para que todas sejam cumpridas (lógica E), multiplique: (C2:C9="Norte")*(D2:D9>500).
• Para que pelo menos uma seja cumprida (lógica OU), some: (C2:C9="Norte")+(C2:C9="Sul").

Vendas da região Norte acima de 500:

=FILTRO(A2:D9;(C2:C9="Norte")*(D2:D9>500);"Sem resultados")

A fórmula retorna duas linhas: Cliente D com 1250 e Cliente C com 870.

CLASSIFICAR: ordenar resultados sem mexer nos dados originais

A sintaxe é CLASSIFICAR(matriz; [índice_classificação]; [ordem_classificação]; [por_col]). O índice indica por qual coluna da matriz ordenar (1 é a primeira) e a ordem vale 1 para crescente, o padrão, ou -1 para decrescente.

=CLASSIFICAR(A2:D9;4;-1)

Retorna a tabela completa ordenada por valor, do maior para o menor: primeiro Cliente D (1250), depois Cliente A (910), Cliente B (890) e assim por diante até Cliente C com 45. A tabela original mantém a ordem.

Ordenar o resultado de um filtro

Como FILTRO retorna uma matriz, você pode passá-la diretamente para CLASSIFICAR. Assim você obtém as vendas do Norte do maior para o menor valor:

=CLASSIFICAR(FILTRO(A2:D9;C2:C9="Norte");4;-1)

ÚNICO: listas sem repetições com uma só fórmula

A sintaxe é ÚNICO(matriz; [por_col]; [exatamente_uma_vez]). Na forma mais simples, retorna cada valor distinto uma única vez, na ordem em que aparece pela primeira vez:

=ÚNICO(B2:B9)

O resultado é Teclado, Monitor, Mouse e Notebook.

Valores que aparecem exatamente uma vez

O terceiro argumento muda o comportamento: com VERDADEIRO, retorna apenas os valores que não se repetem nenhuma vez. Repare no ponto e vírgula duplo, que deixa o segundo argumento vazio:

=ÚNICO(A2:A9;;VERDADEIRO)

Retorna Cliente D e Cliente E, ou seja, os clientes com uma única compra.

Contar valores distintos

Se você envolver ÚNICO em CONT.VALORES, obtém quantos itens diferentes existem. Esta fórmula retorna 3, porque há três regiões distintas:

=CONT.VALORES(ÚNICO(C2:C9))

Combinar as três funções: um resumo por região

O verdadeiro potencial aparece ao aninhá-las. Por exemplo, para ver quais produtos foram vendidos na região Sul, sem repetir e em ordem alfabética:

=CLASSIFICAR(ÚNICO(FILTRO(B2:B9;C2:C9="Sul")))

O resultado é Monitor e Teclado. Com a mesma lógica, você pode montar um pequeno resumo de vendas por região que cresce sozinho:

1. Em F1 digite o cabeçalho Região e em G1 o cabeçalho Total.
2. Em F2 digite =CLASSIFICAR(ÚNICO(C2:C9)). Aparecerão Leste, Norte e Sul.
3. Em G2 digite =SOMASE(C2:C9;F2#;D2:D9). O operador # faz a soma ser calculada para cada região da lista despejada.
4. Confira os totais: Leste 970, Norte 2485 e Sul 1200.

Se uma nova região aparecer nos dados, a lista de F2 aumenta e a coluna G a acompanha sem copiar nenhuma fórmula.

Erros comuns e como resolvê-los

• #DESPEJAR!: há algo no intervalo onde os resultados devem aparecer (um valor, uma fórmula ou até um espaço) ou há células mescladas. Ao selecionar a fórmula, uma borda tracejada marca a área necessária. Esvazie-a ou mova a fórmula.
• #CALC!: FILTRO não encontrou nenhuma linha e você não informou o terceiro argumento. Adicione um texto como “Sem resultados” ou aspas vazias.
• #VALOR!: a matriz e a condição têm número de linhas diferente, por exemplo A2:D9 contra C2:C10.
• Fórmula dentro de uma tabela do Excel: fórmulas que despejam não funcionam dentro de uma tabela criada com Ctrl+T. Coloque-as fora, embora elas possam ler os dados da tabela normalmente.

Quando você precisa limpar o arquivo, não só consultá-lo

ÚNICO é perfeito para consultar valores distintos, mas não remove as linhas repetidas do arquivo original: a tabela de origem continua igual. Se você precisa entregar um arquivo limpo, ou usa uma versão do Excel sem matrizes dinâmicas, pode usar nossa ferramenta gratuita para remover duplicados: você envia o arquivo xlsx, xls, csv ou ods e baixa uma cópia sem linhas repetidas. E se quiser conferir quais linhas sumiram, pode comparar o arquivo original com o arquivo limpo.

Conclusão

FILTRO, CLASSIFICAR e ÚNICO substituem muitas tarefas que antes exigiam filtros manuais ou fórmulas copiadas linha por linha. Digite uma fórmula, deixe espaço livre para o resultado e aproveite o operador # para encadear cálculos. Comece com os exemplos deste guia nos seus próprios dados e, em poucos dias, você vai montar relatórios que se atualizam sozinhos.

Perguntas frequentes