Funcións estatísticas de Excel: titorial con exemplos, terceira parte

Excel ofrece unha ampla gama de funcións estatísticas que realizan cálculos desde a media ata as funcións de liña de tendencia e distribución estatística máis complexas.

Neste artigo afondaremos nas funcións estatísticas de Excel para calcular funcións de liña de tendencia.

Teña en conta que algunhas funcións estatísticas foron introducidas nas versións recentes de Excel e, polo tanto, non están dispoñibles nas versións anteriores.

Tempo estimado de lectura: 12 minutos

Funcións de liña de tendencia

Forecast

A función Previsión de Excel prevé un punto futuro nunha liña de tendencia lineal axustada a un determinado conxunto de valores x e y.

sintaxe

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

argumentos

• x: un valor x numérico para o que quere predicir un novo valor y.
• known_y's: unha matriz de valores y coñecidos
• known_x's: unha matriz de valores x coñecidos

Teña en conta que a lonxitude da matriz de known_x debe ser o mesmo que o de known_y e a varianza de known_x non ten que ser cero.

exemplo

Na seguinte folla de cálculo, a función FORECAST Excel úsase para predicir un punto adicional ao longo da liña recta que mellor se axusta a través dunha serie de valores x e y coñecidos (almacenados nas celas F2:F7 e G2:G7).

Como se mostra na cela F7 da folla de cálculo, a función para calcular o valor y esperado en x=7 é :=FORECAST( 7, G2:G7, F2:F7 )

Isto dá o resultado 32.666667 .

Intercept

Entre as funcións de predición de Excel atopamos o Intercept. A función de intersección de Excel calcula a intersección (o valor na intersección do eixe y) da recta de regresión lineal nun conxunto determinado de valores x e y.

sintaxe

= INTERCEPT( known_y's, known_x's )

argumentos

• known_y's: unha matriz de valores y coñecidos
• known_x's: unha matriz de valores x coñecidos

Teña en conta que a lonxitude da matriz de known_x debe ser o mesmo que o de known_y e a varianza de known_x non ten que ser cero.

exemplo

A seguinte folla de cálculo mostra un exemplo da función Intercept de Excel usado para calcular o punto onde pasa a recta de regresión lineal known_x e known_y (listado nas celas F2:F7 e G2:G7) corta o eixe y.

Le known_x e known_y están representados na gráfica da folla de cálculo.

Como se mostra na cela F9 da folla de cálculo, a fórmula para a función Interceptar é :=INTERCEPT( G2:G7, F2:F7 )

que dá o resultado 2.4 .

Slope

Outra función de predición moi interesante é a Pendente (Slope) Excel calcula a pendente da recta de regresión lineal a través dun conxunto determinado de valores x e y.

A sintaxe da función é:

sintaxe

= SLOPE( known_y's, known_x's )

argumentos

• known_y's: unha matriz de valores y coñecidos
• known_x's: unha matriz de valores x coñecidos

Teña en conta que a lonxitude da matriz de known_x debe ser o mesmo que o de known_y e a varianza de known_x non ten que ser cero.

exemplo

A seguinte folla de cálculo mostra un exemplo da función Slope (pendente) de Excel usado para calcular a pendente da recta de regresión lineal a través do known_x e o known_y, nas celas F2:F7 e G2:G7.

Le known_x e known_y están representados na gráfica da folla de cálculo.

Como se mostra na cela F9 da folla de cálculo, a fórmula para a función Interceptar é :=SLOPE( G2:G7, F2:F7 )

que dá o resultado 4.628571429.

Trend

Unha función de previsión de Excel moi interesante é a TREND Excel (Tendencia) calcula a liña de tendencia lineal mediante un determinado conxunto de valores y e (opcionalmente), un determinado conxunto de valores x.

A función estende entón a liña de tendencia lineal para calcular valores y adicionais para un conxunto adicional de novos valores x.

A sintaxe da función é:

sintaxe

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

argumentos

• known_y's: unha matriz de valores y coñecidos
• [known_x's]: Unha ou máis matrices de valores x coñecidos. Este é un argumento opcional que, se se fornece, debería ter a mesma lonxitude que o conxunto de known_y's. Se se omite, o conxunto de [known_x's] toma o valor {1, 2, 3, …}.
• [novos_x]: un argumento opcional que proporciona unha ou máis matrices de valores numéricos que representan un conxunto de novos valores x, para os que quere calcular os novos valores y correspondentes. Cada matriz de [novos_x] debería coincidir cunha matriz de [known_x's]. Se o argumento [novos_x] omítese, establécese como [known_x's] .
• [custo]: Un argumento lóxico opcional que especifica se a constante 'b', na ecuación lineal y = m x + b , debe ser forzado a ser igual a cero. Auto [custa] é VERDADEIRO (ou se se omite este argumento) a constante b trátase normalmente;
• Auto [custa] é FALSO a constante b establécese en 0 e a ecuación da recta pasa a ser y = mx .

exemplo

Na seguinte folla de cálculo, a función de tendencia de Excel úsase para estender unha serie de valores x e y que se atopan na liña recta y = 2x + 10. Os valores x e y coñecidos gárdanse nas celas A2-B5 de a folla de cálculo e tamén se mostran no gráfico da folla de cálculo.

Teña en conta que non é esencial que os puntos indicados encaixen exactamente ao longo da recta y = 2x + 10 (aínda que neste exemplo si). A función de tendencia de Excel atopará a liña que mellor se axuste a calquera conxunto de valores que proporcione.

A función Tendencia usa o método dos mínimos cadrados para atopar a liña de mellor axuste e despois utilízaa para calcular os novos valores y para os novos valores x proporcionados.

Neste exemplo, os valores de [novos_x] gárdanse nas celas A8-A10 e utilizouse a función Tendencia de Excel, nas celas B8-B10, para atopar os novos valores y correspondentes. Como se mostra na barra de fórmulas, a fórmula é := TENDENCIA (B2:B5, A2:A5, A8:A10)

Ves que a función Tendencia da barra de fórmulas está entre chaves { }. Isto indica que a función se introduciu como fórmula matricial .

Growth

Entre as funcións de predición de Excel atopamos o Growth. A función Growth Excel calcula a curva de crecemento exponencial a través dun conxunto determinado de valores y e (opcional), un ou máis conxuntos de valores x. A función estende entón a curva para calcular valores y adicionais para un conxunto adicional de novos valores x.

A sintaxe da función é:

sintaxe

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

argumentos

• known_y's: unha matriz de valores y coñecidos
• [known_x's]: Unha ou máis matrices de valores x coñecidos. Este é un argumento opcional que, se se fornece, debería ter a mesma lonxitude que o conxunto de known_y's. Se se omite, o conxunto de [known_x's] toma o valor {1, 2, 3, …}.
• [novos_x]: Un conxunto de novos valores x, para os que a función calcula os novos valores y correspondentes. Se se omite, suponse que o conxunto de [novos_x] é igual á de [known_x's] e a función devolve os valores y que se atopan na curva de crecemento exponencial calculada.
• [custo]: Un argumento lóxico opcional que especifica se a constante 'b', na ecuación lineal y = b * m^x , debe ser forzado a ser igual a 1. Se [custa] é VERDADEIRO (ou se se omite este argumento) a constante b trátase normalmente; Auto [custa] é FALSO a constante b establécese en 1 e a ecuación da recta pasa a ser y = mx .

exemplo

Na seguinte folla de cálculo, a función de crecemento de Excel úsase para estender unha serie de valores x e y que se atopan na curva de crecemento exponencial y = 5 * 2^x. Estes gárdanse nas celas A2-B5 da folla de cálculo e tamén aparecen no gráfico da folla de cálculo.

A función Crecemento calcula a curva de crecemento exponencial que mellor se adapta aos valores x e y coñecidos proporcionados. Neste exemplo sinxelo, a curva de mellor axuste é a curva exponencial y = 5 * 2^x.

Unha vez que Excel calcula a ecuación da curva de crecemento exponencial, pode usala para calcular os novos valores y para os novos valores x proporcionados nas celas A8-A10.

Neste exemplo, os valores de [new_x's] almacénanse nas celas A8-A10 e a función Growth de Excel inseriuse nas celas B8-B10. Como se mostra na barra de fórmulas, a fórmula para isto é:=Growth(B2:B5, A2:A5, A8:A10)

Podes ver que a función Crecemento da barra de fórmulas está entre chaves { }. Isto indica que a función se introduciu como fórmula matricial .

Teña en conta que aínda que os puntos do exemplo anterior encaixan exactamente ao longo da curva y = 5 * 2^x, isto non é esencial. A función Growth Excel atopará a curva que mellor se axuste a calquera conxunto de valores que proporcione.

Funcións financeiras

Efecto

A función Effect Excel devolve o tipo de xuro anual efectivo para un tipo de xuro nominal determinado e un determinado número de períodos compostos ao ano.

Tipo de xuro anual efectivo

A taxa de xuro anual efectiva é unha medida de interese que incorpora a capitalización dos intereses e adoita utilizarse para comparar préstamos financeiros con diferentes termos de capitalización.

O tipo de xuro anual efectivo calcúlase mediante a seguinte ecuación:

pomba nominal_rate é o tipo de xuro nominal e npery é o número de períodos compostos por ano.

A sintaxe da función é:

sintaxe

= EFFECT( nominal_rate, npery )

argumentos

• nominal_rate: O tipo de xuro nominal (debe ser un valor numérico entre 0 e 1)
• npery: O número de períodos de composición por ano (debe ser un número enteiro positivo).

exemplo

A seguinte folla de cálculo mostra tres exemplos da función Excel Effect:

Se o resultado da función Effect móstrase como decimal ou mostra o 0 %, estes dous problemas probablemente se deban ao formato da cela que contén a función Effect.

Polo tanto, o problema pódese resolver formateando a cela en porcentaxe, con cifras decimais.

Para facelo:

1. Seleccione as celas para formatar en porcentaxe.
2. Abra a caixa de diálogo "Formatar celas" mediante un dos seguintes métodos:
   • Fai clic co botón dereito na cela ou rango seleccionado e selecciona a opción Formatar celas... desde o menú contextual;
   • Fai clic no Lanzador de caixas de diálogo no grupo Número da pestana casa Cinta de Excel;
   • Usa o atallo do teclado CTRL-1 (é dicir, seleccione a tecla CTRL e, mantendo premida, seleccione a tecla "1" (unha)).
3. No cadro de diálogo "Formato de celas":
   • Asegúrese de que a tarxeta Número na parte superior da caixa de diálogo está seleccionada.
   • seleccionar Porcentaxe da lista Categoría no lado esquerdo da caixa de diálogo .Isto mostrará opcións adicionais no lado dereito da caixa de verificación, o que lle permitirá seleccionar o número de cifras decimais que desexa que aparezan.
   • Despois de seleccionar o número de cifras decimais que desexa mostrar, prema OK .

Nominal

A función Nominal Excel devolve o tipo de xuro nominal para un determinado tipo de xuro efectivo e un determinado número de períodos de composición ao ano.

A sintaxe da función é:

sintaxe

= NOMINAL( effect_rate, npery )

argumentos

• effect_rate: O tipo de xuro efectivo (un valor numérico entre 0 e 1).
• npery: O número de períodos de composición por ano (debe ser un número enteiro positivo).

exemplo

Na seguinte folla de cálculo, a función Nominal de Excel utilízase para calcular o tipo de xuro nominal de tres préstamos con diferentes condicións.

Ercole Palmeri