엑셀 GROUPBY 함수, 20분 총정리 특강
엑셀 GROUPBY 함수 총정리 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 엑셀 GROUPBY 함수 기초-활용 15분 총정리 특강예제파일
라이브 강의 전체영상도 함께 확인해보세요!
위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.
GROUPBY 함수 기초 사용법
피벗테이블의 한계를 뛰어넘는 함수, GROUPBY가 엑셀에 새롭게 추가되었습니다! 기존 피벗테이블만으로는 구현이 어려웠던 실시간 보고서, 고급 차트까지 자동화는 GROUPBY 꿀팁을 이번 강의에서 확인해보세요!👇
⭐ GROUPBY 함수 기본 구문
=GROUPBY(행영역, 값영역, 함수, [머리글여부], [합계표시], [정렬순서], [필터배열], [필드관계])
: 데이터를 그룹화하여 집계된 요약 보고서를 반환합니다.
GROUPBY 함수의 자세한 기초 사용법은 아래 링크를 확인하세요!
- 먼저 예제파일의 [기초] 시트에서 GROUPBY 함수 기초 사용법을 알아보겠습니다. [기초] 시트 상단의 [+] 버튼을 클릭해서 그룹을 펼치면 좌측 판매내역 데이터로 만든 피벗테이블이 있습니다.
+ 버튼을 클릭해서 그룹을 펼치면 피벗테이블을 확인할 수 있습니다. 오빠두Tip : 그룹 기능 사용법은 아래 기초 입문 강의를 참고하세요!👇
- 판매내역 데이터에서 제품 카테고리별 매출액을 집계해보겠습니다. Q7셀을 선택하고 다음과 같이 GROUPBY 함수를 작성합니다.
=GROUPBY(예제[카테고리],예제[매출액],SUM)
Q7셀에 GROUPBY 함수를 작성합니다. - 함수를 입력하면 카테고리별 매출액 합계가 집계되고, 함수의 3번째 인수인 '집계 방식'을 SUM, AVERAGE, COUNTA 등으로 선택해서 집계 방식을 편리하게 변경할 수 있습니다.
집계 방식을 편리하게 변경할 수 있습니다. - 만약 피벗테이블과 같이 '카테고리와 제품명'을 기준으로 데이터를 집계해야 할 경우, 행 영역으로 범위를 선택합니다. Q7셀에 GROUPBY 함수를 다음과 같이 작성하면 카테고리와 제품명 기준 매출액이 집계됩니다.
=GROUPBY(예제[[카테고리]:[제품명]],예제[매출액],AVERAGE)
행 필드로 범위를 입력해서 여러 기준으로 데이터를 집계할 수 있습니다. - 하지만 "카테고리, 제품명"이 아닌 "지점, 카테고리"와 같이 서로 떨어진 필드를 선택할 경우에는 다른 방법을 사용해야 하는데요. 아래 예제를 통해 GROUPBY 함수의 활용법을 하나씩 알아보겠습니다.
서로 떨어진 범위를 입력하려면 다른 방법을 사용해야 합니다.
활용1 : 여러 기준으로 데이터 집계하기
GROUPBY 함수와 HSTACK 함수를 조합하면 여러 조건을 기준으로 집계하거나 여러 결과를 동시에 출력할 수 있습니다. VSTACK & HSTACK 함수의 다양한 활용 예제는 아래 기초 입문 강의에서 자세히 정리했으니 참고하세요!👇
- 이번에는 HSTACK 함수를 사용해서 다양한 조건으로 데이터를 집계하는 방법을 알아보겠습니다. [기초] 시트에서 Q6:T6 범위 머리글을 아래 그림과 같이"지점, 카테고리, 매출액, 할인율"로 변경합니다.
[기초] 시트에서 머리글을 변경합니다. - 먼저 지점과 카테고리를 기준으로 매출액 합계를 집계한 보고서를 만들어보겠습니다. Q7셀에 다음와 같이 행 필드에 HSTACK 함수를 활용하여 GROUPBY 함수를 작성하면 지점과 카테고리를 기준으로 매출액이 집계된 보고서가 완성됩니다.
=GROUPBY(HSTACK(예제[지점],예제[카테고리]),예제[매출액],SUM)
HSTACK 함수를 사용해 서로 떨어진 두 범위를 기준으로 데이터를 집계합니다. - 값 필드에도 HSTACK 함수를 사용할 수 있습니다. 아래와 같이 값 영역에 HSTACK 함수로 매출액과 할인율을 동시에 입력하면 값으로 매출액과 할인율이 동시에 집계됩니다.
=GROUPBY(HSTACK(예제[지점],예제[카테고리]),HSTACK(예제[매출액],예제[할인율]),SUM)
HSTACK 함수는 값 영역에도 사용할 수 있습니다. - 할인율은 '비율'이기 때문에, 집계 방식으로 합계 대신 평균을 사용해야 올바른 결과를 얻을 수 있습니다. 따라서, 아래와 같이 집계 방식에도 HSTACK 함수를 사용해서 첫번째 필드는 합계(SUM), 두번째 필드를 평균(AVERAGE)으로 집계할 수 있습니다.
=GROUPBY(HSTACK(예제[지점],예제[카테고리]),HSTACK(예제[매출액],예제[할인율]),HSTACK(SUM,AVERAGE))
집계방식에 HSTACK 함수를 사용하면 각 필드별 다른 방식으로 데이터를 집계할 수 있습니다.
활용2 : 텍스트 취합하기
- 기존 피벗테이블은 텍스트 필드를 값으로 추가하면 '개수'로 집계되었습니다. 따라서, 텍스트로 취합하려면 '데이터 모델 피벗테이블'을 사용해야만 했습니다.
피벗테이브로 텍스트를 취합하려면 데이터모델의 측정값을 사용해야 합니다. 오빠두Tip : 데이터 모델 피벗테이블을 활용해 텍스트 필드를 취합하는 방법은 아래 기초 입문 강의에서 알기 쉽게 정리했으니 참고하세요!👇
- 하지만 GROUPBY 함수는 기본 집계 방식으로 텍스트 취합을 제공하므로 문자로 작성된 데이터도 편리하게 집계할 수 있습니다. [기초] 시트에서 Q6:R6 머리글을 "지점, 담당자"로 수정합니다.
- 이후 Q7 셀에 다음과 같이 GROUPBY 함수를 작성하면 지점별 담당자 목록이 취합됩니다.
=GROUPBY(예제[지점],예제[담당자],ARRAYTOTEXT)
GROUPBY 의 집계 함수로 'ARRAYTOTEXT'를 선택합니다. - 하지만 GROUPBY 함수에서 기본으로 제공하는 ARRAYTOTEXT 방식은 중복값이 그대로 표시되어 중복값을 제거하려면 LAMBDA 함수를 사용해야 합니다. GROUPBY 함수를 아래과 같이 수정하면 담당자 고유 목록이 집계됩니다.
=GROUPBY(예제[지점],예제[담당자],LAMBDA(r,TEXTJOIN(",",,UNIQUE(r))))
텍스트 고유값을 집계해야 할 경우, LAMBDA 함수를 사용합니다. 오빠두Tip : LAMBDA 함수는 나만의 사용자 함수를 만들어주는 중요한 자동화 함수입니다. LAMBDA 함수 기초 사용법은 아래 영상을 참고하세요!👇
활용3 : 조건부서식으로 실시간 피벗테이블 만들기
GROUPBY 함수와 조건부서식을 활용하면 데이터가 변경되었을 때 실시간으로 업데이트 되는 피벗테이블 보고서를 만들 수 있습니다.
- [기초] 시트에서 Q6:S6 범위의 머리글을 "지점, 카테고리, 매출액"으로 수정한 후, Q7셀에 다음과 같이 GROUPBY 함수를 입력합니다.
=GROUPBY(HSTACK(예제[지점],예제[카테고리]),예제[매출액],SUM)
지점, 카테고리별 매출액 합계를 집계하도록 GROUPBY 함수를 작성합니다. - GROUPBY 함수의 5번째 인수인 [전체 깊이]를 변경하면 피벗테이블의 합계와 소계의 위치와 표시여부를 선택할 수 있습니다. 기존 피벗테이블은 합계가 항상 아래에 표시되었지만, GROUPBY 함수는 합계의 위치를 위/아래로 선택해서 표시할 수 있습니다.
1(기본값) : 합계만 표시, 2 : 합계/소계 표시
-1 : 합계를 위에 표시, -2 : 합계/소계를 위에 표시
GROUPBY 함수는 합계/소계 행의 위치를 지정할 수 있습니다 - 이번에는 합계와 소계를 모두 표시하도록 전체 깊이를 2로 입력합니다. 이후 합계와 소계행을 강조하기 위한 조건을 살펴보면, Q열에 값이 있고 R열이 비어있다는 것을 확인할 수 있습니다.
합계와 소계행은 지점에 값이 있고, 카테고리가 비어있는 행입니다. - 조건부서식을 적용할 범위로 Q7:S27 까지 범위를 넉넉하게 선택한 후, [홈] 탭 - [조건부서식] - [새 규칙] - [수식을 사용하여 서식을 지정할 셀 결정]을 선택하고 입력창에 아래와 같이 수식을 입력합니다.
=AND($Q7<>"",$R7="")
조건부서식 - 새 규칙에서 수식으로 조건부서식 규칙을 작성합니다. 오빠두Tip : 조건부서식으로 전체 행을 실시간으로 강조하는 원리는 아래 기초 입문 강의를 참고하세요!👇
- [서식] 버튼을 클릭하고 글꼴을 굵게, 테두리는 위쪽/아래쪽 테두리를 추가합니다. 서식을 적용한 후 [확인] 버튼을 클릭합니다.
조건부서식으로 강조할 서식을 지정합니다. - 이제 표에 데이터를 추가하면, GROUPBY 로 작성한 피벗테이블이 실시간으로 업데이트됩니다.
표에 데이터를 추가하면 집계 보고서가 실시간으로 업데이트됩니다.
활용4 : SUBTOTAL + 필터배열 활용하기
- GROUPBY 함수와 슬라이서를 활용하면 실시간으로 필터링되는 보고서를 만들 수 있습니다. 예제파일에서 [슬라이서활용] 시트로 이동한 후, 표 오른쪽에 아래 그림과 같이 [조건] 필드를 추가합니다.
슬라이서 활용시트에서 표 우측에 조건 열을 추가합니다. - 이후 조건 필드에 다음과 같이 SUBTOTAL 함수를 작성하면 조건으로 1이 계산됩니다.
=SUBTOTAL(103,[@매출액])
// SUBTOTAL 함수는 화면에 보이는 셀을 대상으로 집계합니다.
//103은 개수를 셉니다.
SUBTOTAL 함수를 입력합니다. - 이제 표에 실시간으로 필터를 적용할 슬라이서를 추가해보겠습니다. 표를 선택한 후 [테이블 디자인] 탭 - [슬라이서 삽입]을 클릭하고 '지점' 슬라이서를 추가합니다.
테이블 디자인 - 슬라이서 삽입에서 '지점' 슬라이서를 추가합니다. - B15셀에 다음과 같이 필터 배열로 조건이 1인 경우에만 집계하도록 GROUPBY 함수를 작성합니다.
=GROUPBY(예제_2[제품명],예제_2[매출액],SUM,,,,예제_2[조건]=1)
조건필드의 값이 1인 경우만 집계하도록 GROUPBY 함수를 작성합니다. - 이제 슬라이서 버튼을 클릭하면 필터링 된 데이터에 대해서만 집계가 되는 것을 확인할 수 있습니다. SUBTOTAL 함수는 화면에 보이는 셀만 집계하므로, 필터가 걸려서 숨겨진 범위는 조건이 '0'로 계산되어 집계에서 제외됩니다. SUBTOTAL 함수가 동작하는 원리는 영상 강의에서 알기 쉽게 정리했으니 참고하세요!
슬라이서를 클릭하면 선택한 데이터에 대해서만 집계됩니다.
활용5 : GROUPBY + 슬라이서 트리맵 차트 만들기
- 마지막으로 GROUPBY 함수와 슬라이서, 트리맵 차트를 활용한 대시보드를 만들어 보겠습니다. 예제파일에서 [오빠두 패션 데이터] 시트로 이동한 후, 아래쪽 표에 다음과 같이 조건열을 추가합니다.
'오빠두 패션 데이터' 시트로 이동한 후, 표에 조건 열을 추가합니다. - 표를 선택하고 [테이블 디자인] 탭 - [슬라이서 삽입] 에서 지점, 카테고리 슬라이서를 추가한 후 대시보드 영역의 적절한 곳으로 옮겨주세요.
- 이제 데이터를 집계하기 위한 GROUPBY 함수를 작성합니다. A2셀에 아래와 같이 GROUPBY 함수를 작성합니다.
=GROUPBY(매출[[카테고리]:[제품명]],매출[매출액],SUM,,0,,매출[조건]=1)
// 필터링 된 데이터에서 카테고리 제품명을 기준으로 매출액을 집계합니다. (합계 표시 안함)
선택한 데이터만 집계하도록 GROUPBY 함수를 작성합니다. - GROUPBY로 반환된 범위에서 임의 셀을 선택하고 [삽입]탭 - [추천차트] - [모든 차트] 에서 트리맵 차트를 선택해서 추가합니다. 기존 피벗테이블 보고서는 트리맵, 선버스트, 주식형과 같은 고급 차트를 만드는데 제약이 있는 반면 GROUPBY 함수는 다양한 고급 차트를 만들 수 있는 장점이 있습니다.
삽입 - 추천차트 - 모든차트에서 트리맵 차트를 추가합니다. - 차트의 제목을 지우고 적절한 위치와 크기로 옮기면 버튼을 클릭해서 필터링할 수 있는 실시간 대시보드가 완성됩니다.
실시간 트리맵 차트 대시보드가 완성됩니다. - 이전 강의에서 소개해드린 스타일시트를 활용하면 아래와 같이 멋진 시각화 보고서를 만들 수 있습니다. 엑셀 스타일시트는 아래 자료실을 확인하세요!👇
- 먼저 예제파일의 [기초] 시트에서 GROUPBY 함수 기초 사용법을 알아보겠습니다. [기초] 시트 상단의 [+] 버튼을 클릭해서 그룹을 펼치면 좌측 판매내역 데이터로 만든 피벗테이블이 있습니다.
