엑셀 함수 > 찾기 및 참조영역 함수 > GROUPBY 함수 - 집계, 정렬, 필터링

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

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

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

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

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

 

구문(Syntax)

구문(Syntax)

GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array],[field_relationship])

GROUPBY(행필드,값,함수,[필드헤더],[합계깊이],[정렬순서],[필터배열],[필드관계])

필드를 기준으로 데이터를 그룹화하여 집계, 정렬, 필터링한다.

인수

  • row_fields: 그룹화 단위가 되는 필드
  • values: 집계할 값
  • function: 집계시 사용할 함수(SUM, …)
  • field_headers: 헤더(제목) 표시 설정
    0 또는 생략: 표시하지 않음
    1: 선택범위에 헤더가 있지만 표시하지 않음
    2: 선택범위에 헤더가 없지만 표시(‘행 필드 1’, ‘값 1’ 과 같은 형태로 표시)
    3: 선택범위에 헤더가 있으며 표시
  • total_depth: 합계와 소계 표시 설정
    0: 합계 표시 안함
    1: 하단에 총계 표시(기본값)
    2: 하단에 총계 및 소계 표시
    -1: 상단에 총계 표시
    -2: 상단에 총계 및 소계 표시
  • sort_order: 인덱스 번호(row_fields, values 순으로 1, 2, 3으로 지정됨)로 정렬 순서 설정
    양수: 오름차순 정렬
    음수: 내림차순 정렬
    * 여러 row_fields를 기준으로 정렬하려면 1차원 숫자 배열 지정 (예: {1,-2})
  • filter_array: 필터링 조건 설정
  • field_relationship: 필드간의 관계 지정
    0: 계층 구조(기본값)
    1: 테이블
실습용 엑셀파일 다운로드 : GROUPBY-함수사용법.xlsx

 

사용 예

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

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

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

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

=GROUPBY(B5:B21
GROUPBY 함수 - 판매일로 그룹화하여 합계 만들기

 

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

GROUPBY 함수 - 판매일로 그룹화하여 합계 만들기

 

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

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

GROUPBY 함수 - 판매일로 그룹화하여 합계 만들기(함수 선택)

 

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

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

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

GROUPBY 함수 - 판매일로 그룹화하여 합계 만들기(헤더 설정)

 

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

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

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

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

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

GROUPBY 함수 - 판매일로 그룹화하여 합계 만들기(최종 결과)

 

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

GROUPBY 함수 - 판매일로 그룹화하여 합계 만들기(서식 설정)

수식 풀이:

GROUPBY 함수 - 판매일로 그룹화하여 합계 만들기 수식 풀이

 

 

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

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

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

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

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

GROUPBY 함수 - 거래처로 그룹화하여 합계 만들기

수식 풀이:

GROUPBY 함수 - 거래처로 그룹화하여 합계 만들기 수식 풀이

 

 

여러 열로 그룹화하여 집계

거래처, 상품으로 그룹화

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

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

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

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

GROUPBY 함수 - 여러 열로 그룹화하여 집계

수식 풀이:

GROUPBY 함수 - 여러 열로 그룹화하여 집계 수식 풀이

 

 

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

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

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

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

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

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

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

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

수식 풀이:

GROUPBY 함수 - 여러 열로 그룹화 + 소계 포함 수식 풀이

 

 

집계 결과 정렬

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

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

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

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

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

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

GROUPBY 함수 - 집계 결과 정렬

수식 풀이:

GROUPBY 함수 - 집계 결과 정렬 수식 풀이

 

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

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

GROUPBY 함수 - 집계 결과 정렬(내림 차순)

 

 

집계 결과 필터링

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

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

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

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

GROUPBY 함수 - 집계 결과 필터링

수식 풀이:

GROUPBY 함수 - 집계 결과 필터링 수식 풀이

 

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

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

GROUPBY 함수 - 집계 결과 필터링(노트인 것은 제외)

 

 


관련 글

GROUPBY 함수로 한 번에 월별 합계 구하기 - 다음과 같은 판매실적 자료에서 월별 판매실적을 구하려면 어떻게 해야 할까요? 일반적으로는 SUMIFS 함수를 사용해 아주 복잡한 수식을 작성한 뒤, 행별로…

GROUPBY 함수로 한 번에 월별 합계 구하기 더 보기 »

Microsoft 365 버전확인 및 업데이트 Microsoft 365 버전 확인 및 최신 업데이트 방법 - Microsoft 365(Office 365)를 사용하게 되면 최신 버전이 자동으로 업데이트되지만 즉시 반영되지는 않고 업데이트 스케줄에 따라 진행됩니다. 따라서 최신버전이 나오긴 했지만…

Microsoft 365 버전 확인 및 최신 업데이트 방법 더 보기 »

 

엑셀웍스 책 출간 안내

그동안 엑셀웍스에 올린 '엑셀 강좌', '함수 사용법', 여러 '실무 예제'에서 핵심만 뽑아 '된다! 엑셀 수식 & 함수' 책으로 출간하였습니다.

 자세히 보기

온라인 구매처: 예스24   교보문고   알라딘 

댓글 남기기

Scroll to Top