Com es calcula una mitjana ponderada a Excel
Una mitjana ponderada és la que té en compte la importància o pes de cada valor. Aquest article us mostrarà com utilitzar les funcions SUMPRODUCT i SUM d'Excel de forma individual i com combinar-les per calcular una mitjana ponderada.
Què és una mitjana ponderada?
Una mitjana ponderada és una mitjana que té en compte la importància o pes de cada valor. Un bon exemple seria calcular la nota final d’un estudiant en funció del seu rendiment en diferents tasques i proves. Les tasques individuals normalment no compten tant per obtenir una nota final com l’examen final, com ara qüestionaris, proves i exàmens finals, amb diferents pesos. La mitjana ponderada es calcula com la suma de tots els valors multiplicats pels seus pesos dividits per la suma de tots els pesos.
L'exemple següent mostrarà com s'utilitzen les funcions SUMPRODUCT i SUM d'Excel per calcular una mitjana ponderada.
Vegem un exemple
Per al nostre exemple, vegem els resultats de les proves i dels exàmens d'un estudiant. Hi ha sis qüestionaris per valor del 5% de la nota total, dos exàmens per valor del 20% de la nota total i un examen final per valor del 30% de la nota total. La nota final de l’alumne serà una mitjana ponderada i utilitzarem les funcions SUMPRODUCT i SUM per calcular-la.
Com podeu veure a la taula següent, ja hem assignat els pesos relatius a cada qüestionari i examen a la columna D.
Primer pas: calculeu el SUMPRODUCT
En primer lloc, vegem com funciona la funció SUMPRODUCT. Comenceu seleccionant la cel·la on voleu que aparegui el resultat (al nostre exemple, és la cel·la D13). A continuació, aneu al menú "Fórmules", seleccioneu el menú desplegable "Matemàtiques i Trig", desplaceu-vos fins a la part inferior i feu clic a la funció "SUMPRODUCT".
Apareixerà la finestra "Arguments de la funció".
Al quadre "Array1", seleccioneu les puntuacions de l'estudiant. Aquí seleccionem totes les cel·les amb puntuacions reals a la columna C.
A continuació, utilitzeu el quadre "Array2" per seleccionar els pesos de les proves i dels exàmens. Per a nosaltres, es troben a la columna D.
Feu clic a "D'acord" quan hàgiu acabat.
La funció SUMPRODUCT multiplicarà cada puntuació pel seu pes corresponent i retornarà la suma de tots aquests productes.
Segon pas: calculeu la SUMA
Vegem ara com funciona la funció SUM. Seleccioneu la cel·la on voleu que apareguin els resultats (al nostre exemple, és la cel·la D14). A continuació, aneu al menú "Fórmules", seleccioneu el menú desplegable "Matemàtiques i activacions", desplaceu-vos fins a la part inferior i feu clic a la funció "SUMA".
Apareixerà la finestra "Arguments de la funció".
Per al quadre "Número1", seleccioneu tots els pesos.
Feu clic a "D'acord".
La funció SUMA sumarà tots els valors.
Tercer pas: combineu SUMPRODUCT i SUM per calcular la mitjana ponderada
Ara podem combinar les dues funcions per determinar la nota final de l’alumne en funció de les seves puntuacions i dels pesos de cada puntuació. Seleccioneu la cel·la on ha d’anar la mitjana ponderada (per a nosaltres, la cel·la D15) i, a continuació, escriviu la fórmula següent a la barra de funcions.
= SUMPRODUCT (C3: C11, D3: D11) / SUM (D3: D11)
Premeu "Retorn" després d'escriure la fórmula per veure la mitjana ponderada.
I aquí ho teniu. És un exemple bastant senzill, però és bo per mostrar com funcionen les mitjanes ponderades.
