Funcții statistice Excel: tutorial cu exemple, partea a treia

Excel oferă o gamă largă de funcții statistice care efectuează calcule de la medie până la funcții mai complexe de distribuție statistică și linii de tendință.

În acest articol vom aprofunda în funcțiile statistice Excel pentru calcularea funcțiilor liniilor de tendință.

Vă rugăm să rețineți că unele funcții statistice au fost introduse în versiunile recente de Excel și, prin urmare, nu sunt disponibile în versiunile mai vechi.

Timp de citire estimat: 12 minute

Funcții Trendline

Forecast

Funcția de prognoză din Excel prezice un punct viitor pe o linie de tendință liniară care se potrivește la un set dat de valori x și y.

sintaxă

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

argumente

• x: O valoare x numerică pentru care doriți să preziceți o nouă valoare y.
• known_y's: O matrice de valori y cunoscute
• known_x's: O matrice de valori x cunoscute

Rețineți că lungimea matricei de known_x trebuie să fie același cu cel al known_y iar varianţa de known_x nu trebuie să fie zero.

Exemplu

În foaia de calcul următoare, funcția FORECAST Excel este folosit pentru a prezice un punct suplimentar de-a lungul liniei drepte care se potrivește cel mai bine printr-o serie de valori x și y cunoscute (stocate în celulele F2:F7 și G2:G7).

După cum se arată în celula F7 a foii de calcul, funcția de calculare a valorii așteptate y la x=7 este :=FORECAST( 7, G2:G7, F2:F7 )

Aceasta dă rezultatul 32.666667 .

Intercept

Printre funcțiile de prognoză ale Excel găsim Intercept. Funcția de interceptare din Excel calculează intersecția (valoarea la intersecția axei y) a liniei de regresie liniară pe un set dat de valori x și y.

sintaxă

= INTERCEPT( known_y's, known_x's )

argumente

• known_y's: O matrice de valori y cunoscute
• known_x's: O matrice de valori x cunoscute

Rețineți că lungimea matricei de known_x trebuie să fie același cu cel al known_y iar varianţa de known_x nu trebuie să fie zero.

Exemplu

Următoarea foaie de calcul prezintă un exemplu de funcție Intercept Excel folosit pentru a calcula punctul în care linia de regresie liniară trece prin known_x și known_y (enumerate în celulele F2:F7 și G2:G7) intersectează axa y.

Le known_x și known_y sunt reprezentate pe graficul din foaia de calcul.

După cum se arată în celula F9 a foii de calcul, formula pentru funcția de interceptare este :=INTERCEPT( G2:G7, F2:F7 )

care dă rezultatul 2.4 .

Slope

O altă funcție de predicție foarte interesantă este Panta (Slope) Excel calculează panta dreptei de regresie liniară printr-un set dat de valori x și y.

Sintaxa funcției este:

sintaxă

= SLOPE( known_y's, known_x's )

argumente

• known_y's: O matrice de valori y cunoscute
• known_x's: O matrice de valori x cunoscute

Rețineți că lungimea matricei de known_x trebuie să fie același cu cel al known_y iar varianţa de known_x nu trebuie să fie zero.

Exemplu

Următoarea foaie de calcul prezintă un exemplu de funcție Slope (panta) din Excel utilizată pentru a calcula panta dreptei de regresie liniară prin known_x si known_y, în celulele F2:F7 și G2:G7.

Le known_x și known_y sunt reprezentate pe graficul din foaia de calcul.

După cum se arată în celula F9 a foii de calcul, formula pentru funcția de interceptare este :=SLOPE( G2:G7, F2:F7 )

care dă rezultatul 4.628571429.

Trend

O funcție de prognoză Excel foarte interesantă este TENDINŢĂ Excel (Trend) calculează linia de tendință liniară printr-un set dat de valori y și (opțional), un set dat de valori x.

Funcția extinde apoi linia de tendință liniară pentru a calcula valori suplimentare y pentru un set suplimentar de noi valori x.

Sintaxa funcției este:

sintaxă

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

argumente

• known_y's: O matrice de valori y cunoscute
• [known_x's]: Una sau mai multe matrice de valori x cunoscute. Acesta este un argument opțional care, dacă este furnizat, ar trebui să aibă aceeași lungime ca și setul de known_y's. Dacă este omis, setul de [known_x's] ia valoarea {1, 2, 3, …}.
• [noi_x]: Un argument opțional, care oferă una sau mai multe matrice de valori numerice reprezentând un set de noi valori x, pentru care doriți să calculați noile valori y corespunzătoare. Fiecare matrice de [new_x's] ar trebui să se potrivească cu o matrice de [known_x's]. Dacă argumentul [new_x's] este omis, este setat la [known_x's] .
• [cost]: Un argument logic opțional care specifică dacă constanta 'b', în ecuația liniară y = m x + b , trebuie forțat să fie egal cu zero. De sine [costa] este TRUE (sau dacă acest argument este omis) constanta b este tratată normal;
• De sine [costa] este FALS, constanta b este setată la 0 și ecuația dreaptă devine y = mx .

Exemplu

În următoarea foaie de calcul, funcția Excel Trend este utilizată pentru a extinde o serie de valori x și y care se află pe linia dreaptă y = 2x + 10. Valorile cunoscute x și y sunt stocate în celulele A2-B5 ale foaia de calcul și sunt afișate și în graficul foii de calcul.

Rețineți că nu este esențial ca punctele date să se potrivească exact de-a lungul dreptei y = 2x + 10 (deși în acest exemplu se întâmplă). Funcția de tendință din Excel va găsi linia cea mai potrivită pentru orice set de valori pe care îl furnizați.

Funcția Trend folosește metoda celor mai mici pătrate pentru a găsi cea mai bună linie de potrivire și apoi o folosește pentru a calcula noile valori y pentru noile valori x furnizate.

În acest exemplu, valorile lui [new_x's] sunt stocate în celulele A8-A10, iar funcția de tendință din Excel a fost folosită, în celulele B8-B10, pentru a găsi noile valori y corespunzătoare. După cum se arată în bara de formule, formula este := TENDINȚĂ( B2:B5, A2:A5, A8:A10 )

Vedeți că funcția Trend din bara de formule este cuprinsă între acolade { }. Aceasta indică faptul că funcția a fost introdusă ca formula matrice .

Growth

Printre funcțiile de prognoză ale Excel găsim Growth. Funcția Growth Excel calculează curba de creștere exponențială printr-un set dat de valori y și (opțional), unul sau mai multe seturi de valori x. Funcția extinde apoi curba pentru a calcula valori suplimentare y pentru un set suplimentar de noi valori x.

Sintaxa funcției este:

sintaxă

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

argumente

• known_y's: O matrice de valori y cunoscute
• [known_x's]: Una sau mai multe matrice de valori x cunoscute. Acesta este un argument opțional care, dacă este furnizat, ar trebui să aibă aceeași lungime ca și setul de known_y's. Dacă este omis, setul de [known_x's] ia valoarea {1, 2, 3, …}.
• [noi_x]: Un set de noi valori x, pentru care funcția calculează noile valori y corespunzătoare. Dacă este omisă, se presupune că mulțimea de [new_x's] este egal cu cel al [known_x's] și funcția returnează valorile y care se află pe curba de creștere exponențială calculată.
• [cost]: Un argument logic opțional care specifică dacă constanta 'b', în ecuația liniară y = b * m^x , trebuie forțat să fie egal cu 1. Dacă [costa] este TRUE (sau dacă acest argument este omis) constanta b este tratată normal; De sine [costa] este FALS, constanta b este setată la 1 și ecuația dreaptă devine y = mx .

Exemplu

În următoarea foaie de calcul, funcția de creștere Excel este utilizată pentru a extinde o serie de valori x și y care se află pe curba de creștere exponențială y = 5 * 2^x. Acestea sunt stocate în celulele A2-B5 ale foii de calcul și apar, de asemenea, în diagrama foii de calcul.

Funcția Creștere calculează curba de creștere exponențială care se potrivește cel mai bine cu valorile cunoscute x și y furnizate. În acest exemplu simplu, curba cea mai potrivită este curba exponențială y = 5 * 2^x.

Odată ce Excel calculează ecuația curbei de creștere exponențială, o poate folosi pentru a calcula noile valori y pentru noile valori x furnizate în celulele A8-A10.

În acest exemplu, valorile lui [new_x's] sunt stocate în celulele A8-A10 și funcția Growth Excel a fost introdus în celulele B8-B10. După cum se arată în bara de formule, formula pentru aceasta este:=Growth( B2:B5, A2:A5, A8:A10)

Puteți vedea că funcția Creștere din bara de formule este cuprinsă între acolade { }. Aceasta indică faptul că funcția a fost introdusă ca formula matrice .

Rețineți că, deși punctele din exemplul de mai sus se potrivesc exact de-a lungul curbei y = 5 * 2^x, acest lucru nu este esențial. Functia Growth Excel va găsi curba cea mai potrivită pentru orice set de valori pe care îl furnizați.

Funcții financiare

Efect

Funcția Effect Excel returnează rata dobânzii anuale efective pentru o anumită rată nominală a dobânzii și un anumit număr de perioade de capitalizare pe an.

Rata anuală efectivă a dobânzii

Rata anuală efectivă a dobânzii este o măsură a dobânzii care încorporează capitalizarea dobânzii și este adesea folosită pentru a compara împrumuturile financiare cu termeni de capitalizare diferiți.

Rata anuală efectivă a dobânzii se calculează folosind următoarea ecuație:

porumbel nominal_rate este rata nominală a dobânzii e npery este numărul de perioade de capitalizare pe an.

Sintaxa funcției este:

sintaxă

= EFFECT( nominal_rate, npery )

argumente

• nominal_rate: Rata nominală a dobânzii (trebuie să fie o valoare numerică între 0 și 1)
• npery: Numărul de perioade de combinare pe an (trebuie să fie un număr întreg pozitiv).

Exemplu

Următoarea foaie de calcul prezintă trei exemple de funcție Excel Effect:

Dacă rezultatul funcției Effect se afișează ca zecimal sau arată 0%, ambele probleme se datorează probabil formatării celulei care conține funcția Effect.

Prin urmare, problema poate fi rezolvată prin formatarea celulei în procente, cu zecimale.

Pentru a face acest lucru:

1. Selectați celulele pe care doriți să le formatați ca procent.
2. Deschideți caseta de dialog „Format Cells” folosind una dintre următoarele metode:
   • Faceți clic dreapta pe celula sau intervalul selectat și selectați opțiunea Celule de format… din meniul contextual;
   • Faceți clic pe Lansatorul casetei de dialog din gruparea Număr din filă Acasă Panglică Excel;
   • Utilizați comanda rapidă de la tastatură CTRL-1 (adică selectați tasta CTRL și, ținând-o apăsată, selectați tasta „1” (una)).
3. În caseta de dialog „Format Cells”:
   • Asigurați-vă că cardul Număr în partea de sus a casetei de dialog este selectat.
   • selecta Procent din lista Categorie în partea stângă a casetei de dialog .Acest lucru va afișa opțiuni suplimentare în partea dreaptă a casetei de selectare, permițându-vă să selectați numărul de zecimale pe care doriți să apară.
   • După ce ați selectat numărul de zecimale pe care doriți să le afișați, faceți clic OK .

Nominal

Funcția Nominal Excel returnează rata nominală a dobânzii pentru o anumită rată efectivă a dobânzii și un anumit număr de perioade de capitalizare pe an.

Sintaxa funcției este:

sintaxă

= NOMINAL( effect_rate, npery )

argumente

• effect_rate: Rata efectivă a dobânzii (o valoare numerică între 0 și 1).
• npery: Numărul de perioade de combinare pe an (trebuie să fie un număr întreg pozitiv).

Exemplu

În foaia de calcul următoare, funcția Nominal Excel este utilizat pentru a calcula rata nominală a dobânzii a trei împrumuturi cu termene diferite.

Ercole Palmeri