Excel a făcut din calcularea mediei mai multor celule o sarcină foarte ușoară - trebuie doar să utilizați funcția IN MEDIE (IN MEDIE). Dar dacă unele valori au mai multă greutate decât altele? De exemplu, în multe cursuri, testele au mai multă greutate decât sarcinile. Pentru astfel de cazuri, este necesar să se calculeze medie ponderată.
Excel nu are o funcție pentru calcularea mediei ponderate, dar există o funcție care va face cea mai mare parte a muncii pentru tine: SUMPRODUCT (SUMA PRODUS). Și chiar dacă nu ați mai folosit niciodată această funcție, până la sfârșitul acestui articol o veți folosi ca un profesionist. Metoda pe care o folosim funcționează în orice versiune de Excel, precum și în alte foi de calcul, cum ar fi Google Sheets.
Pregătim masa
Dacă veți calcula o medie ponderată, veți avea nevoie de cel puțin două coloane. Prima coloană (coloana B în exemplul nostru) conține scorurile pentru fiecare temă sau test. A doua coloană (coloana C) conține ponderile. Mai multă greutate înseamnă mai multă influență a sarcinii sau a testului asupra notei finale.
Pentru a înțelege ce este greutatea, te poți gândi la ea ca un procent din nota finală. De fapt, nu este cazul, deoarece în acest caz ponderile ar trebui să adună până la 100%. Formula pe care o vom analiza în această lecție va calcula totul corect și nu depinde de cantitatea cu care se adună greutățile.
Introducem formula
Acum că tabelul nostru este gata, adăugăm formula în celulă B10 (orice celulă goală va face). Ca și în cazul oricărei alte formule din Excel, începem cu un semn egal (=).
Prima parte a formulei noastre este funcția SUMPRODUCT (SUMA PRODUS). Argumentele trebuie să fie incluse între paranteze, așa că le deschidem:
=СУММПРОИЗВ(
=SUMPRODUCT(
Apoi, adăugați argumentele funcției. SUMPRODUCT (SUMPRODUCT) poate avea mai multe argumente, dar de obicei sunt folosite două. În exemplul nostru, primul argument va fi un interval de celule. B2:B9A care conține scorurile.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
Al doilea argument va fi un interval de celule C2:C9, care conține greutățile. Aceste argumente trebuie separate prin punct și virgulă (virgulă). Când totul este gata, închideți parantezele:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Acum să adăugăm a doua parte a formulei noastre, care va împărți rezultatul calculat de funcție SUMPRODUCT (SUMPRODUS) prin suma greutăților. Vom discuta mai târziu de ce acest lucru este important.
Pentru a efectua operația de împărțire, continuăm formula deja introdusă cu simbolul / (bară oblică), apoi scrieți funcția USM (SUMĂ):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
Pentru funcție USM (SUM) vom specifica un singur argument – un interval de celule C2:C9. Nu uitați să închideți parantezele după ce ați introdus argumentul:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Gata! După apăsarea tastei Intrați, Excel va calcula media ponderată. În exemplul nostru, rezultatul final va fi 83,6.
Abordarea Noastră
Să defalcăm fiecare parte a formulei, începând cu funcția SUMPRODUCT (SUMPRODUCT) pentru a înțelege cum funcționează. Funcţie SUMPRODUCT (SUMPRODUCT) calculează produsul dintre scorul fiecărui articol și greutatea acestuia, apoi însumează toate produsele rezultate. Cu alte cuvinte, funcția găsește suma produselor, de unde și numele. Prin urmare Sarcini 1 înmulțiți 85 cu 5 și pentru Testul înmulțiți 83 cu 25.
Dacă vă întrebați de ce trebuie să înmulțim valorile în prima parte, imaginați-vă că, cu cât este mai mare greutatea sarcinii, cu atât mai multe ori trebuie să luăm în considerare nota pentru aceasta. De exemplu, Sarcina 2 numărat de 5 ori și Examenul final – de 45 de ori. De aceea Examenul final are un impact mai mare asupra notei finale.
Pentru comparație, la calcularea mediei aritmetice obișnuite, fiecare valoare este luată în considerare o singură dată, adică toate valorile au o greutate egală.
Dacă ai putea să te uiți sub capota unei funcții SUMPRODUCT (SUMPRODUCT), am văzut că de fapt ea crede asta:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
Din fericire, nu trebuie să scriem o formulă atât de lungă pentru că SUMPRODUCT (SUMPRODUCT) face toate acestea automat.
O funcție în sine SUMPRODUCT (SUMPRODUCT) ne returnează un număr mare − 10450. În acest moment, intră în joc a doua parte a formulei: /SUMA(C2:C9) or /SUMA(C2:C9), care returnează rezultatul în intervalul normal de scoruri, dând răspunsul 83,6.
A doua parte a formulei este foarte importantă deoarece vă permite să corectați automat calculele. Îți amintești că greutățile nu trebuie să adună până la 100%? Toate acestea datorită celei de-a doua părți a formulei. De exemplu, dacă creștem una sau mai multe valori ale greutății, a doua parte a formulei se va împărți pur și simplu la valoarea mai mare, rezultând din nou răspunsul corect. Sau putem face ponderile mult mai mici, de exemplu prin specificarea unor valori precum 0,5, 2,5, 3 or 4,5, iar formula va funcționa în continuare corect. Este grozav, nu?