그룹과 개요를 알아보았으니 자연스럽게 엑셀의 부분합 기능으로 이어지는 게 맞는 것 같습니다. 사실 부분합이 무엇인지는 이미 이전 강좌에서 설명했습니다. 그룹을 만들기 위해선 통계 행(열)이 반드시 있어야 했습니다. 그리고 자동 개요를 만들기 위해선 그 통계 행(열)이 통계 함수로 작성돼 있어야 했죠. 바로 그 통계 데이터가 "부분합"입니다. 이름으로만 보면 "합"이니 SUM 함수로 작성된 것을 말하는 것 같지만, 평균(AVERAGE), 개수(COUNT), 최대값(MAX), 최소값(MIN) 등 모든 통계를 통칭하는 개념입니다.
그룹과 개요에서 알아본 예제는 다소 복잡한 표였기에 함수를 통해 사용자가 만들어야 했습니다. 하지만 간단한 데이터베이스형 표에서는 이 "부분합"을 자동으로 만들 수 있습니다. 이번 강좌는 부분합을 자동으로 만드는 엑셀의 부분합 기능을 알아보겠습니다.
혼돈하실 수 있으니 조금 정리하고 진행하겠습니다. 표에서 특정 부분의 통계를 작성한 것을 "부분합"이라 했습니다. 명칭을 말하는 것이고요. 이 부분합을 자동으로 만드는 기능도 "부분합"입니다. "자동 부분합" 정도로 기능명이 붙었으면 좋았겠지만 제가 정하는 것은 아니니까요. 그러니까 통계 데이터를 부르는 명칭과 기능명이 동일합니다. 지금 알아보려는 것은 기능입니다. 혼돈하시지 않으셨으면 좋겠습니다. 예제를 확인하겠습니다.
그동안 많이 사용한 예제입니다. 편의를 위해 데이터양을 줄이고 간단하게 만들었습니다. 레이블과 레코드만으로 이루어진 데이터베이스형 표입니다.
이런 데이터베이스형 표로 통계를 작성하는데 가장 좋은 방법은 역시 "피벗 테이블"입니다. 원하는 조건을 원하는 구조로 어떤 형식이든 만들 수 있습니다. 원본 표에 영향을 미치지도 않으며 데이터의 양이 많아도 보기 편한 보고서 형태로 만들 수 있습니다. "피벗 테이블"은 다음 강좌부터 소개할 예정입니다. 꼭 보시길 추천해 드립니다.
어쨌든 지금은 부분합 기능을 알아보는 순서이니 이 표를 이용해 보겠습니다. 예제로 이 표를 선택한 이유가 있겠죠. 자동 부분합 기능은 부분합을 만들 수 있는 조건이 있습니다. 바로 이 표처럼 레이블과 레코드로 만들어진 표여야만 합니다. 중간에 병합된 셀이 있어도 안 됩니다. "매우 제한적이군"이라 생각하실 수 있는데 사실 데이터를 정리한 표는 대부분 이런 형식입니다. 그리고 통계가 필요하다면 이 데이터베이스형 표를 기준으로 다시 만듭니다. 그러니 그렇게 제한적이라 생각할 필요는 없습니다.
조건은 일단 알아보았고, 그럼 이제 내가 이 쇼핑몰의 관리자가 돼 보겠습니다. 내가 관리자라면 이 표로 무엇을 보길 원할까요?
먼저는 총판매 금액을 알고 싶겠죠. 그다음은 레이블 항목에 따른 통계가 궁금해질 것입니다. 많은 주문을 한 지역이 어디인지, 배송사는 어디를 많이 사용했는지, 제품분류는 어느 쪽이 많은지 그리고 그에 따른 금액의 합계나 평균이 궁금하겠죠. 그렇다면 지금까지 알아 온 방법 중 가장 좋은 방법은 필터를 사용하고 SUBTOTAL 함수를 사용하는 것입니다.
하지만 지금처럼 간단한 표라면, 클릭 몇 번으로 손쉽게 통계를 만들 수 있습니다.
이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.
그럼, 먼저 간단하게 "제품분류별 주문 개수"를 알아보겠습니다.
그런데 부분합 기능을 사용하기 전에 반드시 선행 작업이 필요합니다. 일단 위 표를 프린터 했다고 가정해 보겠습니다. 제품분류별로 개수를 세기 위해선 "제품분류"에서 같은 분류를 하나씩 세야겠죠. 가령 "가구"의 개수를 세려면 위에서부터 셀 값이 "가구"인 것을 찾을 것입니다. 그런데 중간중간 "사무용품"도 있고 "전자제품"도 있습니다. 세기 불편합니다. 같은 분류가 모여있으면 훨씬 세기 편할 것입니다. 같은 분류끼리 정리하는 방법이 필요합니다. 바로 "정렬"이죠. 즉 부분합 기능을 사용하기 위해선 "정렬" 작업이 반드시 선행돼야 합니다.
- 정렬하고자 하는 항목의 아무 셀이나 클릭하여 선택합니다.
- "홈 탭 > 정렬 및 필터 > 텍스트 오름차순 정렬"을 클릭합니다.
- 여기선 단순히 제품분류별로만 묶이면 되니, 오름차순이든 내림차순이든 상관없습니다. 원하는 순서로 정렬하시면 됩니다.
결과, 그림처럼 같은 분류끼리 모입니다. 이제 부분합을 만들기 위한 조건을 만족하는 표에서 선행 작업까지 모두 마쳤습니다. 부분합 기능을 적용합니다.
- 표에서 아무 셀이나 선택된 상태에서 "데이터 탭 > 부분합"을 클릭합니다. ⇒ 표가 전체 선택되고 "부분합" 대화상자가 나옵니다.
- "그룹화할 항목"은 기본적으로 선택한 표의 첫 항목(주문번호)이 선택돼 있습니다.
- "사용할 함수"는 기본적으로 합계로 나옵니다.
- "부분합 계산 항목"은 선택한 표에 숫자 값을 가진 항목으로 선택돼 있습니다. 숫자를 가진 항목이 "금액(원)"이므로 기본은 "금액(원)"이 선택된 상태입니다.
- "그룹화할 항목"은 정렬을 통해 같은 셀 값으로 묶은 항목입니다. "제품분류"로 변경합니다.
- "사용할 함수"는 "그룹화할 항목"에서 묶은 데이터에 적용하고자 하는 함수입니다. 지금의 경우는 셀의 개수만 세면 됩니다. 개수로 변경합니다.
- "부분합 계산 항목"은 "그룹화할 항목"에서 묶은 데이터 중 "사용할 함수"를 적용할 항목입니다. 즉 "배송사"의 개수를 세고 싶다면 "배송사"로, "지역"을 세고 싶다면 "지역"으로 선택하면 됩니다. 지금은 "제품분류"의 개수만 필요하니 "제품분류"만 체크하고 나머지는 체크 해제합니다. 물론 다른 항목도 궁금하다면 체크해도 되지만, 셀의 개수를 세는 것이니 모두 동일하게 나오겠죠.
- 최종 확인 버튼을 클릭합니다.
결과, 그림처럼 해당 항목 열(제품분류), 동일 분류 아래로 통계치가 작성됩니다. 그리고 최하단에는 전체 통계(개수)까지 나오는 것을 확인할 수 있습니다.
그뿐만 아니라 정렬한 항목으로 그룹화된 개요도 나타납니다. 개요가 있으니 "수준 단축 단추"와 "표시/숨기기 단추"로 원하는 통계만 보는 것도 가능해진 것입니다.
의견글 입니다.
()
