Media procentelor dă alt rezultat decât procentul total (AVERAGE în loc de medie ponderată)
Media simplă a unor procente (AVERAGE pe coloana de rate) dă aceeași greutate unui magazin cu 50 de vizitatori și unuia cu 1000. Procentul total corect se calculează din totaluri: =SUM(C2:C5)/SUM(B2:B5), nu =AVERAGE(D2:D5).
Explicat de Tudor Andrei Dumitrescu, Senior Software Developer, fondatorul Academiei ProgramatorilorActualizat la
Ce înseamnă
Un procent pe rând (rata de conversie, procentul de promovare, marja) este un raport: ceva împărțit la altceva. Când vrei procentul pe tot tabelul, media acestor rapoarte nu e același lucru cu raportul totalurilor, pentru că rândurile au mărimi diferite.
=AVERAGE(D2:D5) tratează fiecare rând la fel, oricât de mare sau mic e. Un magazin cu 50 de vizitatori și o rată de 20% trage media în sus la fel de mult ca unul cu 1000 de vizitatori. Rezultatul nu dă eroare, dar spune altceva decât crezi: „media magazinelor”, nu „procentul total”.
Procentul total se calculează împărțind totalurile: cumpărătorii totali la vizitatorii totali. E echivalent cu o medie ponderată a ratelor, cu numărul de vizitatori ca pondere: SUMPRODUCT(D2:D5,B2:B5)/SUM(B2:B5).
De ce apare: cauzele frecvente
1AVERAGE pe coloana de procente
D6: =AVERAGE(D2:D5)D6: =C6/B6 (C6 și B6 = totalurile)Raportul totalurilor ține cont de mărimea fiecărui rând.
2Media ponderată, fără coloane de total
=AVERAGE(D2:D5)=SUMPRODUCT(D2:D5,B2:B5)/SUM(B2:B5)Fiecare rată se înmulțește cu numărul de vizitatori al rândului, apoi se împarte la totalul vizitatorilor.
3Media notelor fără credite
=AVERAGE(C2:C6)=SUMPRODUCT(C2:C6,B2:B6)/SUM(B2:B6)Materiile cu mai multe credite contează mai mult. Media ponderată cu creditele e media corectă.
Repară eroarea, direct în pagină
Pe rândul 6, B6 și C6 adună vizitatorii și cumpărătorii, iar D6 calculează rata totală de conversie cu =AVERAGE(D2:D5), care dă 10.5%. Repară formula din D6 ca să afișeze rata de conversie pe toate magazinele la un loc (cumpărători totali împărțiți la vizitatori totali).
Indiciu
Rata totală se calculează din totalurile de pe rândul 6, nu din procentele de deasupra.
Reparația
Împarte totalurile:
=C6/B6D6 afișează 5.25%.
De ce merge
AVERAGE dădea aceeași greutate fiecărui magazin, așa că magazinul cu doar 50 de vizitatori și o rată de 20% trăgea media în sus. Rata reală pe tot tabelul e numărul total de cumpărători (105) împărțit la numărul total de vizitatori (2000), adică 5.25%. Același rezultat îl dă media ponderată SUMPRODUCT(D2:D5,B2:B5)/SUM(B2:B5).
Alte forme ale mesajului
Average of percentages wrongExcel weighted averageMedia procentelor Excel greșităTotal percentage vs average percentage
Cum o eviți pe viitor
- Pentru un procent pe total, adună întâi numărătorii și numitorii, apoi împarte.
- Folosește AVERAGE pe procente doar dacă vrei explicit „media rândurilor”, și scrie asta în etichetă.
- Pentru medii ponderate (note cu credite, prețuri cu cantități) folosește
SUMPRODUCT(valori,ponderi)/SUM(ponderi). - Verifică rezultatul cu un rând foarte mic: dacă îl schimbi mult și totalul se mișcă mult, media nu e ponderată.
Întrebări frecvente
Când e corectă media simplă a procentelor?
Când toate rândurile au aceeași mărime (de exemplu toate magazinele au exact 1000 de vizitatori) sau când chiar vrei media rândurilor, nu procentul total.
Un PivotTable face asta corect?
Nu automat: Average pe un câmp de procente dă tot media simplă. Pune în pivot numărătorul și numitorul cu Sum și calculează raportul dintre ele.
Erori înrudite
AVERAGE dă o medie prea mică (include zerourile) AVERAGE include în medie toate celulele cu 0, iar dacă lunile fără date au 0 în loc să fie goale, media scade. Exclude zerourile cu AVERAGEIF: =AVERAGEIF(B2:B9,"<>0") sau lasă celulele fără date goale, pe care AVERAGE le ignoră. Totalul nu se potrivește cu suma valorilor afișate (rotunjire) Formatul cu 2 zecimale doar ascunde zecimalele, nu le elimină: SUM adună valorile reale, nu cele afișate, așa că totalul poate diferi cu un ban de suma pe care o vezi. Rotunjește chiar valorile cu ROUND, de exemplu =ROUND(B2*1.19,2), înainte să le aduni. Rezultat de 100 de ori prea mare: procentul e scris 21 în loc de 21% Dacă în celula cotei scrie 21, nu 21%, Excel înmulțește cu 21, nu cu 0.21, iar prețurile cu TVA ies de zeci de ori mai mari. Scrie cota ca procent (21%) sau ca zecimal (0.21); formula =B2*(1+$F$1) rămâne la fel. Învață Excel de la zero, cu exerciții la fiecare lecție
Curs Excel: lecții scurte, fiecare cu un exercițiu verificat automat, direct în browser. Primele 20 lecții sunt gratuite.
