Què cal saber
- Sintaxi: =SUMPRODUCT(Matriu1, Matriu2, Matriu3, …Matriu255).
- En un càlcul mitjà ponderat, alguns números de l'interval valen més que altres.
- La fórmula de ponderació de SUMPRODUCT és una fórmula no estàndard que s'ha d'escriure en una cel·la perquè funcioni.
Aquest article explica com utilitzar la funció SUMAPRODUCTE per calcular una mitjana ponderada en fórmules de Microsoft Excel.
Introduïu la fórmula de ponderació
Com la majoria de les altres funcions d'Excel, SUMPRODUCT es pot introduir en un full de treball mitjançant la Biblioteca de funcions a la pestanya Fórmules. Com que la fórmula de ponderació d'aquest exemple utilitza SUMPRODUCT d'una manera no estàndard (el resultat de la funció es divideix pel factor de ponderació), la fórmula de ponderació s'ha d'escriure en una cel·la del full de treball.
L'exemple que es mostra en aquest article calcula la mitjana ponderada de la nota final d'un estudiant mitjançant la funció SUMPRODUCT.
La funció ho aconsegueix mitjançant:
- Multiplicant les diferents marques pel seu factor de pes individual.
- Sumant els productes d'aquestes operacions de multiplicació.
-
Divideix la suma anterior pel total del factor de ponderació 7 (1+1+2+3) per a les quatre avaluacions.
Per introduir la fórmula SUMPRODUCT per calcular una mitjana ponderada, obriu un full de treball en blanc, introduïu les dades a les files 1 fins a 6 de la imatge de d alt i seguiu aquests passos:
- Seleccioneu la cel·la C7 per convertir-la en la cel·la activa (aquesta és la ubicació on es mostrarà la nota final de l'estudiant).
- Escriviu la fórmula =SUMAPRODUCTE(B3:B6, C3:C6)/(1+1+2+3) a la cel·la. La fórmula apareix a la barra de fórmules.
- Premeu la tecla Enter al teclat.
- La resposta 78.6 apareix a la cel·la C7 (la vostra resposta pot tenir més decimals).
La mitjana no ponderada per a les mateixes quatre notes seria 76,5. Com que l'estudiant va obtenir millors resultats als exàmens parcials i finals, ponderar la mitjana va ajudar a millorar la nota general.
Com funciona la funció SUMPRODUCT
Normalment, quan calculeu una mitjana o una mitjana aritmètica, cada nombre té el mateix valor o pes. La mitjana es calcula sumant un rang de nombres i després dividint aquest total pel nombre de valors de l'interval. D' altra banda, una mitjana ponderada considera que un o més nombres de l'interval valen més o tenen un pes més gran que els altres nombres.
SUMPRODUCT multiplica els elements de dues o més matrius i després afegeix els productes per calcular mitjanes ponderades. Per exemple, determinades notes a l'escola, com ara els exàmens parcials i finals, solen valer més que les proves o treballs habituals. Si s'utilitza la mitjana per calcular la nota final d'un estudiant, els exàmens parcials i finals tindran una ponderació més gran.
En una situació en què s'introdueixen dues matrius amb quatre elements cadascun com a arguments per a la funció SUMPRODUCT:
- El primer element de la matriu1 es multiplica pel primer element de la matriu2.
- El segon element de la matriu1 es multiplica pel segon element de la matriu2.
- El tercer element de la matriu1 es multiplica pel tercer element de la matriu2.
- El quart element de la matriu1 es multiplica pel quart element de la matriu2.
A continuació, els productes de les quatre operacions de multiplicació es sumen i la funció retorna com a resultat.
SUMPRODUCT Sintaxi i arguments
La sintaxi d'una funció fa referència a la disposició de la funció i inclou el nom, els claudàtors i els arguments de la funció. La sintaxi de la funció SUMPRODUCT és:
=SUMAPRODUCTE(Matriu1, Matriu2, Matriu3, … Matriu255)
Els arguments de la funció SUMAPRODUCTE són:
- Array1: El primer argument de la matriu (obligatori).
- Array2, Array3, … Array255: matrius addicionals (opcionals), fins a 255. Amb dues o més matrius, la funció multiplica els elements de cada matriu junts i després afegeix els resultats.
Els elements de la matriu poden ser referències de cel·les a la ubicació de les dades al full de treball o números separats per operadors aritmètics, com ara els signes més (+) o menys (-). Si introduïu números que no estan separats per operadors, Excel els tractarà com a dades de text.
Els arguments de la matriu han de tenir el mateix nombre d'elements a cada matriu. Si no, SUMPRODUCT retorna el VALOR! valor d'error. Si els elements de la matriu no són números, com ara dades de text, SUMPRODUCT els tracta com a zeros.
Variacions de la fórmula SUMPRODUCT
Per destacar que els resultats de la funció SUMAPRODUCTE es divideixen per la suma dels pesos de cada grup d'avaluació, el divisor (la part que fa la divisió) s'introdueix com:
(1+1+2+3)
La fórmula de ponderació global es podria simplificar introduint el número 7 (la suma dels pesos) com a divisor. Aleshores la fórmula seria:
=SUMAPRODUCTE(B3:B6, C3:C6)/7
Aquesta opció està bé si el nombre d'elements de la matriu de ponderació és petit i es poden sumar fàcilment, però es torna menys efectiu a mesura que augmenta el nombre d'elements de la matriu de ponderació, dificultant la seva addició.
Una altra opció, i probablement la millor opció, donat que utilitza referències de cel·les en lloc de nombres per sumar el divisor, seria utilitzar la funció SUMA per sumar el divisor. Aleshores la fórmula és:
=SUMAPRODUCTE(B3:B6, C3:C6)/SUMA(B3:B6)
En general, és millor introduir referències de cel·les en lloc de nombres reals a les fórmules. Això simplifica l'actualització si canvien les dades de la fórmula.
Per exemple, si els factors de ponderació de les tasques es canvien a 0,5 a l'exemple i les proves es canvien a 1,5, les dues primeres formes de la fórmula s'han d'editar manualment per corregir el divisor.
A la tercera variació, només s'han d'actualitzar les dades de les cel·les B3 i B4 i la fórmula torna a calcular el resultat.
