엑셀 COVARIANCE.P / COVARIANCE.S 함수로 공분산 구하기: 데이터 관계를 파헤치다!

데이터는 그 자체로 중요하지만, 더 깊은 통찰력을 얻기 위해서는 데이터 간의 관계를 이해하는 것이 필수적입니다. 특히 두 변수가 서로 어떻게 움직이는지 알고 싶을 때, ‘공분산(Covariance)’은 매우 유용한 지표가 됩니다. 엑셀은 이러한 공분산을 쉽게 계산할 수 있도록 COVARIANCE.P와 COVARIANCE.S라는 두 가지 강력한 함수를 제공합니다.

이번 글에서는 엑셀의 이 두 함수를 활용하여 공분산을 구하는 방법과, 각 함수가 어떤 상황에서 사용되어야 하는지, 그리고 공분산이 의미하는 바는 무엇인지 자세히 알아보겠습니다. 함께 데이터의 숨겨진 관계를 파헤쳐 볼 준비 되셨나요? 🚀

1. 공분산(Covariance)이란 무엇인가요?

공분산은 두 변수가 ‘함께 움직이는’ 경향, 즉 선형적인 관계의 방향을 나타내는 통계량입니다. 쉽게 말해, 한 변수가 증가할 때 다른 변수도 증가하는지, 아니면 감소하는지, 아니면 아무런 관계가 없는지를 알려줍니다.

• 양의 공분산 (+) : 두 변수가 같은 방향으로 움직이는 경향이 있습니다. 예를 들어, 광고 지출이 늘어날수록 판매량도 늘어나는 관계가 있다면 양의 공분산을 가질 수 있습니다. 📈
• 음의 공분산 (-) : 두 변수가 반대 방향으로 움직이는 경향이 있습니다. 예를 들어, 기온이 올라갈수록 난방비 지출이 줄어드는 관계가 있다면 음의 공분산을 가질 수 있습니다. 📉
• 0에 가까운 공분산 (0) : 두 변수 사이에 선형적인 관계가 거의 없거나, 관계가 있더라도 복잡하여 선형 모델로 설명하기 어렵습니다. 🤷‍♀️

💡 중요! 공분산의 크기는 두 변수의 측정 단위에 따라 달라지기 때문에, 공분산의 ‘절대적인 크기’만으로는 관계의 강도를 직접적으로 비교하기 어렵습니다. 관계의 강도를 알고 싶다면, 공분산을 표준화한 값인 ‘상관계수(Correlation Coefficient)’를 사용해야 합니다.

2. 엑셀 공분산 함수 깊이 파고들기

엑셀에는 모집단 공분산과 표본 공분산을 계산하는 두 가지 함수가 있습니다.

2.1. COVARIANCE.P 함수 (모집단 공분산)

COVARIANCE.P 함수는 주어진 데이터가 전체 모집단이라고 가정하고 공분산을 계산합니다.

• 사용 시기: 분석하려는 데이터가 모집단 전체를 나타낼 때 사용합니다. 예를 들어, 특정 회사 내 모든 직원의 근속 연수와 성과 점수 사이의 관계를 분석할 때처럼, 우리가 가진 데이터가 전체 대상일 때 사용합니다.
• 계산 방식: 편차 곱의 평균을 계산합니다. (n으로 나눔)
• 구문: =COVARIANCE.P(배열1, 배열2)
  • 배열1: 첫 번째 변수의 셀 범위입니다.
  • 배열2: 두 번째 변수의 셀 범위입니다.

예시 1: A 반 학생들의 공부 시간과 시험 점수 (모집단)

어떤 특정 학급(모집단) 학생들의 공부 시간과 시험 점수를 분석한다고 가정해 봅시다.

엑셀에서 B2:B6에 공부 시간 데이터가, C2:C6에 시험 점수 데이터가 있다고 할 때,

=COVARIANCE.P(B2:B6, C2:C6)

이 함수는 20이라는 결과 값을 반환할 것입니다. 양의 값으로, 공부 시간이 늘어날수록 시험 점수도 함께 늘어나는 경향이 있음을 알 수 있습니다.

2.2. COVARIANCE.S 함수 (표본 공분산)

COVARIANCE.S 함수는 주어진 데이터가 모집단에서 추출된 표본이라고 가정하고 공분산을 계산합니다.

• 사용 시기: 분석하려는 데이터가 더 큰 모집단의 일부(표본)일 때 사용합니다. 대부분의 통계 분석 상황에서 전체 모집단 데이터를 얻기 어렵기 때문에, 이 함수가 더 일반적으로 사용됩니다.
• 계산 방식: 모집단 공분산과 유사하지만, 분모가 n-1 (자유도)로 나뉘어집니다. 이를 ‘베셀의 보정(Bessel’s correction)’이라고 하며, 표본 공분산이 모집단 공분산을 더 정확하게 추정할 수 있도록 편향을 보정합니다.
• 구문: =COVARIANCE.S(배열1, 배열2)
  • 배열1: 첫 번째 변수의 셀 범위입니다.
  • 배열2: 두 번째 변수의 셀 범위입니다.

예시 2: 전국 고등학생 표본의 공부 시간과 시험 점수 (표본)

앞서와 같은 데이터를 사용하지만, 이번에는 이 데이터가 전국 고등학생 중 무작위로 추출된 ‘표본’이라고 가정해 봅시다.

엑셀에서 B2:B6에 공부 시간 데이터가, C2:C6에 시험 점수 데이터가 있다고 할 때,

=COVARIANCE.S(B2:B6, C2:C6)

이 함수는 25라는 결과 값을 반환할 것입니다. COVARIANCE.P 값인 20보다 약간 높은 값을 가집니다. 이는 n-1로 나누면서 표본의 분산을 과소평가하는 경향을 보정했기 때문입니다.

2.3. 핵심 차이점: 왜 P와 S를 구분하나요? 🤔

COVARIANCE.P와 COVARIANCE.S의 핵심적인 차이는 바로 분모에 있습니다.

• COVARIANCE.P: 데이터 개수 n으로 나눕니다. (모집단의 실제 공분산을 계산할 때 사용)
• COVARIANCE.S: 데이터 개수에서 1을 뺀 n-1로 나눕니다. (표본 데이터를 통해 모집단의 공분산을 ‘추정’할 때 사용)

n-1을 사용하는 이유는 표본 공분산이 모집단 공분산을 더 정확하게 추정하기 위함입니다. 표본 데이터만으로는 모집단의 변동성을 완벽히 포착하기 어렵기 때문에, n-1로 나누어 주면 모집단 공분산을 더 ‘덜 편향되게’ 추정할 수 있습니다. 이를 ‘자유도(Degrees of Freedom)’라고 부르며, 통계학에서 중요한 개념입니다.

대부분의 실제 분석 상황에서는 전체 모집단 데이터를 얻기 어렵기 때문에, 일반적으로 COVARIANCE.S 함수를 더 많이 사용하게 됩니다.

3. 실생활 예시로 배우는 공분산 활용 📊

이제 실제 시나리오를 통해 공분산 함수를 어떻게 활용할 수 있는지 살펴보겠습니다.

3.1. 예시 1: 광고 지출과 판매량의 관계

어떤 회사가 월별 광고 지출과 해당 월의 판매량 데이터를 가지고 있습니다. 광고 지출이 판매량에 어떤 영향을 미치는지 공분산을 통해 알아보고자 합니다.

이 데이터가 회사의 지난 5개월치 ‘전체’ 기록이라고 가정하고 COVARIANCE.P를 사용해 봅시다.

1. 데이터 입력: 엑셀 시트에 위 표와 같이 데이터를 입력합니다. (예: 광고 지출이 B2:B6, 판매량이 C2:C6)
2. 함수 입력: 아무 빈 셀에 다음 수식을 입력합니다.

   =COVARIANCE.P(B2:B6, C2:C6)

3. 결과 확인: 결과는 5800이 나올 것입니다.

해석: 양의 공분산(+5800)이 나왔으므로, 광고 지출이 증가할수록 판매량도 증가하는 양의 선형 관계가 있음을 알 수 있습니다. 즉, 광고 투자가 판매량 증대에 긍정적인 영향을 미친다고 추정할 수 있습니다. 📈

3.2. 예시 2: 두 주식의 수익률 변동성

어떤 투자자가 두 주식(주식 A, 주식 B)의 일간 수익률 데이터를 분석하여, 두 주식이 함께 어떻게 움직이는지 파악하고자 합니다. 이 데이터는 전체 시장이 아닌 특정 기간의 ‘표본’이라고 가정합니다.

이 데이터가 특정 기간의 ‘표본’이라고 가정하고 COVARIANCE.S를 사용해 봅시다.

1. 데이터 입력: 엑셀 시트에 위 표와 같이 데이터를 입력합니다. (예: 주식 A 수익률이 B2:B6, 주식 B 수익률이 C2:C6)
2. 함수 입력: 아무 빈 셀에 다음 수식을 입력합니다.

   =COVARIANCE.S(B2:B6, C2:C6)

3. 결과 확인: 결과는 약 0.85 (소수점 이하 반올림)가 나올 것입니다.

해석: 양의 공분산(+0.85)이 나왔으므로, 주식 A의 수익률이 오를 때 주식 B의 수익률도 함께 오르고, 주식 A의 수익률이 내릴 때 주식 B의 수익률도 함께 내리는 경향이 있음을 알 수 있습니다. 이는 두 주식이 비교적 유사한 방향으로 움직인다는 것을 의미하며, 포트폴리오 다변화 관점에서 볼 때 주의가 필요할 수 있습니다. 🤝

4. 공분산과 상관계수, 어떤 관계일까요? 🤝

앞서 언급했듯이 공분산은 두 변수가 함께 움직이는 ‘방향’을 알려주지만, 그 관계의 ‘강도’는 측정 단위에 따라 달라져 해석하기 어렵습니다. 이때 등장하는 것이 바로 상관계수(Correlation Coefficient)입니다.

상관계수는 공분산을 각 변수의 표준편차로 나누어 표준화한 값입니다. 이 덕분에 상관계수는 항상 -1과 1 사이의 값을 가지며, 값의 크기만으로도 관계의 강도를 직관적으로 파악할 수 있습니다.

• +1: 완벽한 양의 선형 관계
• -1: 완벽한 음의 선형 관계
• 0: 선형 관계 없음

엑셀에서는 CORREL 함수를 사용하여 상관계수를 쉽게 계산할 수 있습니다.

예시: 위에 사용한 ‘광고 지출과 판매량’ 데이터의 상관계수를 계산해보면, =CORREL(B2:B6, C2:C6) 수식은 약 0.98이라는 값을 반환할 것입니다. 이는 광고 지출과 판매량 사이에 매우 강한 양의 선형 관계가 있음을 의미합니다.

결론적으로, 공분산은 관계의 방향을, 상관계수는 관계의 방향과 강도를 모두 알려주는 지표라고 할 수 있습니다. 대부분의 데이터 분석에서는 관계의 강도까지 파악하기 위해 상관계수를 더 선호하는 경우가 많습니다.

결론 🎉

엑셀의 COVARIANCE.P와 COVARIANCE.S 함수를 이해하고 활용하면, 두 데이터 변수 간의 숨겨진 관계를 효과적으로 파악할 수 있습니다. 모집단 데이터라면 COVARIANCE.P를, 표본 데이터라면 COVARIANCE.S를 사용하는 것이 원칙입니다.

공분산이 양수인지 음수인지, 혹은 0에 가까운지를 통해 데이터가 함께 움직이는 방향을 예측하고, 더 나아가 상관계수와 함께 분석한다면 데이터로부터 훨씬 더 풍부한 인사이트를 얻을 수 있을 것입니다.

오늘 배운 내용을 바탕으로 여러분의 데이터를 직접 분석해보세요! 데이터가 들려주는 이야기에 귀 기울이는 것이 통찰력 있는 결정을 내리는 첫걸음입니다. 🚀✨ D