Normalmente, quando você calcula uma média ou média aritmética, cada número tem igual valor ou peso. A média é calculada adicionando um intervalo de números e, em seguida, dividindo esse total pelo número de valores no intervalo. Uma média ponderada, por outro lado, considera que um ou mais números no intervalo valem mais ou têm um peso maior do que os outros números.
Por exemplo, certas notas na escola, como exames de meio termo e final, geralmente valem mais do que testes ou tarefas regulares. Se a média for usada para calcular a nota final de um aluno, os exames intercalar e final receberiam pesos maiores. No Excel, você pode calcular médias ponderadas usando oSUMPRODUCT função.
Como funciona a função SUMPRODUCT
O SUMPRODUCT multiplica os elementos de dois ou mais arrays e, em seguida, adiciona os produtos. Por exemplo, em uma situação na qual duas matrizes com quatro elementos cada são inseridas como argumentos para o SUMPRODUCT função:
- O primeiro elemento de array1 é multiplicado pelo primeiro elemento em array2.
- O segundo elemento do array1 é multiplicado pelo segundo elemento do array2.
- O terceiro elemento de array1 é multiplicado pelo terceiro elemento de array2.
- O quarto elemento de array1 é multiplicado pelo quarto elemento de array2.
Em seguida, os produtos das quatro operações de multiplicação são somados e retornados pela função como resultado.
02 de 04Sintaxe SUMPRODUCT e Argumentos
A sintaxe de uma função refere-se ao layout da função e inclui o nome, colchetes e argumentos da função. A sintaxe para o SUMPRODUCT função é:
= SUMPRODUCT (array1, array2, array3, … array255)
Os argumentos para a função SUMPRODUCT são:
- Array1: O primeiro argumento do array (obrigatório)
- Array2, array3, … array255: Arrays adicionais (opcionais), até 255. Com dois ou mais arrays, a função multiplica os elementos de cada array e adiciona os resultados.
Os elementos da matriz podem ser referências de célula para a localização dos dados na planilha ou números separados por operadores aritméticos, como plus (+) ou menos (-) sinais. Se você inserir números que não sejam separados por operadores, o Excel os tratará como dados de texto.
- Todos os argumentos da matriz devem ter o mesmo tamanho. Em outras palavras, o mesmo número de elementos deve estar em cada matriz. Se não, SUMPRODUCT devolve o #VALOR! valor de erro.
- Se algum elemento da matriz não for um número, como dados de texto, SUMPRODUCT trata-os como zeros.
Inserindo a fórmula de ponderação
Nosso exemplo mostrado neste artigo calcula a média ponderada da nota final de um aluno usando o SUMPRODUCT função; a função realiza isso por:
- Multiplicando as várias marcas pelo seu fator de peso individual.
- Adicionando os produtos dessas operações de multiplicação juntos.
- Dividindo a soma acima pelo total do fator de ponderação 7 (1 + 1 + 2 + 3) para as quatro avaliações.
Como a maioria das outras funções no Excel, você normalmente entra SUMPRODUCT em uma planilha usando o Construtor de fórmulas; no entanto, como a fórmula de ponderação usa SUMPRODUCT de maneira não padrão, no entanto - o resultado da função é dividido pelo fator de peso - você deve digitar a fórmula de ponderação em uma célula da planilha.
= SUMPRODUCT (B3: B6, C3: C6) / (1 + 1 + 2 + 3)
- Clique na célula C7 para torná-lo a célula ativa (o local onde a marca final do aluno será exibida).
- Digite a fórmula acima na célula.
- aperte oEntrar tecla no teclado.
- A resposta78.6 deve aparecer no celular C7 (sua resposta pode ter mais casas decimais).
A média não ponderada para as mesmas quatro marcas seria 76,5. Como o aluno obteve melhores resultados em seus exames de meio de período e final, pesar a média ajudou a melhorar sua nota geral.
04 de 04Variações de fórmulas SUMPRODUCT
Para enfatizar que os resultados do SUMPRODUCT função são divididas pela soma dos pesos para cada grupo de avaliação, o divisor - a parte que faz a divisão - foi inserido como
(1+1+2+3A fórmula de ponderação geral pode ser simplificada inserindo o número 7 (a soma dos pesos) como o divisor. A fórmula seria então:
= SUMPRODUCT (B3: B6, C3: C6) / 7
Esta opção é válida se o número de elementos na matriz de ponderação for pequeno e eles puderem ser facilmente adicionados juntos, mas se tornará menos eficaz à medida que o número de elementos na matriz de ponderação aumentar, dificultando sua adição.
Outra opção, e provavelmente a melhor escolha, já que usa referências de célula em vez de números ao totalizar o divisor, seria usar a função SUM para totalizar o divisor. A fórmula é então:
= SUMPRODUCT (B3: B6, C3: C6) / SUM (B3: B6)
Geralmente é melhor inserir referências de célula em vez de números reais em fórmulas. Isso simplifica a atualização, se os dados da fórmula forem alterados.
Por exemplo, se os fatores de ponderação para atribuições foi alterado para 0,5 no exemplo e paraTestes para 1.5, você teria que editar as duas primeiras formas da fórmula manualmente para corrigir o divisor.
Na terceira variação, apenas os dados nas células B3 e B4 precisa ser atualizado, e a fórmula irá recalcular o resultado.
