엑셀 누적상대도수 - egsel nujeogsangdaedosu

도수표(frequency table)는 빈도수에 대한 정보를 제공한다. 

얼마나 많큼 그 사건이 일어났는지 말해준다.

위의 표는 도수표의 예시다.

도수표에서 누적도수(cumulative frequency)를 구하는 것 역시 가능하다.

두 번째 열이 도수(frequency)열이 계급 구간(class interval) 

세 번째 열이 누적도수(cumulative frequency)다.

아래로 가면서 점차 누적해나간다. 

20

20+21 = 41

20+21+13 = 54

20+21+13+8 = 62

20+21+13+8+4 = 66

위와 같이 도수표(frequency table)를 작성하고 누적도수(cumulative frequency)를 구한다.

또한 그래프로 시각화(visualization)도 가능하다.

CTRL키를 누르고 열A와 열 C를 선택한다.

> 삽입 > 차트 > 바 차트 > (직선 및 표식이 있는) 분산형으로 변경 > 차트 제목 수정

누적도수분포 곡선(Ogive graph)는 데이터 값이 데이트 세트에서 분포한 위치를 알 수 있는 시각화 그래프다.

예시: 누적도수분포 곡선(Ogive graph) 그리기

몇 가지 단계적으로 실행한다.

1단계: 데이터 입력한다.

2단계: 데이터 계급 하한(class limit)을 구한다.

누적도수분포 곡선(Ogive graph)그리기 위해 데이터 계급 하한(class limit)을 구한다.

3단계: 계급에 도수(frequency)를 구한다.

각 계급별로, 몇 번이나 나와있는지 도수(frequency)를 구한다.

4단계: 누적도수분포(cumulative frequency)를 구한다.

각 계급(class)별 도수에서 누적도수분포(cumulative frequency)를 구한다.

5단계: 누적도수분포 곡선(Ogive graph)를 그린다.

계급 구만의 끝(2열)과 누적도수(4열)를 선택하고, 삽입 > 차트 > 분산형 > 직선 및 표식이 있는 분산형 실행한다.

곡선 제목, x/y열 이름 수정 등 곡선 수정을 한다.

관심있는-퍼온것

1.5.(Excel) 데이터를 정리하자 : 도수분포표

어제 써놓았던 글이...홀라당 날라가 버렸다...

여담이지만...네이버 블로그에 매번 직접 글을 쓰고..그냥 습관적으로 저장을 누르다간..

간혹..이렇게 글을 날리게 된다..--;;;

컴퓨터 기술은 참..많이 좋아졌는데...언제 이런것까지 다 좋아질까...--;;

그래서...오늘 아침 다시 쓰기 시작한다.

의학 연구를 하다보면..언제나 접하게 되는게 수많은 양의 자료와...통계결과이다. 그 어떤 논문에도 기본적으로 저자가 힘들게 얻어낸 표와 그래프가 등장하게 된다. 또한 이런 표나 그래프를 만들고 나면...논문을 한편 다 쓴것과 같이 뿌듯한 느낌이 들정도이다.

만약 자료의 수가 적다면...일일이 다 세어서 표를 만드는것도 그다지 복잡한 과정은 아니지만, 만약 자료의 수가 수백개에 이른다면...정말 말그대로 대략난감이다...

이런 복잡한 과정을 대신해주는 기능이 엑셀에 제공된다.

예를들어...어떤 학급에서 시험을 본 결과가 다음과 같이 나왔다고 하자.

87 88 55 46 92 52 87 46 74 63 74 83 98 65 84 82 79 75 61 58 79 88 82 71 83 95 84 71 75 92 83 89 35

이정도야 직접 점수의 구간을 나누어서 성적을 도수분포표로 만드는게 어렵지는 않을것이다. 도수분포표...말그대로 어떤 구간을 설정해서 그 구간에 해당하는 자료의 값이 몇개씩 있는가를 표로 만들어 놓은 것이다. 위와같이 33개 정도의 데이터라면 부담 없지만...수백개라면..어떻게 해야 할까...?

그래서 이제부터는 엑셀에서 도수분포표를 만드는 방법을 알아보자.

우선 위의 데이터를 엑셀에 입력해보자. 모든 통계처리의 가장 기본이 되는것이 바로 데이터의 입력이다.

이전의 글들에서 많이 봐왔듯이 어떤 한 그룹의 데이터는 한 컬럼에 입력하는게 이후에 통계처리가 용이해진다. 대충..살펴보니 점수가 40점대에서 90점대로 분포되어 있다. 이 점수들 중에서 가장 큰 점수와 가장 작은 점수, 그리고 데이터의 개수를 알아보자.

위의 그림을 보면 [...]안에 들어가 있는 식이 바로 엑셀에서 제공하는 함수식이다.

즉, D2셀에 = MAX(A2:A32)를 입력한뒤 엔터를 치면 알아서 A2~A32의 자료중에 가장 큰값을 찾아주게 되며 MIN의 경우에도 마찬가지 결과를 보여준다. COUNT라는 명령어는 전체 자료의 개수를 세어주게 된다. 생각보다 편하다...^^

일반적으로 도수분포표를 만드는데 있어서 정해진 규칙은 없지만...도수분포표의 기본적 개념으로 '계급'이라는 용어와 '급간'이라는 용어가 사용된다. '계급'은 위의 경우에는 50점 미만, 50~59점, 60~69...이런식으로 나누면 되겠고...이렇게 되는 경우 '급간'은 9가 될 것이다. 이렇게 점수는 편의상 나눌 수 있겠지만...그렇지 못하는 경우에는 어떻게 해야 할까...?

이럴때는 딱 정해진 규칙이지만 대부분 받아들이고 있는 방법이 다음과 같이 계급의 수와 급간을 정하는 것이다.

위의 방법으로 한번 계급의 수와 급간을 정해보자. 이걸 위해서..열심히 공학용 계산기를 찾았다면...엑셀을 무시하는 처사이다...^^ 다음 그림을 보자.

엑셀에서는 당연히 1/3승...같은 계산도 척척해준다. 결과에 의하면 계급의 수는 3개정도..급간은 20으로..하면 되리라는 결과가 나온다. 하지만 위의 자료는 점수를 조사한 자료로 사회적인 통념이 10점 단위로 그 우열을 가리므로 10점 단위로 도수분포표를 만드는게 더 합당해 보인다. 이렇게..딱 정해진 규칙이 아니므로 참고로 사용하면 된다.

자..이제부터 도수분포표를 만들어 보도록 하자.

우선...급간을 9로 해서 40~49, 50~59...90~100으로 6개의 계급으로 도수분포표를 만들기 위해 이 급간의 제일 큰값을 다음과 같이 입력한다. 그런뒤 그림에 보이는 것과 같이 FREQUENCY라는 함수를 입력한다.

그런뒤 Ctrl+Shift+Enter를 입력하면..도수분포표가 저절로 계산되어 나온다.

FREQUENCY 함수뿐만 아니라 대부분의 함수를 지정하는게 있어서 범위를 직접 A2:A32라고 입력해도 되지만, 마우스로 그 범위를 주욱..선택해주면 저절로 입력이 된다.

FREQUENCY함수의 사용법은 FREQUENCY(data_array, bins_array)이다. data_array부분에는 자료의 위치를, bins_array에는 미리 적어둔 계급의 위치를 지정해 주면 된다. 물론 이 함수를 입력하기 전에 이 결과로 출력될 부분을 선택해 둔뒤 함수를 입력한다. 또 한가지...함수 입력뒤 그냥 엔터만 치면 단 한개의 값만 계산되어 나온다. 이것은...엑셀에서 "배열 수식 (배열 수식: 한 개 이상의 값 집합에 대해 여러 가지 계산을 수행한 다음 한 개의 결과 또는 여러 개의 결과를 반환하는 수식입니다. 배열 수식은 중괄호 { }로 묶으며, Ctrl+Shift+Enter를 눌러 입력합니다.)"이렇게 설명하고 있다.^^

자..이제 멋지게 도수분포표까지 만들었다.

엑셀에서는 이렇게 도수분포표를 만들수도 있지만 '분석도구'라는 기능으로 도수분포표뿐만 아니라 여러가지 통계처리가 가능하도록 하고 있다.

우선 '분석도구'는 기본적으로 기능이 꺼져 있으므로 풀다운메뉴에서 도구->추가기능에서 분석도구부분을 체크해야 사용할 수 있다.

이 분석도구기능이 활성화 되면 풀다운메뉴에서 도구->데이터분석이라는 항목이 생긴다.

이를 이용해서 도수분포표를 만들어 보자. 데이터 분석을 실행하면 다음과 같은 창이 나타난다.

여기서 히스토그램을 선택한다.

위와 같이 나타나면 데이터 범위와 계급구간을 마우스로 지정해주고 출력옵션을 특정셀들로 지정해주면 다음과 같은 도수분포표를 얻을 수 있다.

이 도수분포표에는 '기타'라는 계급이 하나 더 생긴다. 즉, 데이터 중에서 계급에 해당되지 않는 것들이 있는지 확인해 주는 것이다. 그러나 다시한번 데이터가 모두 적절하게 사용되었는지 확인하기 위해 모든 도수를 더해서 전체 자료개수와 비교해보자.

더할 셀들을 선택한위 메뉴 위쪽에 있는 sigam표시를 누르면 자동합산이 된다. 물론 =SUM(자료범위)라는 함수식을 직접 입력해도 된다.

이번에는 각 빈도를 상대도수로 계산해보자. 상대도수는 그 계급의 도수가 전체의 얼마를 차지하는가..하는 비를 보여주는 것이므로 각 도수를 전체 개수 33으로 나누어 주면 된다. 이걸..일일이 계산할 필요없이...엑셀의 기능으로 해보자.

이젠 좀 능숙해 졌다고 가정하고...상대도수가 표시될 셀을 지정하고 함수 = (빈도수의 셀들)/(자료개수)를 입력한뒤 Ctrl+Shift+Enter를 입력하면 다음과 같은 상대도수분포표를 얻을 수 있다.

자...이번엔 누적도수표를 만들어 보자.

상대도수 옆에 누적도수라는 셀을 만들고 첫번째 셀에 = SUM($E$21:E21)을 입력한뒤 엔터를 치면 첫번째 누적도수가 계산되어 나온다. 여기서 $E$21이란 의미는 이 값을 고정하라는 의미이다. 이걸 일일이 $표시를 입력할 필요 없이 E21을 입력한뒤 F4키를 누르면 $E$21로 바뀐다. 이렇게 하면 첫번째 셀을 마우스로 지정한뒤 오른쪽 아래 귀퉁이를 가리키면 마우스 포인트가 +모양으로 바뀌고 이상태에서 아래쪽 까지 드래그 하게 되면 나머지 누적도수가 저절로 계산된다.

즉...고정된 E21셀부터 지정된 셀까지의 값을 더해서 출력해주는 것이다....

생각보다 편하다...^^

자 이와같은 방법으로 도수분포표를 쉽게 쉽게 만들 수 있다.