Excel statistiske funksjoner: Opplæring med eksempler, del tre

Excel tilbyr et bredt spekter av statistiske funksjoner som utfører beregninger fra gjennomsnittet til mer komplekse statistiske distribusjons- og trendlinjefunksjoner.

I denne artikkelen skal vi gå dypere inn i Excels statistiske funksjoner for beregning av trendlinjefunksjoner.

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

Beregnet lesetid: 12 minutter

Trendlinjefunksjoner

Forecast

Excels prognosefunksjon forutsier et fremtidig punkt på en lineær trendlinjetilpasning til et gitt sett med x- og y-verdier.

syntaks

= FORECAST( x, known_y's, known_x's )

fag

• x: En numerisk x-verdi som du vil forutsi en ny y-verdi for.
• known_y's: En rekke kjente y-verdier
• known_x's: En matrise med kjente x-verdier

Legg merke til at lengden på matrisen av known_x må være den samme som known_y og variansen av known_x det trenger ikke være null.

eksempel

I det følgende regnearket, funksjonen FORECAST Excel brukes til å forutsi et ekstra punkt langs den best passende rette linjen gjennom en rekke kjente x- og y-verdier (lagret i cellene F2:F7 og G2:G7).

Som vist i celle F7 i regnearket, er funksjonen for å beregne forventet y-verdi ved x=7 :=FORECAST( 7, G2:G7, F2:F7 )

Dette gir resultatet 32.666667 .

Intercept

Blant Excels prognosefunksjoner finner vi Intercept. Excels Intercept-funksjon beregner skjæringspunktet (verdien ved skjæringspunktet mellom y-aksen) av den lineære regresjonslinjen over et gitt sett med x- og y-verdier.

syntaks

= INTERCEPT( known_y's, known_x's )

fag

• known_y's: En rekke kjente y-verdier
• known_x's: En matrise med kjente x-verdier

Legg merke til at lengden på matrisen av known_x må være den samme som known_y og variansen av known_x det trenger ikke være null.

eksempel

Følgende regneark viser et eksempel på funksjonen Intercept av Excel brukes til å beregne punktet der den lineære regresjonslinjen gjennom known_x og known_y (oppført i cellene F2:F7 og G2:G7) skjærer y-aksen.

Le known_x og known_y er plottet på grafen i regnearket.

Som vist i celle F9 i regnearket, er formelen for Intercept-funksjonen :=INTERCEPT( G2:G7, F2:F7 )

som gir resultatet 2.4 .

Slope

En annen veldig interessant prediksjonsfunksjon er Slope (Slope) Excel beregner helningen til den lineære regresjonslinjen gjennom et gitt sett med x- og y-verdier.

Syntaksen til funksjonen er:

syntaks

= SLOPE( known_y's, known_x's )

fag

• known_y's: En rekke kjente y-verdier
• known_x's: En matrise med kjente x-verdier

Legg merke til at lengden på matrisen av known_x må være den samme som known_y og variansen av known_x det trenger ikke være null.

eksempel

Følgende regneark viser et eksempel på funksjonen Slope (helling) av Excel brukes til å beregne helningen til den lineære regresjonslinjen gjennom known_x og known_y, i cellene F2:F7 og G2:G7.

Le known_x og known_y er plottet på grafen i regnearket.

Som vist i celle F9 i regnearket, er formelen for Intercept-funksjonen :=SLOPE( G2:G7, F2:F7 )

som gir resultatet 4.628571429.

Trend

En veldig interessant Excel-prognosefunksjon er TREND Excel (trend) beregner den lineære trendlinjen gjennom et gitt sett med y-verdier og (valgfritt), et gitt sett med x-verdier.

Funksjonen utvider deretter den lineære trendlinjen for å beregne ytterligere y-verdier for et ekstra sett med nye x-verdier.

Syntaksen til funksjonen er:

syntaks

= TREND( known_y's, [known_x's], [new_x's], [const] )

fag

• known_y's: En rekke kjente y-verdier
• [known_x's]: En eller flere matriser med kjente x-verdier. Dette er et valgfritt argument som, hvis det leveres, bør ha samme lengde som settet med known_y's. Hvis utelatt, settet med [known_x's] får verdien {1, 2, 3, …}.
• [nye_x'er]: Et valgfritt argument som gir en eller flere arrayer med numeriske verdier som representerer et sett med nye x-verdier, som du vil beregne de tilsvarende nye y-verdiene for. Hver rekke av [nye_x'er] skal samsvare med en rekke av [known_x's]. Hvis argumentet [nye_x'er] er utelatt, den settes lik [known_x's] .
• [koste]: Et valgfritt logisk argument som spesifiserer om konstanten 'b', i den lineære ligningen y = m x + b , må tvinges til å være lik null. Selv [koste] er TRUE (eller hvis dette argumentet er utelatt) behandles konstanten b normalt;
• Selv [koste] er FALSE konstanten b settes til 0 og den rette linjeligningen blir y = mx .

eksempel

I det følgende regnearket brukes Excel Trend-funksjonen til å utvide en serie med x- og y-verdier som ligger på den rette linjen y = 2x + 10. De kjente x- og y-verdiene er lagret i cellene A2-B5 av regnearket og vises også i regnearkgrafen.

Merk at det ikke er avgjørende at punktene som er gitt passer nøyaktig langs den rette linjen y = 2x + 10 (selv om de gjør det i dette eksemplet). Excels Trend-funksjon vil finne den best passende linjen for ethvert sett med verdier du oppgir.

Trendfunksjonen bruker minste kvadraters metode for å finne den beste tilpasningslinjen og bruker den deretter til å beregne de nye y-verdiene for de nye x-verdiene som er oppgitt.

I dette eksemplet er verdiene til [nye_x'er] er lagret i cellene A8-A10, og Excels Trend-funksjon har blitt brukt, i cellene B8-B10, for å finne de nye tilsvarende y-verdiene. Som vist i formellinjen er formelen := TREND( B2:B5, A2:A5, A8:A10 )

Du ser at trendfunksjonen i formellinjen er omsluttet av klammeparenteser { }. Dette indikerer at funksjonen ble lagt inn som matriseformel .

Growth

Blant Excels prognosefunksjoner finner vi Growth. Funksjonen Growth Excel beregner den eksponentielle vekstkurven gjennom et gitt sett med y-verdier og (valgfritt), ett eller flere sett med x-verdier. Funksjonen utvider deretter kurven for å beregne ytterligere y-verdier for et ekstra sett med nye x-verdier.

Syntaksen til funksjonen er:

syntaks

= GROWTH( known_y's, [known_x's], [new_x's], [const] )

fag

• known_y's: En rekke kjente y-verdier
• [known_x's]: En eller flere matriser med kjente x-verdier. Dette er et valgfritt argument som, hvis det leveres, bør ha samme lengde som settet med known_y's. Hvis utelatt, settet med [known_x's] får verdien {1, 2, 3, …}.
• [nye_x'er]: Et sett med nye x-verdier, som funksjonen beregner de tilsvarende nye y-verdiene for. Hvis det utelates, antas det at settet med [nye_x'er] er lik den til [known_x's] og funksjonen returnerer y-verdiene som ligger på den beregnede eksponentielle vekstkurven.
• [koste]: Et valgfritt logisk argument som spesifiserer om konstanten 'b', i den lineære ligningen y = b * m^x , må tvinges til å være lik 1. Hvis [koste] er TRUE (eller hvis dette argumentet er utelatt) behandles konstanten b normalt; Selv [koste] er FALSE konstanten b settes til 1 og den rette linjeligningen blir y = mx .

eksempel

I det følgende regnearket brukes Excel-vekstfunksjonen til å utvide en serie med x- og y-verdier som ligger på den eksponentielle vekstkurven y = 5 * 2^x. Disse er lagret i cellene A2-B5 i regnearket og vises også i regnearkdiagrammet.

Vekstfunksjonen beregner den eksponentielle vekstkurven som best passer til de kjente x- og y-verdiene som er oppgitt. I dette enkle eksemplet er den best passende kurven den eksponentielle kurven y = 5 * 2^x.

Når Excel beregner ligningen for eksponentiell vekstkurve, kan den bruke den til å beregne de nye y-verdiene for de nye x-verdiene gitt i cellene A8-A10.

I dette eksemplet er verdiene til [new_x's] er lagret i cellene A8-A10 og funksjonen Growth av Excel er satt inn i cellene B8-B10. Som vist i formellinjen, er formelen for dette:=Growth( B2:B5, A2:A5, A8:A10 )

Du kan se at Growth-funksjonen i formellinjen er omsluttet av klammeparenteser { }. Dette indikerer at funksjonen ble lagt inn som matriseformel .

Merk at selv om punktene i eksemplet ovenfor passer nøyaktig langs kurven y = 5 * 2^x, er dette ikke avgjørende. Funksjonen Growth Excel vil finne den kurven som passer best for ethvert sett med verdier du oppgir.

Økonomiske funksjoner

Effekt

Funksjonen Effect Excel returnerer den effektive årlige renten for en gitt nominell rente og et gitt antall sammensatte perioder per år.

Effektiv årlig rente

Den effektive årlige renten er et mål på rente som inkluderer rentekapitalisering og brukes ofte til å sammenligne finansielle lån med forskjellige kapitaliseringsbetingelser.

Den effektive årlige renten beregnes ved å bruke følgende ligning:

due nominal_rate er den nominelle renten e npery er antall sammensatte perioder per år.

Syntaksen til funksjonen er:

syntaks

= EFFECT( nominal_rate, npery )

fag

• nominal_rate: Den nominelle renten (må være en numerisk verdi mellom 0 og 1)
• npery: Antall sammensetningsperioder per år (må være et positivt heltall).

eksempel

Følgende regneark viser tre eksempler på Excel-effektfunksjonen:

Hvis resultatet av funksjonen Effect vises som desimal eller viser 0 %, begge disse problemene skyldes sannsynligvis formateringen av cellen som inneholder funksjonen Effect.

Derfor kan problemet løses ved å formatere cellen i prosent, med desimaler.

Å gjøre dette:

1. Velg cellene som skal formateres i prosent.
2. Åpne dialogboksen "Formater celler" ved å bruke en av følgende metoder:
   • Høyreklikk på den valgte cellen eller området og velg alternativet Formater celler... fra kontekstmenyen;
   • Klikk på dialogboksstarteren i nummergrupperingen på fanen Hjemprodukt Excel-bånd;
   • Bruk hurtigtasten CTRL-1 (dvs. velg CTRL-tasten og hold den nede og velg "1" (én)-tasten).
3. I dialogboksen "Formater celler":
   • Pass på at kortet Antall øverst i dialogboksen er valgt.
   • velg Prosentdel fra listen Kategori på venstre side av dialogboksen .Dette vil få opp flere alternativer på høyre side av avmerkingsboksen, slik at du kan velge antall desimaler du ønsker skal vises.
   • Når du har valgt antall desimaler du vil vise, klikker du OK .

Nominal

Funksjonen Nominal Excel returnerer den nominelle renten for en gitt effektiv rente og et gitt antall sammensetningsperioder per år.

Syntaksen til funksjonen er:

syntaks

= NOMINAL( effect_rate, npery )

fag

• effect_rate: Den effektive renten (en numerisk verdi mellom 0 og 1).
• npery: Antall sammensetningsperioder per år (må være et positivt heltall).

eksempel

I det følgende regnearket, funksjonen Nominal av Excel brukes til å beregne nominell rente på tre lån med ulike vilkår.

Ercole Palmeri