이번 포스팅에서는 엑셀로 회귀분석 해보는 시간을 갖도록 하겠습니다.
회귀분석이란 간단히 말해 두 데이터의 상관관계를 알아보는 것입니다.
우선 아래 포스팅에서 파이썬을 이용해 S&P500 지수와 KOSPI200 지수를 불러와서 엑셀로 저장하였습니다.
2020/12/03 - [프로그래밍/┗ Pandas] - [Pandas] S&P500 지수와 KOSPI200 지수 불러와서 엑셀로 저장
[Pandas] S&P500 지수와 KOSPI200 지수 불러와서 엑셀로 저장
이번 포스팅에서는 S&P500 지수와 KOSPI200 지수의 상관관계를 알아보기에 앞서 각각의 데이터를 불러온 후 엑셀파일로 저장하는 과정을 설명드리겠습니다. 먼저 소스는 다음과 같습니다. import Finan
grand-unified-engine.tistory.com
파이썬 과정을 건너 뛰고 아래 첨부파일로 해보셔도 됩니다.
엑셀 파일을 열고 A2 셀을 선택 후 Ctrl + Shift + 아래 방향키를 누르면 열을 일괄 선택할 수 있습니다.
마우스 오른쪽 클릭하여 셀 서식을 누르거나 Ctrl + 1을 누르면 셀 서식이 나타납니다.
날짜만 표시되도록 형식을 yyyy-mm-dd로 변경해줍니다.
Ctrl + A를 눌러 전체 셀을 선택 하고 삽입 -> 차트 -> 2차원 꺾은선형을 클릭하면 다음과 같이 그래프가 표시됩니다.
그런데 S&P500에 비해 KOSPI200 지수가 작아서 비교하기가 쉽지 않습니다.
이럴 때 값이 다른 것을 비교하기 위해 지수화 작업을 해줘야 합니다.
D2 셀을 클릭 후 =B2/B$2*100를 입력합니다.
E2 셀을 클릭후 =C2/C$2*100를 입력합니다.
D2와 E2셀을 선택하고 선택한 영역 오른쪽 아래 점을 클릭해 +모양으로 바뀌면 더블 클릭합니다.
그러면 마지막 행까지 수식이 반영되어 데이터가 채워집니다.
지수화 한 데이터로 그래프를 그리기 위해 B열과 C열을 선택해서 숨김처리합니다.
Ctrl + A를 선택후 삽입 -> 차트 -> 2차원 꺾은선형을 클릭하면 다음과 같이 그래프가 표시됩니다.
이제 두 데이터 간 비교를 할 수 있게 되었으므로 회귀분석을 해보겠습니다.
엑셀에서 회귀분석을 하려면 엑셀 기능을 추가해야 합니다.
1. 엑셀의 상단 메뉴에서 파일 -> 옵션 메뉴를 클릭합니다.
2. 'Excel 옵션'창이 열리면 추가 기능 옆에 이동 버튼을 누릅니다.
3. 추가 기능 팝업이 뜨면 분석 도구에 체크하고 확인 버튼을 누릅니다.
엑셀 상단 메뉴에 보면 '데이터 분석'이 활성화 되었습니다.
데이터 분석을 눌러 '회귀 분석'을 찾아 선택합니다.
회귀 분석 창에서 'Y축 입력 범위'에는 KOSPI200 데이터를 선택하고, 'X축 입력 범위'에는 S&P500 데이터를 선택합니다. 이 창에서는 Ctrl을 눌러 데이터 선택하는 기능이 작동하지 않으므로 마우스로 드레그 해야 합니다.
입력이 완료된 후 확인 버튼을 누릅니다.
Sheet2에 회귀분석한 결과가 표시됩니다.
추세선 방정식은 y = 0.033x + 97.31이고 결정계수는 0.002로, 거의 상관관계가 없다는 결과가 나왔습니다.