엑셀 GROUPBY, PIVOTBY 함수 사용법 – 피벗테이블 없이 요약표 만들기

엑셀에서 “상품별 매출 합계”, “상품×지역 교차표” 같은 요약표를 만들 때 보통 피벗테이블을 사용합니다. 그런데 Microsoft 365에서는 GROUPBY, PIVOTBY 함수를 이용해 수식 1개로 요약표를 자동 생성할 수 있습니다.

이 글에서는 GROUPBY/PIVOTBY의 차이를 정리하고, 직접 따라 할 수 있는 예제 데이터로 요약표를 만든 뒤, 마지막에 매개변수(인수) 설명까지 한 번에 정리합니다.

1. GROUPBY / PIVOTBY 차이

GROUPBY: 한 축(행 기준)으로 그룹화해서 합계/개수/평균 등을 구하는 요약표
PIVOTBY: 행 기준 + 열 기준(2축)으로 그룹화해서 교차표(피벗 형태) 만들기

2. 예제 데이터 준비(그대로 붙여넣기)

아래 데이터를 A1:D9에 붙여넣어 주세요.

날짜	상품	지역	매출
2026-01-01	키보드	서울	120000
2026-01-01	마우스	서울	30000
2026-01-02	키보드	부산	90000
2026-01-02	모니터	서울	250000
2026-01-03	마우스	부산	28000
2026-01-03	키보드	서울	110000
2026-01-04	모니터	부산	230000
2026-01-04	마우스	서울	32000

팁: 데이터 범위를 선택하고 Ctrl + T로 표(Table)로 만들면 데이터가 늘어날 때 범위 관리가 훨씬 편합니다.

3. GROUPBY 예제: 상품별 매출 합계 만들기

원하는 셀(예: F2)에 아래 수식을 입력합니다.

=GROUPBY(B2:B9, D2:D9, SUM)

이 수식은 “상품(B열)”을 기준으로 그룹화한 다음 “매출(D열)”을 SUM으로 집계하여 요약표를 자동으로 펼쳐줍니다.

3-1) 결과 예시(위 데이터 기준)

마우스: 90,000
모니터: 480,000
키보드: 320,000
합계: 890,000

4. PIVOTBY 예제: 상품 × 지역 교차표 만들기

원하는 셀(예: I2)에 아래 수식을 입력합니다.

=PIVOTBY(B2:B9, C2:C9, D2:D9, SUM)

이 수식은 “상품”을 행 머리글, “지역”을 열 머리글로 만들고, 각 교차 지점의 매출을 합계로 집계합니다. 즉, 피벗테이블처럼 보이는 교차표를 수식으로 한 번에 생성합니다.

4-1) 결과 예시(위 데이터 기준)

부산 합계: 348,000 (마우스 28,000 / 모니터 230,000 / 키보드 90,000)
서울 합계: 542,000 (마우스 62,000 / 모니터 250,000 / 키보드 230,000)
전체 합계: 890,000

▼ 엑셀 GROUPBY, PIVOTBY 함수 사용 예시

5. GROUPBY 함수 매개변수(인수) 설명

GROUPBY는 “그룹화 + 집계 + (선택)정렬/필터/합계 표시”까지 한 번에 처리할 수 있는 함수입니다.

5-1) 구문

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

5-2) 필수 인수

row_fields (필수): 그룹화 기준이 되는 열(또는 범위). 여러 열을 넣으면 다단계 그룹(예: 부서→팀)도 가능
values (필수): 집계할 값 범위(예: 매출). 여러 열을 넣으면 집계 결과도 여러 개가 나옴
function (필수): 집계 방식. SUM, AVERAGE, COUNT 등(또는 람다) 사용

5-3) 선택 인수

field_headers: 원본에 헤더가 있는지, 결과에 헤더를 표시할지 결정하는 숫자
- 생략: 자동(기본)
- 0: 헤더 없음
- 1: 헤더 있음(표시 안 함)
- 2: 헤더 없지만 생성
- 3: 헤더 있음(표시)
total_depth: 합계/부분합을 어디까지 표시할지 결정
- 생략: 자동(기본) – 총합계 및 가능한 경우 부분합
- 0: 합계 없음
- 1: 총합계만
- 2: 총합계 + 부분합
- 음수 값: 합계를 위쪽에 두는 형태 등(환경에 따라 옵션 제공)
sort_order: 정렬 기준(숫자) 지정. 양수는 오름차순, 음수는 내림차순
- 정렬 기준이 row_fields 또는 values의 몇 번째 열인지 숫자로 지정
- 여러 기준을 쓰려면 벡터(여러 숫자) 형태로 지정 가능
filter_array: 포함할 행(TRUE)/제외할 행(FALSE)을 지정하는 1차원 배열
- 길이는 row_fields의 행 수와 동일해야 함
- 예: “서울만 집계” 같은 조건을 먼저 배열로 만든 뒤 적용 가능
field_relationship: row_fields에 여러 열이 있을 때 관계를 결정
- 0: 계층(기본) – 다음 필드 정렬이 이전 필드 계층을 고려
- 1: 테이블 – 각 필드 정렬이 독립적으로 수행(부분합은 지원되지 않을 수 있음)

6. PIVOTBY 함수 매개변수(인수) 설명

PIVOTBY는 GROUPBY를 “행+열 2축”으로 확장한 교차표 함수라고 이해하면 쉽습니다.

6-1) 구문

PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array], [relative_to])

6-2) 필수 인수

row_fields (필수): 행 방향 그룹 기준(예: 상품)
col_fields (필수): 열 방향 그룹 기준(예: 지역)
values (필수): 집계할 값(예: 매출)
function (필수): SUM, AVERAGE, COUNT 등 집계 함수(또는 람다)

6-3) 선택 인수

field_headers: row_fields/col_fields/values 헤더 처리 방식(자동/0/1/2/3) – GROUPBY와 동일한 개념
row_total_depth: 행 머리글의 합계/부분합 표시 깊이
- 생략: 자동(기본)
- 0: 합계 없음
- 1: 총합계만
- 2: 총합계 + 부분합(행 필드가 2개 이상일 때 의미가 커짐)
row_sort_order: 행 정렬 기준(숫자/벡터). 음수면 내림차순
col_total_depth: 열 머리글의 합계/부분합 표시 깊이(개념은 row_total_depth와 동일)
col_sort_order: 열 정렬 기준(숫자/벡터). 음수면 내림차순
filter_array: 포함/제외 행을 TRUE/FALSE 배열로 지정(길이는 원본 데이터 행 수와 일치해야 함)
relative_to: PERCENTOF처럼 “두 번째 인수(totalset)”가 필요한 집계 함수를 쓸 때 기준을 결정
- 0: 열 합계(기본)
- 1: 행 합계
- 2: 총합계
- 3: 상위(부모) 열 합계
- 4: 상위(부모) 행 합계

7. 자주 발생하는 오류/문제

#NAME?: 현재 버전/채널에서 함수가 아직 제공되지 않거나 업데이트가 덜 된 경우
범위 불일치: row_fields/col_fields/values의 행 수가 다르면 결과가 정상적이지 않을 수 있음
값이 텍스트: 매출 같은 숫자가 텍스트로 들어가 있으면 합계가 기대와 다르게 계산될 수 있음

8. 정리

피벗테이블 없이도 GROUPBY로 “한 기준 요약표”, PIVOTBY로 “교차표”를 수식으로 만들 수 있습니다.
데이터가 늘어나면 요약표도 자동 갱신되는 “수식 기반 요약”이 필요할 때 특히 유용합니다.
처음에는 필수 인수(기준/값/집계)만 써도 충분하고, 필요하면 정렬/합계/필터 옵션을 추가하면 됩니다.

함께 보면 좋은 게시글

이 글이 도움이 되셨다면 공유 부탁 드립니다.