Funções SOMASE e CONT.SE com múltiplos critérios de pesquisa

Hoje recebi uma planilha com um dashboard bem interessante: gráficos refinados, informações bem estruturadas e apresentadas, vários resumos de informações mais relevantes.

Resolvi fazer uma “engenharia reversa” da estrutura da planilha e, para minha surpresa, existiam muitas colunas, com vários filtros e somas, para simplesmente criar vários critérios de pesquisa numa única planilha! Muito complicado para fazer, mais complicado ainda para uma outra pessoa manter e quase impossível de explicar...

A solução? Utilizar as funções SOMASES e CONT.SES. Isso mesmo, com um baita “S” no final.

Para a função SOMASES vamos imaginar uma tabela que contenha o lucro individual para cada produto de uma empresa, classificando os produtos quanto ao público (Masculino ou Feminino) e a origem (Fabricação própria ou Revenda).

Suponha que desejamos saber qual é soma do lucro dos produtos para o público Feminino E de Fabricação própria. A fórmula ficaria assim, retornando o valor 24:

=SOMASES(D2:D13;B2:B13;"Feminino";C2:C13;"Fabricação própria")

Para saber quantos produtos se enquadram nos mesmos critérios, a fórmula fica assim, retornando o valor 3:

=CONT.SES(B2:B13;"Feminino";C2:C13;"Fabricação própria")

Este é apenas um exemplo básico, mas, as funções permitem usos mais refinados, como a entrega dos critérios de pesquisa como referência a outras células, inclusão de caracteres curinga para pesquisa apenas parte de uma sentença, entre outros. E acima de tudo, muito mais fácil para organizar seus dashboards.

Qual é o melhor gráfico para o meu dashboard?

Não vamos reinventar a roda, certo? Sendo assim, vou responder à esta pergunta com o trabalho do Stephen Few, que analisa cada tipo de gráfico em função do tipo de conteúdo (fonte dos dados).

Graph Selection Matrix

Calculando dias úteis entre duas datas

Levanta a mão quem ainda não precisou calcular a quantidade de dias úteis entre duas datas!

Todos os caminhos levam à Roma, mas, alguns são mais fáceis. Ao invés de criar fórmulas mirabolantes, que tal utilizar alguma totalmente pronta?

Antes de mais nada, habilite as Ferramentas de Análise do Excel. Clique no menu Ferramentas, Suplementos e marque a opção Ferramentas de Análise. A localização desta opção pode variar dependendo da versão do Excel, então, na dúvida consulta o menu Ajuda.

A primeira fórmula é a DiaTrabalhoTotal, que calcula a quantidade de dias úteis entre duas datas, permitindo excluir os finais de semana e feriados. Explicando resumidamente o assistente de fórmulas temos os seguintes parâmetros:

Data inicial: é a célula que contem a data inicial do período

Data final: é a célula que contem a data final do período

Feriados: é o intervalo de células que contem a relação de datas a serem consideradas como feriados. Este parâmetro é opcional. Como boa prática, quando preciso utilizar este parâmetro, costumo nomear o intervalo de células para facilitar o entendimento. Fica a seu critério.

A segunda fórmula calcula o próximo dia útil após X dias úteis. Muito útil para o cálculo de prazos legais e datas limite. Também permite incluir a lista de feriados. Resumidamente os parâmetros são:

Data inicial: é a célula que contem a data inicial do período

Dias: é a quantidade de dias úteis a serem acrescidos na data inicial

Feriados: idem à primeira fórmula

Gráficos de barras com preenchimento personalizado (Copas do Mundo)

Os gráficos de barras são ótimos para representar séries quantitativas de dados. São visualmente agradáveis, com boa usabilidade para o usuário final e fáceis de construir. E ainda podem ficar visualmente mais bonitos!


Neste exemplo substituí o preenchimento padrão das barras por uma imagem representando o conteúdo do
gráfico, ou seja, a bandeira de cada País.

Primeiramente criei a tabela com os dados, sem grandes complicações.

Criei um gráfico de barras comum e fiz algumas alterações gerais (ajustei a escala do eixo vertical, retirei os
títulos e legendas, aumentei o espaçamento entre as séries, retirei as linhas de grade).


Cliquei com o botão direito na primeira barra e selecionei a opção Formatar ponto de dados, opção
Preenchimento, opção Preenchimento com imagem ou textura.


Depois selecionei a opção “Inserir de Arquivo” e escolhi a imagem correta (a bandeira do respectivo País
representado por cada uma das barras). Marquei a opção Empilhar e dimensionar com 1 unidades/imagem.
Repeti a operação para cada uma das barras. Veja o original e compare com o final.

É possível fazer o mesmo com outros tipos de gráficos? Sim, totalmente possível. Que tal criar algum e
compartilhar conosco?


p.s.1: Utilizei o MS-Excel 2010, portanto, as opções de menu poderão variar de acordo com a versão
p.s.2: Agradecimentos especiais ao Adriano Estokero pela arte final nas bandeiras

Descobrindo o trimestre para uma data específica

Na consolidação de dados para criação de dashboards ou até mesmo no dia-a-dia dos profissionais de finanças é bastante comum a necessidade de descobrir a qual trimestre uma determinada data pertence.

Uma fórmula bastante simples resolve este problema. A lógica é: pegue um mês qualquer e acrescente o número 2, divida tudo por 3, faça o arredondamento para baixo e pronto, você tem o trimestre relativo ao mês em questão.

Por exemplo, Janeiro é o mês 1. Portanto, 1 + 2 = 3, dividido por 3 = 1 (primeiro trimestre). Maio é o mês 5. Portanto, 5 + 2 = 7, dividido por 3 = 2,33, que arredondado para baixo = 2 (segundo trimestre). E assim por diante.

Para fazer no Excel são necessárias duas fórmulas:

INT, que arredonda o valor informado em um número inteiro.

MÊS, que retorna em formato numérico o mês relativo à data informada.

A fórmula final fica assim: =INT((MÊS(célula que contem a data, por exemplo, A1)+2)/3)

Gráficos com legendas dinâmicas (legendas com fórmulas)

Você passa horas tabulando dados, diagramando a estrutura de informação, produzindo gráficos, escolhendo cores e quando termina o seu dashboard se depara com aquela “legendinha” sem graça do Excel?

Eu costumo incrementar as legendas com informações mais claras, precisas e direcionadas ao resultado a ser apresentado.

Na tabela abaixo temos as variações de receita, despesa e lucro de uma determinada empresa na última década.

Um gráfico convencional (padrão do Excel, sem nenhuma adaptação) ficaria assim:

Incluí em duas células, por meio do comando Inserir símbolo, uma seta para cima e outra para baixo. Depois incluí uma outra coluna na tabela, acrescentando a seguinte fórmula (adaptando para cada item da planilha): =SE(C2>B2;$H$1;$I$1)&" Receita em "&TEXTO(C2/B2-1;"0%")

Explicando rapidamente: se a variação é positiva, seta para cima. Caso contrário, seta para baixo. A função texto acrescenta o símbolo de percentual e formata as casas decimais. $H$1 e $I$1 são referências para as células que contêm os símbolos. O caracter & concatena tudo num único texto. O resultado final fica assim: 

Quanto ao gráfico, inseri três caixas de texto abaixo de cada item (receita, despesa e lucro). Selecionei a primeira caixa de texto, depois cliquei na barra de fórmulas e apontei como referência a célula que contem a fórmula criada anteriormente. Repeti a operação para os demais itens e o resultado final ficou assim.

Dashboard garante viagem à Disney

Um certo professor costumava repetir em sala de aula: “Sou um cara que adora coisas práticas, e nada mais prático do uma boa teoria!”

A teoria colocada em prática neste artigo trata de um caso real de família com despesas mensais fechando no 0 x 0 (zero a zero). Ou seja, pagavam todas as contas e faziam seus investimentos, mas não “sobrava” dinheiro para a tão sonhada viagem à Disney.

Conforme orientação do Gustavo Cerbasi, conceituado autor do segmento de finanças pessoais, a recomendação é justamente não cortar os gastos relacionados ao lazer e sim aqueles relacionados aos chamados “gastos burocráticos”: água, luz, telefone, gás, supermercado, escola.

Portanto, construí para a família um dashboard focado no orçamento familiar, apontando justamente a “performance” nos gastos burocráticos.

Dois itens chamaram a atenção:

1)      Alto volume de ligações DDD, VC1 (ligação local para celular) e VC3 (ligação interurbana para celular) sempre para os mesmos números: familiares que residem em outra cidade ou filhos em passeios com os amigos.

2)      Volume de compras no supermercado não condizente com a rotina familiar, onde praticamente todos fazem refeições fora de casa, inclusive aos fins de semana.

Com base nos gráficos ficou claro que a melhor estratégia para o telefone era adotar um plano que possibilitasse o máximo de ligações ao menor custo. Leia-se: é para continuar falando, inclusive mais, porém, pagando menos. A solução óbvia foi o plano de uma determinada operada que permite ligações ilimitadas entre números de sua rede, inclusive interurbanos, à custo ZERO.

Já para o caso do supermercado a solução foi ainda mais simples: checar a geladeira, despensa e armários antes de ir ao supermercado, comprando apenas os itens realmente necessários.

Os resultados podem ser vistos abaixo. Redução em quase 50% da fatura de telefone (aproximadamente R$ 200,00 ao mês), além da redução de aproximadamente R$ 500,00 ao mês com o consumo de supermercado.

O que mais me chamou a atenção é que a família “já sabia” de tudo isso, mas, literalmente pagou pra ver. Deve ser coisa de São Tomé!

Redução de despesas com telefone

Reduçao de despesas com supermercado

P.S.: Sim, eles realizaram o sonho de ir à Disney.