01 de 01
Función de Excel SUMPRODUCT
Visión media ponderada vs peso non ponderada
Normalmente, ao calcular a media ou media aritmética, cada número ten o mesmo valor ou peso.
A media calcúlase engadindo xuntos un número de números e dividindo este total polo número de valores no intervalo .
Un exemplo sería (2 + 3 + 4 + 5 + 6) / 5 que dá unha media non ponderada de 4.
En Excel, eses cálculos son facilmente realizados usando a función AVERAGE .
Unha media ponderada, pola contra, considera que un ou máis números no rango valen máis ou teñen un peso maior que os outros números.
Por exemplo, certas marcas na escola, como o medio e os exames finais, adoitan valer máis que probas regulares ou asignacións.
Se se usa un promedio para calcular a nota final dun alumno os exames finais e medio terían un maior peso.
En Excel, os medias ponderadas pódense calcular usando a función SUMPRODUCT .
Como funciona a función SUMPRODUCT
O que SUMPRODUCT fai é multiplicar os elementos de dúas ou máis matrices e logo engadir ou sumar os produtos.
Por exemplo, nunha situación en que se introducen dúas arrays con catro elementos cada un como argumentos para a función SUMPRODUCT:
- o primeiro elemento da matriz1 multiplicado polo primeiro elemento da matriz2;
- o segundo elemento da matriz1 multiplicado polo segundo elemento da matriz2;
- o terceiro elemento da matriz1 multiplicado polo terceiro elemento da matriz2;
- o cuarto elemento da matriz1 multiplicado polo cuarto elemento da matriz2.
A continuación, os produtos das catro operacións de multiplicación son resumidos e devoltos pola función como resultado.
Sintaxis e argumentos de Excel SUMPRODUCT
A sintaxe dunha función refírese ao deseño da función e inclúe o nome, parénteses e argumentos da función.
A sintaxe para a función SUMPRODUCT é:
= SUMPRODUCT (array1, array2, array3, ... array255)
Os argumentos para a función SUMPRODUCT son:
array1: (necesario) o primeiro argumento de matriz.
array2, array3, ... array255: (opcional) matrices adicionais, ata 255. Con dúas ou máis matrices, a función multiplica os elementos de cada conxunto xuntos e despois engade os resultados.
- Os elementos da matriz poden ser referencias celulares para a localización dos datos na folla de traballo ou números separados por operadores aritméticos , como máis (+) ou menos signos (-). Se os números introdúcense sen ser separados polos operadores, Excel trátanos como datos de texto. Esta situación está cuberta no seguinte exemplo.
Nota :
Todos os argumentos de matriz deben ser do mesmo tamaño. Ou, noutras palabras, debe haber o mesmo número de elementos en cada matriz. Se non, SUMPRODUCT devolve #VALUE. valor de erro.
Se os elementos de matriz non son números, como os datos de texto, SUMPRODUCT trátanos como ceros.
Exemplo: Calcular a media ponderada en Excel
O exemplo que se mostra na imaxe anterior calcula a media ponderada da nota final do alumno usando a función SUMPRODUCT.
A función realiza isto por:
- multiplicando as distintas marcas polo seu peso individual;
- engadindo os produtos destas operacións de multiplicación;
- dividiu a suma anterior polo total do factor de ponderación 7 (1 + 1 + 2 + 3) para as catro avaliacións.
Introducir a fórmula de ponderación
Do mesmo xeito que a maioría das outras funcións en Excel, SUMPRODUCT normalmente ingresa nunha folla de traballo usando o diálogo da función. Non obstante, como a fórmula de ponderación usa SUMPRODUCT de forma non estándar: o resultado da función divídese co factor de peso, a fórmula de ponderación debe escribirse nunha folla de cálculo .
Usáronse os seguintes pasos para introducir a fórmula de ponderación na cela C7:
- Prema na cela C7 para facela a cela activa : a localización onde se mostrará a nota final do alumno
- Escriba a seguinte fórmula na cela:
= SUMPRODUCT (B3: B6, C3: C6) / (1 + 1 + 2 + 3)
Prema a tecla Intro no teclado
- A resposta 78.6 debería aparecer na cela C7 - a túa resposta pode ter máis decimais
A media non ponderada para as mesmas catro marcas sería de 76,5
Dado que o alumno tivo mellores resultados para os exames finais e finais, o peso da media axudou a mellorar a súa marca global.
Variacións de fórmula
Para salientar que os resultados da función SUMPRODUCT están divididos pola suma dos pesos para cada grupo de avaliación, o divisor - a parte que fai a división - ingresou como (1 + 1 + 2 + 3).
A fórmula global de ponderación podería simplificarse introducindo o número 7 (a suma dos pesos) como divisor. A fórmula sería entón:
= SUMPRODUCT (B3: B6, C3: C6) / 7
Esta opción está ben se a cantidade de elementos da matriz de ponderación é pequena e poden ser engadidos facilmente, pero faise menos efectiva xa que a cantidade de elementos na matriz de ponderación aumenta facendo que a súa adición sexa máis difícil.
Outra opción, e probablemente a mellor opción, xa que usa referencias celulares en lugar de números para o total do divisor, sería usar a función SUM para completar o divisor coa fórmula sendo:
= SUMPRODUCT (B3: B6, C3: C6) / SUM (B3: B6)
Normalmente é mellor introducir referencias celulares en vez de números reais en fórmulas, xa que simplifica a actualización se os datos da fórmula cambian.
Por exemplo, se os factores de ponderación de Asignacións cambiáronse a 0.5 no exemplo e para probas a 1.5, as dúas primeiras formas da fórmula deberían ser editadas manualmente para corrixir o divisor.
Na terceira variación, só se deben actualizar os datos das celas B3 e B4 e a fórmula recalculará o resultado.