Excelin tilastofunktiot: Esimerkkejä opetusohjelma, osa kolmas

Excel tarjoaa laajan valikoiman tilastollisia toimintoja, jotka suorittavat laskelmia keskiarvosta monimutkaisempiin tilastojakauma- ja trendiviivafunktioihin.

Tässä artikkelissa perehdymme Excelin tilastollisiin funktioihin trendiviivafunktioiden laskemiseen.

Huomaa, että jotkin tilastofunktiot otettiin käyttöön Excelin uusimmissa versioissa, eivätkä ne siksi ole käytettävissä vanhemmissa versioissa.

Arvioitu lukuaika: 12 minuti

Trendline-toiminnot

Forecast

Excelin Ennuste-funktio ennustaa tulevan pisteen lineaarisella trendiviivalla, joka sopii annettuun x- ja y-arvojen joukkoon.

syntaksi

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

aiheita

• x: Numeerinen x-arvo, jolle haluat ennustaa uuden y-arvon.
• known_y's: Joukko tunnettuja y-arvoja
• known_x's: Joukko tunnettuja x-arvoja

Huomaa, että taulukon pituus known_x on oltava sama kuin known_y ja varianssi known_x sen ei tarvitse olla nolla.

esimerkki

Seuraavassa laskentataulukossa funktio FORECAST Exceliä käytetään lisäpisteen ennustamiseen parhaiten sopivaa suoraa pitkin tunnettujen x- ja y-arvojen sarjan kautta (tallennettu soluihin F2:F7 ja G2:G7).

Kuten laskentataulukon solussa F7 näkyy, funktio, joka laskee odotetun y-arvon, kun x=7 on :=FORECAST( 7, G2:G7, F2:F7 )

Tämä antaa tuloksen 32.666667 .

Intercept

Excelin ennustetoimintojen joukosta löytyy mm Intercept. Excelin leikkausfunktio laskee lineaarisen regressioviivan leikkauspisteen (arvon y-akselin leikkauspisteessä) tietyn x- ja y-arvojoukon poikki.

syntaksi

= INTERCEPT( known_y's, known_x's )

aiheita

• known_y's: Joukko tunnettuja y-arvoja
• known_x's: Joukko tunnettuja x-arvoja

Huomaa, että taulukon pituus known_x on oltava sama kuin known_y ja varianssi known_x sen ei tarvitse olla nolla.

esimerkki

Seuraavassa laskentataulukossa on esimerkki funktiosta Intercept Excelissä käytetään laskemaan piste, jossa lineaarinen regressioviiva läpi known_x ja known_y (lueteltu soluissa F2:F7 ja G2:G7) leikkaa y-akselin.

Le known_x ja known_y piirretään laskentataulukon kaavioon.

Kuten laskentataulukon solussa F9 näkyy, sieppausfunktion kaava on :=INTERCEPT( G2:G7, F2:F7 )

joka antaa tuloksen 2.4 .

Slope

Toinen erittäin mielenkiintoinen ennustefunktio on kaltevuus (Slope) Excel laskee lineaarisen regressioviivan kaltevuuden tietyn x- ja y-arvojoukon kautta.

Funktion syntaksi on:

syntaksi

= SLOPE( known_y's, known_x's )

aiheita

• known_y's: Joukko tunnettuja y-arvoja
• known_x's: Joukko tunnettuja x-arvoja

Huomaa, että taulukon pituus known_x on oltava sama kuin known_y ja varianssi known_x sen ei tarvitse olla nolla.

esimerkki

Seuraavassa laskentataulukossa on esimerkki funktiosta Slope (kaltevuus) Excelissä, jota käytetään laskemaan lineaarisen regressioviivan kaltevuus known_x ja known_y, soluissa F2:F7 ja G2:G7.

Le known_x ja known_y piirretään laskentataulukon kaavioon.

Kuten laskentataulukon solussa F9 näkyy, sieppausfunktion kaava on :=SLOPE( G2:G7, F2:F7 )

joka antaa tuloksen 4.628571429.

Trend

Erittäin mielenkiintoinen Excel-ennustetoiminto on TRENDI Excel (trendi) laskee lineaarisen trendiviivan tietyn y-arvojoukon ja (valinnaisesti) tietyn x-arvojoukon kautta.

Funktio laajentaa sitten lineaarista trendiviivaa laskeakseen ylimääräiset y-arvot uusien x-arvojen lisäjoukolle.

Funktion syntaksi on:

syntaksi

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

aiheita

• known_y's: Joukko tunnettuja y-arvoja
• [known_x's]: Yksi tai useampia tunnettujen x-arvojen matriisia. Tämä on valinnainen argumentti, jonka, jos se toimitetaan, tulee olla yhtä pitkä kuin joukko known_y's. Jos jätetään pois, joukko [known_x's] saa arvon {1, 2, 3, …}.
• [uudet_x:t]: Valinnainen argumentti, joka tarjoaa yhden tai useamman numeroarvon taulukon, joka edustaa joukkoa uusia x-arvoja, joille haluat laskea vastaavat uudet y-arvot. Jokainen joukko [uudet_x:t] tulee vastata joukkoa [known_x's]. Jos argumentti [uudet_x:t] jätetään pois, sen arvoksi asetetaan [known_x's] .
• [kustannus]: Valinnainen looginen argumentti, joka määrittää, onko vakio "b" lineaarisessa yhtälössä y = m x + b , on pakotettava olemaan yhtä suuri kuin nolla. Itse [maksaa] on TOSI (tai jos tämä argumentti jätetään pois), vakiota b käsitellään normaalisti;
• Itse [maksaa] on EPÄTOSI, vakio b on asetettu arvoon 0 ja suora yhtälö muuttuu y = mx .

esimerkki

Seuraavassa laskentataulukossa Excel Trend -funktiolla laajennetaan sarjaa x- ja y-arvoja, jotka sijaitsevat suoralla y = 2x + 10. Tunnetut x- ja y-arvot tallennetaan soluihin A2-B5. laskentataulukkoon ja näkyvät myös laskentataulukkokaaviossa.

Huomaa, että ei ole välttämätöntä, että annetut pisteet sopivat täsmälleen suoraa y = 2x + 10 pitkin (vaikka tässä esimerkissä ne sopivat). Excelin Trend-toiminto löytää parhaiten sopivan rivin kaikille toimittamillesi arvoille.

Trend-funktio käyttää pienimmän neliösumman menetelmää löytääkseen parhaiten sopivan viivan ja laskee sen sitten uudet y-arvot uusille x-arvoille.

Tässä esimerkissä arvot [uudet_x:t] on tallennettu soluihin A8-A10, ja Excelin Trend-funktiota on käytetty soluissa B8-B10 uusien vastaavien y-arvojen etsimiseen. Kuten kaavapalkissa näkyy, kaava on := TRENDI( B2:B5, A2:A5, A8:A10 )

Näet, että Trend-funktio kaavapalkissa on suljettu aaltosulkeisiin { }. Tämä osoittaa, että toiminto syötettiin muodossa taulukon kaava .

Growth

Excelin ennustetoimintojen joukosta löytyy mm Growth. Toiminto Growth Excel laskee eksponentiaalisen kasvukäyrän tietyn y-arvojoukon ja (valinnainen) yhden tai useamman x-arvojoukon kautta. Funktio laajentaa sitten käyrää laskeakseen ylimääräiset y-arvot uusien x-arvojen lisäjoukolle.

Funktion syntaksi on:

syntaksi

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

aiheita

• known_y's: Joukko tunnettuja y-arvoja
• [known_x's]: Yksi tai useampia tunnettujen x-arvojen matriisia. Tämä on valinnainen argumentti, jonka, jos se toimitetaan, tulee olla yhtä pitkä kuin joukko known_y's. Jos jätetään pois, joukko [known_x's] saa arvon {1, 2, 3, …}.
• [uudet_x:t]: Joukko uusia x-arvoja, joille funktio laskee vastaavat uudet y-arvot. Jos jätetään pois, oletetaan, että joukko [uudet_x:t] on yhtä suuri kuin [known_x's] ja funktio palauttaa y-arvot, jotka ovat lasketulla eksponentiaalisella kasvukäyrällä.
• [kustannus]: Valinnainen looginen argumentti, joka määrittää, onko vakio "b" lineaarisessa yhtälössä y = b * m^x , on pakotettava olemaan yhtä suuri kuin 1. Jos [maksaa] on TOSI (tai jos tämä argumentti jätetään pois), vakiota b käsitellään normaalisti; Itse [maksaa] on EPÄTOSI, vakio b on asetettu arvoon 1 ja suora yhtälö muuttuu y = mx .

esimerkki

Seuraavassa laskentataulukossa Excelin kasvufunktiota käytetään laajentamaan x- ja y-arvojen sarjaa, jotka sijaitsevat eksponentiaalisella kasvukäyrällä y = 5 * 2^x. Nämä tallennetaan laskentataulukon soluihin A2-B5 ja näkyvät myös laskentataulukkokaaviossa.

Kasvufunktio laskee eksponentiaalisen kasvukäyrän, joka sopii parhaiten annettuihin tunnettuihin x- ja y-arvoihin. Tässä yksinkertaisessa esimerkissä parhaiten sopiva käyrä on eksponentiaalinen käyrä y = 5 * 2^x.

Kun Excel on laskenut eksponentiaalisen kasvukäyrän yhtälön, se voi käyttää sitä laskeakseen uudet y-arvot uusille x-arvoille, jotka on annettu soluissa A8-A10.

Tässä esimerkissä arvot [new_x's] tallennetaan soluihin A8-A10 ja funktioon Growth Excel on lisätty soluihin B8-B10. Kuten kaavapalkissa näkyy, tämän kaava on:=Growth( B2:B5, A2:A5, A8:A10 )

Voit nähdä, että kasvufunktio kaavapalkissa on suljettu aaltosulkeisiin { }. Tämä osoittaa, että toiminto syötettiin muodossa taulukon kaava .

Huomaa, että vaikka yllä olevan esimerkin pisteet sopivat täsmälleen käyrälle y = 5 * 2^x, tämä ei ole välttämätöntä. Toiminto Growth Excel löytää parhaiten sopivan käyrän kaikille toimittamillesi arvoille.

Taloudelliset toiminnot

Vaikutus

Toiminto Effect Excel palauttaa efektiivisen vuosikoron tietylle nimelliskorolle ja tietylle määrälle korkojaksoja vuodessa.

Todellinen vuosikorko

Efektiivinen vuosikorko on koron mitta, joka sisältää korkopääoman ja jota käytetään usein vertailemaan rahoituslainoja, joilla on erilaiset pääomitusehdot.

Efektiivinen vuosikorko lasketaan seuraavalla yhtälöllä:

kyyhky nominal_rate on nimellinen korko e npery on sekoitusjaksojen lukumäärä vuodessa.

Funktion syntaksi on:

syntaksi

= EFFECT( nominal_rate, npery )

aiheita

• nominal_rate: Nimellinen korko (täytyy olla numeerinen arvo välillä 0 ja 1)
• npery: Yhdistelmäjaksojen määrä vuodessa (täytyy olla positiivinen kokonaisluku).

esimerkki

Seuraavassa laskentataulukossa on kolme esimerkkiä Excel Effect -toiminnosta:

Jos funktion tulos Effect näkyy desimaaleina tai 0 %, molemmat ongelmat johtuvat todennäköisesti funktion sisältävän solun muotoilusta Effect.

Siksi ongelma voidaan ratkaista muotoilemalla solu prosentteina desimaalien tarkkuudella.

Tehdä tämä:

1. Valitse muotoiltavat solut prosentteina.
2. Avaa "Format Cells" -valintaikkuna jollakin seuraavista tavoista:
   • Napsauta valittua solua tai aluetta hiiren kakkospainikkeella ja valitse vaihtoehto Muotoile solut… kontekstivalikosta;
   • Napsauta valintaikkunan käynnistysohjelmaa välilehden Numeroryhmässä Koti Excelin nauha;
   • Käytä pikanäppäintä CTRL-1 (eli valitse CTRL-näppäin ja pidä sitä alhaalla ja valitse "1" (yksi) näppäin).
3. Muotoile solut -valintaikkunassa:
   • Varmista kortti numero valintaikkunan yläosassa on valittuna.
   • Valitse Prosenttiosuus luettelosta Luokka valintaikkunan vasemmalla puolella .Tämä tuo esiin lisävaihtoehtoja valintaruudun oikealle puolelle, jolloin voit valita näytettävien desimaalien määrän.
   • Kun olet valinnut näytettävien desimaalien määrän, napsauta OK .

Nominal

Toiminto Nominal Excel palauttaa nimelliskoron tietylle efektiiviselle korolle ja tietylle määrälle korkojaksoja vuodessa.

Funktion syntaksi on:

syntaksi

= NOMINAL( effect_rate, npery )

aiheita

• effect_rate: Efektiivinen korko (numeerinen arvo välillä 0 ja 1).
• npery: Yhdistelmäjaksojen määrä vuodessa (täytyy olla positiivinen kokonaisluku).

esimerkki

Seuraavassa laskentataulukossa funktio Nominal Excelin avulla lasketaan kolmen eri ehdoin lainan nimelliskorko.

Ercole Palmeri