엑셀 GROUPBY 함수 총정리 | 피벗테이블 뛰어넘는 데이터 분석 끝판왕!

실습 가이드

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.

GROUPBY 함수 기초 사용법

피벗테이블의 한계를 보완하는 새 함수, GROUPBY가 엑셀에 추가되었습니다. 기존 피벗테이블만으로는 구현이 까다로웠던 실시간 보고서와 고급 차트 자동화까지 가능한 GROUPBY 활용법을 강의에서 단계별로 살펴봅니다.

⭐ GROUPBY 함수 기본 구문

1. 먼저 예제파일의 [기초] 시트에서 GROUPBY 함수의 기초 사용법을 살펴보겠습니다. [기초] 시트 상단의 [+] 버튼을 클릭해 그룹을 펼치면 좌측 판매내역 데이터로 작성된 피벗테이블이 보입니다.

   

   오빠두Tip : 그룹 기능 사용법은 아래 기초 입문 강의를 참고하세요!👇
   
2. 판매내역 데이터에서 제품 카테고리별 매출액을 집계해보겠습니다. Q7셀을 선택한 뒤 다음과 같이 GROUPBY 함수를 작성합니다.
   =GROUPBY(예제[카테고리],예제[매출액],SUM)

   

3. 함수를 입력하면 카테고리별 매출액 합계가 집계됩니다. 함수의 세 번째 인수인 '집계 방식'을 SUM, AVERAGE, COUNTA 등으로 변경하면 원하는 집계 방식을 손쉽게 적용할 수 있습니다.

   

4. 피벗테이블처럼 '카테고리와 제품명'을 동시에 기준으로 집계해야 한다면, 행 영역에 두 필드의 범위를 함께 선택합니다. Q7셀에 GROUPBY 함수를 다음과 같이 작성하면 카테고리와 제품명을 기준으로 매출액 평균이 집계됩니다.
   =GROUPBY(예제[[카테고리]:[제품명]],예제[매출액],AVERAGE)

   

5. 다만 "카테고리, 제품명"처럼 인접한 필드가 아니라 "지점, 카테고리"와 같이 서로 떨어진 필드를 함께 기준으로 삼을 때는 다른 방법이 필요합니다. 다음 활용 예제에서 GROUPBY 함수의 다양한 사용법을 단계별로 살펴봅니다.

   

활용1 : 여러 기준으로 데이터 집계하기

GROUPBY 함수와 HSTACK 함수를 조합하면 여러 조건을 기준으로 집계하거나 여러 결과를 동시에 출력할 수 있습니다. VSTACK과 HSTACK 함수의 다양한 활용 예제는 아래 기초 입문 강의에서 자세히 정리했으니 참고하세요!👇

1. HSTACK 함수를 활용해 다양한 조건으로 데이터를 집계하는 방법을 살펴보겠습니다. [기초] 시트에서 Q6:T6 범위의 머리글을 아래 그림과 같이 "지점, 카테고리, 매출액, 할인율"로 변경합니다.
   
2. 먼저 지점과 카테고리를 기준으로 매출액 합계를 집계한 보고서를 작성해보겠습니다. Q7셀에 다음과 같이 행 필드에 HSTACK 함수를 활용해 GROUPBY 함수를 입력하면 지점과 카테고리를 기준으로 매출액이 집계된 보고서가 완성됩니다.
   =GROUPBY(HSTACK(예제[지점],예제[카테고리]),예제[매출액],SUM)

   

3. 값 필드에도 HSTACK 함수를 사용할 수 있습니다. 다음과 같이 값 영역에 HSTACK 함수로 매출액과 할인율을 동시에 입력하면 두 값이 함께 집계됩니다.
   =GROUPBY(HSTACK(예제[지점],예제[카테고리]),HSTACK(예제[매출액],예제[할인율]),SUM)

   

4. 할인율은 비율이기 때문에 합계 대신 평균을 사용해야 의미 있는 결과가 나옵니다. 따라서 다음과 같이 집계 방식에도 HSTACK 함수를 적용해 첫 번째 필드는 합계(SUM), 두 번째 필드는 평균(AVERAGE)으로 집계할 수 있습니다.
   =GROUPBY(HSTACK(예제[지점],예제[카테고리]),HSTACK(예제[매출액],예제[할인율]),HSTACK(SUM,AVERAGE))

   

활용2 : 텍스트 취합하기

1. 기존 피벗테이블은 텍스트 필드를 값 영역에 추가하면 '개수'로 집계되었습니다. 따라서 텍스트를 그대로 취합하려면 '데이터 모델 피벗테이블'을 사용해야 했습니다.

   

   오빠두Tip : 데이터 모델 피벗테이블을 활용해 텍스트 필드를 취합하는 방법은 아래 기초 입문 강의에서 알기 쉽게 정리했으니 참고하세요!👇
   
2. 반면 GROUPBY 함수는 기본 집계 방식으로 텍스트 취합을 지원하므로, 문자로 작성된 데이터도 손쉽게 집계할 수 있습니다. [기초] 시트에서 Q6:R6 머리글을 "지점, 담당자"로 수정합니다.
3. 이어서 Q7셀에 다음과 같이 GROUPBY 함수를 작성하면 지점별 담당자 목록이 한 번에 취합됩니다.
   =GROUPBY(예제[지점],예제[담당자],ARRAYTOTEXT)

   

4. 다만 GROUPBY 함수의 기본 ARRAYTOTEXT 방식은 중복값이 그대로 표시되므로, 중복을 제거하려면 LAMBDA 함수를 함께 사용해야 합니다. GROUPBY 함수를 다음과 같이 수정하면 담당자의 고유 목록이 집계됩니다.
   =GROUPBY(예제[지점],예제[담당자],LAMBDA(r,TEXTJOIN(",",,UNIQUE(r))))

   

   오빠두Tip : LAMBDA 함수는 나만의 사용자 함수를 만들어주는 중요한 자동화 함수입니다. LAMBDA 함수 기초 사용법은 아래 영상을 참고하세요!👇
   

활용3 : 조건부서식으로 실시간 피벗테이블 만들기

GROUPBY 함수와 조건부서식을 조합하면 데이터가 변경될 때 실시간으로 갱신되는 피벗테이블 보고서를 만들 수 있습니다.

1. [기초] 시트에서 Q6:S6 범위의 머리글을 "지점, 카테고리, 매출액"으로 수정한 뒤, Q7셀에 다음과 같이 GROUPBY 함수를 입력합니다.
   =GROUPBY(HSTACK(예제[지점],예제[카테고리]),예제[매출액],SUM)

   

2. GROUPBY 함수의 다섯 번째 인수인 [전체 깊이]를 변경하면 합계와 소계의 위치 및 표시 여부를 지정할 수 있습니다. 기존 피벗테이블은 합계가 항상 아래에 표시되었지만, GROUPBY 함수는 합계 위치를 위/아래 어디로든 지정할 수 있는 장점이 있습니다.
   1(기본값) : 합계만 표시, 2 : 합계/소계 표시
   -1 : 합계를 위에 표시, -2 : 합계/소계를 위에 표시

   

3. 이번에는 합계와 소계를 모두 표시하도록 전체 깊이 인수에 2를 입력합니다. 이후 합계와 소계 행을 강조하기 위한 조건을 살펴보면, Q열에 값이 있고 R열은 비어 있다는 공통점을 확인할 수 있습니다.

   

4. 조건부서식을 적용할 범위로 Q7:S27까지 넉넉하게 선택한 뒤, [홈] 탭 - [조건부서식] - [새 규칙] - [수식을 사용하여 서식을 지정할 셀 결정]을 선택하고 입력창에 다음 수식을 입력합니다.
   =AND($Q7<>"",$R7="")

   

   오빠두Tip : 조건부서식으로 전체 행을 실시간으로 강조하는 원리는 아래 기초 입문 강의를 참고하세요!👇
   
5. [서식] 버튼을 클릭한 뒤 글꼴은 굵게, 테두리는 위쪽과 아래쪽에 추가합니다. 서식을 적용한 후 [확인] 버튼을 클릭합니다.

   

6. 이제 표에 데이터를 추가하면, GROUPBY로 작성한 피벗테이블이 실시간으로 갱신되는 것을 확인할 수 있습니다.

   

활용4 : SUBTOTAL + 필터배열 활용하기

1. GROUPBY 함수와 슬라이서를 활용하면 실시간으로 필터링되는 보고서를 만들 수 있습니다. 예제파일에서 [슬라이서활용] 시트로 이동한 뒤, 표 오른쪽에 다음 그림과 같이 [조건] 필드를 추가합니다.

   

2. 이어서 조건 필드에 다음과 같이 SUBTOTAL 함수를 작성하면 조건값으로 1이 계산됩니다.
   =SUBTOTAL(103,[@매출액])
   // SUBTOTAL 함수는 화면에 보이는 셀을 대상으로 집계합니다.
   //103은 개수를 셉니다.

   

3. 이제 표에 실시간으로 필터를 적용할 슬라이서를 추가해보겠습니다. 표를 선택한 후 [테이블 디자인] 탭 - [슬라이서 삽입]을 클릭하고 '지점' 슬라이서를 추가합니다.

   

4. B15셀에 다음과 같이 필터 배열을 활용하여 조건이 1인 항목만 집계하도록 GROUPBY 함수를 작성합니다.
   =GROUPBY(예제_2[제품명],예제_2[매출액],SUM,,,,예제_2[조건]=1)

   

5. 슬라이서 버튼을 클릭하면 필터링된 데이터에 한해 집계가 갱신됩니다. SUBTOTAL 함수는 화면에 보이는 셀만 집계하므로, 필터로 숨겨진 범위는 조건값이 0으로 계산되어 집계에서 자동으로 제외됩니다. SUBTOTAL 함수의 동작 원리는 영상 강의에서 알기 쉽게 정리했으니 참고하세요.

   

활용5 : GROUPBY + 슬라이서 트리맵 차트 만들기

1. 마지막으로 GROUPBY 함수와 슬라이서, 트리맵 차트를 활용한 대시보드를 만들어 보겠습니다. 예제파일에서 [오빠두 패션 데이터] 시트로 이동한 뒤, 아래쪽 표에 다음과 같이 조건 열을 추가합니다.

   

2. 표를 선택하고 [테이블 디자인] 탭 - [슬라이서 삽입]에서 지점, 카테고리 슬라이서를 추가한 뒤, 대시보드 영역의 적절한 위치로 옮겨 배치합니다.
3. 이제 데이터를 집계하기 위한 GROUPBY 함수를 작성합니다. A2셀에 다음과 같이 GROUPBY 함수를 입력합니다.
   =GROUPBY(매출[[카테고리]:[제품명]],매출[매출액],SUM,,0,,매출[조건]=1)
   // 필터링 된 데이터에서 카테고리 제품명을 기준으로 매출액을 집계합니다. (합계 표시 안함)

   

4. GROUPBY로 반환된 범위에서 임의의 셀을 선택한 뒤 [삽입] 탭 - [추천차트] - [모든 차트]에서 트리맵 차트를 선택해 추가합니다. 기존 피벗테이블 보고서는 트리맵, 선버스트, 주식형 같은 고급 차트를 만들기에 제약이 있었던 반면, GROUPBY 함수는 다양한 고급 차트를 자유롭게 구성할 수 있는 장점이 있습니다.

   

5. 차트 제목을 지우고 적절한 위치와 크기로 정돈하면, 버튼을 클릭해 필터링할 수 있는 실시간 대시보드가 완성됩니다.

   

6. 이전 강의에서 소개한 스타일시트를 활용하면 다음과 같이 깔끔한 시각화 보고서를 완성할 수 있습니다. 엑셀 스타일시트는 아래 자료실에서 확인하세요!👇