Baixe a apostila de exemplo
Este tutorial demonstra como usar o Função SUMPRODUCT do Excel no Excel.
Visão geral da função SUMPRODUCT
A função SUMPRODUCT Multiplica matrizes de números e soma a matriz resultante.
Para usar a função de planilha do Excel SUMPRODUCT, selecione uma célula e digite:
(Observe como as entradas da fórmula aparecem)
Sintaxe da função SUMPRODUCT e entradas:
| 1 | = SUMPRODUCT (matriz1, matriz2, matriz3) |
array1 - Matrizes de números.
O que é a função SUMPRODUCT?
A função SUMPRODUCT é uma das funções mais poderosas do Excel. Seu nome pode levar você a acreditar que se destina apenas a cálculos matemáticos básicos, mas pode ser usado para muito mais.
Matrizes
SUMPRODUCT requer entradas de matrizes.
Então, primeiro, o que queremos dizer com “array”? Uma matriz é um grupo simples de itens (por exemplo, números) organizados em uma ordem específica, como um intervalo de células. Portanto, se você tivesse os números 1, 2, 3 nas células A1: A3, o Excel leria isso como matriz {1,2,3}. Na verdade, você pode inserir {1,2,3} diretamente nas fórmulas do Excel e ele reconhecerá a matriz.
Falaremos mais sobre arrays abaixo, mas primeiro vamos dar uma olhada em um exemplo simples.
Matemática básica
Vejamos um exemplo básico de SUMPRODUCT, usando-o para calcular as vendas totais.
Temos nossa tabela de produtos e queremos calcular o total de vendas. Você ficará tentado a apenas adicionar uma nova coluna, pegar o preço da quantidade vendida * e, em seguida, somar a nova coluna. Em vez disso, no entanto, você pode simplesmente usar a função SUMPRODUCT. Vamos examinar a fórmula:
| 1 | = SUMPRODUTO (A2: A4, B2: B4) |
A função irá carregar os intervalos de números em matrizes, multiplicá-los entre si e, em seguida, somar os resultados:
| 1234 | = SUMPRODUCT ({100, 50, 10}, {6, 7, 5})= SUMPRODUCT ({100 * 6, 50 * 7, 10 * 5})= SUMPRODUCT ({600, 350, 50}= 1000 |
A função SUMPRODUCT foi capaz de multiplicar todos os números para nós E fazer a soma.
Média ponderada
Outro caso em que é útil usar SUMPRODUCT é quando você precisa calcular uma média ponderada. Isso ocorre com mais frequência ao lidar com tarefas escolares, então vamos considerar a tabela a seguir.
Podemos ver quanto valem os questionários, testes e trabalhos de casa para a nota geral, bem como qual é a média atual de cada item específico. Podemos calcular a nota geral, escrevendo
| 1 | = SUMPRODUTO (B2: B4, C2: C4) |
Nossa função novamente multiplica cada item nas matrizes antes de somar o total. Isso funciona assim
| 123 | = SUMPRODUCT ({30%, 50%, 20%}, {73%, 90%, 95%})= SUMPRODUCT ({22%, 45%, 19%})= 86% |
Múltiplas Colunas
Outro lugar onde podemos usar SUMPRODUCT é com ainda mais colunas que precisam ser multiplicadas umas contra as outras. Vejamos um exemplo em que precisamos calcular o volume em peças de madeira serrada.
Em vez de criar uma coluna auxiliar para calcular a venda total de cada linha, podemos fazer isso com uma única fórmula. Nossa fórmula será
| 1 | = SUMPRODUTO (B2: B5, C2: C5, D2: D5) |
Os primeiros itens de cada matriz se multiplicarão uns contra os outros (por exemplo, 4 * 2 * 1 = 8). Então, o segundo (4 * 2 * 2 = 16) e 3rd, etc. No geral, isso produzirá a variedade de produtos que se parecem com {8, 16, 16, 32). Então, o volume total seria a soma dessa matriz, 72.
Um critério
Ok, vamos adicionar outra camada de complexidade. Vimos que SUMPRODUCT pode lidar com matrizes de números, mas e se quisermos verificar os critérios? Bem, você também pode criar matrizes para valores booleanos (valores booleanos são valores que são TRUE ou FALSE).
Por exemplo, pegue uma matriz básica {1, 2, 3}. Vamos criar uma matriz correspondente que indica se cada número é maior que 1. Esta matriz seria semelhante a {FALSE, TRUE, TRUE}.
Isso é extremamente útil em fórmulas, porque podemos facilmente converter VERDADEIRO / FALSO em 1 / 0. Vejamos um exemplo.
Usando a tabela abaixo, queremos calcular "Quantas unidades vendidas eram vermelhas?"
Podemos fazer isso com esta fórmula:
| 1 | = SUMPRODUCT (A2: A4, - (B2: B4 = "Vermelho")) |
"Aguentar! O que há com o símbolo duplo menos aí? ” você diz. Lembra como eu disse que poderíamos converter de Verdadeiro / Falso em 1/0? Fazemos isso forçando o computador a fazer uma operação matemática. Neste caso, estamos dizendo "pegue o valor negativo e, em seguida, pegue o negativo novamente". Escrevendo isso, nosso array vai mudar assim:
| 123 | {Verdadeiro, Verdadeiro, Falso}{-1, -1, 0}{1, 1, 0} |
Então, de volta à fórmula SUMPRODUCT completo, ele vai carregar em nossos arrays e depois se multiplicar, assim
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0})= SUMPRODUCT ({100, 50, 0})= 150 |
Observe como os 3rd item tornou-se 0, porque qualquer coisa multiplicada por 0 torna-se zero.
Critérios múltiplos
Podemos carregar até 255 arrays em nossa função, portanto, certamente podemos carregar mais critérios. Vejamos esta tabela maior onde adicionamos o Mês vendido.
Se quisermos saber quantos itens vendidos eram vermelhos e estivéssemos no mês de fevereiro, poderíamos escrever nossa fórmula como
| 1 | = SUMPRODUCT (A2: A4, - (B2: B4 = "Vermelho"), - (C2: C4 = "Fev")) |
O computador então avaliaria nossos arrays e se multiplicava. Já cobrimos como as matrizes True / False são alteradas para 1/0, então vou pular essa etapa por enquanto.
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0}, {0, 1, 1})= SUMPRODUCT ({0, 50, 0})= 50 |
Tivemos apenas uma linha em nosso exemplo que correspondeu a todos os critérios, mas com dados reais, você pode ter várias linhas que precisava adicionar juntas.
Critérios complexos
Ok, até este ponto, você pode não estar impressionado porque todos os nossos exemplos poderiam ter sido feitos usando outras funções como SUMIF ou CONT.SE. Agora vamos fazer algo com essas outras funções não pode Faz. Anteriormente, nossa coluna Mês tinha os nomes reais dos meses. E se em vez disso houvesse datas?
Não podemos fazer um SUMIF agora, porque SUMIF não pode atender aos critérios de que precisamos. Porém, SUMPRODUCT pode nos manipular a manipulação do array e fazer um teste mais profundo. Já manipulamos matrizes quando traduzimos True / False para 1/0. Vamos manipular essa matriz com a função MONTH. Aqui está a fórmula completa que vamos usar
| 1 | = SUMPRODUCT (A2: A4, - (B2: B4 = "Vermelho"), - (MÊS (C2: C4) = 2)) |
Vejamos os 3rd array mais de perto. Primeiro, nossa fórmula extrairá o número do mês de cada data em C2: C4. Isso nos dará {1, 2, 2}. Em seguida, verificamos se esse valor é igual a 2. Agora nosso array se parece com {False, True, True}. Fazemos o duplo menos novamente e temos {0, 1, 1}. Agora estamos de volta a uma situação semelhante à que tínhamos no Exemplo 3, e nossa fórmula será capaz de nos dizer que 50 unidades vendidas em fevereiro eram vermelhas.
Duplo menos vs. multiplicação
Se você já viu a função SUMPRODUCT em uso antes, pode ter visto uma notação um pouco diferente. Em vez de usar um duplo menos, você pode escrever
| 1 | = SUMPRODUTO (A2: A4 * (B2: B4 = "Vermelho") * (MÊS (C2: C4) = 2)) |
A fórmula ainda vai funcionar da mesma maneira, estamos apenas dizendo manualmente ao computador que queremos multiplicar as matrizes. SUMPRODUCT ia fazer isso de qualquer maneira, então não há nenhuma mudança na forma como a matemática funciona. Realizar a operação matemática converte o nosso Verdadeiro / Falso em 1/0 da mesma forma. Então, por que a diferença?
Na maioria das vezes, não importa muito, e tudo se resume à preferência do usuário. Há pelo menos um caso em que a multiplicação é necessária.
Quando você usa SUMPRODUCT, o computador espera que todos os argumentos (array1, array2, etc.) tenham o mesmo tamanho. Isso significa que eles têm o mesmo número de linhas ou colunas. No entanto, você pode fazer o que é conhecido como cálculo de matriz bidimensional com SUMPRODUCT, que veremos no próximo exemplo. Quando você faz isso, os arrays têm tamanhos diferentes, então precisamos ignorar a verificação de “todos do mesmo tamanho”.
Duas dimensões
Todos os exemplos anteriores tiveram nossos arrays indo na mesma direção. SUMPRODUCT pode lidar com as coisas indo em duas direções, como veremos na próxima tabela.
Aqui está nossa tabela de unidades vendidas, mas os dados são reorganizados de acordo com as categorias que aparecem no topo. Se quisermos descobrir quantos itens eram vermelhos e na categoria A, podemos escrever
| 1 | = SUMPRODUCT ((A2: A4 = "Vermelho") * (B1: C1 = "A") * B2: C4) |
O que está acontecendo aqui?? Acontece que vamos nos multiplicar em duas direções diferentes. Visualizar isso é mais difícil de fazer apenas com uma frase escrita, então temos algumas imagens para nos ajudar. Primeiro, nossos critérios de linha (é vermelho?) Vão se multiplicar em cada linha do array.
| 1 | = SUMPRODUCT ((A2: A4 = "VERMELHO") * B2: C4) |
Em seguida, os critérios da coluna (é a categoria A?) Vão multiplicar cada coluna
| 1 | = SUMPRODUCT ((A2: A4 = "Vermelho") * (B1: C1 = "A") * B2: C4) |
Depois de ambos os critérios terem feito seu trabalho, os únicos não zeros restantes são 5 e 10. SUMPRODUCT nos dará o total geral de 15 como nossa resposta.
Lembra como falamos sobre os arrays precisarem ter o mesmo tamanho, a menos que você esteja fazendo duas dimensões? Isso estava parcialmente correto. Examina novamente as matrizes que usamos em nossa fórmula. o altura de dois de nossos arrays é o mesmo, e o largura de duas de nossas matrizes são iguais. Portanto, você ainda precisa ter certeza de que as coisas vão se alinhar corretamente, mas você pode fazer isso em diferentes dimensões.
Duas dimensões e complexo
Muitas vezes somos apresentados a dados que não estão no melhor layout adequado para nossas fórmulas. Podemos tentar reorganizá-lo manualmente ou podemos ser mais inteligentes com nossas fórmulas. Vamos considerar a seguinte tabela.
Aqui temos os dados de nossos itens e vendas combinados para cada mês. Como faríamos para descobrir quantos itens Bob vendeu durante o ano inteiro?
Para fazer isso, usaremos duas funções adicionais: SEARCH e ISNUMBER. A função SEARCH permitirá que procuremos nossa palavra-chave “itens” dentro das células do cabeçalho. A saída desta função será um número ou um erro (se a palavra-chave não for encontrada). Então, usaremos o ISNUMBER para converter naquela saída em nossos valores booleanos. Nossa fórmula terá a seguinte aparência.
Você já deve estar bastante familiarizado com o primeiro array. Isso vai criar uma saída como {0, 1, 0, 1}. A próxima matriz de critérios da qual acabamos de falar. Isso criará um número para todas as células com "Itens" nelas e um erro para as outras {5, # N / A !, 5, # N / A!}. O ISNUMBER então converte isso para Boolean {True, False, True, False}. Então, quando multiplicamos, isso só vai manter os valores da primeira e da terceira colunas. Depois que todas as matrizes se multiplicam, os únicos números diferentes de zero que teremos são os destacados aqui:
| 1 | = SUMPRODUCT ((A2: A5 = "Bob") * (ISNUMBER (SEARCH ("Items", B1: E1)) * B2: E5)) |
O SUMPRODUCT irá então somar tudo isso, e nós temos nosso resultado final de 29.
SUMPRODUTO Ou
Muitas situações surgem em que gostaríamos de poder somar valores se nossa coluna de critérios tiver um valor OU outro valor. Você pode fazer isso no SUMPRODUCT adicionando duas matrizes de critérios uma contra a outra.
Neste exemplo, queremos somar as unidades vendidas para Vermelho e Azul.
Nossa fórmula será semelhante a esta
| 1 | = SUMPRODUCT (A2: A7, (B2: B7 = "Vermelho") + (B2: B7 = "Azul")) |
Vejamos a matriz de critérios de vermelho. Ele produzirá uma matriz parecida com esta: {1, 1, 0, 0, 0, 0}. A matriz de critérios Blue será semelhante a {0, 0, 1, 0, 1, 0}. Ao adicioná-los, a nova matriz será semelhante a {1, 1, 1, 0, 1, 0}. Podemos ver como as duas matrizes se combinaram em uma única matriz de critérios. A função então multiplicará isso por nossa primeira matriz e obteremos {100, 50, 10, 0, 75, 0}. Observe que os valores de Verde foram zerados. A etapa final do SUMPRODUCT é somar todos os números para chegar à nossa solução de 235.
Uma palavra de cautela aqui. Tenha cuidado quando as matrizes de critérios não são mutuamente exclusivas. Em nosso exemplo, os valores na coluna B poderiam ser Vermelho ou Azul, mas sabíamos que nunca poderiam ser os dois. Considere se escrevemos esta fórmula:
| 1 | = SUMPRODUCT (A2: A7, (A2: A7> = 50) + (B2: B7 = "Azul")) |
Nossa intenção é encontrar itens azuis que foram vendidos ou em quantidade superior a 50. No entanto, essas condições não são exclusivas, pois uma única linha pode ter mais de 50 na coluna A e seja azul. Isso resultaria na primeira matriz de critérios semelhante a {1, 1, 0, 1, 1, 0}, a segunda matriz de critérios sendo {0, 0, 1, 0, 1, 0}. Somando-os produziu {1, 1, 1, 1, 2, 0}. Você vê como temos um 2 aí agora? Se deixado sozinho, o SUMPRODUCT acabaria dobrando o valor nessa linha, mudando de 75 para 150, e obteríamos o resultado errado. Para corrigir isso, colocamos uma verificação de critérios externos em nossa matriz, assim:
| 1 | = SUMPRODUCT (A2: A7, - ((A2: A7> = 50) + (B2: B7 = "Azul")> 0)) |
Agora, depois que as duas matrizes de critérios internos forem adicionadas, verificaremos se o resultado é maior que 0. Isso elimina os 2 que tínhamos antes e, em vez disso, teremos uma matriz como {1, 1, 1 , 1, 1, 0} que produzirá o resultado correto.
SUMPRODUCT Exact
A maioria das funções no Excel não diferencia maiúsculas de minúsculas, mas às vezes precisamos ser capazes de fazer uma pesquisa levando em consideração a diferenciação de maiúsculas e minúsculas. Quando o resultado desejado é numérico, podemos fazer isso usando EXACT dentro da função SUMPRODUCT. Considere a seguinte tabela:
Queremos encontrar a pontuação do item “ABC123”. Normalmente, a função EXACT irá comparar dois itens e retornar uma saída booleana informando se os dois itens são exatamente o mesmo. No entanto, como estamos dentro de um SUMPRODUCT, nosso computador saberá que estamos lidando com matrizes e será capaz de comparar um item com cada item de uma matriz. Nossa fórmula será parecida com esta
| 1 | = SUMPRODUCT (- EXACT ("ABC123", A2: A5), B2: B5) |
A função EXACT irá então verificar cada item em A2: A5 para ver se ele corresponde ao valor e ao caso. Isso produzirá uma matriz semelhante a {0, 1, 0, 0}. Quando multiplicado por B2: B5, a matriz se torna {0, 2, 0, 0}. Após o somatório final, obtemos nossa solução de 2.
SUMPRODUCT no Planilhas Google
A função SUMPRODUCT funciona exatamente da mesma forma no Planilhas Google e no Excel:
Exemplos de SUMPRODUCT em VBA
Você também pode usar a função SUMPRODUCT no VBA. Digite: application.worksheetfunction.sumproduct (array1, array2, array3)
Executando as seguintes instruções VBA
| 1 | Range ("B10") = Application.WorksheetFunction.SumProduct (Range ("A2: A7"), Range ("B2: B7")) |
irá produzir os seguintes resultados
Para os argumentos da função (array1, etc.), você pode inseri-los diretamente na função ou definir as variáveis a serem usadas.
