Excel fixo que calcular a media de varias celas sexa unha tarefa moi sinxela: só tes que usar a función MEDIA (MEDIA). Pero e se uns valores teñen máis peso que outros? Por exemplo, en moitos cursos, as probas teñen máis peso que as tarefas. Para tales casos, é necesario calcular media ponderada.
Excel non ten unha función para calcular a media ponderada, pero hai unha función que fará a maior parte do traballo por ti: SUMPRODUCT (PRODUTO SUMA). E aínda que nunca utilizaches esta función antes, ao final deste artigo estarás a usalo como un profesional. O método que usamos funciona en calquera versión de Excel, así como noutras follas de cálculo como Google Sheets.
Preparamos a mesa
Se vas calcular unha media ponderada, necesitarás polo menos dúas columnas. A primeira columna (columna B no noso exemplo) contén as puntuacións de cada tarefa ou proba. A segunda columna (columna C) contén os pesos. Máis peso significa máis influencia da tarefa ou da proba na nota final.
Para entender o que é o peso, podes pensar nel como unha porcentaxe da túa nota final. De feito, non é así, xa que neste caso os pesos deberían sumar o 100%. A fórmula que analizaremos nesta lección calculará todo correctamente e non dependerá da cantidade que sumen os pesos.
Introducimos a fórmula
Agora que a nosa táboa está lista, engadimos a fórmula á cela B10 (calquera cela baleira servirá). Como con calquera outra fórmula en Excel, comezamos cun signo igual (=).
A primeira parte da nosa fórmula é a función SUMPRODUCT (PRODUTO SUMA). Os argumentos deben ir entre corchetes, polo que os abrimos:
=СУММПРОИЗВ(
=SUMPRODUCT(
A continuación, engade os argumentos da función. SUMPRODUCT (SUMPRODUCT) pode ter varios argumentos, pero normalmente úsanse dous. No noso exemplo, o primeiro argumento será un intervalo de celas. B2:B9A que contén as puntuacións.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
O segundo argumento será un rango de celas C2:C9, que contén os pesos. Estes argumentos deben estar separados por punto e coma (coma). Cando todo estea listo, pecha os corchetes:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Agora imos engadir a segunda parte da nosa fórmula, que dividirá o resultado calculado pola función SUMPRODUCT (SUMAPRODUTO) pola suma dos pesos. Discutaremos máis adiante por que isto é importante.
Para realizar a operación de división, continuamos coa fórmula xa introducida co símbolo / (barra recta) e despois escriba a función SUM (SUMA):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
Por función SUM (SUMA) especificaremos só un argumento: un rango de celas C2:C9. Non esquezas pechar os parénteses despois de introducir o argumento:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Listo! Despois de premer a tecla Entrar, Excel calculará a media ponderada. No noso exemplo, o resultado final será 83,6.
Como funciona
Imos desglosar cada parte da fórmula, comezando pola función SUMPRODUCT (SUMPRODUCT) para entender como funciona. Función SUMPRODUCT (SUMPRODUCT) calcula o produto da puntuación de cada elemento e o seu peso e, a continuación, suma todos os produtos resultantes. Noutras palabras, a función atopa a suma dos produtos, de aí o nome. Así para Tarefas 1 multiplica 85 por 5, e para A proba multiplica 83 por 25.
Se estás a preguntar por que hai que multiplicar os valores na primeira parte, imaxina que canto maior sexa o peso da tarefa, máis veces teremos que considerar a nota para ela. Por exemplo, Tarefa 2 contado 5 veces e Exame final - 45 veces. Por iso Exame final ten un maior impacto na nota final.
Para comparar, ao calcular a media aritmética habitual, cada valor tense en conta só unha vez, é dicir, todos os valores teñen o mesmo peso.
Se puideses mirar baixo o capó dunha función SUMPRODUCT (SUMPRODUCT), vimos que de feito ela cre isto:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
Por sorte, non necesitamos escribir unha fórmula tan longa porque SUMPRODUCT (SUMPRODUCT) fai todo isto automaticamente.
Unha función en si mesma SUMPRODUCT (SUMPRODUCT) devólvenos un número enorme − 10450. Neste punto, entra en xogo a segunda parte da fórmula: /SUMA(C2:C9) or /SUMA(C2:C9), que devolve o resultado ao rango normal de puntuacións, dando a resposta 83,6.
A segunda parte da fórmula é moi importante porque permite corrixir cálculos automaticamente. Lembras que os pesos non teñen que sumar o 100%? Todo isto grazas á segunda parte da fórmula. Por exemplo, se aumentamos un ou máis valores de peso, a segunda parte da fórmula simplemente dividirase polo valor maior, resultando de novo a resposta correcta. Ou podemos facer que os pesos sexan moito máis pequenos, por exemplo, especificando valores como 0,5, 2,5, 3 or 4,5, e a fórmula seguirá funcionando correctamente. É xenial, non?