Funciones Estadísticas Avanzadas de Excel para Análisis Profundo de Datos
Excel también ofrece una amplia gama de funciones estadísticas. Estas funciones estadísticas le permiten realizar diversas operaciones matemáticas y estadísticas con los datos para obtener información útil.
Algunas de las funciones estadísticas más comunes que se pueden encontrar en Excel incluyen el conteo de celdas, la suma, el promedio, el mínimo, el máximo, la desviación estándar, la correlación y la regresión.
👉 Además de estas funciones básicas, Excel también ofrece una variedad de funciones avanzadas de análisis de datos …
Como por ejemplo, ANOVA, la Regresión Lineal Múltiple, el Análisis de Componentes Principales (PCA) y el Análisis de Cluster. Estas funciones le permiten realizar análisis predictivos y comprender mejor los datos.
Análisis profundos de los datos con Excel
Función de correlación: calcula el grado de relación entre dos variables. Por ejemplo, se puede usar para determinar cuánto cambia una variable cuando cambia la otra.
La función de correlación en Excel permite a los usuarios calcular el grado de relación entre dos conjuntos de datos. Por ejemplo, para determinar si existe una relación entre el tamaño de la casa y su precio, los usuarios pueden usar la función de correlación.
Para hacer esto, los usuarios primero deben insertar los valores de tamaño de la casa en una columna y los valores de precio de la casa en otra. Luego, los usuarios pueden usar la función CORREL para calcular un coeficiente de correlación entre las dos columnas.
Esta función devolverá un número entre -1 y 1, que indica el grado de relación entre los dos conjuntos de datos. Un resultado de 1 indica una correlación perfecta, mientras que un resultado de -1 indica una correlación inversa perfecta. Un resultado de 0 indica que no existe correlación entre los dos conjuntos de datos.
Función de regresión:
Se utiliza para encontrar la mejor línea de ajuste para un conjunto de datos. Esta línea de ajuste puede ser utilizada para predecir un resultado dado cierta información.
Supongamos que queremos obtener una función de regresión para predecir el precio de una casa en función de su tamaño (en metros cuadrados).
Para realizar esto en Excel, primero tendremos que crear una tabla con nuestros datos. En la columna A, ingresaremos los tamaños de los inmuebles, mientras que en la columna B ingresaremos los precios correspondientes.
Una vez que hayamos ingresado los datos, hacemos clic en el menú “Insertar” y elegimos la opción “Gráfico”. Seleccionamos “Gráfico de dispersión” y luego “Aceptar”.
Ahora debemos seleccionar nuestros datos y hacer clic en el botón “Agregar”. Esto creará un gráfico que muestra la relación entre el tamaño de la casa y su precio.
Para obtener la función de regresión, hacemos clic derecho en el gráfico y elegimos “Agregar línea de tendencia”. Seleccionamos la opción “Lineal” y luego hacemos clic en “Aceptar”.
La línea de tendencia se dibujará en el gráfico, y la ecuación de regresión se mostrará en la parte inferior de la ventana. Esta ecuación nos dirá cómo calcular el precio de una casa en función de su tamaño.
Función de análisis de componentes principales:
Esta función se usa para reducir la dimensionalidad de un conjunto de datos y encontrar patrones ocultos en los datos. Esto es útil para entender mejor los datos y encontrar relaciones entre variables.
La función de análisis de componentes principales en Excel se usa para reducir el número de variables de un conjunto de datos sin perder mucha información. Esta función es útil para encontrar patrones en los datos de una forma más rápida y eficiente.
Para usar esta función, primero debes abrir una hoja de cálculo de Excel y seleccionar el conjunto de datos que desea analizar. A continuación, ve a la Pestaña Datos / Análisis de componentes principales.
Una vez que hayas hecho esto, aparecerá un cuadro de diálogo en el que debe seleccionar los parámetros del análisis.
Estos incluyen el número de variables a analizar, el número de componentes principales a calcular, el tipo de normalización a usar, el método de rotación, etc.
Una vez que hayas seleccionado los parámetros, haz clic en «Aceptar». El resultado del análisis de componentes principales se mostrará en una nueva hoja de cálculo.
Esta hoja de cálculo contendrá información sobre los componentes principales calculados, así como el número de variables analizadas y el número de componentes principales calculados.
Función de clúster:
Se utiliza para agrupar datos similares en grupos comunes. Esto es útil para encontrar grupos específicos dentro de un conjunto de datos.
La función de clúster en Excel se utiliza para agrupar los datos en grupos similares. Por ejemplo, los datos se pueden agrupar según ciertas características comunes.
Supongamos que tenemos una lista de estudiantes con sus calificaciones en una prueba. Podemos usar la función de clúster para agrupar estos datos en grupos según el promedio de calificación.
Por ejemplo, si tenemos una columna con los nombres de los estudiantes y otra columna con sus calificaciones, podemos usar la función de clúster para agruparlos según el promedio de calificación.
Para hacer esto, primero debemos seleccionar todos los datos, incluyendo los encabezados de columna. Luego, en la ficha Datos, haga clic en Análisis de datos y luego en Cluster.
Aparecerá un cuadro de diálogo donde puedes seleccionar la columna con los nombres de los estudiantes y la columna con las calificaciones.
Cuando hayas hecho esto, selecciona el número de grupos en los que quieres agrupar los datos y luego haz clic en OK. Excel agrupará los datos en los grupos especificados y los datos se mostrarán en una nueva hoja de cálculo.
Función de regresión lineal múltiple:
Utilizada para encontrar la mejor línea de ajuste para un conjunto de datos con más de una variable. Esta línea de ajuste puede ser utilizada para predecir un resultado dado cierta información.
La regresión lineal múltiple en Excel se puede usar para predecir el valor de Y (la variable dependiente) en función de varias variables predictoras independientes X1, X2, X3, etc.
Por ejemplo, imagina que deseas estimar la producción total de una empresa en función de los niveles de producción de cada uno de sus departamentos.
Si los departamentos son A, B y C, entonces X1 sería el nivel de producción del departamento A, X2 sería el nivel de producción del departamento B y X3 sería el nivel de producción del departamento C. El valor de Y sería el nivel de producción total de la empresa.
Para realizar la regresión lineal múltiple en Excel, primero debes ingresar los datos en una hoja de cálculo. A continuación, ve a la ficha Datos y selecciona Análisis de datos. Selecciona el comando Regresión y aparecerá un cuadro de diálogo.
En la sección Variable independiente (s), agregue las variables predictoras X1, X2 y X3, y en la sección Variable dependiente (s), agregue la variable Y. A continuación, haga clic en Aceptar.
Excel generará una hoja de resultados que contiene los coeficientes de regresión para cada variable predictora, así como el intercepto o punto de intercepción.
También mostrará los valores de R2 y el grado de significación de los coeficientes. Con estos resultados, puedes calcular los valores de Y para cualquier combinación de valores de X1, X2 y X3.
Función de regresión logística:
Se utiliza para predecir un resultado binario, como por ejemplo, si una persona va a comprar un producto o no. Esta función es útil para entender mejor las relaciones entre variables y predecir resultados.
La regresión logística en Excel se usa para predecir los resultados de una variable binaria (por ejemplo, si un cliente compra un producto o no) a partir de los valores de otras variables.
Por ejemplo, supongamos que se desea predecir si un cliente comprará un producto basándose en su edad, ingreso y estado civil. Para ello, se usará la regresión logística.
Pasos para realizar la regresión logística en Excel:
1. En la hoja de cálculo, ingresa los datos de edad, ingreso y estado civil de los clientes.
2. Crea una columna para almacenar los resultados de la variable binaria (compra del producto o no).
3. Asegúrate de que los datos sean numéricos, si es necesario, convierte a valores numéricos los datos de estado civil.
4. Selecciona las celdas con los datos de edad, ingreso y estado civil.
5. Haz clic en «Datos» y luego en «Análisis de datos». Seleccione «Regresión» y luego haz clic en «Aceptar».
6. En la ventana emergente, selecciona los campos de edad, ingreso y estado civil como variables de entrada y selecciona la variable binaria (compra del producto o no) como variable de salida.
7. Haz clic en el botón «Aceptar» para ejecutar la regresión logística.
8. Revisa los resultados para ver si la regresión logística ha logrado predecir con éxito los resultados de la variable binaria.
Función de regresión no lineal:
Se usa para encontrar la mejor línea de ajuste para un conjunto de datos con una variable no lineal. Esta línea de ajuste puede ser utilizada para predecir un resultado dado cierta información.
Para ilustrar una función de regresión no lineal en Excel, primero debe recopilar los datos en una hoja de cálculo.
Por ejemplo, si se estamos midiendo el nivel de aceite en un motor en función del número de horas que ha funcionado, los datos se enumeran como sigue:
Número de horas Nivel de aceite:
1 ……………………. 2
2 ……………………. 4
3 ……………………. 6
4 ……………………. 8
5 ……………………. 10
A continuación, se crea un gráfico de dispersión con estos datos para ver la tendencia. Después de hacer esto, se puede usar la función de regresión no lineal para encontrar la mejor curva de ajuste para los datos. La función de regresión no lineal se encuentra en el cuadro de herramientas de datos en Excel.
Para seleccionar una curva de ajuste, primero se necesita seleccionar una función para ajustar a los datos. Por ejemplo, la función de regresión no lineal para esta situación puede ser una curva polinomial de segundo grado. Luego, se seleccionan los parámetros para el ajuste.
En este caso, los parámetros son el coeficiente de la x2 término, el coeficiente del término x, el coeficiente constante y el valor de R2. Una vez que se han seleccionado los parámetros, se hace clic en el botón «Aceptar» para ajustar la curva a los datos.
Una vez que se ha ajustado la curva, se puede ver en el gráfico cómo se ajusta a los datos. Además, se pueden usar los parámetros para predecir el nivel de aceite para cualquier número de horas.
Por ejemplo, para predecir el nivel de aceite después de 10 horas, se pueden usar los parámetros para calcular que el nivel de aceite será de aproximadamente 18.
Función de regresión logística multinomial:
Se utiliza para predecir un resultado con múltiples categorías, como por ejemplo, si una persona comprará un producto de una categoría específica. Esta función es útil para entender mejor las relaciones entre variables y predecir resultados.
Se puede usar para predecir la probabilidad de que una variable cualitativa (categórica) dependiente adopte uno de varios valores.
Por ejemplo, se puede usar para predecir el género de una persona basada en sus edad, su estado civil y su ingreso.
Aquí hay un ejemplo de una regresión logística multinomial en Excel:
1. Primero, reúna sus datos en una hoja de cálculo. En esta prueba, tenemos los datos sobre la edad, el estado civil y el ingreso de 100 personas.
2. Una vez que tenga sus datos, vaya a la pestaña «Datos» en la parte superior de la pantalla.
3. Seleccione «Análisis de datos» y luego «Regresión logística multinomial».
4. Luego, seleccione la columna que contendrá los datos de la categoría dependiente (en este caso, el género).
5. A continuación, seleccione las columnas que contengan los datos de las variables independientes (en este caso, edad, estado civil y ingreso).
6. Haga clic en «Aceptar» para ver sus resultados.
Los resultados mostrarán la probabilidad de que una persona sea hombre o mujer en función de su edad, su estado civil y su ingreso.
Función de regresión de Poisson:
Se usa para predecir la probabilidad de un resultado dado cierta información. Esta función es útil para entender mejor las relaciones entre variables y predecir resultados.
En Excel, se puede realizar utilizando la función REGLIN.POISSON. Esta función devuelve los parámetros de regresión necesarios para estimar la relación entre una variable dependiente y una o más variables independientes.
Por ejemplo, supongamos que desea estimar la relación entre el número de accidentes de tráfico en una ciudad y el número de vehículos circulando. Se recopilan los siguientes datos:
Número de vehículos circulando (X): 1000, 2000, 3000, 4000
Número de accidentes de tráfico (Y): 10, 20, 30, 40
En Excel, la función REGLIN.POISSON se puede usar de la siguiente manera:
=REGLIN.POISSON(Y;X)
En este caso, la función devuelve los siguientes parámetros de regresión:
• Intervalo de confianza (IC): 0,897
• Coeficiente de regresión (b): 0,01
• Error estándar (SE): 0,00281
• Estadístico t: 3,541
• P-valor: 0,0020
Estos resultados indican que existe una relación significativa entre el número de vehículos circulando y el número de accidentes de tráfico.
Además, el coeficiente de regresión indica que se espera un aumento del 1% en el número de accidentes de tráfico por cada aumento del 1% en el número de vehículos circulando.
Función de regresión de Cox:
Se utiliza para analizar los efectos de múltiples factores sobre un resultado.
Esta función es útil para entender mejor las relaciones entre variables y predecir resultados.
Por ejemplo, la siguiente es una regresión de Cox en Excel para predecir el riesgo de enfermedad cardiovascular en una población determinada. Se utiliza una tabla con dos variables: el nivel de colesterol total (TC) y el índice de masa corporal (IMC):
TC IMC Cox_Hazard_Ratio
200 30 1.08
220 30 1.12
200 25 1.03
220 25 1.07
Para calcular la regresión de Cox, primero debe ingresar los datos en una hoja de cálculo. A continuación, se usa la función «COXREG» para calcular la regresión de Cox. La sintaxis para esta función es COXREG(datos, riesgo, [opciones]).
En este caso, los datos se ingresan como una matriz de dos columnas (TC y IMC) y el riesgo se establece como «Cox_Hazard_Ratio». La regresión de Cox produce los siguientes resultados:
Coefficiente de regresión de Cox:
TC: 0.0078
IMC: 0.0891
Intercepto:
-3.0902
Esto significa que un aumento de 1 unidad en el nivel de colesterol total aumenta el riesgo de enfermedad cardiovascular en 0.0078 unidades, mientras que un aumento de 1 unidad en el IMC aumenta el riesgo de enfermedad cardiovascular en 0.0891 unidades.
El intercepto de -3.0902 indica que el riesgo basal de enfermedad cardiovascular en esta población es de 0.0497 (1/exp (-3.0902)).
Función de análisis de regresión generalizada:
Se usa para encontrar la mejor línea de ajuste para un conjunto de datos con múltiples variables relacionadas. Esta línea de ajuste puede ser utilizada para predecir un resultado dado cierta información.
Un ejemplo de función de regresión generalizada en Excel es la función LOGEST. Esta función calcula los valores ajustados de los parámetros de una regresión exponencial de los datos de dos variables, x e y. Esta función toma los siguientes argumentos:
• Valores_Y: La matriz o el rango de celdas que contienen los valores y (la variable dependiente).
• Valores_X: La matriz o el rango de celdas que contienen los valores x (la variable independiente).
• Constante: Un valor lógico (verdadero o falso) que especifica si se debe ajustar una constante en la regresión exponencial.
• Estimación: Un valor lógico (verdadero o falso) que especifica si se debe realizar una estimación para los parámetros de la regresión.
Por ejemplo, podemos usar la función LOGEST para calcular los parámetros de una regresión exponencial para los siguientes datos:
x y
2 5
4 7
6 11
8 15
Para calcular los parámetros de la regresión, primero debemos seleccionar un rango de celdas para los datos de x (en este caso, desde la celda A2 hasta la A5) y un rango de celdas para los datos de y (en este caso, desde la celda B2 hasta la B5). Luego, en una celda, ingresamos la siguiente fórmula:
=LOGEST(Valores_Y, Valores_X, Constante, Estimación)
En este caso, la fórmula sería:
=LOGEST(B2:B5, A2:A5,FALSO, VERDADERO)
Al presionar Enter, obtendríamos los parámetros ajustados de la regresión exponencial, como se muestra a continuación:
Intercepto (a): 2,45
Exponente (b): 1,40
Coeficiente de determinación (R²): 0,95
Función de análisis de agrupamiento jerárquico:
Se utiliza para agrupar datos similares en grupos jerárquicos. Esto es útil para encontrar patrones ocultos dentro de un conjunto de datos.
Permite identificar patrones de datos algorítmicamente. Esta función es útil para agrupar datos en grupos más grandes basados en similitudes. Esto puede ser útil para identificar patrones en grandes conjuntos de datos.
Por ejemplo, para analizar los consumos de electricidad de una lista de clientes, se puede usar la función de Análisis de Agrupamiento Jerárquico para agrupar los clientes según sus consumos.
Esto permitirá a los gestores de cuentas identificar patrones de consumo entre los clientes y proporcionar mejoras para satisfacerlas.
Para utilizar esta función en Excel, primero debe ingresar todos los datos en una hoja de cálculo. Una vez que los datos estén ingresados, seleccione la celda donde desea que se muestre el resultado.
Luego, seleccione “Análisis de Datos” desde la pestaña “Herramientas de Análisis” y luego seleccionar “Análisis de Agrupamiento Jerárquico”.
A continuación, seleccione la celda con los datos que desea agrupar y luego haga clic en “Aceptar”. El resultado se mostrará en la celda seleccionada.
Función de análisis de componentes principales múltiples (ACP):
Ae usa para reducir la dimensionalidad de un conjunto de datos con múltiples variables y encontrar patrones ocultos en los datos. Esto es útil para entender mejor los datos y encontrar relaciones entre variables.
Se puede usar para reducir los datos de varios factores a un número más pequeño de factores. Esta herramienta es útil para identificar los factores subyacentes entre varias variables.
Para usar la función de análisis de componentes principales múltiples en Excel, primero debe abrir el archivo de Excel y seleccionar la opción «Análisis de datos» en la ficha «Datos» en la cinta de opciones.
Una vez que haga clic en esta opción, aparecerá un cuadro de diálogo donde puede seleccionar la opción «Análisis de componentes principales». Haga clic en esta opción para abrir el asistente para el Análisis de componentes principales.
En el asistente, debe seleccionar los Factores de entrada, que son variables que se utilizan para analizar los datos. Debe seleccionar el Rango de datos y asegurarse de que los datos estén en una sola columna. Una vez que haya seleccionado los factores de entrada, haga clic en el botón «Siguiente» para pasar al siguiente paso.
En el siguiente paso, debe seleccionar el número de componentes principales que desea analizar. Esto determinará el número de factores subyacentes que se van a estudiar. Una vez que haya seleccionado el número de componentes principales, haga clic en el botón «Siguiente» para pasar al siguiente paso.
En el último paso, debe seleccionar la ubicación donde se guardarán los resultados del análisis. Una vez que haya seleccionado la ubicación, haga clic en el botón «Finalizar» para iniciar el análisis.
El análisis de componentes principales múltiples en Excel generará un informe con los resultados del análisis. El informe contiene gráficos y tablas que muestran los factores subyacentes entre las variables.
Función de análisis factorial:
Se utiliza para agrupar variables relacionadas en factores clave. Esto es útil para entender mejor las relaciones entre variables y encontrar patrones en los datos.
Nos permite identificar patrones y relaciones ocultas entre variables de datos. Esta función se puede utilizar para descubrir tendencias, patrones y correlaciones entre variables.
Un ejemplo de uso de la función de análisis factorial en excel es el siguiente: supongamos que tenemos una base de datos que contiene información sobre el nivel de satisfacción de los clientes con nuestros productos.
Esta base de datos contiene variables tales como la edad, el género, el nivel educativo, el lugar de residencia, el ingreso promedio, etc.
Utilizando la función de análisis factorial en Excel, podemos agrupar estas variables en componentes factoriales y descubrir patrones que no eran obvios antes.
Por ejemplo, podemos descubrir que los clientes jóvenes con un nivel educativo alto y un ingreso promedio alto tienen un mayor nivel de satisfacción con nuestros productos.
Esta información nos ayuda a mejorar nuestros productos y servicios y aumentar la satisfacción de nuestros clientes.
Enlaces relacionados: funciones estadísticas de Excel
- Usar las Herramientas para análisis para realizar análisis de datos complejos en https://support.microsoft.com.
Bibliografía relacionada: aplicaciones estadísticas con Excel
Aplicaciones Estadísticas usando MS Excel con ejemplos paso a paso. Libro de Estadística básica e intermedia, para estudiantes universitarios y de educación superior.
Empleando las capacidades de aplicativo MS Excel (c), brinda un gran número de ejemplos que sirven para muchas disciplinas tales como la Economía, la Administración de Negocios, las Finanzas, las Ingenierías, la Biología, la Psicología, la Sociología y el Derecho.
ESTADÍSTICA DESCRIPTIVA CON MS EXCEL. Carrascal (Spanish) 2010.
