PIVOTBY 함수 (Microsoft 365 Excel 신규 호환함수)

【 함수 요약 】

Microsoft 365에 신규 추가될 예정인 PivotBy 함수를 이전 버전에서 사용할 수 있는 호환함수

아직 공식 배포되지 않은 함수로 최종 배포시 사용법이 변경되면 그에 따라 수정될 수 있습니다.

 

이 함수는 EGTools 추가기능에서 지원하는 함수입니다.

 

원본함수 설명  :   

https://support.microsoft.com/en-us/office/pivotby-function-de86516a-90ad-4ced-8522-3a25fac389cf

 

원본 함수와 차이점 :

1. 원본함수처럼 함수명을 그냥 사용할 수 없고, "SUM" 또는 "sum"처럼 쌍따옴표로 입력해야 함

2. 원본함수처럼 사용자가 작성한 Lambda 수식을 설정할 수 없음

3. Lambda 함수 지원대신에 "TextJoin", "CountUnique", "TextJoinUnique"  집계합수 추가함 (v4.0.2)

    => 구번전 사용자의 신청이 있을 경우 추가 함수 검토 가능

 

【 적용 버전 】

Windows용 : 여러 함수를 사용할 수 있는데 지원하는 버전이 다름
STDEV.S, STDEV.P, VAR.S, VAR.P, MODE.SNGL : Excel 2007~

SUM, PERCENTOF, AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, ARRAYTOTEXT, CONCAT, TEXTJOIN, COUNTUNIQUE, TEXTJOINUNIQUE : 모든버전

 

【 함수 구문 】

= FUNCTION(  row_Fields ,  value_Array ,  function_Name ,  [field_Headers] ,  [total_Depth] , [sort_Order] , [filter_Array]  )

 

【 함수 인수 】

인수명	옵션	기본값	설    명
row_Fields	필수 (둘 중 하나 or 둘 다)	없음	요약표의 각 행 앞쪽에 표시할 열단위로 입력된 자료의 범위 또는 배열 여러 열을 선택할 수 있으나 하나의 구역으로 선택되어야 함 함수 결과에 동일한 열 수가 출력됨
col_Fileds		없음	요약표의 각 열 윗쪽에 표시할 열단위로 입력된 자료의 범위 또는 배열 여러 열을 선택할 수 있으나 하나의 구역으로 선택되어야 함 함수 결과에 동일한 열 수가 출력됨
value_Array	필수	없음	요약을 진행할 열단위로 입력된 자료의 범위 또는 배열 여러 열을 선택할 수 있으나 하나의 구역으로 선택되어야 함 함수 결과에 row_Fileds 오른쪽으로 동일한 열 수가 출력됨 행수는 row_Fields 및 col_Fields와 같아야 함
function_Name	필수	없음	요약에 적용할 함수,   (대소문자 구분 없음) "SUM", "PERCENTOF", "AVERAGE", "COUNT", "COUNTA", "MAX", "MIN", "PRODUCT", "ARRAYTOTEXT", "CONCAT", "STDEV.S", "STDEV.P","VAR.S", "VAR.P", "MODE.SNGL", "TEXTJOIN", "COUNTUNIQUE", "TEXTJOINUNIQUE"
field_Headers	선택	Auto	row_Fields와 value_Array의 표머릿글이 있는지와 출력형태를 지정 생략하면 자동으로 추정 (첫줄과 둘째줄을 비교하여 0이나 1로 반영)   0: 표 머릿글 없음 1: 표 머릿글 있는데, 출력은 안함   2: 표 머릿글 없는데, 출력에는 표시 3: 표 머릿글이 있고, 출력에도 그대로 표시
row_total_Depth	선택	Auto	행방향의 합계를 추가할 것인지 지정 생략하면 자동  0: 합계출력 없음   1: 총합계만 출력하고 목록의 아래쪽에 위치  2: 총합계 및 소계를 출력하고 목록의 아래쪽에 위치 -1: 총합계만 출력하고 목록의 위쪽에 위치 -2: 총합계 및 소계를 출력하고 목록의 위쪽에 위치
row_sort_Order	선택	row_Fields 오름차순	행방향 결과를 출력할 때 정렬 여부를 지정 숫자를 지정하는데, 출력되는 열번호를 사용하며 양수를 지정하면 오름차순, 음수를 지정하면 내림차순 row_fields의 정렬은 여러개를 지정할 수 있는데 { , } 를 사용하여 지정 예) {1, -2}  -> 첫번째 열은 오름차순, 두번째 열은 내림차순 value_Array 해당 열은 1개만 지정할 수 있으며 row_Fields 열에 대해서 다단계로 동일한 정렬방법이 적용됩니다.
col_total_Depth	선택	Auto	열방향의 합계를 추가할 것인지 지정 생략하면 자동   0: 합계출력 없음   1: 총합계만 출력하고 목록의 오른쪽에 위치  2: 총합계 및 소계를 출력하고 목록의 오른쪽에 위치 -1: 총합계만 출력하고 목록의 왼쪽에 위치 -2: 총합계 및 소계를 출력하고 목록의 왼쪽에 위치
col_sort_Order	선택	col_Fields 오름차순	열방향 결과를 출력할 때 정렬 여부를 지정 숫자를 지정하는데, 출력되는 열번호를 사용하며 양수를 지정하면 오름차순, 음수를 지정하면 내림차순 row_fields의 정렬은 여러개를 지정할 수 있는데 { , } 를 사용하여 지정 예) {1, -2}  -> 첫번째 열은 오름차순, 두번째 열은 내림차순 value_Array 해당 열은 1개만 지정할 수 있으며 col_Fields 열에 대해서 다단계로 동일한 정렬방법이 적용됩니다.
filter_Array	선택	없음	row_Fields와 value_Array를 필터링하는 조건이 평가된 결과 배열로 row_Fields와 value_Array의 행수와 같아야 함. Excel 2019 이하는 조건식을 Ctrl+Shift+Enter로 입력하거나,  Index( 조건식, ) 처럼 입력함 예) {=PivotBy( ,,,,, (A1:A10=2023)*(B1:B10="출근") )}           =PivotBy( ,,,,, Index( (A1:A10=2023)*(B1:B10="출근") ,) )

 

【 함수 상세 설명 】

피벗테이블을 만드는 것처럼 여러 옵션을 지정하여 출력 형태를 조정할 수 있으며

피벗테이블과 달리 계산 방식이 "자동"인 경우 목록의 변경이 수식 계산으로 즉시 반영 됩니다.

value_Array는 반드시 있어야 하며, row_Fields와 col_Fields는 최소 1개 이상 있어야 합니다.

 

GroupBy 함수는 row_Fields에 따라 세로형 표만 만들수 있지만,

PivotBy 함수는 첫번째 row_Fields를 넣고, col_Fields를 비우면 GroupBy와 같이 세로형 표가 되고,

추가로 row_Fields를 비우고, col_Fields만 사용하면 가로형 집계표가 됩니다.

 

추가로 지원하는 집계함수는 아래와 같은 주의 필요

TEXTJOIN 및 TEXTJOINUNIQUE 함수를 사용할 때에는 연결문자를  "TextJoin(,)" 처럼 괄호안에 구분자를 넣어야 함.

 

【 함수 오류 설명 】

인수가 많아서 잘 못 적용할 경우 #VALUE! 오류가 나오므로 인수 적용 주의 필요함

- row_Fields, col_Fields는  value_Array와 행수가 같지 않거나, filter_Array 보다 작으면 #VALUE! 

- field_Headers, total_Depth, sort_Order 등에 가능한 옵션 이외를 지정한 경우 #VALUE!

함수 이름은 대소문자를 구별하지 않으나 지원하지 않는 이름은 #NAME?  오류

사용하는 통계 함수에 따라 내용이 #DIV/0 등으로 표시될 수 있습니다.

=> Excel 2021이상은 IfError함수로 처리하면 되지만    

     Excel 2019 이하에서는 EGTools에서 새로 지원하는 IfErrorX 함수로 사용하여 처리하세요.

    = IfErrorX( PivotBy(,,,) , "")    -> 주변 영역으로 결과 값을 자동으로 펼쳐줍니다.  

 

【 함수 사용시 주의사항 】

동적 영역에 출력하는 함수이므로 출력 범위를 경고 없이 덮어 쓸 수 있으므로 주의 필요

대량의 자료를 사용하는 경우 시간이 오래 걸릴 수 있으므로 피벗테이블 사용을 권함

출력후 가능하면 값으로 변경하여 Excel 사용에 부담이 되지 않도록 하기를 권함.

값열의 제목이 연도/월/일 처럼 날짜나 숫자인 경우 field_Headers를 반드시 지정이 필요함.

(value_Array 첫번째 자료가 텍스트가 아니면 Header가 없다고 잘못 판단하므로 옵션인 3을 지정해야 함)

 

【 함수 사용 예제 】

PivotBy_Sample.xlsx

0.10MB

 

 

 

【 비슷한 기능의 다른 함수 】

GROUPBY