Exceli statistilised funktsioonid keskmiste arvutamiseks: Õpetus koos näidetega, teine ​​osa

Excel pakub laia valikut statistilisi funktsioone, mis teostavad arvutusi põhikeskmisest, mediaanist ja režiimist keerukamate statistiliste jaotuste ja tõenäosustestideni.

Selles artiklis käsitleme Exceli statistilisi funktsioone keskmise arvutamiseks.

Pange tähele, et mõned statistilised funktsioonid võeti kasutusele Exceli viimastes versioonides ja seetõttu pole need vanemates versioonides saadaval.

Funktsioonid keskmiste arvutamiseks

AVERAGE

Funktsioon AVERAGE on üks Exceli statistilistest funktsioonidest. Funktsioon tagastab funktsiooni sisestatud arvväärtuste keskmise. Lihtsamalt öeldes lisab see kõik funktsioonis määratud väärtused, seejärel jagab need arvuga ja tagastab tulemuse.

süntaks

= AVERAGE(number1,number2,…)

teemasid

• numero1 : esimene arv, mida soovite keskmise arvutamiseks kasutada.
• [numero2] : teine ​​arv, mida soovite keskmistamiseks kasutada.

näide

Et näha, kuidas funktsioon töötab AVERAGE vaatame näidet:

Esimeses näites sisestasime argumendid otse funktsiooni.

Teises näites viitasime numbreid sisaldavale vahemikule. Piiramata lahtrile saate viidata pideva vahemiku abil ja kui soovite viidata dünaamilisele vahemikule, saate kasutada selleks tabelit.

Piiramata lahtrile saate viidata pideva vahemiku abil ja kui soovite viidata dünaamilisele vahemikule, saate kasutada tabelit.

Kolmandas näites viitasime vahemikule, milles lahtrid on vormindatud tekstiväärtustena. Sel juhul saate need tekstinumbrid keskmise arvutamiseks teisendada reaalarvudeks.

Neljandas näites on meil iga väärtuse ees igas lahtris apostroof ja seetõttu funktsioon seda ignoreerib.

AVERAGEA

Funktsioon AVERAGEA Excel on loetletud Microsoft Exceli statistiliste funktsioonide kategoorias. Tagastab määratud arvude keskmise funktsioonis, kuid erinevalt AVERAGE, käsitleb Boole'i ​​väärtusi ja numbreid, mis on vormindatud tekstina.

süntaks

=AVERAGEA(valore1,valore2,…)

teemasid

• value1 : väärtus, mis on arv, loogiline väärtus või tekstina salvestatud arv.
• [valore2] : väärtus, mis on arv, loogiline väärtus või tekstina salvestatud arv.

näide

Funktsiooni mõistmiseks AVERAGEA me peame nägema näidet:

Funktsiooni tagastatav väärtus on 10,17, mis on “(0+0+1+10+20+30)/6”.

AVERAGEIF

Funktsioon AVERAGEIF Excel on loetletud Microsoft Exceli statistiliste funktsioonide kategoorias. Tagastab mitmele määratud tingimusele vastavate arvude keskmise . 

süntaks

= AVERAGEIF( range, criteria, [average_range] )

Argumendid

• range:  väärtuste massiiv (või väärtusi sisaldav lahtrivahemik), et testida esitatud kriteeriumide alusel.
• criteria:  Tingimus, mida testitakse iga pakutud vahemikus oleva väärtuse suhtes.
• [average_range]:  Valikuline arvväärtuste massiiv (või numbreid sisaldavad lahtrid), mis tuleks keskmistada, kui vahemiku vastav väärtus vastab esitatud kriteeriumidele.

Kui teema [average_range] jäetakse välja, arvutatakse keskmine väärtus esialgses esitatud vahemikus.

Esitatud kriteeriumid võivad olla järgmised:

arvväärtus (sh täisarvud, kümnendkohad, kuupäevad, kellaajad ja loogilised väärtused) (nt 10, 01/01/2008, TRUE)
O
tekstistring (nt "tekst", "neljapäev") – PEAB olema jutumärkides
O
avaldis (nt “>12”, “<>0”) – PEAB olema jutumärkides.
Pange tähele ka seda, et funktsioon AVERAGEIF Excel ei ole tõstutundlik. Näiteks tekstistringid "TEXT"Ja"text” hinnatakse võrdseks.

näide

Funktsiooni mõistmiseks AVERAGEIF peame proovima seda näites.

Rakud A16-A20 järgmises arvutustabelis on viis funktsiooni näidet AVERAGEIF Excelist.

Iga funktsioonikõne jaoks AVERAGEIF Exceli teema range (testida criteria) on rakkude vahemik A1-A14 ja teema [average_range] (sisaldab keskmistatavaid väärtusi) on lahtrite vahemik B1-B14.

Pange tähele, et ülaltoodud arvutustabeli lahtrites A16, A18 ja A20 on tekstiväärtus "neljapäev" ning avaldised ">2" ja "<>TRUE” on jutumärkides. See on oluline kõigi tekstide või väljendite puhul.

AVERAGEIFS

Funktsioon AVERAGEIFS Excel on loetletud Microsoft Exceli statistiliste funktsioonide kategoorias. Tagastab mitmele määratud tingimusele vastavate arvude keskmise . Erinevalt AVERAGEIF, saate määrata mitu tingimust ja arvutada keskmise ainult kõikidele tingimustele vastavate arvude puhul.

süntaks

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

Argumendid

• average_range:  Arvväärtuste massiiv (või numbreid sisaldavad lahtrid), mis tuleb keskmistada.
• criteria_range1, [criteria_range2], …: väärtuste massiivid (või väärtusi sisaldavad lahtrite vahemikud), mida saab üksteise suhtes testida criteria1, criteria2, … (massiivid criteria_range kõik kaasasolevad peavad olema sama pikkusega).
• criteria1, [criteria2], …: tingimused, mida tuleb testida seoses väärtustega criteria_range1, [criteria_range2], …

näide

Vaatame nüüd funktsiooni näidet AVERAGEIFS:

Järgmises näites kasutasime funktsiooni AVERAGEIFS arvutada müüja “Pietro” ja toote “B” keskmine müüdud kogus. Sisestasime kriteeriumid otse funktsiooni ja meil on kaks kirjet Peetri toote B müügi kohta.

Järgmises näites oleme kasutanud AVERAGEIFS tärniga, et arvutada nende puuviljade keskmine hind, mille kogus on suurem kui 20 ühikut ja mille nimes on B.

Allolevates andmetes on meil kaks puuvilja, mis vastavad nendele kriteeriumidele.

MEDIAN

Funktsioon MEDIAN Excel tagastab esitatud arvude loendi statistilise mediaani (keskmise väärtuse).

süntaks

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

Argumendid

numbrilised argumendid on komplekt ühest või mitmest arvväärtusest (või arvväärtuste massiivist), mille jaoks soovite arvutada mediaan

Pange tähele, et:

• Kui antud andmekogumis on paarisarv väärtusi, tagastatakse kahe keskmise väärtuse keskmine;
• Kui tarnitud massiiv sisaldab tühje lahtreid, teksti või loogilisi väärtusi, ignoreeritakse neid väärtusi mediaani arvutamisel.
• Exceli praegustes versioonides (Excel 2007 ja uuemad) saate funktsioonile Mediaan esitada kuni 255 numbrilist argumenti, kuid Excel 2003 puhul saab funktsioon vastu võtta ainult kuni 30 numbrilist argumenti. Kuid iga numbriline argument võib olla paljude väärtuste massiiv.

näide

Järgmisel arvutustabelil on kolm funktsiooni näidet Median:

Mõelge sellele eelmistes näidetes:

• Lahtris olev näide B2 saab paarisarv väärtusi ja seetõttu arvutatakse mediaan kahe keskmise väärtuse 8 ja 9 keskmisena;
• Lahtris olev näide B3 sisaldab tühja lahtrit A8. Mediaani arvutamisel seda lahtrit eiratakse.

Funktsiooni kohta lisateabe saamiseks MEDIAN Excelist, vaadake Microsoft Office'i veebisait .

MODE

Funktsioon MODE Excel tagastab MODE esitatud numbrite loendi statistika (kõige sagedasem väärtus). Kui esitatud andmetes on 2 või enam korduvat väärtust, tagastab funktsioon neist madalaima väärtuse

süntaks

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

Argumendid

on komplekt ühest või mitmest arvväärtusest (või arvväärtuste massiivist), mille jaoks soovite arvutada MODE statistikat.

Märkus:

• Exceli praegustes versioonides (Excel 2007 ja uuemad) saate funktsioonile esitada kuni 255 numbrilist argumenti MODE, kuid Excel 2003-s saab funktsioon aktsepteerida ainult kuni 30 numbrilist argumenti.
• Funktsioon ignoreerib teksti ja loogilisi väärtusi antud numbrimassiivis Mode.

Funktsioonide näited MODE

Näide 1

Järgmine tabel näitab funktsiooni MODE Excel, mida kasutatakse arvutamiseks MODE lahtrite väärtuste komplekti statistika A1-A6.

Näide 2

Järgmine tabel näitab funktsiooni MODE, mida kasutatakse arvutamiseks MODE lahtrite väärtuste komplekti statistika A1-A10.

Pange tähele, et antud juhul on neid kaks mode andmetes.

Ülaltoodud juhul, kui eelmise arvutustabeli veeru A andmetel on kaks MODE statistika (3 ja 4), funktsioon MODE tagastab neist kahest väärtusest väiksema.

Funktsiooni kohta lisateavet ja näiteid MODE Excelist, vaadake Microsoft Office'i veebisait .

MODE.SNGL

Funktsioon MODE.SNGL Excel tagastab MODE esitatud numbrite loendi statistika (kõige sagedasem väärtus). Kui esitatud andmetes on 2 või enam korduvat väärtust, tagastab funktsioon neist madalaima väärtuse.

Funktsioon Mode.Sngl on Excel 2010-s uus ja seetõttu pole see Exceli varasemates versioonides saadaval. Funktsioon on aga lihtsalt funktsiooni ümbernimetatud versioon MODE saadaval Exceli varasemates versioonides.

süntaks

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

Argumendid

on komplekt ühest või mitmest arvväärtusest (või arvväärtuste massiivist), mille jaoks soovite arvutada MODE.SNGL statistikat.

Funktsioonide näited MODE.SNGL

Näide 1

Järgmine tabel näitab funktsiooni MODE.SNGL Excel, mida kasutatakse lahtrite väärtuste komplekti statistilise MODE arvutamiseks A1-A6.

Näide 2

Järgmine tabel näitab funktsiooni MODE.SNGL, mida kasutatakse lahtrite väärtuste komplekti statistilise režiimi arvutamiseks A1-A10.

Pange tähele, et antud juhul on neid kaks mode andmetes.

Ülaltoodud juhul, kui eelmise arvutustabeli veeru A andmetel on kaks MODE statistika (3 ja 4), funktsioon MODE.SNGL tagastab neist kahest väärtusest väiksema.

Funktsiooni kohta lisateavet ja näiteid MODE.SNGL Excelist, vaadake Microsoft Office'i veebisait .

GEOMEAN

Geomeetriline keskmine on keskmise mõõt, mis näitab arvude komplekti tüüpilist väärtust. Seda mõõtmist saab kasutada ainult positiivsete väärtuste korral.

Väärtuste hulga geomeetriline keskmine, y ₁ , Ja ₂ , …, seal _n see arvutatakse järgmise valemiga:

Pange tähele, et geomeetriline keskmine on alati väiksem või võrdne aritmeetilise keskmisega.

Funktsioon Geomean Excel arvutab antud väärtuste komplekti geomeetrilise keskmise.

süntaks

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

Argumendid

üks või mitu positiivset arvväärtust (või arvväärtuste massiivi), mille jaoks soovite arvutada geomeetrilise keskmise.

Exceli praegustes versioonides (Excel 2007 ja uuemad) saab funktsioon aktsepteerida kuni 255 numbrilist argumenti, kuid Excel 2003 puhul saab funktsioon aktsepteerida ainult kuni 30 numbrilist argumenti. Iga argument võib aga olla väärtuste massiiv või lahtrivahemik, millest igaüks võib sisaldada palju väärtusi.

näide

Rakk B1 arvutustabel näitab funktsiooni lihtsat näidet geomean Excelis, mida kasutatakse lahtrite A1-A5 väärtuste geomeetrilise keskmise arvutamiseks.

Selles näites tagastab funktsioon Geomean väärtuse 1.622671112 .

HARMEAN

Harmooniline keskmine on keskmise mõõt, mis arvutatakse pöördväärtuste aritmeetilise keskmise pöördarvuna. Seda saab arvutada ainult positiivsete väärtuste korral.

Väärtuste hulga y1, y2, ..., yn harmooniline keskmine on seega antud valemiga:

harmooniline keskmine on alati väiksem või võrdne geomeetrilisest keskmisest ja geomeetriline keskmine on alati väiksem või võrdne aritmeetilise keskmisega.

Funktsioon Harmean Excel arvutab antud väärtuste komplekti harmoonilise keskmise.

süntaks

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

Argumendid

üks või mitu positiivset arvväärtust (või arvväärtuste massiivi), mille harmoonilise keskmise soovite arvutada.

Exceli praegustes versioonides (Excel 2007 ja uuemad) saab funktsioon aktsepteerida kuni 255 numbrilist argumenti, kuid Excel 2003 puhul saab funktsioon aktsepteerida ainult kuni 30 numbrilist argumenti. Iga argument võib aga olla väärtuste massiiv või lahtrivahemik, millest igaüks võib sisaldada palju väärtusi.

näide

Parempoolse arvutustabeli lahter B1 näitab funktsiooni lihtsat näidet Harmean Excelis, mida kasutatakse lahtrite A1-A5 väärtuste harmoonilise keskmise arvutamiseks.

Selles näites funktsioon Harmean tagastab väärtuse 1.229508197.

TRIMMEAN

Funktsioon TRIMMEAN (tuntud ka kui kärbitud keskmine) on keskmise mõõt, mis näitab väärtuste kogumi keskset tendentsi.

Kärbitud keskmine arvutatakse, jättes kõrvale mõned väärtuste vahemiku lõpus olevad väärtused, enne kui arvutatakse ülejäänud väärtuste aritmeetiline keskmine. See hoiab ära arvutatud keskmise moonutamise äärmuslike väärtuste (tehniliselt tuntud ka kui kõrvalekallete) tõttu outliers).

süntaks

= TRIMMEAN( array, percent )

Argumendid

• massiivi – Arvväärtuste massiiv, mille jaoks soovite arvutada kärbitud keskmise.
• protsent – Väärtuste protsent, millest soovite kustutadaarray ette nähtud.

Pange tähele, et määratud protsentväärtus on arvutusest välja jäetavate väärtuste koguprotsent. See protsent jagatakse kahega, et saada vahemiku mõlemast otsast eemaldatud väärtuste arv.

Samuti tuleb märkida, et kui Excel arvutab, kui palju väärtusi failist kustutatiarray esitatud väärtustest ümardatakse arvutatud protsent allapoole 2 lähima kordseni. Näiteks kui soovite arvutada a kärbitud keskmist array 10 väärtust, seega:

• 15% protsent vastab 1,5 väärtusele, mis ümardatakse alla 0-ni (st ühtegi väärtust ei jäeta kõrvalearray enne keskmise arvutamist);
• 20% protsent vastab kahele väärtusele, seega jäetakse vahemiku mõlemast otsast üks väärtus enne ülejäänud väärtuste keskmistamist kõrvale;
• 25% protsent vastab 2,5 väärtusele, mis ümardatakse alla 2-ni (see tähendab, et vahemiku mõlemast otsast jäetakse 1 väärtus enne ülejäänud väärtuste keskmistamist kõrvale).

näide

Rakud B1-B3 alloleval arvutustabelis on 3 funktsiooni näidet trimmean Excelis, mida kasutatakse lahtrite väärtuste kärbitud keskmise arvutamiseks A1-A10, erinevate protsendiväärtuste jaoks.

Pidage meeles, et kambris B1 ülaltoodud arvutustabelist on antud protsentuaalne argument 15%. Alates aastastarray eeldusel, et väärtusi on 10, on ignoreeritavate väärtuste arv 1,5 ümardatuna allapoole 2 lähima kordseni, mis on null.

Permutatsioonide arvutamise funktsioonid

PERMUT

Antud arvu objektide permutatsioonide arv on kombinatsioonide arv mis tahes võimalikus järjekorras.

Permutatsioonid erinevad kombinatsioonidest selle poolest, et permutatsiooni puhul loeb objektide järjekord, kuid kombinatsioonis ei oma järjekord tähtsust.

Võimalike permutatsioonide arv määratakse järgmise valemiga:

tuvi k on valitud objektide arv e n on võimalike objektide arv.

Exceli funktsioon Permut arvutab objektide hulgast kindlaksmääratud arvu objektide permutatsioonide arvu.

süntaks

= PERMUT( number, number_chosen )

Argumendid

• number: saadaolevate üksuste koguarv
• number_chosen: objektide arv igas permutatsioonis (st komplektist valitud objektide arv)

Pange tähele, et kui mõni argument on antud kümnendarvuna, kärbib funktsioon need täisarvudeks Permut.

näide

Järgmises arvutustabelis on Excel Permut kasutatakse kuue objekti permutatsioonide arvu arvutamiseks, mis on valitud erineva suurusega komplektidest:

PERMUTATIONA

Exceli funktsioonid Vahetada ja Permutationa arvutavad mõlemad komplektist valitud objektide permutatsioonide arvu.

Need kaks funktsiooni erinevad aga selle poolest, et Permut.funktsioon ei loe kordusi, samas kui funktsioon Permutationa loeb kordusi.

Näiteks kolmest objektist koosnevas komplektis a , b , c , mitu permutatsiooni on kahel objektil?

• La Permut.funktsioon tagastab tulemuse 6 (permutatsioonid: ab , ac , ba , bc , ca , cb );
• Funktsioon Permutationa tagastab tulemuse 9 (permutatsioonid: aa , ab , ac , ba , bb , bc , ca , cb , cc ).

Exceli funktsioon Permutationa arvutab objektide hulgast kindlaksmääratud arvu objektide permutatsioonide arvu.

süntaks

= PERMUTATIONA( number, number_chosen )

Argumendid

• number: objektide koguarv komplektis (peab olema ≥ 0).
• number_chosen: komplektist valitud objektide arv (peab olema ≥ 0).

Pange tähele, et kui mõni argument on antud kümnendarvuna, kärbib funktsioon need täisarvudeks PERMUTATIONA.

näide

Järgmises arvutustabelis on Excel PERMUTATIONA kasutatakse kuue objekti permutatsioonide arvu arvutamiseks, mis on valitud erineva suurusega komplektidest:

Usaldusvahemike arvutamise funktsioonid

CONFIDENCE

Excel 2010-s on funktsioon CONFIDENCE on asendatud funktsiooniga Confidence.Norm.

Kuigi see on asendatud, on Exceli praegustel versioonidel see funktsioon endiselt olemas Confidence (salvestatud ühilduvusfunktsioonide loendis), et võimaldada ühilduvust Exceli eelmiste versioonidega.

Funktsioon siiski Confidence ei pruugi olla Exceli tulevastes versioonides saadaval, seega soovitame seda funktsiooni kasutada Confidence.Norm, kui võimalik.

Funktsioon Confidence Excel kasutab usaldusväärtuse arvutamiseks normaaljaotust, mida saab kasutada populatsiooni keskmise, antud tõenäosuse ja valimi suuruse usaldusvahemiku koostamiseks. Eeldatakse, et populatsiooni standardhälve on teada.

süntaks

= CONFIDENCE( alpha, standard_dev, size )

Argumendid

• alfa: olulisuse tase (= 1 – usaldusnivoo). (Näiteks olulisuse tase 0,05 võrdub 95% usaldustasemega).
• standard_dev: populatsiooni standardhälve.
• size: üldkogumi valimi suurus.

Populatsiooni keskmise usaldusvahemiku arvutamiseks tuleb tagastatud usaldusväärtus lisada valimi keskmisele ja lahutada sellest. Mis tähendab. valimi keskmine x:

Confidence Interval =   x   ±   CONFIDENCE

näide

Alloleval tabelis kasutatakse Exceli usaldusfunktsiooni usaldusvahemiku arvutamiseks olulisusega 0,05 (st 95% usaldusnivooga) 100 mehepikkuse valimi keskmise jaoks. Valimi keskmine on 1,8 meetrit ja standardhälve 0,07 meetrit.

Eelmine funktsioon tagastab usaldusväärtuse 0,013719748

Seetõttu on usaldusvahemik 1,8 ± 0,013719748, mis on samaväärne vahemikuga 1,786280252 kuni 1,813719748

CONFIDENCE.NORM

Statistikas on usaldusvahemik vahemik, millesse populatsiooni parameeter teatud tõenäosuse korral tõenäoliselt langeb.

Näiteks. Teatud populatsiooni ja 95% tõenäosusega on usaldusvahemik vahemik, millesse populatsiooni parameeter langeb 95% tõenäosusega.

Pange tähele, et usaldusvahemiku täpsus sõltub sellest, kas populatsioonil on normaaljaotus.

Funktsioon Confidence.Norm Excel kasutab usaldusväärtuse arvutamiseks normaaljaotust, mida saab kasutada populatsiooni keskmise, antud tõenäosuse ja valimi suuruse usaldusvahemiku koostamiseks. Eeldatakse, et populatsiooni standardhälve on teada.

süntaks

= CONFIDENCE.NORM( alpha, standard_dev, size )

Argumendid

• alfa: olulisuse tase (= 1 – usaldusnivoo). (Näiteks olulisuse tase 0,05 võrdub 95% usaldustasemega).
• standard_dev: populatsiooni standardhälve.
• size: üldkogumi valimi suurus.

Populatsiooni keskmise usaldusvahemiku arvutamiseks tuleb tagastatud usaldusväärtus lisada valimi keskmisele ja lahutada sellest. Mis tähendab. valimi keskmine x:

Confidence Interval =   x   ±   CONFIDENCE

näide

Alloleval tabelis kasutatakse Exceli usaldusfunktsiooni usaldusvahemiku arvutamiseks olulisusega 0,05 (st 95% usaldusnivooga) 100 mehepikkuse valimi keskmise jaoks. Valimi keskmine on 1,8 meetrit ja standardhälve 0,07 meetrit.

Eelmine funktsioon tagastab usaldusväärtuse 0,013719748

Seetõttu on usaldusvahemik 1,8 ± 0,013719748, mis on samaväärne vahemikuga 1,786280252 kuni 1,813719748

Ercole Palmeri