엑셀 매출 정리 피벗 없이 요약 그룹별 합계 조건별 데이터 요약 빨리 끝내는 방법

회사에서 엑셀을 사용하다 보면 지점별 혹은 제품별,담당자 별로 매출 정리를 하는 경우가 있습니다. 그런데 혹시 아직도 일일이 필터를 걸거나 피벗을 걸어 정리하고 계셧나요?

그런데, 이렇게 복잡하게 하다보면 데이터를 빼먹거나 데이터 합이 이상하게 나올 수 있습니다.
그래서 이럴 때 유용하게 쓸 수 있는 함수  GROUPBY 함수를 배워 보도록 하겠습니다.

 

[목차]

---

GROUPBY 함수란?

GROUPBY 함수는 Excel에서 데이터를 그룹화하고 요약할 수 있는 강력한 배열 함수입니다. Microsoft 365 Excel에 추가된 함수로, 데이터를 체계적으로 분류하고 각 그룹에 대해 요약 결과를 계산하는 데 사용됩니다. 특히 피벗 테이블처럼 복잡한 도구를 사용하지 않고도 수식으로 직접 데이터를 처리할 수 있어서 간편합니다.

숨겨진 보석 같은 함수, GROUPBY

많은 사람들이 잘 모르는 함수지만, 알고 나면 정말 유용해요.
GROUPBY함수는 배열 공식으로, 데이터를 그룹화하고 요약 정보를 생성하는 데 매우 유용합니다. 특히, Power Query나 피벗 테이블을 사용할 필요 없이 수식만으로도 데이터를 간단히 처리할 수 있다는 점에서 편리합니다.

함수 구문

=GROUPBY(데이터_범위, 그룹화할_열, "요약_컬럼", 요약_함수)

• 데이터범위(array): 데이터를 그룹화할 전체 범위(테이블 형태일 수 있음).
• 그룹화할 열(group_by_column): 데이터를 그룹화할 기준이 되는 열.
• 요약컬럼,요약함수(column_name과 aggregation_function): 선택 사항으로, 요약 정보가 담길 열과 그 열에 적용할 함수(예: SUM, AVERAGE 등)를 지정합니다.

---

.

---

예제로 이해하는 GROUPBY 함수

1. 매출액계산하기

우리는 각 지점별 과일 매출액을 계산할 예정입니다. 그래서 아래와 같은 데이터가 있다고 가정해 봅시다.

지점 별로 매출액을 확인할 예정이므로 E1셀에 함수를 입력합니다.

=GROUPBY(A2:A19,C2:C19,SUM)

 

👉 결과: 

자동으로 지점 매출액이 확인 되었습니다.

2. 각 과일별 전체 평균 매출

이번엔 과일별 평균 매출을 구해보도록 하겠습니다.

방법은 합계와 동일 하지만 이번엔 평균 함수인 AVERAGE함수를 씁니다.

=GROUPBY(A2:A19,C2:C19,AVERAGE)

👉 결과:

각 과일별 매출액 평균을 알 수 있어요.

 

---

언제 활용하면 좋을까?

GROUPBY 함수는 데이터 그룹화와 요약 작업을 간단히 처리하고 싶을 때 매우 유용합니다. 특히 다음과 같은 상황에서 활용하면 좋습니다:

1. 데이터 분석 간소화: 다양한 그룹별로 데이터를 요약하거나 집계해야 할 때, GROUPBY를 사용해 빠르게 결과를 얻을 수 있습니다. 예를 들어, 판매 데이터에서 지역별 매출 합계를 계산할 때 적합합니다.
2. 복잡한 데이터 처리 없이 결과 얻기: 피벗 테이블이나 Power Query를 설정하는 번거로움 없이 수식만으로 데이터를 처리하고 싶을 때 사용하면 편리합니다.
3. 실시간 데이터 변화 반영: 배열 함수이기 때문에 데이터가 변경될 때 결과도 실시간으로 자동 업데이트됩니다. 따라서 동적으로 그룹화 및 요약 작업을 수행하고 싶을 때 특히 유용합니다.
4. 큰 데이터 세트 작업: 큰 데이터 세트에서 각 그룹별 평균, 합계, 최대값 등을 한 번에 계산해야 할 때 효과적입니다.

예를 들어, 학교 성적표 데이터를 분석하며 학급별 평균 점수를 계산하거나, 재고 관리 데이터에서 각 카테고리별 총 수량을 확인하는 데 유용할 수 있어요.

---

사용 시 꼭 기억하세요!

GROUPBY 함수를 사용할 때 몇 가지 주의할 점이 있습니다. 이를 염두에 두면 더 효과적으로 활용할 수 있어요:

1. Office 버전 확인: GROUPBY 함수는 Microsoft 365 Excel에서만 사용할 수 있습니다. Excel 2019나 이전 버전에서는 작동하지 않으니, 사용 중인 Excel 버전을 확인하세요.
2. 데이터 정렬 및 구성: 데이터를 그룹화하려면 array 매개변수가 올바르게 설정되어야 합니다. 테이블 형식의 데이터를 사용하는 것이 가장 이상적이며, 데이터가 체계적이지 않으면 오류가 발생할 수 있어요.
3. 지원되는 함수 확인: GROUPBY 함수에 입력하는 집계 함수(예: SUM, AVERAGE 등)가 배열 작업을 지원하지 않는 경우 오류가 발생할 수 있으니, 사용하려는 함수의 호환성을 확인해야 합니다.
4. 배열 함수 특성 이해: GROUPBY는 배열 함수로, 작업 결과가 동적으로 업데이트됩니다. 데이터 변경 시 결과도 자동으로 갱신되지만, 기존의 셀에 결과가 겹치면 "스필" 오류가 발생할 수 있으니 여유 공간을 확보하세요.
5. 다단계 그룹화 제한: GROUPBY는 단일 수준의 그룹화를 처리하는 데 유용하지만, 다단계 그룹화(예: 첫 번째 기준에 따른 그룹화 후 두 번째 기준 적용)에는 적합하지 않을 수 있습니다. 이 경우 Power Query나 피벗 테이블을 고려해야 합니다.
6. 복잡한 데이터 작업: 함수가 직관적이지만, 매우 복잡한 데이터 세트나 다수의 요약 작업에는 처리 속도가 느려질 수 있습니다. 이럴 때는 성능을 고려해야 해요.

 

---

마무리하며

피벗테이블이나 필터를 사용하는게 번거롭고 힘들다면 이 GROUPBY함수는 하나의 대안이 될 수 있습니다. 다만 상위버젼의 엑셀 버젼에서만 호환된다는 점이 무척 아쉽습니다.

이제 GROUPBY 함수 하나면 특정 데이터를 피벗 없이 간편하게 계산 할 수 있습니다.

이 함수만 익혀두면 품목별 합계 평균등 매번 반복되는 업무에 시간을 크게 절약할 수 있어요.

💬 지금 본인의 엑셀이 EXCEL2019이거나 Office365엑셀이라면 엑셀에서 직접 GROUPBY 함수를 입력해 보세요.
조금만 익숙해지면 정말 유용하게 쓰실 수 있을 거예요!