Montar um relatório gerencial ou um dashboard no Excel que se atualiza automaticamente quando o usuário muda os filtros, sem precisar de macros, tabelas dinâmicas ou Power BI, é totalmente possível usando a função SOMASES como base. Quando combinada com células de controle, listas suspensas e formatação condicional, o SOMASES se torna o motor de qualquer painel de análise interativo no Excel. Neste artigo iremos mostrar como usar o SOMASES para construir relatórios e dashboards dinâmicos do zero, com uma estrutura que qualquer profissional pode aplicar no trabalho.
A arquitetura de um dashboard baseado em SOMASES
Antes de sair criando fórmulas, é importante entender a estrutura que torna um dashboard baseado em SOMASES funcional e fácil de manter. Um bom dashboard tem pelo menos três camadas separadas: a camada de dados, a camada de cálculo e a camada de apresentação.
A camada de dados é onde ficam os dados brutos, geralmente em uma aba separada chamada “Dados” ou “Base”. Essa aba contém a tabela com todos os registros, como as vendas, os pedidos ou as transações. Ela deve ser estruturada como uma Tabela do Excel (Ctrl+T) para que novas linhas sejam incluídas automaticamente nas fórmulas.
A camada de cálculo é onde ficam as fórmulas SOMASES que calculam os totais. Pode ser na mesma aba do dashboard ou em uma aba intermediária chamada “Cálculos”. Aqui ficam as células de controle (os filtros que o usuário vai manipular) e as fórmulas que usam esses controles como critério.
A camada de apresentação é o dashboard propriamente dito: gráficos, indicadores e tabelas formatadas que mostram os resultados calculados pela camada de cálculo. Essa separação torna o arquivo mais organizado, mais fácil de depurar e mais simples de expandir no futuro.
Criando células de controle com listas suspensas
As células de controle são os campos que o usuário vai preencher para filtrar o dashboard. Para tornar a experiência do usuário mais agradável e evitar erros de digitação, configure essas células com listas suspensas usando a Validação de Dados do Excel.
Para criar uma lista suspensa com os vendedores disponíveis na base de dados, selecione a célula de controle do vendedor (por exemplo, B2), vá em Dados > Validação de Dados > Permitir: Lista, e no campo Fonte referencie a coluna de vendedores da sua base de dados, ou use a função ÚNICO (no Excel 365) para listar automaticamente apenas os valores únicos:
=ÚNICO(Dados[Vendedor])
Para o filtro de período, você pode usar uma lista com os meses (“Janeiro”, “Fevereiro”… “Dezembro”) e outra com os anos disponíveis nos dados. Adicione também uma opção “Todos” em cada lista para quando o usuário quiser ver o total sem filtro por aquela dimensão.
Com as células de controle configuradas com listas suspensas, o usuário não precisa digitar nada — apenas clica e seleciona. Isso elimina erros de digitação e inconsistências que poderiam fazer o SOMASES não encontrar correspondências.
Construindo a fórmula SOMASES com a opção “Todos”
O grande desafio ao criar dashboards com SOMASES é permitir que o usuário selecione “Todos” em um filtro para ver o total sem restrição naquela dimensão. A solução elegante para isso é usar o curinga asterisco como critério quando o filtro está em “Todos”, e o valor real quando está em um item específico.
Para implementar isso, use uma fórmula SE que verifica o valor da célula de controle e alterna entre o curinga e o valor selecionado:
=SOMASES(Dados[Valor]; Dados[Vendedor]; SE(B2="Todos"; "*"; B2); Dados[Região]; SE(C2="Todos"; "*"; C2))
Quando B2 contém “Todos”, o critério do vendedor se torna “*” (asterisco = qualquer texto), e todas as linhas são incluídas. Quando B2 contém “Carlos”, o critério é “Carlos” e apenas as linhas desse vendedor são somadas. O mesmo se aplica ao filtro de região em C2.
Essa combinação de SE com o curinga dentro do SOMASES é um dos truques mais poderosos para criar dashboards interativos no Excel. Adicione quantos filtros forem necessários replicando esse padrão, e o dashboard vai se comportar exatamente como um painel de análise profissional.
Montando a tabela de resultados do dashboard
Com as células de controle configuradas, o próximo passo é montar a tabela de resultados que alimenta os gráficos e indicadores do dashboard. Essa tabela usa o SOMASES para calcular os totais de cada dimensão de análise.
Por exemplo, para uma análise de vendas por mês considerando os filtros de vendedor e região ativos, crie uma tabela com os meses nas linhas e use o SOMASES para calcular o total de cada mês, combinando os filtros de controle com o critério do mês:
=SOMASES(Dados[Valor]; Dados[Mês]; A7; Dados[Vendedor]; SE($B$2="Todos";"*";$B$2); Dados[Região]; SE($C$2="Todos";"*";$C$2))
Onde A7 é a célula que contém o número do mês (1 para janeiro, 2 para fevereiro, etc.) e os controles de vendedor e região estão nas células fixas $B$2 e $C$2. Arraste essa fórmula para baixo para os 12 meses. Toda vez que o usuário mudar os filtros B2 ou C2, a tabela inteira se recalcula automaticamente.
Para os indicadores de KPI (cartões com os totais), use o SOMASES com todos os filtros ativos para mostrar o total do período, o total do mês atual, o número de vendas e qualquer outra métrica relevante.
Criando gráficos dinâmicos vinculados ao SOMASES
Com a tabela de resultados montada e alimentada pelo SOMASES, os gráficos são o passo final. Crie os gráficos diretamente a partir da tabela de resultados — não a partir da base de dados bruta. Dessa forma, quando os filtros mudam e o SOMASES recalcula os totais, os gráficos se atualizam automaticamente sem nenhuma interação adicional.
Para o gráfico de evolução mensal, use um gráfico de linhas com os meses no eixo horizontal e os totais calculados pelo SOMASES no eixo vertical. Para a distribuição por categoria, um gráfico de barras com as categorias no eixo vertical e os totais na horizontal. Para comparar períodos, um gráfico de barras agrupadas com uma série para o período atual e outra para o mesmo período do ano anterior.
Uma dica importante: nomeie os intervalos de dados dos gráficos com nomes descritivos no Gerenciador de Nomes para facilitar a manutenção. Quando a tabela de resultados crescer ou mudar de posição, basta atualizar o intervalo no Gerenciador de Nomes e todos os gráficos se ajustam automaticamente.
Adicionando indicadores de variação com formatação condicional
Para enriquecer o dashboard, adicione indicadores visuais de variação que mostram se um resultado está acima ou abaixo de uma referência. Por exemplo, compare o total do mês atual com o total do mesmo mês do ano anterior usando dois SOMASES e calcule a variação percentual:
Total mês atual: =SOMASES(Dados[Valor]; Dados[Mês]; B2; Dados[Ano]; ANO(HOJE()); ...) Total mesmo mês ano anterior: =SOMASES(Dados[Valor]; Dados[Mês]; B2; Dados[Ano]; ANO(HOJE())-1; ...) Variação: =(Total_atual - Total_anterior) / Total_anterior
Com a variação calculada, aplique formatação condicional para exibir uma seta para cima em verde quando positiva e uma seta para baixo em vermelho quando negativa. Isso dá ao dashboard um aspecto profissional e comunica rapidamente a tendência sem que o gestor precise calcular nada.
Dicas finais para um dashboard com SOMASES de alto nível
Alguns detalhes fazem a diferença entre um dashboard funcional e um dashboard verdadeiramente profissional. Primeiro, oculte as abas de dados e de cálculos para que o usuário veja apenas a aba do dashboard. Use a proteção de planilha para impedir alterações acidentais nas fórmulas, deixando desbloqueadas apenas as células de controle onde o usuário faz a seleção dos filtros.
Segundo, use a formatação personalizada de número para exibir os valores em milhares ou milhões quando os números forem grandes. Por exemplo, o formato personalizado #.##0,0″K” exibe 1.500.000 como 1.500,0K e o formato #.##0,0,,”M” exibe como 1,5M. Isso melhora muito a legibilidade dos indicadores.
Terceiro, adicione um botão ou célula para resetar todos os filtros para “Todos” de uma vez. No Excel sem VBA, você pode usar um hiperlink ou um texto instrucional. Com VBA básico, um botão que define todas as células de controle como “Todos” resolve elegantemente o problema e melhora a experiência do usuário.
Por fim, documente as fórmulas mais complexas com comentários nas células (clique com botão direito > Inserir Comentário ou Nota) explicando a lógica de cada cálculo. Isso facilita muito a manutenção futura, seja por você mesmo ou por outra pessoa que precisar dar suporte ao arquivo.
Se você curtiu esse artigo onde mostramos como usar o SOMASES para criar relatórios e dashboards dinâmicos no Excel, compartilhe com as suas redes sociais e não se esqueça de deixar um comentário aqui embaixo caso você tenha ficado com alguma dúvida.