Excel ha fet que calcular la mitjana de diverses cel·les sigui una tasca molt fàcil: només cal que utilitzeu la funció MITJANA (MITJANA). Però, què passa si alguns valors tenen més pes que altres? Per exemple, en molts cursos, les proves tenen més pes que les tasques. En aquests casos, cal calcular mitjana ponderada.
Excel no té una funció per calcular la mitjana ponderada, però hi ha una funció que farà la major part del treball per vosaltres: SUMPRODUCT (SUMA PRODUCTE). I fins i tot si mai no heu utilitzat aquesta funció abans, al final d'aquest article la fareu servir com un professional. El mètode que utilitzem funciona en qualsevol versió d'Excel, així com en altres fulls de càlcul com ara Google Sheets.
Preparem la taula
Si voleu calcular una mitjana ponderada, necessitareu almenys dues columnes. La primera columna (columna B en el nostre exemple) conté les puntuacions de cada tasca o prova. La segona columna (columna C) conté els pesos. Més pes significa més influència de la tasca o prova en la nota final.
Per entendre què és el pes, podeu pensar-ho com un percentatge de la vostra nota final. De fet, no és així, ja que en aquest cas els pesos haurien de sumar el 100%. La fórmula que analitzarem en aquesta lliçó ho calcularà tot correctament i no depèn de la quantitat que sumin els pesos.
Introduïm la fórmula
Ara que la nostra taula està preparada, afegim la fórmula a la cel·la B10 (qualsevol cel·la buida servirà). Com amb qualsevol altra fórmula a Excel, comencem amb un signe igual (=).
La primera part de la nostra fórmula és la funció SUMPRODUCT (SUMA PRODUCTE). Els arguments s'han de tancar entre claudàtors, així que els obrim:
=СУММПРОИЗВ(
=SUMPRODUCT(
A continuació, afegiu els arguments de la funció. SUMPRODUCT (SUMPRODUCT) pot tenir diversos arguments, però normalment s'utilitzen dos. En el nostre exemple, el primer argument serà un rang de cel·les. B2: B9A que conté les puntuacions.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
El segon argument serà un rang de cel·les C2:C9, que conté els pesos. Aquests arguments s'han de separar per un punt i coma (coma). Quan tot estigui a punt, tanqueu els claudàtors:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Ara afegim la segona part de la nostra fórmula, que dividirà el resultat calculat per la funció SUMPRODUCT (SUMAPRODUCTE) per la suma dels pesos. Més endavant parlarem de per què això és important.
Per realitzar l'operació de divisió, continuem la fórmula ja introduïda amb el símbol / (barra recta) i després escriviu la funció SUM (SUMA):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
Per a la funció SUM (SUMA) només especificarem un argument: un rang de cel·les C2:C9. No oblideu tancar els parèntesis després d'introduir l'argument:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
A punt! Després de prémer la tecla Enter, Excel calcularà la mitjana ponderada. En el nostre exemple, el resultat final serà 83,6.
Com funciona
Desglossem cada part de la fórmula, començant per la funció SUMPRODUCT (SUMPRODUCT) per entendre com funciona. Funció SUMPRODUCT (SUMPRODUCT) calcula el producte de la puntuació de cada element i el seu pes, i després suma tots els productes resultants. En altres paraules, la funció troba la suma dels productes, d'aquí el nom. Així per Tasques 1 multiplica 85 per 5, i per La prova multiplica 83 per 25.
Si us pregunteu per què hem de multiplicar els valors de la primera part, imagineu-vos que com més gran sigui el pes de la tasca, més vegades haurem de tenir en compte la nota d'aquesta. Per exemple, Tasca 2 comptat 5 vegades i Examen final - 45 vegades. Aixo es perqué Examen final té un impacte més gran en la nota final.
Per comparar, en calcular la mitjana aritmètica habitual, cada valor només es té en compte una vegada, és a dir, tots els valors tenen el mateix pes.
Si poguéssiu mirar sota el capó d'una funció SUMPRODUCT (SUMPRODUCT), vam veure que de fet ella creu això:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
Per sort, no necessitem escriure una fórmula tan llarga perquè SUMPRODUCT (SUMPRODUCT) fa tot això automàticament.
Una funció en si mateixa SUMPRODUCT (SUMPRODUCT) ens retorna un nombre enorme − 10450. En aquest punt, entra en joc la segona part de la fórmula: /SUMA(C2:C9) or /SUMA(C2:C9), que retorna el resultat al rang normal de puntuacions, donant la resposta 83,6.
La segona part de la fórmula és molt important perquè permet corregir automàticament els càlculs. Recordeu que els pesos no han de sumar fins al 100%? Tot això gràcies a la segona part de la fórmula. Per exemple, si augmentem un o més valors de pes, la segona part de la fórmula simplement es dividirà pel valor més gran, donant lloc de nou a la resposta correcta. O podem fer que els pesos siguin molt més petits, per exemple, especificant valors com 0,5, 2,5, 3 or 4,5, i la fórmula encara funcionarà correctament. És genial, oi?