GROUPBY 함수 - 집계, 정렬, 필터링

엑셀에 집계함수 끝판왕이 될 GROUPBY(그룹바이) 함수라는 것이 생겼습니다. 함수 하나로 집계, 정렬, 필터링을 할 수 있는 대박 기능이라고 할 수 있습니다.

원래 Database를 다루는 언어 SQL에 GROUP BY 기능이 있었는데 이 기능이 엑셀로 들어온 것입니다.

GROUPBY 함수는 데이터를 그룹화해서 집계(합계, 평균 등)하고 정렬, 필터링까지 해 줍니다. 예를 들어 다음과 같이 판매일 별로 판매금액 합계를 내고 결과를 정렬, 필터링할 수 있습니다.

※ GROUPBY 함수는 2024년 11월 현재 Microsoft 365 '2409(빌드 18025.20096)' 버전 이상에서만 사용가능(버전 확인 및 업데이트 방법은 여기를 클릭!)

 

구문(Syntax)

 

사용 예

하나의 열로 그룹화하여 집계

판매일로 그룹화하여 합계 만들기

다음과 같은 판매일/거래처/상품별 판매실적 자료를 이용하여 '판매일'로 그룹화하여 합계를 만들어 보겠습니다.

다운로드한 실습 엑셀파일의 [판매일로 그룹화] 시트를 선택하고 [G5] 셀에 '=GROUPBY(' 까지 입력하고 첫 번째 인수(row_fields: 그룹화 단위가 되는 필드)가 입력되도록 ① '판매일' 범위 [B5:B21]를 선택합니다.

=GROUPBY(B5:B21

 

두 번째 인수(values: 집계할 값)에는 ② '판매금액' 범위 [E5:E21]를 선택합니다.

 

합계를 구하는 것이므로 세 번째 인수(function: 집계시 사용할 함수)에는 ③ 'SUM'을 선택합니다.

합계가 아니라 갯수를 구한다면 COUNT, 평균을 구한다면 AVERAGE를 선택하는 식으로 함수 목록에서 원하는 함수를 선택하면 됩니다.

 

결과에 헤더를 표시해야 한다면 네 번째 인수(field_headers: 헤더(제목) 표시 설정)에는 ④'3'을 선택합니다.

네 번째 인수는 다음 네 가지를 사용할 수 있습니다.

• 0 또는 생략: 표시하지 않음
• 1: 선택범위에 헤더가 있지만 표시하지 않음
• 2: 선택범위에 헤더가 없지만 표시('행 필드 1', '값 1' 과 같은 형태로 표시)
• 3: 선택범위에 헤더가 있으며 표시

 

마지막으로 괄호를 입력하고 Enter를 누르면 다음과 같이 수식이 완성되고

=GROUPBY(B5:B21,E5:E21,SUM,3)

⑤ 아래와 같이 결과가 하나의 셀이 아닌 범위에 표시됩니다.

▷ 동적 배열 수식을 사용하면 수식의 결과를 하나의 셀이 아닌 범위에 표시할 수 있습니다. '엑셀의 새로운 기능, 동적 배열 수식'을 참고하세요.

판매일 필드가 날짜가 아닌 숫자 44751의 형태로 표시되면 리본 메뉴 [홈] → [표시형식] → [간단한 날짜]로 바꿔주면 됩니다.

 

다음과 같이 판매 금액 필드등 숫자도 기본 형식이 1000단위 콤마가 붙어 있지 않습니다. 날짜와 마찬가지로 [표시형식]에서 원하는 형식으로 바꾸면 됩니다.

수식 풀이:

 

 

거래처로 그룹화하여 합계 만들기

이번에는 '거래처'로 그룹화하여 합계를 만들어 보겠습니다.

다운로드한 실습 엑셀파일의 [거래처로 그룹화] 시트를 선택하고 [G5] 셀에 다음 수식을 입력합니다.

=GROUPBY(C5:C21,E5:E21,SUM,3)

다음과 같이 결과가 [G5:H9] 범위에 표시됩니다.

수식 풀이:

 

 

여러 열로 그룹화하여 집계

거래처, 상품으로 그룹화

이번에는 두 개의 열 '거래처, 상품'으로 그룹화하여 합계를 만들어 보겠습니다.

다운로드한 실습 엑셀파일의 [여러열로 그룹화] 시트를 선택하고 [G5] 셀에 다음 수식을 입력합니다.

=GROUPBY(C5:D21,E5:E21,SUM,3)

그룹화열을 C5:D21로 지정했으므로 거래처 C열, 상품 D열로 그룹화되어 다음과 같이 거래처, 상품별로 합계가 표시됩니다.

수식 풀이:

 

 

여러 열로 그룹화 + 소계 포함

이번에는 여러 열로 그룹화하면서 집계 결과에 소계를 넣어 보겠습니다.

다운로드한 실습 엑셀파일의 [소계포함] 시트를 선택하고 [G5] 셀에 다음 수식을 입력합니다.

=GROUPBY(C5:D21,E5:E21,SUM,3,2)

다섯 번째 인수(total_depth: 합계와 소계 표시 설정)에 '2'를 설정했으므로 하단에 '총계, 소계'가 표시됩니다.

다섯 번째 인수는 다음 다섯 가지를 사용할 수 있습니다.

• 0: 합계 표시 안함
• 1: 하단에 총계 표시(기본값)
• 2: 하단에 총계 및 소계 표시
• -1: 상단에 총계 표시
• -2: 상단에 총계 및 소계 표시

수식 풀이:

 

 

집계 결과 정렬

GROUPBY 함수로 집계만 하는 것이 아니라 결과를 원하는 순서로 정렬할 수 있습니다.

다운로드한 실습 엑셀파일의 [정렬] 시트를 선택하고 [G5] 셀에 다음 수식을 입력합니다.

=GROUPBY(C5:D21,E5:E21,SUM,3,1,3)

여섯 번째 인수(sort_order: 인덱스 번호)에 '3'을 설정했으므로 결과의 세번째 열이 오름차순(작은 수가 먼저 옴)으로 정렬되어 표시됩니다.

여섯 번째 인수는 다음 두 가지를 사용할 수 있습니다.

• 양수: 오름차순 정렬
• 음수: 내림차순 정렬

수식 풀이:

 

※ 만약 내림차순으로 정렬하려면 다음과 같이 여섯 번째 인수(sort_order: 인덱스 번호)를 음수로 입력합니다.

집계결과 거래처, 상품, 판매금액 중에서 3번째인 판매금액을 내림차순(큰 수가 먼저옴)으로 정렬해 줍니다.

 

 

집계 결과 필터링

이번에는 GROUPBY 함수로 원하는 데이터만 걸러내는 필터링 기능을 사용해서 집계해 보겠습니다.

다운로드한 실습 엑셀파일의 [필터링] 시트를 선택하고 [G5] 셀에 다음 수식을 입력합니다.

=GROUPBY(C5:C21,E5:E21,SUM,3,,,D5:D21="노트")

일곱 번째 인수(filter_array: 필터링 조건 설정)에 D5:D21="노트"을 설정했으므로 상품이 '노트'인 것만 가져와서 집계를 표시해 줍니다.

수식 풀이:

 

※ 만약 상품 중에서 '노트'가 아닌 것만(노트 제외) 집계하려면 다음과 같이 수식을 입력합니다.

=GROUPBY(C5:C21,E5:E21,SUM,3,,,D5:D21<>"노트")

 

 

---

관련 글