Tabelas de resumo com matrizes dinâmicas

Uma tabela de resumo condensa uma grande lista de linhas brutas em um bloco pequeno e legível: uma linha por categoria, com os totais ao lado. Pense em um registro de vendas com centenas de linhas que se transforma em uma tabela organizada mostrando cada região e sua receita total.

O método antigo era uma tabela dinâmica manual que precisava ser atualizada. O método moderno usa fórmulas de matriz dinâmica que se atualizam no instante em que os dados mudam. Sem botões, sem atualização manual.

Nesta lição, você combinará três ferramentas poderosas: UNIQUE para listar as categorias, SUMIFS para totalizar cada uma e FILTER para buscar as linhas correspondentes. Juntas, elas criam um resumo dinâmico.

Imagine uma planilha chamada Vendas com três colunas: Região na coluna A, Produto na coluna B e Valor na coluna C, preenchendo as linhas de 2 a 200.

Nosso objetivo é criar um resumo que mostre cada região distinta e seu total de vendas. O primeiro desafio é obter uma lista limpa de regiões sem digitá-las manualmente, pois novas regiões podem aparecer mais tarde.

• A2:A200 contém muitos nomes de região repetidos, como Leste, Oeste, Leste, Norte.
• Queremos apenas: Leste, Oeste, Norte, cada um listado uma vez.

Essa lista distinta é a base de todo o resumo.

A função UNIQUE recebe um intervalo e retorna cada valor apenas uma vez. Ela se expande, ou seja, uma única fórmula preenche tantas células quantos forem os valores distintos.

Digite isto na célula E2, e a lista de regiões aparecerá automaticamente abaixo dela:

Se uma nova região for adicionada aos dados mais tarde, a lista expandida crescerá por conta própria. Você nunca precisa editar a fórmula.

Agora precisamos do Valor total de cada região na coluna E. SUMIFS soma valores de um intervalo somente quando outro intervalo corresponde a uma condição.

A estrutura é SUMIFS(sum_range, criteria_range, criteria). Coloque isto em F2, ao lado da primeira região:

A referência E2# é o detalhe crucial. O sinal # significa todo o intervalo expandido a partir de E2. Portanto, essa única fórmula totaliza todas as regiões geradas por UNIQUE.

A referência de expansão E2# sempre aponta para o bloco completo produzido por uma fórmula, independentemente de quanto ele cresça. É isso que torna o resumo dinâmico.

Quando UNIQUE encontra 3 regiões, E2# tem 3 células de altura e SUMIFS retorna 3 totais. Quando os dados crescem para 5 regiões, os dois intervalos se expandem juntos, sem nenhuma edição.

• E2 = apenas a célula superior.
• E2# = toda a matriz expandida que começa em E2.

Familiarize-se com o sinal #; ele é o coração das fórmulas de painéis.

Um resumo fica mais fácil de ler quando os totais estão ordenados. Envolva a lista de regiões com SORT para que as categorias apareçam em ordem alfabética ou classifique a tabela inteira pelo total.

Para listar as regiões em ordem alfabética em E2:

Como os totais em F ainda fazem referência a E2#, a classificação das regiões realinha automaticamente os totais. As duas colunas permanecem sincronizadas.

Às vezes, você quer ver as linhas originais de uma categoria, não apenas um total. FILTER retorna todas as linhas que atendem a uma condição e as expande.

Para mostrar todas as linhas de vendas em que Região é igual ao valor da célula H1:

Se H1 contiver Leste, você obterá todas as linhas de Leste. Altere H1 para Oeste, e o bloco se reescreverá instantaneamente. Essa é a base de uma visão de detalhamento em um painel.

FILTER gera um erro #CALC! quando nada corresponde. Para manter o resultado limpo, forneça o terceiro argumento opcional como uma mensagem alternativa.

O terceiro argumento é exibido quando não há correspondências:

Agora, uma região sem vendas exibe uma mensagem amigável em vez de um erro. Sempre adicione essa alternativa aos painéis para que uma seleção inesperada nunca desorganize o layout.

Um resumo geralmente mostra quantos pedidos cada região teve, não apenas o valor total. COUNTIFS conta as linhas que correspondem a uma condição, assim como SUMIFS, mas sem um intervalo de soma.

Coloque isto na coluna G, ao lado dos totais:

Agora seu resumo de três colunas apresenta Região, Total de vendas e Contagem de pedidos, todos orientados pela única lista de regiões expandida em E2#. Tudo é atualizado em conjunto.

Aqui está a receita completa, lado a lado:

• E2: =SORT(UNIQUE(Sales!A2:A200)) lista as regiões.
• F2: =SUMIFS(Sales!C2:C200, Sales!A2:A200, E2#) calcula o total de cada uma.
• G2: =COUNTIFS(Sales!A2:A200, E2#) conta cada uma.

Somente a fórmula de E2 é inserida nas linhas; F e G se expandem a partir da referência #. Adicione uma nova venda em qualquer lugar de Sales, e as três colunas serão atualizadas sem nenhum clique.

Um resumo orientado por fórmulas oferece vantagens reais em relação a digitar valores ou atualizar uma tabela dinâmica:

• Atualização em tempo real: ele recalcula no instante em que os dados mudam.
• Dimensionamento automático: novas categorias aparecem automaticamente por meio de UNIQUE e da referência #.
• Transparência: qualquer pessoa pode ler a lógica na célula.

A desvantagem é que os intervalos expandidos precisam de espaço vazio para crescer; abordaremos as expansões bloqueadas em uma lição posterior. Por enquanto, deixe espaço abaixo das fórmulas.

Teste o que você aprendeu sobre a criação de uma tabela de resumo que se atualiza sozinha.

Você criou uma tabela de resumo que se mantém atualizada:

• UNIQUE lista cada categoria uma vez e expande o resultado.
• SORT ordena essa lista para facilitar a leitura.
• SUMIFS e COUNTIFS totalizam e contam cada categoria usando a referência de expansão E2#.
• FILTER busca as linhas correspondentes para um detalhamento, com uma mensagem alternativa quando não há correspondências.

Como todas as fórmulas usam a lista expandida como referência, adicionar novos dados atualiza todo o resumo sem nenhuma etapa manual. A seguir, você recriará relatórios completos no estilo de tabelas dinâmicas usando apenas fórmulas.