이 글에서는 엑셀 그래프에서 두 변수 간의 관계(상관관계)를 표시하는 방법을 단계별로 알아보겠습니다. 실무나 연구에서는 미래의 결과를 예측해 적절한 대응을 준비해야 하는 경우가 많은데, 이를 위해서는 무엇보다 변수들 사이의 관계를 먼저 파악하는 것이 중요합니다. 오늘 소개할 단계별 절차를 따라 하면 엑셀만으로 두 변수 간의 관계를 손쉽게 그래프로 나타낼 수 있습니다. 그럼 바로 시작해 보겠습니다.
연습용 파일 다운로드
직접 따라 해보실 수 있도록 아래에서 연습용 파일을 다운로드할 수 있습니다.
두 변수 간의 상관관계란?
두 변수 사이의 관계는 일반적으로 상관관계(correlation)라고 부릅니다. 상관관계는 두 변수가 얼마나 강하게 연결되어 있는지를 나타내며, 그 정도를 수치로 측정한 값이 바로 상관계수(correlation coefficient)입니다.
상관계수에 따른 세 가지 유형
- 상관계수 = 0: 두 변수 사이에 아무런 관계가 없습니다. 즉, 한쪽 변수가 변해도 다른 변수에는 영향을 주지 않으며, 그래프는 수평선 형태로 나타납니다.
- 상관계수가 -1~0 사이(음수): 음의 상관관계입니다. 한 변수가 증가하면 다른 변수는 감소하며, 그래프는 우하향하는 형태를 띱니다.
- 상관계수가 0~+1 사이(양수): 양의 상관관계입니다. 한 변수가 증가하면 다른 변수도 함께 증가하며, 그래프는 우상향하는 형태로 표현됩니다.
엑셀 그래프에서 두 변수 간의 관계를 표시하는 단계별 방법
설명을 위해 어떤 회사 직원들의 키(Height)와 몸무게(Weight) 정보가 담긴 데이터셋을 사용하겠습니다. 여기서 두 변수를 다루며, 키는 X축, 몸무게는 Y축에 배치합니다. 일반적으로 두 변수 간의 관계를 나타낼 때는 분산형 차트(Scatter Chart)가 가장 적합합니다. 이 그래프를 통해 키와 몸무게 사이의 관계를 살펴보겠습니다.
1단계: 분산형 차트(산점도) 만들기
- 먼저 데이터셋을 이용해 산점도를 만들어야 합니다.
- 그러려면 데이터 범위 C4:D13을 선택하세요.
- 다음으로 삽입(Insert) 탭으로 이동해 분산형 차트 삽입(Insert Scatter) 아이콘을 클릭하면 드롭다운 메뉴가 열립니다.
- 메뉴에서 분산형(Scatter Plot) 아이콘을 선택합니다.
- 이제 시트에 그래프가 생성된 것을 확인할 수 있습니다.
2단계: 차트 제목과 축 제목 추가하기
- 두 번째 단계에서는 그래프를 더 쉽게 이해할 수 있도록 차트 제목(Chart Title)과 축 제목(Axis Title)을 수정합니다.
3단계: 추세선(Trendline) 삽입하기
- 세 번째로 차트를 클릭하면 더하기(+) 아이콘이 나타납니다.
- 해당 아이콘을 클릭해 옵션 목록을 연 후, 추세선(Trendline) 항목에 체크하세요.
- 즉시 그래프에 선형 추세선이 표시됩니다.
- 추세선의 방향을 통해 두 변수가 양의 상관관계임을 알 수 있습니다.
- 즉, 한 변수가 증가하면 다른 변수도 함께 증가한다는 의미입니다.
4단계: R 제곱값(R-Squared) 표시하기
- 이번 단계에서는 그래프에 R 제곱값을 표시합니다. 이 값을 이용하면 상관계수 R을 계산할 수 있습니다.
- 추세선을 더블클릭하면 시트 오른쪽에 추세선 서식(Format Trendline) 창이 열립니다.
- 여기서 '차트에 R-제곱값 표시(Display R-squared value on chart)' 옵션에 체크합니다.
- 결과적으로 차트에 R 제곱값이 표시되는 것을 확인할 수 있습니다.
5단계: 차트에 추세선 수식 표시하기
- 5단계에서는 추세선의 수식(equation)을 추가합니다. 이 수식은 추세선이 어떻게 변화하는지 파악하는 데 유용합니다.
- 추세선 수식을 추가하려면 추세선 서식 창에서 '차트에 수식 표시(Display Equation on chart)' 옵션을 체크하세요.
- 이제 차트에 선형 추세선의 수식이 나타납니다.
6단계: 상관계수 구하기
- 이 단계에서는 상관계수를 구해 두 변수가 얼마나 강하게 연관되어 있는지 확인합니다.
- 4단계에서 구한 R 제곱값은 0.6618입니다.
- SQRT 함수를 사용하면 상관계수 R을 얻을 수 있습니다.
- 이를 위해 D5 셀을 선택하고 아래 수식을 입력하세요.
=SQRT(0.6618)
- Enter 키를 누르면 결과를 확인할 수 있습니다.
- 상관계수가 0.8135로 나타나며, 이는 양의 상관관계를 의미합니다.
7단계: 상관계수 검증하기
- 그래프에서 얻은 결과가 정확한지 확인하려면 CORREL 함수로 상관계수 값을 교차 검증할 수 있습니다.
- 이를 위해 D16 셀을 선택하고 아래 수식을 입력하세요.
=CORREL(C5:C13,D5:D13)
CORREL 함수는 두 변수의 상관계수 값을 구해주는 함수입니다. 첫 번째 인수에는 X 변수의 데이터 범위를, 두 번째 인수에는 Y 변수의 데이터 범위를 입력하면 됩니다.
- 마지막으로 Enter 키를 눌러 결과를 확인합니다.
- 두 방법으로 구한 상관계수 값이 동일한 것을 볼 수 있습니다.
최종 해석
- 위의 모든 단계를 완료하면 상관계수 R과 추세선의 수식을 얻을 수 있습니다.
- 추세선을 상관 없음(0), 양의 상관관계, 음의 상관관계 그래프와 비교해 보면, 두 변수는 양의 상관관계에 있다고 결론 내릴 수 있습니다.
- 또한 상관계수 R의 값 역시 강한 양의 상관관계를 나타냅니다.
- 즉, 한 변수의 값이 커지면 다른 변수도 함께 커진다는 의미입니다.
마무리
이번 글에서는 엑셀 그래프에서 두 변수 간의 관계를 표시하는 방법을 단계별로 살펴보았습니다. 산점도 작성부터 추세선 삽입, R 제곱값 표시, SQRT 및 CORREL 함수를 활용한 상관계수 계산까지 전 과정을 익히셨기를 바랍니다. 글 초반에 공유드린 연습용 파일을 다운로드하여 실력을 점검해 보세요. 이와 같은 유용한 팁이 더 필요하시면 ExcelDemy 웹사이트를 방문해 주시기 바랍니다. 마지막으로 제안이나 궁금한 점이 있다면 아래 댓글로 편하게 남겨주세요.