Cum se calculează o medie ponderată în Excel cu procente

Calcularea unei medii ponderate în Excel cu procente vă oferă puterea de a trata date care au grade diferite de importanță.

O medie ponderată este similară cu media standard, dar pune accent pe valori specifice pe baza ponderilor lor atribuite. Când diferitele ponderi sunt exprimate în procente, rezultatele sunt adesea mai semnificative.

În acest articol, veți afla pașii pentru a calcula o medie ponderată folosind procente în Excel. Acoperim formulele, funcțiile și sfaturile Excel necesare pe care trebuie să le cunoașteți.

Să intrăm în asta.

Cuprins

• Ce este o medie ponderată?
  • Folosind procente sau unități
• Configurarea foii de lucru Excel
  • Formatarea celulelor pentru procente
• Cum se calculează o medie ponderată în Excel
  • Utilizarea funcției Excel SUMPRODUCT
  • Împărțirea la greutatea totală
  • Instrucțiuni pentru Excel
  • Defalcarea formulei
• Scenarii din lumea reală
  • Sisteme de clasificare
  • Analiză financiară
• 4 Greșeli frecvente și depanare
  • 1. Date și ponderi nealiniate
  • 2. Nu se contabilizează procentele
  • 3. Referințe incorecte pentru celule
  • 4. Folosind o funcție greșită
• Cuvântul nostru final

Ce este o medie ponderată?

Mediile ponderate și procentele sunt concepte esențiale în multe domenii, inclusiv finanțe, statistici și analiza datelor.

O medie ponderată este un tip de medie care ia în considerare semnificația relativă a unui set de valori.

Să comparăm asta cu media simplă pe care probabil ai folosit-o de multe ori. Media aritmetică simplă tratează toate valorile în mod egal atunci când calculează rezultatul.

Cu o medie ponderată, nu toate valorile au o greutate egală. Ponderile sunt atribuite valorilor în funcție de importanța sau relevanța acestora.

În termeni matematici, o medie ponderată se calculează prin:

1. înmulțind fiecare valoare cu greutatea ei corespunzătoare
2. însumând produsele pentru a obține totalul
3. împărțind totalul la suma greutăților

Formula matematică poate fi reprezentată astfel:

Medie ponderată = (?(valoare × greutate)) / ?(greutate)

Puteți scrie formula matematică atunci când lucrați cu macrocomenzi și VBA.

Cu toate acestea, acest tutorial folosește câteva funcții utile în Microsoft Excel pentru a simplifica calculul. Veți descoperi că sunt comenzi rapide ușor de utilizat.

Folosind procente sau unități

Unele calcule ponderate folosesc procente, iar altele folosesc unități.

De exemplu, dacă faceți o medie a prețurilor într-un magazin, veți folosi unități monetare care reprezintă prețul unui articol.

Dacă un creion este cincizeci de cenți și un stilou este un dolar, atunci greutățile sunt 0,5 și, respectiv, 1,0.

Este mai probabil ca notele de la facultate să se bazeze pe procente. De exemplu, examenului intermediar i se poate atribui 40% din nota totală, în timp ce 60% este alocată examenului final.

Acest articol se concentrează pe calcularea și afișarea mediilor folosind procente.

Configurarea foii de lucru Excel

Înainte de a calcula o medie ponderată în Excel, trebuie să configurați foaia de lucru cu datele necesare. Urmați acești pași:

1. Introduceți valorile datelor într-o coloană (de exemplu, coloana B).
2. Atribuiți ponderi ca procent într-o coloană separată (de exemplu, coloana C).
3. Verificați dacă ponderile se adună până la 100%.

Acest tutorial începe cu un exemplu din lumea sportului. Antrenorii își evaluează jucătorii după diferite atribute, iar unele atribute sunt mai valoroase decât altele.

Antrenorul decide asupra ponderii procentuale pentru fiecare atribut și apoi punctează jucătorul. Media ponderată oferă o imagine mai completă a modului în care jucătorul măsoară.

Copiați acest tabel în șablonul dvs. Excel:

Greutățile de mai sus sunt reprezentate în procente, iar suma lor este de 100%.

Formatarea celulelor pentru procente

Pentru claritate, este esențial să formatați celulele care conțin valorile de greutate ca procente. Iată sarcinile pas cu pas:

1. Selectați celulele de greutate care trebuie formatate (de exemplu, coloana B).
2. Faceți clic pe fila „Acasă” din bara de instrumente Excel.
3. Găsiți grupul „Număr” și faceți clic pe butonul „Stil procentual” (%). Aceasta va formata celulele selectate ca procente și le va afișa cu semnul %.

După parcurgerea acestor pași, foaia de lucru Excel ar trebui să fie acum configurată și gata pentru calcularea mediei ponderate. Asigurați-vă că datele și valorile de greutate sunt introduse corect și că celulele care conțin ponderile sunt formatate ca procente pentru o lizibilitate îmbunătățită.

Cum se calculează o medie ponderată în Excel

Calculul unei medii ponderate presupune o serie de pași simpli folosind funcțiile SUMPRODUCT și SUM.

Aceste funcții pot fi combinate pentru a găsi o valoare medie finală care ia în considerare importanța variată sau ponderile acordate diferitelor valori.

Utilizarea funcției Excel SUMPRODUCT

Formula Excel SUMPRODUCT este utilizată pentru a multiplica două sau mai multe matrice de valori și a returna suma produselor lor.

O matrice se referă la o listă de valori. Gama de celule care conțin cantitățile este o singură matrice. Matricea care conține prețul este o a doua matrice.

Împărțirea la greutatea totală

După ce ați folosit funcția SUMPRODUS pentru a înmulți și însumați cantitățile și prețurile articolelor, trebuie să o împărțiți la suma ponderilor pentru a calcula media ponderată.

Să presupunem că greutatea totală reprezintă suma cantităților din coloana B.

Folosind funcția SUM, astfel: SUM(B2:B4), veți obține greutatea totală.

Această cifră este folosită pentru a împărți în rezultatul rezultatului SUMPRODUCT.

Instrucțiuni pentru Excel

Urmați acești pași în foaia de lucru Excel :

1. Faceți clic pe o celulă goală, unde va fi afișată media ponderată.
2. Introduceți următoarea formulă: =SUMPRODUCT(B2:B4, C2:C4)/SUM(B2:B4)
3. Apasa Enter'.

După ce ați finalizat acești pași, Excel va afișa prețul mediu ponderat în celula pe care ați selectat-o.

Dacă urmați acest exemplu, este posibil să vedeți un număr lung în celula rezultat cu multe zecimale.

În acest moment, ar trebui să ordonați afișajul formatând celula rezultat pentru a afișa două zecimale.

Defalcarea formulei

Iată o defalcare a formulei din secțiunea anterioară:

• SUMPRODUS(B2:B4, C2:C4) obține suma produselor din cantitățile de articole înmulțită cu prețurile lor respective
• SUM(B2:B4) calculează greutatea totală (suma cantităților de articole)
• SUMPRODUS(B2:B4; C2:C4)/SUM(B2:B4) împarte suma produselor la greutatea totală, furnizând media ponderată

Scenarii din lumea reală

Două aplicații comune ale acestei formule includ sistemele de notare și analiza financiară.

Aceste exemple demonstrează cum se aplică medii ponderate în Excel folosind procente ca ponderi.

Sisteme de clasificare

Când profesorii și profesorii evaluează performanța elevilor la un curs, ei folosesc adesea medii ponderate pentru a determina nota finală. În acest caz, fiecare temă, test sau examen are o pondere sau un procent specific care contribuie la nota generală.

De exemplu, să presupunem următoarea defalcare a notelor:

• Teme: 40% (în total 4, fiecare valorând 10%)
• Teste: 20% (în total 2, fiecare valorând 10%)
• Examen intermediar: 20%
• Examenul final: 20%

Pentru a urma exemplul, puteți copia datele din acest tabel în foaia de calcul. Puneți prima coloană din tabel în coloana A.

Pentru a calcula media ponderată în Excel, puteți utiliza funcția SUMPRODUS.

Presupunând că notele sunt în coloana B (de la B2 la B9) și ponderile corespunzătoare exprimate în procente sunt în coloana C (de la C2 la C9), formula va arăta astfel:

=SUMAPRODUS(B2:B9; C2:C9)/SUMA(C2:C9)

Această formulă Excel calculează media ponderată folosind notele și procentele furnizate.

Analiză financiară

Mediile ponderate joacă, de asemenea, un rol esențial în analiza financiară, în special atunci când se evaluează performanța unui portofoliu de acțiuni . În astfel de cazuri, ponderea acordată fiecărui stoc se poate baza pe procentul de investiție alocat.

Să presupunem că dețineți următoarele acțiuni în portofoliu:

Presupunând că prețurile acțiunilor sunt în coloana B (de la B2 la B4), acțiunile în coloana C (de la C2 la C4) și procentele de investiții în coloana D (de la D2 la D4), formula medie ponderată va fi următoarea:

=SUMAPRODUS(B2:B4; D2:D4)/SUMA(D2:D4)

Formula de mai sus calculează prețul mediu ponderat al acțiunilor pe baza procentului de investiție pentru fiecare acțiune. Această valoare ajută la evaluarea performanței globale a portofoliului.

Această captură de ecran arată calculul și rezultatele:

Vă puteți îmbunătăți și mai bine prezentările prin codificarea culorilor valorilor care se situează peste sau sub media ponderată. De exemplu, valorile mai mari ar putea fi evidențiate cu verde.

Pentru a face acest lucru, utilizați formatarea condiționată pentru a adăuga o regulă de „evidențiere celulă” cu condiția „mai mare decât”. Aceasta vă permite să specificați pragul și să alegeți culoarea de evidențiere.

Analiza prețului acțiunilor combină adesea puterea mediilor ponderate cu frecvența prețurilor de închidere în anumite intervale de preț. Analiștii atribuie ponderi diferitelor intervale de preț și folosesc Excel pentru a crea un tabel de frecvență care numără de câte ori prețul unei acțiuni a scăzut într-un interval de preț în decurs de treizeci de zile.

Acest lucru permite calcularea prețurilor medii zilnice de închidere pe baza frecvenței. Acest videoclip util vă arată cum să faceți tabele de frecvență în Excel:

4 Greșeli frecvente și depanare

Iată cele mai frecvente patru greșeli pe care le veți întâlni atunci când calculați o medie ponderată în Excel.

1. Date și ponderi nealiniate

Datele și ponderile nealiniate pot duce, de asemenea, la rezultate medii ponderate inexacte. Precizia este condiționată de alinierea corectă a datelor și a greutăților în Excel.

Datele ar trebui să fie listate în coloane adiacente, fiecare valoare corespunzătoare ponderii sale respective:

Dacă observați că datele și ponderile sunt nealiniate, ajustați coloanele din foaia de calcul pentru a asigura o potrivire adecvată între valori și ponderile corespunzătoare.

2. Nu se contabilizează procentele

Când aveți de-a face cu procente ca ponderi, asigurați-vă că ponderile sunt exprimate ca valori zecimale. De exemplu, dacă o pondere este reprezentată ca 20%, echivalentul zecimal ar trebui să fie 0,20.

Pentru a converti un procent într-o zecimală, împărțiți procentul la 100:

=C2 / 100

Luând în considerare corect procentele, calcularea mediei ponderate va produce rezultate precise.

3. Referințe incorecte pentru celule

Este esențial să vă asigurați că referințele celulelor sunt exacte. Referințele incorecte ale celulelor pot duce la erori sau rezultate inexacte în calcul.

Verificați dacă formulele includ intervalele de celule adecvate atât pentru valori, cât și pentru greutăți.

4. Folosind o funcție greșită

Probabil ați folosit de multe ori funcția medie normală a Excel. Această funcție nu este utilizată pentru mediile ponderate.

Abaterea standard și media mobilă sunt modalități de a captura modificările mediilor datelor. Aceste calcule folosesc formule diferite.

Funcția AVERAGEIF vă permite să filtrați valorile care nu îndeplinesc anumite criterii. Cu toate acestea, este mai bine să atribuiți valorilor un procent mic atunci când calculați o medie ponderată.

Cuvântul nostru final

În acest articol, am demonstrat procesul de calcul al mediilor ponderate în Excel cu procente. Mediile ponderate oferă informații valoroase despre seturile de date, subliniind importanța relativă a unui număr de valori.

Folosind funcția SUMPRODUS, Excel poate calcula cu ușurință media ponderată prin înmulțirea punctelor de date corespunzătoare și împărțind rezultatul la suma totală a ponderilor. Formula este următoarea:

=SUMPRODUCT(interval_date, interval_greutate) / SUM(interval_greutate)

Această metodă este deosebit de utilă atunci când aveți de-a face cu note, rezultate ale sondajelor sau date financiare, unde unele valori au mai multă semnificație decât altele.

Stăpânirea acestei tehnici poate îmbunătăți capacitățile de analiză a datelor, permițând utilizatorilor să ia decizii mai informate, bazate pe date precise și relevante.

Sunteți gata să începeți să vă dezvoltați niște abilități puternice de Excel? Consultați