데이터를 깊이 있게 이해하려면 단순히 평균이나 합계를 넘어, 데이터가 얼마나 퍼져 있는지, 즉 분산(Variance)과 표준 편차(Standard Deviation)를 파악하는 것이 중요합니다.
또한, 서로 다른 두 데이터 셋이 어떤 관계를 가지고 움직이는지, 즉 상관 관계(Correlation)를 분석하는 능력은 데이터 기반 의사결정의 핵심입니다. 이러한 데이터의 산포도(Scattering)와 관계를 분석하는 데 필수적인 함수들을 집중적으로 다룰 예정입니다.
왜 분산 및 상관 관계 분석 함수를 알아야 할까요?
비즈니스 환경에서 우리는 종종 "이 제품의 가격 변동성은 얼마나 되는가?", "두 마케팅 캠페인 간에 판매량의 차이가 유의미한가?", "광고비 지출이 매출액에 얼마나 영향을 미치는가?"와 같은 질문에 직면합니다.
이러한 질문에 답하려면 데이터의 퍼짐 정도와 여러 변수 간의 연관성을 측정하는 통계적 도구가 필요합니다.
이 함수들은 데이터의 안정성, 위험도, 그리고 인과 관계 추론의 기초를 제공하여 더 정교하고 전략적인 의사결정을 가능하게 합니다.
주요 함수들
다음 함수들을 실무 예제와 함께 자세히 살펴보겠습니다.
- VAR.S, VAR.P: 표본 및 모집단의 분산 계산
- STDEV.S, STDEV.P: 표본 및 모집단의 표준 편차 계산
- COVARIANCE.S, COVARIANCE.P: 두 데이터 집합 간의 공분산 계산
- CORREL: 두 데이터 집합 간의 상관 관계 계수 계산
- STANDARDIZE: 데이터 정규화 (Z-점수) 계산
실무 예제로 배우는 엑셀 함수 마스터하기
각 함수를 실제 비즈니스 시나리오에 적용하여 데이터 기반의 의사결정을 지원하는 방법을 보여드리겠습니다.
1. VAR.S, VAR.P: 데이터의 퍼짐 정도 (분산) 측정
분산(Variance)은 데이터가 평균으로부터 얼마나 떨어져 분포되어 있는지를 나타내는 척도입니다. 값이 클수록 데이터가 넓게 퍼져있고, 작을수록 평균에 가깝게 밀집되어 있습니다. 엑셀에서는 표본 분산(VAR.S)과 모집단 분산(VAR.P)을 계산하는 함수를 제공합니다. 대부분의 경우, 우리가 다루는 데이터는 전체 모집단이 아닌 '표본'이므로 VAR.S를 사용하는 것이 일반적입니다.
- 기본 형식: =VAR.S(숫자1, [숫자2], ...) / =VAR.P(숫자1, [숫자2], ...)
실무 예제: 우리 회사의 지난 6개월간 월별 판매량이 아래와 같이 B2:B7 셀에 입력되어 있다고 가정해봅시다.
| 월 | 판매량 |
| 1월 | 150 |
| 2월 | 160 |
| 3월 | 145 |
| 4월 | 180 |
| 5월 | 155 |
| 6월 | 170 |
예제 목표: 월별 판매량 데이터의 분산을 계산하여 판매량의 변동성을 파악하세요. (이 데이터는 전체 기간이 아닌 '표본'이라고 가정합니다.)
해결 방법: 판매량 데이터의 분산을 계산하려면 C2 셀 (또는 원하는 셀)에 다음 수식을 입력합니다. =VAR.S(B2:B7)
이 결과로 월별 판매량의 분산은 약 179.17이 됩니다.
최상급 실무 활용: 분산은 제품의 품질 관리, 재고 관리, 금융 투자 분석 등에서 위험도나 안정성을 평가하는 데 사용됩니다.
예를 들어, 두 가지 다른 투자 포트폴리오의 수익률 분산을 비교하여 변동성이 낮은 (안정적인) 포트폴리오를 선택하는 데 활용할 수 있습니다. 분산 값이 높다는 것은 판매량이 월마다 크게 변동한다는 것을 의미하므로, 재고 계획이나 생산 계획 수립 시 이를 고려해야 합니다.
2. STDEV.S, STDEV.P: 데이터의 퍼짐 정도 (표준 편차) 측정
표준 편차(Standard Deviation)는 분산에 제곱근을 씌운 값으로, 분산과 마찬가지로 데이터가 평균으로부터 얼마나 퍼져 있는지를 나타내지만, 데이터와 동일한 단위를 가지므로 해석이 더 용이합니다.
분산과 마찬가지로 표본 표준 편차(STDEV.S)와 모집단 표준 편차(STDEV.P)가 있습니다. 역시 대부분의 경우 STDEV.S를 사용합니다.
- 기본 형식: =STDEV.S(숫자1, [숫자2], ...) / =STDEV.P(숫자1, [숫자2], ...)
실무 예제: 위 월별 판매량 데이터(B2:B7)를 사용하여 표준 편차를 계산하세요.
예제 목표: 월별 판매량 데이터의 표준 편차를 계산하여 판매량의 평균적인 변동 폭을 파악하세요.
해결 방법: 판매량 데이터의 표준 편차를 계산하려면 C3 셀에 다음 수식을 입력합니다. =STDEV.S(B2:B7)
이 결과로 월별 판매량의 표준 편차는 약 13.38이 됩니다.
최상급 실무 활용: 표준 편차는 품질 관리에서 제품의 일관성을 평가하거나, 금융 상품의 수익률 변동성을 측정하는 데 광범위하게 사용됩니다.
예를 들어, 두 생산 라인에서 생산된 제품의 불량률 표준 편차를 비교하여 어떤 라인이 더 안정적인 품질을 유지하는지 판단할 수 있습니다. 판매량 표준 편차가 13.38이라는 것은, 평균 판매량(약 150)에서 대략 13.38 정도의 변동이 일반적이라는 의미로 해석하여, 향후 판매량 예측의 오차 범위를 가늠하는 데 활용할 수 있습니다.
3. COVARIANCE.S, COVARIANCE.P: 두 변수의 공분산
공분산(Covariance)은 두 확률 변수가 함께 변화하는 경향을 나타내는 척도입니다. 양수이면 두 변수가 같은 방향으로 움직이고, 음수이면 반대 방향으로 움직이며, 0에 가까우면 선형 관계가 거의 없음을 의미합니다. 공분산 역시 표본 공분산(COVARIANCE.S)과 모집단 공분산(COVARIANCE.P)이 있습니다.
- 기본 형식: =COVARIANCE.S(배열1, 배열2) / =COVARIANCE.P(배열1, 배열2)
실무 예제: 지난 5개월간의 광고비 지출과 매출액 데이터가 아래와 같이 B2:C6 셀에 있다고 가정해봅시다.
| 월 | 광고비(만원) | 매출액(만원) |
| 1월 | 500 | 5,000 |
| 2월 | 600 | 6,500 |
| 3월 | 450 | 4,800 |
| 4월 | 700 | 7,200 |
| 5월 | 550 | 5,800 |
예제 목표: 광고비 지출과 매출액 간의 공분산을 계산하여 두 변수가 함께 움직이는 경향을 파악하세요.
해결 방법: 공분산을 계산하려면 D2 셀에 다음 수식을 입력합니다. =COVARIANCE.S(B2:B6, C2:C6)
이 결과로 광고비와 매출액 간의 공분산은 약 76,000이 됩니다. (양수이므로 같은 방향으로 움직이는 경향이 있음)
최상급 실무 활용: 공분산은 두 변수 간의 방향성을 파악하는 데 유용하지만, 값의 크기가 변수의 단위에 따라 달라지기 때문에 상관 관계 계수가 더 널리 사용됩니다. 하지만 공분산은 포트폴리오 이론에서 자산 간의 위험을 분산시키는 데 중요한 역할을 하는 등, 특정 통계 분석의 기초가 됩니다.
4. CORREL: 두 변수의 상관 관계 계수
상관 관계 계수(Correlation Coefficient)는 두 변수 간의 선형 관계의 강도와 방향을 -1에서 +1 사이의 값으로 나타냅니다. +1에 가까울수록 강한 양의 선형 관계(하나가 증가하면 다른 하나도 증가), -1에 가까울수록 강한 음의 선형 관계(하나가 증가하면 다른 하나는 감소), 0에 가까울수록 선형 관계가 없음을 의미합니다.
공분산의 단위를 제거하여 더 직관적으로 관계의 강도를 파악할 수 있습니다.
- 기본 형식: =CORREL(배열1, 배열2)
실무 예제: 위 광고비 지출과 매출액 데이터(B2:C6)를 사용하여 상관 관계 계수를 계산하세요.
예제 목표: 광고비 지출과 매출액 간의 선형 관계 강도와 방향을 파악하세요.
해결 방법: 상관 관계 계수를 계산하려면 D3 셀에 다음 수식을 입력합니다. =CORREL(B2:B6, C2:C6)
이 결과로 광고비와 매출액 간의 상관 관계 계수는 약 0.992가 됩니다.
최상급 실무 활용: 이 높은 양의 상관 관계 계수는 광고비 지출이 증가할수록 매출액도 매우 강하게 증가하는 경향이 있음을 시사합니다. 이는 마케팅 투자 전략 수립에 중요한 근거가 됩니다.
고객 만족도와 재구매율, 직원 교육 시간과 생산성 등 다양한 비즈니스 지표 간의 연관성을 객관적으로 평가하는 데 사용되며, 회귀 분석의 기초가 됩니다. 상관 관계는 인과 관계를 의미하지는 않지만, 인과 관계를 추론하기 위한 중요한 단서를 제공합니다.
5. STANDARDIZE: 데이터 정규화 (Z-점수)
Z-점수(Standard Score) 또는 표준화(Standardization)는 특정 데이터 포인트가 평균으로부터 표준 편차의 몇 배만큼 떨어져 있는지를 나타내는 값입니다. 이는 서로 다른 분포를 가진 데이터들을 비교할 때 유용합니다. 평균이 0, 표준 편차가 1인 표준 정규 분포로 변환합니다.
- 기본 형식: =STANDARDIZE(X, 평균, 표준_편차)
- X: 표준화할 데이터 값
- 평균: 데이터 집합의 평균
- 표준_편차: 데이터 집합의 표준 편차
실무 예제: 한 영업 사원의 월별 판매 점수가 다음과 같다고 가정해봅시다.
- 이번 달 판매 점수: 85점
- 팀 전체의 평균 판매 점수: 70점
- 팀 전체의 표준 편차: 10점
예제 목표: 이 영업 사원의 점수 85점이 팀 평균으로부터 표준 편차의 몇 배만큼 떨어져 있는지 (Z-점수) 계산하세요.
해결 방법: Z-점수를 계산하려면 C2 셀에 다음 수식을 입력합니다. =STANDARDIZE(85, 70, 10)
이 결과로 Z-점수는 1.5가 됩니다.
최상급 실무 활용: Z-점수는 서로 다른 시험 점수, 고객 만족도 점수 등 단위가 다른 데이터들을 비교할 때 유용합니다.
예를 들어, 영어 시험에서 80점, 수학 시험에서 70점을 받았다고 했을 때, 단순히 점수만으로는 어떤 과목을 더 잘했는지 알기 어렵습니다. 하지만 각 과목의 Z-점수를 계산하면, 해당 학생이 각 과목의 평균에서 표준 편차의 몇 배만큼 떨어져 있는지 파악하여 상대적인 성과를 정확하게 비교할 수 있습니다. 이는 성과 평가, 이상치 탐지 등에 활용됩니다.
데이터의 산포도와 관계를 분석하는 데 필수적인 함수들(VAR.S/P, STDEV.S/P, COVARIANCE.S/P, CORREL, STANDARDIZE)을 살펴보았습니다.
이 함수들은 데이터가 얼마나 안정적인지, 특정 요인들이 서로 어떻게 연관되어 있는지에 대한 깊이 있는 통찰력을 제공하여, 위험 관리, 마케팅 효율성 분석, 품질 개선 등 다양한 비즈니스 영역에서 현명한 의사결정을 내릴 수 있도록 돕습니다.