¿Cómo se calcula el coeficiente de correlación entre dos variables en Excel?
Normalmente utilizamos el coeficiente de correlación —un valor comprendido entre -1 y 1— para indicar la fuerza y la dirección de la relación lineal entre dos variables. Se trata de una estadística ampliamente utilizada que le ayuda a comprender relaciones clave, como la conexión entre ventas y gasto publicitario, temperatura y ventas de helados, u otros datos emparejados. En Excel, puede calcular fácilmente este coeficiente mediante varios métodos sencillos, incluidas funciones integradas y herramientas de análisis.
Método A: Usar directamente la función COEF.DE.CORREL
Método B: Aplicar Análisis de datos y generar el análisis
Método C: Usar la función PEARSON como alternativa
Método D: Usar código VBA para calcular coeficientes de correlación para múltiples pares
Método A: Usar directamente la función COEF.DE.CORREL
Considere dos listas de datos, cada una representando una variable. Si desea calcular el coeficiente de correlación entre ambas en Excel, este método es rápido y eficaz.
Para un uso práctico, asegúrese de que ambos rangos sean numéricos y contengan el mismo número de observaciones. Por ejemplo, si tiene los siguientes datos emparejados:
Seleccione una celda vacía donde desee mostrar el resultado del cálculo, introduzca la siguiente fórmula y pulse la tecla **Intro** para obtener el coeficiente de correlación.
=CORREL(A2:A7,B2:B7)
En esta fórmula, A2:A7 y B2:B7 representan las dos listas de variables que desea analizar. Ambos rangos deben tener la misma longitud, y cada par debe corresponder a una misma observación.
Consejo práctico: COEF.DE.CORREL ignora automáticamente las celdas vacías y el texto, pero si no hay pares numéricos válidos en ambas columnas, devolverá un error #¡DIV/0!. Asegúrese de que sus datos estén correctamente alineados y contengan pares numéricos para obtener un cálculo de correlación preciso.
Una vez que haya calculado el coeficiente de correlación, puede insertar un gráfico de líneas para observar visualmente las relaciones e interpretar mejor la correlación, tal como se muestra a continuación:
Este método es ideal para comprobaciones rápidas y manuales entre dos conjuntos pequeños de datos o cuando trabaja de forma interactiva en su hoja de cálculo. Resulta perfecto para usuarios que buscan un resultado inmediato sin necesidad de análisis estadísticos avanzados.
Descubra la magia de Excel con KUTOOLS AI
- Ejecución inteligente: Realice operaciones en celdas, analice datos y cree gráficos con comandos sencillos.
- fórmulas personalizadas: Cree fórmulas a medida para optimizar sus flujos de trabajo.
- Programación en VBA: Escriba e implemente código VBA con facilidad.
- Interpretación de fórmulas: Entienda las fórmulas complejas con facilidad.
- Traducción de texto: Rompa las barreras del idioma directamente en sus hojas de cálculo.
Método B: Aplicar Análisis de datos y generar el análisis
Si necesita analizar la correlación entre varias variables simultáneamente o prefiere obtener una tabla de resultados más completa, el complemento «Herramientas para análisis» de Excel es una solución muy útil. Este complemento genera una matriz de correlación y le permite comparar múltiples variables en un solo paso, lo que resulta especialmente valioso al trabajar con conjuntos de datos extensos o al elaborar informes estadísticos. 1. Si ya ha agregado el complemento Análisis de datos a la pestaña Datos, puede pasar directamente al paso 3. De lo contrario, haga clic en Archivo > Opciones. En el cuadro de diálogo «Opciones de Excel», seleccione Complementos en el panel izquierdo y, a continuación, haga clic en el botón Ir situado junto al campo «Complementos de Excel».
1. Si ya has agregado el complemento Análisis de datos a la pestaña Datos, puedes pasar directamente al paso 3. Si no, haz clic en Archivo > Opciones. En el cuadro de diálogo «Opciones de Excel», selecciona Complementos en el panel izquierdo y, a continuación, haz clic en el botón Ir situado junto al cuadro «Complementos de Excel».
2. En el cuadro de diálogo «Complementos», active la casilla etiquetada como Herramientas para análisis y, a continuación, haga clic en Aceptar. Así se añadirá el grupo «Análisis de datos» a la pestaña Datos.
3. A continuación, haga clic en Datos > Análisis de datos. En el cuadro de diálogo emergente «Análisis de datos», seleccione Correlación de la lista y, a continuación, haga clic en Aceptar.
4. En el cuadro de diálogo Correlación, configura lo siguiente:
1) Selecciona el rango que contiene tus datos.
2) Elige la opción «Columnas» o «Filas», según cómo estén organizados tus datos.
3) Si tus datos incluyen encabezados, marca la casilla «Etiquetas en la primera fila».
4) Indica una ubicación de salida en «Opciones de salida» para mostrar los resultados.
5. Haga clic en Aceptar para generar la tabla de análisis de correlación. Los coeficientes de correlación se mostrarán en el rango especificado.
Este método es adecuado cuando necesita evaluar las relaciones entre más de dos variables o desea una tabla resumen con fines informativos. La salida de Análisis de datos es concisa, pero no proporciona estadísticas de significación adicionales. Si obtiene resultados inesperados, verifique nuevamente la coherencia de sus datos, la presencia de Celdas vacías y la selección correcta del rango.
Método C: Usar la función PEARSON como alternativa
Además de COEF.DE.CORREL, Excel ofrece la función PEARSON, que también calcula el coeficiente de correlación de Pearson entre dos variables. Ambas funciones, PEARSON y COEF.DE.CORREL, devuelven exactamente el mismo resultado. Sin embargo,PEARSON sigue estrictamente la fórmula matemática original, mientras que COEF.DE.CORREL está optimizada para el entorno de Excel. Si estás familiarizado con la teoría estadística o trabajas con herramientas estadísticas fuera de Excel, es probable que PEARSONte resulte más familiar.
Por ejemplo, con dos listas numéricas en A2:A7 y B2:B7, puede calcular la correlación de la siguiente manera:
1. Seleccione una celda donde quiera mostrar el resultado e introduzca esta fórmula:
=PEARSON(A2:A7,B2:B7)2. Pulse Intro para completar el cálculo. Si desea analizar pares adicionales de datos, ajuste los rangos de celdas en consecuencia o arrastre la fórmula a otras celdas.
Consejos: PEARSON ignora los valores de texto o lógicos, así que asegúrese de que ambos rangos contengan únicamente valores numéricos y tengan la misma longitud. Si falta algún dato en una columna, ajuste sus rangos en consecuencia para evitar errores.
Usar PEARSON resulta especialmente práctico para usuarios que migran desde otro software estadístico o en contextos académicos donde se espera un rigor terminológico estricto. Tanto COEF.DE.CORREL como PEARSON ofrecen el mismo resultado en casos de uso habituales en Excel.
Si recibe un error #DIV/0!, compruebe que ambos rangos tengan la misma longitud y no contengan celdas vacías o no numéricas sin correspondencia.
Ventajas: Fácil de usar y coherente con el software estadístico.Inconvenientes: No ofrece ninguna diferencia significativa respecto a COEF.DE.CORREL para la mayoría de los usuarios.
Método D: Usar código VBA para calcular coeficientes de correlación para múltiples pares
Si necesita automatizar el cálculo de coeficientes de correlación para varios pares de datos (por ejemplo, al trabajar con numerosas combinaciones de variables), escribir una macro sencilla en VBA es una opción eficaz. Este método es ideal para usuarios avanzados que desean procesar grandes conjuntos de datos o automatizar tareas de análisis repetitivas.
1. Para utilizar este método, primero abra el editor de VBA haciendo clic en Programador > Visual Basic. En la ventana de Visual Basic para Aplicaciones, vaya a Insertar > Módulo y, a continuación, pegue el siguiente código en el módulo:
Sub BatchCalculateCorrelations()
Dim ws As Worksheet
Dim rng1 As Range, rng2 As Range
Dim lastRow As Long
Dim i As Long
Dim resultCol As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
Set rng1 = Application.InputBox("Select first variable range (single column)", xTitleId, Type:=8)
Set rng2 = Application.InputBox("Select second variable range (multiple columns)", xTitleId, Type:=8)
Set resultCol = Application.InputBox("Select starting cell for output", xTitleId, Type:=8)
If rng1.Rows.Count <> rng2.Rows.Count Then
MsgBox "The two data ranges must have the same number of rows.", vbCritical, xTitleId
Exit Sub
End If
For i = 1 To rng2.Columns.Count
resultCol.Cells(1, i).Value = "Correlation with " & rng2.Cells(1, i).EntireColumn.Column
resultCol.Cells(2, i).Value = WorksheetFunction.Correl(rng1, rng2.Columns(i))
Next i
End Sub2. Tras insertar el código, cierre el editor de VBA. En Excel, pulse Alt + F8, seleccione BatchCalculateCorrelations y haga clic en Ejecutar. Se le pedirá que seleccione:
- El primer rango de variables (una única columna, p. ej., A2:A7)
- El segundo rango de variables (una o más columnas, p. ej., B2:D7)
- La celda en la que desea que comiencen los resultados (p. ej., F2)
La macro calcula entonces el coeficiente de correlación entre la primera variable y cada columna del segundo rango, mostrando los resultados horizontalmente desde la celda elegida.
Ventajas: automatiza cálculos repetitivos, ahorra tiempo considerable con conjuntos de datos grandes y garantiza coherencia.
Si encuentra problemas como «Los dos Rango deben tener el mismo número de filas», asegúrese de que todas las columnas seleccionadas tengan exactamente el mismo número de filas y no contengan Filas en blanco. Para solucionar errores, verifique que las macros estén habilitadas y que los rangos se hayan seleccionado correctamente.
Al trabajar con coeficientes de correlación en Excel, la elección del método adecuado depende de la estructura de sus datos y de sus necesidades de análisis. Para cálculos puntuales y rápidos entre dos series, fórmulas como CORREL o PEARSON son eficientes y sencillas de usar. Para múltiples variables o cuando necesite tablas resumen, el complemento Herramientas para el análisis resulta muy práctico. Si requiere realizar análisis repetidos en grandes conjuntos de datos o desea flujos de trabajo personalizados, considere automatizar con VBA para ahorrar tiempo y reducir errores humanos.
Asegúrese siempre de que sus rangos estén alineados, limpios y no contengan celdas vacías ni valores no numéricos, ya que esto podría provocar errores en las fórmulas. Si obtiene resultados inesperados, revise cuidadosamente las selecciones y los tipos de datos.
Artículos relacionados
- Calcular el cambio o la diferencia porcentual entre dos números en Excel
Este artículo explica cómo calcular el cambio o la diferencia porcentual entre dos números en Excel.
- Calcular o asignar una calificación con letras en Excel
Asignar una calificación con letras a cada estudiante según su puntuación es una tarea habitual para los profesores. Por ejemplo, tengo una escala de calificaciones definida en la que las puntuaciones de 0 a 59 equivalen a F, de 60 a 69 a D, de 70 a 79 a C, de 80 a 89 a B y de 90 a 100 a A; consulte más información.
- Calcular la tasa de descuento o el precio en Excel
Con la Navidad a la vuelta de la esquina, los centros comerciales se llenan de promociones. Pero si cada artículo tiene un descuento distinto, ¿cómo puedes calcular rápidamente las tasas de descuento o los precios finales de todos ellos?
- Contar el número de días, días laborables o fines de semana entre dos fechas en Excel
A veces solo querrás calcular los días laborables entre dos fechas; en otras ocasiones, necesitarás contar exclusivamente los fines de semana comprendidos entre ambas.
Las mejores herramientas de productividad para Office
Potencie sus habilidades en Excel con Kutools para Excel y experimente una eficiencia como nunca antes.Kutools para Excel ofrece más de 300 funciones avanzadas para aumentar su productividad y Ahorrar tiempo.Haga clic aquí para obtener la función que más necesita...
Office Tab aporta una interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Active la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abra y cree varios documentos en nuevas pestañas dentro de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumente su productividad en un 50 % y elimine cientos de clics del ratón cada día!
Todos los complementos de Kutools en un solo instalador.
Kutools for Office es la suite que incluye complementos para Excel, Word, Outlook y PowerPoint, además de Office Tab Pro, ideal para equipos que trabajan en distintas aplicaciones de Office.
- Suite integral— complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un instalador, una licencia— configuración en minutos (compatible con MSI)
- Rendimiento mejorado en conjunto— productividad optimizada en todas las aplicaciones de Office
- Prueba gratuita de 30 días con todas las funciones— sin registro ni tarjeta de crédito
- La mejor relación calidad-precio— ahorre frente a la compra individual de complementos