안녕하세요! 여러분의 엑셀 능력을 한 단계 업그레이드해 드릴 엑셀 튜터입니다. 😊 혹시 두 데이터 사이에 어떤 관계가 있는지 궁금했던 적 없으신가요? 예를 들어, "광고비를 늘리면 정말 매출도 함께 오를까?" 또는 "공부 시간이 늘어나면 성적도 항상 오를까?" 같은 질문들이요.
이런 궁금증을 해결할 때 바로 엑셀의 COVARIANCE.S 함수가 큰 힘을 발휘한답니다. 오늘은 저와 함께 COVARIANCE.S 함수가 무엇인지, 어떻게 사용하는지 실제 업무 데이터를 통해 쉽고 재미있게 배워볼게요!
📌 [핵심] COVARIANCE.S 함수는 두 데이터 집합(예: 광고비와 매출)이 함께 어떻게 변하는지를 숫자로 보여주는 기능이에요.
관련 게시물: [기타 함수] 엑셀 COVAR 로 공분산 구하기
📊 COVARIANCE.S 함수, 대체 뭔가요?
엑셀 초보자분들에게 '공분산' 이라는 단어는 조금 낯설 수 있어요. 최대한 쉽게 설명해 드릴게요!
공분산(Covariance)이란, 두 개의 변수가 얼마나 함께 움직이는지를 측정하는 값이에요.
- 결과값이 양수(+)라면? 두 변수는 같은 방향으로 움직이는 경향이 있어요. (A가 증가할 때 B도 증가)
- 결과값이 음수(-)라면? 두 변수는 반대 방향으로 움직이는 경향이 있어요. (A가 증가할 때 B는 감소)
- 결과값이 0에 가깝다면? 두 변수 사이에는 특별한 관계가 없다고 볼 수 있어요.
COVARIANCE.S 의 'S' 는 Sample(표본) 을 의미해요. 전체 데이터가 아닌, 일부 표본 데이터를 가지고 공분산을 계산할 때 사용하는 함수랍니다. (전체 데이터는 COVARIANCE.P 를 사용해요!)
✅ 함수 구문 및 인수
함수 형태는 아주 간단해요. 관계를 보고 싶은 두 개의 데이터 범위를 콕 집어주면 끝!
=COVARIANCE.S(array1, array2)- array1 (필수): 첫 번째 데이터 범위(셀 범위)입니다. 예를 들어, 월별 광고비 데이터가 되겠죠?
- array2 (필수): 두 번째 데이터 범위(셀 범위)입니다. 예를 들어, 월별 매출액 데이터가 해당돼요.
📝 꼭 알아둘 점 (Note)
- array1 과 array2 의 데이터 개수(행의 수)는 반드시 같아야 해요. 짝이 맞아야 관계를 분석할 수 있겠죠?
- 인수에는 숫자 데이터만 포함되어야 해요. 텍스트나 논리값, 빈 셀은 계산에서 제외된답니다.
- 두 데이터 범위 중 하나라도 데이터가 없으면 #DIV/0! 오류가 발생해요.
💻 COVARIANCE.S 함수, 직접 사용해봐요!
백 마디 설명보다 한 번의 실습이 최고죠! 실제 업무에서 바로 써먹을 수 있는 예제들로 함께 알아볼게요.
예제 1: [기본] 광고비와 매출의 관계 알아보기
가장 대표적인 예제예요. 지난 10개월간의 광고비 지출액과 그에 따른 월 매출액 데이터가 있다고 가정해 봅시다.
[데이터]
월,광고비(만원),매출액(만원)
1월,100,1500
2월,120,1800
3월,150,2200
4월,110,1650
5월,200,3000
6월,220,3500
7월,180,2800
8월,250,4000
9월,280,4500
10월,300,5000
[수식]
광고비 데이터가 B2:B11에, 매출액 데이터가 C2:C11에 있다면 D2 셀에 아래와 같이 수식을 입력합니다.
=COVARIANCE.S(B2:B11, C2:C11)
[결과 및 해석]
- 결과값: 87894.4
- 결과값이 아주 큰 양수로 나왔네요! 이것은 "광고비를 많이 지출할수록 매출액도 함께 증가하는 경향이 있다"고 해석할 수 있어요. 📈
📝 노트. 계산 단계
정확한 계산 과정은 다음과 같습니다.
- 각 데이터의 평균 계산
- 광고비 평균: 191
- 매출액 평균: 2995
- 각 데이터에서 (관측값 - 평균)의 곱을 모두 더함
- (100-191) * (1500-2995) + (120-191) * (1800-2995) + ... + (300-191) * (5000-2995) = 791,055
- 위의 합계를 (n-1)로 나눔 (n=데이터 개수 10)
- 791,055 / (10 - 1) = 87,895
예제 2: [중급] 공분산 값의 한계와 CORREL 함수로 명확하게 보기
"87894.4" 이라는 숫자가 크다는 건 알겠는데, 그래서 두 데이터의 관계가 '얼마나' 강한지는 감이 잘 안 오시죠? 맞아요. 공분산은 값의 크기만으로는 관계의 강도를 직관적으로 파악하기 어려워요.
이때 함께 사용하면 좋은 함수가 바로 CORREL (상관계수) 함수입니다. 상관계수는 공분산을 표준화하여 -1과 1 사이의 값으로 표현해 줘서 관계의 강도를 훨씬 쉽게 파악할 수 있게 해줘요.
[수식]
같은 데이터에 CORREL 함수를 적용해 볼게요.
=CORREL(B2:B11, C2:C11)
[결과 및 해석]
- 결과값: 약 0.998
- CORREL 결과가 1 에 매우 가깝죠? 이는 광고비와 매출액이 매우 강한 양의 상관관계에 있다는 것을 의미해요. 이처럼 COVARIANCE.S 로 방향성을 파악하고, CORREL 로 관계의 강도를 파악하면 완벽한 데이터 분석이 가능해요!
예제 3: [실무 활용] 업무 스트레스 지수와 직무 만족도의 관계 분석
이번에는 반대로 움직이는 관계, 즉 음의 상관관계를 분석해 볼게요. 직원들의 월별 스트레스 지수(높을수록 나쁨)와 직무 만족도 점수(높을수록 좋음) 데이터가 있다고 가정해 보겠습니다. 상식적으로 스트레스가 높으면 만족도는 떨어지겠죠? COVARIANCE.S 함수로 정말 그런지 확인해 보겠습니다.
[데이터]
직원,스트레스 지수(1-10),직무 만족도(1-100)
A,8,45
B,7,55
C,3,85
D,9,30
E,2,90
F,10,25
G,4,75
H,5,65
I,6,60
[수식] 스트레스 지수 데이터가 B2:B10 에, 직무 만족도 데이터가 C2:C10 에 있다면 아래와 같이 수식을 입력합니다.
=COVARIANCE.S(B2:B10, C2:C10)
[결과 및 해석]
- 결과값: -61.87500
- 드디어 음수(-) 결과값이 나왔네요! 이것은 "스트레스 지수가 높아질수록 직무 만족도는 낮아지는 경향이 있다"는 음의 상관관계를 명확하게 보여줍니다. 📉
🚦 자주 만나는 오류, 해결 방법은?
COVARIANCE.S 함수 사용 시 발생할 수 있는 대표적인 오류와 해결 방법이에요.
- #N/A 오류 : array1 과 array2 의 데이터 개수가 다를 때 발생해요. 범위를 올바르게 다시 지정해 주세요.
- #DIV/0! 오류 : 데이터 범위가 비어있거나, 유효한 숫자 데이터가 하나도 없을 때 발생해요. 데이터가 제대로 입력되었는지 확인하세요.
- #VALUE! 오류 : 인수가 숫자가 아닌 텍스트를 포함하고 있을 때 발생할 수 있지만, 일반적으로 이 함수는 텍스트를 무시합니다. 하지만 다른 계산과 겹쳤을 때 발생할 수 있으니 데이터 형식을 확인해 보세요.
🔗 함께 쓰면 좋은 친구 함수들
- COVARIANCE.P : 표본이 아닌 모집단 전체의 공분산을 계산할 때 사용해요.
- CORREL : 두 데이터 사이의 상관계수를 계산해요. 관계의 '강도' 를 파악하는 데 최고!
- PEARSON : CORREL 함수와 동일한 결과를 반환하는 피어슨 상관계수 함수예요.
- SLOPE : 선형 회귀선의 기울기를 구해줘요. 두 변수의 관계를 시각화할 때 유용하답니다.
💜 핵심 정리 및 다음 단계
오늘 배운 COVARIANCE.S 함수, 어떠셨나요? 정리해 볼게요!
- COVARIANCE.S 는 두 데이터 그룹이 함께 움직이는 방향성을 알려줘요.
- 결과가 양수면 같은 방향, 음수면 반대 방향으로 움직인다는 의미예요.
- 관계의 '강도' 를 명확히 보려면 CORREL 함수를 함께 사용하는 것이 좋아요.
이제 여러분의 업무 데이터나 개인적인 데이터를 한번 열어보세요. 혹시 관계가 궁금했던 두 데이터가 있나요? 오늘 배운 COVARIANCE.S 와 CORREL 함수를 직접 적용해서 숨겨진 인사이트를 찾아보는 건 어떨까요?
궁금한 점이 있다면 언제든지 댓글로 질문 남겨주시고요, 오늘 내용이 유익했다면 주변 동료들에게도 공유해 주세요! 여러분의 '좋아요' 와 '공유' 는 제게 큰 힘이 된답니다. ❤️
📱 바쁘신 분들을 위한 30초 요약 (모바일용)
엑셀 COVARIANCE.S 함수 한눈에 보기
🤔 언제 쓰나요?
- 두 데이터가 함께 움직이는지(방향성) 궁금할 때!
- (예: 광고비 📈 → 매출 📈 ?)
📝 사용법
- =COVARIANCE.S(데이터범위1, 데이터범위2)
✅ 결과 해석
- 양수(+) : 같이 증가하거나 같이 감소 (정비례)
- 음수(-) : 하나는 증가, 하나는 감소 (반비례)
💡 꿀팁!
- 관계의 '세기' 가 궁금하면? CORREL 함수를 쓰세요! (결과가 -1 ~ 1 사이로 나와서 보기 편해요)
