Statistische functies van Excel: zelfstudie met voorbeelden, deel drie

Excel biedt een breed scala aan statistische functies die berekeningen uitvoeren van het gemiddelde tot complexere statistische verdelings- en trendlijnfuncties.

In dit artikel gaan we dieper in op de statistische functies van Excel voor het berekenen van trendlijnfuncties.

Houd er rekening mee dat sommige statistische functies in recente versies van Excel zijn geïntroduceerd en daarom niet beschikbaar zijn in oudere versies.

Geschatte leestijd: 12 minuti

Trendlijn-functies

Forecast

De voorspellingsfunctie van Excel voorspelt een toekomstig punt op een lineaire trendlijn die past bij een gegeven reeks x- en y-waarden.

syntaxis

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

onderwerpen

• x: Een numerieke x-waarde waarvoor u een nieuwe y-waarde wilt voorspellen.
• known_y's: Een array met bekende y-waarden
• known_x's: Een array met bekende x-waarden

Merk op dat de lengte van de array van known_x moet dezelfde zijn als die van known_y en de variantie van known_x het hoeft niet nul te zijn.

Voorbeeld

In het volgende spreadsheet is de functie FORECAST Excel wordt gebruikt om een ​​extra punt langs de best passende rechte lijn te voorspellen via een reeks bekende x- en y-waarden (opgeslagen in de cellen F2:F7 en G2:G7).

Zoals weergegeven in cel F7 van het werkblad, is de functie voor het berekenen van de verwachte y-waarde bij x=7: :=FORECAST( 7, G2:G7, F2:F7 )

Dit geeft het resultaat 32.666667 .

Intercept

Onder de voorspellingsfuncties van Excel vinden we de Intercept. De Intercept-functie van Excel berekent het snijpunt (de waarde op het snijpunt van de y-as) van de lineaire regressielijn over een gegeven reeks x- en y-waarden.

syntaxis

= INTERCEPT( known_y's, known_x's )

onderwerpen

• known_y's: Een array met bekende y-waarden
• known_x's: Een array met bekende x-waarden

Merk op dat de lengte van de array van known_x moet dezelfde zijn als die van known_y en de variantie van known_x het hoeft niet nul te zijn.

Voorbeeld

Het volgende spreadsheet toont een voorbeeld van de functie Intercept van Excel gebruikt om het punt te berekenen waar de lineaire regressielijn door de known_x en known_y (vermeld in de cellen F2:F7 en G2:G7) snijdt de y-as.

Le known_x en known_y worden uitgezet in de grafiek in het spreadsheet.

Zoals weergegeven in cel F9 van het werkblad is de formule voor de Intercept-functie: :=INTERCEPT( G2:G7, F2:F7 )

wat het resultaat geeft 2.4 .

Slope

Een andere zeer interessante voorspellingsfunctie is de Slope (Slope) Excel berekent de helling van de lineaire regressielijn via een gegeven reeks x- en y-waarden.

De syntaxis van de functie is:

syntaxis

= SLOPE( known_y's, known_x's )

onderwerpen

• known_y's: Een array met bekende y-waarden
• known_x's: Een array met bekende x-waarden

Merk op dat de lengte van de array van known_x moet dezelfde zijn als die van known_y en de variantie van known_x het hoeft niet nul te zijn.

Voorbeeld

Het volgende spreadsheet toont een voorbeeld van de functie Slope (helling) van Excel gebruikt om de helling van de lineaire regressielijn door de te berekenen known_x en de known_y, in de cellen F2:F7 en G2:G7.

Le known_x en known_y worden uitgezet in de grafiek in het spreadsheet.

Zoals weergegeven in cel F9 van het werkblad is de formule voor de Intercept-functie: :=SLOPE( G2:G7, F2:F7 )

wat het resultaat geeft 4.628571429.

Trend

Een zeer interessante Excel-voorspellingsfunctie is de TREND Excel (Trend) berekent de lineaire trendlijn via een gegeven set y-waarden en (optioneel) een gegeven set x-waarden.

De functie breidt vervolgens de lineaire trendlijn uit om aanvullende y-waarden te berekenen voor een extra set nieuwe x-waarden.

De syntaxis van de functie is:

syntaxis

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

onderwerpen

• known_y's: Een array met bekende y-waarden
• [known_x's]: Een of meer arrays met bekende x-waarden. Dit is een optioneel argument dat, indien opgegeven, dezelfde lengte moet hebben als de set van known_y's. Indien weggelaten, wordt de set van [known_x's] krijgt de waarde {1, 2, 3, …}.
• [nieuwe_x's]: Een optioneel argument dat een of meer arrays van numerieke waarden levert die een reeks nieuwe x-waarden vertegenwoordigen, waarvoor u de overeenkomstige nieuwe y-waarden wilt berekenen. Elke array van [nieuwe_x's] moet overeenkomen met een array van [known_x's]. Als het argument [nieuwe_x's] wordt weggelaten, het wordt gelijkgesteld aan [known_x's] .
• [kosten]: Een optioneel logisch argument dat specificeert of de constante 'b' in de lineaire vergelijking aanwezig is y = m x + b , moet gedwongen worden gelijk te zijn aan nul. Zelf [kosten] WAAR is (of als dit argument wordt weggelaten), wordt de constante b normaal behandeld;
• Zelf [kosten] ONWAAR is, wordt de constante b op 0 gezet en wordt de vergelijking in rechte lijn y = MX .

Voorbeeld

In het volgende spreadsheet wordt de Excel Trend-functie gebruikt om een ​​reeks x- en y-waarden uit te breiden die op de rechte lijn y = 2x + 10 liggen. De bekende x- en y-waarden worden opgeslagen in de cellen A2-B5 van het spreadsheet en worden ook weergegeven in de spreadsheetgrafiek.

Merk op dat het niet essentieel is dat de gegeven punten precies langs de rechte lijn y = 2x + 10 passen (hoewel dat in dit voorbeeld wel het geval is). De Trend-functie van Excel vindt de best passende lijn voor elke reeks waarden die u opgeeft.

De Trend-functie gebruikt de kleinste kwadratenmethode om de best passende lijn te vinden en gebruikt deze vervolgens om de nieuwe y-waarden voor de nieuwe x-waarden te berekenen.

In dit voorbeeld zijn de waarden van [nieuwe_x's] worden opgeslagen in de cellen A8-A10, en de Trend-functie van Excel is in de cellen B8-B10 gebruikt om de nieuwe overeenkomstige y-waarden te vinden. Zoals weergegeven in de formulebalk is de formule: := TREND( B2:B5, A2:A5, A8:A10 )

U ziet dat de functie Trend in de formulebalk tussen accolades { } staat. Dit geeft aan dat de functie is ingevoerd als matrixformule .

Growth

Onder de voorspellingsfuncties van Excel vinden we de Growth. De functie Growth Excel berekent de exponentiële groeicurve via een gegeven set y-waarden en (optioneel) een of meer sets x-waarden. De functie breidt vervolgens de curve uit om extra y-waarden te berekenen voor een extra set nieuwe x-waarden.

De syntaxis van de functie is:

syntaxis

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

onderwerpen

• known_y's: Een array met bekende y-waarden
• [known_x's]: Een of meer arrays met bekende x-waarden. Dit is een optioneel argument dat, indien opgegeven, dezelfde lengte moet hebben als de set van known_y's. Indien weggelaten, wordt de set van [known_x's] krijgt de waarde {1, 2, 3, …}.
• [nieuwe_x's]: een reeks nieuwe x-waarden, waarvoor de functie de overeenkomstige nieuwe y-waarden berekent. Als dit wordt weggelaten, wordt aangenomen dat de verzameling van [nieuwe_x's] is gelijk aan die van [known_x's] en de functie retourneert de y-waarden die op de berekende exponentiële groeicurve liggen.
• [kosten]: Een optioneel logisch argument dat specificeert of de constante 'b' in de lineaire vergelijking aanwezig is y = b * m^x , moet gedwongen worden gelijk te zijn aan 1. Als [kosten] WAAR is (of als dit argument wordt weggelaten), wordt de constante b normaal behandeld; Zelf [kosten] ONWAAR is, wordt de constante b op 1 gezet en wordt de vergelijking in rechte lijn y = mx .

Voorbeeld

In het volgende spreadsheet wordt de Excel-groeifunctie gebruikt om een ​​reeks x- en y-waarden uit te breiden die op de exponentiële groeicurve y = 5 * 2^x liggen. Deze worden opgeslagen in de cellen A2-B5 van het spreadsheet en verschijnen ook in het spreadsheetdiagram.

De functie Groei berekent de exponentiële groeicurve die het beste past bij de opgegeven bekende x- en y-waarden. In dit eenvoudige voorbeeld is de best passende curve de exponentiële curve y = 5 * 2^x.

Zodra Excel de vergelijking van de exponentiële groeicurve heeft berekend, kan het deze gebruiken om de nieuwe y-waarden te berekenen voor de nieuwe x-waarden in de cellen A8-A10.

In dit voorbeeld zijn de waarden van [new_x's] worden opgeslagen in de cellen A8-A10 en de functie Growth van Excel is ingevoegd in de cellen B8-B10. Zoals weergegeven in de formulebalk is de formule hiervoor:=Growth(B2:B5, A2:A5, A8:A10)

U kunt zien dat de functie Groei in de formulebalk tussen accolades { } staat. Dit geeft aan dat de functie is ingevoerd als matrixformule .

Merk op dat hoewel de punten in het bovenstaande voorbeeld precies langs de curve y = 5 * 2^x passen, dit niet essentieel is. De functie Growth Excel vindt de best passende curve voor elke reeks waarden die u opgeeft.

Financiële functies

Effect

De functie Effect Excel retourneert het effectieve jaarlijkse rentetarief voor een bepaald nominaal rentetarief en een bepaald aantal samengestelde perioden per jaar.

Effectieve jaarlijkse rente

De effectieve jaarlijkse rente is een rentemaatstaf die rentekapitalisatie omvat en wordt vaak gebruikt om financiële leningen met verschillende kapitalisatievoorwaarden te vergelijken.

De effectieve jaarlijkse rente wordt berekend met behulp van de volgende vergelijking:

duif nominal_rate is de nominale rente e npery is het aantal samengestelde perioden per jaar.

De syntaxis van de functie is:

syntaxis

= EFFECT( nominal_rate, npery )

onderwerpen

• nominal_rate: Het nominale rentepercentage (moet een numerieke waarde tussen 0 en 1 zijn)
• npery: Het aantal samengestelde perioden per jaar (moet een positief geheel getal zijn).

Voorbeeld

Het volgende spreadsheet toont drie voorbeelden van de Excel-effectfunctie:

Als het resultaat van de functie Effect wordt weergegeven als decimaal of geeft 0% weer. Beide problemen zijn waarschijnlijk te wijten aan de opmaak van de cel die de functie bevat Effect.

Daarom kan het probleem worden opgelost door de cel op te maken in procenten, met decimalen.

Om dit te doen:

1. Selecteer de cellen die u als percentage wilt opmaken.
2. Open het dialoogvenster "Cellen opmaken" op een van de volgende manieren:
   • Klik met de rechtermuisknop op de geselecteerde cel of het geselecteerde bereik en selecteer de optie Cellen opmaken… vanuit het contextmenu;
   • Klik op de Dialoogvensterstarter in de groep Nummer op het tabblad Home Excel-lint;
   • Gebruik de sneltoets CTRL-1 (d.w.z. selecteer de CTRL-toets en houd deze ingedrukt en selecteer de “1” (één)-toets).
3. In het dialoogvenster "Cellen opmaken":
   • Zorg ervoor dat de kaart nummer bovenaan het dialoogvenster is geselecteerd.
   • Kiezen Percentage van de lijst categorie aan de linkerkant van het dialoogvenster .Hierdoor verschijnen er extra opties aan de rechterkant van het selectievakje, zodat u het aantal decimalen kunt selecteren dat u wilt weergeven.
   • Nadat u het aantal decimalen dat u wilt weergeven hebt geselecteerd, klikt u op OK .

Nominal

De functie Nominal Excel retourneert het nominale rentetarief voor een bepaald effectief rentetarief en een bepaald aantal samengestelde perioden per jaar.

De syntaxis van de functie is:

syntaxis

= NOMINAL( effect_rate, npery )

onderwerpen

• effect_rate: De effectieve rente (een numerieke waarde tussen 0 en 1).
• npery: Het aantal samengestelde perioden per jaar (moet een positief geheel getal zijn).

Voorbeeld

In het volgende spreadsheet wordt de functie Nominal van Excel wordt gebruikt om de nominale rente van drie leningen met verschillende looptijden te berekenen.

Ercole Palmeri