Excelove statističke funkcije za izračun prosjeka: Udžbenik s primjerima, drugi dio

Excel nudi širok raspon statističkih funkcija koje izvode izračune od osnovne srednje vrijednosti, medijana i načina do složenijih statističkih distribucija i testova vjerojatnosti.

U ovom ćemo članku proniknuti u statističke funkcije programa Excel za izračun prosjeka.

Imajte na umu da su neke statističke funkcije uvedene u novijim verzijama Excela i stoga nisu dostupne u starijim verzijama.

Funkcije za izračunavanje prosjeka

AVERAGE

Funkcija AVERAGE je jedna od Excelovih statističkih funkcija. Funkcija vraća prosjek numeričkih vrijednosti unesenih u funkciju. Jednostavnim riječima, zbraja sve vrijednosti navedene u funkciji, zatim ih dijeli s brojem i vraća rezultat.

sintaksa

= AVERAGE(number1,number2,…)

subjekti

• numero1 : Prvi broj koji želite koristiti za izračunavanje prosjeka.
• [numero2] : Drugi broj koji želite koristiti za izračunavanje prosjeka.

primjer

Da biste vidjeli kako funkcija radi AVERAGE da vidimo primjer:

U prvom smo primjeru umetnuli argumente izravno u funkciju.

U drugom primjeru referencirali smo raspon koji sadrži brojeve. Možete se pozvati na neograničenu ćeliju pomoću kontinuiranog raspona, a ako se želite pozvati na dinamički raspon, za to možete koristiti tablicu.

Možete se pozvati na neograničenu ćeliju pomoću kontinuiranog raspona, a ako se želite pozvati na dinamički raspon, možete koristiti tablicu.

U trećem primjeru referirali smo se na raspon u kojem su ćelije oblikovane kao tekstualne vrijednosti. U ovom slučaju možete pretvoriti te tekstualne brojeve u stvarne brojeve kako biste izračunali prosjek.

U četvrtom primjeru imamo apostrof ispred svake vrijednosti u svakoj ćeliji i stoga ga funkcija zanemaruje.

AVERAGEA

Funkcija AVERAGEA programa Excel naveden je u kategoriji Statističke funkcije programa Microsoft Excel. Vraća prosjek navedenih brojeva u funkciji, ali za razliku AVERAGE, tretira Booleove vrijednosti i brojeve formatirane kao tekst.

sintaksa

=AVERAGEA(valore1,valore2,…)

subjekti

• value1 : Vrijednost koja je broj, logička vrijednost ili broj pohranjen kao tekst.
• [valore2] : Vrijednost koja je broj, logička vrijednost ili broj pohranjen kao tekst.

primjer

Za razumijevanje funkcije AVERAGEA moramo vidjeti primjer:

Vrijednost koju vraća funkcija je 10,17 što je "(0+0+1+10+20+30)/6".

AVERAGEIF

Funkcija AVERAGEIF programa Excel naveden je u kategoriji Statističke funkcije programa Microsoft Excel. Vraća prosjek brojeva koji zadovoljavaju više navedenih uvjeta . 

sintaksa

= AVERAGEIF( range, criteria, [average_range] )

Teme

• range:  niz vrijednosti (ili raspon ćelija koje sadrže vrijednosti) za testiranje prema navedenim kriterijima.
• criteria:  Uvjet koji se testira u odnosu na svaku od vrijednosti u navedenom rasponu.
• [average_range]:  Izborni niz numeričkih vrijednosti (ili ćelija koje sadrže brojeve) koji bi trebao biti prosječan ako odgovarajuća vrijednost u rasponu zadovoljava navedene kriterije.

Ako je tema [average_range] izostavljen, prosjek se izračunava za vrijednosti u početnom danom rasponu.

Ponuđeni kriteriji mogu biti:

numerička vrijednost (uključujući cijele brojeve, decimale, datume, vremena i logičke vrijednosti) (na primjer, 10, 01/01/2008, TRUE)
O
tekstualni niz (npr. "Tekst", "Četvrtak") - MORA biti naveden pod navodnicima
O
izraz (npr. “>12”, “<>0”) – MORA biti naveden u navodnicima.
Također imajte na umu da funkcija AVERAGEIF Excel ne razlikuje velika i mala slova. Tako, na primjer, tekstualni nizovi "TEXT"A"text” bit će vrednovani kao jednaki.

primjer

Za razumijevanje funkcije AVERAGEIF moramo to isprobati na primjeru.

Stanice A16-A20 sljedeće proračunske tablice prikazuju pet primjera funkcije AVERAGEIF programa Excel.

Za svaki poziv funkcije AVERAGEIF Excela, tema range (za testiranje criteria) je raspon ćelija A1-A14 i temu [average_range] (koji sadrži vrijednosti za koje treba izračunati prosjek) je raspon ćelija B1-B14.

Imajte na umu da su u ćelijama A16, A18 i A20 gornje proračunske tablice tekstualna vrijednost “Četvrtak” i izrazi “>2” i “<>TRUE” nalaze se pod navodnicima. Ovo je bitno za sve tekstove ili izraze.

AVERAGEIFS

Funkcija AVERAGEIFS programa Excel naveden je u kategoriji Statističke funkcije programa Microsoft Excel. Vraća prosjek brojeva koji zadovoljavaju više navedenih uvjeta . Za razliku od AVERAGEIF, možete postaviti više uvjeta i izračunati prosjek samo za brojeve koji ispunjavaju sve uvjete.

sintaksa

= AVERAGEIFS( average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

Teme

• average_range:  Niz numeričkih vrijednosti (ili ćelija koje sadrže brojeve) koje treba izračunati prosjek.
• criteria_range1, [criteria_range2], …: nizovi vrijednosti (ili rasponi ćelija koje sadrže vrijednosti) za međusobno ispitivanje criteria1, criteria2, … (Nizovi criteria_range svi isporučeni moraju imati istu duljinu).
• criteria1, [criteria2], …: Uvjeti koje treba ispitati s obzirom na vrijednosti u criteria_range1, [criteria_range2], …

primjer

Sada pogledajmo primjer funkcije AVERAGEIFS:

U sljedećem primjeru koristili smo funkciju AVERAGEIFS za izračun prosječne količine koju je prodao prodavač “Pietro” i za proizvod “B”. Kriterije smo unijeli izravno u funkciju i imamo dva unosa Peterove prodaje proizvoda B.

U sljedećem primjeru koristili smo AVERAGEIFS sa zvjezdicom za izračun prosječne cijene voća čija je količina veća od 20 jedinica i ima B u nazivu.

U podacima ispod imamo dva voća koja zadovoljavaju ove kriterije.

MEDIAN

Funkcija MEDIAN Excel vraća statistički medijan (prosječnu vrijednost) popisa dostavljenih brojeva.

sintaksa

= MEDIAN( number1, [number2], ... )

Teme

numerički argumenti su skup jedne ili više numeričkih vrijednosti (ili nizova numeričkih vrijednosti), za koje želite izračunati medijan

Napomena:

• Ako postoji paran broj vrijednosti u danom skupu podataka, vraća se prosjek dviju prosječnih vrijednosti;
• Ako isporučeni niz sadrži prazne ćelije, tekst ili logičke vrijednosti, te se vrijednosti zanemaruju pri izračunavanju medijana.
• U trenutnim verzijama programa Excel (Excel 2007 i noviji) možete dati do 255 numeričkih argumenata za funkciju medijana, ali u programu Excel 2003 funkcija može prihvatiti samo do 30 numeričkih argumenata. Međutim, svaki od numeričkih argumenata može biti niz mnogih vrijednosti.

primjer

Sljedeća proračunska tablica prikazuje tri primjera funkcije Median:

Uzmite u obzir da u prethodnim primjerima:

• Primjer u ćeliji B2 prima paran broj vrijednosti i stoga se medijan izračunava kao prosjek dviju srednjih vrijednosti, 8 i 9;
• Primjer u ćeliji B3 uključuje praznu ćeliju A8. Ova se ćelija zanemaruje pri izračunavanju medijana.

Za više detalja o funkciji MEDIAN programa Excel, pogledajte Microsoft Office web mjesto .

MODE

Funkcija MODE programa Excel vraća MODE statistika (najčešća vrijednost) popisa navedenih brojeva. Ako postoje 2 ili više ponavljajućih vrijednosti u dostavljenim podacima, funkcija vraća najnižu vrijednost među njima

sintaksa

= MODE( number1, [number2], ... )

Teme

su skup jedne ili više numeričkih vrijednosti (ili nizova numeričkih vrijednosti), za koje želite izračunati MODE statistika.

Napomena:

• U trenutnim verzijama programa Excel (Excel 2007 i noviji) možete dati do 255 numeričkih argumenata funkciji MODE, ali u Excelu 2003 funkcija može prihvatiti samo do 30 numeričkih argumenata.
• Funkcija zanemaruje tekst i logičke vrijednosti unutar danog niza brojeva Mode.

Primjeri funkcija MODE

Primjer 1

Sljedeća proračunska tablica prikazuje funkciju MODE Excel, koji se koristi za izračunavanje MODE statistika skupa vrijednosti u ćelijama A1-A6.

Primjer 2

Sljedeća proračunska tablica prikazuje funkciju MODE, koristi se za izračun MODE statistika skupa vrijednosti u ćelijama A1-A10.

Imajte na umu da u ovom slučaju postoje dva mode u podacima.

U gornjem slučaju, gdje podaci u stupcu A prethodne proračunske tablice imaju dva MODE statistika (3 i 4), funkcija MODE vraća nižu od ove dvije vrijednosti.

Za daljnje pojedinosti i primjere funkcije MODE programa Excel, pogledajte Microsoft Office web mjesto .

MODE.SNGL

Funkcija MODE.SNGL programa Excel vraća MODE statistika (najčešća vrijednost) popisa navedenih brojeva. Ako postoje 2 ili više ponavljajućih vrijednosti u dostavljenim podacima, funkcija vraća najnižu vrijednost među njima.

Funkcija Mode.Sngl je nov u programu Excel 2010 i stoga nije dostupan u ranijim verzijama programa Excel. Međutim, funkcija je jednostavno preimenovana verzija funkcije MODE dostupno u ranijim verzijama Excela.

sintaksa

= MODE.SNGL( number1, [number2], ... )

Teme

su skup jedne ili više numeričkih vrijednosti (ili nizova numeričkih vrijednosti), za koje želite izračunati MODE.SNGL statistika.

Primjeri funkcija MODE.SNGL

Primjer 1

Sljedeća proračunska tablica prikazuje funkciju MODE.SNGL Excel, koji se koristi za izračunavanje statističkog NAČINA skupa vrijednosti u ćelijama A1-A6.

Primjer 2

Sljedeća proračunska tablica prikazuje funkciju MODE.SNGL, koristi se za izračunavanje statističkog načina skupa vrijednosti u ćelijama A1-A10.

Imajte na umu da u ovom slučaju postoje dva mode u podacima.

U gornjem slučaju, gdje podaci u stupcu A prethodne proračunske tablice imaju dva MODE statistika (3 i 4), funkcija MODE.SNGL vraća nižu od ove dvije vrijednosti.

Za daljnje pojedinosti i primjere funkcije MODE.SNGL programa Excel, pogledajte Microsoft Office web mjesto .

GEOMEAN

Geometrijska sredina je mjera prosjeka koja označava tipičnu vrijednost skupa brojeva. Ovo mjerenje može se koristiti samo za pozitivne vrijednosti.

Geometrijska sredina skupa vrijednosti, y ₁ , I ₂ , …, tamo _n izračunava se formulom:

Imajte na umu da je geometrijska sredina uvijek manja ili jednaka aritmetičkoj sredini.

Funkcija Geomean Excel izračunava geometrijsku sredinu zadanog skupa vrijednosti.

sintaksa

= GEOMEAN( number1, [number2], ... )

Teme

jedna ili više pozitivnih numeričkih vrijednosti (ili nizova numeričkih vrijednosti), za koje želite izračunati geometrijsku sredinu.

U trenutnim verzijama Excela (Excel 2007 i noviji), funkcija može prihvatiti do 255 numeričkih argumenata, ali u Excelu 2003 funkcija može prihvatiti samo do 30 numeričkih argumenata. Međutim, svaki argument može biti niz vrijednosti ili raspon ćelija, od kojih svaka može sadržavati mnogo vrijednosti.

primjer

Stanica B1 proračunske tablice prikazuje jednostavan primjer funkcije geomean u Excelu, koristi se za izračunavanje geometrijske sredine vrijednosti u ćelijama A1-A5.

U ovom primjeru funkcija Geomean vraća vrijednost 1.622671112 .

HARMEAN

Harmonijska sredina je mjera srednje vrijednosti izračunata kao recipročna vrijednost aritmetičke sredine recipročnih vrijednosti. Ovo se može izračunati samo za pozitivne vrijednosti.

Harmonijska sredina skupa vrijednosti, y1, y2, ..., yn je stoga dana formulom:

harmonijska sredina uvijek je manja ili jednaka geometrijskoj sredini, a geometrijska sredina je uvijek manja ili jednaka aritmetičkoj sredini.

Funkcija Harmean Excel izračunava harmonijsku sredinu zadanog skupa vrijednosti.

sintaksa

= HARMEAN( number1, [number2], ... )

Teme

jedna ili više pozitivnih numeričkih vrijednosti (ili nizova numeričkih vrijednosti), za koje želite izračunati harmonijsku sredinu.

U trenutnim verzijama Excela (Excel 2007 i noviji), funkcija može prihvatiti do 255 numeričkih argumenata, ali u Excelu 2003 funkcija može prihvatiti samo do 30 numeričkih argumenata. Međutim, svaki argument može biti niz vrijednosti ili raspon ćelija, od kojih svaka može sadržavati mnogo vrijednosti.

primjer

Ćelija B1 u proračunskoj tablici s desne strane prikazuje jednostavan primjer funkcije Harmean u Excelu, koristi se za izračunavanje harmonijske sredine vrijednosti u ćelijama A1-A5.

U ovom primjeru funkcija Harmean vraća vrijednost 1.229508197.

TRIMMEAN

Funkcija TRIMMEAN (također poznata kao skraćena sredina) je mjera srednje vrijednosti koja označava središnju tendenciju skupa vrijednosti.

Skraćena sredina izračunava se odbacivanjem nekih vrijednosti na krajevima raspona vrijednosti, prije izračunavanja aritmetičke sredine preostalih vrijednosti. To sprječava da izračunati prosjek bude iskrivljen zbog ekstremnih vrijednosti (tehnički poznatih i kao izvanredne vrijednosti outliers).

sintaksa

= TRIMMEAN( array, percent )

Teme

• poredak – Niz numeričkih vrijednosti za koje želite izračunati skraćenu srednju vrijednost.
• posto – Postotak vrijednosti iz kojih želite izbrisatiarray pod uvjetom.

Imajte na umu da je navedena postotna vrijednost ukupni postotak vrijednosti koje treba isključiti iz izračuna. Taj se postotak dijeli s dva kako bi se dobio broj vrijednosti uklonjenih sa svakog kraja raspona.

Također treba napomenuti da kada Excel izračuna koliko je vrijednosti izbrisano izarray navedenih vrijednosti, izračunati postotak zaokružuje se na najbliži višekratnik od 2. Na primjer, ako želite izračunati skraćenu srednju vrijednost a array od 10 vrijednosti, dakle:

• Postotak od 15% odgovara 1,5 vrijednosti, koje će biti zaokružene na 0 (tj. nijedna vrijednost neće biti odbačena izarray prije izračunavanja prosjeka);
• Postotak od 20% odgovara 2 vrijednosti, tako da će se 1 vrijednost odbaciti sa svakog kraja raspona prije izračunavanja prosjeka preostalih vrijednosti;
• Postotak od 25% odgovara 2,5 vrijednosti, koje će se zaokružiti na 2 (to jest, 1 vrijednost će se odbaciti sa svakog kraja raspona prije izračunavanja prosjeka preostalih vrijednosti).

primjer

Stanice B1-B3 u proračunskoj tablici ispod prikazana su 3 primjera funkcije trimmean u Excelu, sve se koristi za izračunavanje skraćene srednje vrijednosti u ćelijama A1-A10, za različite postotne vrijednosti.

Imajte na umu da, u ćeliji B1 gornje proračunske tablice, dani argument postotka je 15%. Budući da je uarray pod uvjetom da postoji 10 vrijednosti, broj vrijednosti koje treba zanemariti je 1,5 zaokruženo na najbliži višekratnik od 2 što je nula.

Funkcije za izračunavanje permutacija

PERMUT

Broj permutacija za određeni broj objekata je broj kombinacija bilo kojim mogućim redoslijedom.

Permutacije se razlikuju od kombinacija po tome što je za permutaciju bitan redoslijed objekata, ali u kombinaciji redoslijed nije bitan.

Broj mogućih permutacija dan je formulom:

golub k je broj odabranih objekata e n je broj mogućih objekata.

Excel funkcija Permut izračunava broj permutacija određenog broja objekata iz skupa objekata.

sintaksa

= PERMUT( number, number_chosen )

Teme

• number: Ukupan broj dostupnih stavki
• number_chosen: Broj objekata u svakoj permutaciji (tj. broj objekata odabranih iz skupa)

Imajte na umu da ako je bilo koji od argumenata dan kao decimalna vrijednost, funkcija će ih skratiti na cijele brojeve Permut.

primjer

U sljedećoj proračunskoj tablici Excel Permut koristi se za izračun broja permutacija šest objekata, odabranih iz skupova različitih veličina:

PERMUTATIONA

Excel funkcije Razmjena i Permutationa izračunavaju broj permutacija odabira objekata iz skupa.

Međutim, dvije se funkcije razlikuju po tome što Permut funkcija ne broji ponavljanja dok funkcija Permutationa broji ponavljanja.

Na primjer, u skupu od 3 objekta, a , b , c , koliko permutacija ima 2 objekta?

• La Permut funkcija vraća rezultat 6 (permutacije: ab , ac , ba , bc , ca , cb );
• Funkcija Permutationa vraća rezultat 9 (permutacije: aa , ab , ac , ba , bb , bc , ca , cb , cc ).

Excel funkcija Permutationa izračunava broj permutacija određenog broja objekata iz skupa objekata.

sintaksa

= PERMUTATIONA( number, number_chosen )

Teme

• number: Ukupan broj objekata u skupu (mora biti ≥ 0).
• number_chosen: Broj objekata odabranih iz skupa (mora biti ≥ 0).

Imajte na umu da ako je bilo koji od argumenata dan kao decimalna vrijednost, funkcija će ih skratiti na cijele brojeve PERMUTATIONA.

primjer

U sljedećoj proračunskoj tablici Excel PERMUTATIONA koristi se za izračun broja permutacija šest objekata, odabranih iz skupova različitih veličina:

Funkcije za izračunavanje intervala pouzdanosti

CONFIDENCE

U programu Excel 2010 funkcija CONFIDENCE je zamijenjen funkcijom Confidence.Norm.

Iako je zamijenjena, trenutne verzije programa Excel još uvijek imaju tu značajku Confidence (pohranjeno na popisu funkcija kompatibilnosti), kako bi se omogućila kompatibilnost s prethodnim verzijama programa Excel.

Međutim, funkcija Confidence možda neće biti dostupan u budućim verzijama Excela, stoga preporučujemo korištenje ove značajke Confidence.Norm, ako je moguće.

Funkcija Confidence Excel koristi normalnu distribuciju za izračun vrijednosti pouzdanosti koja se može koristiti za konstrukciju intervala pouzdanosti za srednju populaciju, danu vjerojatnost i veličinu uzorka. Pretpostavlja se da je standardna devijacija populacije poznata.

sintaksa

= CONFIDENCE( alpha, standard_dev, size )

Teme

• alfa: Razina značajnosti (= 1 – razina pouzdanosti). (Na primjer, razina značajnosti od 0,05 jednaka je razini pouzdanosti od 95%).
• standard_dev: Standardna devijacija populacije.
• size: Veličina uzorka populacije.

Da bi se izračunao interval pouzdanosti za srednju populaciju, vraćena vrijednost pouzdanosti mora se dodati i oduzeti od srednje vrijednosti uzorka. Znači što. za uzorak srednje vrijednosti x:

Confidence Interval =   x   ±   CONFIDENCE

primjer

U proračunskoj tablici u nastavku, funkcija pouzdanosti programa Excel koristi se za izračunavanje intervala pouzdanosti sa značajnošću od 0,05 (tj. 95% razine pouzdanosti), za srednju vrijednost uzorka od 100 visina muškaraca. Srednja vrijednost uzorka je 1,8 metara, a standardna devijacija je 0,07 metara.

Prethodna funkcija vraća vrijednost pouzdanosti od 0,013719748

Stoga je interval pouzdanosti 1,8 ± 0,013719748, što je ekvivalentno rasponu između 1,786280252 i 1,813719748

CONFIDENCE.NORM

U statistici, interval pouzdanosti je raspon unutar kojeg će parametar populacije vjerojatno pasti, za danu vjerojatnost.

Na primjer. Za danu populaciju i vjerojatnost od 95%, interval pouzdanosti je raspon u kojem je 95% vjerojatnosti da će parametar populacije pasti.

Imajte na umu da točnost intervala pouzdanosti ovisi o tome ima li populacija normalnu distribuciju.

Funkcija Confidence.Norm Excel koristi normalnu distribuciju za izračun vrijednosti pouzdanosti koja se može koristiti za konstrukciju intervala pouzdanosti za srednju populaciju, danu vjerojatnost i veličinu uzorka. Pretpostavlja se da je standardna devijacija populacije poznata.

sintaksa

= CONFIDENCE.NORM( alpha, standard_dev, size )

Teme

• alfa: Razina značajnosti (= 1 – razina pouzdanosti). (Na primjer, razina značajnosti od 0,05 jednaka je razini pouzdanosti od 95%).
• standard_dev: Standardna devijacija populacije.
• size: Veličina uzorka populacije.

Da bi se izračunao interval pouzdanosti za srednju populaciju, vraćena vrijednost pouzdanosti mora se dodati i oduzeti od srednje vrijednosti uzorka. Znači što. za uzorak srednje vrijednosti x:

Confidence Interval =   x   ±   CONFIDENCE

primjer

U proračunskoj tablici u nastavku, funkcija pouzdanosti programa Excel koristi se za izračunavanje intervala pouzdanosti sa značajnošću od 0,05 (tj. 95% razine pouzdanosti), za srednju vrijednost uzorka od 100 visina muškaraca. Srednja vrijednost uzorka je 1,8 metara, a standardna devijacija je 0,07 metara.

Prethodna funkcija vraća vrijednost pouzdanosti od 0,013719748

Stoga je interval pouzdanosti 1,8 ± 0,013719748, što je ekvivalentno rasponu između 1,786280252 i 1,813719748

Ercole Palmeri