Excel 지식인 011. 엑셀을 이용하여 도수분포표 작성하기

전체 목차
001. lookup 함수를 이용한 양도세 구간 구하기
002. 공백 셀 제거 후 정렬
003.데이터 분석 후 행 추가하기 (나누기, 나머지, 정수)
004. 목록상자, 토글버튼 연동
005. 다른 시트에 있는 특정 데이터를 복사해오기
006. 근무시간에서 특정시간 구하기 (야간 근무 인정 시간)
007. 상반기/하반기 매출 집계(SUMIF)
008. 콤보박스 활용하여 필터 변경하기

009. 특정 위치에 있는 데이터만 추출하기 (address, indirect)

010. 엑셀에서 숫자를 특정 패턴으로 입력하기, 숫자 건너뛰어 입력하기

 

포스트 목차.

• 01. 문제
• 02. 문제해결 방법
• 03. 예제 및 설명

 

01. 문제

다음 키와 체중자료로 각각 도수분포표의 빈 칸을 작성하시오. 키와 체중의 상관표를 작성하시오.

 

문제는 12명의 키와 체중에 대한 정보를 기반으로 키와 체중에 대한 도수분포표를 각각 작성한 후, 키와 체중에 대한 상관표까지 작성하는 것으로 보인다. 

 

02. 문제해결 방법

(1) 개념 정리

 엑셀에서 문제를 어떤방식으로 해결할지를 설명하기 전에, 도수분포표에 나오는 여러 용어에 대해서 정리를 해보자.  (작성자도..기억안나서 찾아봄)

 - 계급구간: 도수분포표 작성자가 의도를 갖고 나눈 통계값의 구간

 - 도수: 계급구간에 해당하는 통계값의 갯수

 - 상대도수: 도수의 총합(전체데이터)에 대한 각 계급의 도수의 비율

 - 누적도수: 시작계급부터 해당계급까지의 도수의 총합 

 - 누적상대도수: 도수의 총합에 대한 각 계급의 누적도수의 비율

 - 중앙값: 데이터의 크기별로 정렬했을 때, 중간 위치에 해당하는 값, 데이터의 수가 짝수 개일 때는 중앙에서 가까운 두수의 산술평균으로 구한다.

 

도수분포표는 구간별로 데이터가 어떤 식으로 분포되어 있는지를 나타내는 표라고 볼 수 있다.

 

(2) 결국에는 조건을 만족하는 데이터의 숫자를 세는 연산이 필요하다. 

 - COUNTIFS 또는 배열수식의 논리식 + SUM 또는 데이터베이스 관련함수 DCOUNT를 활용할 수 있을 것이다. 

 이러한 함수들은 이전 포스트에서 다룬적이 있으므로, 자세한 내용은 다음링크를 참고하자.

 - 상관표는 COUNTIFS로 해결하기는 힘들것으로 보인다. 배열수식의 논리식으로 해결해보자.

 

 (3) 중앙값은 엑셀에서 제공하는 Median 함수를 이용할 수 있다.

 

 

03. 예제 및 설명

위에서 제시한 문제 해결 방법 중에서 COUNTIFS함수와 배열수식의 논리식 조합을 이용하여 문제를 해결해보자.

 

(1) 도수 구하기

도수는 위의 그림처럼 COUNTIFS Criteria1, Criteria2에 이상조건, 미만조건을 넣어서 구할 수 있다.

=COUNTIFS($A$2:$A$13, F3, $A$2:$A:$13, G3)

F3, G3에는 계급구간의 조건이 들어가 있다. 이 부분은 텍스트 형태로 수식의 F3, G3부분에 넣어도 상관없다.

 

(2) 중앙값 구하기

중앙값은 MEDIAN 함수를 이용하면 구할 수 있지만, 우리가 원하는 작업은 MEDIANIFS 와 같은 함수이다. 특정 조건에 맞는 데이터에 대해서만 중앙값을 구해야한다. 배열수식을 이용해보자.

 

위의 그림처럼 두 개의 조건에 대한 배열 수식을 IF안에 넣어서 배열을 만든 후, MEDIAN으로 중앙값을 계산할 수 있다. 수식을 입력후에 Ctrl + Shift + Enter를 눌러야 된다는 것을 잊지말자

 

=MEDIAN(IF(($A$2:$A$13>=D3)*($A$2:$A$13<E3),$A$2:$A$13,""))

 

IF함수는 A2:A13의 데이터 중에 150이상, 160미만을 동시에 만족할 경우, A열에 있는 값을 반환하고, 만족하지 못할 경우 공백을 반환하도록 작성되어 있다. MEDIAN는 숫자가 있는 값에 대해 중앙값을 반환해준다.

 

자세한 내용은 다음링크의 엑셀에서 배열 수식 사용하기를 참고하자.

 

다른 계급의 중앙값은 처음에 작성한 중앙값 셀을 복사한 후 붙여넣기 하면된다. 위의 수식에서 데이터의 범위 부분은 절대주소로 작성하여 복사하여 사용하기 용이하게 만들었다. 

 

(3) 상대도수 및 누적상대도수 구하기

상대 도수를 구하려면 전체 도수(전체 데이터의 수: 12)를 알아야 한다. 이것 count함수로 구하자. 상대도수는 도수를 전체데이터수로 나누면 되는것으로, 전체 데이터 수를 특정셀에 입력한 후에 나눠주는 식으로 구할 수  있다.

 

12가 들어 있는 셀을 절대주소로 참조한 이유는 밑에 있는 3가지 계급구간에도 해당값을 복사하기 위해서이다.  

 

누적상대도수는  현재 계급까지의 도수의 총합을 전체데이터 값으로 나눈값이다. 

SUM의 시작 범위만 절대주소로 만들어주면 밑에 있는 셀로 복사할 경우, 누적 값이 만들어진다.

 

절대주소의 활용에 대해서는 이전 포스트를 참고하기 바란다. 다음링크의 엑셀 절대주소 vs 상대주소 부분에 있다.

 

(4) 상관표 작성하기

 

상관표는 배열 논리식을 이용하여 작성해보자.

첫셀에 다음과 같은 수식을 작성한다.

=SUM(IF(($A$2:$A$13>=P$1)*($A$2:$A$13<P$2)*($B$2:$B$13>=$M4)*($B$2:$B$13<$N4),1,""))

복잡해 보이지만 총 4개의 논리식이 AND 연산으로 묶여 있는 것과 같다.

키의 이상 조건, 키의 미만 조건, 체중의 이상 조건, 체중의 미만 조건

이 4가지 조건을 만족하면, 1을 반환하고 , 만족하지 못할 경우 공백을 반환한다.

IF문이 반환하는 배열을 SUM함수로 넣으면 키와 체중 조건을 만족하는 데이터의 수를 구할 수 있다.

 

수식 입력 시에는 CTRL +SHIFT + ENTER를 사용하자.

P$1, P$2, $M4, $N4 처럼 열이나 행에만 절대 주소를 적용한 이유는 첫셀의 값을 다음셀에 그대로 복사하기 위함이다. 

다음링크의 엑셀 절대주소 vs 상대주소 부분을 참고하자.

 

 

 

(5) 완성된 모습

 

도수분포표

 

상관표

같이보면 좋은 글

• 엑셀에서 배열 수식 사용하기(1)
• 엑셀에서 배열 수식 사용하기(2)
• SUMIF 함수, SUMIFS 함수, COUNTIF 함수, COUNTIFS 함수
• 엑셀 절대주소 vs 상대주소 (절대주소 활용법)
• DCOUNT, DCOUNTA, DSUM, DGET, DPRODUCT, DVAR, DSTDEV 함수