들어가기 전에
한때 피벗 테이블은 엑셀의 가장 뛰어난 기능이었지만, 이제는 더 이상 그렇지 않습니다. 피벗 테이블을 만들고, 레이아웃을 조정하고, 데이터를 추가하거나 바꿀 때마다 새로 고치는 대신, 훨씬 적은 노력과 시간으로 동일한 결과를 얻을 수 있는 수식을 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: Most Excel users are still using Pivot Tables, but this new function is faster
- URL: https://www.makeuseof.com/pivotby-instead-of-pivot-tables/
PIVOTBY 함수 개요
PIVOTBY 함수를 사용하면 피벗 테이블처럼 두 축을 기준으로 요약을 그룹화하고 값을 집계할 수 있습니다. 하지만 모든 작업이 수식 안에 이루어지기 때문에 요약이 자동으로 업데이트되어 데이터의 추세를 더 쉽고 빠르게 파악할 수 있습니다.
겉보기에는 비슷한 결과물을 만들어내지만, PIVOTBY 함수는 엑셀의 기존 피벗 테이블 기능과는 전혀 관련이 없습니다. PIVOTBY 함수는 Microsoft 365용 엑셀, 엑셀 2024, 엑셀 2021(Mac 버전 포함)에서 사용할 수 있는 동적 배열 함수로, 데이터를 한 번에 그룹화, 집계, 정렬 및 필터링할 수 있습니다. 함수를 입력하고 Enter 키를 누르면 추가 설정 없이 엑셀이 완전한 요약 표를 워크시트에 바로 표시해 줍니다.
구문은 다음과 같습니다.
=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])
상당히 복잡해 보이지만 필요한 인수는 네 개뿐입니다. 행으로 사용할 열(row_fields), 상단에 표시할 열(col_fields), 집계할 데이터(values), 그리고 집계 방법입니다. 집계 방법은 SUM, AVERAGE, COUNT와 같은 익숙한 방법일 수도 있고, 사용자 지정 람다 함수 와 같은 고급 방법일 수도 있습니다 . 나머지 인수는 모두 선택 사항이며, 필요에 따라 추가적인 제어 기능을 제공합니다.
예를 들어, 품목 유형(행)과 판매 채널(열)별 총 이익을 가장 높은 순으로 정렬하여 요약하고 싶다고 가정해 보겠습니다. 이 경우 수식은 다음과 같습니다.
=PIVOTBY(C2:C5000, D2:D5000, N2:N5000, SUM,,,-2)
이 경우 C열에는 품목 유형, D열에는 판매 채널, N열에는 수익이 포함됩니다. 정렬 인수의 -2는 Excel에게 결과를 내림차순으로 정렬하도록 지시합니다. 그 앞에 쉼표 세 개가 있는 것을 볼 수 있는데, 이는 row_sort_order 인수가 일곱 번째 위치에 있기 때문이며, Excel은 건너뛸 인수에 대한 자리 표시자가 필요합니다.
기존 피벗 테이블과 마찬가지로 여러 행과 열 그룹을 결합할 수도 있습니다.
=PIVOTBY(HSTACK(YEAR(F2:F5000), C2:C5000), D2:E5000, N2:N5000, SUM)
여기서는 F열의 주문 연도와 C열의 품목 유형을 행 범주로, D열과 E열의 판매 채널 및 주문 우선순위를 열 범주로 그룹화했습니다. 행 그룹화 기준이 서로 다른 열에서 가져왔기 때문에 HSTACK 함수를 사용하여 하나의 배열로 결합했습니다. 한편, 열 그룹화 기준은 서로 인접해 있으므로 범위를 확장했습니다. 또한 주문 날짜 범위를 YEAR 함수로 감싸서 Excel에서 개별 날짜가 아닌 연도별로 그룹화하도록 했습니다.
이제 이 함수가 피벗 테이블의 기능을 어떻게 구현하는지 살펴보았으니, 차이점이 더 명확해질 것입니다. 첫째, PIVOTBY 함수는 원본 데이터가 변경될 때마다 자동으로 업데이트되므로 수동으로 새로 고칠 필요가 없습니다. 둘째, PIVOTBY 함수는 단순한 수식이기 때문에 워크시트의 다른 부분처럼 출력 결과를 다룰 수 있습니다. 즉, 레이아웃이 깨질 염려 없이 드롭다운 메뉴에 연결하거나 서식을 적용하거나 대화형 대시보드를 구축할 수 있습니다. 셋째, 람다 함수를 지원하므로 기본 제공 옵션을 뛰어넘는 사용자 지정 집계 논리를 생성하여 기존 피벗 테이블로는 구현할 수 없는 분석을 수행할 수 있습니다.
PIVOTBY 함수 활용
PIVOTBY는 빠르고 자동화된 보고서를 최소한의 유지 관리로 생성하고자 할 때 특히 유용합니다. 수식 형태로 제공되므로 오래된 요약이나 왜곡된 결과가 발생할 위험이 없으며, 항상 최신 데이터를 반영합니다. 특히 필요한 그룹화 및 집계 방식을 이미 알고 있는 경우, 필드를 수동으로 재배열하는 대신 모든 것을 미리 정의할 수 있어 매우 편리합니다.
그렇긴 하지만, 기존 피벗 테이블도 여전히 유효합니다. Microsoft 365, Excel 2024 또는 Excel 2021이 설치되지 않은 동료와 통합 문서를 공유하는 경우, 피벗 테이블은 제대로 작동하지 않습니다. 모든 사람이 수식 편집에 익숙하지 않은 공동 작업 환경에서는 드래그 앤 드롭 방식의 기존 피벗 테이블이 유지 관리를 더 쉽게 해줍니다. 또한 피벗 테이블은 빠른 탐색적 분석과 다른 사용자가 데이터를 대화형으로 자세히 살펴볼 수 있도록 확장 가능한 계층 구조를 제공하는 데 여전히 유리합니다.
하지만 대부분의 경우 PIVOTBY는 제 워크플로에 완벽하게 통합됩니다. 저는 단일 데이터 세트를 요약할 때 PIVOTBY를 자주 사용하는데, 결과가 훨씬 빠르게 나오고 추가 설정이 필요 없기 때문입니다. 뿐만 아니라 PIVOTBY는 단일 시트를 넘어 확장성도 뛰어납니다. PIVOTBY 안에 VSTACK을 중첩하면 여러 워크시트의 데이터를 거의 즉시 통합된 요약으로 결합할 수 있습니다. 예를 들어, Mercy, Mike, Mitchell 세 직원의 매출 데이터가 각각 다른 시트에 있고, 위치별 및 직원 직급별 매출을 요약하고 싶다면 다음과 같이 작성할 수 있습니다.
=PIVOTBY(VSTACK(Mercy!C2:C5, Mike!C2:C5, Mitchell!C2:C5), VSTACK(Mercy!D2:D5, Mike!D2:D5, Mitchell!D2:D5), VSTACK(Mercy!B2:B5, Mike!B2:B5, Mitchell!B2:B5), SUM)
filter_array 인수를 드롭다운 메뉴에 연결하여 요약 결과를 대화형으로 만들 수도 있습니다. 예를 들어, 다음 수식은 국가 및 우선순위에 따른 주문 수를 계산하지만, 셀 V2에서 선택한 품목 유형에 대해서만 계산합니다.
=PIVOTBY(B2:B5000, E2:E5000, G2:G5000, COUNT,,,,,,C2:C5000=V2)
표현식 C2:C5000=V2는 각 행을 평가하고 일치하는 레코드만 반환하는 부울 마스크 역할을 합니다. V2에 "모두"가 포함된 경우 모든 주문을 표시하려면 filter_array 인수를 IF 문으로 묶으면 됩니다.
=PIVOTBY(B2:B5000, E2:E5000, G2:G5000, COUNT,,,,,,IF(V2="all", TRUE, (C2:C5000=V2)))
요약 정보를 시각화하려면 표준 차트를 PIVOTBY 스필 범위에 직접 연결할 수 있습니다. 이렇게 하면 데이터가 변경될 때 시각화가 자동으로 업데이트됩니다. 따라서 피벗 차트가 없어도 불편함을 느끼지 않으실 겁니다.
마치며
PIVOTBY가 모든 상황에서 피벗 테이블을 완전히 대체할 수는 없지만, 동적 대시보드를 구축하거나, 여러 시트의 데이터를 통합하거나, 반복적인 설정 단계를 없앨 때는 분명한 이점을 제공합니다. 모든 작업이 단일 수식을 통해 이루어지기 때문에 자동으로 최신 상태를 유지하고 워크시트의 나머지 부분과 쉽게 통합할 수 있는 요약을 생성할 수 있습니다.
'Excel' 카테고리의 다른 글
| IF 대신 사용하면 편리한 이 엑셀 함수 (0) | 2026.06.19 |
|---|---|
| 엑셀 체크박스 수동 클릭 대신, 수식을 이용해 자동화하기 (0) | 2026.06.18 |
| [엑셀 바이브 코딩 #1] 엑셀 바이브 코딩으로 호랑이에게 날개달기 (0) | 2026.06.17 |
| BYROW를 사용하여 Excel 오류를 방지하고 확장성을 높이는 방법 (0) | 2026.05.29 |
| 윈도우와 맥에서 사용할 수 있는 Excel의 강력한 Agent 모드 (0) | 2026.05.26 |
