Relatórios no estilo de tabela dinâmica com fórmulas

Uma tabela dinâmica cruza dados: linhas para uma categoria, colunas para outra e totais preenchendo a grade. Um exemplo clássico é Região na lateral, Trimestre no topo e Vendas em cada célula.

As tabelas dinâmicas são excelentes, mas precisam ser atualizadas manualmente e ficam em um bloco fixo. Uma tabela dinâmica baseada em fórmulas se reconstrói em tempo real sempre que os dados mudam.

Nesta lição, você organizará cabeçalhos de linha, cabeçalhos de coluna e um corpo de fórmulas SUMIFS que calcula automaticamente cada interseção.

Usaremos uma planilha chamada Vendas com estas colunas: Região em A, Trimestre em B e Valor em C, nas linhas de 2 a 500.

O relatório que queremos tem esta aparência:

• Rótulos de linha: cada Região distinta na coluna E.
• Rótulos de coluna: T1, T2, T3, T4 na linha 1, de F a I.
• Corpo: o Valor total para cada combinação de Região e Trimestre.

Cada célula do corpo responde a uma pergunta: quanto essa região vendeu nesse trimestre?

Os cabeçalhos de linha são as regiões distintas. Use UNIQUE com SORT para que elas se expandam pela coluna E e permaneçam ordenadas.

Coloque isto em E2:

As regiões agora preenchem E2 e as células abaixo automaticamente. Assim como nas tabelas de resumo, essa lista é a base à qual toda a grade retorna.

Os cabeçalhos de coluna são os trimestres distribuídos por uma linha. Você pode digitar T1, T2, T3, T4 manualmente ou expandi-los horizontalmente usando TRANSPOSE em torno de UNIQUE.

Em F1, isto organiza os trimestres distintos na parte superior:

TRANSPOSE transforma uma lista vertical em uma lista horizontal, fazendo com que uma coluna de trimestres se torne uma linha de cabeçalhos. Agora os dois eixos da grade estão definidos.

Agora, preencha o corpo. Cada célula precisa do total da região da sua linha e do trimestre da sua coluna. SUMIFS lida facilmente com duas condições.

Na primeira célula do corpo, F2, escreva:

Isso soma o Valor quando Região é igual ao rótulo à esquerda e Trimestre é igual ao cabeçalho acima. É uma única interseção da tabela dinâmica.

Os cifrões permitem que uma única fórmula preencha toda a grade ao ser copiada. Observe as referências mistas:

• $E2 fixa a coluna em E, mas permite que a linha mude, para que cada linha leia sua própria região.
• F$1 fixa a linha em 1, mas permite que a coluna mude, para que cada coluna leia seu próprio trimestre.
• $C$2:$C$500 é totalmente fixa, pois o intervalo de dados nunca muda de posição.

Copie F2 por todos os trimestres e por todas as regiões; cada célula se ajustará perfeitamente.

Com F2 escrita corretamente, selecione-a e arraste a alça de preenchimento para a direita, por todas as colunas de trimestres, e depois para baixo, por todas as linhas de regiões. O Excel reescreve as partes relativas para você.

• A célula G2 se torna Região $E2 e Trimestre G$1.
• A célula F3 se torna Região $E3 e Trimestre F$1.

O resultado é uma tabela cruzada completa, com todas as interseções totalizadas. Nenhum assistente de tabela dinâmica é necessário, e o resultado é recalculado no instante em que os dados de Sales mudam.

Uma tabela dinâmica completa mostra totais gerais. Adicione uma coluna Total à direita e uma linha Total na parte inferior usando SUM simples em cada linha ou coluna.

Para o total da linha da primeira região, coloque isto na coluna depois do último trimestre:

Para obter o total de uma coluna, some as células do corpo daquele trimestre ao longo das linhas. Esses totais nas bordas fazem o relatório parecer completo e permitem que os leitores verifiquem rapidamente se os números fazem sentido.

Se o seu programa oferecer suporte, você poderá evitar a cópia passando referências de expansão diretamente para SUMIFS. Use os cabeçalhos expandidos como critérios.

Esta única fórmula totaliza todas as interseções de região e trimestre:

Aqui, E2# é a lista vertical de regiões e F1# é a lista horizontal de trimestres. O Excel combina as duas em uma grade completa de uma só vez. O método de arrastar é mais compatível, mas esta é a versão moderna e elegante.

Os relatórios se tornam mais informativos quando mostram a participação, não apenas os valores. Adicione uma coluna que expresse o total de cada região como percentual do total geral.

Se o total da linha da região estiver em J2 e o total geral estiver em J10, escreva:

Fixar o total geral com $J$10 permite preencher a fórmula para baixo em todas as regiões, mantendo sempre a divisão pelo mesmo denominador. Formate a coluna como percentual, e os leitores verão imediatamente quais regiões predominam.

Alguns hábitos mantêm uma tabela dinâmica baseada em fórmulas confiável:

• Use intervalos amplos, como as linhas 2 a 500, para incluir novas linhas.
• Fixe os intervalos de dados com âncoras $ completas; somente as referências dos cabeçalhos devem mudar.
• Deixe espaço vazio abaixo e à direita para que os cabeçalhos e totais expandidos tenham espaço.

Quando bem feito, esse relatório não exige nenhuma manutenção. Insira novas vendas, e a grade, os totais e os rótulos se atualizarão sozinhos.

Verifique se você compreendeu as referências mistas que fazem uma tabela dinâmica baseada em fórmulas funcionar.

Você recriou uma tabela dinâmica usando apenas fórmulas:

• UNIQUE junto com SORT criou os cabeçalhos de linha em uma coluna expandida.
• TRANSPOSE distribuiu os cabeçalhos de coluna por uma linha.
• SUMIFS com as referências mistas $E2 e F$1 preencheu todas as interseções, seja arrastando a fórmula, seja usando referências de expansão como E2# e F1#.
• SUM adicionou as bordas de total geral.

Toda a grade é recalculada em tempo real. A seguir, você tornará o painel interativo com listas suspensas que controlam as métricas.