Funcțiile statistice Excel pe care trebuie să le cunoașteți. Cum se calculează media în Excel

În matematică (și statistică), o medie este un număr între cele mai mici și cele mai mari valori dintr-un set de numere. Există trei medii comune: media aritmetică, mediana și modul. În Microsoft Excel, puteți calcula toate cele trei cantități și, de asemenea, puteți găsi media ponderată, care este utilizată pentru a calcula prețul mediu.

Pași

In medie

Introduceți numerele a căror medie aritmetică doriți să găsiți. De exemplu, luați în considerare un set de zece numere.

• În cele mai multe cazuri, numerele sunt introduse în coloane (ca în exemplul nostru), așa că introduceți numerele în celulele A1 – A10.
• Numerele de introdus: 2, 3, 5, 5, 7, 7, 7, 9, 16 și 19.
• Dacă doriți, găsiți suma numerelor introducând formula „=SUM(A1:A10)” în celula A11 (fără ghilimele).

1. Puteți găsi media aritmetică folosind funcția MEDIE.

   • Faceți clic pe o celulă goală, cum ar fi A12, apoi tastați „=AVERAGE(A1:10)” (fără ghilimele).
   • Faceți clic pe o celulă goală și apoi faceți clic pe butonul „f x” (în bara de formule direct deasupra foii de lucru Excel). În fereastra care se deschide, în lista „Selectați o funcție”, găsiți și evidențiați „AVERAGE”, apoi faceți clic pe OK. Introduceți intervalul „A1:A10” în linia „Numărul 1” a ferestrei care se deschide și faceți clic pe OK.
   • Introduceți un semn egal (=) în bara de formule. În stânga barei de formule, în lista derulantă Funcții, găsiți și selectați MEDIE. Introduceți intervalul „A1:A10” în linia „Numărul 1” a ferestrei care se deschide și faceți clic pe OK.

2. În celula în care ați introdus formula, va fi afișată media aritmetică, egală cu raportul dintre suma numerelor din intervalul dat de celule (80) și numărul total de numere din intervalul (10): 80/ 10 = 8.

   • Pentru a verifica răspunsul corect în orice celulă goală, introduceți formula „=A11/10”.
   • Media aritmetică este utilă pentru centrarea unei distribuții atunci când numerele individuale dintr-un set de numere nu sunt foarte diferite unele de altele.

   Median

   1. Introduceți numerele a căror mediană doriți să o găsiți.

      Puteți găsi media aritmetică folosind funcția MEDIAN. Puteți introduce formula într-unul din trei moduri:

      • Faceți clic pe o celulă goală, cum ar fi A13, apoi introduceți „=MEDIAN(A1:10)” (fără ghilimele).
      • Faceți clic pe o celulă goală și apoi faceți clic pe butonul „f x” (în bara de formule direct deasupra foii de lucru Excel). În fereastra care se deschide, în lista „Selectați o funcție”, găsiți și evidențiați „MEDIAN”, apoi apăsați OK. Introduceți intervalul „A1:A10” în linia „Numărul 1” a ferestrei care se deschide și faceți clic pe OK.
      • Introduceți un semn egal (=) în bara de formule. În stânga barei de formule, în lista derulantă Funcții, găsiți și selectați MEDIAN. Introduceți intervalul „A1:A10” în linia „Numărul 1” a ferestrei care se deschide și faceți clic pe OK.

   2. Celula în care ați introdus formula va afișa valoarea mediană, unde jumătate din numerele dintr-un set de numere au valori mai mari decât mediana, iar cealaltă jumătate au valori mai mici decât mediana (în exemplul nostru, mediana este 7). Mediana poate fi sau nu egală cu unul dintre numerele dintr-un set de numere.

   Modă

   Introduceți numerele al căror mod doriți să îl găsiți. De exemplu, luați în considerare un set de zece numere (2, 3, 5, 5, 7, 7, 7, 9, 16 și 19). Introduceți aceste numere în celulele A1 – A10.

   Funcția de căutare a modului este diferită în diferite versiuni de Excel.

   1. Puteți introduce formula într-unul din trei moduri:

      • Faceți clic pe o celulă goală, cum ar fi A14, apoi introduceți „=MODE(A1:10)” sau „=MODE.ONE(A1:10)” (fără ghilimele).
      • Faceți clic pe o celulă goală și apoi faceți clic pe butonul „f x” (în bara de formule direct deasupra foii de lucru Excel). În fereastra care se deschide, în lista „Selectați o funcție”, găsiți și evidențiați „FASHION” sau „FASHION.ONE”, apoi apăsați OK. Introduceți intervalul „A1:A10” în linia „Numărul 1” a ferestrei care se deschide și faceți clic pe OK.
      • Introduceți un semn egal (=) în bara de formule. În stânga barei de formule, în lista derulantă Funcții, găsiți și selectați MEDIAN sau MODE.ONE. Introduceți intervalul „A1:A10” în linia „Numărul 1” a ferestrei care se deschide și faceți clic pe OK.

   2. Celula în care ați introdus formula va afișa valoarea modului egală cu numărul care apare cel mai des într-un anumit set de numere. În exemplul nostru, modul este 7, deoarece numărul 7 apare de trei ori în setul nostru de numere.

      • Dacă două numere apar de același număr de ori într-un interval de numere, funcția MODE sau MODE.ONE va afișa primul număr întâlnit. Dacă în exemplul nostru schimbați 3 în 5, atunci modul va fi 5, nu 7, deoarece 5 apare primul.

   Medie ponderată

   1. Introduceți numerele a căror medie ponderată doriți să o găsiți. Acest lucru necesită două coloane de numere. De exemplu, luați în considerare mai multe transporturi de tonic.

      • În exemplul nostru, vom introduce titluri de coloane. Introduceți „Prețul unitar” în celula A1 și „Numărul de unități expediate” în celula B1.
      • Primul lot a constat din 10 unități la 20 USD pe unitate. Introduceți „20 USD” în celula A2 și „10” în celula B2.
      • Cererea de tonic a crescut, așa că al doilea lot a constat din 40 de unități la 30 USD pe unitate. Introduceți „30 USD” în celula A3 și „40” în celula B3.
      • Pe măsură ce prețul a crescut, cererea de tonic a scăzut, iar al treilea lot a constat din 20 de unități la 25 USD pe unitate. Introduceți „25 USD” în celula A4 și „20” în celula B4.

   2. Pentru a calcula o medie ponderată în Excel, trebuie să utilizați două funcții:

      • SUMPRODUS Funcția SUMPRODUS înmulțește numerele pe un rând și adună produsele numerelor din toate rândurile. Specificați intervalul fiecărei coloane; în exemplul nostru: „SUMPRODUCT=(A2:A4,B2:B4)”. Ca urmare, veți primi costul total al tuturor tonicului expediat.
      • SUMĂ. Funcția SUM adaugă numere într-un singur rând sau coloană. Întrucât doriți să aflați prețul unitar mediu al apei tonice livrate, trebuie să găsiți cantitatea totală de apă tonică expediată. În exemplul nostru: „=SUM(B2:B4)”.
      Deoarece media este determinată de raportul dintre suma tuturor numerelor și numărul de numere, puteți combina aceste două funcții într-o singură formulă: „SUMPRODUCT=(A2:A4,B2:B4)/SUM(B2:B4). ”

   3. Celula în care ați introdus formula va afișa valoarea mediei ponderate.În exemplul nostru, acesta este prețul mediu pe unitate de tonic, egal cu raportul dintre costul total al tonicului expediat și cantitatea totală de tonic.

      • Costul total al tonicului livrat: 20 x 10 + 30 x 40 + 25 x 20 = 200 + 1200 + 500 = 1900 USD.
      • Cantitatea totală de tonic livrat: 10 + 40 + 20 = 70.
      • Preț mediu: 1900/70 = 27,14 USD.
   • Nu trebuie să introduceți toate numerele într-un rând (într-o coloană sau rând), dar trebuie să vă asigurați că Excel înțelege ce numere doriți să includeți și să excludeți. În exemplul nostru, dacă doriți să găsiți media primelor cinci numere, introduceți formula astfel: „=AVERAGE(A1:A5,A10).”

În pregătirea pentru rezolvarea cu succes a Problemei 19 din partea 3, va trebui să cunoașteți câteva funcții Excel. O astfel de funcție este IN MEDIE. Să aruncăm o privire mai atentă.

excela vă permite să găsiți media aritmetică a argumentelor. Sintaxa pentru această funcție este:

MEDIE(număr1, [număr2],...)

Nu uitați că introducerea unei formule într-o celulă începe cu semnul „=”.

Între paranteze putem enumera numerele a căror medie dorim să aflăm. De exemplu, dacă scriem într-o celulă =MEDIE(1, 2, -7, 10, 7, 5, 9), apoi obținem 3,857142857. Acest lucru este ușor de verificat - dacă adunăm toate numerele dintre paranteze (1 + 2 + (-7) + 10 + 7 + 5 + 9 = 27) și împărțim la numărul lor (7), obținem 3,857142857142857.

Vă rugăm să rețineți - numerele în paranteze separate prin punct și virgulă (; ). Astfel putem specifica până la 255 de numere.

Pentru exemple, folosesc Microsort Excel 2010.

În plus, folosind Funcții MEDIE noi putem gasi media unui interval de celule. Să presupunem că avem câteva numere stocate în intervalul A1:A7 și dorim să găsim media lor aritmetică.

Să plasăm media aritmetică a intervalului A1:A7 în celula B1. Pentru a face acest lucru, plasați cursorul în celula B1 și scrieți =MEDIE(A1:A7). Am indicat o serie de celule între paranteze. Rețineți că delimitatorul este caracterul colon (: ). Ați putea să o faceți și mai simplu - scrieți în celula B1 =MEDIE(, apoi utilizați mouse-ul pentru a selecta intervalul dorit.

Ca rezultat, în celula B1 obținem numărul 15.85714286 - aceasta este media aritmetică a intervalului A1:A7.

Ca o încălzire, vă sugerez să găsiți valoarea medie a numerelor de la 1 la 100 (1, 2, 3 etc. până la 100). Prima persoană care răspunde corect în comentarii va primi 50 de ruble la telefon. Lucrăm.

Funcții de categorie Statistic sunt destinate în principal analizării intervalelor de celule în Excel. Folosind aceste funcții, puteți calcula valoarea cea mai mare, cea mai mică sau medie, puteți număra numărul de celule care conțin informații specificate etc.

Această categorie conține peste 100 de funcții Excel diferite, dintre care majoritatea sunt destinate exclusiv calculelor statistice și vor părea o pădure întunecată pentru utilizatorul obișnuit. În această lecție, ne vom uita la cele mai utile și comune funcții ale acestei categorii.

În acest articol, nu vom atinge funcțiile statistice Excel atât de populare precum VERIFICAȘi COUNTIF, le-a fost pregătită o lecție separată.

IN MEDIE()

Funcția statistică IN MEDIE returnează media aritmetică a argumentelor sale.

Această funcție poate lua până la 255 de argumente și poate găsi media în mai multe intervale și celule non-adiacente simultan:

Dacă există celule goale sau celule care conțin text în intervalul calculat, acestea sunt ignorate. În exemplul de mai jos, media este căutată pe patru celule, adică. (4+15+11+22)/4 = 13

Dacă trebuie să calculați media, luând în considerare toate celulele din interval, puteți utiliza funcția statistică IN MEDIE. În exemplul următor, media este căutată pentru peste 6 celule, adică (4+15+11+22)/6 = 8,6(6) .

Funcția statistică IN MEDIE poate folosi ca argumente operatori matematici și diverse funcții Excel:

AVERAGEIF()

Dacă trebuie să returnați media aritmetică a valorilor care îndeplinesc o anumită condiție, puteți utiliza funcția statistică MEDIEIF. Următoarea formulă calculează media numerelor care sunt mai mari decât zero:

În acest exemplu, același interval este utilizat pentru a calcula media și a testa condiția, ceea ce nu este întotdeauna convenabil. În acest caz, funcția MEDIEIF există un al treilea argument opțional din care se poate calcula media. Acestea. Folosind primul argument verificăm condiția, iar folosind al treilea argument găsim media.

Să presupunem că tabelul de mai jos conține statistici privind costul medicamentelor în oraș. Într-o farmacie medicamentul este mai scump, în alta este mai ieftin. Pentru a calcula costul mediu al analginului în oraș, folosim următoarea formulă:

Dacă trebuie să îndepliniți mai multe condiții, puteți utiliza întotdeauna o funcție statistică MEDIEIFS, care vă permite să calculați media aritmetică a celulelor care îndeplinesc două sau mai multe criterii.

MAX()

Funcția statistică MAX returnează cea mai mare valoare dintr-un interval de celule:

MIN()

Funcția statistică MIN returnează cea mai mică valoare dintr-un interval de celule:

MARE()

Returnează a n-a cea mai mare valoare dintr-o matrice de date numerice. De exemplu, în figura de mai jos am găsit a cincea cea mai mare valoare din listă.

Pentru a verifica acest lucru, puteți sorta numerele în ordine crescătoare:

CEL MAI PUŢIN()

Returnează a n-a cea mai mică valoare dintr-o matrice de date numerice. De exemplu, în figura de mai jos am găsit a patra cea mai mică valoare din listă.

Dacă sortați numerele în ordine crescătoare, totul va deveni mult mai clar:

MEDIAN()

Funcția statistică MEDIAN returnează mediana dintr-o matrice dată de date numerice. O mediană este un număr care este mijlocul unui set de numere. Dacă există un număr impar de valori în listă, atunci funcția returnează ceea ce este exact în mijloc. Dacă numărul de valori este par, atunci funcția returnează media celor două numere.

De exemplu, în figura de mai jos, formula returnează mediana pentru o listă de 14 numere.

Dacă sortați valorile în ordine crescătoare, atunci totul devine mult mai clar:

MODĂ()

Returnează valoarea care apare cel mai frecvent într-o matrice de date numerice.

Dacă sortați numerele în ordine crescătoare, atunci totul devine mult mai clar:

Funcția statistică MODĂ este în prezent depășită sau, mai degrabă, forma sa de înregistrare este depășită. Funcția este acum folosită în schimb MODA.ONE. Formularul de înscriere MODĂ suportat și în Excel pentru compatibilitate.

După cum se știe, categoria Statistic Excel conține peste 100 dintre cele mai diverse funcții. Dar, după cum arată practica, cea mai mare parte a acestor funcții nu este practic utilizată, în special de utilizatorii începători. În această lecție, am încercat să vă prezentăm doar cele mai populare funcții statistice ale Excel, pe care mai devreme sau mai târziu le veți putea pune în practică. Sper că această lecție ți-a fost de folos. Mult succes și succes în învățarea Excel.

O invenție foarte convenabilă a lumii computerelor sunt foile de calcul. Puteți introduce date în ele și le puteți aranja frumos sub formă de documente pe gustul dvs. (sau pe gustul superiorilor).

Puteți crea un astfel de document o dată - de fapt, o întreagă familie de documente simultan, care, în terminologia Excel, se numește „registru de lucru” (versiunea în engleză a registrului de lucru).

Cum se comportă Excel

Apoi, trebuie doar să schimbați câteva numere inițiale când datele se schimbă, apoi Excel va efectua mai multe acțiuni simultan, aritmetice și altele. Este in document:

Pentru a face acest lucru, un program de foaie de calcul (și Excel este departe de a fi singurul) are un întreg arsenal de instrumente aritmetice și funcții gata făcute, realizate folosind programe deja depanate și funcționale. Trebuie doar să indicați în orice celulă atunci când scrieți o formulă, printre alți operanzi, numele funcției corespunzătoare și argumentele din paranteze ale acesteia.

Există o mulțime de funcții și sunt grupate pe domenii de aplicare:

Există un întreg set de funcții statistice pentru rezumarea mai multor date. Obținerea valorii medii a unor date este probabil primul lucru care îi vine în minte unui statistician când se uită la numere.

Care este media?

Acesta este momentul în care se ia o anumită serie de numere, din ele se calculează două valori - numărul total de numere și suma lor totală, iar apoi a doua este împărțită la prima. Apoi obțineți un număr a cărui valoare este undeva la mijlocul seriei. Poate chiar va coincide cu unele numere din serie.

Ei bine, să presupunem că acel număr a fost teribil de norocos în acest caz, dar, de obicei, media aritmetică nu numai că nu coincide cu niciunul dintre numerele din seria sa, dar chiar și, după cum se spune, „nu se încadrează în nicio poartă” în această serie. De exemplu, număr mediu de persoane Ar putea fi 5.216 de persoane care locuiesc în apartamente într-un oraș din N-Ska. Ce zici de asta? Traiesc 5 persoane si inca 216 miimi din unul dintre ei? Cei care știu vor rânji doar: despre ce vorbești! Acestea sunt statistici!

Tabelele statistice (sau pur și simplu contabile) pot avea forme și dimensiuni complet diferite. De fapt, forma este un dreptunghi, dar pot fi largi, înguste, repetate (să zicem, date pentru o săptămână de zi), împrăștiate pe diferite foi ale registrului de lucru.

Sau chiar și în alte cărți de lucru (adică în cărți, în engleză), și chiar pe alte computere din rețeaua locală, sau, înfricoșător să spun, în alte părți ale lumii noastre, acum unite de Internetul atotputernic. O mulțime de informații pot fi obținute din surse foarte reputate de pe Internet în formă gata făcută. Apoi procesează, analizează, a trage concluzii, scrie articole, disertații...

De fapt, astăzi trebuie doar să calculăm media pe o serie de date omogene, folosind miraculosul program foaie de calcul. Omogen înseamnă date despre unele obiecte similare și în aceleași unități de măsură. Pentru ca oamenii să nu fie rezumați niciodată cu saci de cartofi și kilobiți cu ruble și copeici.

Exemplu de găsire a valorii medii

Să avem datele inițiale scrise în unele celule. De obicei, aici sunt înregistrate cumva datele generalizate sau datele obținute din datele originale.

Datele inițiale sunt situate în partea stângă a tabelului (de exemplu, o coloană este numărul de piese produse de un angajat A, care corespunde unei linii separate din tabel, iar a doua coloană este prețul unei piese) , ultima coloană indică producția angajatului A în bani.

Anterior, acest lucru se făcea folosind un calculator, dar acum puteți încredința o sarcină atât de simplă unui program care nu face niciodată greșeli.

Tabel simplu al câștigurilor zilnice

Aici in poza cuantumul câștigurilorși se calculează pentru fiecare angajat din coloana E folosind formula de înmulțire a numărului de piese (coloana C) cu prețul pieselor (coloana D).

Apoi nici măcar nu va putea păși în alte locuri din tabel și nu va putea să se uite la formule. Deși, desigur, toată lumea din acel atelier știe cum producția unui lucrător individual se transformă în banii pe care îi câștigă într-o zi.

Valori totale

Apoi, valorile totale sunt de obicei calculate. Acestea sunt cifre sumareîn întregul atelier, în zonă sau în întreaga echipă. De obicei, aceste cifre sunt raportate de unii șefi altora - șefi mai sus.

Așa poți calculați sumele în coloane date sursă și, în același timp, în coloana derivată, adică coloana câștigurilor

Permiteți-mi să observ imediat că, în timp ce tabelul Excel este creat, nu se face protectie in celule. Altfel, cum am desena semnul în sine, cum am introduce designul, l-am colora și am introduce formule inteligente și corecte? Ei bine, când totul este gata, înainte de a da acest registru de lucru (adică un fișier de calcul) unei persoane complet diferite, protecția este făcută. Da, pur și simplu dintr-o acțiune neglijentă, pentru a nu deteriora accidental formula.

Și acum tabelul de autocalculare va începe să funcționeze în atelier împreună cu restul lucrătorilor atelierului. După terminarea zilei de lucru, toate astfel de tabele de date despre activitatea atelierului (și nu numai unul dintre acestea) sunt transferate conducerii superioare, care va rezuma aceste date a doua zi și va trage câteva concluzii.

Iată-l, mediu (adică - în engleză)

Este primul va calcula numărul mediu de piese, produs pe angajat pe zi, precum și câștigul mediu zilnic al lucrătorilor din atelier (și apoi al fabricii). Vom face acest lucru și în ultimul rând, cel mai de jos al tabelului nostru.

După cum puteți vedea, puteți utiliza sumele deja calculate în rândul precedent, pur și simplu împărțiți-le la numărul de angajați - 6 în acest caz.

În formule, împărțirea la constante, numere constante, este o formă proastă. Ce se întâmplă dacă ni se întâmplă ceva ieșit din comun, iar numărul de angajați devine mai mic? Apoi va trebui să parcurgeți toate formulele și să schimbați numărul șapte cu altul peste tot. Puteți, de exemplu, „înșela” semnul astfel:

În loc de un anumit număr, puneți un link în formulă către celula A7, unde este numărul de serie al ultimului angajat din listă. Adică, acesta va fi numărul de angajați, ceea ce înseamnă că împărțim corect suma pentru coloana care ne interesează la număr și obținem valoarea medie. După cum puteți vedea, numărul mediu de piese s-a dovedit a fi de 73 și plus o uluitoare din punct de vedere al numerelor (deși nu este semnificativă) makeweight, care este de obicei eliminată prin rotunjire.

Rotunjirea la copecul cel mai apropiat

Rotunjirea este o acțiune comună când în formule, în special în cele contabile, un număr este împărțit la altul. Mai mult, acesta este un subiect separat în contabilitate. Contabilii sunt implicați în rotunjire de mult timp și cu scrupulozitate: rotunjesc imediat fiecare număr obținut prin împărțire la copecul cel mai apropiat.

Excel este un program matematic. Ea nu este uluită de o parte dintr-un ban - unde să o pun. Excel pur și simplu stochează numerele așa cum sunt, cu toate zecimale incluse. Și din nou și din nou va efectua calcule cu astfel de numere. Iar rezultatul final poate fi rotunjit (dacă dăm comanda).

Numai contabilitatea va spune că aceasta este o greșeală. Pentru că rotunjesc fiecare număr „strâmb” rezultat la ruble întregi și copeici. Iar rezultatul final se dovedește de obicei puțin diferit de cel al unui program indiferent la bani.

Dar acum vă voi spune principalul secret. Excel poate găsi valoarea medie fără noi; are o funcție încorporată pentru aceasta. Ea trebuie doar să specifice intervalul de date. Și apoi ea însăși le va însuma, le va număra și apoi ea însăși va împărți suma la cantitate. Și rezultatul va fi exact același cu ceea ce am înțeles pas cu pas.

Pentru a găsi această funcție, mergem la celula E9, unde ar trebui plasat rezultatul acesteia - valoarea medie din coloana E și facem clic pe pictograma fx, care se află în stânga barei de formule.

1. Se va deschide un panou numit „Asistent de funcții”. Acesta este un dialog în mai mulți pași (Wizard, în engleză), cu ajutorul căruia programul ajută la construirea de formule complexe. Și, rețineți că ajutorul a început deja: în bara de formule, programul a introdus semnul = pentru noi.
2. Acum putem fi liniștiți, programul ne va ghida prin toate dificultățile (fie în rusă, fie în engleză) și ca urmare se va construi formula corectă pentru calcul.

În fereastra de sus („Căutare funcție:”) este scris că putem căuta și găsi aici. Adică, aici puteți scrie „medie” și faceți clic pe butonul „Găsiți” (Găsiți, în engleză). Dar o poți face altfel. Știm că această funcție este din categoria statistică. Așa că vom găsi această categorie în a doua fereastră. Și în lista care se deschide mai jos, vom găsi funcția „AVERAGE”.

În același timp, vom vedea cât de grozav este acolo multe funcții la categoria statistică sunt doar 7 medii. Iar pentru fiecare dintre funcții, dacă mutați indicatorul peste ele, mai jos puteți vedea un scurt rezumat al acestei funcții. Și dacă faceți clic și mai jos, pe inscripția „Ajutor pentru această funcție”, puteți obține o descriere foarte detaliată a acesteia.

Acum vom calcula doar media. Faceți clic pe „OK” (așa este exprimat acordul în engleză, deși este mai probabil în americană) pe butonul de mai jos.

Programul a intrat la începutul formulei, acum trebuie să setăm intervalul pentru primul argument. Doar selectați-l cu mouse-ul. Faceți clic pe OK și obțineți rezultatul. Stânga adăugați rotunjire aici, pe care am realizat-o în celula C9, iar placa este gata de utilizare zilnică.