01 din 01
Funcția Excel SUMPRODUCT
Media generală ponderată vs medie ponderată
De obicei, atunci când se calculează media medie sau aritmetică, fiecare număr are valoare egală sau greutate.
Media este calculată prin adăugarea unui interval de numere împreună și apoi împărțind acest total la numărul de valori din interval .
Un exemplu ar fi (2 + 3 + 4 + 5 + 6) / 5 care dă o medie neponderată de 4.
În Excel, astfel de calcule sunt ușor de realizat utilizând funcția AVERAGE .
O medie ponderată, pe de altă parte, consideră că unul sau mai multe numere din domeniu sunt mai valoroase sau au o greutate mai mare decât celelalte numere.
De exemplu, anumite note din școală, cum ar fi examenele de la jumătatea perioadei sau de la sfârșitul anului, merită, de obicei, mai mult decât teste sau sarcini regulate.
Dacă medierea este folosită pentru a calcula nota finală a elevului, examenele medii și finale ar avea o pondere mai mare.
În Excel, mediile ponderate pot fi calculate utilizând funcția SUMPRODUCT .
Cum funcționează funcția SUMPRODUCT
Ce face SUMPRODUCT este multiplicarea elementelor a două sau mai multe matrice și apoi adăugarea sau însumarea produselor.
De exemplu, într-o situație în care două arhive cu patru elemente fiecare sunt introduse ca argumente pentru funcția SUMPRODUCT:
- primul element al matricei1 este înmulțit cu primul element din matrice2;
- al doilea element al matricei 1 este înmulțit cu al doilea element al matricei2;
- al treilea element al matricei1 este înmulțit cu al treilea element al matricei2;
- al patrulea element al matricei1 este înmulțit cu al patrulea element al matricei2.
Apoi, produsele celor patru operații de multiplicare sunt însumate și returnate de funcție ca rezultat.
Excel SUMPRODUCT Sintaxă de funcții și argumente
Sintaxa unei funcții se referă la aspectul funcției și include numele funcției, paranteze și argumente.
Sintaxa pentru funcția SUMPRODUCT este:
= SUMPRODUCT (array1, array2, array3, ... array255)
Argumentele pentru funcția SUMPRODUCT sunt:
array1: (necesar) argumentul primului tablou.
array2, array3, ... array255: (opțional) matrice suplimentare, până la 255. Cu două sau mai multe matrice, funcția înmulțește elementele fiecărui matrice împreună și apoi adaugă rezultatele.
- elementele matricei pot fi trimiteri de celule la locația datelor din foaia de lucru sau numere separate de operatorii aritmetici - cum ar fi semne plus (+) sau minus (-). Dacă cifrele sunt introduse fără a fi separate de operatori, Excel le tratează ca date text. Această situație este acoperită în exemplul de mai jos.
Notă :
Toate argumentele array trebuie să aibă aceeași dimensiune. Sau, cu alte cuvinte, trebuie să existe același număr de elemente în fiecare matrice. Dacă nu, SUMPRODUCT returnează #VALUE! valoarea erorii.
Dacă elementele matricei nu sunt numere - cum ar fi datele de text - SUMPRODUCT le tratează ca zerouri.
Exemplu: Calculați media ponderată în Excel
Exemplul prezentat în imaginea de mai sus calculează media ponderată pentru marcajul final al unui student folosind funcția SUMPRODUCT.
Funcția realizează acest lucru prin:
- înmulțirea diferitelor mărci prin factorul lor individual de greutate;
- adăugând produsele acestor operațiuni de multiplicare;
- împărțit suma de mai sus cu totalul coeficientului de ponderare 7 (1 + 1 + 2 + 3) pentru cele patru evaluări.
Introducerea formulei de ponderare
Ca majoritatea celorlalte funcții din Excel, SUMPRODUCT este introdus în mod normal într-o foaie de lucru folosind caseta de dialog a funcției. Cu toate acestea, deoarece formula de ponderare utilizează SUMPRODUCT într-un mod nestandard - rezultatul funcției este împărțit de factorul de greutate - formula de ponderare trebuie introdusă într-o celulă de foaie de lucru .
Următoarele etape au fost utilizate pentru a introduce formula de ponderare în celula C7:
- Faceți clic pe celula C7 pentru a deveni celula activă - locul în care va fi afișat semnul final al elevului
- Introduceți următoarea formulă în celulă:
= SUMPRODUCT (B3: B6, C3: C6) / (1 + 1 + 2 + 3)
Apăsați tasta Enter de pe tastatură
- Răspunsul 78.6 trebuie să apară în celula C7 - răspunsul dvs. poate avea mai multe zecimale
Media nesemnată pentru cele patru mărci ar fi de 76.5
Deoarece elevul a obținut rezultate mai bune pentru examenele la jumătatea și ultimul său examen, ponderarea mediei a ajutat la îmbunătățirea marcajului său general.
Variante de formulare
Pentru a sublinia că rezultatele funcției SUMPRODUCT sunt împărțite la suma ponderilor pentru fiecare grup de evaluare, divizorul - partea care face divizarea - a fost introdus ca (1 + 1 + 2 + 3).
Formula generală de ponderare ar putea fi simplificată prin introducerea numărului 7 (suma greutăților) ca divizor. Formula ar fi apoi:
= SUMPRODUCT (B3: B6, C3: C6) / 7
Această alegere este bună dacă numărul elementelor din matricea de ponderare este mic și poate fi ușor asociat, dar devine mai puțin eficient deoarece numărul elementelor din matricea de ponderare crește, ceea ce face ca adăugarea lor să fie mai dificilă.
O altă opțiune, și probabil cea mai bună alegere - deoarece folosește mai degrabă referințe celulare decât numere în totalul divizorului - ar fi să folosească funcția SUM pentru a delimita divizorul cu formula:
= SUMPRODUCT (B3: B6, C3: C6) / SUM (B3: B6)
Este, de obicei, cel mai bine să introduceți în formule referințe de celule decât numere reale, deoarece simplifică actualizarea acestora dacă se schimbă datele formulei.
De exemplu, dacă factorii de ponderare pentru alocări au fost modificați la 0,5 în exemplul respectiv și pentru teste la 1,5, primele două forme ale formulei ar trebui să fie editate manual pentru a corecta divizorul.
În a treia variantă, numai datele din celulele B3 și B4 trebuie să fie actualizate, iar formula va recalcula rezultatul.