Excel에서 두 변수 간의 상관 계수를 계산하는 방법은 무엇입니까?

우리는 일반적으로 상관 계수(값이 -1에서 1 사이인)를 사용하여 두 변수 간 선형 관계의 강도와 방향을 나타냅니다. 상관 계수는 판매와 광고 지출, 온도와 아이스크림 판매 또는 기타 쌍으로 이루어진 데이터 간의 연결과 같은 관계를 이해하는 데 도움이 되는 널리 사용되는 통계입니다. Excel에서는 상관 계수를 계산하기 위한 여러 가지 간단한 방법이 있으며, 여기에는 내장 함수와 분석 도구가 포함됩니다.

참고: 상관 계수가 +1이면 완벽한 양의 선형 관계를 나타내며, 이는 변수 X가 증가하면 변수 Y도 증가하고, X가 감소하면 Y도 감소한다는 것을 의미합니다. 반대로 값이 -1이면 완벽한 음의 상관 관계를 나타내므로 X가 증가하면 Y는 감소하고, 그 반대도 마찬가지입니다. 계수가 0에 가까우면 변수 간에 거의 선형 관계가 없다는 것을 나타냅니다.

방법 A: CORREL 함수 직접 사용

방법 B: 데이터 분석 적용 및 분석 결과 출력

방법 C: PEARSON 함수를 대안으로 사용

방법 D: VBA 코드를 사용하여 여러 쌍에 대한 상관 계수 계산

방법 A: CORREL 함수 직접 사용

각각 하나의 변수를 나타내는 두 개의 데이터 목록이 있다고 가정해 봅시다. Excel에서 이 두 변수 간의 상관 계수를 계산하려는 경우, 이 방법은 빠르고 효율적입니다.

실제 사용 시, 두 데이터 범위가 모두 숫자여야 하며 동일한 수의 관측치를 포함해야 합니다. 예를 들어 다음 쌍의 데이터가 있는 경우:

계산 결과를 표시하려는 빈 셀을 선택하세요. 다음 공식을 입력한 후 'Enter' 키를 눌러 상관 계수를 계산합니다:

=CORREL(A2:A7,B2:B7)

이 공식에서 A2:A7과 B2:B7은 분석하려는 두 개의 변수 목록을 나타냅니다. 범위는 길이가 같아야 하며, 각 쌍은 동일한 관측치에 해당해야 합니다.

실용적인 팁: CORREL은 자동으로 빈 셀과 텍스트를 무시하지만, 두 열에 유효한 숫자 쌍이 없는 경우 #DIV/0! 오류를 반환합니다. 정확한 상관 계산을 위해 데이터가 적절히 정렬되어 있고 숫자 쌍을 포함하고 있는지 확인하세요.

상관 계수를 계산한 후 아래와 같이 선 그래프를 삽입하여 관계를 시각적으로 관찰하고 상관 관계를 추가로 해석할 수 있습니다:

이 방법은 두 개의 작은 데이터 세트 간에 빠른 수동 검사를 하거나 스프레드시트 내에서 대화식으로 작업할 때 가장 적합합니다. 고급 통계 출력 없이 즉각적인 결과를 원하는 사용자에게 적합합니다.

Kutools AI로 엑셀의 마법을 풀다

• 스마트 실행: 셀 작업 수행, 데이터 분석 및 차트 생성 - 간단한 명령어로 모든 것을 처리합니다.
• 사용자 정의 수식: 작업을 간소화하기 위한 맞춤형 수식을 생성합니다.
• VBA 코딩: 손쉽게 VBA 코드를 작성하고 실행합니다.
• 수식 해석: 복잡한 수식도 쉽게 이해할 수 있습니다.
• 텍스트 번역: 스프레드시트 내 언어 장벽을 허물어 보세요.

AI 기반 도구로 엑셀 활용 능력을 강화하세요. 지금 다운로드하여 그 어느 때보다 높은 효율성을 경험해 보세요!

방법 B: 데이터 분석 적용 및 분석 결과 출력

여러 변수 간의 상관 관계를 한 번에 분석하거나 더 포괄적인 출력 테이블이 필요한 경우, Excel의 '분석 도구 팩'이 유용한 솔루션을 제공합니다. 이 추가 기능은 상관 행렬을 생성하며, 여러 변수를 한 단계에서 비교할 수 있어 큰 데이터 세트나 통계 보고서 작성에 가치가 있습니다.

1. 이미 데이터 탭에 데이터 분석 추가 기능을 추가했다면 3단계로 건너뛸 수 있습니다. 그렇지 않으면 다음을 클릭하세요: 파일 > 옵션'Excel 옵션' 대화 상자에서 왼쪽 창에서 추가 기능 을 선택하고 'Excel 추가 기능' 상자 옆에 있는 이동 버튼을 클릭하세요.

2. '추가 기능' 대화 상자에서 분석 도구 팩확인란을 체크한 후 확인을 클릭하세요. 그러면 "데이터 분석" 그룹이 데이터 탭에 추가됩니다.

3. 다음으로 데이터 > 데이터 분석을 클릭하세요. '데이터 분석' 대화 상자가 나타나면 목록에서 상관 관계 를 선택하고 확인.

을 클릭하세요.
4. 상관 관계 대화 상자에서 다음을 설정하세요:
1) 데이터를 포함하는 범위를 선택하세요.
2) 데이터가 구성된 방식에 따라 '열' 또는 '행' 옵션을 선택하세요.
3) 데이터에 헤더가 포함된 경우 '첫 행에 레이블 있음' 옵션을 체크하세요.

4) 결과를 표시할 '출력 옵션'에서 출력 위치를 지정하세요. 확인 을 클릭하여 상관 분석 테이블을 생성합니다. 상관 계수는 지정된 범위에 표시됩니다.

이 방법은 두 개 이상의 변수 간 관계를 평가해야 하거나 보고 목적으로 요약 테이블이 필요한 경우에 적합합니다. 데이터 분석 출력은 간결하지만 추가적인 유의미한 통계는 제공하지 않습니다. 예상치 못한 결과를 받은 경우 데이터의 일관성, 빈 셀 및 올바른 범위 선택 여부를 다시 확인하세요.

방법 C: PEARSON 함수를 대안으로 사용

CORREL 외에도 Excel은 두 변수 간 Pearson 상관 계수를 계산하는 PEARSON 함수를 제공합니다. 기능적으로 PEARSON과 CORREL은 동일한 결과를 반환합니다. 그러나 PEARSON은 원래의 수학 공식을 엄격히 준수하고, CORREL은 Excel 환경에 최적화되어 있습니다. 통계 이론에 익숙하거나 Excel 외부의 통계 도구를 사용하는 경우 PEARSON이 더 익숙하게 느껴질 수 있습니다.

예를 들어, A2:A7과 B2:B7에 두 개의 숫자 목록이 있는 경우 다음과 같이 상관 관계를 계산할 수 있습니다:

1. 결과를 표시하려는 셀을 선택하고 다음 공식을 입력하세요:

=PEARSON(A2:A7,B2:B7)

2Enter를 눌러 계산을 완료합니다. 추가 데이터 쌍을 분석하려면 셀 범위를 적절히 조정하거나 공식을 다른 셀로 드래그하세요.
팁: PEARSON은 텍스트 또는 논리 값을 무시하므로 두 범위 모두 숫자 값만 포함하고 길이가 같아야 합니다. 한 열에 누락된 데이터가 있는 경우 오류를 방지하기 위해 범위를 적절히 정렬하세요.

PEARSON을 사용하는 것은 특히 다른 통계 소프트웨어에서 이전 중인 사용자나 용어 사용의 엄격한 준수가 요구되는 학문적 환경에서 실용적입니다. CORREL과 PEARSON은 Excel에서의 일반적인 사용 사례에서는 동일한 결과를 제공합니다.

#DIV/0! 오류가 발생하는 경우 두 범위의 길이가 동일하고 비어 있거나 숫자가 아닌 셀이 없는지 확인하세요.

장점: 사용하기 쉽고, 통계 소프트웨어와 일관됨; 단점: 대부분의 사용자에게 CORREL과 크게 다르지 않음.

방법 D: VBA 코드를 사용하여 여러 쌍에 대한 상관 계수 계산

여러 쌍의 데이터(예: 많은 변수 조합을 다룰 때)에 대해 상관 계수 계산을 자동화해야 하는 경우, 간단한 VBA 매크로를 작성하는 것이 효율적인 선택입니다. 이 방법은 대규모 데이터 세트 처리나 반복적인 분석 작업을 자동화하려는 고급 사용자에게 적합합니다.

1. 이 방법을 사용하려면 먼저 개발 도구 > Visual Basic을 클릭하여 VBA 편집기를 엽니다. Visual Basic for Applications 창에서 삽입 > 모듈로 이동한 후 다음 코드를 모듈에 붙여넣습니다:

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. 코드를 삽입한 후 VBA 편집기를 닫습니다. Excel에서 Alt + F8을 눌러 BatchCalculateCorrelations를 선택하고 실행을 클릭하세요. 그러면 다음을 선택하라는 메시지가 표시됩니다:

• 첫 번째 변수 범위 (단일 열, 예: A2:A7)
• 두 번째 변수 범위 (한 개 이상의 열, 예: B2:D7)
• 결과를 시작할 셀 (예: F2)

매크로는 첫 번째 변수와 두 번째 범위의 각 열 간의 상관 계수를 계산하고 선택한 셀부터 결과를 가로로 표시합니다.

장점: 반복적인 계산을 자동화하고, 대규모 데이터 세트에서 시간을 크게 절약하며 일관성을 보장합니다.

“두 데이터 범위는 동일한 행 수를 가져야 함”과 같은 문제를 겪는 경우, 선택한 모든 열이 정확히 동일한 행 수를 가지고 빈 행이 없는지 확인하세요. 오류 해결을 위해 매크로가 활성화되었는지와 범위가 올바르게 선택되었는지를 확인하세요.

Excel에서 상관 계수로 작업할 때 적절한 방법을 선택하는 것은 데이터 구조와 분석 필요에 따라 다릅니다. 두 시리즈 간에 일회성, 빠른 계산이 필요한 경우 CORREL이나 PEARSON과 같은 공식이 효율적이고 사용하기 쉽습니다. 여러 변수 또는 요약 테이블이 필요한 경우 분석 도구 팩이 매우 실용적입니다. 대규모 데이터 세트에 대한 반복적인 분석이나 맞춤형 워크플로가 필요한 경우 시간 절약과 인간 오류 감소를 위해 VBA를 사용하여 자동화하는 것을 고려하세요.
항상 데이터 범위가 정렬되고 깨끗하며 빈 셀이나 숫자가 아닌 셀이 포함되지 않도록 하여 수식 오류를 방지하세요. 예상치 못한 결과가 나오는 경우 선택 항목과 데이터 유형을 다시 확인하세요.

관련 기사

• Excel에서 두 숫자 간 백분율 변화 또는 차이 계산
  이 문서는 Excel에서 두 숫자 간 백분율 변화 또는 차이를 계산하는 방법에 대해 설명합니다.

• Excel에서 문자 등급 계산 또는 할당
  학생들의 점수에 따라 각 학생에게 문자 등급을 부여하는 것은 교사에게 일반적인 작업일 수 있습니다. 예를 들어, 제가 정의한 등급 척도가 있는데, 점수 0-59 = F, 60-69 = D, 70-79 = C, 80-89 = B, 90-100 = A입니다. 자세히 보기.

• Excel에서 할인율 또는 가격 계산
  크리스마스가 다가오면 쇼핑몰에는 다양한 판촉 행사가 있을 것입니다. 하지만 서로 다른 품목들이 서로 다른 할인을 받는다면, 어떻게 서로 다른 품목들의 할인율 또는 가격을 계산할 수 있을까요?

• Excel에서 두 날짜 간 날짜 수 / 평일 수 / 주말 수 계산
  때때로, 두 날짜 간의 평일 수만 계산하고 싶을 수도 있고, 반대로 주말 날짜만 계산해야 할 때도 있을 것입니다.