Excel statistiska funktioner: Handledning med exempel, del tre

Excel tillhandahåller ett brett utbud av statistiska funktioner som utför beräkningar från medelvärdet till mer komplexa statistiska fördelnings- och trendlinjefunktioner.

I den här artikeln kommer vi att fördjupa oss i Excels statistiska funktioner för att beräkna trendlinjefunktioner.

Observera att vissa statistiska funktioner introducerades i nyare versioner av Excel och är därför inte tillgängliga i äldre versioner.

Beräknad lästid: 12 minuter

Trendlinjefunktioner

Forecast

Excels prognosfunktion förutsäger en framtida punkt på en linjär trendlinje som passar en given uppsättning x- och y-värden.

syntax

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

ämnen

• x: Ett numeriskt x-värde för vilket du vill förutsäga ett nytt y-värde.
• known_y's: En matris med kända y-värden
• known_x's: En matris med kända x-värden

Observera att längden på arrayen av known_x måste vara samma som known_y och variansen av known_x det behöver inte vara noll.

exempel

I följande kalkylblad visas funktionen FORECAST Excel används för att förutsäga ytterligare en punkt längs den bäst passande räta linjen genom en serie kända x- och y-värden (lagrade i cellerna F2:F7 och G2:G7).

Som visas i cell F7 i kalkylarket är funktionen för att beräkna det förväntade y-värdet vid x=7 :=FORECAST( 7, G2:G7, F2:F7 )

Detta ger resultatet 32.666667 .

Intercept

Bland Excels prognosfunktioner hittar vi Intercept. Excels Intercept-funktion beräknar skärningen (värdet vid skärningspunkten mellan y-axeln) för den linjära regressionslinjen över en given uppsättning x- och y-värden.

syntax

= INTERCEPT( known_y's, known_x's )

ämnen

• known_y's: En matris med kända y-värden
• known_x's: En matris med kända x-värden

Observera att längden på arrayen av known_x måste vara samma som known_y och variansen av known_x det behöver inte vara noll.

exempel

Följande kalkylblad visar ett exempel på funktionen Intercept av Excel används för att beräkna punkten där den linjära regressionslinjen genom known_x och known_y (listad i cellerna F2:F7 och G2:G7) skär y-axeln.

Le known_x och known_y plottas på grafen i kalkylbladet.

Som visas i cell F9 i kalkylarket är formeln för Intercept-funktionen :=INTERCEPT( G2:G7, F2:F7 )

vilket ger resultatet 2.4 .

Slope

En annan mycket intressant förutsägelsefunktion är lutningen (Slope) Excel beräknar lutningen för den linjära regressionslinjen genom en given uppsättning x- och y-värden.

Syntaxen för funktionen är:

syntax

= SLOPE( known_y's, known_x's )

ämnen

• known_y's: En matris med kända y-värden
• known_x's: En matris med kända x-värden

Observera att längden på arrayen av known_x måste vara samma som known_y och variansen av known_x det behöver inte vara noll.

exempel

Följande kalkylblad visar ett exempel på funktionen Slope (lutning) i Excel används för att beräkna lutningen för den linjära regressionslinjen genom known_x och den known_y, i cellerna F2:F7 och G2:G7.

Le known_x och known_y plottas på grafen i kalkylbladet.

Som visas i cell F9 i kalkylarket är formeln för Intercept-funktionen :=SLOPE( G2:G7, F2:F7 )

vilket ger resultatet 4.628571429.

Trend

En mycket intressant Excel-prognosfunktion är TREND Excel (trend) beräknar den linjära trendlinjen genom en given uppsättning y-värden och (valfritt), en given uppsättning x-värden.

Funktionen utökar sedan den linjära trendlinjen för att beräkna ytterligare y-värden för ytterligare en uppsättning nya x-värden.

Syntaxen för funktionen är:

syntax

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

ämnen

• known_y's: En matris med kända y-värden
• [known_x's]: En eller flera arrayer med kända x-värden. Detta är ett valfritt argument som, om det tillhandahålls, bör ha samma längd som uppsättningen av known_y's. Om den utelämnas, uppsättningen av [known_x's] får värdet {1, 2, 3, …}.
• [nya_x]: Ett valfritt argument som tillhandahåller en eller flera matriser med numeriska värden som representerar en uppsättning nya x-värden, för vilka du vill beräkna motsvarande nya y-värden. Varje array av [nya_x's] bör matcha en array av [known_x's]. Om argumentet [nya_x's] utelämnas, den är satt till lika med [known_x's] .
• [kosta]: Ett valfritt logiskt argument som anger om konstanten 'b', i den linjära ekvationen y = m x + b , måste tvingas vara lika med noll. själv [kosta] är TRUE (eller om detta argument utelämnas) behandlas konstanten b normalt;
• själv [kosta] är FALSK konstanten b sätts till 0 och den räta linjeekvationen blir y = mx .

exempel

I följande kalkylblad används Excel Trend-funktionen för att utöka en serie av x- och y-värden som ligger på den räta linjen y = 2x + 10. De kända x- och y-värdena lagras i cellerna A2-B5 i kalkylarket och visas också i kalkylarksdiagrammet.

Observera att det inte är nödvändigt att de angivna punkterna passar exakt längs den räta linjen y = 2x + 10 (även om de gör det i det här exemplet). Excels trendfunktion hittar den bästa linjen för alla värden du tillhandahåller.

Trendfunktionen använder minsta kvadratmetoden för att hitta den bästa passformen och använder den sedan för att beräkna de nya y-värdena för de nya x-värdena.

I det här exemplet är värdena för [nya_x's] lagras i cellerna A8-A10, och Excels trendfunktion har använts, i cellerna B8-B10, för att hitta de nya motsvarande y-värdena. Som visas i formelfältet är formeln := TREND( B2:B5, A2:A5, A8:A10 )

Du ser att trendfunktionen i formelfältet är omgiven av klammerparenteser { }. Detta indikerar att funktionen angavs som matrisformel .

Growth

Bland Excels prognosfunktioner hittar vi Growth. Funktionen Growth Excel beräknar den exponentiella tillväxtkurvan genom en given uppsättning av y-värden och (valfritt), en eller flera uppsättningar av x-värden. Funktionen utökar sedan kurvan för att beräkna ytterligare y-värden för ytterligare en uppsättning nya x-värden.

Syntaxen för funktionen är:

syntax

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

ämnen

• known_y's: En matris med kända y-värden
• [known_x's]: En eller flera arrayer med kända x-värden. Detta är ett valfritt argument som, om det tillhandahålls, bör ha samma längd som uppsättningen av known_y's. Om den utelämnas, uppsättningen av [known_x's] får värdet {1, 2, 3, …}.
• [nya_x]: En uppsättning nya x-värden, för vilka funktionen beräknar motsvarande nya y-värden. Om det utelämnas, antas det att uppsättningen av [nya_x's] är lika med den för [known_x's] och funktionen returnerar y-värdena som ligger på den beräknade exponentiella tillväxtkurvan.
• [kosta]: Ett valfritt logiskt argument som anger om konstanten 'b', i den linjära ekvationen y = b * m^x , måste tvingas vara lika med 1. Om [kosta] är TRUE (eller om detta argument utelämnas) behandlas konstanten b normalt; själv [kosta] är FALSK konstanten b sätts till 1 och den räta linjeekvationen blir y = mx .

exempel

I följande kalkylblad används Excel-tillväxtfunktionen för att utöka en serie av x- och y-värden som ligger på den exponentiella tillväxtkurvan y = 5 * 2^x. Dessa lagras i cellerna A2-B5 i kalkylarket och visas även i kalkylbladet.

Tillväxtfunktionen beräknar den exponentiella tillväxtkurvan som bäst passar de kända x- och y-värdena som tillhandahålls. I detta enkla exempel är den bäst passande kurvan den exponentiella kurvan y = 5 * 2^x.

När Excel väl har beräknat ekvationen för exponentiell tillväxtkurva kan den använda den för att beräkna de nya y-värdena för de nya x-värdena som finns i cellerna A8-A10.

I det här exemplet är värdena för [new_x's] lagras i cellerna A8-A10 och funktionen Growth av Excel har infogats i cellerna B8-B10. Som visas i formelfältet är formeln för detta:=Growth( B2:B5, A2:A5, A8:A10 )

Du kan se att tillväxtfunktionen i formelfältet är omgiven av klammerparenteser { }. Detta indikerar att funktionen angavs som matrisformel .

Observera att även om punkterna i exemplet ovan passar exakt längs kurvan y = 5 * 2^x, är detta inte nödvändigt. Funktionen Growth Excel hittar den kurva som passar bäst för alla värden du tillhandahåller.

Finansiella funktioner

Effekt

Funktionen Effect Excel returnerar den effektiva årsräntan för en given nominell ränta och ett givet antal sammansättningsperioder per år.

Effektiv årlig ränta

Den effektiva årsräntan är ett räntemått som inkluderar räntekapitalisering och används ofta för att jämföra finansiella lån med olika kapitaliseringsvillkor.

Den effektiva årliga räntan beräknas med följande ekvation:

duva nominal_rate är den nominella räntan e npery är antalet sammansättningsperioder per år.

Syntaxen för funktionen är:

syntax

= EFFECT( nominal_rate, npery )

ämnen

• nominal_rate: Den nominella räntan (måste vara ett numeriskt värde mellan 0 och 1)
• npery: Antalet sammansättningsperioder per år (måste vara ett positivt heltal).

exempel

Följande kalkylblad visar tre exempel på Excel-effektfunktionen:

Om resultatet av funktionen Effect visas som decimal eller visar 0 %, båda dessa problem beror troligen på formateringen av cellen som innehåller funktionen Effect.

Därför kan problemet lösas genom att formatera cellen i procent, med decimaler.

Att göra detta:

1. Markera cellerna som ska formateras i procent.
2. Öppna dialogrutan "Formatera celler" med någon av följande metoder:
   • Högerklicka på den markerade cellen eller området och välj alternativet Formatera celler... från snabbmenyn;
   • Klicka på dialogrutan Launcher i nummergrupperingen på fliken Hem Excel-band;
   • Använd kortkommandot CTRL-1 (dvs. välj CTRL-tangenten och håll den nedtryckt och välj tangenten "1" (ett)).
3. I dialogrutan "Formatera celler":
   • Se till att kortet nummer överst i dialogrutan är markerad.
   • Välj Procentsats från listan Kategori på vänster sida av dialogrutan .Detta kommer att visa ytterligare alternativ till höger om kryssrutan, så att du kan välja antalet decimaler som du vill ska visas.
   • När du har valt antalet decimaler du vill visa klickar du på OK .

Nominal

Funktionen Nominal Excel returnerar den nominella räntan för en given effektiv ränta och ett givet antal sammansättningsperioder per år.

Syntaxen för funktionen är:

syntax

= NOMINAL( effect_rate, npery )

ämnen

• effect_rate: Den effektiva räntan (ett numeriskt värde mellan 0 och 1).
• npery: Antalet sammansättningsperioder per år (måste vara ett positivt heltal).

exempel

I följande kalkylblad visas funktionen Nominal of Excel används för att beräkna den nominella räntan på tre lån med olika löptider.

Ercole Palmeri