[파워피벗공부] Pivot에 문자를 값항목으로 넣기

https://blog.naver.com/djabba

Data_Text를 피벗 값항목으로 사용.xlsx

0.26MB

파워 피벗에서는 텍스트를 값항목에 넣을 수 있다. 

[활용 포인트]

업무를 하다보면 가끔 텍스트를 값 항목으로 넣는 표를 만들어야 할 경우가 있다. 이런 경우 입력량이 적고 내용을 정리하면서 타이핑 하는 것이 보통이다. 하지만 설문 결과의 비고를 정리하거나 주문내역의 특기사항을 요약해야 할때 데이터를 정리하는 시간이 엄청 소요된다. 이때 매우 유용하게 사용할 수 있는 방법이 된다. 

주문내역의 특기사항을 요약하는 표를 Pivot Table 로 만들어 보자 

[Data 이해] 

[데이터는 주문내역리스트이고 Remark 항목을 표로 정리해야 한다.]

오더번호 별로 주문 품목과 고객, 주문유형이 등록된 리스트를 가지고 고객,주문유형별 비고(Remark)항목을 정리해야한다.

[작업]

1. 데이터 모델에 추가 
 1) 먼저 파워피벗을 사용하기 위해 해당 Data 를 표로 만든다. 
  - 표로 만들 데이터를 영역으로 지정하고 Ctrl + T -> 표로 구성
  - 구성된 표의 이름을 수정 -> 여기서는 'Table1' 으로 변경함 
    (꼭 변경하지 않아도 된다. 하지만 여러개의 표를 사용할 경우 표 이름 관리는 필수이니 습관을 들이는 것을 추천함)
 2) 표로 구성 후 데이터모델로 구성한다. 
  - Power Pivot 탭의 '데이터 모델에 추가' 를 클릭하여 데이터 모델을 구성 

 여기까지는 작업이 되었으면 이제 측정값을 추가하고 피벗테이블만 만들면 끝~ 

2. 측정값을 생성
 1) Power Pivot 탭의 '측정값' 를 클릭하여 새로운 측정값을 만들자. 

[측정값을 생성하는 팝업]

 사용할 수식은 다음과 같다. 

Remark 값요약:=
CONCATENATEX(
                       SUMMARIZE(
                                         FILTER(
                                                  Table1,
                                                  Table1[Remark]<>BLANK()
                                                 ),
                                         Table1[Remark]
                                         )
                       ,Table1[Remark]
                       ,"
")

 

  <수식 설명> 
  - CONCATENATEX : 테이블의 각 행에 대해 계산되는 식의 결과를 연결 -> Remark 필드의 값들을 텍스트로 합쳐준다. 
  - SUMMARIZE : 그룹 집합에 대해 요청된 합계의 요약 테이블을 반환-> 중복된 값을 하나로 만들기 위해 사용
  - FILTER : 다른 테이블이나 식의 하위 집합을 나타내는 테이블을 반환-> Remark 필드가 공백인 항목을 제거 

[수식 구성 설명]

  * 다른 데이터에 적용할때 표이름과 필드명 만 수정하여 위의 수식을 그대로 사용하면 된다. 

설명 보완 2021.01.03

 한 줄로 요약하자면 "SUMMARISE 함수로 'Remark' 필드를 중복 값없이 요약한 테이블을 만들어 그 값을 1개의 행만 출력하도록 텍스트를 합친 측정값" 이 됩니다. 

 

3. 피벗 테이블 생성 
  1) 마지막 단계 피벗 테이블생성

 

  2) 새시트에 피벗 테이블을 생성하고 다음과 같이 구성하면된다. 

주의 할 점은 그림의 노란색 하이라이트 부분 처럼 '텍스트 줄바꿈' 서식이 먹혀야 한다는 것이다. 그래야 보기 편하다.
수식 설명에서 구분자를 넣을 때 enter 를 넣어 놓은 것은 이것 때문이다. 그리고 피벗 테이블을 보다 보기 좋게 하기위해서 총합계는 제거했다. 

최종 결과 그림과 같이 'Remark 값요약' 필드가 값항목에 쏙 들어오게 되면 Order type 별 , Customer 별로 Remark 항목이 정리된다. 이번 예제는 Remark이 잘 정리된 상태이므로 예쁘게 표가 바로 구성되었다. 하지만 실제 업무에서는 지저분하게 입력된 값들의 Cleaning 과 유형화가 먼저 작업이 되어야 이런 결과를 얻을 수 있다. 이 과정은 Power Query 를 사용해서 전처리를 하는 것이 보다 효율적이라 생각된다. 

예제파일을 첨부합니다. 따라해보면 이해가 더 쉽겠죠. 일찍 퇴근합시다.

---