엑셀 GROUPBY 함수 5단계 사용법과 오류 해결 순서

Table of Contents

바쁜 현대인을 위한 3줄 요약

1단계로 필수 인수 3개인 행 기준 범위, 집계 값 범위, 계산 함수(SUM 등)를 연결해 기본 요약표를 만듭니다.
2~4단계에서 머리글 번호(3), 총합계(1), 내림차순 정렬(-2), HSTACK·PERCENTOF 다중 계산식을 추가합니다.
5단계로 범위 콜론 뒤에 점을 찍는 TRIMRANGE 연산자(:.)를 결합해 빈 행의 0 출력을 없앱니다.

원본 표의 숫자가 바뀔 때마다 피벗 테이블에서 마우스 오른쪽 버튼을 눌러 새로고침을 반복하는 건 실무자들이 가장 자주 겪는 번거로움입니다.

엑셀 GROUPBY 함수 사용법을 적용하면 수식 한 줄로 데이터를 그룹화하고 원본 수정 즉시 결과를 자동으로 갱신할 수 있어요.

복잡해 보이는 8개 인수 구조를 5단계 설정 순서로 나누어 기본 합계부터 매출 비중 계산과 오류 차단까지 차례대로 정리했습니다.

엑셀 GROUPBY 함수 지원 버전과 사전 점검 체크리스트

본격적인 수식 작성에 앞서 현재 사용 중인 오피스 환경이 동적 배열 집계 함수를 지원하는지부터 확인해야 합니다.

엑셀 GROUPBY 함수 지원 대상은 Microsoft 365 구독 버전이며, 2023년 12월 4일 인사이더 채널 공개 후 2024년 9월 25일 현재 채널에 정식 배포되었습니다.

만약 수식 입력줄에 함수 이름을 쳤을 때 자동 완성 목록이 뜨지 않거나 엔터를 누른 직후 #NAME? 오류가 나타난다면 구버전 오피스이거나 업데이트가 지연된 상태입니다.

Microsoft 365 배포 기준과 #NAME? 오류 사전 확인

Microsoft Learn Q&A 공식 포럼에서도 동일한 문서를 다른 PC에서 열거나 업데이트 직후에 #NAME? 오류가 발생한다는 질문이 꾸준히 올라옵니다.

이처럼 엑셀 GROUPBY 함수 오류 현상은 구버전 오피스를 쓰거나 사내 업데이트 채널 배포가 지연되었을 때 주로 나타납니다.

따라서 수식을 입력하기 전에 아래 3가지 사전 조건을 먼저 점검하는 것이 안전합니다.

사용 중인 오피스가 Microsoft 365 구독 제품인지, 최신 업데이트 채널이 적용되었는지 먼저 확인합니다.
그룹화할 행 기준 범위(row_fields)와 계산할 값 범위(values), 조건 범위(filter_array)의 시작 행과 끝 행 길이가 정확히 일치하는지 대조합니다.
수식을 입력할 셀 아래쪽과 오른쪽에 다른 텍스트나 병합된 셀이 없는지 미리 비워두어야 동적 배열이 정상적으로 펼쳐집니다.

1~2단계: 필수 인수 3개 입력과 머리글·합계 번호 설정 순서

사전 점검을 마쳤다면 엑셀 GROUPBY 함수 인수를 이루는 전체 8개 항목 중 앞쪽 5개부터 차례대로 완성할 차례입니다.

Microsoft 공식 GROUPBY 함수 지원 문서에 명시된 전체 구문은 GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array],[field_relationship])의 8개 인수로 이루어져 있습니다.

앞의 3개는 반드시 입력해야 하는 필수 인수이고, 대괄호로 묶인 뒤쪽 5개는 보고서 형태에 맞춰 숫자와 조건을 넣는 선택 인수입니다.

아래 표는 8개 인수의 역할과 입력할 수 있는 숫자 옵션 코드를 정리한 매핑표입니다.

인수 순서 및 이름	필수 여부	핵심 역할 및 입력 가능한 번호·옵션 값
1. row_fields	필수	그룹화할 기준 열 범위 지정 (여러 열 선택 시 다단계 행 그룹 생성)
2. values	필수	합계·평균 등을 계산할 데이터 열 범위 지정 (여러 열 선택 가능)
3. function	필수	SUM, AVERAGE, COUNT, PERCENTOF 등 축약형 람다 집계 함수 입력
4. field_headers	선택	0(머리글 없음), 1(원본에 있으나 숨김), 2(자동 생성), 3(원본 머리글 표시)
5. total_depth	선택	0(없음), 1(하단 총합계), 2(하단 총합+부분합), -1(상단 총합계), -2(상단 총합+부분합)
6. sort_order	선택	정렬할 열 순번 숫자 입력 (양수는 오름차순, 음수는 내림차순 정렬)
7. filter_array	선택	포함할 행을 거르는 TRUE/FALSE 논리식 배열 (행 길이 일치 필수)
8. field_relationship	선택	다중 열 그룹 관계 설정: 0(계층 구조 및 부분합 지원), 1(독립 테이블 구조)

위 표에서 보듯 필수 인수 3개만 넣으면 즉시 계산이 돌아가고, 4번째와 5번째 인수에 한 자리 숫자만 추가하면 표의 머리글과 합계 줄 위치까지 자유롭게 제어할 수 있습니다.

1단계: 행 기준(row_fields)·집계 값(values)·함수(function) 3개 연결

첫 번째 단계는 무엇을 기준으로 묶을지, 어떤 숫자를 계산할지, 어떤 방식으로 계산할지 3가지를 지정하는 과정입니다.

예를 들어 A1:D100 범위에 A열(부서명), B열(담당자), C열(지역), D열(매출액)이 적힌 실적표가 있고 1행은 제목이라고 가정해 보겠습니다.

이때 부서별 매출 합계를 구하는 기본 수식은 =GROUPBY(A2:A100, D2:D100, SUM)입니다.

이처럼 엑셀 GROUPBY 함수 구문은 =GROUPBY(행기준범위, 집계값범위, 집계함수)라는 필수 인수 3개만 넣어도 즉시 작동합니다.

여기서 3번째 인수에 들어간 SUM은 복잡한 LAMBDA 수식을 길게 쓰지 않고 함수 이름만 바로 적어 넘기는 축약형 람다(eta reduced lambda) 방식입니다.

수식 입력 중 세 번째 콤마를 찍으면 화면에 SUM, AVERAGE, COUNT, MAX, MIN, PERCENTOF 등 선택할 수 있는 함수 목록이 자동으로 나타나므로 목록에서 골라 넣으면 됩니다.

만약 부서와 담당자를 함께 묶는 엑셀 GROUPBY 함수 다중 그룹이 필요하다면 첫 번째 인수를 A2:B100처럼 여러 열로 넓혀 주면 됩니다.

2단계: field_headers(0~3)와 total_depth(-2~2) 숫자 코드 지정

1단계 수식에서 제목 행을 뺀 A2:A100을 넣으면 결과 표 맨 위에 열 머리글이 나타나지 않아 어떤 숫자인지 한눈에 알아보기 어렵습니다.

이때 범위를 제목 행이 포함된 A1:A100과 D1:D100으로 잡은 뒤, 4번째 인수인 field_headers와 5번째 인수인 total_depth를 숫자로 지정해 표의 완성도를 높입니다.

4번째 인수 field_headers에는 0부터 3까지의 숫자 중 하나를 입력하며 각 숫자의 작동 방식은 다음과 같습니다.

숫자 0: 원본 범위에 머리글이 없다고 간주하며 결과 표에도 머리글을 표시하지 않습니다.
숫자 1: 원본 범위 첫 줄에 머리글이 포함되어 있으나 결과 표에서는 해당 머리글을 숨깁니다.
숫자 2: 원본 범위에 머리글이 없지만 결과 표 상단에 기본 열 제목을 자동으로 생성해 표시합니다.
숫자 3: 원본 범위 첫 줄에 머리글이 포함되어 있으며 그 제목을 결과 표 맨 위에 그대로 출력합니다.

이어서 5번째 인수 total_depth는 총합계(Grand Total)와 부분합(Subtotal)을 어디에 표시할지 결정합니다.

하단에 총합계만 넣으려면 1, 총합계와 부분합을 함께 넣으려면 2를 적고, 합계 줄을 아예 빼려면 0을 입력합니다.

반대로 표 맨 위(상단)에 총합계를 띄우려면 -1, 상단에 총합계와 부분합을 모두 띄우려면 -2를 지정합니다.

따라서 엑셀 GROUPBY 함수 옵션 중 실무에서 가장 많이 쓰는 조합은 field_headers는 3(머리글 표시), total_depth는 1(하단 총합계)을 넣은 =GROUPBY(A1:A100, D1:D100, SUM, 3, 1)입니다.

단, 부분합을 출력하는 2 또는 -2 옵션은 첫 번째 인수(row_fields)에 최소 2개 이상의 열(예: A1:B100)이 들어갔을 때만 작동한다는 점을 기억해야 합니다.

3~4단계: 내림차순 정렬·조건 필터링과 다중 집계 확장 순서

기본 표와 합계 줄까지 만들었다면 이제 실적이 높은 순서대로 줄을 세우거나 특정 조건만 발라내고, 합계 옆에 비중(%)을 나란히 붙이는 3~4단계로 넘어갑니다.

피벗 테이블에서는 마우스로 필터 창을 열고 값 필드 설정을 여러 번 클릭해야 하지만, 여기서는 6·7번째 인수와 배열 결합 함수로 한 번에 처리합니다.

3단계: sort_order 음수 정렬과 filter_array 조건식 결합

6번째 인수인 sort_order는 결과 표의 몇 번째 열을 기준으로 정렬할지 숫자로 지정하는 자리입니다.

열 번호는 결과 표의 왼쪽 첫 번째 열(row_fields 첫 열)이 1번이 되고 오른쪽으로 갈수록 2번, 3번 순서로 번호가 매겨집니다.

양수(예: 1 또는 2)를 넣으면 가나다순이나 작은 숫자부터 커지는 오름차순이 되고, 숫자 앞에 마이너스 기호를 붙인 음수를 넣으면 큰 숫자부터 작아지는 내림차순이 됩니다.

예를 들어 부서별(1열) 매출액(2열) 요약표에서 실적이 큰 순서로 줄을 세우려면 집계 값 기준 내림차순은 열 순번 앞에 마이너스(-2)를 입력하여 =GROUPBY(A1:A100, D1:D100, SUM, 3, 1, -2)라고 적습니다.

이처럼 엑셀 GROUPBY 함수 정렬 방향은 부호 하나로 결정되며, 특정 조건만 걸러낼 때는 7번째 인수 filter_array를 덧붙입니다.

filter_array는 각 행의 포함 여부를 판별하는 TRUE·FALSE 논리 배열을 받으며 길이는 첫 번째 인수와 같아야 합니다.

가령 C열이 “서울”인 행만 추리려면 엑셀 GROUPBY 함수 필터 조건으로 =GROUPBY(A1:A100, D1:D100, SUM, 3, 1, -2, C1:C100=”서울”)을 입력합니다.

만약 첫 번째 인수에 여러 열(A1:B100)을 넣었고 두 열을 계층 구조가 아닌 대등한 독립 열로 정렬하고 싶다면 마지막 8번째 인수 field_relationship에 기본값 0 대신 1을 넣으면 됩니다.

다만 8번째 인수를 1(테이블 관계)로 설정하면 각 열을 개별적으로 정렬할 수 있는 대신 5번째 인수의 부분합(2 또는 -2) 기능은 지원되지 않습니다.

4단계: HSTACK과 PERCENTOF로 합계·비율 동시 계산

실무 보고서에서는 부서별 매출 합계 옆에 전체 매출 대비 몇 퍼센트를 차지하는지 점유율(%)이나 평균을 함께 적어달라는 요청이 많습니다.

이때는 마이크로소프트가 동적 집계 함수와 함께 도입한 PERCENTOF 함수와 가로 배열 결합 함수인 HSTACK을 3번째 인수(function) 자리에 묶어 넣습니다.

Microsoft 공식 PERCENTOF 함수 문서에 따르면 PERCENTOF(data_subset, data_all) 함수는 하위 그룹의 합계를 전체 데이터의 합계로 나누는 =SUM(data_subset)/SUM(data_all) 계산식과 논리적으로 동일합니다.

예를 들어 전체 매출이 1,000만 원이고 영업1팀 매출이 250만 원이라고 가정하면, PERCENTOF는 250만 원 ÷ 1,000만 원 = 0.25(25%)를 반환합니다.

합계와 비율을 나란히 출력하려면 배열을 가로로 묶는 HSTACK을 써서 =GROUPBY(A1:A100, D1:D100, HSTACK(SUM, PERCENTOF), 3, 1)을 입력합니다.

이렇게 엑셀 GROUPBY 함수 비율 계산식을 구성하면 부서명 옆에 매출 합계 열과 전체 대비 점유율 열이 동시에 만들어집니다.

PERCENTOF는 하위 그룹 합계를 전체 데이터 합계로 나눈 값을 바로 돌려주므로 별도의 나눗셈 수식을 따로 적을 필요가 없습니다.

5단계: TRIMRANGE 점(.) 연산자로 빈 행 0 표시 없애는 동적 범위 설정

데이터가 추가될 것을 대비해 엑셀 GROUPBY 함수 범위를 A1:D500처럼 넉넉히 잡으면 빈 행 때문에 결과 표에 0이 나타납니다.

이때는 범위 가장자리의 빈 행과 빈 열을 자동으로 잘라내는 TRIMRANGE 함수나 점(.) 축약 연산자를 결합해 해결합니다.

Microsoft 공식 TRIMRANGE 함수 문서를 보면 TRIMRANGE(range, [trim_rows], [trim_cols]) 함수는 지정한 범위의 바깥쪽 가장자리부터 안쪽으로 스캔하여 값이 있는 셀이 나올 때까지 빈 행과 빈 열을 자동으로 잘라냅니다.

더 편리한 방법은 함수 이름을 길게 쓰는 대신 범위 참조 콜론(:) 앞이나 뒤에 마침표(.)를 찍는 축약 참조(Trim References) 문법을 쓰는 것입니다.

앞뒤 공백 모두 제거(.:.): A1.:.D500처럼 콜론 양쪽에 점을 찍으면 TRIMRANGE(A1:D500, 3, 3)과 같아져 위아래 빈 행을 모두 제외합니다.
끝부분 공백만 제거(:.): A1:.D500처럼 콜론 오른쪽에만 점을 찍으면 TRIMRANGE(A1:D500, 2, 2)와 같아져 아래쪽 빈 행만 잘라냅니다.
시작 부분 공백만 제거(.:): A1.:D500처럼 콜론 왼쪽에만 점을 찍으면 TRIMRANGE(A1:D500, 1, 1)과 같아져 위쪽 빈 행만 제외합니다.

실무 표는 보통 1행부터 제목이 시작되고 아래쪽으로만 새 데이터가 쌓이므로, 콜론 뒤에 점을 찍는 A1:.D500(Trim Ref) 참조 방식을 쓰면 가장 효율적입니다.

앞선 수식에 이 문법을 적용해 =GROUPBY(A1:.A500, D1:.D500, HSTACK(SUM, PERCENTOF), 3, 1)이라고 적으면 101행 이하 공백이 자동으로 제외됩니다.

이후 101행에 새로운 매출 데이터를 입력하면 엑셀 GROUPBY 함수 결과 표가 수식 수정 없이 즉시 늘어나며 반영됩니다.

심지어 A:.A나 D:.D처럼 열 전체 참조에도 마침표 연산자를 그대로 적용할 수 있어 대용량 시트 관리 시간을 크게 줄여줍니다.

피벗 테이블·PIVOTBY 비교와 작동 불가 시 중단 기준

5단계 설정을 모두 익혔더라도 모든 보고서를 단일 축 함수 하나로만 처리할 수 있는 것은 아닙니다.

행과 열 두 방향으로 교차 표를 만들어야 하거나 공유 대상자의 오피스 버전이 낮을 때는 PIVOTBY 함수나 기존 피벗 테이블을 선택하는 것이 맞습니다.

단일 축 요약과 2차원 교차 요약(PIVOTBY) 선택 기준

엑셀 GROUPBY 함수와 쌍둥이처럼 함께 출시된 PIVOTBY 함수는 행(row_fields)뿐만 아니라 열(col_fields) 기준까지 총 2개의 축으로 데이터를 그룹화합니다.

Microsoft 공식 PIVOTBY 함수 지원 문서에 따르면 PIVOTBY는 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])의 총 11개 인수를 사용합니다.

아래 표는 실무 상황에 맞춰 세 가지 도구 중 무엇을 골라야 하는지 정리한 핵심 사양 비교표입니다.

비교 항목	GROUPBY 함수	PIVOTBY 함수	기존 피벗 테이블 (PivotTable)
그룹화 축 방향	단일 축 (행 방향만 그룹화)	2개 축 (행·열 방향 교차 그룹화)	다중 축 (행·열·필터·슬라이서)
전체 인수 개수	총 8개 (필수 3개, 선택 5개)	총 11개 (필수 4개, 선택 7개)	마우스 드래그 앤 드롭 설정
원본 변경 시 갱신	수식 기반 즉시 자동 갱신	수식 기반 즉시 자동 갱신	수동 새로고침 실행 필요
최소 지원 환경	Microsoft 365 (2024년 9월 GA)	Microsoft 365 (2024년 9월 GA)	모든 엑셀 버전 호환 가능

표에서 확인되듯 부서별·담당자별처럼 세로 방향으로만 목록을 요약할 때는 인수가 8개로 간결한 GROUPBY를 쓰고, 좌측에는 부서명·상단에는 연도나 분기를 가로로 펼쳐야 할 때는 2번째 인수에 열 범위를 넣는 PIVOTBY를 선택하면 됩니다.

수식 수정을 멈추고 원본 표나 피벗 테이블로 전환할 중단 기준

엑셀 GROUPBY 함수 작성 중 아래 4가지 상황에 해당한다면 수식 수정을 멈추고 원본 표나 피벗 테이블로 전환해야 합니다.

첫째, 거래처나 동료 PC가 Microsoft 365가 아니라서 #NAME? 오류가 계속 뜬다면 일반 피벗 테이블로 전환합니다.
둘째, row_fields와 values·filter_array의 시작 행·끝 행 길이가 다르면 수식 옵션 대신 범위 숫자부터 똑같이 맞춥니다.
셋째, 8번째 인수에 1(독립 테이블)을 넣고 5번째 인수에 2(부분합)를 함께 쓰면 충돌하므로 둘 중 하나만 선택합니다.
넷째, 결과가 펼쳐질 아래·오른쪽 셀에 고정 양식이 막고 있다면 빈 시트에 작성하거나 기존 계산 함수를 유지합니다.

한 문장 결론 및 실무 함수 확장 가이드

엑셀 GROUPBY 함수 설정은 필수 인수 3개로 기본 골격을 세운 뒤 머리글·합계 번호(3, 1)와 음수 정렬(-2)을 차례로 덧붙이는 순서로 진행합니다.

여기에 HSTACK·PERCENTOF 다중 계산과 마침표(:.) 공백 제거 연산자까지 결합하면 새로고침 없는 자동화 요약표가 완성됩니다.

오늘 다룬 동적 집계 함수 외에 실무에서 자주 막히는 값 찾기(XLOOKUP)와 조건별 합계·오류 대응 기준이 궁금하다면 엑셀 꿀팁 5가지, 업무 상황별로 바로 쓰는 함수 정리 글을 함께 확인해 보세요.

이 글의 모든 함수 구문과 배포 채널 규격은 마이크로소프트 공식 지원 문서를 바탕으로 작성되었습니다.

엑셀 GROUPBY 함수 5단계 사용법과 오류 해결 순서

바쁜 현대인을 위한 3줄 요약