[파워피벗] 계산된 열을 사용한 피벗테이블에 자동 금액구간 만들기

어떤 목적에서든  데이터 집계. 요약을 하다보면 유의미한 수준으로 그룹핑을 해야 할 경우가 많습니다. 고객유형, 품목범주, 매출유형 등등 대표성을 갖는 값들로 세부 데이터를 묶는 경우가 일반적입니다. 

이 경우 피벗 테이블을 통해서 간단하게 그룹핑 하는 방법 부터 LOOKUP 함수를 이용해서 그룹핑 항목을 원본 데이터에 붙이기 등 여러 방법이 있죠. 그리고 그리 어렵지 않게 적용이 가능합니다. 

하지만 그룹핑 대상이 숫자 값, 즉 금액이나 수량과 같이 구간으로 그룹핑 해야 하는 경우 상당히 귀찮은 작업이 됩니다. 중복 IF 문 또는 IFS 로 반복된 여러 조건을 주며 구간을 나누어 주어야 하기 때문이죠. 

이걸 파워피벗의 계산된열을 사용해서 쉽게 처리 할 수 있는 방법을 소개합니다. 

먼저 예시 데이터는 아래와 같습니다. 

여기서 판매수량을 9개의 구간으로 나누어 그룹핑을 할 것입니다. 

위 표의 from 과 to에 각 구간의 범위를 지정합니다. 

그리고 위 두개의 데이터를 모두 파워피벗의 테이블로 구성합니다. 여기서 테이블의 이름은 각각 fSales 와 dRange 로 명명하였습니다. 

파워피벗 편집기를 열어 아래와 같이 fSales 테이블에 새로운 계산된 열을 만듭니다. 

계산된 열을 생성하는 수식을 아래와 같습니다. 

=CALCULATE(
                  VALUES(dRange[구간]),
                  FILTER(dRange,
                           fSales[판매수량] >= dRange[from]
                           &&fSales[판매수량] <  dRange[to]
                   )
   )

Filter 함수를 이용하여 현재 fSales 테이블의 각 행별로 판매수량이 from과 to 사이에 들어가는 각 구간을 단일값으로 반환하는 수식입니다. 

이걸 피벗 테이블에 반영하면 수량구간 별로 요약이 가능합니다. 

이렇게 되면 구간을 정하는 기준을 바꿀때 요약되는 값의 수준을 보면서 처리가 가능합니다. 즉 유의미한 구간으로 만드는 과정이 보다 쉽게 처리 할 수 있게 된 것이죠. 

구간의 범위를 늘렸다 줄였다 하면서 원하는 상태로 접근하는 과정이 보다 간결해집니다. 

만약 이걸 중복IF로 사용했다면 IF를 8번 사용해야 합니다. (구간이 9개 이므로)
게다가 구간을 수정할 일이 생길때마다 수식을 수정해야죠. ㅜㅜ; 

하지만 이제 더 이상 중복IF를 복잡하게 사용할 필요가 없겠네요. 

예제 파일 첨부합니다. 

파워피벗_구간 만들기.xlsx

1.01MB