새로운 2가지 Excel 함수를 사용하여 데이터 분석 간소화하는 법

들어가기 전에

Excel의 피벗 테이블은 강력하지만 데이터를 요약할 때마다 새로 고침해야 하고 사용상 불편한 점이 있습니다. 최근에 추가된 GROUPBY, PIVOTBY 함수를 사용하면 기존 피벗 테이블에 대한 동적이고 유연한 대안을 제공하여 수식 내에서 직접 요약 및 보고서를 만들 수 있습니다.

권현욱(엑셀러) | 아이엑셀러 닷컴 대표 · Microsoft Excel MVP · Excel 솔루션 프로바이더 · 작가

이미지: 아이엑셀러 닷컴

 

※ 이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.

---

• 원문: How to use Excel GROUPBY and PIVOTBY Functions to simplify data analysis
• URL: https://www.geeky-gadgets.com/excel-data-analysis-2024/

---

GROUPBY 함수

GROUPBY 함수를 사용하면 지정된 행 필드 및 값을 기준으로 데이터를 집계할 수 있습니다. 구문은 아래와 같이 간단합니다. 예를 들어, 이 함수를 사용하여 부서 및 영업 관리자별 매출을 합산할 수 있습니다. 이렇게 하면 복잡한 수식이나 수동 계산 없이도 데이터를 빠르게 요약하고 인사이트를 쉽게 얻을 수 있습니다. GROUBY는 또한 데이터 집계를 더욱 맞춤화할 수 있는 몇 가지 선택적 인수를 제공합니다.

=GROUPBY(Row fields, Values, Function)

 

• Field Headers: 헤더를 사용자 지정하여 집계된 데이터를 더 명확하게 파악하고 이해할 수 있도록 합니다.
• Total Depth: 총합계 및 소계의 표시를 보고 요구 사항에 맞게 제어합니다.
• Sort Order: 분석과 해석이 쉽도록 지정된 열을 기준으로 결과를 정렬합니다.
• Filter Array: 조건에 따라 특정 행을 제외하여 데이터 집합을 세분화하고 관련 정보에 집중할 수 있습니다.

 

기존 피벗 테이블과 비교했을 때 GROUPBY는 몇 가지 장점이 있습니다. 데이터 변경에 따른 동적 업데이트를 제공하므로 요약에 항상 최신 정보가 반영됩니다. 또한 GROUPBY는 텍스트 값을 반환하고 내장된 계산을 통해 표 형식이 아닌 데이터를 처리할 수 있어 데이터 분석의 유연성이 향상됩니다.

 

고급 GROUPBY 함수

GROUPBY는 기본 집계를 넘어 보다 복잡한 데이터 조작을 위한 고급 기능을 지원합니다. GROUPBY 내에서 Lambda 함수를 사용하여 고유한 텍스트 집계를 수행하여 고도로 맞춤화된 데이터 요약을 만들 수 있습니다. 이를 통해 이전에는 어렵거나 시간이 많이 걸리던 방식으로 데이터를 분석하고 해석할 수 있는 새로운 가능성이 열립니다.

날짜 처리는 GROUBY가 탁월한 또 다른 영역입니다. 연도별 또는 연도-월별 조합으로 데이터를 쉽게 집계할 수 있는 `YEAR` 및 `TEXT`와 같은 함수를 사용할 수 있습니다. 이렇게 하면 시간 경과에 따른 추세와 패턴을 분석하는 과정이 간소화되어 데이터의 계절성이나 장기적인 변화를 쉽게 파악할 수 있습니다.

또한, `HSTACK` 기능을 사용하면 GROUPBY 내에서 여러 행 필드를 결합할 수 있습니다. 이렇게 하면 데이터 집계의 유연성이 향상되어 데이터의 여러 차원을 아우르는 보다 포괄적이고 의미 있는 요약을 만들 수 있습니다.

PIVOTBY 함수

PIVOTBY 함수는 행과 열 필드 모두를 기준으로 데이터를 요약할 수 있도록 하여 데이터 집계를 한 단계 더 발전시킵니다. 구문은 GROUPBY와 유사합니다. 예를 들어, 영업 관리자별 매출을 연도별로 열에 요약하여 여러 기간에 걸친 실적을 명확하게 파악할 수 있습니다.

=PIVOTBY(Row fields, Column fields, Values, Function)

 

PIVOTBY도 GROUPBY와 마찬가지로 데이터 요약을 사용자 지정할 수 있는 선택적 인수를 제공합니다.

• Column Total Depth: 집계된 데이터에 대한 보다 명확한 개요를 위해 열 합계 표시를 제어합니다.
• Column Sort Order: 더 쉬운 해석과 분석을 위해 지정된 기준에 따라 열을 정렬합니다.
• Filter Array: 조건에 따라 특정 열을 제외하여 데이터 집합을 더욱 세분화하고 관련 정보에 집중할 수 있습니다.

 

PIVOTBY 함수는 Excel 내에서 직접 포괄적이고 통찰력 있는 보고서를 만들 수 있는 새로운 가능성을 열어줍니다. 다양한 관점에서 데이터를 분석할 수 있으므로 원시 데이터에서 즉시 드러나지 않을 수 있는 패턴, 추세, 관계를 더 쉽게 파악할 수 있습니다.

 

마치며

GROUPBY와 PIVOTBY의 힘을 활용하면 데이터 분석 프로세스를 크게 개선할 수 있습니다. 이들 함수는 데이터를 집계하고 요약하는 보다 효율적이고 동적인 방법을 제공하여 시간과 노력을 절약하는 동시에 가치 있는 인사이트를 제공합니다. 영업 데이터, 재무 보고서 또는 기타 모든 유형의 정보로 작업할 때 GROUPBY와 PIVOTBY는 데이터를 이해하고 더 나은 의사 결정을 내리는 데 도움이 될 수 있습니다.

멤버십 안내 "최고" 가성비로 "최신" Office 활용 정보를 보내드립니다.

추천 도서 심혈을 기울여 집필한 책들입니다. 자신있게 추천합니다.

강의 & 개발 최고 전문가의 실무 노하우를 아낌 없이 전수해 드립니다.