들어가기 전에
피벗 테이블은 강력하지만, FILTER와 GROUPBY 함수만으로도 요약·분석 작업을 더 간단하게 처리할 수 있습니다. FILTER 함수는 원하는 조건의 데이터만 동적으로 추출하고, GROUPBY는 범주별 집계와 요약을 만들 수 있습니다. 반복적인 보고서 작성이나 대시보드 작업에서는 피벗 테이블보다 최신 동적 배열 함수가 더 빠르고 유연합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: Two new Excel functions replaced every pivot table I used to build
- URL: https://www.makeuseof.com/two-excel-functions-replaced-every-pivot-table/
강력하지만 사용하기에는 다소 불편한 피벗 테이블
피벗 테이블에는 사용자를 괴롭히는 치명적인 결함이 하나 있습니다. 바로 자동으로 새로 고쳐지지 않는다는 점인데, 작업량이 많아질수록 이 문제는 더욱 심각해집니다. 멋진 요약 자료를 만들어서 누군가와 공유했는데 원본 데이터가 업데이트되면, 누군가 수동으로 테이블을 새로 고치지 않는 한, 갑자기 요약 내용이 거짓이 되어버립니다. 원본 데이터가 끊임없이 업데이트되는 빠른 속도의 스프레드시트 환경에서는 이 문제가 심각한 병목 현상을 일으켜 피벗 테이블을 거의 쓸모없게 만들어 버립니다.
또 다른 문제는 피벗 테이블이 수식 논리에서 분리되어 있다는 점입니다. 피벗 테이블을 다른 함수에 입력하거나, 필터 또는 정렬 함수에 연결하거나, 계산된 데이터를 가져오는 차트의 동적 소스로 사용하는 것이 쉽지 않습니다. 피벗 테이블은 강력한 도구이지만, 이러한 독립적인 구조 때문에 활용도가 크게 떨어집니다.
GROUPBY와 PIVOTBY 함수는 이 두 가지 문제를 모두 해결해 줍니다. 이제 Excel에서 정규 표현식을 지원하게 되었으므로 , 이전에는 수동으로 수정해야 했던 데이터도 자동으로 정리할 수 있습니다.
수식 속의 피벗 테이블처럼 느껴지는 GROUPBY 함수

GROUPBY 함수는 수식 안에 있는 피벗 테이블이라고 생각하면 됩니다. 가장 기본적인 형태에서는 세 가지 요소만 필요합니다. 그룹화할 열, 집계할 열, 그리고 사용할 함수입니다.
예를 들어 매출 데이터 세트가 있고 제품 카테고리별 총 매출을 알고 싶다면 다음과 같이 입력 =GROUPBY(C2:C500, E2:E500, SUM)하고 Enter 키를 누릅니다. Excel은 카테고리 이름(한 열)과 총액(다음 열)으로 구성된 전체 요약 표를 자동으로 시트에 생성하며, 원본 데이터의 변경 사항이 발생하면 즉시 업데이트됩니다. 매번 수동으로 새로 고칠 필요가 없습니다.
더욱 유용한 점은 하나의 수식에만 국한되지 않는다는 것입니다. 평균, 개수, 최소값, 최대값, 중앙값 함수는 물론 텍스트 집계 함수까지 활용하여 다양한 데이터를 보여주는 표를 만들 수 있습니다. 특히 텍스트 집계 함수를 사용하면 여러 행의 텍스트 값을 하나의 요약 셀로 결합할 수 있는데, 이는 피벗 테이블에서는 불가능한 기능입니다.
함수 내에서 직접 출력을 정렬하고, 포함할 행을 필터링하고, 합계 표시 여부를 제어할 수도 있습니다. 피벗 테이블을 사용할 때는 이러한 모든 작업에 추가 단계나 보조 열이 필요하지만, GROUPBY 함수에서는 이러한 값들을 인수로 직접 전달하기만 하면 됩니다.
여기서 한 단계 더 나아가는 PIVOTBY 함수

GROUPBY는 1차원 요약을 제공합니다. 행에는 범주가 표시되고 그 옆에는 집계된 값이 표시됩니다. 제품별 판매량과 월별 판매량처럼 2차원 데이터를 보려면 PIVOTBY를 사용해야 합니다.
PIVOTBY는 GROUPBY와 정확히 동일하게 작동하지만 두 번째 그룹화 축을 추가합니다. 행 필드, 열 필드, 값 범위 및 집계 함수를 제공하면 전체 교차표를 생성합니다. 예를 들어 다음을 고려해 보세요.
=PIVOTBY(C2:C500, A2:A500, E2:E500, SUM)
이 수식을 사용하면 왼쪽에 제품 카테고리, 위쪽에 연도(또는 월, 지역 또는 기타 축)가 표시되고 양 끝에 합계가 나타나는 매트릭스가 생성됩니다.
기존 피벗 테이블과 차별화되는 가장 큰 특징은 실시간으로 변화하는 동적 배열을 생성한다는 점입니다. 이 배열을 다른 함수로 감싸거나, 차트 데이터 소스로 사용하거나, 스필 표기법을 통해 개별 셀을 참조할 수도 있습니다. 데이터가 업데이트되면 차트도 자동으로 업데이트되고, 하위 함수도 자동으로 동기화되어 모든 것이 최신 상태를 유지합니다.
그럼에도 피벗 테이블이 더 나은 점
GROUPBY와 PIVOTBY 함수는 사용하기 쉽지만 모든 상황에서 피벗 테이블을 완벽하게 대체할 수는 없습니다. 슬라이서, 타임라인 또는 셀을 두 번 클릭하여 하위 행으로 이동하는 기능이 필요한 경우에는 여전히 기존 피벗 테이블을 사용하는 것이 좋습니다. 또한 피벗 테이블은 날짜를 연도, 분기, 월별로 자동 그룹화하는 기능을 제공하는데, 이러한 함수들은 기본적으로 이 기능을 지원합니다. 하지만 피벗 테이블에서 이 기능을 사용하려면 날짜를 YEAR() 또는 MONTH() 함수로 감싸야 하므로 약간의 추가 설정이 필요합니다.
더 중요한 것은, 이 기능들은 Microsoft 365 기능이므로, 이전 버전의 Excel을 사용하는 사람들과 파일을 공유하거나 본인이 이전 버전을 사용하는 경우 해당 수식이 작동하지 않는다는 점입니다.
마치며
매주 동일한 데이터 세트를 기반으로 보고서를 작성하고, 원본을 새로 고칠 때마다 요약 결과가 정확하게 표시되기를 기대하는 경우라면 일반 피벗 테이블보다 GROUPBY와 PIVOTBY 함수가 훨씬 더 유용합니다. 이러한 자동 완성 기능은 구축 속도가 빠르고, 새로 고침을 잊어버릴 염려가 없으며, Excel의 수식 엔진에 있는 다른 모든 기능과도 호환됩니다. 일단 익숙해지면 이전 방식으로 돌아가는 것은 불필요한 번거로움처럼 느껴집니다.
'Excel' 카테고리의 다른 글
| 추측으로 해결했던 문제들을 해결해 주는 숨겨진 엑셀 기능 (0) | 2026.10.22 |
|---|---|
| 과도한 IF 함수 사용을 멈추자 즉시 더 깔끔하고 빨라진 엑셀 (0) | 2026.10.19 |
| 복잡한 데이터 처리에서 VLOOKUP 보다 FILTER 함수를 써야 하는 이유 (0) | 2026.10.13 |
| 조용히 개선된 Micrsoft 365 큰 불편 사항 — 대부분의 사용자가 잘 모르는 (0) | 2026.10.12 |
| 엑셀 VBA 몰라도 되는 업무자동화, '엑셀 바이브 코딩' 책 소개 (0) | 2026.10.12 |
