Excel statistiske funksjoner for beregning av gjennomsnitt: Opplæring med eksempler, del to

Excel tilbyr et bredt spekter av statistiske funksjoner som utfører beregninger fra grunnleggende gjennomsnitt, median og modus til mer komplekse statistiske distribusjoner og sannsynlighetstester.

I denne artikkelen skal vi fordype oss i Excels statistiske funksjoner for å beregne gjennomsnittet.

Vær oppmerksom på at enkelte statistiske funksjoner ble introdusert i nyere versjoner av Excel og er derfor ikke tilgjengelige i eldre versjoner.

Funksjoner for beregning av gjennomsnitt

AVERAGE

Funksjonen AVERAGE er en av Excels statistiske funksjoner. Funksjonen returnerer gjennomsnittet av de numeriske verdiene som er lagt inn i funksjonen. Med enkle ord legger den til alle verdiene spesifisert i funksjonen, deler dem deretter med antallet og returnerer resultatet.

syntaks

= AVERAGE(number1,number2,…)

fag

• numero1 : Det første tallet du vil bruke for å beregne gjennomsnittet.
• [numero2] : Det andre tallet du vil bruke for gjennomsnittsberegning.

eksempel

For å se hvordan funksjonen fungerer AVERAGE la oss se et eksempel:

I det første eksemplet satte vi inn argumentene direkte i funksjonen.

I det andre eksemplet refererte vi til et område som inneholder tall. Du kan referere til den ubegrensede cellen ved å bruke et kontinuerlig område, og hvis du vil referere til et dynamisk område, kan du bruke en tabell for det.

Du kan referere til den ubegrensede cellen ved å bruke et kontinuerlig område, og hvis du vil referere til et dynamisk område kan du bruke en tabell.

I det tredje eksemplet refererte vi til et område der cellene er formatert som tekstverdier. I dette tilfellet kan du konvertere disse teksttallene til reelle tall for å beregne gjennomsnittet.

I det fjerde eksemplet har vi en apostrof foran hver verdi i hver celle og derfor ignorert av funksjonen.

AVERAGEA

Funksjonen AVERAGEA av Excel er oppført i kategorien Microsoft Excel Statistical Functions. Returnerer gjennomsnittet av de angitte tallene i funksjon, men ulikt AVERAGE, behandler boolske verdier og tall formatert som tekst.

syntaks

=AVERAGEA(valore1,valore2,…)

fag

• value1 : En verdi som er et tall, en logisk verdi eller et tall lagret som tekst.
• [valore2] : En verdi som er et tall, en logisk verdi eller et tall lagret som tekst.

eksempel

For å forstå funksjonen AVERAGEA vi må se et eksempel:

Verdien som returneres av funksjonen er 10,17 som er "(0+0+1+10+20+30)/6".

AVERAGEIF

Funksjonen AVERAGEIF av Excel er oppført i kategorien Microsoft Excel Statistical Functions. Returnerer gjennomsnittet av tall som tilfredsstiller flere spesifiserte betingelser . 

syntaks

= AVERAGEIF( range, criteria, [average_range] )

emner

• range:  en rekke verdier (eller en rekke celler som inneholder verdier) for å teste mot de angitte kriteriene.
• criteria:  Tilstanden som skal testes mot hver av verdiene i det angitte området.
• [average_range]:  En valgfri matrise med numeriske verdier (eller celler som inneholder tall) som skal beregnes som gjennomsnitt hvis den tilsvarende verdien i området oppfyller kriteriene som er oppgitt.

Hvis temaet [average_range] er utelatt, beregnes gjennomsnittet for verdiene i det opprinnelige angitte området.

Kriteriene som er oppgitt kan være:

en numerisk verdi (inkludert heltall, desimaler, datoer, klokkeslett og logiske verdier) (for eksempel 10, 01/01/2008, TRUE)
O
en tekststreng (f.eks. «Tekst», «torsdag») – MÅ angis i anførselstegn
O
et uttrykk (f.eks. ">12", "<>0") – MÅ angis i anførselstegn.
Merk også at funksjonen AVERAGEIF Excel skiller ikke mellom store og små bokstaver. Så for eksempel tekststrengene "TEXT"Og"text” vil bli vurdert som likeverdig.

eksempel

For å forstå funksjonen AVERAGEIF vi må prøve det i et eksempel.

Cellene A16-A20 i følgende regneark viser fem eksempler på funksjonen AVERAGEIF av Excel.

For hvert funksjonsanrop AVERAGEIF av Excel, emnet range (skal testes mot criteria) er celleområdet A1-A14 og temaet [average_range] (som inneholder verdiene som skal beregnes i gjennomsnitt) er celleområdet B1-B14.

Merk at i cellene A16, A18 og A20 i regnearket ovenfor, tekstverdien «torsdag» og uttrykkene «>2» og «<>TRUE” er omgitt av anførselstegn. Dette er viktig for alle tekster eller uttrykk.

AVERAGEIFS

Funksjonen AVERAGEIFS av Excel er oppført i kategorien Microsoft Excel Statistical Functions. Returnerer gjennomsnittet av tall som tilfredsstiller flere spesifiserte betingelser . I motsetning til AVERAGEIF, kan du angi flere betingelser og beregne gjennomsnittet bare for tall som oppfyller alle betingelser.

syntaks

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

emner

• average_range:  En rekke numeriske verdier (eller celler som inneholder tall) som skal gjennomsnittliggjøres.
• criteria_range1, [criteria_range2], …: Matriser med verdier (eller celleområder som inneholder verdier) for å teste mot hverandre criteria1, criteria2, … (Arrayene criteria_range som følger med må alle ha samme lengde).
• criteria1, [criteria2], …: Betingelsene som skal testes med hensyn til verdiene i criteria_range1, [criteria_range2], …

eksempel

La oss nå se på et eksempel på funksjonen AVERAGEIFS:

I følgende eksempel har vi brukt funksjonen AVERAGEIFS å beregne gjennomsnittlig mengde solgt av selgeren "Pietro" og for produktet "B". Vi la inn kriteriene direkte i funksjonen og har to oppføringer av Peters salg av produkt B.

I følgende eksempel har vi brukt AVERAGEIFS med en stjerne for å beregne gjennomsnittsprisen på frukt hvis mengde er større enn 20 enheter og har B i navnet.

I dataene nedenfor har vi to frukter som oppfyller disse kriteriene.

MEDIAN

Funksjonen MEDIAN Excel returnerer den statistiske medianen (gjennomsnittsverdien) av en liste over oppgitte tall.

syntaks

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

emner

numeriske argumenter er et sett med én eller flere numeriske verdier (eller matriser med numeriske verdier), som du vil beregne medianen for

Noter det:

• Hvis det er et partall verdier i det gitte datasettet, returneres gjennomsnittet av de to gjennomsnittsverdiene;
• Hvis en oppgitt matrise inneholder tomme celler, tekst eller logiske verdier, ignoreres disse verdiene når medianen beregnes.
• I gjeldende versjoner av Excel (Excel 2007 og nyere) kan du levere opptil 255 numeriske argumenter til Median-funksjonen, men i Excel 2003 kan funksjonen bare godta opptil 30 numeriske argumenter. Hvert av de numeriske argumentene kan imidlertid være en rekke med mange verdier.

eksempel

Følgende regneark viser tre eksempler på funksjonen Median:

Tenk på at i de forrige eksemplene:

• Eksempelet i cellen B2 mottar et jevnt antall verdier og derfor beregnes medianen som gjennomsnittet av de to gjennomsnittsverdiene, 8 og 9;
• Eksempelet i cellen B3 inkluderer den tomme cellen A8. Denne cellen ignoreres ved beregning av medianen.

For mer informasjon om funksjonen MEDIAN av Excel, se Microsoft Office-nettstedet .

MODE

Funksjonen MODE av Excel returnerer MODE statistikk (den hyppigste verdien) av en liste over oppgitte tall. Hvis det er 2 eller flere gjentakende verdier i de oppgitte dataene, returnerer funksjonen den laveste verdien blant dem

syntaks

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

emner

er et sett med en eller flere numeriske verdier (eller matriser med numeriske verdier), som du vil beregne MODE statistikk.

Merk:

• I gjeldende versjoner av Excel (Excel 2007 og nyere) kan du levere opptil 255 numeriske argumenter til funksjonen MODE, men i Excel 2003 kan funksjonen bare akseptere opptil 30 numeriske argumenter.
• Tekst og logiske verdier innenfor en gitt rekke tall ignoreres av funksjonen Mode.

Funksjonseksempler MODE

Eksempel 1

Følgende regneark viser funksjonen MODE Excel, brukes til å beregne MODE statistikk over settet med verdier i cellene A1-A6.

Eksempel 2

Følgende regneark viser funksjonen MODE, brukes til å beregne MODE statistikk over settet med verdier i cellene A1-A10.

Merk at i dette tilfellet er det to mode i dataene.

I tilfellet ovenfor, hvor dataene i kolonne A i forrige regneark har to MODE statistikk (3 og 4), funksjonen MODE returnerer den laveste av disse to verdiene.

For ytterligere detaljer og eksempler på funksjonen MODE av Excel, se Microsoft Office-nettstedet .

MODE.SNGL

Funksjonen MODE.SNGL av Excel returnerer MODE statistikk (den hyppigste verdien) av en liste over oppgitte tall. Hvis det er 2 eller flere gjentakende verdier i de oppgitte dataene, returnerer funksjonen den laveste verdien blant dem.

Funksjonen Mode.Sngl er ny i Excel 2010 og derfor ikke tilgjengelig i tidligere versjoner av Excel. Imidlertid er funksjonen ganske enkelt en omdøpt versjon av funksjonen MODE tilgjengelig i tidligere versjoner av Excel.

syntaks

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

emner

er et sett med en eller flere numeriske verdier (eller matriser med numeriske verdier), som du vil beregne MODE.SNGL statistikk.

Funksjonseksempler MODE.SNGL

Eksempel 1

Følgende regneark viser funksjonen MODE.SNGL Excel, brukes til å beregne den statistiske MODUS for settet med verdier i cellene A1-A6.

Eksempel 2

Følgende regneark viser funksjonen MODE.SNGL, brukes til å beregne den statistiske modusen til settet med verdier i celler A1-A10.

Merk at i dette tilfellet er det to mode i dataene.

I tilfellet ovenfor, hvor dataene i kolonne A i forrige regneark har to MODE statistikk (3 og 4), funksjonen MODE.SNGL returnerer den laveste av disse to verdiene.

For ytterligere detaljer og eksempler på funksjonen MODE.SNGL av Excel, se Microsoft Office-nettstedet .

GEOMEAN

Det geometriske gjennomsnittet er et mål på gjennomsnittet som indikerer den typiske verdien av et sett med tall. Denne målingen kan bare brukes for positive verdier.

Det geometriske gjennomsnittet av et sett med verdier, y ₁ , Og ₂ , …, der _n det beregnes med formelen:

Merk at det geometriske gjennomsnittet alltid er mindre enn eller lik det aritmetiske gjennomsnittet.

Funksjonen Geomean Excel beregner det geometriske gjennomsnittet av et gitt sett med verdier.

syntaks

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

emner

en eller flere positive numeriske verdier (eller matriser med numeriske verdier), som du vil beregne det geometriske gjennomsnittet for.

I gjeldende versjoner av Excel (Excel 2007 og nyere) kan funksjonen godta opptil 255 numeriske argumenter, men i Excel 2003 kan funksjonen bare akseptere opptil 30 numeriske argumenter. Hvert argument kan imidlertid være en rekke verdier eller en rekke celler, som hver kan inneholde mange verdier.

eksempel

Cellen B1 av regnearket viser et enkelt eksempel på funksjonen geomean i Excel, brukes til å beregne det geometriske gjennomsnittet av verdiene i cellene A1-A5.

I dette eksemplet returnerer Geomean-funksjonen verdien 1.622671112 .

HARMEAN

Det harmoniske gjennomsnittet er et mål på gjennomsnittet beregnet som det resiproke av det aritmetiske gjennomsnittet av de resiproke. Dette kan kun beregnes for positive verdier.

Det harmoniske gjennomsnittet av et sett med verdier, y1, y2, ..., yn er derfor gitt av formelen:

det harmoniske gjennomsnittet er alltid mindre enn eller lik det geometriske gjennomsnittet og det geometriske gjennomsnittet er alltid mindre enn eller likt det aritmetiske gjennomsnittet.

Funksjonen Harmean Excel beregner det harmoniske gjennomsnittet av et gitt sett med verdier.

syntaks

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

emner

en eller flere positive numeriske verdier (eller matriser med numeriske verdier), som du vil beregne det harmoniske gjennomsnittet for.

I gjeldende versjoner av Excel (Excel 2007 og nyere) kan funksjonen godta opptil 255 numeriske argumenter, men i Excel 2003 kan funksjonen bare akseptere opptil 30 numeriske argumenter. Hvert argument kan imidlertid være en rekke verdier eller en rekke celler, som hver kan inneholde mange verdier.

eksempel

Celle B1 i regnearket til høyre viser et enkelt eksempel på funksjonen Harmean i Excel, brukes til å beregne det harmoniske gjennomsnittet av verdiene i cellene A1-A5.

I dette eksemplet er funksjonen Harmean returnerer verdien 1.229508197.

TRIMMEAN

Funksjonen TRIMMEAN (også kjent som trimmet gjennomsnitt) er et mål på gjennomsnittet som indikerer den sentrale tendensen til et sett med verdier.

Det trimmede gjennomsnittet beregnes ved å forkaste noen verdier ved enden av verdiområdet, før det aritmetiske gjennomsnittet av de gjenværende verdiene beregnes. Dette forhindrer at det beregnede gjennomsnittet blir forvrengt av ekstreme verdier (også kjent som uteliggere, teknisk sett outliers).

syntaks

= TRIMMEAN( array, percent )

emner

• matrise – En rekke numeriske verdier som du vil beregne det avkortede gjennomsnittet for.
• prosent – Prosentandelen av verdier du vil slette fraarray sørget for.

Merk at den angitte prosentverdien er den totale prosentandelen av verdier som skal ekskluderes fra beregningen. Denne prosentandelen er delt på to for å få antall verdier fjernet fra hver ende av området.

Det bør også bemerkes at når Excel beregner hvor mange verdier som er slettet fraarray av oppgitte verdier rundes den beregnede prosentandelen ned til nærmeste multiplum av 2. For eksempel, hvis du ønsker å beregne det trimmede gjennomsnittet av a array av 10 verdier, derfor:

• En prosentandel på 15 % tilsvarer 1,5 verdier, som vil bli rundet ned til 0 (dvs. ingen verdier vil bli forkastet fraarray før du beregner gjennomsnittet);
• En prosentandel på 20 % tilsvarer 2 verdier, så 1 verdi vil bli forkastet fra hver ende av området før gjennomsnittet av de gjenværende verdiene tas;
• En prosentandel på 25 % tilsvarer 2,5 verdier, som vil bli rundet ned til 2 (det vil si at 1 verdi vil bli forkastet fra hver ende av området før gjennomsnittet av de gjenværende verdiene).

eksempel

Cellene B1-B3 i regnearket nedenfor viser 3 eksempler på funksjonen trimmean i Excel, alt brukt til å beregne det trimmede gjennomsnittet av verdiene i cellene A1-A10, for forskjellige prosentverdier.

Husk det, i cellen B1 av regnearket ovenfor er prosentargumentet som er gitt 15 %. Siden iarray forutsatt at det er 10 verdier, er antall verdier å ignorere 1,5 rundet ned til nærmeste multiplum av 2 som er null.

Funksjoner for beregning av permutasjoner

PERMUT

Antall permutasjoner for et gitt antall objekter er antall kombinasjoner i en mulig rekkefølge.

Permutasjoner skiller seg fra kombinasjoner ved at for en permutasjon har rekkefølgen på objektene betydning, men i en kombinasjon spiller rekkefølgen ingen rolle.

Antall mulige permutasjoner er gitt av formelen:

due k er antall valgte objekter e n er antall mulige objekter.

Excel-funksjonen Permut beregner antall permutasjoner av et spesifisert antall objekter fra et sett med objekter.

syntaks

= PERMUT( number, number_chosen )

emner

• number: Totalt antall tilgjengelige varer
• number_chosen: Antall objekter i hver permutasjon (dvs. antall objekter valgt fra settet)

Merk at hvis noen av argumentene er gitt som desimalverdier, vil de bli avkortet til heltall av funksjonen Permut.

eksempel

I det følgende regnearket, Excel Permut brukes til å beregne antall permutasjoner av seks objekter, valgt fra sett med forskjellige størrelser:

PERMUTATIONA

Excel-funksjoner Utveksling og Permutationa beregner begge antall permutasjoner av et utvalg objekter fra et sett.

Imidlertid er de to funksjonene forskjellige ved at Permut.funksjon teller ikke repetisjoner mens Permutationa-funksjonen teller repetisjoner.

For eksempel, i et sett med 3 objekter, a , b , c , hvor mange permutasjoner er det av 2 objekter?

• La Permut.funksjon returnerer resultatet 6 (permutasjoner: ab , ac , ba , bc , ca , cb );
• Permutationa-funksjonen returnerer resultatet 9 (permutasjoner: aa , ab , ac , ba , bb , bc , ca , cb , cc ).

Excel-funksjonen Permutationa beregner antall permutasjoner av et spesifisert antall objekter fra et sett med objekter.

syntaks

= PERMUTATIONA( number, number_chosen )

emner

• number: Totalt antall objekter i settet (må være ≥ 0).
• number_chosen: Antall objekter valgt fra settet (må være ≥ 0).

Merk at hvis noen av argumentene er gitt som desimalverdier, vil de bli avkortet til heltall av funksjonen PERMUTATIONA.

eksempel

I det følgende regnearket, Excel PERMUTATIONA brukes til å beregne antall permutasjoner av seks objekter, valgt fra sett med forskjellige størrelser:

Funksjoner for beregning av konfidensintervaller

CONFIDENCE

I Excel 2010 er funksjonen CONFIDENCE er erstattet av funksjonen Confidence.Norm.

Selv om den har blitt erstattet, har gjeldende versjoner av Excel fortsatt funksjonen Confidence (lagret i listen over kompatibilitetsfunksjoner), for å tillate kompatibilitet med tidligere versjoner av Excel.

Men funksjonen Confidence er kanskje ikke tilgjengelig i fremtidige versjoner av Excel, så vi anbefaler å bruke funksjonen Confidence.Norm, hvis mulig.

Funksjonen Confidence Excel bruker en normalfordeling for å beregne en konfidensverdi som kan brukes til å konstruere konfidensintervallet for et populasjonsgjennomsnitt, en gitt sannsynlighet og en utvalgsstørrelse. Det antas at populasjonsstandardavviket er kjent.

syntaks

= CONFIDENCE( alpha, standard_dev, size )

emner

• alfa: Signifikansnivået (= 1 – konfidensnivå). (For eksempel, et signifikansnivå på 0,05 tilsvarer et 95 % konfidensnivå).
• standard_dev: Populasjonens standardavvik.
• size: Størrelsen på populasjonsutvalget.

For å beregne konfidensintervallet for et populasjonsmiddel, må den returnerte konfidensverdien legges til og trekkes fra utvalgets gjennomsnitt. Betyr hva. for prøvens gjennomsnitt x:

Confidence Interval =   x   ±   CONFIDENCE

eksempel

I regnearket nedenfor brukes Excel-konfidensfunksjonen til å beregne konfidensintervallet med en signifikans på 0,05 (dvs. et 95 % konfidensnivå), for gjennomsnittet av et utvalg på 100 menns høyder. Prøvegjennomsnittet er 1,8 meter og standardavviket er 0,07 meter.

Den forrige funksjonen returnerer en konfidensverdi på 0,013719748

Derfor er konfidensintervallet 1,8 ± 0,013719748, som tilsvarer området mellom 1,786280252 og 1,813719748

CONFIDENCE.NORM

I statistikk er konfidensintervallet området som en populasjonsparameter sannsynligvis faller innenfor, for en gitt sannsynlighet.

For eksempel. For en gitt populasjon og en 95 % sannsynlighet, er konfidensintervallet området der det er 95 % sannsynlig at en populasjonsparameter faller.

Merk at nøyaktigheten av konfidensintervallet avhenger av om populasjonen har normalfordeling.

Funksjonen Confidence.Norm Excel bruker en normalfordeling for å beregne en konfidensverdi som kan brukes til å konstruere konfidensintervallet for et populasjonsgjennomsnitt, en gitt sannsynlighet og en utvalgsstørrelse. Det antas at populasjonsstandardavviket er kjent.

syntaks

= CONFIDENCE.NORM( alpha, standard_dev, size )

emner

• alfa: Signifikansnivået (= 1 – konfidensnivå). (For eksempel, et signifikansnivå på 0,05 tilsvarer et 95 % konfidensnivå).
• standard_dev: Populasjonens standardavvik.
• size: Størrelsen på populasjonsutvalget.

For å beregne konfidensintervallet for et populasjonsmiddel, må den returnerte konfidensverdien legges til og trekkes fra utvalgets gjennomsnitt. Betyr hva. for prøvens gjennomsnitt x:

Confidence Interval =   x   ±   CONFIDENCE

eksempel

I regnearket nedenfor brukes Excel-konfidensfunksjonen til å beregne konfidensintervallet med en signifikans på 0,05 (dvs. et 95 % konfidensnivå), for gjennomsnittet av et utvalg på 100 menns høyder. Prøvegjennomsnittet er 1,8 meter og standardavviket er 0,07 meter.

Den forrige funksjonen returnerer en konfidensverdi på 0,013719748

Derfor er konfidensintervallet 1,8 ± 0,013719748, som tilsvarer området mellom 1,786280252 og 1,813719748

Ercole Palmeri