PIVOTBY 함수 – 행과 열로 집계, 정렬, 필터링

다음과 같은 판매실적 데이터가 있을 때 거래처,상품별 집계를 하려면 주로 피벗테이블을 사용했지만 Microsoft 365(Office 365) Excel이 있으면 PIVOTBY 함수 하나로 피벗테이블과 같은 모양의 집계표를 만들 수 있습니다.

피벗테이블과 PIVOTBY 함수의 기능이 정확히 같은 것은 아닙니다. 피벗테이블은 필드를 행 또는 열에다 끌어다 놓고 다양한 분석을 할 수 있었지만  PIVOTBY 함수로 그렇게 하려면 수식을 수정해야 합니다.

※ PIVOTBY 함수는 2024년 11월 현재 Microsoft 365 '2409(빌드 18025.20096)' 버전 이상에서만 사용가능(버전 확인 및 업데이트 방법은 여기를 클릭!)

 

구문(Syntax)

 

사용 예

거래처, 상품별 집계 하기

다음과 같은 판매일/거래처/상품별 판매실적 자료를 이용하여 '거래처, 상품'으로 그룹화하여 합계를 만들어 보겠습니다.

다운로드한 실습 엑셀파일의 [거래처,상품별 집계] 시트를 선택하고 [G5] 셀에 '=PIVOTBY(' 까지 입력하고 첫 번째 인수(row_fields: 행으로 그룹화할 범위)가 입력되도록 ① '거래처' 범위 [C5:C21]을 선택하고 쉼표를 입력합니다.

=PIVOTBY(C5:C21,

 

두 번째 인수(col_fields: 열로 그룹화할 범위)에는 ② '상품' 범위 [D5:D21]를 선택하고 쉼표를 입력합니다.

=PIVOTBY(C5:C21,D5:D21,

 

세 번째 인수(values: 집계할 값)에는 ③ '판매금액' 범위 [E5:E21]를 선택하고 쉼표를 입력합니다.

=PIVOTBY(C5:C21,D5:D21,E5:E21,

 

합계를 구하는 것이므로 네 번째 인수(function: 집계시 사용할 함수)에는 ④ 'SUM'을 선택합니다.

=PIVOTBY(C5:C21,D5:D21,E5:E21,SUM

합계가 아니라 갯수를 구한다면 COUNT, 평균을 구한다면 AVERAGE를 선택하는 식으로 함수 목록에서 원하는 함수를 선택하면 됩니다.

 

마지막으로 괄호를 입력하고 Enter를 누르면 다음과 같이 수식이 완성되고

=PIVOTBY(C5:C21,D5:D21,E5:E21,SUM)

⑤ 아래와 같이 결과가 하나의 셀이 아닌 범위에 표시됩니다.

▷ 동적 배열 수식을 사용하면 수식의 결과를 하나의 셀이 아닌 범위에 표시할 수 있습니다. '엑셀의 새로운 기능, 동적 배열 수식'을 참고하세요.

결과가 나오긴 했는데 제목행이 잘 구분되지도 않고 숫자에 1000단위 콤마가 붙어 있지 않아서 읽기 힘듭니다.

 

결과를 읽기 쉽게 만들고 싶다면 다음과 같이 ⑥ 표를 꾸미고 [표시형식]에서 숫자를 원하는 형식으로 바꿔줍니다.

 

수식 풀이:

 

 

거래처, 상품, 판매일별 집계 하기

이번에는 '거래처, 상품, 판매일별'로 그룹화하여 합계를 만들어 보겠습니다.

다운로드한 실습 엑셀파일의 [거래처,상품,판매일별 집계] 시트를 선택하고 [G5] 셀에 '=PIVOTBY(' 까지 입력하고 첫 번째 인수(row_fields: 행으로 그룹화할 범위)가 입력되도록 ① '거래처, 상품' 범위 [C5:D21]을 선택하고 쉼표를 입력합니다.

=PIVOTBY(C5:D21,

 

두 번째 인수(col_fields: 열로 그룹화할 범위)에는 ② '판매일' 범위 [B5:B21]를 선택하고 쉼표를 입력합니다.

=PIVOTBY(C5:D21,B5:B21,

 

세 번째 인수(values: 집계할 값)에는 ③ '판매금액' 범위 [E5:E21]를 선택하고 쉼표를 입력합니다.

=PIVOTBY(C5:D21,B5:B21,E5:E21,

 

합계를 구하는 것이므로 네 번째 인수(function: 집계시 사용할 함수)에는 ④ 'SUM'을 선택합니다.

=PIVOTBY(C5:D21,B5:B21,E5:E21,SUM

합계가 아니라 갯수를 구한다면 COUNT, 평균을 구한다면 AVERAGE를 선택하는 식으로 함수 목록에서 원하는 함수를 선택하면 됩니다.

 

마지막으로 괄호를 입력하고 Enter를 누르면 다음과 같이 수식이 완성되고

=PIVOTBY(C5:D21,B5:B21,E5:E21,SUM)

⑤ 아래와 같이 결과가 하나의 셀이 아닌 범위에 표시됩니다.

▷ 동적 배열 수식을 사용하면 수식의 결과를 하나의 셀이 아닌 범위에 표시할 수 있습니다. '엑셀의 새로운 기능, 동적 배열 수식'을 참고하세요.

결과가 나오긴 했는데 제목행이 잘 구분되지도 않고 날짜는 44751과 같이 숫자로 표시되고 판매금액에 1000단위 콤마가 붙어 있지 않아서 읽기 힘듭니다.

 

결과를 읽기 쉽게 만들고 싶다면 다음과 같이 ⑥ 표를 꾸미고 [표시형식]에서 날짜는 리본 메뉴 [홈] → [표시형식] → [간단한 날짜]로 바꿔주고, 판매금액은 [표시형식] 에서 원하는 형식으로 바꿔줍니다.

 

수식 풀이:

 

 

행과 열로 그룹화 + 소계 포함

이번에는 행과 열로 그룹화하면서 집계 결과에 소계를 넣어 보겠습니다.

다운로드한 실습 엑셀파일의 [소계포함] 시트를 선택하고 [G5] 셀에 다음 수식을 입력합니다.

=PIVOTBY(C5:D21,B5:B21,E5:E21,SUM,3,2)

다음과 같이 결과가 [G5:M21] 범위에 표시됩니다. 결과가 나오긴 했는데 제목과 소계 행이 잘 구분되지도 않고 날짜는 44751과 같이 숫자로 표시되고 판매금액에 1000단위 콤마가 붙어 있지 않아서 읽기 힘듭니다.

 

결과를 읽기 쉽게 만들고 싶다면 다음과 같이 표를 꾸미고 [표시형식]에서 날짜는 리본 메뉴 [홈] → [표시형식] → [간단한 날짜]로 바꿔주고, 판매금액은 [표시형식] 에서 원하는 형식으로 바꿔줍니다.

 

수식 풀이:

 

 

집계 결과 정렬

PIVOTBY 함수로 집계만 하는 것이 아니라 결과를 원하는 순서로 정렬할 수 있습니다.

다운로드한 실습 엑셀파일의 [정렬] 시트를 선택하고 [G5] 셀에 다음 수식을 입력합니다.

=PIVOTBY(C5:D21,B5:B21,E5:E21,SUM,3,2,,,-1)

아홉 번째 인수(col_sort_order: 열단위 정렬 인덱스 번호)에 '-1'을 설정했으므로 열 단위 집계결과의 첫 번째 열(판매일)이 내림차순(최근 날짜가 먼저 옴)으로 정렬되어 표시됩니다.

아홉 번째 인수는 다음 두 가지를 사용할 수 있습니다.

• 양수: 오름차순 정렬
• 음수: 내림차순 정렬

 

수식 풀이:

 

 

집계 결과 필터링

이번에는 PIVOTBY 함수로 원하는 데이터만 걸러내는 필터링 기능을 사용해서 집계해 보겠습니다.

다운로드한 실습 엑셀파일의 [필터링] 시트를 선택하고 [G5] 셀에 다음 수식을 입력합니다.

=PIVOTBY(C5:C21,D5:D21,E5:E21,SUM,,,,,,D5:D21="노트")

열 번째 인수(filter_array: 필터링 조건 설정)에 D5:D21="노트"을 설정했으므로 상품이 '노트'인 것만 가져와서 집계해 줍니다.

 

※ 만약 상품 중에서 '노트'가 아닌 것만(노트 제외) 집계하려면 다음과 같이 수식을 입력합니다.

=PIVOTBY(C5:C21,D5:D21,E5:E21,SUM,,,,,,D5:D21<>"노트")

 

 

 

백분율로 표시하기

이번에는 PIVOTBY 함수의 네 번째 인수(function: 집계시 사용할 함수)로 PERCENTOF 함수를 지정해서 백분율을 계산해 보겠습니다.

열의 합계를 분모로 하여 백분율 계산

다운로드한 실습 엑셀파일의 [백분율-열합계] 시트를 선택하고 [G12] 셀에 다음 수식을 입력합니다.

=PIVOTBY(C5:C21,D5:D21,E5:E21,PERCENTOF,,,,,,,0)

마지막 인수 relative_to는 'function'에 두번 째 인수가 필요한 함수(일반적으로 PERCENTOF 함수)를 지정할 때 계산 방식을 결정합니다. 위 수식에서 마지막 인수를 0으로 지정했으므로 열의 합계를 분모로 하여 백분율을 계산해줍니다.

• relative_to: 상대적
  0: 열의 합계(기본값)를 두 번째 인수로 지정(PERCENTOF 함수의 경우 열합계를 분모로 하여 백분율을 계산)
  1: 행의 합계를 두 번째 인수로 지정
  2: 총계를 두 번째 인수로 지정
  3: 상위 열의 합계를 두 번째 인수로 지정
  4: 상위 행의 합계를 두 번째 인수로 지정

 

행의 합계를 분모로 하여 백분율 계산

다운로드한 실습 엑셀파일의 [백분율-행합계] 시트를 선택하고 [G12] 셀에 다음 수식을 입력합니다.

=PIVOTBY(C5:C21,D5:D21,E5:E21,PERCENTOF,,,,,,,1)

마지막 인수 relative_to는 'function'에 두번 째 인수가 필요한 함수(일반적으로 PERCENTOF 함수)를 지정할 때 계산 방식을 결정합니다. 위 수식에서 마지막 인수를 1로 지정했으므로 행의 합계를 분모로 하여 백분율을 계산해줍니다.

• relative_to: 상대적
  0: 열의 합계(기본값)를 두 번째 인수로 지정(PERCENTOF 함수의 경우 열합계를 분모로 하여 백분율을 계산)
  1: 행의 합계를 두 번째 인수로 지정
  2: 총계를 두 번째 인수로 지정
  3: 상위 열의 합계를 두 번째 인수로 지정
  4: 상위 행의 합계를 두 번째 인수로 지정

 

상위 열의 합계를 분모로 하여 백분율 계산

이번에는 하나의 행과 두 개의 열로 집계한 자료에서 상위 열의 합계를 분모로 하여 백분율을 계산해 보겠습니다. 이 방법은 상위 열 기준으로 하위 열의 비중 또는 분포를 파악할 때 유용합니다.

 

다운로드한 실습 엑셀파일의 [백분율-상위열합계] 시트를 선택하고 [G14] 셀에 다음 수식을 입력합니다.

=PIVOTBY(B5:B21,C5:D21,E5:E21,PERCENTOF,,,,,,,3)

마지막 인수 relative_to는 'function'에 두번 째 인수가 필요한 함수(일반적으로 PERCENTOF 함수)를 지정할 때 계산 방식을 결정합니다. 위 수식에서 마지막 인수를 3으로 지정했으므로 상위 열의 합계를 분모로 하여 백분율을 계산해줍니다.

• relative_to: 상대적
  0: 열의 합계(기본값)를 두 번째 인수로 지정(PERCENTOF 함수의 경우 열합계를 분모로 하여 백분율을 계산)
  1: 행의 합계를 두 번째 인수로 지정
  2: 총계를 두 번째 인수로 지정
  3: 상위 열의 합계를 두 번째 인수로 지정
  4: 상위 행의 합계를 두 번째 인수로 지정

위의 예는 거래처 '강남지사'의 7월 9일 전체 판매액 기준으로 노트, 사무기기, 필기구의 판매 비중을 보여줍니다.

 

 

---

관련 글