Uma fórmula, vários resultados: listas que estouram para as células abaixo.
Excel moderno e agilidade · 5 exercícios curtos em uma planilha ao vivo: você digita cada fórmula sozinho e responda perguntas que façam você prever resultados ou encontrar erros. Cada resposta é verificada na hora.
O que você vai praticar
Listando valores únicos com UNIQUE
Classificando valores com SORT
Filtrando linhas com FILTER
Filtrando números acima de um limite
Por que o intervalo de estouro precisa estar vazio
Com matrizes dinâmicas, uma única fórmula pode retornar vários valores, e eles preenchem automaticamente as células abaixo ou ao lado dela. Isso se chama estouro. Ele transforma muitas tarefas antigas, com várias etapas, como montar uma lista ordenada de valores distintos, em uma única fórmula.
Uma fórmula, muitas células
Imagine uma lista de abrigo de animais: A2:A7 contém o tipo de animal (Cachorro, Gato, Cachorro, Coelho, Gato, Cachorro) e B2:B7 contém a idade (3, 5, 2, 1, 7, 4). Digite =ÚNICO(A2:A7) em D2 e o Excel preenche D2:D4 com Cachorro, Gato e Coelho. Você digitou apenas uma fórmula. O contorno colorido em volta de D2:D4 é o intervalo de estouro.
Para se referir a todo o estouro, use o sinal #: =CONT.VALORES(D2#) conta quantos itens existem agora, mesmo que os dados mudem depois.
Idades da maior para a menor (7, 5, 4, 3, 2, 1). Animais com idade maior que 3 (Gato, Gato, Cachorro). Tipos de animal distintos em ordem alfabética (Cachorro, Coelho, Gato).
CLASSIFICAR (Inglês: SORT) recebe o intervalo, a coluna pela qual ordenar e 1 para crescente ou -1 para decrescente. Para classificar a tabela inteira por idade, informe as duas colunas: =CLASSIFICAR(A2:B7;2;-1). ÚNICO (Inglês: UNIQUE) retorna cada valor uma vez. FILTRO (Inglês: FILTER) mantém as linhas em que o teste é VERDADEIRO.
Para duas condições, multiplique os testes, o que equivale a E: =FILTRO(B2:B7;(A2:A7="Cachorro")*(B2:B7>2)) retorna as idades 3 e 4. Se nada corresponder, FILTRO (Inglês: FILTER) retorna #CALC!. Adicione um terceiro argumento para escolher sua própria mensagem: =FILTRO(A2:A7;B2:B7>10;"nenhum").
Encadeando as funções
Como cada função retorna uma lista, você pode alimentar uma na seguinte. Comece de novo com a lista de abrigo (A2:A7 animal, B2:B7 idade). Para obter apenas os animais com idade maior que 2, os mais velhos primeiro, com as duas colunas à vista, aninhe o filtro dentro de uma classificação:
=CLASSIFICAR(FILTRO(A2:B7;B2:B7>2);2;-1)
O filtro mantém quatro linhas (Cachorro 3, Gato 5, Gato 7, Cachorro 4). Depois, a classificação ordena pela segunda coluna, do maior para o menor: Gato 7, Gato 5, Cachorro 4, Cachorro 3.
Leia fórmulas aninhadas de dentro para fora: primeiro o filtro, depois a classificação. Se uma cadeia longa for difícil de acompanhar, monte-a em etapas, em células separadas, e combine-as quando cada etapa estiver certa.
O erro #DESPEJAR!
#DESPEJAR! significa que as células para onde o resultado quer ir não estão vazias. Limpe a célula que está bloqueando, ou mova a fórmula. O mesmo erro aparece se você colocar uma fórmula de estouro dentro de uma Tabela do Excel, porque tabelas não podem conter estouros. Mantenha as fórmulas de estouro ao lado da tabela.
Arquivos antigos e o sinal @
Em uma pasta de trabalho criada em uma versão antiga do Excel, você pode ver um sinal @ dentro de uma fórmula. Ele marca o comportamento antigo de um único valor, chamado interseção implícita. O Excel o adiciona para que o arquivo continue dando exatamente as respostas de sempre. Em uma fórmula nova, normalmente você não precisa dele: escreva a fórmula sem @ e deixe-a estourar.
Dicas
Matrizes dinâmicas existem no Excel 2021, no Excel 2024 e no Microsoft 365. Versões mais antigas mostram #NOME?.
Não digite dentro de um intervalo de estouro. Você só pode editar a fórmula na célula do canto superior esquerdo. Para manter valores fixos, copie o estouro e cole como valores.
Combine-as: =CONT.VALORES(ÚNICO(A2:A7)) retorna 3, o número de tipos de animal distintos.