0224-Dica sobre funções: SOMARPRODUTO – Parte 1

Sem dúvida, essa é a minha função preferida do Excel.

Por quê?

  • É fácil de usar,
  • É fácil de compreender,
  • Realiza somas condicionais,
  • Realiza contagens condicionais,
  • É compatível com todas as versões do Excel,
  • Pode ser utilizada como substituto de fórmulas matriciais,
  • Pode ser utilizada em conjunto com inúmeras outras funções.

Só aqui no blog temos mais de 10 artigos em que a função foi utilizada para resolver as mais variadas questões, a começar pelo nosso primeiro artigo.

Então, acho que vale a pena conversarmos um pouco mais sobre ela.

Se consultarmos a ajuda do Excel sobre esta função, veremos o seguinte:

Descrição
Multiplica os componentes correspondentes nas matrizes fornecidas e retorna a soma destes produtos.

Sintaxe
SOMARPRODUTO(matriz1; [matriz2]; [matriz3], …)

A sintaxe da função SOMARPRODUTO tem os seguintes argumentos:

  • Matriz1 Obrigatório. O primeiro argumento matricial cujos componentes você deseja multiplicar e depois somar.
  • Matriz2; matriz3,… Opcional. Argumentos matriciais de 2 a 255 cujos componentes você deseja multiplicar e depois somar.

Comentários
Os argumentos da matriz devem ter a mesma dimensão. Se não tiverem, SOMARPRODUTO fornecerá o valor de erro #VALOR!.
SOMARPRODUTO trata as entradas da matriz não numéricas como se fossem zeros.

O que me chama atenção é que nada é revelado sobre os usos potenciais da função…

Outro detalhe, é que a única sintaxe é a separação dos argumentos com ponto-e-vírgula, quando o mais utilizado é o sinal de multiplicação…

Veremos, a seguir, um exemplo simples trabalhando com uma única coluna. O raciocínio pode ser estendido para uma única linha.

Se aplicarmos SOMARPRODUTO a um intervalo que contenha apenas valores numéricos, o resultado é o mesmo obtido pela função SOMA.

Se os valores forem não numéricos, o resultado será zero.

Mas, como podemos utilizar SOMARPRODUTO para realizar uma contagem condicional?

E se quisermos contar quantas registros são iguais a “a” no exemplo a seguir?

0224_01

Poderíamos testar individualmente cada valor, criando um teste numa outra coluna para identificarmos se o valor da célula é igual ou não ao valor pesquisado, e em seguida contar o número de ocorrências de valores iguais.

Mas utilizando SOMARPRODUTO, podemos fazer isso de forma direta.

Inicialmente vamos criar a condição de teste , comparando o intervalo com o valor desejado:
=(A1:A10)=”a”

Se digitarmos essa fórmula numa célula, obteremos um único valor: VERDADEIRO.

0224_02

O que isso significa, uma vez que temos valores iguais a “a” e outros diferentes?

Esse valor representa apenas o primeiro resultado de uma série de comparações:
A1=”a”; A2=”a”; A3=”a”;….;A10=”a”.

Se selecionarmos a fórmula na Barra de Fórmulas e pressionarmos F9, o resultado obtido é
={VERDADEIRO;VERDADEIRO;FALSO….

0224_03

Embora apenas o primeiro resultado seja exibido, o Excel armazena internamente o resultado de todas essas comparações.

Então para sabermos quantas ocorrências houve do registro “a”, teremos que contar o número de ocorrências do valor VERDADEIRO.

Mas não podemos utilizar esse resultado diretamente como argumento de SOMARPRODUTO, pois, como os resultados não são numéricos, obteremos o valor zero.

Agora podemos lançar mão de alguns truques.

a. Multiplicar o resultado por 1
=1*((A1:A10)=”a”)
Isso funciona, pois o resultado do produto 1*VERDADEIRO é igual a 1, e do produto 1*FALSO é igual a 0. Dessa forma transformaremos uma matriz de VERDADEIROS e FALSOS numa matriz binária (1 ou 0).

b. Utilizar a função N
=N((A1:A10)=”a”)
Essa função transforma VERDADEIRO em 1 e FALSO em 0.

c. Utilizar dois sinais negativos (–)
=–((A1:A10)=”a”)
Ou seja, multiplicamos por -1 obtendo valores -1 e 0, e em seguida multiplicamos novamente para obtermos valores 1 e 0.

Importante: Observem que em todos os casos, a comparação (A1:A10) foi inserida entre parênteses, para garantir que esse resultado seja obtido primeiramente e, em seguida, sejam realizadas as transformações de Booleanos para valores numéricos.

Agora podemos utilizar qualquer uma das formas acima como argumento para SOMARPRODUTO.
=SOMARPRODUTO(N((A1:A10)=”a”))

O resultado é o número de ocorrências do valor “a” no intervalo A1:A10.

0224_04

O conceito chave que abordamos aqui é a criação de uma matriz de valores 1 e 0.

Utilizamos a igualdade para comparação, mas, da mesma forma, podemos trabalhar utilizando os comparativos maior do que (>), maior ou igual a (>=), menor do que (<), menor ou igual a (<=) e diferente de (<>), para realizar os testes.

A partir desta compreensão, continuaremos nos aprofundando nos artigos futuros aumentando a complexidade dos exemplos.

Anúncios

In Excel We Trust

Marcado com:
Publicado em Nível: Avançado
2 comentários em “0224-Dica sobre funções: SOMARPRODUTO – Parte 1
  1. Juliana disse:

    Excelente explicação! Comecei a acompanhar o blog a pouco tempo, cheguei nele através do google, e logo me inscrevi para receber os posts!

  2. kio disse:

    Po show de bola essas dicas, é bom pra agregar mais info nesse site que tem umas planilhas já prontas com graficos e tudo mais:
    http://www.lojaexcel.com.br

Deixe um comentário

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

%d blogueiros gostam disto: