0001-Somas e contagens condicionais

Uma das dúvidas mais frequentes que vejo aparecer nos fóruns é como realizar uma soma ou contagem de valores, com base em mais de uma condição ou utilizando condições mais complexas.
As funções SOMASE e CONT.SE embora resolvam uma boa gama de problemas, não atendem a questões com maior número de condições. Com a versão 2007 do Excel, fomos brindados com as funções SOMASES e CONT.SES, que ampliaram a capacidade de suas antecessoras, mas que possuem limitações no seu uso. Tente, por exemplo, contar quantos usuários em uma lista possuem a letra “A” como inicial do seu primeiro nome, utilizando CONT.SE ou CONT.SES.
O uso de fórmulas matriciais provê a solução para quase 100% destas questões, porém geram dificuldades de compreensão em usuários não iniciados.
Por outro lado, a função SOMARPRODUTO se ajusta muito bem aos mais diferentes casos de contagens e somas condicionais, tendo uma sintaxe relativamente simples e consumindo menos recursos de memória do que as soluções matriciais equivalentes.
Vamos ilustrar algumas possibilidades de uso desta função, utilizando uma base de dados bem simples:

Data Cliente Valor da Compra
1/mar João R$ 1.665
1/mar José R$ 1.384
1/mar Antônio R$ 1.577
1/mar Pedro R$ 1.675
1/mar Paulo R$ 1.738
1/mar Maria R$ 2.252
1/mar Isabel R$ 2.482
2/mar João R$ 1.328
2/mar José R$ 955
2/mar Antônio R$ 888
2/mar Pedro R$ 1.452
2/mar Paulo R$ 2.465
2/mar Maria R$ 1.651
2/mar Isabel R$ 1.875
3/mar João R$ 1.698
3/mar José R$ 659
3/mar Antônio R$ 1.618
3/mar Pedro R$ 2.180
3/mar Paulo R$ 1.436
3/mar Maria R$ 620
3/mar Isabel R$ 1.599
4/mar João R$ 717
4/mar José R$ 1.179
4/mar Antônio R$ 2.489
4/mar Pedro R$ 778
4/mar Paulo R$ 2.424
4/mar Maria R$ 895
4/mar Isabel R$ 685
5/mar João R$ 2.290
5/mar José R$ 902
5/mar Antônio R$ 2.315
5/mar Pedro R$ 1.738
5/mar Paulo R$ 2.199
5/mar Maria R$ 2.046
5/mar Isabel R$ 1.922

Se inserirem a listagem acima numa planilha a partir da célula A1, verão que o intervalo dos dados se estenderá até a linha 36 da coluna C.
Proponho em seguida algumas questões que responderemos em seguida utilizando a função SOMARPRODUTO.
1ª Questão: Qual o valor comprado pelos clientes no dia 04/mar?
2ª Questão: Qual o valor comprado entre os dias 01 e 03/mar?
3ª Questão: Qual o valor das compras realizadas pelo Pedro?
4ª Questão: Qual o valor das compras realizadas por Maria e Isabel
5ª Questão: Qual o valor das compras do Antônio nos dias 01/mar e 05/mar?
6ª Questão: Quantas compras foram realizadas por clientes com a inicial “J”?

Usaremos a seguinte sintaxe para a função: =SOMARPRODUTO((Argumento1)*(Argumento2)*(Argumento3)*…*(ArgumentoN)), onde cada argumento representa um intervalo de dados que pode, ou não, apresentar uma condição. A função trabalha, multiplicando o 1º elemento do 1º argumento, pelo 1º elemento do 2º argumento, e assim por diante até o 1º elemento do argumento N. Depois faz o mesmo com o 2º elemento, com o 3º e assim por diante, somando em seguida todos esses produtos.

1ª Questão
Antes da resposta é importante compreender como podemos informar ao Excel um valor de data, sendo que não podemos digitar 04/03/2009 diretamente em uma fórmula, pois seria interpretado como uma divisão de valores. As formas mais comuns, e igualmente eficazes, são:
a. Informar o número serial correspondente: 39876, que corresponde ao número de dias contados desde 01/01/1900 até a data corrente;
b. Utilizar a função DATA (=DATA(2009;3;4));
c. Utilizar a função Valor (=VALOR(“04/03/2009”).
d. Indicar uma célula que contenha o valor da data (por exemplo; $D$2)
Agora que sabemos com inserir uma data numa fórmula, vamos à condição propriamente dita. Queremos contabilizar todos os dias, presentes na coluna A, que sejam iguais a 04/mar. Desta forma nosso primeiro argumento será ($A$2:$A$36=VALOR(“04/03/2009”))
Agora teremos que inserir o argumento que representa os valores a serem somados. No nosso caso ($C$2:$C$36). Temos então que a nossa fórmula fica assim=SOMARPRODUTO(($A$2:$A$36=VALOR(“04/03/2009”))*($C$2:$C$36)), sendo o resultado igual a R$ 9.167.

2ª Questão:
Creio que podemos partir direto para a solução
=SOMARPRODUTO(($A$2:$A$36>=VALOR(“01/03/2009”))*($A$2:$A$36<=VALOR("03/03/2009"))*($C$2:$C$36))
Onde :
Argumento1 representa todos os dias a partir de 01/03;
Argumento2 representa todos os dias anteriores a 03/03, incluindo também esta data.
O produto de Argumento1 * Argumento2, tem como resultante todos os dias compreendidos neste intervalo (matematicamente falamos que é a intersecção dos intervalos)
Finalmente Argumento3 representa os valores a serem somados com base nestas condições.

3ª Questão
=SOMARPRODUTO(($B$2:$B$36="Pedro")*($C$2:$C$36))

4ª Questão
=SOMARPRODUTO((($B$2:$B$36="Maria")+($B$2:$B$36="Isabel"))*($C$2:$C$36))
Vale a pena destacar o uso do sinal de adição para as condições, criando um argumento de uma forma diferente, cuja leitura pode ser "quero destacar todos os valores comprados por Maria somados aos valores comprados por Isabel".

5ª Questão
=SOMARPRODUTO(($B$2:$B$36="Antônio")*(($A$2:$A$36=VALOR("01/03/2009"))+($A$2:$A$36=VALOR("05/03/2009")))*($C$2:$C$36))
Vale lembrar que não estamos falando do intervalo entre os dias e sim de cada data individualmente.

6ª Questão
=SOMARPRODUTO(1*(ESQUERDA($B$2:$B$36)="J"))
Este último é um exemplo de contagem e que só tem um argumento. Neste caso usamos o truque de multiplicar o argumento por 1.

Espero ter lançado alguma luz sobre o tema e facilitar a vida de quem se confronta com este tipo de questão no dia a dia.
A planilha com os dados e as soluções está disponível em: Arquivo de Exemplo

In Excel We Trust

Marcado com: , , ,
Publicado em Nível: Intermediário
6 comentários em “0001-Somas e contagens condicionais
  1. flaviox27 disse:

    Este somarproduto é sensacional e estas demonstrações acima são perfeitas para ajudar a entender esta função. Parabéns e obrigado por compartilhar o arquivo de exemplo.

  2. Luciano disse:

    Bom dia Adilson…segue abaixo um exemplo do que pretendo fazer:::

    QUANTIDA DE PONTOS PARA CÁLCULO = 20

    DATA INFRAÇÃO PONTOS data inicio data fim

    12/01/2011 4 12/01/2011 12/01/2012
    23/03/2011 5 23/03/2011 23/03/2012
    20/04/2011 4 20/04/2011 20/04/2012
    16/09/2011 5 16/09/2011 16/09/2012

    TOTAL 18

    SALDO DOS PONTOS

    … já tentei utilizando a fórmula SOMARPRODUTO da seguinte maneira—–{=SOMARPRODUTO(–(A12:A15>=C12:C15);–(A12:A15<=D12:D15);B12:B15)}.
    porém, gostaria que, no saldo dos pontos, que é o saldo negativo, quando atingir a data fim, de cada infração, e saldo fosse diminuindo, até zerar, ou seja, ficar com saldo negativo de "0" pontos na carteira.

    Fico no aguardo, Obrigado.

    Luciano

  3. Marcelo disse:

    Finalmente alguém explicando de forma clara o funcionamento e a utilidade da função SOMARPRODUTO.
    Parabéns!

  4. José Augusto disse:

    Muito bem explicado. Porém, só uma dúvida: por que em alguns casos utilizou-se a adição, ao invés da multiplicação, como na 4ª e 5ª questões? Qual é o critério para definir se será utilizada adição ou multiplicação? Obrigado.

    • Bom dia,
      Observe que na 4ª questão queremos as compras realizadas pela Maria e pela Isabel.
      Em termos de lógica, dizemos que queremos ‘compras realizadas por Maria OU Isabel’, uma vez que dizer ‘compras realizadas por Maria E Isabel’ seria seriam compras realizadas pelas duas simultaneamente.
      Quando temos um OU lógico utilizamos o operador soma (+).
      Na 5ª questão, a interpretação é a mesma, pois queremos as compras realizadas em 01/mar OU 05/mar.
      [ ]s

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

Visitas dos usuários
  • 1,964,110

Clique para assinar este blog e receber notificações de novos artigos por email.

Junte-se a 1.476 outros seguidores

Aumente sua interação

Siga-nos do twitter (@usuariosdoexcel).

Acesse o nosso fórum e compartilhe as suas dúvidas.

Curta nossa página no Facebook.

Junte-se ao grupo do LinkedIn.

Visite-nos utilizando o seu smartphone ou tablet.

Recomende este Blog
Visualizar notícias
Site monitorado:
Real Time Web Analytics

Clicky

%d blogueiros gostam disto: