Funciones estadísticas de Excel: tutorial con ejemplos, tercera parte

Excel proporciona una amplia gama de funciones estadísticas que realizan cálculos desde la media hasta funciones estadísticas más complejas de distribución y línea de tendencia.

En este artículo profundizaremos en las funciones estadísticas de Excel para calcular funciones de líneas de tendencia.

Tenga en cuenta que algunas funciones estadísticas se introdujeron en versiones recientes de Excel y, por lo tanto, no están disponibles en versiones anteriores.

Tiempo estimado de lectura: 12 acta

Funciones de línea de tendencia

Forecast

La función Pronóstico de Excel predice un punto futuro en una línea de tendencia lineal que se ajusta a un conjunto dado de valores xey.

sintaxis

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

temas

• x: un valor numérico de x para el cual desea predecir un nuevo valor de y.
• known_y's: una matriz de valores de y conocidos
• known_x's: una matriz de valores x conocidos

Tenga en cuenta que la longitud de la matriz de known_x debe ser el mismo que el de known_y y la varianza de known_x no tiene por qué ser cero.

Ejemplo

En la siguiente hoja de cálculo, la función FORECAST Excel se utiliza para predecir un punto adicional a lo largo de la línea recta que mejor se ajusta a través de una serie de valores xey conocidos (almacenados en las celdas F2:F7 y G2:G7).

Como se muestra en la celda F7 de la hoja de cálculo, la función para calcular el valor esperado de y en x=7 es :=FORECAST( 7, G2:G7, F2:F7 )

Esto da el resultado 32.666667 .

Intercept

Entre las funciones de previsión de Excel encontramos la Intercept. La función Intercepción de Excel calcula la intersección (el valor en la intersección del eje y) de la línea de regresión lineal a través de un conjunto dado de valores xey.

sintaxis

= INTERCEPT( known_y's, known_x's )

temas

• known_y's: una matriz de valores de y conocidos
• known_x's: una matriz de valores x conocidos

Tenga en cuenta que la longitud de la matriz de known_x debe ser el mismo que el de known_y y la varianza de known_x no tiene por qué ser cero.

Ejemplo

La siguiente hoja de cálculo muestra un ejemplo de la función. Intercept de Excel utilizado para calcular el punto donde la recta de regresión lineal pasa por el known_x y known_y (enumerados en las celdas F2:F7 y G2:G7) intersecta el eje y.

Le known_x y known_y se trazan en el gráfico de la hoja de cálculo.

Como se muestra en la celda F9 de la hoja de cálculo, la fórmula para la función Interceptar es :=INTERCEPT( G2:G7, F2:F7 )

que da el resultado 2.4 .

Slope

Otra función de predicción muy interesante es la Pendiente (Slope) Excel calcula la pendiente de la recta de regresión lineal a través de un conjunto dado de valores xey.

La sintaxis de la función es:

sintaxis

= SLOPE( known_y's, known_x's )

temas

• known_y's: una matriz de valores de y conocidos
• known_x's: una matriz de valores x conocidos

Tenga en cuenta que la longitud de la matriz de known_x debe ser el mismo que el de known_y y la varianza de known_x no tiene por qué ser cero.

Ejemplo

La siguiente hoja de cálculo muestra un ejemplo de la función. Slope (pendiente) de Excel utilizado para calcular la pendiente de la línea de regresión lineal a través de la known_x y el known_y, en las celdas F2:F7 y G2:G7.

Le known_x y known_y se trazan en el gráfico de la hoja de cálculo.

Como se muestra en la celda F9 de la hoja de cálculo, la fórmula para la función Interceptar es :=SLOPE( G2:G7, F2:F7 )

que da el resultado 4.628571429.

Trend

Una función de pronóstico de Excel muy interesante es la TENDENCIA Excel (Tendencia) calcula la línea de tendencia lineal a través de un conjunto determinado de valores de y y (opcionalmente), un conjunto determinado de valores de x.

Luego, la función extiende la línea de tendencia lineal para calcular valores de y adicionales para un conjunto adicional de nuevos valores de x.

La sintaxis de la función es:

sintaxis

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

temas

• known_y's: una matriz de valores de y conocidos
• [known_x's]: Una o más matrices de valores de x conocidos. Este es un argumento opcional que, si se proporciona, debe tener la misma longitud que el conjunto de known_y's. Si se omite, el conjunto de [known_x's] toma el valor {1, 2, 3,…}.
• [nuevo_x]: un argumento opcional, que proporciona una o más matrices de valores numéricos que representan un conjunto de nuevos valores de x, para los cuales desea calcular los nuevos valores de y correspondientes. Cada matriz de [nuevo_x] debe coincidir con una matriz de [known_x's]. Si el argumento [nuevo_x] se omite, se establece en igual a [known_x's].
• [costo]: Un argumento lógico opcional que especifica si la constante 'b', en la ecuación lineal y = mx + b , debe ser forzado a ser igual a cero. Ser [el costo] es VERDADERO (o si se omite este argumento) la constante b se trata normalmente;
• Ser [el costo] es FALSO la constante b se establece en 0 y la ecuación en línea recta se convierte en y = mx .

Ejemplo

En la siguiente hoja de cálculo, la función Tendencia de Excel se utiliza para extender una serie de valores de x e y que se encuentran en la línea recta y = 2x + 10. Los valores de x e y conocidos se almacenan en las celdas A2-B5 de la hoja de cálculo y también se muestran en el gráfico de la hoja de cálculo.

Tenga en cuenta que no es esencial que los puntos dados encajen exactamente a lo largo de la línea recta y = 2x + 10 (aunque en este ejemplo sí lo hacen). La función Tendencia de Excel encontrará la línea que mejor se ajuste a cualquier conjunto de valores que proporcione.

La función Tendencia utiliza el método de mínimos cuadrados para encontrar la línea que mejor se ajusta y luego la usa para calcular los nuevos valores de y para los nuevos valores de x proporcionados.

En este ejemplo, los valores de [nuevo_x] se almacenan en las celdas A8-A10, y se ha utilizado la función Tendencia de Excel, en las celdas B8-B10, para encontrar los nuevos valores de y correspondientes. Como se muestra en la barra de fórmulas, la fórmula es := TENDENCIA( B2:B5, A2:A5, A8:A10 )

Verá que la función Tendencia en la barra de fórmulas está entre llaves { }. Esto indica que la función fue ingresada como fórmula matricial .

Growth

Entre las funciones de previsión de Excel encontramos la Growth. La función Growth Excel calcula la curva de crecimiento exponencial a través de un conjunto dado de valores de y y (opcional), uno o más conjuntos de valores de x. Luego, la función extiende la curva para calcular valores de y adicionales para un conjunto adicional de nuevos valores de x.

La sintaxis de la función es:

sintaxis

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

temas

• known_y's: una matriz de valores de y conocidos
• [known_x's]: Una o más matrices de valores de x conocidos. Este es un argumento opcional que, si se proporciona, debe tener la misma longitud que el conjunto de known_y's. Si se omite, el conjunto de [known_x's] toma el valor {1, 2, 3,…}.
• [nuevo_x]: un conjunto de nuevos valores de x, para los cuales la función calcula los nuevos valores de y correspondientes. Si se omite, se supone que el conjunto de [nuevo_x] es igual a la de [known_x's] y la función devuelve los valores de y que se encuentran en la curva de crecimiento exponencial calculada.
• [costo]: Un argumento lógico opcional que especifica si la constante 'b', en la ecuación lineal y = b * m^x , debe ser forzado a ser igual a 1. Si [el costo] es VERDADERO (o si se omite este argumento) la constante b se trata normalmente; Ser [el costo] es FALSO la constante b se establece en 1 y la ecuación en línea recta se convierte en y = mx .

Ejemplo

En la siguiente hoja de cálculo, la función de crecimiento de Excel se utiliza para extender una serie de valores de xey que se encuentran en la curva de crecimiento exponencial y = 5 * 2^x. Estos se almacenan en las celdas A2-B5 de la hoja de cálculo y también aparecen en el gráfico de la hoja de cálculo.

La función Crecimiento calcula la curva de crecimiento exponencial que mejor se ajusta a los valores x e y conocidos proporcionados. En este ejemplo sencillo, la curva que mejor se ajusta es la curva exponencial y = 5 * 2^x.

Una vez que Excel calcula la ecuación de la curva de crecimiento exponencial, puede usarla para calcular los nuevos valores de y para los nuevos valores de x proporcionados en las celdas A8-A10.

En este ejemplo, los valores de [new_x's] se almacenan en las celdas A8-A10 y la función Growth de Excel se ha insertado en las celdas B8-B10. Como se muestra en la barra de fórmulas, la fórmula para esto es:=Growth(B2:B5, A2:A5, A8:A10)

Puede ver que la función Crecimiento en la barra de fórmulas está entre llaves { }. Esto indica que la función fue ingresada como fórmula matricial .

Tenga en cuenta que aunque los puntos del ejemplo anterior encajan exactamente a lo largo de la curva y = 5 * 2^x, esto no es esencial. La función Growth Excel encontrará la curva que mejor se ajuste a cualquier conjunto de valores que proporcione.

Funciones financieras

Efecto

La función Effect Excel devuelve la tasa de interés anual efectiva para una tasa de interés nominal determinada y un número determinado de períodos de capitalización por año.

Tasa de interés anual efectiva

La tasa de interés anual efectiva es una medida de interés que incorpora la capitalización de intereses y se utiliza a menudo para comparar préstamos financieros con diferentes plazos de capitalización.

La tasa de interés efectiva anual se calcula mediante la siguiente ecuación:

donde nominal_rate es la tasa de interés nominal e npery es el número de períodos de capitalización por año.

La sintaxis de la función es:

sintaxis

= EFFECT( nominal_rate, npery )

temas

• nominal_rate: La tasa de interés nominal (debe ser un valor numérico entre 0 y 1)
• npery: El número de períodos de capitalización por año (debe ser un número entero positivo).

Ejemplo

La siguiente hoja de cálculo muestra tres ejemplos de la función Efecto de Excel:

Si el resultado de la función Effect se muestra como decimal o muestra 0%, ambos problemas probablemente se deben al formato de la celda que contiene la función Effect.

Por tanto, el problema se puede solucionar formateando la celda en porcentaje, con decimales.

Para hacer esto:

1. Seleccione las celdas para formatear como porcentaje.
2. Abra el cuadro de diálogo "Formato de celdas" utilizando uno de los siguientes métodos:
   • Haga clic derecho en la celda o rango seleccionado y seleccione la opción Formato de celdas… desde el menú contextual;
   • Haga clic en el iniciador del cuadro de diálogo en el grupo Número de la pestaña Inicio Cinta de Excel;
   • Usa el atajo de teclado CTRL-1 (es decir, seleccione la tecla CTRL y, manteniéndola presionada, seleccione la tecla “1” (uno)).
3. En el cuadro de diálogo "Formato de celdas":
   • Asegúrese de que la tarjeta Número en la parte superior del cuadro de diálogo está seleccionado.
   • seleccionar Porcentaje de Categoría en el lado izquierdo del cuadro de diálogo .Esto mostrará opciones adicionales en el lado derecho de la casilla de verificación, lo que le permitirá seleccionar la cantidad de decimales que desea que aparezca.
   • Una vez que haya seleccionado el número de decimales que desea mostrar, haga clic en OK .

Nominal

La función Nominal Excel devuelve la tasa de interés nominal para una tasa de interés efectiva determinada y un número determinado de períodos de capitalización por año.

La sintaxis de la función es:

sintaxis

= NOMINAL( effect_rate, npery )

temas

• effect_rate: La tasa de interés efectiva (un valor numérico entre 0 y 1).
• npery: El número de períodos de capitalización por año (debe ser un número entero positivo).

Ejemplo

En la siguiente hoja de cálculo, la función Nominal de Excel se utiliza para calcular la tasa de interés nominal de tres préstamos con diferentes plazos.

Ercole Palmeri