¿Cómo calcular el coeficiente de correlación entre dos variables en Excel?

Usualmente utilizamos el coeficiente de correlación (un valor que va de -1 a 1) para indicar la fuerza y dirección de la relación lineal entre dos variables. Un coeficiente de correlación es una estadística ampliamente usada que te ayuda a entender relaciones, como la conexión entre ventas y gastos en publicidad, temperatura y ventas de helados, u otros datos emparejados. En Excel, hay múltiples métodos sencillos para calcular el coeficiente de correlación, incluyendo funciones integradas y herramientas de análisis.

Nota: Un coeficiente de correlación de +1 muestra una relación lineal positiva perfecta, lo que significa que a medida que la variable X aumenta, la variable Y también sube; similarmente, cuando X disminuye, Y baja. Por el contrario, un valor de -1 muestra una correlación negativa perfecta, así que cuando X aumenta, Y disminuye, y viceversa. Un coeficiente cercano a 0 sugiere poca o ninguna relación lineal entre las variables.

Método A: Usar directamente la función 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 CORREL

Considera dos listas de datos, cada una representando una variable. Si deseas calcular el coeficiente de correlación entre estas dos variables en Excel, este método es rápido y eficiente.

Para un uso práctico, asegúrate de que ambos rangos de datos sean numéricos y contengan el mismo número de observaciones. Por ejemplo, si tienes los siguientes datos emparejados:

Selecciona una celda en blanco donde desees mostrar el resultado del cálculo. Ingresa la siguiente fórmula, y luego presiona la tecla "Enter" para calcular 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 deseas analizar. Los rangos deben tener la misma longitud, y cada par debe corresponder a la misma observación.

Consejo práctico: CORREL ignora automáticamente las celdas vacías y el texto, pero si no hay pares numéricos válidos en las dos columnas, devolverá un error #¡DIV/0!. Asegúrate de que tus datos estén alineados correctamente y contengan pares numéricos para un cálculo preciso de la correlación.

Una vez que hayas calculado el coeficiente de correlación, puedes insertar un gráfico de líneas para observar visualmente las relaciones e interpretar mejor la correlación, como se muestra a continuación:

Este método es ideal para verificaciones rápidas y manuales entre dos conjuntos de datos pequeños o cuando trabajas interactivamente dentro de tu hoja de cálculo. Es adecuado para usuarios que buscan un resultado inmediato sin necesidad de un análisis estadístico avanzado.

Descubre la Magia de Excel con Kutools AI

• Ejecución Inteligente: Realiza operaciones en celdas, analiza datos y crea gráficos, todo impulsado por comandos simples.
• Fórmulas Personalizadas: Genera fórmulas adaptadas para optimizar tus flujos de trabajo.
• Codificación VBA: Escribe e implementa código VBA sin esfuerzo.
• Interpretación de Fórmulas: Comprende fórmulas complejas con facilidad.
• Traducción de Texto: Supera las barreras del idioma dentro de tus hojas de cálculo.

Potencia tus capacidades de Excel con herramientas impulsadas por IA. ¡Descarga Ahora y experimenta una eficiencia como nunca antes!

Método B: Aplicar Análisis de Datos y generar el análisis

Si necesitas analizar la correlación entre múltiples variables de una sola vez o deseas una tabla de resultados más completa, el complemento “Herramientas para análisis” de Excel proporciona una solución útil. Este complemento genera una matriz de correlación y te permite comparar varias variables en un solo paso, lo que es valioso para conjuntos de datos grandes o para crear informes estadísticos.

1. Si ya has agregado el complemento Análisis de Datos a la pestaña Datos, puedes saltar al paso 3. De lo contrario, haz clic en Archivo > Opciones. En el cuadro de diálogo "Opciones de Excel", selecciona Complementos en el panel izquierdo, y luego haz clic en el botón Ir junto al cuadro "Complementos de Excel".

2. En el cuadro de diálogo "Complementos", marca la casilla etiquetada Herramientas para análisis, luego haz clic en Aceptar. Esto agregará el grupo "Análisis de Datos" a la pestaña Datos .

3. Luego, haz clic en Datos > Análisis de Datos. En el cuadro emergente “Análisis de Datos”, selecciona Correlación de la lista y luego haz 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", dependiendo de cómo estén organizados tus datos.
3) Si tus datos incluyen encabezados, marca la opción “Etiquetas en la primera fila”.
4) Especifica una ubicación de salida en “Opciones de salida” para mostrar los resultados.

5. Haz clic en Aceptar para generar la tabla de análisis de correlación. Los coeficientes de correlación se presentarán en el rango especificado.

Este método es adecuado cuando necesitas evaluar las relaciones entre más de dos variables o deseas una tabla resumen para propósitos de reporte. La salida de Análisis de Datos es concisa, pero no proporciona estadísticas adicionales de significancia. Si obtienes resultados inesperados, verifica tus datos en busca de consistencia, celdas vacías y selección correcta de rangos.

Método C: Usar la función PEARSON como alternativa

Además de CORREL, Excel proporciona la función PEARSON, que también calcula el coeficiente de correlación de Pearson entre dos variables. Funcionalmente, PEARSON y CORREL devuelven el mismo resultado. Sin embargo, PEARSON sigue estrictamente la fórmula matemática original, mientras que CORREL está optimizada para el entorno de Excel. Si estás acostumbrado a la teoría estadística o trabajas con herramientas estadísticas fuera de Excel, PEARSON podría resultar más familiar.

Por ejemplo, con dos listas numéricas en A2:A7 y B2:B7, puedes calcular la correlación de la siguiente manera:

1. Selecciona una celda donde quieras mostrar el resultado, e ingresa esta fórmula:

=PEARSON(A2:A7,B2:B7)

2. Presiona Enter para completar el cálculo. Si deseas analizar pares adicionales de datos, ajusta los rangos de celdas según sea necesario, o arrastra la fórmula a otras celdas.
Consejos: PEARSON ignora el texto o los valores lógicos, así que asegúrate de que ambos rangos contengan únicamente valores numéricos y tengan la misma longitud. Si hay datos faltantes en una columna, alinea tus rangos en consecuencia para evitar errores.

Usar PEARSON es especialmente práctico para usuarios que migran desde otro software estadístico, o en entornos académicos donde se espera una estricta adhesión a la terminología. Tanto CORREL como PEARSON dan el mismo resultado para casos de uso típicos en Excel.

Si encuentras un error #¡DIV/0!, verifica que ambos rangos tengan la misma longitud y no contengan celdas vacías o no numéricas desiguales.

Ventajas: Fácil de usar, consistente con el software estadístico; Desventajas: No ofrece diferencias significativas 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 necesitas automatizar el cálculo de coeficientes de correlación para varios pares de datos (por ejemplo, al trabajar con muchas combinaciones de variables), escribir una macro VBA simple es una opción eficiente. Este método es adecuado para usuarios avanzados que desean procesar grandes volúmenes de datos o automatizar tareas repetitivas de análisis.

1. Para usar este método, primero abre el editor VBA haciendo clic en Desarrollador > Visual Basic. En la ventana de Visual Basic para Aplicaciones, ve a Insertar > Módulo, y luego pega 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 Sub

2. Después de insertar el código, cierra el editor VBA. En Excel, presiona Alt + F8, selecciona BatchCalculateCorrelations, y haz clic en Ejecutar. Se te pedirá que selecciones:

• El primer rango de variables (una sola columna, por ejemplo, A2:A7)
• El segundo rango de variables (una o más columnas, por ejemplo, B2:D7)
• La celda donde deseas que comiencen los resultados (por ejemplo, F2)

La macro entonces calcula el coeficiente de correlación entre la primera variable y cada columna en el segundo rango, mostrando los resultados horizontalmente a partir de la celda elegida.

Ventajas: Automatiza cálculos repetitivos, ahorra tiempo significativo con grandes volúmenes de datos y asegura consistencia.

Si encuentras problemas como “Los dos rangos de datos deben tener el mismo número de filas”, asegúrate de que todas las columnas seleccionadas compartan exactamente el mismo recuento de filas y no tengan filas en blanco. Para la resolución de errores, verifica si las macros están habilitadas y los rangos están seleccionados correctamente.

Al trabajar con coeficientes de correlación en Excel, elegir el método adecuado depende de la estructura de tus datos y las necesidades de análisis. Para cálculos rápidos entre dos series, fórmulas como CORREL o PEARSON son eficientes y fáciles de usar. Para múltiples variables o la necesidad de tablas resumen, Herramientas para análisis es muy práctica. Si requieres análisis repetido en grandes volúmenes de datos o flujos de trabajo personalizados, considera automatizar con VBA para ahorrar tiempo y reducir errores humanos.
Asegúrate siempre de que tus rangos de datos estén alineados, limpios y no contengan celdas vacías o no numéricas para evitar errores en las fórmulas. Si encuentras resultados inesperados, revisa las selecciones y los tipos de datos.

Artículos Relacionados

• Calcular el cambio porcentual o diferencia entre dos números en Excel
  Este artículo trata sobre cómo calcular el cambio porcentual o la diferencia entre dos números en Excel.

• Calcular o asignar calificación con letras en Excel
  Asignar una calificación con letras a cada estudiante basándose en sus puntajes puede ser una tarea común para un profesor. Por ejemplo, tengo una escala de calificación definida donde 0-59 = F, 60-69 = D, 70-79 = C, 80-89 = B, y 90-100 = A, ver más.

• Calcular la tasa de descuento o precio en Excel
  Cuando se acerca la Navidad, seguramente habrá muchas promociones en los centros comerciales. Pero si los diferentes tipos de artículos tienen diferentes descuentos, ¿cómo puedes calcular las tasas de descuento o precios de los diferentes artículos?

• Contar el número de días / días laborables / fines de semana entre dos fechas en Excel
  Quizás, en algunas ocasiones, solo quieres calcular los días laborables entre dos fechas, y en otras ocasiones, necesitas contar los días de fin de semana únicamente entre las dos fechas.