01 de 01
Funció SUMPRODUCT d'Excel
Visió general mitjana ponderada vs. no ponderada
En general, quan es calcula la mitjana o la mitjana aritmètica, cada número té igual valor o pes.
La mitjana es calcula sumant un rang de nombres i dividint aquest total pel nombre de valors del rang .
Un exemple seria (2 + 3 + 4 + 5 + 6) / 5 que dóna una mitjana no ponderada de 4.
En Excel, aquests càlculs es realitzen fàcilment amb la funció AVERAGE .
Una mitjana ponderada, d'altra banda, considera que un o més nombres en el rang val més, o tenen un pes major que els altres nombres.
Per exemple, certes marques a l'escola, com l'examen intermedi i final, solen valer més que proves o assignacions habituals.
Si es fa servir un mètode intermedi per calcular la nota final d'un alumne, els exàmens intermedis i finals obtindrien un major pes.
En Excel, les mitjanes ponderades es poden calcular utilitzant la funció SUMPRODUCT .
Com funciona la funció SUMPRODUCT
El que SUMPRODUCT fa és multiplicar els elements de dues o més matrius i, a continuació, afegir o sumar els productes.
Per exemple, en una situació en què s'introdueixen dos conjunts 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 resumeixen i retornen per la funció com a resultat.
Excel SUMPRODUCT Function Sintaxi i Arguments
La sintaxi d' una funció es refereix al disseny de la funció i inclou el nom de la funció, claudàtors i arguments.
La sintaxi de la funció SUMPRODUCT és:
= SUMPRODUCT (array1, array2, array3, ... array255)
Els arguments de la funció SUMPRODUCT són:
array1: (obligatori) el primer argument de la matriu.
array2, array3, ... array255: (opcional) matrius addicionals, fins a 255. Amb dues o més matrius, la funció multiplica els elements de cada matriu i afegeix els resultats.
- els elements de la matriu poden ser referències de cel·les a la ubicació de les dades del full de treball o els números separats per operadors aritmètics , com ara els signes més (+) o menys (-). Si s'introdueixen números sense ser separats pels operadors, Excel els tracta com a dades de text. Aquesta situació es cobreix en l'exemple següent.
Nota :
Tots els arguments de la matriu han de ser de la mateixa mida. O, en altres paraules, ha d'haver-hi el mateix nombre d'elements en cada matriu. Si no, SUMPRODUCT torna #VALUE! valor d'error.
Si els elements de matriu no són nombres, com ara dades de text, SUMPRODUCT els tracta com zeros.
Exemple: calcula la mitjana ponderada a Excel
L'exemple que es mostra a la imatge anterior calcula la mitjana ponderada de la nota final de l'alumne mitjançant la funció SUMPRODUCT.
La funció realitza això per:
- multiplicant les diverses marques pel seu factor de pes individual;
- afegint conjuntament els productes d'aquestes operacions de multiplicació;
- va dividir la suma anterior pel total del factor de ponderació 7 (1 + 1 + 2 + 3) per a les quatre avaluacions.
Introduir la fórmula de ponderació
Igual que la majoria de les altres funcions d'Excel, SUMPRODUCT normalment s'introdueix en un full de càlcul utilitzant el quadre de diàleg de la funció. No obstant això, atès que la fórmula de ponderació utilitza SUMPRODUCT d'una manera no estàndard: el resultat de la funció es divideix pel factor de pes, la fórmula de ponderació s'ha d'escriure en una cel·la de full de càlcul .
Es van utilitzar els passos següents per introduir la fórmula de ponderació a la cel·la C7:
- Feu clic a la cel·la C7 per fer-la la cel·la activa : la ubicació on es mostrarà la nota final de l'alumne
- Escriviu la següent fórmula a la cel·la:
= SUMPRODUCT (B3: B6, C3: C6) / (1 + 1 + 2 + 3)
Premeu la tecla Enter al teclat
- La resposta 78.6 hauria d'aparèixer a la cel·la C7: la vostra resposta pot tenir més llocs decimals
La mitjana no ponderada de les mateixes quatre marques seria de 76,5
Atès que l'estudiant va obtenir millors resultats per a la seva part mitja i els exàmens finals, la ponderació de la mitjana va ajudar a millorar la seva nota global.
Variacions de fórmules
Per ressaltar que els resultats de la funció SUMPRODUCT es divideixen per la suma dels pesos per a cada grup d'avaluació, el divisor - la part que fa la divisió - s'ha introduït com (1 + 1 + 2 + 3).
La fórmula global de ponderació es pot simplificar introduint el número 7 (la suma dels pesos) com a divisor. La fórmula seria llavors:
= SUMPRODUCT (B3: B6, C3: C6) / 7
Aquesta opció està bé si la quantitat d'elements de la matriu de ponderació és petita i es pot afegir fàcilment, però es fa menys eficaç ja que la quantitat d'elements de la matriu de ponderació augmenta fent que la seva addició sigui més difícil.
Una altra opció, i probablement la millor opció, ja que utilitza referències cel·lulars en comptes de números en totalitzar el divisor, seria utilitzar la funció SUM per a totalitzar el divisor amb la fórmula:
= SUMPRODUCT (B3: B6, C3: C6) / SUM (B3: B6)
En general, és millor introduir referències de cel·les més que números reals en fórmules ja que simplifica l'actualització si la informació de la fórmula canvia.
Per exemple, si els factors de ponderació de Tasques es van canviar a 0.5 en l'exemple i en Proves a 1.5, les dues primeres formes de la fórmula haurien de ser modificades manualment per corregir el divisor.
En la tercera variació, només cal actualitzar les dades de les cel·les B3 i B4 i la fórmula tornarà a calcular el resultat.