엑셀에 숨겨진(?) 통계처리 메뉴

출처> http://m.blog.naver.com/noalnose/220359077128

엑셀에 숨겨진 통계 분석 모듈

 

 

얼마전 마케팅리서치 관련 강의를 갔다가 엑셀을 이용하여 간단한 통계 분석을 할수 있다는 사실을 아는 사람이 생각보다 거의 없다는 사실에 놀랐다.

엑셀을 이용한 간단한 통계처리 기능에 대해서 소개만 살짝 하려고 한다.  

 

엑셀을 이용하여 통계 분석을 하는 방법에는 크게 두가지가 있다. 엑셀 함수를 이용하는 방법과 다른 통계 패키지들처럼 메뉴식으로 분석 모듈을 클릭해서 처리하는 방법이 있다.

 

예를 들면 상관분석의 경우 @correl(x축의 데이터 범위, y축의 데이터 범위)와 같이 함수를 직접 입력하여 구하는 방식이 있고, 엑셀의 데이터 분석 메뉴에서 상관 분석이라는 메뉴를 선택해서 처리하는 방법이 있는 것이다.

 

함수식을 이용하는 방법은 서점에 가면 엑셀 함수 사전이 시중에 많이 나와 있으므로 이를 이용하면 되는데, 엑셀에서 통계 처리를 할수 있는 메뉴는 숨겨져 있는 경향이 있어서 이를 세팅을 해서 사용하여야 한다.

 

즉, 처음에 MS 프로그램을 세팅하여 엑셀을 부팅시키면 데이터 분석의 통계 처리 메뉴가 보이지 않는다.

 

엑셀화면 좌측 상단에 있는 MS 아이콘을 클릭하면 ‘엑셀 옵션’이라는 메뉴가 있고, 이것을 다시 클릭하면 다음과 같은 화면이 나타난다.

이제 다시 '추가 기능'을 클릭하면 다음과 같은 화면이 나타나며

엑셀 '추가 기능'을 선택한 후 '이동'을 클릭하면 다음과 같은 화면이 제시되고

 

다시 '분석도구'을 선택한 후 확인을 클릭하면 통계 처리를 할 수 있는 모듈이 세팅이 된다.

 

이제 홈 화면에서 데이터 메뉴를 클릭하면 오른쪽에 ‘데이터 분석’이라는 메뉴와 ‘해찾기’ 라는 메뉴가 나타난다.

 

데이터 분석 메뉴를 클릭하면 다음과 같이 통계 분석 메뉴들이 나타나고, 분석하고 싶은 메뉴를 선택하여 사용하면 된다.

단, MS 버젼에 따라 약간씩 구성이 다르지만, 추가 기능 - 분석 도구 를 찾아내는 절차는 모두 동일하다. 

 

 

 물론, 통계 분석 전문 패키지처럼 다양한 옵션과 이에 따른 통계량을 제공하지는 않는다. 각 통계처리 모듈별로 가장 기본적인 통계량 값만 제공을 한다.

 

상 관 분석을 하기 위하여 상관분석을 클릭하면 다음과 같은 화면이 나타나는데 입력 범위에서 데이터가 있는 x축과 y축의 엑셀 화면상의 위치를 입력해도 되고, 엑셀 화면에서 데이터가 있는 부분을 마우스로 선택하면 자동으로 입력이 된다.

 

 

출력 범위는 결과값을 보기 원하는 엑셀 화면 상의 위치를 설정해주는 것인데, 기본 값는 워크시트로 되어 있어 출력 범위를 설정하지 않으면 옆에 별도의 sheet를 만들어서 출력값을 제시한다.

사회과학을 하면서 가장 기본적인 통계량 값은 제공을 하므로, 통계 패키지를 사용할 수 없는 환경이거나, 사용법을 잘 모르는 사람들은 간단한 처리를 할수 있다.

 

제 공하는 통계 분석은 기본적인 기술 통계량과, 상관분석, 분산분석, 회귀분석, t-검정, f-검정, z-검정, 간단한 시계열 분석 정도인데 가장 기본적인 통계량 값만 제공을 한다는 한계점은 있다. 예를 들면 상관분석에서는 상관계수 값만 제시를 하고, 분산분석에서 다중 비교 기능같은 옵션 통계량 값은 제공하지 않으며, 회귀분석에서 변수 선택이나 다중공선성 진단, 잔차 분석 같은 기능은 없다는 한계가 있다.

 

회귀분석을 예를 들어 보겠다.

다음과 같은 ‘구매의향’이라는 종속변수와 ‘타인추천의향’, ‘가격’이라는 독립변수가 있다고 가정하자.

 데이터분석 - 회귀분석 메뉴를 선택해서, 다음과 같이 y축 범위에 종속변수의 데이터 범위를 입력하고, x축 범위에 독립변수 데이터 위치를 입력하고, 출력 범위를 설정한 후 확인을 클릭한다.

이제 다음과 같이 회귀분석 출력 화면이 나타난다.

 

회귀 계수와 유의성 검증 통계량, 그리고 분산분석 테이블, 결정 계수 정도의 회귀 분석에서 많이 보는 기본적인 통계량이 제공됨을 알 수 있다.

 

이 와 유사한 방식으로 가장 기초적인 통계 분석을 엑셀을 이용하여 할 수 있는데 물론, 엑셀이 제공하는 통계적 함수를 이용해서 통계처리를 할수도 있지만 이는 논리적 관계를 수작업으로 프로그램 해야 한다는 번거로움이 있으므로 - 예들 들면 검증통계량 값과, 기각역, p-value 등을 함수식으로 이용하여 산출할 수 있다. - 간단한 분석시는 메뉴를 선택해서 사용하면 편리하고 간단하게 통계량 값을 얻을 수 있다.

 

참 고로, 서점에 가면 엑셀을 이용한 통계 분석 관련 책이 상당히 많이 출간되어 있다. 이러한 기초 통계뿐 아니라 엑셀의 분산/공분산 함수를 이용하여 요인분석 같은 다변량 분석을 할 수도 있으나, 그 정도의 고급 통계 분석은 일일히 프로그램을 하느니 전문 통계 패키지를 구해서 사용하는 것이 효율적이라 생각한다.