[엑셀, 어렵지 않다 XIV] 부분합으로 데이터 요약하기

[호영]

언뜻 보기에 복잡하게 보이는 이 세상도 기준에 따라 분류를 해 놓으면 설명하기가 쉬워진다. 예를 들어 어떤 다수의 사람들을 나이에 따라, 사는 곳에 따라, 졸업학교에 따라 분류를 하면 이해하기 혹은 설명하기가 쉬워진다. 때론 같은 편, 다른 편, 구분 짓는 편가르기가 더 많은 분란을 일으키기도 하지만 말이다. 어쨌든 수많은 데이터를 다루는 엑셀에도 이렇게 동일한 부분만을 찾아 필요에 따라 정보를 빼낼 수 있는 기능이 있다. 그 기능이 바로 부분합이다.

지금까지 했던 필터링 등의 작업처럼 데이터를 가공하고 걸러내는 작업도 중요하지만, 데이터를 요약해서 분석하는 것도 매우 중요한 데이터베이스의 작업 중 하나이다. 엑셀은 데이터의 요약을 위해 많은 도구들을 제공하는데, 그 중에서 가장 핵심적인 기능이 부분합이다. 부분합이란 기준을 정한 부분에 대한 계산을 하는 것으로, 합계나 평균 같은 요약 함수로 부분합의 값을 계산할 수 있다. 예를 들어 서울 지역 출신들의 평균 월수입을 요약하거나, 직급이 과장 이상인 사람들의 2008년도 수입 합계를 구하는 등의 작업을 할 수가 있는 것이다.

자, 그럼 한 번 같이 해보도록 하자. 다음과 같은 성적 일람표가 있다. 



예를 들어 부서별(트로트부, 알엔비부, 댄스부) 점수의 국어/전산/평균/석차 등에 관한 요약정보를 얻고 싶을 때, 부분합을 사용하면 된다.

1. 원하는 기준으로 정렬하라.
부분합을 이용해 데이터를 요약하기 위해서는 우선 원하는 기준으로 정렬을 해야 한다. 이 작업은 매우 중요하므로 꼭 기억해두자. 여기서는 부서별로 부분합을 계산하고자 하므로 일단 부서별 오름차순으로 정렬을 시키면 된다.
오름차순이나 내림차순 정렬은 기본 메뉴 중 편집에 있고, 원하는 영역을 드래그한 후 오른쪽 클릭을 하면 다음과 같은 메뉴가 나오기도 한다. 
    

정렬을 하고 나면 다음과 같은 상태가 된다. 



2. 정렬이 끝났으면 엑셀 메뉴에서 [데이터]의 [부분합]을 선택한다. 그러면 다음과 같은 대화상자가 나타난다. 여기에서 원하는 부분합을 설정해야 한다.

그림의 맨 위에 있는 “그룹화할 항목”은 위에서 정렬(Sort)한 기준, 부서를 선택한다.
“사용할 함수”는 부분합에 사용할 함수를 정하는 곳이다. 여기에는 합계/개수/평균/최대값/최소값/수치 갯수/표본 표준 편차/표준 편차/표본 분산/분산 등이다. 통계에 사용되는 함수는 거의 모두 들어있다. 여기서는 일단 합계를 선택하자. 설정이 모두 끝나면 확인을 클릭한다.

3. 다음과 같은 시트에 데이터의 윤곽이 자동으로 변경되면서 부분합이 만들어진다. 



4. 현재는 하위 데이터가 모두 보이는 상태이다. 이 상태에서 하위 데이터를 숨기고 그 석차의 합계만 보고 싶다면 왼쪽의 윤곽기호 [-]를 클릭해서 [+]표시로 바꾸면 된다.



이렇게 윤곽기호를 이용해서 개별적으로 하위데이터를 숨기거나 볼 수 있지만, 이름 상자 바로 밑에 생긴 윤곽 번호를 통해서 전체적인 데이터 수준을 한번에 조정할 수도 있다. 3번은 모든 하위 데이터를 보여주고(부분합을 처음 만들었을 때와 같다.) 1번은 모든 하위데이터를 숨기고 전체 평균만을 보여준다. 2번은 그 중간 단계로서 다음과 같이 각 지역별 평균만 출력해 준다. 



5. 이번에는 부분합을 통해 요약된 데이터를 이용해 차트를 만들어보자. 부분합에서 2수준을 선택하고 부서와 석차합계를 범위로 선택한다. 기본적으로 현재 화면에 보이는 부분만 차트로 나타나게 되지만, 데이터 윤곽 수준을 변경하면 변경된 내용이 차트에 그대로 반영된다. 
 


부분합을 잘 활용하면 이렇게 특정 기준에 따른 데이터를 요약해서 분석할 수 있다. 




부분합 중첩하기

1. 이번에는 부분합이 만들어진 상태에서 다른 항목을 추가해보는 작업을 해보자. 이 기능을 부분합의 중첩이라고 하는데, 예를 들어 현재는 부분합에 석차의 합계만 계산되어 있다. 여기다 다른 것을 추가해보자. 



2. 이어서 사용할 함수는 평균을 구할 것이므로 “평균”을 선택하고, 아래 옵션에서 “새로운 데이터로 대치”의 선택을 해제해 준다. “새로운 데이터로 대치”를 선택하면 기존의 부분합 데이터는 무시되고 다시 부분합이 만들어지게 된다. 
 


반드시 “새로운 값으로 대치”의 선택을 해제해야 한다. 만약 해제하지 않으면 전에 계산했던 석차에 관한 요약은 없어지고, 지금의 국어 부서별 평균만 남게 된다. 이점을 유의해야 한다.

3. 다음 그림처럼 2개의 항목이 요약된 부분합 시트가 만들어진다.
 

어떤가? 참 쉽고 재미있다. 데이터의 구조가 변경되었으므로 윤곽기호나 윤곽번호도 한 수준 증가되어 변경되었다. 클릭하면서 어떤 데이터 수준으로 화면에 보이게 되는지 해보길 바란다.

부분합 제거하기

지금까지 부분합을 사용하는 방법을 배워보았다. 그런데 다시 원본 데이터 형식으로 돌아가려면 어떻게 해야 할까? [데이터]의 [부분합]을 선택한 후 부분합 대화상자에서 [모두 제거]를 클릭하면 원본데이터로 복귀할 수 있다.



자, 지금까지 부분합에 대해 알아보았다. 사실 연습을 범위가 작은 데이터로 해서 실감이 안 날지 모르지만, 만약 전국민, 혹은 한 학교의 전교생의 데이터를 가지고 부분합을 해보면 얼마나 간편하고 실용적인지 알게 될 것이다. 이제 데이터를 요약해서 보고서를 작성하거나 차트를 만들 때 부분합으로 간편하게 해결해보자.@