Funksionet statistikore të Excel për llogaritjen e mesatareve: Tutorial me shembuj, pjesa e dytë

Excel ofron një gamë të gjerë funksionesh statistikore që kryejnë llogaritjet nga mesatarja bazë, mesatare dhe modaliteti deri te shpërndarjet më komplekse statistikore dhe testet e probabilitetit.

Në këtë artikull do të shqyrtojmë funksionet statistikore të Excel për llogaritjen e mesatares.

Ju lutemi vini re se disa funksione statistikore u prezantuan në versionet e fundit të Excel dhe për këtë arsye nuk janë të disponueshme në versionet e vjetra.

Funksionet për llogaritjen e mesatareve

AVERAGE

Funksioni AVERAGE është një nga funksionet statistikore të Excel-it. Funksioni kthen mesataren e vlerave numerike të futura në funksion. Me fjalë të thjeshta, ai shton të gjitha vlerat e specifikuara në funksion, më pas i ndan ato me numërimin dhe kthen rezultatin.

sintaksë

= AVERAGE(number1,number2,…)

lëndët

• numero1 : Numri i parë që dëshironi të përdorni për llogaritjen e mesatares.
• [numero2] : Numri i dytë që dëshironi të përdorni për mesataren.

shembull

Për të parë se si funksionon funksioni AVERAGE le të shohim një shembull:

Në shembullin e parë ne futëm argumentet direkt në funksion.

Në shembullin e dytë, ne iu referuam një gamë që përmban numra. Ju mund t'i referoheni qelizës së pakufizuar duke përdorur një interval të vazhdueshëm dhe nëse dëshironi t'i referoheni një diapazoni dinamik, mund të përdorni një tabelë për këtë.

Ju mund t'i referoheni qelizës së pakufizuar duke përdorur një interval të vazhdueshëm dhe nëse dëshironi t'i referoheni një diapazoni dinamik, mund të përdorni një tabelë.

Në shembullin e tretë ne iu referuam një diapazoni në të cilin qelizat janë formatuar si vlera teksti. Në këtë rast, ju mund t'i konvertoni ato numra teksti në numra realë për të llogaritur mesataren.

Në shembullin e katërt kemi një apostrof përpara çdo vlere në secilën qelizë dhe për këtë arsye të injoruar nga funksioni.

AVERAGEA

Funksioni AVERAGEA i Excel është renditur në kategorinë e funksioneve statistikore të Microsoft Excel. Rikthen mesataren e numrave të specifikuar në funksion, por ndryshe nga AVERAGE, trajton vlerat Boolean dhe numrat e formatuar si tekst.

sintaksë

=AVERAGEA(valore1,valore2,…)

lëndët

• value1 : Një vlerë që është një numër, një vlerë logjike ose një numër i ruajtur si tekst.
• [valore2] : Një vlerë që është një numër, një vlerë logjike ose një numër i ruajtur si tekst.

shembull

Për të kuptuar funksionin AVERAGEA duhet të shohim një shembull:

Vlera e kthyer nga funksioni është 10,17 që është “(0+0+1+10+20+30)/6”.

AVERAGEIF

Funksioni AVERAGEIF i Excel është renditur në kategorinë e funksioneve statistikore të Microsoft Excel. Rikthen mesataren e numrave që plotësojnë disa kushte të specifikuara . 

sintaksë

= AVERAGEIF( range, criteria, [average_range] )

argumentet

• range:  një grup vlerash (ose një varg qelizash që përmbajnë vlera) për të testuar kundrejt kritereve të parashikuara.
• criteria:  Kushti që do të testohet kundrejt secilës prej vlerave në diapazonin e dhënë.
• [average_range]:  Një grup opsional vlerash numerike (ose qeliza që përmbajnë numra) që duhet të mesatarizohen nëse vlera përkatëse në interval plotëson kriteret e dhëna.

Nëse tema [average_range] është lënë jashtë, mesatarja llogaritet për vlerat në intervalin fillestar të dhënë.

Kriteret e ofruara mund të jenë:

një vlerë numerike (përfshirë numra të plotë, dhjetorë, data, kohë dhe vlera logjike) (për shembull, 10, 01/01/2008, E VËRTETË)
O
një varg teksti (p.sh. "Text", "E Enjte") - DUHET të jepet në thonjëza
O
një shprehje (p.sh. ">12", "<>0") - DUHET të jepet në thonjëza.
Gjithashtu vini re se funksioni AVERAGEIF Excel nuk është i ndjeshëm ndaj shkronjave të vogla. Kështu, për shembull, vargjet e tekstit "TEXT"Dhe"text” do të vlerësohet e barabartë.

shembull

Për të kuptuar funksionin AVERAGEIF duhet ta provojmë në një shembull.

Qelizat A16-A20 e tabelës së mëposhtme tregoni pesë shembuj të funksionit AVERAGEIF të Excel-it.

Për çdo thirrje funksioni AVERAGEIF e Excel, tema range (për t'u testuar kundër criteria) është diapazoni i qelizave A1-A14 dhe tema [average_range] (që përmban vlerat që do të mesatarizohen) është diapazoni i qelizave B1-B14.

Vini re se, në qelizat A16, A18 dhe A20 të tabelës së mësipërme, vlera e tekstit "E Enjte" dhe shprehjet ">2" dhe "<>TRUE” janë të mbyllura në thonjëza. Kjo është thelbësore për të gjitha tekstet ose shprehjet.

AVERAGEIFS

Funksioni AVERAGEIFS i Excel është renditur në kategorinë e funksioneve statistikore të Microsoft Excel. Rikthen mesataren e numrave që plotësojnë disa kushte të specifikuara . Ndryshe nga AVERAGEIF, mund të vendosni kushte të shumta dhe të llogaritni mesataren vetëm për numrat që plotësojnë të gjitha kushtet.

sintaksë

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

argumentet

• average_range:  Një grup vlerash numerike (ose qeliza që përmbajnë numra) që do të mesatarizohen.
• criteria_range1, [criteria_range2], …: Vargje vlerash (ose vargje qelizash që përmbajnë vlera) për t'u testuar kundër njëra-tjetrës criteria1, criteria2, … (Vargjet criteria_range të furnizuara duhet të kenë të gjithë të njëjtën gjatësi).
• criteria1, [criteria2], …: Kushtet që do të testohen në lidhje me vlerat në criteria_range1, [criteria_range2], …

shembull

Tani le të shohim një shembull të funksionit AVERAGEIFS:

Në shembullin e mëposhtëm, ne kemi përdorur funksionin AVERAGEIFS për të llogaritur sasinë mesatare të shitur nga shitësi “Pietro” dhe për produktin “B”. Kemi futur kriteret direkt në funksion dhe kemi dy hyrje të shitjes së Pjetrit të produktit B.

Në shembullin e mëposhtëm, ne kemi përdorur AVERAGEIFS me një yll për të llogaritur çmimin mesatar të frutave, sasia e të cilit është më e madhe se 20 njësi dhe ka B në emër.

Në të dhënat e mëposhtme kemi dy fruta që plotësojnë këto kritere.

MEDIAN

Funksioni MEDIAN Excel kthen mesataren statistikore (vlerën mesatare) të një liste numrash të dhënë.

sintaksë

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

argumentet

argumentet numerike janë një grup i një ose më shumë vlerave numerike (ose vargjeve të vlerave numerike), për të cilat dëshironi të llogaritni mesataren

Vini re se:

• Nëse ka një numër çift vlerash në të dhënat e dhëna, kthehet mesatarja e dy vlerave mesatare;
• Nëse një grup i dhënë përmban qeliza bosh, tekst ose vlera logjike, këto vlera injorohen kur llogaritet mesatarja.
• Në versionet aktuale të Excel (Excel 2007 dhe më vonë), mund të jepni deri në 255 argumente numerike në funksionin Median, por në Excel 2003 funksioni mund të pranojë vetëm deri në 30 argumente numerike. Megjithatë, secili nga argumentet numerike mund të jetë një grup me shumë vlera.

shembull

Tabela e mëposhtme tregon tre shembuj të funksionit Median:

Konsideroni se, në shembujt e mëparshëm:

• Shembulli në qelizë B2 merr një numër çift vlerash dhe për këtë arsye mediana llogaritet si mesatare e dy vlerave mesatare, 8 dhe 9;
• Shembulli në qelizë B3 përfshin qelizën e zbrazët A8. Kjo qelizë nuk merret parasysh kur llogaritet mesatarja.

Për më shumë detaje mbi funksionin MEDIAN të Excel, shih Faqja e internetit e Microsoft Office .

MODE

Funksioni MODE e Excel kthen MODE statistika (vlera më e shpeshtë) e një liste numrash të dhënë. Nëse ka 2 ose më shumë vlera të përsëritura në të dhënat e dhëna, funksioni kthen vlerën më të ulët midis tyre

sintaksë

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

argumentet

janë një grup i një ose më shumë vlerave numerike (ose vargjeve të vlerave numerike), për të cilat dëshironi të llogaritni MODE statistikat.

Shenim:

• Në versionet aktuale të Excel (Excel 2007 dhe më vonë), ju mund të jepni deri në 255 argumente numerike për funksionin MODE, por në Excel 2003 funksioni mund të pranojë vetëm deri në 30 argumente numerike.
• Teksti dhe vlerat logjike brenda një grupi numrash të dhënë injorohen nga funksioni Mode.

Shembuj funksionesh MODE

Shembulli 1

Tabela e mëposhtme tregon funksionin MODE Excel, përdoret për të llogaritur MODE statistikat e grupit të vlerave në qeliza A1-A6.

Shembulli 2

Tabela e mëposhtme tregon funksionin MODE, përdoret për të llogaritur MODE statistikat e grupit të vlerave në qeliza A1-A10.

Vini re se në këtë rast janë dy mode në të dhënat.

Në rastin e mësipërm, ku të dhënat në kolonën A të tabelës së mëparshme kanë dy MODE statistikat (3 dhe 4), funksioni MODE kthen vlerën më të ulët nga këto dy vlera.

Për detaje të mëtejshme dhe shembuj të funksionit MODE të Excel, shih Faqja e internetit e Microsoft Office .

MODE.SNGL

Funksioni MODE.SNGL e Excel kthen MODE statistika (vlera më e shpeshtë) e një liste numrash të dhënë. Nëse ka 2 ose më shumë vlera të përsëritura në të dhënat e dhëna, funksioni kthen vlerën më të ulët midis tyre.

Funksioni Mode.Sngl është e re në Excel 2010 dhe për këtë arsye nuk disponohet në versionet e mëparshme të Excel. Sidoqoftë, funksioni është thjesht një version i riemërtuar i funksionit MODE në dispozicion në versionet e mëparshme të Excel.

sintaksë

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

argumentet

janë një grup i një ose më shumë vlerave numerike (ose vargjeve të vlerave numerike), për të cilat dëshironi të llogaritni MODE.SNGL statistikat.

Shembuj funksionesh MODE.SNGL

Shembulli 1

Tabela e mëposhtme tregon funksionin MODE.SNGL Excel, përdoret për të llogaritur MODE statistikore të grupit të vlerave në qeliza A1-A6.

Shembulli 2

Tabela e mëposhtme tregon funksionin MODE.SNGL, përdoret për të llogaritur mënyrën statistikore të grupit të vlerave në qeliza A1-A10.

Vini re se në këtë rast janë dy mode në të dhënat.

Në rastin e mësipërm, ku të dhënat në kolonën A të tabelës së mëparshme kanë dy MODE statistikat (3 dhe 4), funksioni MODE.SNGL kthen vlerën më të ulët nga këto dy vlera.

Për detaje të mëtejshme dhe shembuj të funksionit MODE.SNGL të Excel, shih Faqja e internetit e Microsoft Office .

GEOMEAN

Mesatarja gjeometrike është një masë e mesatares që tregon vlerën tipike të një grupi numrash. Kjo matje mund të përdoret vetëm për vlera pozitive.

Mesatarja gjeometrike e një grupi vlerash, y ₁ , Dhe ₂ , …, atje _n llogaritet me formulën:

Vini re se mesatarja gjeometrike është gjithmonë më e vogël ose e barabartë me mesataren aritmetike.

Funksioni Geomean Excel llogarit mesataren gjeometrike të një grupi të caktuar vlerash.

sintaksë

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

argumentet

një ose më shumë vlera numerike pozitive (ose grupe vlerash numerike), për të cilat dëshironi të llogaritni mesataren gjeometrike.

Në versionet aktuale të Excel (Excel 2007 dhe më vonë), funksioni mund të pranojë deri në 255 argumente numerike, por në Excel 2003 funksioni mund të pranojë vetëm deri në 30 argumente numerike. Sidoqoftë, çdo argument mund të jetë një grup vlerash ose një varg qelizash, secila prej të cilave mund të përmbajë shumë vlera.

shembull

Qeliza B1 e spreadsheet tregon një shembull të thjeshtë të funksionit geomean në Excel, përdoret për të llogaritur mesataren gjeometrike të vlerave në qelizat A1-A5.

Në këtë shembull, funksioni Geomean kthen vlerën 1.622671112 .

HARMEAN

Mesatarja harmonike është një masë e mesatares e llogaritur si reciproke e mesatares aritmetike të reciprokeve. Kjo mund të llogaritet vetëm për vlera pozitive.

Prandaj, mesatarja harmonike e një grupi vlerash, y1, y2, ..., yn jepet me formulën:

mesatarja harmonike është gjithmonë më e vogël ose e barabartë me mesataren gjeometrike dhe mesatarja gjeometrike është gjithmonë më e vogël ose e barabartë me mesataren aritmetike.

Funksioni Harmean Excel llogarit mesataren harmonike të një grupi të caktuar vlerash.

sintaksë

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

argumentet

një ose më shumë vlera numerike pozitive (ose grupe vlerash numerike), për të cilat dëshironi të llogaritni mesataren harmonike.

Në versionet aktuale të Excel (Excel 2007 dhe më vonë), funksioni mund të pranojë deri në 255 argumente numerike, por në Excel 2003 funksioni mund të pranojë vetëm deri në 30 argumente numerike. Sidoqoftë, çdo argument mund të jetë një grup vlerash ose një varg qelizash, secila prej të cilave mund të përmbajë shumë vlera.

shembull

Qeliza B1 në tabelën në të djathtë tregon një shembull të thjeshtë të funksionit Harmean në Excel, përdoret për të llogaritur mesataren harmonike të vlerave në qelizat A1-A5.

Në këtë shembull, funksioni Harmean kthen vlerën 1.229508197.

TRIMMEAN

Funksioni TRIMMEAN (i njohur edhe si mesatarja e shkurtuar) është një masë e mesatares që tregon tendencën qendrore të një grupi vlerash.

Mesatarja e shkurtuar llogaritet duke hedhur disa vlera në skajet e diapazonit të vlerave, përpara se të llogaritet mesatarja aritmetike e vlerave të mbetura. Kjo parandalon që mesatarja e llogaritur të shtrembërohet nga vlerat ekstreme (të njohura edhe si periferike, teknikisht outliers).

sintaksë

= TRIMMEAN( array, percent )

argumentet

• grup – Një grup vlerash numerike për të cilat dëshironi të llogaritni mesataren e cunguar.
• përqind – Përqindja e vlerave nga të cilat dëshironi të fshiniarray me kusht.

Vini re se vlera e përqindjes e specifikuar është përqindja totale e vlerave që duhet të përjashtohen nga llogaritja. Kjo përqindje ndahet me dy për të marrë numrin e vlerave të hequra nga çdo skaj i diapazonit.

Duhet gjithashtu të theksohet se kur Excel llogarit se sa vlera janë fshirë ngaarray e vlerave të dhëna, përqindja e llogaritur rrumbullakoset në shumëfishin më të afërt të 2. Për shembull, nëse dëshironi të llogaritni mesataren e shkurtuar të a array prej 10 vlerash, pra:

• Një përqindje prej 15% korrespondon me 1,5 vlera, të cilat do të rrumbullakosen në 0 (d.m.th. asnjë vlerë nuk do të hiqet ngaarray para llogaritjes së mesatares);
• Një përqindje prej 20% korrespondon me 2 vlera, kështu që 1 vlerë do të hiqet nga çdo skaj i diapazonit përpara se të mesatarizohen vlerat e mbetura;
• Një përqindje prej 25% korrespondon me 2,5 vlera, të cilat do të rrumbullakosen në 2 (d.m.th., 1 vlerë do të hiqet nga çdo skaj i diapazonit përpara se të mesatarizohen vlerat e mbetura).

shembull

Qelizat B1-B3 në tabelën më poshtë tregoni 3 shembuj të funksionit trimmean në Excel, të gjitha përdoren për të llogaritur mesataren e shkurtuar të vlerave në qeliza A1-A10, për vlera të ndryshme përqindjeje.

Mbani parasysh se, në qeli B1 nga tabela e mësipërme, argumenti i përqindjes i dhënë është 15%. Që nëarray me kusht që të ketë 10 vlera, numri i vlerave që duhen injoruar është 1,5 i rrumbullakosur në shumëfishin më të afërt të 2 që është zero.

Funksionet për llogaritjen e permutacioneve

PERMUT

Numri i permutacioneve për një numër të caktuar objektesh është numri i kombinimeve në çdo rend të mundshëm.

Permutacionet ndryshojnë nga kombinimet në atë që, për një ndërrim, rendi i objekteve ka rëndësi, por në një kombinim rendi nuk ka rëndësi.

Numri i permutacioneve të mundshme jepet me formulën:

pëllumb k është numri i objekteve të zgjedhur e n është numri i objekteve të mundshme.

Funksioni Excel Permut llogarit numrin e permutacioneve të një numri të caktuar objektesh nga një grup objektesh.

sintaksë

= PERMUT( number, number_chosen )

argumentet

• number: Numri i përgjithshëm i artikujve në dispozicion
• number_chosen: Numri i objekteve në çdo ndërrim (d.m.th. numri i objekteve të zgjedhura nga grupi)

Vini re se nëse ndonjë nga argumentet jepet si vlera dhjetore, ato do të shkurtohen në numra të plotë nga funksioni Permut.

shembull

Në tabelën e mëposhtme, Excel Permut përdoret për të llogaritur numrin e permutacioneve të gjashtë objekteve, të zgjedhura nga grupe të madhësive të ndryshme:

PERMUTATIONA

funksionet e Excel Shkëmbim dhe Permutationa të dyja llogaritin numrin e permutacioneve të një përzgjedhjeje objektesh nga një grup.

Megjithatë, të dy funksionet ndryshojnë në atë Funksioni i përhershëm nuk numëron përsëritjet ndërsa funksioni Permutationa numëron përsëritjet.

Për shembull, në një grup prej 3 objektesh, a , b , c , sa permutacione ka 2 objekte?

• La Funksioni i përhershëm kthen rezultatin 6 (permutacione: ab , ac , ba , bc , ca , cb );
• Funksioni Permutationa kthen rezultatin 9 (permutacionet: aa , ab , ac , ba , bb , bc , ca , cb , cc ).

Funksioni Excel Permutationa llogarit numrin e permutacioneve të një numri të caktuar objektesh nga një grup objektesh.

sintaksë

= PERMUTATIONA( number, number_chosen )

argumentet

• number: Numri total i objekteve në grup (duhet të jetë ≥ 0).
• number_chosen: Numri i objekteve të zgjedhura nga grupi (duhet të jetë ≥ 0).

Vini re se nëse ndonjë nga argumentet jepet si vlera dhjetore, ato do të shkurtohen në numra të plotë nga funksioni PERMUTATIONA.

shembull

Në tabelën e mëposhtme, Excel PERMUTATIONA përdoret për të llogaritur numrin e permutacioneve të gjashtë objekteve, të zgjedhura nga grupe të madhësive të ndryshme:

Funksionet për llogaritjen e intervaleve të besimit

CONFIDENCE

Në Excel 2010, funksioni CONFIDENCE është zëvendësuar nga funksioni Confidence.Norm.

Edhe pse është zëvendësuar, versionet aktuale të Excel e kanë ende funksionin Confidence (të ruajtura në listën e funksioneve të përputhshmërisë), për të lejuar përputhshmërinë me versionet e mëparshme të Excel.

Megjithatë, funksioni Confidence mund të mos jetë i disponueshëm në versionet e ardhshme të Excel, prandaj ju rekomandojmë të përdorni funksionin Confidence.Norm, nëse është e mundur.

Funksioni Confidence Excel përdor një shpërndarje normale për të llogaritur një vlerë besimi që mund të përdoret për të ndërtuar intervalin e besueshmërisë për një mesatare të popullsisë, një probabilitet të caktuar dhe një madhësi të mostrës. Supozohet se devijimi standard i popullsisë është i njohur.

sintaksë

= CONFIDENCE( alpha, standard_dev, size )

argumentet

• alfa: Niveli i rëndësisë (= 1 – niveli i besimit). (Për shembull, një nivel i rëndësisë prej 0,05 barazohet me një nivel besimi 95%).
• standard_dev: Devijimi standard i popullsisë.
• size: Madhësia e kampionit të popullsisë.

Për të llogaritur intervalin e besimit për një mesatare të popullsisë, vlera e besimit të kthyer duhet t'i shtohet dhe të zbritet nga mesatarja e mostrës. Do të thotë çfarë. për kampionin mesatarja x:

Confidence Interval =   x   ±   CONFIDENCE

shembull

Në tabelën më poshtë, funksioni i besimit në Excel përdoret për të llogaritur intervalin e besueshmërisë me një rëndësi prej 0,05 (d.m.th. një nivel besimi 95%) për mesataren e një kampioni prej 100 lartësish meshkujsh. Mesatarja e mostrës është 1,8 metra dhe devijimi standard është 0,07 metra.

Funksioni i mëparshëm kthen një vlerë besimi prej 0,013719748

Prandaj, intervali i besimit është 1,8 ± 0,013719748, që është ekuivalente me diapazonin midis 1,786280252 dhe 1,813719748

CONFIDENCE.NORM

Në statistika, intervali i besimit është diapazoni brenda të cilit një parametër i popullsisë ka të ngjarë të bjerë, për një probabilitet të caktuar.

Për shembull. Për një popullsi të caktuar dhe një probabilitet 95%, intervali i besimit është diapazoni në të cilin një parametër i popullsisë ka 95% gjasa të bjerë.

Vini re se saktësia e intervalit të besimit varet nëse popullsia ka një shpërndarje normale.

Funksioni Confidence.Norm Excel përdor një shpërndarje normale për të llogaritur një vlerë besimi që mund të përdoret për të ndërtuar intervalin e besueshmërisë për një mesatare të popullsisë, një probabilitet të caktuar dhe një madhësi të mostrës. Supozohet se devijimi standard i popullsisë është i njohur.

sintaksë

= CONFIDENCE.NORM( alpha, standard_dev, size )

argumentet

• alfa: Niveli i rëndësisë (= 1 – niveli i besimit). (Për shembull, një nivel i rëndësisë prej 0,05 barazohet me një nivel besimi 95%).
• standard_dev: Devijimi standard i popullsisë.
• size: Madhësia e kampionit të popullsisë.

Për të llogaritur intervalin e besimit për një mesatare të popullsisë, vlera e besimit të kthyer duhet t'i shtohet dhe të zbritet nga mesatarja e mostrës. Do të thotë çfarë. për kampionin mesatarja x:

Confidence Interval =   x   ±   CONFIDENCE

shembull

Në tabelën më poshtë, funksioni i besimit në Excel përdoret për të llogaritur intervalin e besueshmërisë me një rëndësi prej 0,05 (d.m.th. një nivel besimi 95%) për mesataren e një kampioni prej 100 lartësish meshkujsh. Mesatarja e mostrës është 1,8 metra dhe devijimi standard është 0,07 metra.

Funksioni i mëparshëm kthen një vlerë besimi prej 0,013719748

Prandaj, intervali i besimit është 1,8 ± 0,013719748, që është ekuivalente me diapazonin midis 1,786280252 dhe 1,813719748

Ercole Palmeri