Microsoft Excel (denumit adesea Excel) este un software puternic pentru foi de calcul conceput pentru a stoca seturi mari de date. De asemenea, permite utilizatorilor să organizeze și să analizeze datele și chiar să efectueze calcule de la bază până la complexe pe datele înregistrate. Sarcinile esențiale de analiză a datelor din Excel includ în principal calcularea sau găsirea mediei (sau a mediei aritmetice), a modului și a mediei.
În acest tutorial, discutăm diferite metode sau soluții pas cu pas cu privire la modul de calcul al mediei în Excel. Înainte de a discuta metodele, să înțelegem mai întâi pe scurt conceptul de medie și beneficiile sale.
Care este media în Excel?
În Excel, media aritmetică nu este altceva decât un număr mediu care este de obicei calculat prin adăugarea tuturor numerelor unui set de date dat și apoi împărțirea sumei la numărul total de valori (sau numărarea) elementelor date.
Deși este ușor de calculat media aritmetică pentru seturi de date mici în viața reală, devine dificil să lucrezi cu seturi mari de date. Acolo este util un program puternic de foi de calcul precum Excel. Poate calcula cu ușurință Media de la sute la mii de numere. De asemenea, este mai puțin predispus la erori.
Există multe utilizări esențiale ale mediei în analiza datelor, cum ar fi:
- Pentru a compara datele istorice
- Pentru a evidenția KPI-uri (Key Performance Indicators) și cotații
- Pentru a găsi punctul de mijloc pentru punctele date date
- Pentru a determina managementul de proiect și planurile de afaceri
Scenariul de bază în care este posibil să fie nevoie să calculăm media în Excel este atunci când trebuie să aflăm vânzările medii ale unui articol într-o lună, trimestru, an, etc.
Cum se calculează media în Excel?
Din păcate, nu există o funcție MEAN definită pentru a calcula sau determina media aritmetică pentru date. Cu toate acestea, există câteva moduri eficiente. În general, putem aplica de obicei conceptul matematic de adăugare de valori și apoi împărțirea sumei acestora la numărul de valori din celulele Excel. În plus, există funcții existente precum AVERAGE, AVERAGEA, AVERAGEIF și AVERAGEIFS, care ne ajută, de asemenea, să calculăm media aritmetică în diferite cazuri de utilizare.
Pentru a vă face o idee mai bună despre modul în care funcționează aceste concepte pentru calcularea mediei aritmetice, să le discutăm pe fiecare individual cu un exemplu:
Calcularea mediei în Excel: Metoda generală
Putem aplica funcțiile SUM și COUNT într-o foaie Excel pentru a folosi abordarea de a adăuga valori și de a le împărți la numărul de număr de valori incluse.
De exemplu, să presupunem că am dat numerele de la 1 la 5 și trebuie să calculăm media aritmetică. Matematic, mai întâi adunăm toate aceste numere, adică (1+2+3+4+5). Apoi, împărțim SUMA lor la 5, deoarece este numărul total al numerelor date.
Astfel, întreaga formulă pentru calcularea mediei devine următoarea:
(1+2+3+4+5)/ 5
Aceasta returnează media aritmetică 3.
În Excel, putem folosi formula matematică de mai sus combinând-o cu funcțiile SUM și COUNT în următorul mod:
=SUMA(1,2,3,4,5)/NUMĂRĂ(1,2,3,4,5)
Aici, funcția SUM adună valorile date, în timp ce funcția COUNT determină numărul de valori date.
Acum, dacă înregistrăm aceste cinci numere în intervalul A1:A5, formula de mai jos din celula A6 va calcula media aritmetică:
=SUMA(A1:A5)/NUMĂRĂ(A1:A5)
Rezultatul (sau media) obținut în imaginea de mai sus este același cu cel pe care l-am calculat din formula matematică, adică, 3.
Calcularea mediei în Excel: Funcția MEDIE
Deoarece Media este o medie, funcția AVERAGE din Excel este cea mai eficientă modalitate de a calcula media aritmetică. Este una dintre funcțiile statistice utilizate cel mai frecvent pentru a calcula media pentru un anumit interval dacă nu există nicio condiție sau limitare specială. Este funcția Excel încorporată.
Sintaxă
Sintaxa funcției AVERAGE din Excel este următoarea:
=MEDIE(număr1, [număr2], ...)
Numărul 1 este argumentul necesar, ceea ce înseamnă că un argument este obligatoriu în funcția AVERAGE. Cu toate acestea, putem furniza mai multe argumente în forme diferite, cum ar fi numere, celule, intervale, matrice, constante etc. Când calculăm media aritmetică, ar trebui să furnizăm cel puțin două argumente în funcția AVERAGE.
Pași pentru a calcula media folosind funcția MEDIE
Să înțelegem acum funcționarea funcției MEDIE și să calculăm media aritmetică pentru un grup de numere dintr-o foaie Excel folosind datele eșantionului.
Să presupunem că listăm câteva fructe într-o foaie Excel cu prețurile corespunzătoare. Denumirile fructelor sunt înregistrate în coloana A (A2:A9), iar prețurile acestora sunt în următoarea coloana B (B2:B9). Trebuie să găsim sau să calculăm prețul mediu (sau media aritmetică a prețurilor) în celula B10.
Trebuie să parcurgem pașii de mai jos pentru a calcula media aritmetică din foaia noastră de lucru exemplu:
- În primul rând, trebuie să selectăm sau să evidențiem celula pentru a înregistra media aritmetică rezultată, adică celula B10.
- În continuare, trebuie să inserăm numele funcției începând cu semnul egal și să furnizăm argumentele necesare în funcție. În cazul nostru, introducem semnul egal (=) și funcția MEDIE. Putem alege funcția și din lista de funcții sugerate de Excel.
- După introducerea numelui funcției, trebuie să introducem paranteza de început și să furnizăm gama noastră efectivă de seturi de date. Deoarece avem date în celule adiacente sau un interval (de la B2 la B9), putem trece întregul interval în funcție în felul următor:
- În cele din urmă, trebuie să introducem paranteza de închidere și să apăsăm tasta introduce tasta de pe tastatură pentru a obține rezultatul efectiv sau media aritmetică pentru setul de date dat. Imaginea de mai jos arată media aritmetică a prețurilor din celulele de la B2 la B9:
În imaginea de mai sus, am folosit celulele adiacente și am furnizat întreaga gamă de seturi de date dintr-o singură mișcare. Cu toate acestea, putem calcula și media pentru celulele neadiacente din foaia noastră Excel, furnizând celulele individual.
Să presupunem că trebuie doar să calculăm media aritmetică a prețurilor pentru doar trei fructe: Măr, Mango și Struguri. În acest caz, putem trece fiecare celulă separat în funcție în următorul mod:
=MEDIE(B2;B3;B6)
Aici, B2, B3 și B6 sunt celulele individuale cu prețurile de măr, mango și struguri.
Deși funcția AVERAGE poate fi utilizată cu celule goale, texte, zerouri, valori logice și chiar argumente mixte, ea ignoră celulele goale, textul și valorile logice în timpul calculului. Cu toate acestea, celulele cu valoare zero sunt incluse în funcția MEDIE.
Calcularea mediei în Excel: Funcția AVERAGEA
Funcția AVERAGEA este o versiune ușor avansată a funcției AVERAGE. Funcția AVERAGEA ne ajută, de asemenea, să calculăm media aritmetică pentru seturi de date date și funcționează în toate versiunile de Excel. Un avantaj al funcției AVERAGEA este că poate include valori de text și tipuri de date booleene în timp ce se calculează media în Excel. Valorile textului sunt evaluate ca zerouri, în timp ce valorile booleene TRUE și FALSE sunt evaluate ca 1 și, respectiv, 0.
Sintaxă
Sintaxa funcției AVERAGEA din Excel este următoarea:
=AVERAGEA(valoare1, [valoare2], ...)
Ca și funcția AVERAGE, primul argument din funcția AVERAGEA este necesar. Cu toate acestea, putem folosi mai multe argumente după cum este necesar.
Sintaxa funcțiilor AVERAGE și AVERAGEA este aproape similară; cu toate acestea, conceptul lor de lucru este ușor diferit. Funcția AVERAGE poate calcula numai media aritmetică pentru valori numerice, în timp ce funcția AVERAGEA poate calcula aceeași pentru orice tip de date, inclusiv text și booleeni.
Pași pentru calcularea mediei folosind funcția AVERAGEA
Să reluăm exemplele anterioare de seturi de date în care avem numele fructelor în coloana A (A2:A9) și prețurile acestora în coloana B (B2:B9). Cu toate acestea, de data aceasta nu avem prețul pentru struguri (înregistrat ca „Nedisponibil” în celula corespunzătoare B6), după cum se arată mai jos:
Putem executa pașii de mai jos și calcula valoarea medie folosind funcția AVERAGEA:
- Ca și metoda anterioară, mai întâi selectăm celula rezultată B10 pentru a înregistra media. Apoi, introducem semnul egal (=), introducem sau selectăm funcția AVERAGEA și transmitem întregul interval de date funcției incluse în paranteză.
- În cele din urmă, trebuie să apăsăm pe introduce cheie pentru a obține rezultate (sau medii) eficiente.
Imaginea de mai sus returnează rezultatul mediu de 57.125 cu funcția AVERAGEA.
Dacă folosim funcția MEDIE în acest exemplu și calculăm media aritmetică, vedem o diferență uriașă între rezultatele obținute de ambele funcții. Ieșirea medie de 65.285 apare cu funcția MEDIE, după cum se arată mai jos:
Diferențele de medie aritmetică din acest exemplu au sens deoarece numărul de diviziune este diferit pentru ambele funcții. Numărul pentru funcția MEDIE este 8, deoarece evaluează și TEXTul ca date valide. Dar, pentru funcția MEDIE, numărul de diviziuni este 7, deoarece nu include datele TEXT în calculul său.
În locul datelor TEXT, dacă setul de date conține zero (0), același rezultat (sau medii aritmetice) va apărea pentru ambele funcții (MEDIA și MEDIEA), deoarece ambele includ zero ca date valide în calcul.
Calcularea mediei în Excel: Funcția AVERAGEIF
Funcția AVERAGEIF este funcția relevantă a funcției AVERAGE și ne ajută să calculăm media aritmetică cu o condiție sau un criteriu dat. Cu funcția AVERAGEIF, putem calcula numai Media pentru anumite numere care urmează criteriile specificate. Excel evaluează mai întâi criteriile specifice, localizează celulele efective urmând acele criterii și apoi calculează în consecință Media numai pentru celulele localizate.
Sintaxă
Sintaxa funcției AVERAGEIF din Excel este următoarea:
scaner java
=AVERAGEAIF(interval, criterii, [interval_medie])
Aici, intervalul și criteriile sunt argumentele necesare, în timp ce intervalul_medie este opțional.
- Un interval conține celule care trebuie testate în funcție de criteriile date.
- Un criteriu se referă la argumentul în care putem specifica condiția dorită de urmat. Poate include text, valori numerice, referință la celule, interval și expresie logică.
- Intervalul mediu este argumentul în care menționăm celule (sau un interval) pentru care dorim să calculăm Media. Dacă acest argument nu este specificat, Excel utilizează automat intervalul.
Pași pentru a calcula media folosind funcția AVERAGEIF
În același exemplu, să presupunem că listăm câteva fructe de mai multe ori cu prețuri diferite. De data aceasta, trebuie doar să calculăm media pentru orice anumit fruct repetat (adică, măr) din celula B11. Deci, folosim funcția AVERAGEIF și aplicăm condiția în timp ce calculăm Media. Trebuie să executăm pașii de mai jos pentru a calcula prețul mediu pentru Apple:
- În primul rând, selectăm celula rezultată (B11) și inserăm formula AVERAGEIF în felul următor:
=AVERAGEIF(A2:B9; „Mere”, B2:B9)
Aici, A2:B9 reprezintă intervalul efectiv, Apple reprezintă criteriul/condiția dorită, iar B2:B9 reprezintă intervalul_medie.
Aici, am furnizat manual opțional average_range pentru a include numai celulele cu prețuri. În exemplul nostru, dacă nu trecem media_range, funcția va folosi intervalul primar (A2:B9) cu datele text și va provoca #DIV! eroare.
Notă: Este esențial să rețineți că funcția AVERAGEIF ignoră celulele goale din argumentul său average_range. În plus, dacă orice celulă specificată în criterii este goală, funcția o tratează ca o celulă cu valoare zero.
Calcularea mediei în Excel: Funcția AVERAGEIFS
Funcția AVERAGEIFS este o versiune extinsă sau avansată a funcției AVERAGEIF. Funcția AVERAGEIF ne permite să aplicăm doar o condiție la un moment dat. Cu toate acestea, cu funcția AVERAGEIFS, putem aplica mai multe criterii pentru a fi testate simultan. În plus, funcția AVERAGEIFS ne permite să folosim celule cu valori booleene TRUE și FALSE, ajutându-ne să testăm cu ușurință mai multe criterii. Acest lucru nu este posibil și cu funcția AVERAGEIF.
Sintaxă
Sintaxa funcției AVERAGEIFS din Excel este următoarea:
=AVERAGEAIFS(interval_medie, interval_criterii1, criteriu1, [interval_criterii2, criteriu2], ...)
Aici, average_range, criteria1_range1 și criteria1 sunt argumentele necesare. Cu toate acestea, putem furniza argumentele opționale bazate pe seturile noastre de date eficiente. În plus, criteriul 1 se referă la o condiție care urmează să fie testată pe intervalul 1, criteriul 2 se referă la o condiție care urmează să fie testată pe intervalul 2 și multe altele.
Pași pentru calcularea mediei folosind funcția AVERAGEIFS
Să luăm în considerare aceleași date de exemplu pe care le-am folosit în metoda anterioară. În metoda anterioară, am folosit funcția AVERAGEIF și am calculat Media pentru prețurile fructelor repetate (Mere). În acest caz, aveam o singură condiție.
Acum, să presupunem că trebuie să calculăm media prețurilor Apple, dar includem numai acele celule în care prețurile sunt peste 40.
Avem două criterii diferite în acest caz, deci funcția AVERAGEIF nu poate fi utilizată.
Criteriul 1: Într-un interval A2:A9, criteriul inițial este de a verifica dacă „Apple” este satisfăcut de trei celule (sau rânduri)
Criteriul 2: Într-un interval B2:B9, celălalt criteriu este de a verifica prețurile peste 40 (>40) care sunt satisfăcute de cele două din trei celule (sau rând) cu un măr de fructe
Astfel, dacă calculăm matematic media, folosim formula de mai jos:
(95+68)/2
Aceasta returnează valoarea medie a 81,5. Să verificăm dacă obținem aceleași rezultate cu funcția AVERAGEIFS din Excel.
Când trebuie să testăm condiții în Excel, de obicei luăm ajutorul operatorului logic SAU. Operatorul OR returnează TRUE și FALSE în funcție de îndeplinirea condiției date. De exemplu, să presupunem că trebuie să verificăm dacă celula A2 conține Apple. În acest caz, putem folosi operatorul OR astfel:
=SAU(A2='Mere')
Inserăm condiția de mai sus în foaia noastră de exemplu pentru a testa sau a localiza celulele cu mărul de fructe. Deci, creăm o nouă coloană (C) și introducem condiția specifică în celula C2. După aceea, copiem aceeași condiție în celulele rămase din coloana C. Arată astfel:
Imaginea de mai sus arată că operatorul OR returnează TRUE pentru celulele care corespund fructelor Măr, în timp ce pentru altele, returnează FALSE. Pe baza acestui rezultat, aplicăm acum funcția AVERAGEIFS și inserăm mai multe condiții pentru celulele cu valoare ADEVĂRATĂ în felul următor:
- Ca și alte metode, selectăm din nou mai întâi celula rezultată (adică B12).
- În continuare, introducem formula AVERAGEIFS în felul următor:
=AVERAGEIFS(B2:B9;C2:C9;'ADEVĂRAT';B2:B9;'>40')
Cu formula AVERAGEIFS de mai sus, am inclus doar fructele „Mere” și prețurile peste 40. - În cele din urmă, trebuie să apăsăm pe introduce cheie pentru a obține rezultatul formulei. Pentru setul nostru de date, formula a returnat media aritmetică a 81,5, la fel ca rezultatul nostru calculat matematic.
Cazuri speciale
Deși este posibil să avem diverse scenarii în timpul calculării mediei aritmetice în Excel, cele două cele mai comune sunt discutate aici. Trebuie să fim precauți atunci când calculăm media aritmetică în aceste cazuri.
Calcularea mediei inclusiv zerourile
Când trebuie să calculăm Media cu zerourile, putem folosi în mod normal funcția MEDIE și să furnizăm celulele efective, inclusiv celulele cu valoarea zero. Funcția MEDIE include zerourile ca numere valide în calculul său.
În următorul set de date exemplu, aplicăm funcția MEDIE pentru a calcula media pentru notele a doi elevi diferiți:
Imaginea de mai sus arată diferitele valori medii pentru ambii studenți. Dacă ne uităm la note, suma notelor este aceeași pentru ambii elevi. Al doilea elev are zero note la o materie (celula cu valoare zero), în timp ce primul elev este absent (celula cu valoarea textului) la o materie.
Deoarece funcția MEDIE ignoră valorile text, dar include valori zero, numărul de diviziuni variază pentru ambii studenți. Pentru primul elev, numărul de diviziuni este 4, în timp ce pentru un alt elev, este 5. Astfel, există o diferență în rezultatele medii. Cu toate acestea, putem exclude în mod explicit valorile zero dacă este necesar.
Calcularea mediei excluzând zerourile
Când trebuie să calculăm Media excluzând zerourile, trebuie să profităm de funcția AVERAGEIF din Excel. Putem aplica condiția specifică în această funcție, indicând Excel să nu includă celulele cu valoarea zero în timpul calculului.
Formula de mai jos AVERAGEIF poate fi utilizată pentru a exclude valorile zero în timpul calculării mediei în Excel:
=AVERAGEIF(celula sau un interval,'>0')
Dacă inserăm formula de mai sus pentru datele noastre eșantion, aceleași rezultate ale valorii medii apar pentru ambii studenți.
Formula de mai sus AVERAGEIF ignoră textul și celulele cu valoare zero. Deci, numărul de diviziuni este același (adică, 4), la fel și media aritmetică pentru notele ambilor studenți.
Puncte importante de reținut
- Este important de reținut că orice funcție din Excel, inclusiv AVERAGE, AVERAGEA, AVERAGEIF și AVERAGEIFS, poate accepta maximum 255 de argumente.
- Nu se recomandă introducerea manuală a numelui funcției într-o celulă Excel. Selectarea funcției dorite din lista de sugestii este mai bine pentru a evita numele de funcții scrise greșit și pentru a elimina erorile din rezultate.