Funcions estadístiques d'Excel: Tutorial amb exemples, tercera part

Excel ofereix una àmplia gamma de funcions estadístiques que realitzen càlculs des de la mitjana fins a funcions de línia de tendència i distribució estadística més complexes.

En aquest article aprofundirem en les funcions estadístiques d'Excel per calcular funcions de línia de tendència.

Tingueu en compte que algunes funcions estadístiques s'han introduït a les versions recents d'Excel i, per tant, no estan disponibles en versions anteriors.

Temps estimat de lectura: 12 acta

Funcions de línia de tendència

Forecast

La funció de previsió d'Excel prediu un punt futur en una línia de tendència lineal que s'ajusta a un conjunt determinat de valors x i y.

sintaxi

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

subjectes

• x: un valor x numèric per al qual voleu predir un valor y nou.
• known_y's: una matriu de valors y coneguts
• known_x's: una matriu de valors x coneguts

Tingueu en compte que la longitud de la matriu de known_x ha de ser el mateix que el de known_y i la variància de known_x no ha de ser zero.

exemple

En el següent full de càlcul, la funció FORECAST Excel s'utilitza per predir un punt addicional al llarg de la línia recta que millor s'ajusta a través d'una sèrie de valors x i y coneguts (emmagatzemats a les cel·les F2:F7 i G2:G7).

Com es mostra a la cel·la F7 del full de càlcul, la funció per calcular el valor y esperat a x=7 és :=FORECAST( 7, G2:G7, F2:F7 )

Això dóna el resultat 32.666667 .

Intercept

Entre les funcions de previsió d'Excel trobem el Intercept. La funció d'Intercepció d'Excel calcula la intersecció (el valor a la intersecció de l'eix y) de la línia de regressió lineal a través d'un conjunt determinat de valors x i y.

sintaxi

= INTERCEPT( known_y's, known_x's )

subjectes

• known_y's: una matriu de valors y coneguts
• known_x's: una matriu de valors x coneguts

Tingueu en compte que la longitud de la matriu de known_x ha de ser el mateix que el de known_y i la variància de known_x no ha de ser zero.

exemple

El següent full de càlcul mostra un exemple de la funció Intercept d'Excel utilitzat per calcular el punt on la línia de regressió lineal passa per la known_x i known_y (llistat a les cel·les F2:F7 i G2:G7) talla l'eix y.

Le known_x i known_y es representen al gràfic del full de càlcul.

Tal com es mostra a la cel·la F9 del full de càlcul, la fórmula per a la funció Interceptar és :=INTERCEPT( G2:G7, F2:F7 )

que dóna el resultat 2.4 .

Slope

Una altra funció de predicció molt interessant és la pendent (Slope) Excel calcula el pendent de la recta de regressió lineal mitjançant un conjunt determinat de valors x i y.

La sintaxi de la funció és:

sintaxi

= SLOPE( known_y's, known_x's )

subjectes

• known_y's: una matriu de valors y coneguts
• known_x's: una matriu de valors x coneguts

Tingueu en compte que la longitud de la matriu de known_x ha de ser el mateix que el de known_y i la variància de known_x no ha de ser zero.

exemple

El següent full de càlcul mostra un exemple de la funció Slope (pendent) d'Excel que s'utilitza per calcular el pendent de la recta de regressió lineal a través del known_x i la known_y, a les cel·les F2:F7 i G2:G7.

Le known_x i known_y es representen al gràfic del full de càlcul.

Tal com es mostra a la cel·la F9 del full de càlcul, la fórmula per a la funció Interceptar és :=SLOPE( G2:G7, F2:F7 )

que dóna el resultat 4.628571429.

Trend

Una funció de previsió d'Excel molt interessant és la TENDÈNCIES Excel (Tendència) calcula la línia de tendència lineal mitjançant un conjunt determinat de valors y i (opcionalment), un conjunt determinat de valors x.

Aleshores, la funció amplia la línia de tendència lineal per calcular valors y addicionals per a un conjunt addicional de valors x nous.

La sintaxi de la funció és:

sintaxi

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

subjectes

• known_y's: una matriu de valors y coneguts
• [known_x's]: una o més matrius de valors x coneguts. Aquest és un argument opcional que, si es proporciona, hauria de tenir la mateixa longitud que el conjunt de known_y's. Si s'omet, el conjunt de [known_x's] pren el valor {1, 2, 3, …}.
• [nou_x's]: un argument opcional, que proporciona una o més matrius de valors numèrics que representen un conjunt de nous valors x, per als quals voleu calcular els nous valors y corresponents. Cada matriu de [nous_x] hauria de coincidir amb una matriu de [known_x's]. Si l'argument [nous_x] s'omet, s'estableix en igual a [known_x's] .
• [cost]: Un argument lògic opcional que especifica si la constant 'b', a l'equació lineal i = mx + b , s'ha de forçar a ser igual a zero. Jo mateix [cost] és TRUE (o si s'omet aquest argument) la constant b es tracta amb normalitat;
• Jo mateix [cost] és FALS, la constant b es posa a 0 i l'equació de línia recta es converteix y = mx .

exemple

En el següent full de càlcul, la funció de tendència d'Excel s'utilitza per estendre una sèrie de valors x i y que es troben a la línia recta y = 2x + 10. Els valors x i y coneguts s'emmagatzemen a les cel·les A2-B5 de el full de càlcul i també es mostren al gràfic del full de càlcul.

Tingueu en compte que no és essencial que els punts donats encaixin exactament al llarg de la recta y = 2x + 10 (tot i que en aquest exemple sí). La funció de tendència d'Excel trobarà la línia més adequada per a qualsevol conjunt de valors que proporcioneu.

La funció de tendència utilitza el mètode dels mínims quadrats per trobar la millor línia d'ajust i després l'utilitza per calcular els nous valors y per als nous valors x proporcionats.

En aquest exemple, els valors de [nous_x] s'emmagatzemen a les cel·les A8-A10, i s'ha utilitzat la funció Tendència d'Excel, a les cel·les B8-B10, per trobar els nous valors y corresponents. Com es mostra a la barra de fórmules, la fórmula és := TENDÈNCIA (B2:B5, A2:A5, A8:A10)

Veu que la funció Tendència a la barra de fórmules està tancada entre claus { }. Això indica que la funció s'ha introduït com a fórmula de matriu .

Growth

Entre les funcions de previsió d'Excel trobem el Growth. La funció Growth Excel calcula la corba de creixement exponencial mitjançant un conjunt determinat de valors y i (opcional), un o més conjunts de valors x. Aleshores, la funció amplia la corba per calcular valors y addicionals per a un conjunt addicional de valors x nous.

La sintaxi de la funció és:

sintaxi

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

subjectes

• known_y's: una matriu de valors y coneguts
• [known_x's]: una o més matrius de valors x coneguts. Aquest és un argument opcional que, si es proporciona, hauria de tenir la mateixa longitud que el conjunt de known_y's. Si s'omet, el conjunt de [known_x's] pren el valor {1, 2, 3, …}.
• [nou_x's]: un conjunt de nous valors x, per als quals la funció calcula els nous valors y corresponents. Si s'omet, se suposa que el conjunt de [nous_x] és igual a la de [known_x's] i la funció retorna els valors y que es troben a la corba de creixement exponencial calculada.
• [cost]: Un argument lògic opcional que especifica si la constant 'b', a l'equació lineal y = b * m^x , s'ha de forçar a ser igual a 1. Si [cost] és TRUE (o si s'omet aquest argument) la constant b es tracta amb normalitat; Jo mateix [cost] és FALS, la constant b es posa a 1 i l'equació de línia recta es converteix y = mx .

exemple

En el següent full de càlcul, la funció de creixement d'Excel s'utilitza per ampliar una sèrie de valors x i y que es troben a la corba de creixement exponencial y = 5 * 2^x. S'emmagatzemen a les cel·les A2-B5 del full de càlcul i també apareixen al gràfic del full de càlcul.

La funció de creixement calcula la corba de creixement exponencial que millor s'ajusta als valors x i y coneguts proporcionats. En aquest exemple senzill, la corba que millor s'ajusta és la corba exponencial y = 5 * 2^x.

Un cop Excel calcula l'equació de la corba de creixement exponencial, pot utilitzar-la per calcular els nous valors y per als nous valors x proporcionats a les cel·les A8-A10.

En aquest exemple, els valors de [new_x's] s'emmagatzemen a les cel·les A8-A10 i la funció Growth d'Excel s'ha inserit a les cel·les B8-B10. Com es mostra a la barra de fórmules, la fórmula per a això és:=Growth(B2:B5, A2:A5, A8:A10)

Podeu veure que la funció Creixement a la barra de fórmules està tancada entre claus { }. Això indica que la funció s'ha introduït com a fórmula de matriu .

Tingueu en compte que, tot i que els punts de l'exemple anterior s'ajusten exactament al llarg de la corba y = 5 * 2^x, això no és essencial. La funció Growth Excel trobarà la corba més adequada per a qualsevol conjunt de valors que proporcioneu.

Funcions financeres

Efecte

La funció Effect Excel retorna el tipus d'interès anual efectiu per a un tipus d'interès nominal determinat i un nombre determinat de períodes de capitalització per any.

Tipus d'interès anual efectiu

El tipus d'interès anual efectiu és una mesura d'interès que incorpora la capitalització dels interessos i s'utilitza sovint per comparar préstecs financers amb diferents termes de capitalització.

El tipus d'interès anual efectiu es calcula mitjançant l'equació següent:

colom nominal_rate és el tipus d'interès nominal e npery és el nombre de períodes de capitalització per any.

La sintaxi de la funció és:

sintaxi

= EFFECT( nominal_rate, npery )

subjectes

• nominal_rate: El tipus d'interès nominal (ha de ser un valor numèric entre 0 i 1)
• npery: el nombre de períodes de composició per any (ha de ser un nombre enter positiu).

exemple

El següent full de càlcul mostra tres exemples de la funció Excel Effect:

Si el resultat de la funció Effect es mostra com a decimal o mostra 0%, aquests dos problemes probablement es deuen al format de la cel·la que conté la funció Effect.

Per tant, el problema es pot resoldre formatant la cel·la en percentatge, amb decimals.

Fer això:

1. Seleccioneu les cel·les per formatar com a percentatge.
2. Obriu el quadre de diàleg "Format de cel·les" mitjançant un dels mètodes següents:
   • Feu clic amb el botó dret a la cel·la o l'interval seleccionat i seleccioneu l'opció Formata cel·les... des del menú contextual;
   • Feu clic al Llançador de quadres de diàleg a l'agrupació Número de la pestanya Home Cinta d'Excel;
   • Utilitzeu la drecera del teclat CTRL-1 (és a dir, seleccioneu la tecla CTRL i, mantenint-la premuda, seleccioneu la tecla "1" (una)).
3. Al quadre de diàleg "Format cel·les":
   • Assegureu-vos que la targeta nombre a la part superior del quadre de diàleg està seleccionat.
   • Seleccionar Percentatge de la llista Categoria al costat esquerre del quadre de diàleg .Això mostrarà opcions addicionals a la part dreta de la casella de selecció, que us permetran seleccionar el nombre de decimals que voleu que apareguin.
   • Un cop hàgiu seleccionat el nombre de decimals que voleu mostrar, feu clic OK .

Nominal

La funció Nominal Excel retorna el tipus d'interès nominal per a un tipus d'interès efectiu determinat i un nombre determinat de períodes de capitalització per any.

La sintaxi de la funció és:

sintaxi

= NOMINAL( effect_rate, npery )

subjectes

• effect_rate: El tipus d'interès efectiu (un valor numèric entre 0 i 1).
• npery: el nombre de períodes de composició per any (ha de ser un nombre enter positiu).

exemple

En el següent full de càlcul, la funció Nominal d'Excel s'utilitza per calcular el tipus d'interès nominal de tres préstecs amb condicions diferents.

Ercole Palmeri